This might be another manifestation of
*Bug 25342699 : WRONG RESULTS WITH ANSI JOIN USING AND IDENTICAL TABLE
ALIASES*
See: https://jonathanlewis.wordpress.com/2020/03/18/using-bug/
Although the example there uses the USING() clause rather thon ON, and it's
not an outer join.
Regards
Jonathan Lewis
On Tue, 22 Dec 2020 at 22:19, jaromir nemec <jaromir@xxxxxxxxxxxx> wrote:
I know that ANSI joins have some different strategy for query blocks
compared with the Oracle join syntax, but I did not know until recently,
that some extra care with checking the syntax should be taken.
Let’s start with a little anecdotic evidence – the query below (which I
call a double outer join) is a valid syntax in 19.8
select *
from tab1 a
left outer join tab2 b on a.id = b.fk_id(+);
Well this is probably tolerated due to Oracle internal interpretation and
can IMO cause no harm – if you try to place the (+) on the other side you
get an error.
But recently I was confronted with an example, where similar typo can
produce a completely different query result.
I’ll illustrate it on a minimal example.
create table tab1 as select 1 id from dual;
create table tab2 as select 1 fk_id from dual;
create table tab3 (fk_id number);
create table tab4 as select 1 fk_id from dual;
The following query returns NULL, because the outer join to tab3 fails as
the table is empty, so no match in tab4 is the consequence.
select d.fk_id
from tab1 a
left outer join tab2 b on a.id = b.fk_id
left outer join tab3 c on a.id = c.fk_id
left outer join tab4 d on c.fk_id = d.fk_id
FK_ID
----------
(Null)
Now if I make a typo in the query and use the same alias twice, it happens
the query executes and returns a result
select d.fk_id
from tab1 a
left outer join tab2 c on a.id = c.fk_id /* Typo here - c alias is used
instead of b */
left outer join tab3 c on a.id = c.fk_id
left outer join tab4 d on c.fk_id = d.fk_id
FK_ID
----------
1
Apparently the result of the join to tab2.fk_id now “helps” to make the
join to the tab3.
I think it is a bug – rewriting the query in Oracle proprietary outer join
you get ORA-00918: column ambiguously defined.
I would not share it as it is probably not a big problem, but I was
somehow fascinating with this behaviour and decided to post it. For me it
means that until this is fixed in some cases for the check of ambiguously
defined columns in ANSI SQL is responsible the query writer...
Is my interpretation right?
KR,
Jaromir D.B.Nemec
--
//www.freelists.org/webpage/oracle-l
