Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.4
Description
A correlated subquery misses a row when it reads t1 by ref_or_null on a two-part key where each key part has an IS NULL alternative.
CREATE TABLE t1 (a INT, b INT, KEY(a,b)) ENGINE=InnoDB; |
INSERT INTO t1 VALUES (NULL,2); |
CREATE TABLE t2 (x INT) ENGINE=InnoDB; |
INSERT INTO t2 VALUES (2); |
|
|
SELECT (SELECT COUNT(*) FROM t2 AS i, t1 |
WHERE (t1.a = 1 OR t1.a IS NULL) AND |
(t1.b = o.x OR t1.b IS NULL) AND |
t1.b = i.x) AS c |
FROM t2 AS o; |
|
|
SELECT (SELECT COUNT(*) FROM t2 AS i, t1 IGNORE INDEX(a) |
WHERE (t1.a = 1 OR t1.a IS NULL) AND |
(t1.b = o.x OR t1.b IS NULL) AND |
t1.b = i.x) AS c |
FROM t2 AS o; |
|
|
DROP TABLE t1, t2; |
|
The first SELECT returns 0 while the second returns 1. The correct result is 1, because the only row of t1, (NULL,2), satisfies all three predicates.
On MySQL 9.6.0, the scenario returns 1 for both SELECTs.