BUG #8444: ERROR: table name "tblb" specified more than once in subquery - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      8444
Logged by:          Andreas
Email address:      maps.on@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 );