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)
