| Bug #116034 | Unexpected behavior when subquery returns an empty set | ||
|---|---|---|---|
| Submitted: | 8 Sep 2024 16:54 | Modified: | 9 Sep 2024 6:56 |
| Reporter: | Long Gu | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.4.2, 8.0.39, 9.0.1 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[8 Sep 2024 23:24]
MySQL Verification Team
mysql> select @@version;
+------------------+
| @@version |
+------------------+
| 8.4.0-commercial |
+------------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE t0 (c0 FLOAT(15, 15) PRIMARY KEY);
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> INSERT INTO t0 VALUES (0.52907);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT 1
-> FROM t0
-> WHERE t0.c0 > ALL (SELECT ALL t0.c0
-> FROM t0
-> WHERE NOT ( t0.c0 BETWEEN t0.c0 AND t0.c0 ));
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
[8 Sep 2024 23:27]
MySQL Verification Team
mysql> select @@version;
+------------------+
| @@version |
+------------------+
| 9.0.1-commercial |
+------------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE t0 (c0 FLOAT(15, 15) PRIMARY KEY);
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> INSERT INTO t0 VALUES (0.52907);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT 1
-> FROM t0
-> WHERE t0.c0 > ALL (SELECT ALL t0.c0
-> FROM t0
-> WHERE NOT ( t0.c0 BETWEEN t0.c0 AND t0.c0 ));
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
[9 Sep 2024 2:30]
Long Gu
Sorry, I missed a statement. To reproduce this, you also need to execute `SET SESSION sql_buffer_result=ON` before the SELECT query;
[9 Sep 2024 6:56]
MySQL Verification Team
Hello Long Gu, Thank you for the report and feedback. Verified as described. regards, Umesh
Description: Dear MySQL developers, I used my fuzzer to test MySQL and found a logic bug that made the MySQL server output unexpected results. How to repeat: ```sql CREATE TABLE t0 (c0 FLOAT(15, 15) PRIMARY KEY); INSERT INTO t0 VALUES (0.52907); SELECT 1 FROM t0 WHERE t0.c0 > ALL (SELECT ALL t0.c0 FROM t0 WHERE NOT ( t0.c0 BETWEEN t0.c0 AND t0.c0 )); ``` The actual result of this SELECT query is an empty set. However, in the subquery within the SELECT, the predicate `NOT (t0.c0 BETWEEN t0.c0 AND t0.c0)` is obviously FALSE, making the subquery result an empty set. Since `t0.c0 > ALL (empty set)` should evaluate to TRUE, the expected result of the SELECT query is 1. I also found that if I replace `NOT (t0.c0 BETWEEN t0.c0 AND t0.c0)` directly with FALSE in the subquery, the SELECT query returns 1.