From 192427e37d2b066f9f681cc1b998b2efdc407c55 Mon Sep 17 00:00:00 2001 From: Monty Date: Wed, 15 Feb 2023 13:56:33 +0200 Subject: MDEV-30333 Wrong result with not_null_range_scan and LEFT JOIN with empty table There was a bug in JOIN::make_notnull_conds_for_range_scans() when clearing TABLE->tmp_set, which was used to mark fields that could not be null. This function was only used if 'not_null_range_scan=on' is set. The effect was that tmp_set contained a 'random value' and this caused the optimizer to think that some fields could not be null. FLUSH TABLES clears tmp_set and because of this things worked temporarily. Fixed by clearing tmp_set properly. --- mysql-test/main/empty_table.result | 58 ++++++++++++++++++++++++++++++++++++++ mysql-test/main/empty_table.test | 36 ++++++++++++++++++++++- sql/sql_select.cc | 6 ++-- 3 files changed, 95 insertions(+), 5 deletions(-) diff --git a/mysql-test/main/empty_table.result b/mysql-test/main/empty_table.result index 2bca3e792fa..90aec2eda3b 100644 --- a/mysql-test/main/empty_table.result +++ b/mysql-test/main/empty_table.result @@ -16,3 +16,61 @@ ERROR 42S02: Table 'test.t2' doesn't exist show status like "Empty_queries"; Variable_name Value Empty_queries 2 +# End of 4.1 tests +# +# MDEV-30333 Wrong result with not_null_range_scan and LEFT JOIN with empty table +# +set @save_optimizer_switch=@@optimizer_switch; +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'; +explain extended SELECT b FROM t1 LEFT JOIN t2 ON t2.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 t2.c = a WHERE a IS NULL ORDER BY b; +b +1 +2 +SET optimizer_switch = 'not_null_range_scan=on'; +explain extended SELECT b FROM t1 LEFT JOIN t2 ON t2.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 t2.c = a WHERE a IS NULL ORDER BY b; +b +1 +2 +flush tables; +SELECT b FROM t1 LEFT JOIN t2 ON t2.c = a WHERE a IS NULL ORDER BY b; +b +1 +2 +drop table t1,t2; +# Second test in MDEV-30333 +CREATE TABLE t1 (a int, b varchar(10)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (69,'foo'),(71,'bar'); +CREATE TABLE t2 (c int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(2); +CREATE TABLE t3 (d int, e int, KEY(e)) ENGINE=MyISAM; +SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t3.e = t3.d ON 1; +a b c d e +69 foo 1 NULL NULL +71 bar 1 NULL NULL +69 foo 2 NULL NULL +71 bar 2 NULL NULL +SET optimizer_switch = 'not_null_range_scan=on'; +SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t3.e = t3.d ON 1; +a b c d e +69 foo 1 NULL NULL +71 bar 1 NULL NULL +69 foo 2 NULL NULL +71 bar 2 NULL NULL +DROP TABLE t1, t2, t3; +set @@optimizer_switch=@save_optimizer_switch; +End of 10.5 tests diff --git a/mysql-test/main/empty_table.test b/mysql-test/main/empty_table.test index 754671868ba..a17b0c897d5 100644 --- a/mysql-test/main/empty_table.test +++ b/mysql-test/main/empty_table.test @@ -21,4 +21,38 @@ drop table t1; select * from t2; show status like "Empty_queries"; -# End of 4.1 tests +--echo # End of 4.1 tests + +--echo # +--echo # MDEV-30333 Wrong result with not_null_range_scan and LEFT JOIN with empty table +--echo # + +set @save_optimizer_switch=@@optimizer_switch; +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 +explain extended SELECT b FROM t1 LEFT JOIN t2 ON t2.c = a WHERE a IS NULL ORDER BY b; +SELECT b FROM t1 LEFT JOIN t2 ON t2.c = a WHERE a IS NULL ORDER BY b; +SET optimizer_switch = 'not_null_range_scan=on'; +explain extended SELECT b FROM t1 LEFT JOIN t2 ON t2.c = a WHERE a IS NULL ORDER BY b; +SELECT b FROM t1 LEFT JOIN t2 ON t2.c = a WHERE a IS NULL ORDER BY b; +flush tables; +SELECT b FROM t1 LEFT JOIN t2 ON t2.c = a WHERE a IS NULL ORDER BY b; +drop table t1,t2; + +--echo # Second test in MDEV-30333 + +CREATE TABLE t1 (a int, b varchar(10)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (69,'foo'),(71,'bar'); +CREATE TABLE t2 (c int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(2); +CREATE TABLE t3 (d int, e int, KEY(e)) ENGINE=MyISAM; +SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t3.e = t3.d ON 1; +SET optimizer_switch = 'not_null_range_scan=on'; +SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t3.e = t3.d ON 1; +DROP TABLE t1, t2, t3; +set @@optimizer_switch=@save_optimizer_switch; + +--echo End of 10.5 tests + diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 42711270f60..c28f104804f 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -29683,7 +29683,6 @@ void JOIN::make_notnull_conds_for_range_scans() { DBUG_ENTER("JOIN::make_notnull_conds_for_range_scans"); - if (impossible_where || !optimizer_flag(thd, OPTIMIZER_SWITCH_NOT_NULL_RANGE_SCAN)) { @@ -29769,7 +29768,6 @@ bool build_notnull_conds_for_range_scans(JOIN *join, Item *cond, table_map allowed) { THD *thd= join->thd; - DBUG_ENTER("build_notnull_conds_for_range_scans"); for (JOIN_TAB *s= join->join_tab; @@ -29777,13 +29775,13 @@ bool build_notnull_conds_for_range_scans(JOIN *join, Item *cond, { /* Clear all needed bitmaps to mark found fields */ if ((allowed & s->table->map) && - !(s->table->map && join->const_table_map)) + !(s->table->map & join->const_table_map)) bitmap_clear_all(&s->table->tmp_set); } /* Find all null-rejected fields assuming that cond is null-rejected and - only formulas over tables from 'allowed' are to be taken into account + only formulas over tables from 'allowed' are to be taken into account */ if (cond->find_not_null_fields(allowed)) DBUG_RETURN(true); -- cgit v1.2.1