BUG #8444: ERROR: table name "tblb" specified more than once in subquery

From: maps(dot)on(at)gmx(dot)net
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #8444: ERROR: table name "tblb" specified more than once in subquery
Date: 2013-09-11 23:51:26
Message-ID: E1VJuBy-0002a1-Qv@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 8444
Logged by: Andreas
Email address: maps(dot)on(at)gmx(dot)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 );

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Thomas Kellerer 2013-09-12 06:50:51 Re: Cant start PostgreSQL Using command prompt
Previous Message John R Pierce 2013-09-11 17:19:22 Re: Cant start PostgreSQL Using command prompt