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