Bug #120273 Incorrect COUNT result when "SET SESSION internal_tmp_mem_storage_engine = MEMORY" is used

• Submitted: 15 Apr 15:39
• Modified: 16 Apr 22:33
• Reporter: jinhui lai
• Status: Verified
• Category: MySQL Server: Memory storage engine
• Severity: S2 (Serious)
• Version: 8.0.45
• OS: Ubuntu (22.04)
• CPU Architecture: x86

[15 Apr 15:39] jinhui lai

Description:
Hi, I found a logic bug in MySQL 9.6.0.
A query that should have returned {0} actually returned {1}.

How to repeat:
CREATE TABLE t0(c0 FLOAT UNIQUE);
CREATE TABLE t1 LIKE t0;
DROP INDEX c0 ON t0;
SET SESSION internal_tmp_mem_storage_engine = MEMORY;
INSERT IGNORE INTO t0(c0) VALUES(3), (2), (1), (0), ('0');
INSERT IGNORE INTO t1(c0) VALUES(1), ('0'), (NULL), (NULL), (NULL);
SELECT COUNT(CASE WHEN EXISTS (SELECT 1 FROM t0 WHERE (-('0') = ((1 XOR t1.c0) IS NOT TRUE)) AND t1.c0 = t0.c0) THEN 1 END) FROM t1  WHERE NOT EXISTS (SELECT 1 FROM t0 WHERE (-('0')  = ((1 XOR t1.c0) IS NOT TRUE)) AND t1.c0 = t0.c0); -- expect:{0}; actually return {1}
-- When you exit the current session, open a new one, and execute the same query again, the result becomes 0 (the correct expected value).

[16 Apr 22:33] Roy Lyseng

Thank you for the bug report.
Verified as described.