NATURAL INNER JOIN no longer working

From: Diana Nemirovsky <diana(at)marinconsulting(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: NATURAL INNER JOIN no longer working
Date: 2005-07-18 14:14:05
Message-ID: 6.0.1.1.2.20050718070332.02bf3db0@marinconsulting.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Sorry for the vagueness of the last post, I'll try again.

After adding columns onto each table in my schema, queries that used to
work no longer work. I ran the following code on each table:

ALTER TABLE person ADD COLUMN record_created TIMESTAMP;
ALTER TABLE person ADD COLUMN record_created_by_user INT2;
ALTER TABLE person ADD COLUMN record_modified TIMESTAMP;
ALTER TABLE person ADD COLUMN record_modified_by_user INT2;

Shortly after having done so, queries that used to work now don't. I can't
say with certainty that the two are related, but the timing fits. So, for
example,
SELECT id_blog
FROM blog NATURLAL INNER JOIN person_blog_role
WHERE ID_blog_role != 6 AND id_person=590 and blog.top_level='news' AND
blog.title<>'School Notes'
GROUP by id_blog

now returns nothing, whereas before it correctly found a handful of records.

If I rewrite the query as

SELECT blog.id_blog
FROM blog, person_blog_role
WHERE ID_blog_role != 6 AND person_blog_role.ID_blog = blog.ID_blog AND
id_person=590 and blog.top_level='news' AND blog.title<>'School Notes'
GROUP by blog.id_blog

It works fine.

An example of the error I get is
Warning: pg_fetch_result()
[<http://www.codethis.org/function.pg-fetch-result>function.pg-fetch-result]:
Unable to jump to row 0 on PostgreSQL result index 48 in
/usr/local/www/codethis.org/www/lib/dbclasses.php on line 94

The dbClasses function just abstracts the database calls, and the line in
question in dbclasses is:
return pg_fetch_result($this->cursor, $row, $field );

I also in the same time frame added a trigger on one table (and it wouldn't
for example, have touched the tables referenced above), but I dropped it
after this happened to eliminate it as a possible problem.

This has left me dead in the water, so any help or thoughts are appreciated.

Diana Nemirovsky
Marin Consulting, Inc.
770 Menlo Ave, Ste 223
Menlo Park, CA 94025
v (650) 617-8699
f (650) 833-0790
http://www.marinconsulting.com

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Stephan Szabo 2005-07-18 14:32:04 Re: NATURAL INNER JOIN no longer working
Previous Message Tom Lane 2005-07-18 13:38:02 Re: Problem with starting Postgres