The following bug has been logged on the website: Bug reference: 8444 Logged by: Andreas Email address: maps...@gmx.net PostgreSQL version: 9.3.0 Operating system: openSUSE 12.3 64bit and Windows XP Description:
I've got the binaries from EnterpriseDB for 64bit Linux as well as 32bit Windows. If a table name or alias appeares within and outside a subquery PG throws the error that this table was specified more than once. The error doesn't show if this table gets joined as last in the subquery. The error stays even without aliases. Sample: ERROR: table name "tblb" specified more than once Switch tblB and tblC in the subquery and it works. WITH tblA (a_id, a_val) AS ( VALUES (1::integer, 'one-1'::varchar) ) , tblB (b_id, b_val) AS ( VALUES (1::integer, 'one-b'::varchar) ) , tblC (c_id, c_val) AS ( VALUES (1::integer, 'one-c'::varchar) ) select * from tblA join tblB on tblA.a_id = tblB.b_id join ( tblB join tblC on tblC.c_id = tblB.b_id ) as x on tblA.a_id = x.c_id; Another sample now with real foreign key constraints: drop table if exists sub_tab; drop table if exists main_tab; drop table if exists flag_1; drop table if exists flag_2; create temporary table flag_1 ( flag_1_id integer primary key, flag_1_t text ); insert into flag_1 values (0, null ), ( 1, 'a' ), ( 2, 'b' ), ( 3, 'c' ); create temporary table flag_2 ( flag_2_id integer primary key, flag_2_t text ); insert into flag_2 values (0, null ), ( 1, 'a' ), ( 2, 'b' ), ( 3, 'c' ); create temporary table main_tab ( main_id integer primary key, main_t text, flag_1_id integer references flag_1 ( flag_1_id ) ); insert into main_tab values ( 1, 'Main 1', 1 ), ( 2, 'Main 2', 2 ), ( 3, 'Main 3', 3 ); create temporary table sub_tab ( sub_id integer primary key, sub_t text, main_id integer references main_tab ( main_id ), flag_1_id integer references flag_1 ( flag_1_id ), flag_2_id integer references flag_2 ( flag_2_id ) ); insert into sub_tab values ( 1, 'Sub 1', 1, 3, 1 ), ( 2, 'Sub 2', 2, 2, 2 ), ( 3, 'Sub 3', 3, 1, 3 ); select m.main_id, m.main_t, f.flag_1_t, x.sub_id, x.sub_t, x.flag_1_t, x.flag_2_t from main_tab as m join flag_1 as f using ( flag_1_id ) left join ( sub_tab as s -- join flag_2 as f2 using ( flag_2_id ) -- this way works join flag_1 as f using ( flag_1_id ) join flag_2 as f2 using ( flag_2_id ) -- that way doesn't work ) as x using ( main_id ); -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs