Details
Critical Description
CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM; |
INSERT INTO t1 (b) VALUES (1),(2); |
|
|
CREATE TABLE t2 (c INT) ENGINE=MyISAM; |
|
|
SET optimizer_switch= 'not_null_range_scan=off'; # Default |
SELECT b FROM t1 LEFT JOIN t2 ON c = a WHERE a IS NULL ORDER BY b; |
|
|
SET optimizer_switch = 'not_null_range_scan=on'; |
SELECT b FROM t1 LEFT JOIN t2 ON c = a WHERE a IS NULL ORDER BY b; |
|
|
DROP TABLE t1, t2; |
|
10.5 b8f4b984 |
SET optimizer_switch= 'not_null_range_scan=off'; |
SELECT b FROM t1 LEFT JOIN t2 ON c = a WHERE a IS NULL ORDER BY b; |
b
|
1
|
2
|
SET optimizer_switch = 'not_null_range_scan=on'; |
SELECT b FROM t1 LEFT JOIN t2 ON c = a WHERE a IS NULL ORDER BY b; |
b
|
DROP TABLE t1, t2; |
The expected result is 2 rows.
When the test case is executed exactly this way (nothing else is done), it produces the wrong result as above, and the execution plan for the 2nd SELECT is
EXPLAIN EXTENDED SELECT b FROM t1 LEFT JOIN t2 ON c = a WHERE a IS NULL ORDER BY 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` |
However, important!
The outcome also depends on some other circumstances. For example, if we add FLUSH TABLES and re-execute the query, we can get a different (correct) result and a different plan:
CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM; |
INSERT INTO t1 (b) VALUES (1),(2); |
CREATE TABLE t2 (c INT) ENGINE=MyISAM; |
SET optimizer_switch= 'not_null_range_scan=off'; |
SELECT b FROM t1 LEFT JOIN t2 ON c = a WHERE a IS NULL ORDER BY b; |
b
|
1
|
2
|
SET optimizer_switch = 'not_null_range_scan=on'; |
SELECT b FROM t1 LEFT JOIN t2 ON c = a WHERE a IS NULL ORDER BY b; |
b
|
FLUSH TABLES;
|
EXPLAIN EXTENDED SELECT b FROM t1 LEFT JOIN t2 ON c = a WHERE a IS NULL ORDER BY b; |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 SIMPLE t2 system NULL NULL NULL NULL 0 0.00 Const row not found |
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 b FROM t1 LEFT JOIN t2 ON c = a WHERE a IS NULL ORDER BY b; |
b
|
1
|
2
|
Please be aware of this, as it may affect reproducibility and cause confusion (especially if you are running the test case in the client, not in MTR which does everything from scratch every time). Also, since I don't know the reasons of the non-determinism, it may show up in other forms, e.g. depending on the environment, settings or whatever.