revision-id: f7943bde6a9df2025d811731d4ca439d0694de12 (mariadb-10.5.0-172-gf7943bd) parent(s): f79f537f9facb5a050697ad3dbf6e4a617c3c128 author: Igor Babaev committer: Igor Babaev timestamp: 2020-02-10 21:19:28 -0800 message: MDEV-21683 Server crashes in get_quick_keys with not_null_range_scan ANding of the range built from inferred NOT NULL conditions and the range built from other conditions used in WHERE/ON clauses may produce an IMPOSSIBLE range. The code of MDEV-15777 did not take into account this possibility. --- mysql-test/main/range.result | 20 +++++++++++++++++++- mysql-test/main/range.test | 24 +++++++++++++++++++++++- mysql-test/main/range_mrr_icp.result | 20 +++++++++++++++++++- sql/opt_range.cc | 8 +++++--- 4 files changed, 66 insertions(+), 6 deletions(-) diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index 7f03a7c..eb7f995 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -3116,7 +3116,6 @@ drop table t1,ten,t2; # # MDEV-15777: Use inferred IS NOT NULL predicates in the range optimizer # -set @save_optimizer_switch= @@optimizer_switch; set @@optimizer_switch='not_null_range_scan=on'; create table ten(a int); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -3404,6 +3403,25 @@ drop table t1,t2; drop table ten,one_k; set @@optimizer_switch= @save_optimizer_switch; # +# MDEV-21683: ANDing of the range from inferred NOT NULL condition and +# the range from other conditions produces IMPOSSIBLE range +# +SET @save_optimizer_switch= @@optimizer_switch; +CREATE TABLE t1 (a INT, KEY(a)); +INSERT INTO t1 VALUES (8),(9); +CREATE TABLE t2 (pk INT, b INT, PRIMARY KEY(pk)); +INSERT INTO t2 VALUES (1,1),(2,2); +SET optimizer_switch = 'not_null_range_scan=on'; +EXPLAIN EXTENDED SELECT * FROM t1 LEFT JOIN t2 ON a = pk WHERE b >= 0 AND pk IS NULL; +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 NULL AS `a`,0 AS `pk`,NULL AS `b` from `test`.`t1` join `test`.`t2` where 0 +SELECT * FROM t1 LEFT JOIN t2 ON a = pk WHERE b >= 0 AND pk IS NULL; +a pk b +DROP TABLE t1, t2; +SET @@optimizer_switch= @save_optimizer_switch; +# # End of 10.5 tests # set global innodb_stats_persistent= @innodb_stats_persistent_save; diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test index ce2d2e7..11212fb 100644 --- a/mysql-test/main/range.test +++ b/mysql-test/main/range.test @@ -2106,7 +2106,6 @@ drop table t1,ten,t2; --echo # MDEV-15777: Use inferred IS NOT NULL predicates in the range optimizer --echo # -set @save_optimizer_switch= @@optimizer_switch; set @@optimizer_switch='not_null_range_scan=on'; create table ten(a int); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -2281,6 +2280,29 @@ drop table ten,one_k; set @@optimizer_switch= @save_optimizer_switch; --echo # +--echo # MDEV-21683: ANDing of the range from inferred NOT NULL condition and +--echo # the range from other conditions produces IMPOSSIBLE range +--echo # + +SET @save_optimizer_switch= @@optimizer_switch; + +CREATE TABLE t1 (a INT, KEY(a)); +INSERT INTO t1 VALUES (8),(9); + +CREATE TABLE t2 (pk INT, b INT, PRIMARY KEY(pk)); +INSERT INTO t2 VALUES (1,1),(2,2); + +SET optimizer_switch = 'not_null_range_scan=on'; +let $q= +SELECT * FROM t1 LEFT JOIN t2 ON a = pk WHERE b >= 0 AND pk IS NULL; +eval EXPLAIN EXTENDED $q; +eval $q; + +DROP TABLE t1, t2; + +SET @@optimizer_switch= @save_optimizer_switch; + +--echo # --echo # End of 10.5 tests --echo # diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result index b698ec9..baec486 100644 --- a/mysql-test/main/range_mrr_icp.result +++ b/mysql-test/main/range_mrr_icp.result @@ -3113,7 +3113,6 @@ drop table t1,ten,t2; # # MDEV-15777: Use inferred IS NOT NULL predicates in the range optimizer # -set @save_optimizer_switch= @@optimizer_switch; set @@optimizer_switch='not_null_range_scan=on'; create table ten(a int); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -3401,6 +3400,25 @@ drop table t1,t2; drop table ten,one_k; set @@optimizer_switch= @save_optimizer_switch; # +# MDEV-21683: ANDing of the range from inferred NOT NULL condition and +# the range from other conditions produces IMPOSSIBLE range +# +SET @save_optimizer_switch= @@optimizer_switch; +CREATE TABLE t1 (a INT, KEY(a)); +INSERT INTO t1 VALUES (8),(9); +CREATE TABLE t2 (pk INT, b INT, PRIMARY KEY(pk)); +INSERT INTO t2 VALUES (1,1),(2,2); +SET optimizer_switch = 'not_null_range_scan=on'; +EXPLAIN EXTENDED SELECT * FROM t1 LEFT JOIN t2 ON a = pk WHERE b >= 0 AND pk IS NULL; +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 NULL AS `a`,0 AS `pk`,NULL AS `b` from `test`.`t1` join `test`.`t2` where 0 +SELECT * FROM t1 LEFT JOIN t2 ON a = pk WHERE b >= 0 AND pk IS NULL; +a pk b +DROP TABLE t1, t2; +SET @@optimizer_switch= @save_optimizer_switch; +# # End of 10.5 tests # set global innodb_stats_persistent= @innodb_stats_persistent_save; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index e13065d..b4e2cfd 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -2838,12 +2838,15 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, if (notnull_cond) notnull_cond_tree= notnull_cond->get_mm_tree(¶m, ¬null_cond); - if (cond) + if (cond || notnull_cond_tree) { { Json_writer_array trace_range_summary(thd, "setup_range_conditions"); - tree= cond->get_mm_tree(¶m, &cond); + if (cond) + tree= cond->get_mm_tree(¶m, &cond); + if (notnull_cond_tree) + tree= tree_and(¶m, tree, notnull_cond_tree); } if (tree) { @@ -2865,7 +2868,6 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, } } } - tree= tree_and(¶m, tree, notnull_cond_tree); /* Try to construct a QUICK_GROUP_MIN_MAX_SELECT.