[20160106]ANSI bug.txt
--昨天看了链接：https://jonathanlewis.wordpress.com/2016/01/04/ansi-bug/
--语句很奇特，我自己重复测试看看：
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
with
table1 as ( select 1 my_number from dual ),
table2 as ( select 1 my_number from dual )
select *
from (
select sum(table3.table2.my_number) the_answer
from table1
left join table2 on table1.my_number = table2.my_number
group by table1.my_number
);
THE_ANSWER
----------
1
--换成10.2.0.4下执行，结果也一样：
SCOTT@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
--把table3换成任意的符合规范的表名，都可以通过，而实际上table3根本不存在。
SCOTT@book> alter session set events '10053 trace name context forever, level 12'
Session altered.
With
table1 as ( select 1 my_number from dual ),
table2 as ( select 1 my_number from dual )
select *
from (
select sum(table3.table2.my_number) the_answer
from table1
left join table2 on table1.my_number = table2.my_number
group by table1.my_number
);
SCOTT@book> alter session set events '10053 trace name context off';
Session altered.
--检查跟踪文件：
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT SUM(CASE WHEN "DUAL".ROWID IS NOT NULL THEN 1 ELSE NULL END ) "THE_ANSWER" FROM "SYS"."DUAL" "DUAL","SYS"."DUAL" "DUAL" WHERE CASE WHEN "DUAL".ROWID IS NOT NULL THEN 1 ELSE 1 END =CASE WHEN ("DUAL".ROWID(+) IS NOT NULL) THEN 1 ELSE NULL END GROUP BY 1
kkoqbc: optimizing query block SEL$165705CA (#1)
--格式化看看：
SELECT SUM (CASE WHEN "DUAL".ROWID IS NOT NULL THEN 1 ELSE NULL END)
"THE_ANSWER"
FROM "SYS"."DUAL" "DUAL", "SYS"."DUAL" "DUAL"
WHERE CASE WHEN "DUAL".ROWID IS NOT NULL THEN 1 ELSE 1 END =
CASE WHEN ("DUAL".ROWID(+) IS NOT NULL) THEN 1 ELSE NULL END
GROUP BY 1;
--不过这样并不能执行通过，会报：
ERROR at line 5:
ORA-00918: column ambiguously defined
--要修改如下：
SELECT SUM (CASE WHEN "DUAL".ROWID IS NOT NULL THEN 1 ELSE NULL END)
"THE_ANSWER"
FROM "SYS"."DUAL" "DUAL", "SYS"."DUAL" "DUAL1"
WHERE CASE WHEN "DUAL".ROWID IS NOT NULL THEN 1 ELSE 1 END =
CASE WHEN ("DUAL1".ROWID(+) IS NOT NULL) THEN 1 ELSE NULL END
GROUP BY 1;
--作者的测试还有另外一个例子：
with
table1 as ( select 1 my_number from dual ),
table2 as ( select 1 my_number from dual )
select sum(table3.table2.my_number) the_answer
from table1
left join table2 on table1.my_number = table2.my_number
group by table1.my_number;
--问题依旧。但是作者在12c下测试得到如何答案：
If you're running 12.1.0.2 the first query produces the ORA-00904 error that it should do, but the second query still
survives to produce the same result as 11.2.0.4.
