問題描述

sql 1st 的結(jié)果:

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的結(jié)果:

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 的所有這兩個部分聯(lián)合起來，我期望的 sql 是:

if I union all these two part of sql, my expected sql are:

A    NULL    NULL
B    NULL    NULL

但我沒有得到任何結(jié)果.

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.我想知道為什么結(jié)果不像我的預(yù)期.順便說一句，如果使用union而不是union all，結(jié)果和預(yù)期的一樣.

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 上進(jìn)行了復(fù)制，正如其他人所說它在 10g 和 11.2.0.3 中沒有出現(xiàn)，因此認(rèn)為您可能也在 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)可以查看它，但我無法重現(xiàn)它在這里所說的內(nèi)容，即使它是一個已發(fā)布的錯誤.運行您的查詢和錯誤報告中的示例會產(chǎn)生相同的結(jié)果，并且在兩種情況下從 union all 更改為 union 都會產(chǎn)生正確的結(jié)果.不過，您可能想要提出服務(wù)請求以確認(rèn)這一點.

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 補丁集中修復(fù) - 我不確定我是否真的應(yīng)該分享它，但它已經(jīng)在這里發(fā)布 - 所以修補可能是你最好的選擇，如果你提出 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.

這篇關(guān)于為什么這個 sql 使用 union all 沒有返回任何數(shù)據(jù)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！