Description:
A NOT IN subquery operating on a BIT column produces an incorrect empty result.
The query executed directly against the original table returns an empty set. However, after materializing the grouped data into another table using CREATE TABLE ... AS SELECT, an equivalent NOT IN query correctly returns the row where c1 = 0.
The subquery:
SELECT c1 FROM t0 WHERE c1;
returns only rows where c1 = 1. Therefore, rows where c1 = 0 should satisfy:
c1 NOT IN (1)
No NULL values are involved.
How to repeat:
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
CREATE TABLE t0 (
c0 TINYTEXT,
c1 BIT
);
INSERT INTO t0 (c0, c1) VALUES ('a', 0);
INSERT INTO t0 (c0, c1) VALUES ('b', 0);
INSERT INTO t0 (c0, c1) VALUES ('c', 1);
INSERT INTO t0 (c0, c1) VALUES ('d', 1);
CREATE TABLE t1 AS (
SELECT VAR_POP(c0) AS c0, c1 AS c1
FROM t0
GROUP BY c1
);
SELECT VAR_POP(c0), c1
FROM t0
WHERE c1 NOT IN (
SELECT c1 FROM t0 WHERE c1
)
GROUP BY c1;
SELECT c0, c1
FROM t1
WHERE c1 NOT IN (
SELECT c1 FROM t1 WHERE c1
);
Actual result
The first query incorrectly returns an empty result:
Empty set
The second query returns:
+------+------+
| c0 | c1 |
+------+------+
| 0 | 0x00 |
+------+------+
Expected result
The first query should also return one grouped row for c1 = 0:
+-------------+------+
| VAR_POP(c0) | c1 |
+-------------+------+
| 0 | 0x00 |
+-------------+------+
Description: A NOT IN subquery operating on a BIT column produces an incorrect empty result. The query executed directly against the original table returns an empty set. However, after materializing the grouped data into another table using CREATE TABLE ... AS SELECT, an equivalent NOT IN query correctly returns the row where c1 = 0. The subquery: SELECT c1 FROM t0 WHERE c1; returns only rows where c1 = 1. Therefore, rows where c1 = 0 should satisfy: c1 NOT IN (1) No NULL values are involved. How to repeat: DROP DATABASE IF EXISTS test; CREATE DATABASE test; USE test; CREATE TABLE t0 ( c0 TINYTEXT, c1 BIT ); INSERT INTO t0 (c0, c1) VALUES ('a', 0); INSERT INTO t0 (c0, c1) VALUES ('b', 0); INSERT INTO t0 (c0, c1) VALUES ('c', 1); INSERT INTO t0 (c0, c1) VALUES ('d', 1); CREATE TABLE t1 AS ( SELECT VAR_POP(c0) AS c0, c1 AS c1 FROM t0 GROUP BY c1 ); SELECT VAR_POP(c0), c1 FROM t0 WHERE c1 NOT IN ( SELECT c1 FROM t0 WHERE c1 ) GROUP BY c1; SELECT c0, c1 FROM t1 WHERE c1 NOT IN ( SELECT c1 FROM t1 WHERE c1 ); Actual result The first query incorrectly returns an empty result: Empty set The second query returns: +------+------+ | c0 | c1 | +------+------+ | 0 | 0x00 | +------+------+ Expected result The first query should also return one grouped row for c1 = 0: +-------------+------+ | VAR_POP(c0) | c1 | +-------------+------+ | 0 | 0x00 | +-------------+------+