Re: ANSI Join – Rename of Alias Leads to a Different Result

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_at_db-nemec.com> 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
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l