具有外连接的查询在 Oracle 12c 中的行为不同
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19686262/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query featuring outer joins behaves differently in Oracle 12c
提问by amcintosh
I had a problem come through concerning missing data on Oracle 12c.
我遇到了有关 Oracle 12c 上丢失数据的问题。
I took a look at the code and found a query that works on mysql, mssql, oracle 11g, but has different behaviour in oracle 12c.
我查看了代码，发现一个查询适用于 mysql、mssql、oracle 11g，但在 oracle 12c 中具有不同的行为。
I have generalized the table structure and query somewhat and reproduced the issue.
我已经概括了表结构和查询并重现了这个问题。
create table thing (thing_id number, display_name varchar2(500));
create table thing_related (related_id number, thing_id number, thing_type varchar2(500));
create table type_a_status (related_id number, status varchar2(500));
create table type_b_status (related_id number, status varchar2(500));
insert into thing values (1, 'first');
insert into thing values (2, 'second');
insert into thing values (3, 'third');
insert into thing values (4, 'fourth');
insert into thing values (5, 'fifth');
insert into thing_related values (101, 1, 'TypeA');
insert into thing_related values (102, 2, 'TypeB');
insert into thing_related values (103, 3, 'TypeB');
insert into thing_related (related_id, thing_id) values (104, 4);
insert into type_a_status values (101, 'OK');
insert into type_b_status values (102, 'OK');
insert into type_b_status values (103, 'NOT OK');
Running the query:
运行查询：
SELECT t.thing_id AS id, t.display_name as name,
tas.status as type_a_status,
tbs.status as type_b_status
FROM thing t LEFT JOIN thing_related tr
ON t.thing_id = tr.thing_id
LEFT JOIN type_a_status tas
ON (tr.related_id IS NOT NULL
AND tr.thing_type = 'TypeA'
AND tr.related_id = tas.related_id)
LEFT JOIN type_b_status tbs
ON (tr.related_id IS NOT NULL
AND tr.thing_type = 'TypeB'
AND tr.related_id = tbs.related_id)
on Oracle 11g gives (here's a SQL Fiddle):
在 Oracle 11g 上给出（这里是SQL Fiddle）：
ID | NAME | TYPE_A_STATUS | TYPE_B_STATUS
1 | first | OK | (null)
2 | second | (null) | OK
3 | third | (null) | NOT OK
4 | fourth | (null) | (null)
5 | fifth | (null) | (null)
Yet the same schema, data, and query on Oracle 12c:
然而，Oracle 12c 上的相同架构、数据和查询：
ID | NAME | TYPE_A_STATUS | TYPE_B_STATUS
1 | first | OK | (null)
2 | second | (null) | OK
3 | third | (null) | NOT OK
4 | fourth | (null) | (null)
It seems that the second two outer joins are failing to bring back anything because there is no row in 'thing_related' to join by. However I don't understand why the outer join does not return nulls in this case as it does in Oracle 11g, Mysql, etc..
似乎后两个外部连接未能带回任何内容，因为“thing_related”中没有要加入的行。但是我不明白为什么在这种情况下外连接不会像在 Oracle 11g、Mysql 等中那样返回空值。
I've been researching and found documentation the Oracle 12c had a number of enhancements for outer joins, but nothing that highlighted a change that would affect this.
我一直在研究并发现文档 Oracle 12c 对外连接进行了许多增强，但没有任何内容突出显示会影响这一点的更改。
Does anyone know why this is happening only for Oracle 12c, and how best would I rewrite this to work in 12c and maintain compatibility with 11g, mysql, etc.?
有谁知道为什么这只发生在 Oracle 12c 上，我该如何最好地重写它以在 12c 中工作并保持与 11g、mysql 等的兼容性？
EDIT: Attached plans.
编辑：附加计划。
Oracle 11g:
甲骨文 11g：
Oracle 12c:
甲骨文 12c：
采纳答案by Jon Heller
UPDATE:This is fixed in 12.1.0.2.
更新：这已在 12.1.0.2 中修复。
This definitely looks like a bug in 12.1.0.1. I would encourage you to create a service request through Oracle support. They might be able to find a fix or a better work around. And hopefully Oracle can fix it in a future version for everyone. Normally the worst part about working with support is reproducing the issue. But since you already have a very good test case this issue may be easy to resolve.
这绝对看起来像是 12.1.0.1 中的错误。我鼓励您通过 Oracle 支持创建服务请求。他们或许能够找到修复方法或更好的解决方法。希望 Oracle 可以在未来的版本中为每个人修复它。通常，与支持人员合作最糟糕的部分是重现问题。但是由于您已经有一个非常好的测试用例，所以这个问题可能很容易解决。
There are probably many ways to work around this bug. But it's difficult to tell which method will always work. Your query re-write may work now, but if optimizer statistics change perhaps the plan will change back in the future.
可能有很多方法可以解决此错误。但是很难说哪种方法总是有效。您的查询重写现在可能会起作用，但是如果优化器统计信息发生变化，那么计划将来可能会更改回来。
Another option that works for me on 12.1.0.1.0 is:
在 12.1.0.1.0 上对我有用的另一个选项是：
ALTER SESSION SET optimizer_features_enable='11.2.0.3';
But you'd need to remember to always change this setting before the query is run, and then change it back to '12.1.0.1' after. There are ways to embed that within a query hint, such as /*+ OPT_PARAM('optimizer_features_enable' '11.2.0.3') */. But for some reason that does not work here. Or perhaps you can temporarily set that for the entire system and change it back after a fix or better work around is available.
但是您需要记住在运行查询之前始终更改此设置，然后将其更改回“12.1.0.1”。有多种方法可以将其嵌入查询提示中，例如/*+ OPT_PARAM('optimizer_features_enable' '11.2.0.3') */. 但由于某种原因，这在这里不起作用。或者，您可以暂时为整个系统设置它，并在修复或更好的解决方法可用后将其更改回来。
Whichever solution you use, remember to document it. If a query looks odd the next developer may try to "fix" it and hit the same problem.
无论您使用哪种解决方案，请记住将其记录下来。如果查询看起来很奇怪，下一个开发人员可能会尝试“修复”它并遇到同样的问题。
回答by Brian Fitzgerald
Refer to:
参考：
ANSI Outer Join Query Returns Wrong Results After Upgrade to 12.1.0.1 (Doc ID 1957943.1)
ANSI 外部联接查询在升级到 12.1.0.1 后返回错误结果（文档 ID 1957943.1）
Unpublished bug 16726638
未发布的错误 16726638
Fixed in 12.1.0.2 (I have tested this)
已在 12.1.0.2 中修复（我已对此进行了测试）
Workaround (I tested in 12.1.0.1):
解决方法（我在 12.1.0.1 中测试过）：
alter session set "_optimizer_ansi_rearchitecture"=false;
Note 1957943.1 recommends as an alternative:
Note 1957943.1 建议作为替代方案：
optimizer_features_enable = '11.2.0.4';
but that does notwork.
但这并不能正常工作。
回答by vibhanshu sharma
I've a migration planned from 11gR2 to 12c and whole lot of syntax is in ANSI. Its really a nightmare to test each and every query and compare it with the 11g data. Is setting alter session set "_optimizer_ansi_rearchitecture"=false; is the only solution or the bug is fixed
我计划从 11gR2 迁移到 12c，而且很多语法都在 ANSI 中。测试每个查询并将其与11g数据进行比较真的是一场噩梦。正在设置alter session set "_optimizer_ansi_rearchitecture"=false; 是唯一的解决方案或错误已修复
