[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.