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

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 );

A much more simple example courtesy of Chris Travers from the original
-general thread that I suggested be moved to -bugs.


> 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.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/BUG-8444-ERROR-table-name-tblb-specified-more-than-once-in-subquery-tp5770540p5770654.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

David Johnston <polobo@yahoo.com> writes:
>> 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;
>>
>> On 9.3 it fails:
>> ERROR:  table name "u" specified more than once

This is an intentional change that came in with the LATERAL feature.
The query is illegal per SQL spec but we used to allow it anyway,
on the theory that the table name "u" inside the aliased join "x"
wasn't visible anywhere that the other "u" was visible, so the
duplicate alias name was harmless.  But in the presence of LATERAL
it's not harmless; consider

select * FROM current_user u join
  (current_user u cross join LATERAL (select u.x) v) x on true;

Which instance of "u" does the lateral reference refer to?

(I think there was some discussion of this in the pgsql-hackers list
about a year ago, but I couldn't find it in a desultory search.)

            regards, tom lane

Tom Lane-2 wrote
> David Johnston <

> polobo@

> > writes:
>>> 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;
>>>
>>> On 9.3 it fails:
>>> ERROR:  table name "u" specified more than once
>
> This is an intentional change that came in with the LATERAL feature.
> The query is illegal per SQL spec but we used to allow it anyway,
> on the theory that the table name "u" inside the aliased join "x"
> wasn't visible anywhere that the other "u" was visible, so the
> duplicate alias name was harmless.  But in the presence of LATERAL
> it's not harmless; consider
>
> select * FROM current_user u join
>   (current_user u cross join LATERAL (select u.x) v) x on true;
>
> Which instance of "u" does the lateral reference refer to?
>
> (I think there was some discussion of this in the pgsql-hackers list
> about a year ago, but I couldn't find it in a desultory search.)
>
>             regards, tom lane

I do vaguely recall that said discussion exists.  However, this and the
-general thread for the same issue both seem to indicate that the actual
order of the joining affects whether the error is thrown...I guess the way
LATERAL works this does make sense - somewhat.

While the behavior is intentional not mentioning it in the release notes,
section

E.1.2. Migration to Version 9.3

is an oversight that should be corrected.

Might be worth finding and linking to the thread in the release notes so
that people affected by this change go and look to figure out why it was
made.  Given your example involves a LATERAL sub-clause my first thought is
that any non-LATERAL (and thus all previous version) queries would be
unaffected.

If I find a link I'll come back and post it for reference from here and
-general at least.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/BUG-8444-ERROR-table-name-tblb-specified-more-than-once-in-subquery-tp5770540p5770710.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

[ for the archives' sake ]

David Johnston <polobo@yahoo.com> writes:
> Tom Lane-2 wrote
>> This is an intentional change that came in with the LATERAL feature.
>> The query is illegal per SQL spec but we used to allow it anyway,
>> on the theory that the table name "u" inside the aliased join "x"
>> wasn't visible anywhere that the other "u" was visible, so the
>> duplicate alias name was harmless.  But in the presence of LATERAL
>> it's not harmless; consider

> While the behavior is intentional not mentioning it in the release notes,
> is an oversight that should be corrected.

The point that this is an incompatible change is a good one.  I've
reconsidered and concluded it's better to remove the duplicate-alias
check; instead, we'll throw an error if there actually is an ambiguous
reference in a LATERAL subquery.  This patch will appear in 9.3.2.

            regards, tom lane