Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.0, 13.1
Description
A NOT IN subquery whose result contains a NULL returns rows. A NULL in the subquery makes every comparison UNKNOWN, so the predicate can never be TRUE and no row qualifies. MySQL returns no rows for both testcases below, on every version tested.
The wrong answer appears only once the subquery is materialized. With four rows in the outer table the plan is MATERIALIZED and the row is returned. With three rows the plan is DEPENDENT SUBQUERY and the answer is correct. Setting materialization=off, or turning off both partial_match_rowid_merge and partial_match_table_scan, also gives the correct answer, so the defect is in the partial-matching path for a materialized NOT IN.
The row that comes back is always one whose value lies outside the range of the subquery column's type. 9999 against a TINYINT column reproduces it, and a value the TINYINT can hold does not. With no NULL in the subquery the same query is answered correctly, so the range check is only wrong in the presence of a NULL. The bug is engine-independent: it reproduces on InnoDB, MyISAM, Aria and MEMORY.
Both the single-column and the two-column form are affected.
CLI Testcase, single column:
CREATE TABLE t1 (c1 TINYINT); |
INSERT INTO t1 VALUES(21),(NULL); |
CREATE TABLE t2 (c1 INT); |
INSERT INTO t2 VALUES(1),(2),(3),(9999); |
SELECT * FROM t2 WHERE c1 NOT IN (SELECT c1 FROM t1); |
CLI Testcase, two columns:
CREATE TABLE t1 (c1 TINYINT, c2 TINYINT); |
INSERT INTO t1 VALUES(21,21),(NULL,NULL); |
CREATE TABLE t2 (c1 INT, c2 INT); |
INSERT INTO t2 VALUES(1,1),(2,2),(3,3),(9999,9999); |
SELECT * FROM t2 WHERE (c1,c2) NOT IN (SELECT c1,c2 FROM t1); |
MTR Testcase, both forms:
CREATE TABLE t1 (c1 TINYINT); |
INSERT INTO t1 VALUES(21),(NULL); |
CREATE TABLE t2 (c1 INT); |
INSERT INTO t2 VALUES(1),(2),(3),(9999); |
SELECT COUNT(*) INTO @n FROM t2 WHERE c1 NOT IN (SELECT c1 FROM t1); |
--let $n= query_get_value(SELECT @n AS n, n, 1)
|
if ($n != 0) { |
--die Single column: NOT IN with a NULL in the subquery returned $n row(s), expected 0
|
}
|
CREATE TABLE t3 (c1 TINYINT, c2 TINYINT); |
INSERT INTO t3 VALUES(21,21),(NULL,NULL); |
CREATE TABLE t4 (c1 INT, c2 INT); |
INSERT INTO t4 VALUES(1,1),(2,2),(3,3),(9999,9999); |
SELECT COUNT(*) INTO @m FROM t4 WHERE (c1,c2) NOT IN (SELECT c1,c2 FROM t3); |
--let $m= query_get_value(SELECT @m AS m, m, 1)
|
if ($m != 0) { |
--die Two columns: NOT IN with a NULL row in the subquery returned $m row(s), expected 0
|
}
|
DROP TABLE t1, t2, t3, t4; |
The MTR testcase is a reverse gate: it fails while the bug is present and passes once the result is correct.
Leads to, single column:
|
CS 13.1.0 da18481158c81ca94689702073c3e04aad85a6a3 (Optimized) |
c1
|
9999
|
Leads to, two columns:
|
CS 13.1.0 da18481158c81ca94689702073c3e04aad85a6a3 (Optimized) |
c1 c2
|
9999 9999
|
Expected in both cases, and what every MySQL build returns: no rows.
|
EXPLAIN, four rows in t2, wrong result |
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where
|
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2
|
|
EXPLAIN, three rows in t2, correct result |
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Using where
|
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
|
|
Optimizer switch results, four-row testcase |
optimizer_switch Rows returned Correct
|
default 1 no
|
materialization=off 0 yes
|
partial_match_rowid_merge=off,partial_match_table_scan=off 0 yes
|
|
Bug Detection Matrix |
Rel o/d Build Commit Diff observed
|
CS 10.11 dbg 180826 8f00e6caca633c783140db86d3a48a96de67cf38 DIFF: RESULT
|
CS 10.11 opt 180826 8f00e6caca633c783140db86d3a48a96de67cf38 DIFF: RESULT
|
CS 11.4 dbg 180826 1a052f27e374fc9b4cb5b1fd098cf10f0e1381cc DIFF: RESULT
|
CS 11.4 opt 180826 1a052f27e374fc9b4cb5b1fd098cf10f0e1381cc DIFF: RESULT
|
CS 11.8 dbg 180826 d26f9ab217a7fcf9d4eccc62c01020ff275ff42e DIFF: RESULT
|
CS 11.8 opt 180826 d26f9ab217a7fcf9d4eccc62c01020ff275ff42e DIFF: RESULT
|
CS 12.3 dbg 180826 add63991988734383c5e942de2a19c6c45f511f7 DIFF: RESULT
|
CS 12.3 opt 180826 add63991988734383c5e942de2a19c6c45f511f7 DIFF: RESULT
|
CS 13.0 dbg 180826 a848493c6fe031f23606144420c1ca1e467cbd81 DIFF: RESULT
|
CS 13.0 opt 180826 a848493c6fe031f23606144420c1ca1e467cbd81 DIFF: RESULT
|
CS 13.1 dbg 180826 da18481158c81ca94689702073c3e04aad85a6a3 DIFF: RESULT
|
CS 13.1 opt 180826 da18481158c81ca94689702073c3e04aad85a6a3 DIFF: RESULT
|
ES 10.6 dbg 180826 fcecb2620f25965723d640decede7c018bcb1dcc DIFF: RESULT
|
ES 10.6 opt 180826 fcecb2620f25965723d640decede7c018bcb1dcc DIFF: RESULT
|
ES 11.4 dbg 180826 3b34189bfe675c18c4ced3ef531d016ea74c76f4 DIFF: RESULT
|
ES 11.4 opt 180826 3b34189bfe675c18c4ced3ef531d016ea74c76f4 DIFF: RESULT
|
ES 11.8 dbg 180826 4694e931d10fecf733c34f83ea2146d31b708eb3 DIFF: RESULT
|
ES 11.8 opt 180826 4694e931d10fecf733c34f83ea2146d31b708eb3 DIFF: RESULT
|
ES 12.3 dbg 180826 f513f503feacabfb219d4a6f965b5f72b86d4db0 DIFF: RESULT
|
ES 12.3 opt 180826 f513f503feacabfb219d4a6f965b5f72b86d4db0 DIFF: RESULT
|
MS 5.5 dbg 070123 bac287c315b1792e7ae33f91add6a60292f9bae8 No diff found
|
MS 5.5 opt 070123 bac287c315b1792e7ae33f91add6a60292f9bae8 No diff found
|
MS 5.6 dbg 070123 dab95781a1244104d6b87020ac2fc4d190ba2946 No diff found
|
MS 5.6 opt 070123 dab95781a1244104d6b87020ac2fc4d190ba2946 No diff found
|
MS 5.7 dbg 070525 f7680e98b6bbe3500399fbad465d08a6b75d7a5c No diff found
|
MS 5.7 opt 070525 f7680e98b6bbe3500399fbad465d08a6b75d7a5c No diff found
|
MS 8.0 dbg 060224 49ef33f7edadef3ae04665e73d1babd40179a4f1 No diff found
|
MS 8.0 opt 060224 49ef33f7edadef3ae04665e73d1babd40179a4f1 No diff found
|
MS 9.1 dbg 211024 61a3a1d8ef15512396b4c2af46e922a19bf2b174 No diff found
|
MS 9.1 opt 211024 61a3a1d8ef15512396b4c2af46e922a19bf2b174 BASE/SOURCE
|
MDEV-3509 and MDEV-3033 are the closest earlier bugs. Both are two-column NOT IN under partial matching, and both dropped rows that should have qualified. Their own testcases return the correct result on 13.1.0, so this is not a re-emergence of either. This defect runs the other way, keeps a row that cannot qualify, needs no non-default optimizer_switch, and reproduces under the switch settings those two used.
