MySQL Bugs: #65902: Incorrect Semantics for "> ALL" when NULL in sub query

Bug #65902	Incorrect Semantics for "> ALL" when NULL in sub query
Submitted:	15 Jul 2012 16:27	Modified:	30 Sep 2012 11:03
Reporter:	Martin Smith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1.48, 5.5.26	OS:	Any
Assigned to:	Norvald Ryeng	CPU Architecture:	Any
Tags:	ALL, null

Description:
The predicate "X > ALL(SELECT C FROM T)" seems to be incorrectly optimised to something like "X > (SELECT MAX(C) FROM T)".

This is not a valid transformation if the sub query is on nullable columns. 

See http://stackoverflow.com/q/11462716/73226 if further details are required.

How to repeat:
CREATE TABLE t2
  (
     id INT
  );

INSERT INTO t2
VALUES      (0),
            (NULL),
            (1);

/*Returns row with 10 - This is incorrect*/
SELECT *
FROM   (SELECT 10 AS id) T
WHERE  id > ALL (SELECT id
                 FROM   t2);

/*Returns no rows. Explain Plan says "Impossible Where". This is correct*/
SELECT *
FROM   (SELECT 10 AS id) T
WHERE  id > ALL (SELECT 0
                 UNION ALL
                 SELECT NULL
                 UNION ALL
                 SELECT 1); 

Suggested fix:
Handle NULL values correctly.

Thank you for the bug report. Verified with 5.5.26 on Mac OS X also:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.5.26-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE t2
    ->   (
    ->      id INT
    ->   );
Query OK, 0 rows affected (0.05 sec)

mysql> 
mysql> INSERT INTO t2
    -> VALUES      (0),
    ->             (NULL),
    ->             (1);
Query OK, 3 rows affected (0.09 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t2;
+------+
| id   |
+------+
|    0 |
| NULL |
|    1 |
+------+
3 rows in set (0.00 sec)

mysql> SELECT *
    -> FROM   (SELECT 10 AS id) T
    -> WHERE  id > ALL (SELECT id
    ->                  FROM   t2);
+----+
| id |
+----+
| 10 |
+----+
1 row in set (0.23 sec)

mysql> explain extended SELECT * FROM   (SELECT 10 AS id) T WHERE  id > ALL (SELECT id                  FROM   t2);
+----+--------------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                               |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
|  1 | PRIMARY            | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE noticed after reading const tables |
|  3 | DEPENDENT SUBQUERY | t2    | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where                                         |
|  2 | DERIVED            | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used                                      |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
3 rows in set, 1 warning (0.06 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select '10' AS `id` from dual where <not>(<in_optimizer>('10',<exists>(select 1 from `test`.`t2` where ((<cache>('10') <= `test`.`t2`.`id`) or isnull(`test`.`t2`.`id`)) having <is_not_null_test>(`test`.`t2`.`id`))))
1 row in set (0.03 sec)

mysql> SELECT *
    -> FROM   (SELECT 10 AS id) T
    -> WHERE  id > ALL (SELECT 0
    ->                  UNION ALL
    ->                  SELECT NULL
    ->                  UNION ALL
    ->                  SELECT 1); 
Empty set (0.03 sec)

mysql> explain extended SELECT * FROM   (SELECT 10 AS id) T WHERE  id > ALL (SELECT 0                  UNION ALL                  SELECT NULL                  UNION ALL                  SELECT 1);
+----+--------------------+--------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
| id | select_type        | table        | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                               |
+----+--------------------+--------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
|  1 | PRIMARY            | NULL         | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE noticed after reading const tables |
|  3 | DEPENDENT SUBQUERY | NULL         | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used                                      |
|  4 | DEPENDENT UNION    | NULL         | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used                                      |
|  5 | DEPENDENT UNION    | NULL         | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used                                      |
| NULL | UNION RESULT       | <union3,4,5> | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL |                                                     |
|  2 | DERIVED            | NULL         | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used                                      |
+----+--------------------+--------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select '10' AS `id` from dual where <not>(<in_optimizer>('10',<exists>(select 0 having (<cache>('10') <= <ref_null_helper>(0)) union all select NULL having (<cache>('10') <= <ref_null_helper>(NULL)) union all select 1 having (<cache>('10') <= <ref_null_helper>(1)))))
1 row in set (0.00 sec)

Noted in 5.6.7, 5.7.0 changelogs.

Queries with ALL over a UNION could return an incorrect result if the
UNION result contained NULL.

Why is this closed? 

Is it resolved? If so in what version?

The comment posted seems completely irrelevant to the stated issue. The Query is about ALL <comparison_operator>, nothing to do with UNION ALL.

@Martin

Did you notice "Noted in 5.6.7, 5.7.0 changelogs".  What means that the fix will be available in 5.6.7 (next 5.6x version) as well as in the next major tree (5.7x). And will not be NOT be fixed in 5.1x and 5.5x trees.

Peter
(not a MySQL/Oracle person)

OK thanks. 

Still don't see what UNION has to do with it though. The repro doesn't use UNION at all, did you close it? 

Have you confirmed it is definitely fixed in these versions?

@Martin .. I would advice you to change status from 'Closed' to 'Open' in the dropdown (if you can).  If status is 'Closed', probably nobody at Oracle sees your comments.

Peter: No need to re-open the bug, apparently. I got e-mails with your comments even if it was closed.

Martin: I'm sorry about the confusion. Paul posted the changelog entry, as we usually do when closing a bug, but it looks a bit confusing since the bug we fixed was actually a regression on your example of a correct query execution.

We've made an effort to clean up ALL/ANY processing recently, and this was one of several bug reports involved. When this report was opened, the bug you reported had already been fixed, but our fixes had lead to a regression on your example of a correct query execution (the query with UNION). We fixed the regression and you got the confusing closing comment.

Just to be sure, I re-tested with both your repro case and the example of a correct execution:

CREATE TABLE t2
(
id INT
);
INSERT INTO t2
VALUES      (0),
(NULL),
(1);
/*Returns row with 10 - This is incorrect*/
SELECT *
FROM   (SELECT 10 AS id) T
WHERE  id > ALL (SELECT id
FROM   t2);
id
/*Returns no rows. Explain Plan says "Impossible Where". This is correct*/
SELECT *
FROM   (SELECT 10 AS id) T
WHERE  id > ALL (SELECT 0
UNION ALL
SELECT NULL
UNION ALL
SELECT 1);
id
DROP TABLE t2;

So the bug you reported has been fixed, and so has the regression on the UNION query.

Ah I see. Thanks for the explanation!