Bug #120493 Wrong result for SOME quantified comparison in WHERE clause

• Submitted: 18 May 14:26
• Modified: 19 May 19:52
• Reporter: ss w
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[18 May 14:26] ss w

Description:
When a query uses a SOME quantified comparison predicate between values from different column types, the predicate may evaluate inconsistently between WHERE filtering and SELECT expression evaluation.

In this case, the WHERE clause incorrectly returns an empty result set, while evaluating the same predicate as a SELECT expression indicates that one row satisfies the condition. 

How to repeat:
CREATE TABLE t982 (c1 BLOB, c2 TINYINT UNSIGNED);
INSERT INTO t982 (c1,c2) VALUES ('9F5VCYBCfWuHGKa0hp1e',9);
INSERT INTO t982 (c1,c2) VALUES ('C6yXnu2pJz',32);
SELECT c2 FROM t982 WHERE ( c2 <= SOME (SELECT c1 FROM t982));
-- return null
SELECT SUM(count) FROM (SELECT ( c2 <= SOME (SELECT c1 FROM t982)) IS TRUE AS count FROM t982) AS ta_norec;
-- return 1

[19 May 19:52] Roy Lyseng

Thank you for the bug report.
Verified as described.