Here is a minimal query that demonstrates the problem. In 9.1 it works: chris=# select * FROM current_user u join (current_user u cross join current_user v) x on true; u | u | v -------+-------+------- chris | chris | chris (1 row)
On 9.3 it fails: ERROR: table name "u" specified more than once It may be a silly example but it works. On Wed, Sep 11, 2013 at 5:07 PM, Andreas <maps...@gmx.net> wrote: > I took your sample and modified it a bit. > > 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; > > > > > Am 12.09.2013 00:52, schrieb David Johnston: > > Andreas-3-2 wrote >> >>> I never placed a bug-report. :} >>> Could you give a hand? >>> >> Sure. >> >> PostgreSQL homepage (postgresql.org) >> "Report a Bug" link on right-hand side of page. >> Fill in the form. >> >> SEND THIS>>>> >>>>> >>>> >> 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 ) -- that >>> way it works >>> join flag_1 as f using ( flag_1_id ) >>> -- join flag_2 as f2 using ( flag_2_id ) -- that >>> way it doesn't work >>> ) as x using ( main_id ); >>> >> >> >> >> >> -- >> View this message in context: http://postgresql.1045698.n5.** >> nabble.com/PG-9-3-complains-**about-specified-more-than-** >> once-Those-views-worked-in-PG-**9-1-9-2-tp5770489p5770534.html<http://postgresql.1045698.n5.nabble.com/PG-9-3-complains-about-specified-more-than-once-Those-views-worked-in-PG-9-1-9-2-tp5770489p5770534.html> >> Sent from the PostgreSQL - general mailing list archive at Nabble.com. >> >> >> > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general> > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more.shtml
