sql - 具有外连接的查询在 Oracle 12c 中的行为不同

Question

我遇到了有关 Oracle 12c 上丢失数据的问题。

我查看了代码，发现一个查询适用于 mysql、mssql、oracle 11g，但在 oracle 12c 中具有不同的行为。

我已经概括了表结构和查询并重现了这个问题。

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');

运行查询:

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)

在 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)

然而，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)

似乎后两个外部连接未能带回任何内容，因为“thing_related”中没有要加入的行。但是我不明白为什么在这种情况下外连接不会像在 Oracle 11g、Mysql 等中那样返回空值。

我一直在研究并发现文档 Oracle 12c 对外连接进行了许多增强，但没有任何内容突出显示会影响这一点的更改。

有谁知道为什么这只发生在 Oracle 12c 上，我该如何最好地重写它以在 12c 中工作并保持与 11g、mysql 等的兼容性？

编辑:附加计划。

甲骨文 11g:



甲骨文 12c:

Answer 1

更新:这已在 12.1.0.2 中修复。

这绝对看起来像是 12.1.0.1 中的错误。我鼓励您通过 Oracle 支持创建服务请求。他们也许能够找到解决方法或更好的解决方法。希望 Oracle 可以在 future 的版本中为每个人修复它。通常，与支持人员合作最糟糕的部分是重现问题。但是由于您已经有一个非常好的测试用例，所以这个问题可能很容易解决。

可能有很多方法可以解决此错误。但是很难说哪种方法总是有效。您的查询重写现在可能会起作用，但是如果优化器统计信息发生变化，那么计划将来可能会更改回来。

在 12.1.0.1.0 上对我有用的另一个选项是:

ALTER SESSION SET optimizer_features_enable='11.2.0.3';

但是您需要记住在运行查询之前始终更改此设置，然后将其更改回“12.1.0.1”。有多种方法可以将其嵌入到查询提示中，例如 /*+ OPT_PARAM('optimizer_features_enable' '11.2.0.3') */ .但由于某种原因，这在这里不起作用。或者，您可以暂时为整个系统设置它，并在修复或更好的解决方法可用后将其更改回来。

无论您使用哪种解决方案，请记住将其记录下来。如果查询看起来很奇怪，下一个开发人员可能会尝试“修复”它并遇到同样的问题。