Thread: strange behvaviour in join? BUG in 7.02?
I have PSQL 7.02 classic join on Two tables: select l.id from linia l,lk_strefa2linia lk where l.id = lk.fk_linia and lk.fk_strefa=5; gives correct linia.ids. but select linia.id from linia l,lk_strefa2linia lk where l.id = lk.fk_linia and lk.fk_strefa=5; (the only difference is second word: linia.id instead l.id) will give totaly different results). tables definitions: mlotdev=> \d lk_strefa2linia Table "lk_strefa2linia" Attribute | Type | Modifier -----------+---------+---------- fk_strefa | integer | not null fk_linia | integer | not null Index: lk_strefa2linia_ukey mlotdev=> \d linia Table "linia" Attribute | Type | Modifier -----------+-------------+--------------------------------------------- id | integer | not null default nextval('seq_linia'::text) fk_typ | integer | not null numer | char(10) | not null status | char(1) | not null default '?' stamp | timestamp | not null default now() opis | varchar(64) | not null skrot | varchar(16) | not null Index: linia_pkey Please CC answer to me. Thanx! -- radoslaw.stachowiak.........................................http://alter.pl/
Radoslaw Stachowiak wrote: >I have PSQL 7.02 > >classic join on Two tables: > >select l.id from linia l,lk_strefa2linia lk >where l.id = lk.fk_linia and lk.fk_strefa=5; > >gives correct linia.ids. > >but >select linia.id from linia l,lk_strefa2linia lk >where l.id = lk.fk_linia and lk.fk_strefa=5; > >(the only difference is second word: linia.id instead l.id) >will give totaly different results). You are actually doing a 3-way join, with linia included twice. It is implicitly included a second time when a column is referenced from it without the alias. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "But thanks be to God, which giveth us the victory through our Lord Jesus Christ." I Corinthians 15:57
"Oliver Elphick" <olly@lfix.co.uk> writes: > You are actually doing a 3-way join, with linia included twice. > It is implicitly included a second time when a column is referenced > from it without the alias. 7.1 will provide a NOTICE that's intended to alert people that such constructs probably don't do what they're expecting: regression=# select f.f1, int4_tbl.f1 from int4_tbl f; NOTICE: Adding missing FROM-clause entry for table "int4_tbl" although I wonder whether this will leave the query author any less confused :-(. regards, tom lane