Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group