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

Hi, Matthias,  

thanks for the replay and additional information.  

The expand_sql_text provides indeed the explanation. In the last WHERE condition Oracle takes the first one of the ambiguous FK_ID columns  

  WHERE "A3"."FK_ID_1"="A2"."FK_ID"(+)   The correct predicate should take the second one  

  WHERE "A3"."FK_ID_2"="A2"."FK_ID"(+)   I’ll open a SR with Oracle, let see if some of this fine checking responsibilities, which are of course in last resort on the developer duty will be taken from his shoulders;)  

Kind Regards,  

Jaromir  

From: rogel_at_web.de [mailto:rogel_at_web.de] Sent: Mittwoch, 23. Dezember 2020 08:18
To: jaromir_at_db-nemec.com
Cc: oracle-l_at_freelists.org
Subject: Aw: ANSI Join – Rename of Alias Leads to a Different Result  

Nice Jaromir, thanks for sharing !  

Definitely a bug I'd think.  

You can see how the query is internally rewritten (final output nicely formatted by me)  

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2 l_clob CLOB;
  3 BEGIN
  4 DBMS_UTILITY.expand_sql_text (
  5 input_sql_text => 'select d.fk_id   6 from tab1 a
  7 left outer join tab2 c on a.id = c.fk_id /* Typo here - c alias is used   8 instead of b */
  9 left outer join tab3 c on a.id = c.fk_id  10 left outer join tab4 d on c.fk_id = d.fk_id  11 ',
 12 output_sql_text => l_clob
 13 );
 14
 15 DBMS_OUTPUT.put_line(l_clob);
 16 END;
 17 /

select "A1"."FK_ID_3" "FK_ID"
from (
  select
"A3"."ID_0" "ID",
"A3"."FK_ID_1" "FK_ID",
"A3"."FK_ID_2" "FK_ID",
"A2"."FK_ID" "FK_ID_3"
  from
    (

      select
        "A5"."ID_0" "ID_0",
        "A5"."FK_ID_1" "FK_ID_1",
        "A4"."FK_ID" "FK_ID_2"
      from
        (
          select
            "A7"."ID" "ID_0",
            "A6"."FK_ID" "FK_ID_1"
          from
            "MATTHIAS"."TAB1" "A7",
            "MATTHIAS"."TAB2" "A6"
          where "A7"."ID" = "A6"."FK_ID"(+)
        ) "A5",
        "MATTHIAS"."TAB3" "A4"
      where "A5"."ID_0" = "A4"."FK_ID"(+)

...

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

...      

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