Aw: ANSI Join – Rename of Alias Leads to a Different Result
From: <rogel_at_web.de>
Date: Wed, 23 Dec 2020 08:18:29 +0100
Message-ID: <trinity-c9a991e5-842c-4b5c-993e-6d631a339d79-1608707908995_at_3c-app-webde-bs11>
into
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Dec 23 2020 - 08:18:29 CET
Date: Wed, 23 Dec 2020 08:18:29 +0100
Message-ID: <trinity-c9a991e5-842c-4b5c-993e-6d631a339d79-1608707908995_at_3c-app-webde-bs11>
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 /
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"(+)
) "A3",
"MATTHIAS"."TAB4" "A2"
where "A3"."FK_ID_1" = "A2"."FK_ID"(+)
) "A1"
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"(+)
) "A3",
"MATTHIAS"."TAB4" "A2"
where "A3"."FK_ID_1" = "A2"."FK_ID"(+)
) "A1"
...
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
means that until this is fixed in some cases for the check of ambiguously
defined columns in ANSI SQL is responsible the query writer
...
I guess the query writer is always responsible for what she/he does.
Luckily there are great tools to support us here, my favourite one is
https://www.jooq.org/translate/
(with "Transformation Options" set to "ANSI join to Oracle style").
It transforms
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
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
into
select D.FK_ID
from
TAB1 A,
TAB2 C,
TAB3 C,
TAB4 D
where (
A.ID = C.FK_ID(+)
and A.ID = C.FK_ID(+)
and C.FK_ID = D.FK_ID(+)
)
from
TAB1 A,
TAB2 C,
TAB3 C,
TAB4 D
where (
A.ID = C.FK_ID(+)
and A.ID = C.FK_ID(+)
and C.FK_ID = D.FK_ID(+)
)
, which results in
ERROR at line 10:
ORA-00918: column ambiguously defined
ORA-00918: column ambiguously defined
when thrown to Oracle.
Regards,
Matthias
Gesendet: Dienstag, 22. Dezember 2020 um 23:16 Uhr
Von: "jaromir nemec" <jaromir_at_db-nemec.com>
An: oracle-l_at_freelists.org
Betreff: ANSI Join � Rename of Alias Leads to a Different Result
Von: "jaromir nemec" <jaromir_at_db-nemec.com>
An: oracle-l_at_freelists.org
Betreff: ANSI Join � Rename of Alias Leads to a Different Result
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
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