具有外部联接的查询在Oracle 12c中的行为有所不同 [英] Query featuring outer joins behaves differently in Oracle 12c

问题描述

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

I had a problem come through concerning missing data on Oracle 12c.

我看了一下代码，发现一个适用于mysql，mssql，oracle 11g的查询，但在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.

我已经概括了表结构并进行了一些查询，并重现了该问题.

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

运行查询:

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小提琴):

on Oracle 11g gives (here's a 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上具有相同的架构，数据和查询:

Yet the same schema, data, and query on 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等中那样返回空值.

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..

我一直在研究并发现Oracle 12c的文档对外部联接进行了许多增强，但是没有任何东西可以突出显示会影响此连接的更改.

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上会发生这种情况吗?我如何最好地重写它以便在12c上工作并保持与11g，mysql等的兼容性?

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 11g:

Oracle 11g:

Oracle 12c:

Oracle 12c:

推荐答案

更新:此问题已在12.1.0.2.中修复.

UPDATE: This is fixed in 12.1.0.2.

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

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.

可能有很多方法可以解决此错误.但是很难确定哪种方法将始终有效.您的查询重写现在可能会起作用，但是如果优化器统计信息发生变化，则该计划可能会在将来更改.

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.

对我而言，适用于12.1.0.1.0的另一个选项是:

Another option that works for me on 12.1.0.1.0 is:

ALTER SESSION SET optimizer_features_enable='11.2.0.3';

但是您需要记住始终在运行查询之前更改此设置，然后再将其更改回"12.1.0.1".有多种方法可以将其嵌入查询提示中，例如/*+ OPT_PARAM('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.

无论使用哪种解决方案，请务必对其进行记录.如果查询看起来很奇怪，那么下一个开发人员可能会尝试修复"它并遇到相同的问题.

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.

这篇关于具有外部联接的查询在Oracle 12c中的行为有所不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！