Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi David,
your 3 examples work as you expected. That is 1+2 work and 3 throws an error.

I tried to figure out an example and found something peculiar.
The issue arises when there is another join in the subquery after the one with the reused table alias.
There is no error without this following join.

Look at this rather chunky sample.
If I join flag_2 before flag_1 it works.
It won't with flag_2 after flag_1.

The query works as soon as the reused alias joins last in the subquery.
If there are 2 reused aliases then the query wont work at all without renaming one alias.

The error shows with pgAdmin aws well as psql (9.3).

So probaply it's not an namespace issue but the query-parser screws up.   :(

I never placed a bug-report.      :}
Could you give a hand?


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



--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux