Bug #119146 WHERE IN with Subquery Incorrectly Matches VARCHAR to DECIMAL via Implicit Casting

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

[13 Oct 12:53] zz z

Description:
A SELECT statement using a WHERE (VARCHAR) IN (SELECT DECIMAL) clause produces an incorrect match. The issue is caused by a loose implicit type conversion where the string value is truncated to its leading numeric part for the comparison, leading to an unexpected and erroneous match.

How to repeat:
CREATE TABLE t294 (c1 DECIMAL(10,0), c2 VARCHAR(20));
INSERT INTO t294 (c1,c2) VALUES (8,'IszK');
INSERT INTO t294 (c1,c2) VALUES (-55,'8IZTmBLteEBSxDP9KDia');
INSERT INTO t294 (c1,c2) VALUES (716,'h');
INSERT INTO t294 (c1,c2) VALUES (-44,'d');
SELECT c2 FROM t294 WHERE (c2) IN (SELECT c1 FROM  t294);
-- return 8
SELECT SUM(count) FROM (SELECT ((c2) IN (SELECT c1 FROM  t294)) IS TRUE AS count FROM t294) AS ta_norec;
-- return 0