Description:
When an ALL quantified comparison involves implicit type conversion from BLOB values to numeric values, the predicate may evaluate inconsistently between WHERE filtering and SELECT expression evaluation.
In this case, the WHERE clause returns rows even though evaluating the same predicate directly as a SELECT expression shows that the condition evaluates to FALSE for all rows. This demonstrates an inconsistency between WHERE filtering and SELECT expression evaluation.
How to repeat:
CREATE TABLE t288 (c1 BLOB, c2 CHAR(20));
INSERT INTO t288 (c1,c2) VALUES ('NJrFiLjhUYtFEcSfgzv','qglQYq');
INSERT INTO t288 (c1,c2) VALUES ('2O3pDJ55VAjDK','vVp6x1psaNxQ6wWxmmHb');
SELECT t288.c2 FROM t288 WHERE (0 >= ALL (SELECT t288.c1 FROM t288));
-- 2 rows
SELECT SUM(count) FROM (SELECT (0 >= ALL (SELECT t288.c1 FROM t288)) IS TRUE AS count FROM t288 ) AS ta_norec;
-- 0
Description: When an ALL quantified comparison involves implicit type conversion from BLOB values to numeric values, the predicate may evaluate inconsistently between WHERE filtering and SELECT expression evaluation. In this case, the WHERE clause returns rows even though evaluating the same predicate directly as a SELECT expression shows that the condition evaluates to FALSE for all rows. This demonstrates an inconsistency between WHERE filtering and SELECT expression evaluation. How to repeat: CREATE TABLE t288 (c1 BLOB, c2 CHAR(20)); INSERT INTO t288 (c1,c2) VALUES ('NJrFiLjhUYtFEcSfgzv','qglQYq'); INSERT INTO t288 (c1,c2) VALUES ('2O3pDJ55VAjDK','vVp6x1psaNxQ6wWxmmHb'); SELECT t288.c2 FROM t288 WHERE (0 >= ALL (SELECT t288.c1 FROM t288)); -- 2 rows SELECT SUM(count) FROM (SELECT (0 >= ALL (SELECT t288.c1 FROM t288)) IS TRUE AS count FROM t288 ) AS ta_norec; -- 0