Bug #119958 EXISTS subquery result behaves inconsistently based on source table

• Submitted: 28 Feb 13:09
• Modified: 2 Mar 13:02
• Reporter: Seren Zhou
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 8.0.45
• OS: Any
• CPU Architecture: Any

[28 Feb 13:09] Seren Zhou

Description:
When an `EXISTS` subquery is used in comparison with `ALL`/`ANY` quantifiers, the result differs based on the source table in the EXISTS subquery, even though `EXISTS` should always return a boolean value (TRUE/FALSE or 1/0)

How to repeat:
CREATE TABLE t63 (c1 DOUBLE, c2 BIT(14));
CREATE TABLE t64 (c1 MEDIUMTEXT NOT NULL);

INSERT INTO t63 VALUES (1.0, b'111'); 
INSERT INTO t64 (c1) VALUES ('4r4d');
INSERT INTO t64 (c1) VALUES ('poe_test');

mysql> SELECT ((EXISTS (SELECT c1 FROM t63)) >= ALL (SELECT c1 FROM t64)) AS result;
+--------+
| result |
+--------+
|      0 |
+--------+
1 row in set (0.001 sec)

mysql> SELECT ((EXISTS (SELECT c1 FROM t64)) >= ALL (SELECT c1 FROM t64)) AS result;
+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.001 sec)

[2 Mar 13:02] Roy Lyseng

Thank you for the bug report.
Verified as described.