[#MDEV-28841] Wrong result upon LEFT JOIN and NULL comparison with not_null_range

Possibly related to ~~MDEV-21781~~

CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;

INSERT INTO t1 VALUES  (10,1),(null,2);

CREATE TABLE t2 (pk INT PRIMARY KEY) ENGINE=MyISAM;

INSERT INTO t2 VALUES (1),(2);

SELECT t1.b FROM t1 LEFT JOIN t2 ON t1.a = t2.pk WHERE t1.a IS NULL ORDER BY t1.b;

SET optimizer_switch= 'not_null_range_scan=on';

SELECT t1.b FROM t1 LEFT JOIN t2 ON t1.a = t2.pk WHERE t1.a IS NULL ORDER BY t1.b;

DROP TABLE t1, t2;

Without not_null_range_scan the query returns a result set (the expected result). With not_null_range_scan it returns an empty set (wrong).

10.5 a9d0bb12
SELECT t1.b FROM t1 LEFT JOIN t2 ON t1.a = t2.pk WHERE t1.a IS NULL ORDER BY t1.b;
b
2
SET optimizer_switch= 'not_null_range_scan=on';
SELECT t1.b FROM t1 LEFT JOIN t2 ON t1.a = t2.pk WHERE t1.a IS NULL ORDER BY t1.b;
b
DROP TABLE t1, t2;

Same with the plans:

explain extended SELECT t1.b FROM t1 LEFT JOIN t2 ON t1.a = t2.pk WHERE t1.a IS NULL ORDER BY t1.b;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using filesort

Warnings:

Note	1003	select `test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` is null order by `test`.`t1`.`b`

SELECT t1.b FROM t1 LEFT JOIN t2 ON t1.a = t2.pk WHERE t1.a IS NULL ORDER BY t1.b;

SET optimizer_switch= 'not_null_range_scan=on';

SELECT t1.b FROM t1 LEFT JOIN t2 ON t1.a = t2.pk WHERE t1.a IS NULL ORDER BY t1.b;

explain extended SELECT t1.b FROM t1 LEFT JOIN t2 ON t1.a = t2.pk WHERE t1.a IS NULL ORDER BY t1.b;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables

Warnings:

Note	1003	select `test`.`t1`.`b` AS `b` from `test`.`t1` where 0 order by `test`.`t1`.`b`

[MDEV-28841] Wrong result upon LEFT JOIN and NULL comparison with not_null_range_scan Created: 2022-06-14 Updated: 2022-06-27 Resolved: 2022-06-16
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.5, 10.6, 10.7, 10.8, 10.9
Fix Version/s:	10.5.17, 10.6.9, 10.7.5, 10.8.4, 10.9.2