Re: Re: PG 9.3 complains about specified more than once ??? Those views worked in PG 9.1 + 9.2

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(dot)on(at)gmx(dot)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(at)postgresql(dot)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