MySQL Bugs: #119146: WHERE IN with Subquery Incorrectly Matches VARCHAR to DECIMAL via Implicit Casting

Bug #119146	WHERE IN with Subquery Incorrectly Matches VARCHAR to DECIMAL via Implicit Casting
Submitted:	13 Oct 2025 12:53	Modified:	13 Jan 9:48
Reporter:	zz z	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	9.4.0 8.4.6	OS:	Any
Assigned to:		CPU Architecture:	Any

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

Thank you for the bug report.
Verified as described.