MySQL Bugs: #119087: Incorrect result for subquery with BIGINT UNSIGNED

Bug #119087	Incorrect result for subquery with BIGINT UNSIGNED
Submitted:	28 Sep 5:54	Modified:	11 Oct 3:24
Reporter:	zz z	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	9.4.0 8.4.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When a WHERE clause contains a subquery of the form literal < ALL (SELECT column FROM ...) where the column is of type BIGINT UNSIGNED, the query optimizer incorrectly evaluates the condition to TRUE if the column contains both a value smaller than the literal and a value bigger than 9223372036854775808 (which is 2^63,within the range of big unsigned). This causes the query to return all rows incorrectly.

This bug is different from Bug #117606. That bug deals with issues arising from implicit string-to-number type conversions during comparisons.In contrast, the bug demonstrated here involves no type conversion whatsoever. 

How to repeat:
mysql> CREATE TABLE t4 (c4 BIGINT UNSIGNED);
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT t4 () VALUES (12);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT t4 () VALUES (9223372036854775808);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t4;
+---------------------+
| c4                  |
+---------------------+
|                  12 |
| 9223372036854775808 |
+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM t4 WHERE (95 < ALL (SELECT c4 FROM t4));
+---------------------+
| c4                  |
+---------------------+
|                  12 |
| 9223372036854775808 |
+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT SUM(count) FROM (SELECT ((95 < ALL (SELECT c4 FROM t4))) IS TRUE AS count FROM t4 ) AS ta_norec;
+------------+
| SUM(count) |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

mysql> explain SELECT * FROM t4 WHERE (95 < ALL (SELECT c4 FROM t4));
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | PRIMARY     | t4    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
|  2 | SUBQUERY    | t4    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings
    -> ;
+-------+------+----------------------------------------------------------------------------+
| Level | Code | Message                                                                    |
+-------+------+----------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `test`.`t4`.`c4` AS `c4` from `test`.`t4` where true |
+-------+------+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

Thanks for the report and the test case.