Bug #103233 wrong results when comparing to null
Submitted: 7 Apr 6:58 Modified: 7 Apr 7:31
Reporter: river fang Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.7.33 OS:CentOS
Assigned to: CPU Architecture:Any
Tags: null

[7 Apr 6:58] river fang
Description:
create table I ( col_int_not_null int not null);

insert into I values (9),(9),(9),(9),(9),(9),(9),(9),(9),(2),(3);

SELECT col_int_not_null, table1 . `col_int_not_null` >= ALL (select null union select 9 ) AS field6 FROM `I` AS table1 where col_int_not_null=9 order by 2,1;

How to repeat:
mysql> desc SELECT col_int_not_null, table1 . `col_int_not_null` >= ALL (select null union select 9 ) AS field6 FROM `I` AS table1 where col_int_not_null=9 order by 2,1;
+----+--------------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------+
| id | select_type        | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                        |
+----+--------------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------+
|  1 | PRIMARY            | table1     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   67 |    10.00 | Using where; Using temporary; Using filesort |
|  2 | DEPENDENT SUBQUERY | NULL       | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used                               |
|  3 | DEPENDENT UNION    | NULL       | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used                               |
| NULL | UNION RESULT       | <union2,3> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary                              |
+----+--------------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------+
4 rows in set, 1 warning (0.04 sec)

mysql>  SELECT col_int_not_null, table1 . `col_int_not_null` >= ALL (select null union select 9 ) AS field6 FROM `I` AS table1 where col_int_not_null=9 order by 2,1;
+------------------+--------+
| col_int_not_null | field6 |
+------------------+--------+
|                9 |      0 |
|                9 |      0 |
|                9 |      0 |
|                9 |      0 |
|                9 |      0 |
|                9 |      0 |
|                9 |      0 |
|                9 |      0 |
+------------------+--------+
8 rows in set (0.04 sec)

mysql>  SELECT col_int_not_null, table1 . `col_int_not_null` >= ALL (select null union select 9 ) AS field6 FROM `I` AS table1 where col_int_not_null=9 ;
+------------------+--------+
| col_int_not_null | field6 |
+------------------+--------+
|                9 |   NULL |
|                9 |   NULL |
|                9 |   NULL |
|                9 |   NULL |
|                9 |   NULL |
|                9 |   NULL |
|                9 |   NULL |
|                9 |   NULL |
+------------------+--------+
8 rows in set (0.04 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.33-log |
+------------+
1 row in set (0.05 sec)
[7 Apr 7:00] river fang
the query is right if remove the order by clause.
[7 Apr 7:31] MySQL Verification Team
Hello river fang,

Thank you for the report and test case.
Observed that 5.7.33 is affected.

regards,
Umesh