EXISTS not working with subquery in WITH clause

I ran into a query where the EXISTS clause is not working properly. The query returns results even for items where no matching record exist, seemingly ignoring the EXISTS completely. It used to work fine and I think the troubles started after upgrading from Oracle 12.1 to 12.2.

Below is the complete query (only changed table & column names to make them a little more readable, but I left all the logic in case it’s related to that):

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 )

After playing around with some test data, I think the (partially?) responsible line for the failure is the subquery AND ReferenceDate >= ( SELECT StartDate FROM StartDateTable WHERE ItemId = :itemId ) in the second WITH.

I have found that any of the following edits result in the EXISTS working as expected again:

JOIN ResultTable with SecondDateFilter (on ReferenceDate)
Put ( SELECT ReferenceDate FROM SecondDateFilter WHERE SecondDateFilter.ReferenceDate = ResultTable.ReferenceDate ) in the SELECT ... FROM ResultTable
Comment out StartDateTable subquery (no longer filtering on that table, but otherwise it works again)
Move StartDateTable subquery to the first WITH

The last solution actually fixes my problem with this query (technically not the same, but the underlying business logic checks out that the result will always be the same), but I was wondering if there was a general problem with the EXISTS clause (potentially only in Oracle 12.2?) that I should be aware of. I have a lot more queries that make use of it.

Below is a test script that duplicates the error. The query below returns 2 rows as expected, but removing the commented line gives 5 rows.

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

As per Jonathan’s comments over on Twitter, the suggested workaround is to use the unnest hint in the outer exists subquery, as the issue is due to a bug (potentially Bug 28319114).

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

Advertisement

Answer