Just another addition...
If I remove the aliases for the tbles flag_1 and flag_2 the problem
still comes up.
So one either has to mind the order of the joins or use unique aliases.
It's really an issue as there are bound to be some queries in
sql-functions or some that get assembled dynamically in my application.
Those won't get executed in the initial import of the db-dump but will
come up anytime later when the query gets used the first time.
This is a no go :(
regards
Andreas
Am 12.09.2013 00:33, schrieb Andreas:
> 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 );
>
>
>
