为什么这个 sql 使用 union all 没有返回任何数据 [英] why this sql used union all returned no data

问题描述

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 没有返回任何数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！