Possible Oracle-Bug with SubQueries and Group Functions

Question

can anybody explain me why the following Query returns two Rows and not only one?

SELECT *
  FROM (SELECT 'ASDF' c1, MAX (SUM (1)) c2
          FROM DUAL
         GROUP BY dummy
        UNION
        SELECT 'JKLÖ' c1, 1 c2
          FROM DUAL)
 WHERE c1 != 'ASDF';

--another Version with the same wrong result:
SELECT *
  FROM (SELECT 1 c1, MAX (SUM (1)) c2
          FROM DUAL
         GROUP BY dummy
        UNION all
        SELECT 2 c1, 1 c2
          FROM DUAL)
 WHERE c1 != 1;

Is it correct that Oracle delivers two rows? In my opinion the Row with c1 = ASDF should not be in the result.

Here is a Screenshot of the result from the first query:

I have tested it on the following Versions, always with the same result:

• Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
• Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Answer 1

No this is not a bug. Aggregate functions are the reason why you see this unexpected result. Here is how it works. SUM() function as well as MAX() function will return NULL(producing 1 row) if there is no rows returned by the query. When your query is executed optimizer applies predicate pushing transformation and your original query becomes(will not post the entire trace, only transformed query):

SELECT "from$_subquery$_001"."C1" "C1",
       "from$_subquery$_001"."C2"  "C2" 
   FROM  ( 
           (SELECT 'ASDF' "C1",MAX(SUM(1)) "C2" 
              FROM "SYS"."DUAL" "DUAL" 
             WHERE 'ASDF'<>'ASDF'       [1]-- predicate pushed into the view 
             GROUP BY "DUAL"."DUMMY" )
             UNION 
            (SELECT 'JKLÖ' "C1",
                  1 "C2" 
              FROM "SYS"."DUAL" "DUAL" 
             WHERE 'JKLÖ'<>'ASDF')) "from$_subquery$_001"

[1] Because of predicate pushing your fist sub-query returns no rows and when an aggregate function(except count and few others), MAX or SUM or even both as in this case used on empty result set NULL will be returned - 1 row + 1 row return by the second sub-query thus producing 2 rows result set you are looking at.

Here is simple demonstration:

create table empty_table (c1 varchar2(1));

select 'aa' literal, nvl(max(c1), 'NULL') as res
  from empty_table

LITERAL RES 
------- ----
aa      NULL

1 row selected.

Answer 2

It definitely looks like a bug.

I don't really know how to read explain plans, but here it is. It seems to me the predicate has been pushed to only one of the UNION members and it has been transformed into "NULL IS NOT NULL" which is totally weird.

Note that the strings could be changed to 'a' and 'b' (so we don't use special characters), UNION and UNION ALL produce the same bug, and the bug seems to be triggered by the MAX(SUM(1)) in the first branch; simply replacing that with NULL or anything else that's "simple", or even with SUM(1) (without the MAX) causes the query to work correctly.

ADDED: Strangely, if I change MAX(SUM(1)) to either MAX(1) or SUM(1), or if I simply change it to the literal number 1, the query works correctly - but the Explain Plan still shows the same weird predicate, "NULL IS NOT NULL". So, it seems the problem is that the predicate is not pushed to both branches of the union, not the predicate transformation. (And even that doesn't explain why c2 appears as NULL in the extra row in the result set.) MORE ADDED (see Comments below) - as it turns out, the predicate IS pushed to both branches of the UNION, and this is exactly what causes the problem (as Nicholas explains in his answer).

Plan hash value: 1682090214

-------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     2 |    32 |     2   (0)| 00:00:01 |
|   1 |  VIEW                  |      |     2 |    32 |     2   (0)| 00:00:01 |
|   2 |   UNION-ALL            |      |       |       |            |          |
|   3 |    SORT AGGREGATE      |      |     1 |     2 |            |          |
|   4 |     HASH GROUP BY      |      |     1 |     2 |            |          |
|*  5 |      FILTER            |      |       |       |            |          |
|   6 |       TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
|   7 |    FAST DUAL           |      |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter(NULL IS NOT NULL)

Answer 3

A much simpler example results in the same error:

 SELECT 'ASDF' c1, MAX (SUM (1)) c2
    FROM DUAL where 'ASDF' <> 'ASDF'
GROUP BY dummy

I must confess that I am totally confused. Why doesn't the filter eliminate the record, thereby eliminating any result set?