sql - EXISTS 不适用于 WITH 子句中的子查询

Question

我遇到了一个查询，其中 EXISTS 子句无法正常工作。即使对于不存在匹配记录的项目，查询也会返回结果，似乎完全忽略了 EXISTS。它曾经工作得很好，我认为从 Oracle 12.1 升级到 12.2 后问题就开始了。

下面是完整的查询(只更改了表和列名称，使它们更具可读性，但我保留了所有逻辑，以防与此相关):

WITH FirstDateFilter AS (
    SELECT ReferenceDate,
           Type,
           LAG(Type, 1, 0) OVER (ORDER BY ReferenceDate) AS PreviousType
    FROM ReferenceDateTable
    WHERE ItemId = :itemId
    AND   ReferenceDate <= :endDate
    AND   Type IN (:type1, :type2)
), SecondDateFilter AS (
    SELECT ReferenceDate
    FROM FirstDateFilter
    WHERE ReferenceDate >= :startDate
    AND   ReferenceDate >= ( SELECT StartDate FROM StartDateTable WHERE ItemId = :itemId )
    AND   Type = :type1
    AND   PreviousType = :type1
)
SELECT ReferenceDate, Value
FROM ResultTable
WHERE ItemId = :itemId
AND EXISTS ( SELECT * FROM SecondDateFilter WHERE SecondDateFilter.ReferenceDate = ResultTable.ReferenceDate )

在处理了一些测试数据之后，我认为(部分？)失败的原因是子查询 AND ReferenceDate >= ( SELECT StartDate FROM StartDateTable WHERE ItemId = :itemId )在第二个 WITH 中。

我发现以下任何编辑都会导致 EXISTS 再次按预期工作:

JOIN ResultTable 与 SecondDateFilter(在 ReferenceDate 上)

把 ( SELECT ReferenceDate FROM SecondDateFilter WHERE SecondDateFilter.ReferenceDate = ResultTable.ReferenceDate )在 SELECT ... FROM ResultTable

注释掉 StartDateTable 子查询(不再过滤该表，否则它会再次工作)

将 StartDateTable 子查询移动到第一个 WITH

最后一个解决方案实际上解决了我这个查询的问题(技术上不一样，但底层业务逻辑检查结果总是相同的)，但我想知道 EXISTS 子句是否存在一般问题(可能只是在 Oracle 12.2 中？)我应该知道。我有更多使用它的查询。

下面是一个重复错误的测试脚本。下面的查询按预期返回 2 行，但删除注释行会得到 5 行。

CREATE TABLE ReferenceDateTable 
    (
     ItemId number,
     ReferenceDate date, 
     Type varchar2(1)
    ); 
INSERT INTO ReferenceDateTable (ItemId, ReferenceDate, Type) VALUES (1, to_date('19000201', 'YYYYMMDD'), '1');
INSERT INTO ReferenceDateTable (ItemId, ReferenceDate, Type) VALUES (1, to_date('19000202', 'YYYYMMDD'), '1');
INSERT INTO ReferenceDateTable (ItemId, ReferenceDate, Type) VALUES (1, to_date('19000203', 'YYYYMMDD'), '2');
INSERT INTO ReferenceDateTable (ItemId, ReferenceDate, Type) VALUES (1, to_date('19000204', 'YYYYMMDD'), '1');
INSERT INTO ReferenceDateTable (ItemId, ReferenceDate, Type) VALUES (1, to_date('19000205', 'YYYYMMDD'), '1');

CREATE TABLE ResultTable 
    (
     ItemId number,
     ReferenceDate date, 
     Value number
    ); 
INSERT INTO ResultTable (ItemId, ReferenceDate, Value) VALUES (1, to_date('19000201', 'YYYYMMDD'), 1);
INSERT INTO ResultTable (ItemId, ReferenceDate, Value) VALUES (1, to_date('19000202', 'YYYYMMDD'), 2);
INSERT INTO ResultTable (ItemId, ReferenceDate, Value) VALUES (1, to_date('19000203', 'YYYYMMDD'), 3);
INSERT INTO ResultTable (ItemId, ReferenceDate, Value) VALUES (1, to_date('19000204', 'YYYYMMDD'), 4);
INSERT INTO ResultTable (ItemId, ReferenceDate, Value) VALUES (1, to_date('19000205', 'YYYYMMDD'), 5);

CREATE TABLE StartDateTable
    (
     ItemId number,
     StartDate date
    ); 
INSERT INTO StartDateTable (ItemId, StartDate) VALUES (1, to_date('19000101', 'YYYYMMDD'));

WITH FirstDateFilter AS (
    SELECT ReferenceDate,
           Type,
           LAG(Type, 1, 0) OVER (ORDER BY ReferenceDate) AS PreviousType
    FROM ReferenceDateTable
    WHERE ItemId = 1
    AND   ReferenceDate <= to_date('19000205', 'YYYYMMDD')
    AND   Type IN ('1', '2')
), SecondDateFilter AS (
    SELECT ReferenceDate
    FROM FirstDateFilter
    WHERE ReferenceDate >= to_date('19000201', 'YYYYMMDD')
    --AND   ReferenceDate >= ( SELECT StartDate FROM StartDateTable WHERE ItemId = 1 )
    AND   Type = '1'
    AND   PreviousType = '1'
)
SELECT ReferenceDate, Value
FROM ResultTable
WHERE ItemId = 1
AND EXISTS ( SELECT * FROM SecondDateFilter WHERE SecondDateFilter.ReferenceDate = ResultTable.ReferenceDate )
;

Answer 1

根据 Jonathan's comments over on Twitter ，建议的解决方法是使用 unnest外部存在子查询中的提示，因为问题是由于错误(可能是错误 28319114)造成的。

[...]
SELECT ReferenceDate, Value
FROM ResultTable
WHERE ItemId = 1
AND EXISTS ( SELECT /*+ UNNEST */ * FROM SecondDateFilter WHERE SecondDateFilter.ReferenceDate = ResultTable.ReferenceDate )