| Bug #117606 | Incorrect query result | ||
|---|---|---|---|
| Submitted: | 28 Feb 16:01 | Modified: | 28 Feb 16:26 |
| Reporter: | John Jove | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 8.4.4, 8.0.41 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[28 Feb 16:26]
MySQL Verification Team
Hello John, Thank you for the report and test case. regards, Umesh
Description: I run the following case, and an incorrect result is returned. I got the following query plan for further reference. id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 null ALL null null null null 6 100.0 null 1 PRIMARY t2 null ALL null null null null 6 100.0 Using where; Using join buffer (hash join) 4 DEPENDENT SUBQUERY t2 null unique_subquery PRIMARY PRIMARY 18 func 1 100.0 Using where 3 SUBQUERY t2 null ALL null null null null 6 100.0 null How to repeat: CREATE TABLE t1 (c1 LONGTEXT, PRIMARY KEY (c1(4))) ENGINE = InnoDB; INSERT INTO t1 (c1) VALUES ('3'); INSERT INTO t1 (c1) VALUES ('a'); SELECT c1 FROM t1 WHERE ((0) >= SOME (SELECT c1 AS ca7 FROM t1)) != 1; -- actual: {3, a}, expected: {}