Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
12.0.2
-
None
-
None
Description
The query optimizer incorrectly evaluates a WHERE condition involving < ALL when the subquery selects from a BIGINT UNSIGNED column. The condition is wrongly reduced to a constant TRUE (WHERE 1), causing the query to return all rows instead of the expected empty set.
SHOW WARNINGS after EXPLAIN EXTENDED confirms the faulty rewrite. The optimizer should evaluate 95 < ALL (SELECT c4 FROM t4) as 95 < MIN(c4). Since MIN(c4) is 12 in the test case, the condition should be FALSE. The bug seems to be in the comparison logic for the BIGINT UNSIGNED type.
This issue was first mentioned in the comments of MDEV-37655. However, because that ticket appears to focus on string-to-numeric context issues and this bug is specific to BIGINT UNSIGNED, I am filing a new, separate report for clearer tracking.
|
|
MariaDB [test]> CREATE TABLE t4 (c4 BIGINT UNSIGNED);
|
Query OK, 0 rows affected (0.012 sec)
|
|
MariaDB [test]> INSERT t4 () VALUES (12);
|
Query OK, 1 row affected (0.006 sec)
|
|
MariaDB [test]> INSERT t4 () VALUES (9223372036854775808);
|
Query OK, 1 row affected (0.002 sec)
|
|
MariaDB [test]> SELECT * FROM t4 WHERE (95 < ALL (SELECT c4 FROM t4));
|
+---------------------+
|
| c4 |
|
+---------------------+
|
| 12 |
|
| 9223372036854775808 |
|
+---------------------+
|
2 rows in set (0.001 sec)
|
|
MariaDB [test]> 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.001 sec)
|
|
|
|
MariaDB [test]> explain extended SELECT * FROM t4 WHERE (95 < ALL (SELECT c4 FROM t4));
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
|
| 1 | PRIMARY | t4 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | |
|
| 2 | SUBQUERY | t4 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
|
2 rows in set, 1 warning (0.001 sec)
|
|
MariaDB [test]> show warnings;
|
+-------+------+-------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-------------------------------------------------------------------------+
|
| Note | 1003 | /* select#1 */ select `test`.`t4`.`c4` AS `c4` from `test`.`t4` where 1 |
|
+-------+------+-------------------------------------------------------------------------+
|
1 row in set (0.000 sec)
|