Wrong result with NULL NOT IN subquery

[Timour Katchaounov]

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;

[Timour Katchaounov (timour)]

The problem was discovered when analyzing a result failure in
the 5.3-mwl89 tree in the test file subselect_jcl6, test case:
  Bug #37894: Assertion in init_read_record_seq in handler.h line 1444

[Philip Stoev (pstoev-askmonty)]

Still repeatable with maria-5.3 with semijoin=off,materialization=off,subquery_cache=off.

Repeatable in maria-5.3, maria-5.2, mysql-5.5

[Timour Katchaounov (timour)]

The wrong result is not present in 5.3-mwl89. The relevant difference between
5.3 and 5.3-mwl89 is that MWL#89 doesn't evaluate subqueries during the
optimization phase, and thus doesn't perform constant optimization for
subqueries. Based on this difference, it doesn't make sense to fix the bug
in 5.3.

Explain in 5.3:
+----+--------------------+-------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+---------------+------+---------+------+------+--------------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | |
| 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
+----+--------------------+-------+------+---------------+------+---------+------+------+--------------------------------+
2

Explain in 5.3-mwl89:
+----+--------------------+-------+-------+---------------------+--------------------+---------+-------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-------+---------------------+--------------------+---------+-------+------+------------------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | |
| 2 | DEPENDENT SUBQUERY | INNR2 | ALL | NULL | NULL | NULL | NULL | 2 | |
| 2 | DEPENDENT SUBQUERY | INNR | const | PRIMARY,varchar_key | PRIMARYvarchar_key | 48 | const | 2 | Using where; Full scan on NULL key |
+----+--------------------+-------+-------+---------------------+--------------------+---------+-------+------+------------------------------------+