Обсуждение: NATURAL INNER JOIN no longer working
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
On Mon, 18 Jul 2005, Diana Nemirovsky wrote: > 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. Which makes sense since they now have common column names that probably don't match in value. NATURAL means find common column names and join using those.
Diana, can you go back to a prior backup of your db (the query should still work) and then make modifications one at a time and test the query? i wouldn't thinking adding a column would impact this at all. you can then use what you learn to help resolve your current issue. i also assume pgsql is the same version / revision as before (when the query worked) and that the data is the same or similar. if not, that could be a cause, too. --- Diana Nemirovsky <diana@marinconsulting.com> wrote: > 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 > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please > send an appropriate > subscribe-nomail command to > majordomo@postgresql.org so that your > message can get through to the mailing list > cleanly > ____________________________________________________ Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs