Description:
A query result divergence occurs when evaluating an IN predicate containing functions returning NULL (BIT_COUNT(NULL) and LEAST(NULL, ...)) against a DECIMAL column, depending on whether a UNIQUE index exists on the table.
Specifically:
BIT_COUNT(NULL) and LEAST(NULL, 1914945208) both return NULL. Thus, c0 IN (NULL, NULL) for c0 = 0 should evaluate to NULL (treated as FALSE in WHERE clause) and return 0 rows.
On a table with a UNIQUE index (CREATE TABLE t0(c0 DECIMAL UNIQUE)), the query correctly returns Empty set.
On a table without a UNIQUE index (CREATE TABLE t0(c0 DECIMAL)), the query incorrectly returns 1 row (0). It appears that without an index, constant folding or expression evaluation erroneously coerces BIT_COUNT(NULL) or the IN list elements to 0.
How to repeat:
DROP DATABASE IF EXISTS database639;
CREATE DATABASE database639;
USE database639;
CREATE TABLE IF NOT EXISTS t0(c0 DECIMAL NULL UNIQUE KEY) ;
INSERT IGNORE INTO t0(c0) VALUES("枲|TXTa");
-- Query on original database
SELECT ALL t0.c0 FROM t0 WHERE (t0.c0) IN (BIT_COUNT(NULL), LEAST(NULL, 1914945208));
-- Raw database setup
DROP DATABASE IF EXISTS database639_raw;
CREATE DATABASE database639_raw;
USE database639_raw;
CREATE TABLE t0(c0 decimal(10,0));
INSERT INTO t0 SELECT * FROM database639.t0;
-- Query on raw database
SELECT ALL t0.c0 FROM t0 WHERE (t0.c0) IN (BIT_COUNT(NULL), LEAST(NULL, 1914945208));
Expected Result:
Both queries should return Empty set. 0 IN (NULL, NULL) should evaluate to NULL/FALSE regardless of index availability or execution plan.
Actual Result:
(with UNIQUE index): Returns Empty set.
(without index): Returns 1 row containing 0.
Description: A query result divergence occurs when evaluating an IN predicate containing functions returning NULL (BIT_COUNT(NULL) and LEAST(NULL, ...)) against a DECIMAL column, depending on whether a UNIQUE index exists on the table. Specifically: BIT_COUNT(NULL) and LEAST(NULL, 1914945208) both return NULL. Thus, c0 IN (NULL, NULL) for c0 = 0 should evaluate to NULL (treated as FALSE in WHERE clause) and return 0 rows. On a table with a UNIQUE index (CREATE TABLE t0(c0 DECIMAL UNIQUE)), the query correctly returns Empty set. On a table without a UNIQUE index (CREATE TABLE t0(c0 DECIMAL)), the query incorrectly returns 1 row (0). It appears that without an index, constant folding or expression evaluation erroneously coerces BIT_COUNT(NULL) or the IN list elements to 0. How to repeat: DROP DATABASE IF EXISTS database639; CREATE DATABASE database639; USE database639; CREATE TABLE IF NOT EXISTS t0(c0 DECIMAL NULL UNIQUE KEY) ; INSERT IGNORE INTO t0(c0) VALUES("枲|TXTa"); -- Query on original database SELECT ALL t0.c0 FROM t0 WHERE (t0.c0) IN (BIT_COUNT(NULL), LEAST(NULL, 1914945208)); -- Raw database setup DROP DATABASE IF EXISTS database639_raw; CREATE DATABASE database639_raw; USE database639_raw; CREATE TABLE t0(c0 decimal(10,0)); INSERT INTO t0 SELECT * FROM database639.t0; -- Query on raw database SELECT ALL t0.c0 FROM t0 WHERE (t0.c0) IN (BIT_COUNT(NULL), LEAST(NULL, 1914945208)); Expected Result: Both queries should return Empty set. 0 IN (NULL, NULL) should evaluate to NULL/FALSE regardless of index availability or execution plan. Actual Result: (with UNIQUE index): Returns Empty set. (without index): Returns 1 row containing 0.