Bug #119170 Inconsistent Result with string = SOME (DECIMAL)

• Submitted: 16 Oct 9:23
• Reporter: zz z
• Status: Open
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 9.4.0 8.4.6
• OS: Any
• CPU Architecture: Any

[16 Oct 9:23] zz z

Description:
A query produces inconsistent results between the optimized execution plan and the unoptimized execution path when a string literal is compared against a DECIMAL subquery using the = SOME operator. The optimizer correctly handles the implicit string-to-number cast, evaluating the condition to TRUE for all rows. However, the unoptimized path (used for internal counts) incorrectly evaluates the same condition as FALSE, resulting in a wrong row count.

How to repeat:
CREATE TABLE t7620 (c1 DECIMAL(10,0));
INSERT IGNORE INTO t7620 (c1) VALUES (4);
INSERT HIGH_PRIORITY IGNORE INTO t7620 (c1) VALUES (663);
INSERT INTO t7620 (c1) VALUES (44220986);
INSERT INTO t7620 (c1) VALUES (54453937);

SELECT c1 FROM t7620 WHERE (((('4CzqreZX38AD') = SOME (SELECT c1 FROM t7620)) IS TRUE));
-- Returned Row Count: 4

SELECT SUM(count) FROM (SELECT ((((('4CzqreZX38AD') = SOME (SELECT c1 FROM t7620)) IS TRUE))) IS TRUE AS count FROM t7620) AS ta_norec;
--Returned Row Count: 0