案例行為在 11.2.0.1 和 11.2.0.2 之間更改

我有一個與 Oracle 合作的案例，他們說他們無法重現這一點。在 11.2.0.1 或更低版本中執行以下命令會從這兩個查詢中生成兩行，但在 11.2.0.2 中，第二個查詢將返回三行，除非刪除了 case 語句。任何人都可以確認這種行為嗎？

create table t1 as (select rownum+1 id1 from dual connect by rownum<=3);
create table t2 as (select rownum+1 id2 from dual connect by rownum<=2);

--Correct version that returns two rows.
SELECT t1.id1 id1, t2.id2 id2, t3.id3 id3
, DECODE(1,1,'a','b') "Case/Decode"
FROM t1
LEFT JOIN t2 ON t1.id1 = t2.id2
LEFT JOIN (SELECT 1 id3 FROM dual) t3 ON t3.id3 = t1.id1
WHERE (t2.id2 IS NOT NULL or t3.id3 IS NOT NULL);

--Incorrect version that returns three rows when it should return two.
SELECT t1.id1 id1, t2.id2 id2, t3.id3 id3
, case when 1=1 then 'a' else 'b' end "Case/Decode"
FROM t1
LEFT JOIN t2 ON t1.id1 = t2.id2
LEFT JOIN (SELECT 1 id3 FROM dual) t3 ON t3.id3 = t1.id1
WHERE (t2.id2 IS NOT NULL or t3.id3 IS NOT NULL);

同樣的問題似乎正在影響以下內容，即使行數相同，它也會返回錯誤的數據。

create table t1 as (select rownum id1 from dual connect by rownum<=3);

--Correct version.
SELECT t1.id1, NVL(t2.id2,'NULL') id2, 'No' Case_Present, t2.Constant_Value
FROM t1
LEFT JOIN (select null id2, 'Should Be NULL' Constant_Value from dual) t2
ON t1.id1 = t2.id2;

--Incorrect version shows values for Constant_Value when it shouldn't.
SELECT t1.id1, NVL(t2.id2,'NULL') id2
, case
when 1=1 then ('Yes')
else ' '
end as Case_Present
, t2.Constant_Value
FROM t1
LEFT JOIN (select null id2, 'Should Be NULL' Constant_Value from dual) t2
ON t1.id1 = t2.id2;

可以在託管 apex.oracle.com 的 11gR2 數據庫上重現

以下查詢產生 3 行：

SELECT t1.*, t2.*, t3.dummy, case when 1=1 then 'a' else 'b' end "Case/Decode"
FROM t1
LEFT JOIN (select 1 dummy from dual) t3 ON t3.dummy = t1.id1
LEFT JOIN t2 ON t1.id1 = t2.id2
WHERE (t2.id2 IS NOT NULL or t3.dummy IS NOT NULL)

刪除 CASE 選擇會給出兩行並將 t3.dummy 的值從“1”更改為空。

使用 EXPLAIN PLAN，您可以看到添加 CASE 會刪除 FILTER

"T2"."ID2" IS NOT NULL OR CASE WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END IS NOT NULL"

我懷疑優化器正在將連結重新調整為雙重連結。在內聯視圖中執行對偶的外部連接是非常奇怪的事情，所以我懷疑它不會影響很多人。

引用自：https://dba.stackexchange.com/questions/665