問題描述
sql 1st 的結果:
the result of sql 1st:
select p1.t1, p2.t2, p2.t3 from
(select 'A' as t1,'' as t2,'' as t3 from dual ) p1 left join
(select '' as t1, '' as t2, '' as t3 from dual where 1=2) p2 on p1.t1 = p2.t1
是
A NULL NULL
sql 2nd的結果:
the result of sql 2nd:
select p1.t1, p2.t2, p2.t3 from
(select 'B' as t1,'' as t2,'' as t3 from dual ) p1 left join
(select '' as t1, '' as t2, '' as t3 from dual where 1=2) p2 on p1.t1 = p2.t1
是
B NULL NULL
如果我將 sql 的所有這兩個部分聯合起來，我期望的 sql 是:
if I union all these two part of sql, my expected sql are:
A NULL NULL
B NULL NULL
但我沒有得到任何結果.
but I get no result .
sql如下:
select p1.t1, p2.t2, p2.t3 from
(select 'A' as t1,'' as t2,'' as t3 from dual ) p1 left join
(select '' as t1, '' as t2, '' as t3 from dual where 1=2) p2 on p1.t1 = p2.t1
union all
select p1.t1, p2.t2, p2.t3 from
(select 'B' as t1,'' as t2,'' as t3 from dual ) p1 left join
(select '' as t1, '' as t2, '' as t3 from dual where 1=2) p2 on p1.t1 = p2.t1
我在 oracle 上測試了這個 sql.我想知道為什么結果不像我的預期.順便說一句，如果使用union而不是union all，結果和預期的一樣.
I tested this sql on oracle. I want to know why the result is not like my expected. by the way, if use union instead of union all, the result is just like expected.
推薦答案
您還沒有說明您在哪個版本上遇到此問題，但 Gordon Linoff 在運行 11.2.0.2 的 SQL Fiddle 上進行了復制，正如其他人所說它在 10g 和 11.2.0.3 中沒有出現，因此認為您可能也在 11.2.0.2 上似乎是合理的.
You haven't said which version you're encountering this on, but Gordon Linoff reproduced on SQL Fiddle which is running 11.2.0.2, and as others have said it's not seen in 10g and 11.2.0.3, so it seems reasonable to think you might be on 11.2.0.2 as well.
在這種情況下，這看起來與錯誤 12336962 相同.如果您可以訪問 Oracle 支持，您(或您的 DBA)可以查看它，但我無法重現它在這里所說的內容，即使它是一個已發布的錯誤.運行您的查詢和錯誤報告中的示例會產生相同的結果，并且在兩種情況下從 union all 更改為 union 都會產生正確的結果.不過，您可能想要提出服務請求以確認這一點.
In which case, this looks identical to bug 12336962. If you have access to Oracle Support you (or your DBA) can look that up, but I can't reproduce what it says here, even though it's a published bug. Running your query and the example from the bug report produce the same results, and in both cases changing from union all to union produces correct results. You might want to raise a service request to get that confirmed though.
該錯誤已在 11.2.0.3 補丁集中修復 - 我不確定我是否真的應該分享它，但它已經在這里發布 - 所以修補可能是你最好的選擇，如果你提出 SR，Oracle 可能會建議你這樣做.如有疑問，請直接詢問 Oracle.
That bug is fixed in the 11.2.0.3 patch set - I'm not sure I'm really supposed to even share that, but it's already published here - so patching up might be your best bet, and Oracle might suggest that if you do raise an SR. If in doubt, ask Oracle directly.
這篇關于為什么這個 sql 使用 union all 沒有返回任何數據的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！