Wrong result returns when Subquery is unnested

A couple of days ago I got a request to take a look at one SQL query, which obviously returns the wrong result.

The database version is 12.2 running on Windows on Azure Cloud.

Simplified for the demo purpose it looks like this – the query returns 1 and 2, but it should return 1, 2 and 3:

WITH sub1 AS
  ( SELECT 1 AS val FROM dual
    UNION ALL
    SELECT 2 AS val FROM dual
    UNION ALL
    SELECT 3 AS val FROM dual
  ),
  sub2 AS
  ( SELECT 1 AS val FROM dual
    UNION ALL
    SELECT 2 AS val FROM dual
  )
SELECT  sub1.*
FROM sub1
WHERE ( SELECT COUNT(DISTINCT sub2.val) FROM sub2 WHERE sub2.val = sub1.val ) < 10;

       VAL
----------
         1
         2

I really didn’t lose too much time on this – after a couple of minutes searching on My Oracle Support I stumbled on a couple of possible bugs related to this case. One of them is:

BUG 30593046 – WRONG RESULT RETURNS WHEN SUBQUERY IS UNNESTED

This bug is still investigating by the development team. I noticed, there is no issue in the 19.5 release, and didn’t tested it in another releases.

You can find more details in this note on the My Oracle Support homepage:

Wrong Result Returned When Subquery is Unnested by _fix_control=7215982:ON (Doc ID 2623275.1)

Oracle Support provides 3 workarounds for this issue:

• Use NO_UNNEST hint
• alter session set “_fix_control“=’7215982:off‘;
• alter session set  “_optimizer_unnest_all_subqueries“=false;

The NO_UNNEST hint works perfectly in this case and query returns now the correct result:

WITH sub1 AS
( SELECT 1 AS val FROM dual
  UNION ALL
  SELECT 2 AS val FROM dual
  UNION ALL
  SELECT 3 AS val FROM dual
),
sub2 AS
( SELECT 1 AS val FROM dual
  UNION ALL
  SELECT 2 AS val FROM dual
)
SELECT sub1.*
FROM sub1
WHERE ( SELECT /*+ NO_UNNEST */ COUNT(DISTINCT sub2.val) FROM sub2 WHERE sub2.val = sub1.val ) < 10;

   VAL
------
     1
     2
     3

So, please run this simple query in your database to check if you’re affected by this bug.