Bug #119366 Incorrect != ALL Subquery Evaluation with NULL on ARCHIVE Engine

• Submitted: 12 Nov 3:51
• Modified: 12 Nov 4:42
• Reporter: zz z
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 9.4.0
• OS: Any
• CPU Architecture: Any

[12 Nov 3:51] zz z

Description:
The boolean logic of an != ALL subquery is evaluated incorrectly when it reads data from a table using the ARCHIVE storage engine and its result set contains a NULL value. According to SQL's three-valued logic, the comparison X != ALL ({Y, NULL}) should result in UNKNOWN (NULL). However, the expression is incorrectly evaluated as TRUE. 

How to repeat:
CREATE TABLE t2322 (c2 BIT) ENGINE ARCHIVE;
INSERT t2322 () VALUES (b'0');
INSERT t2322 () VALUES (b'0');
INSERT t2322 () VALUES ();

SELECT * FROM t2322 WHERE (2002 != ALL (SELECT t2322.c2 FROM t2322));
-- empty set

SELECT SUM(count) FROM (SELECT ((2002 != ALL (SELECT t2322.c2 FROM t2322))) IS TRUE AS count FROM t2322) AS ta_norec;
-- 3

[12 Nov 3:53] zz z

MEMORY OR MyISAM can also trigger the bug

[12 Nov 4:42] zz z

sorry,it is the same as Bug #119349