Wrong result with NULL NOT IN subquery
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Won't Fix
|
Low
|
Timour Katchaounov |
Bug Description
In the following test case the two subqueries should produce the same
empty result, however, the first one produces all rows of the outer table,
while the second query correctly doesn't return any rows.
The only difference in the two queries is LEFT vs INNER join in the
subquery, however the two subqueries produce the same result if
run separately. The only difference in the results of the subqueries
is the nullability of the result column. This is shown in the two tables
t2_inr, and t2_outr below.
drop table if exists t1;
CREATE TABLE t1 (
pk INT PRIMARY KEY,
int_key INT,
varchar_key VARCHAR(5) UNIQUE
);
INSERT INTO t1 VALUES (9, 7,NULL), (10,8,'p');
-- Wrong result - should be empty
EXPLAIN
SELECT * FROM t1
WHERE NULL NOT IN (
SELECT INNR.pk FROM t1 AS INNR2 JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key)
WHERE INNR.varchar_key > 'n{');
SELECT * FROM t1
WHERE NULL NOT IN (
SELECT INNR.pk FROM t1 AS INNR2 JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key)
WHERE INNR.varchar_key > 'n{');
-- Correct empty result
EXPLAIN
SELECT * FROM t1
WHERE NULL NOT IN (
SELECT INNR.pk FROM t1 AS INNR2 LEFT JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key)
WHERE INNR.varchar_key > 'n{');
SELECT * FROM t1
WHERE NULL NOT IN (
SELECT INNR.pk FROM t1 AS INNR2 LEFT JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key)
WHERE INNR.varchar_key > 'n{');
-- Verify that the result from the two subqueries is the same.
SELECT INNR.pk FROM t1 AS INNR2 JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key)
WHERE INNR.varchar_key > 'n{';
SELECT INNR.pk FROM t1 AS INNR2 LEFT JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key)
WHERE INNR.varchar_key > 'n{';
create table t2_inr as
SELECT INNR.pk FROM t1 AS INNR2 JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key)
WHERE INNR.varchar_key > 'n{';
create table t2_outr as
SELECT INNR.pk FROM t1 AS INNR2 LEFT JOIN t1 AS INNR ON (INNR2.int_key = INNR.int_key)
WHERE INNR.varchar_key > 'n{';
explain t2_inr;
explain t2_outr;
Changed in maria: | |
assignee: | nobody → Timour Katchaounov (timour) |
status: | New → Confirmed |
importance: | Undecided → Low |
The problem was discovered when analyzing a result failure in record_ seq in handler.h line 1444
the 5.3-mwl89 tree in the test file subselect_jcl6, test case:
Bug #37894: Assertion in init_read_