Description:
A query using an IN subquery involving CURRENT_USER() and a BIT column may evaluate inconsistently between WHERE filtering and direct SELECT expression evaluation when a UNIQUE prefix index is present.
The issue appears only when the table contains at least 3 rows and disappears if the UNIQUE(c2, c1(1)) constraint is removed.
In this case, direct SELECT expression evaluation indicates that the predicate evaluates to FALSE for all rows, while the same predicate in the WHERE clause incorrectly evaluates to TRUE and returns all rows from the table.
How to repeat:
CREATE TABLE t381 (c1 TINYBLOB, c2 BIT, UNIQUE (c2, c1(1)));
INSERT INTO t381 (c1,c2) VALUES ('MCR4js',b'1');
INSERT INTO t381 (c1,c2) VALUES ('f3eVDg1FgSirtO',b'1');
INSERT INTO t381 (c1,c2) VALUES ('gFMKqFu',b'0');
SELECT t381.c2 FROM t381 WHERE ((CURRENT_USER() IN (SELECT c2 FROM t381)) IS FALSE);
-- return 3 rows
SELECT SUM(count) FROM (SELECT ((CURRENT_USER() IN (SELECT c2 FROM t381)) IS FALSE) IS TRUE AS count FROM t381) AS ta_norec;
-- return 0
Description: A query using an IN subquery involving CURRENT_USER() and a BIT column may evaluate inconsistently between WHERE filtering and direct SELECT expression evaluation when a UNIQUE prefix index is present. The issue appears only when the table contains at least 3 rows and disappears if the UNIQUE(c2, c1(1)) constraint is removed. In this case, direct SELECT expression evaluation indicates that the predicate evaluates to FALSE for all rows, while the same predicate in the WHERE clause incorrectly evaluates to TRUE and returns all rows from the table. How to repeat: CREATE TABLE t381 (c1 TINYBLOB, c2 BIT, UNIQUE (c2, c1(1))); INSERT INTO t381 (c1,c2) VALUES ('MCR4js',b'1'); INSERT INTO t381 (c1,c2) VALUES ('f3eVDg1FgSirtO',b'1'); INSERT INTO t381 (c1,c2) VALUES ('gFMKqFu',b'0'); SELECT t381.c2 FROM t381 WHERE ((CURRENT_USER() IN (SELECT c2 FROM t381)) IS FALSE); -- return 3 rows SELECT SUM(count) FROM (SELECT ((CURRENT_USER() IN (SELECT c2 FROM t381)) IS FALSE) IS TRUE AS count FROM t381) AS ta_norec; -- return 0