Bug #119138 Incorrect result with BINARY EXISTS(...) IN (...) on TIMESTAMP column

• Submitted: 13 Oct 3:45
• 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 3:45] zz z

Description:
When a TIMESTAMP column is used in an IN clause, and the value being checked is derived from BINARY EXISTS(...), the database's implicit type conversion mechanism incorrectly evaluates the condition. Specifically, BINARY EXISTS(...) results in '\x01' (a byte string representing 1), and when compared against a TIMESTAMP value of '0000-00-00 00:00:00' (which is often treated numerically as 0), the condition 1 IN (0) is unexpectedly evaluated as TRUE.

How to repeat:
drop TABLE IF EXISTS t145;
CREATE TABLE t145 (c1 TIMESTAMP UNIQUE, c2 DATE);
INSERT IGNORE INTO t145 (c1,c2) VALUES ('0000-00-00 00:00:00','2011-06-09');
SELECT c1 FROM t145 WHERE (((BINARY EXISTS (SELECT c2 FROM t145)) IN (SELECT c1 FROM t145)));
-- return  0000-00-00 00:00:00
SELECT SUM(count) FROM (SELECT ((((BINARY EXISTS (SELECT c2 FROM t145)) IN (SELECT c1 FROM t145)))) IS TRUE AS count FROM t145 )AS ta_norec;
-- return 1