diff options
| -rw-r--r-- | mysql-test/main/rowid_filter.result | 33 | ||||
| -rw-r--r-- | mysql-test/main/rowid_filter.test | 31 | ||||
| -rw-r--r-- | mysql-test/main/rowid_filter_innodb.result | 33 | ||||
| -rw-r--r-- | sql/sql_select.cc | 8 |
4 files changed, 105 insertions, 0 deletions
diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result index ea7de8c5db8..f0253055065 100644 --- a/mysql-test/main/rowid_filter.result +++ b/mysql-test/main/rowid_filter.result @@ -1941,4 +1941,37 @@ ALTER TABLE orders DROP COLUMN o_totaldiscount; DROP VIEW v1; DROP DATABASE dbt3_s001; use test; +# +# MDEV-18816: potential range filter for one join table with +# impossible WHERE for another +# +create table t1 ( +pk int not null primary key, c2 varchar(10) , i1 int,key (c2) +) engine=myisam; +insert into t1 values (1,'a',-5),(2,'a',null); +create table t2 ( +pk int, i1 int, c1 varchar(30) , key c1 (c1(30)), key i1 (i1) +) engine=myisam; +insert into t2 values +(1,-5,'a'),(2,null,'a'),(3,null,'a'),(4,null,'a'),(5,5,'a'),(6,null,'a'), +(7,4,'a'),(8,55,'a'),(9,null,'a'),(10,null,'a'),(11,null,'a'),(12,-5,'a'), +(13,-5,'a'),(14,null,'a'),(15,null,'a'),(16,-5,'a'),(17,-5,'a'); +select 1 +from t1 +left join +t2 join t1 as t1_a on t2.i1 = t1_a.pk +on t1.c2 = t2.c1 +where t1_a.pk is null and t1_a.i1 != 3; +1 +explain extended select 1 +from t1 +left join +t2 join t1 as t1_a on t2.i1 = t1_a.pk +on t1.c2 = t2.c1 +where t1_a.pk is null and t1_a.i1 != 3; +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 1 AS `1` from `test`.`t1` join `test`.`t2` join `test`.`t1` `t1_a` where 0 +drop table t1,t2; set @@use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/main/rowid_filter.test b/mysql-test/main/rowid_filter.test index a67402170e8..87e8b2c10dd 100644 --- a/mysql-test/main/rowid_filter.test +++ b/mysql-test/main/rowid_filter.test @@ -230,4 +230,35 @@ DROP DATABASE dbt3_s001; use test; +--echo # +--echo # MDEV-18816: potential range filter for one join table with +--echo # impossible WHERE for another +--echo # + +create table t1 ( + pk int not null primary key, c2 varchar(10) , i1 int,key (c2) +) engine=myisam; +insert into t1 values (1,'a',-5),(2,'a',null); + +create table t2 ( + pk int, i1 int, c1 varchar(30) , key c1 (c1(30)), key i1 (i1) +) engine=myisam; +insert into t2 values + (1,-5,'a'),(2,null,'a'),(3,null,'a'),(4,null,'a'),(5,5,'a'),(6,null,'a'), + (7,4,'a'),(8,55,'a'),(9,null,'a'),(10,null,'a'),(11,null,'a'),(12,-5,'a'), + (13,-5,'a'),(14,null,'a'),(15,null,'a'),(16,-5,'a'),(17,-5,'a'); + +let $q= +select 1 + from t1 + left join + t2 join t1 as t1_a on t2.i1 = t1_a.pk + on t1.c2 = t2.c1 +where t1_a.pk is null and t1_a.i1 != 3; + +eval $q; +eval explain extended $q; + +drop table t1,t2; + set @@use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result index cd09f1d25a6..ac7ca119512 100644 --- a/mysql-test/main/rowid_filter_innodb.result +++ b/mysql-test/main/rowid_filter_innodb.result @@ -1870,6 +1870,39 @@ ALTER TABLE orders DROP COLUMN o_totaldiscount; DROP VIEW v1; DROP DATABASE dbt3_s001; use test; +# +# MDEV-18816: potential range filter for one join table with +# impossible WHERE for another +# +create table t1 ( +pk int not null primary key, c2 varchar(10) , i1 int,key (c2) +) engine=myisam; +insert into t1 values (1,'a',-5),(2,'a',null); +create table t2 ( +pk int, i1 int, c1 varchar(30) , key c1 (c1(30)), key i1 (i1) +) engine=myisam; +insert into t2 values +(1,-5,'a'),(2,null,'a'),(3,null,'a'),(4,null,'a'),(5,5,'a'),(6,null,'a'), +(7,4,'a'),(8,55,'a'),(9,null,'a'),(10,null,'a'),(11,null,'a'),(12,-5,'a'), +(13,-5,'a'),(14,null,'a'),(15,null,'a'),(16,-5,'a'),(17,-5,'a'); +select 1 +from t1 +left join +t2 join t1 as t1_a on t2.i1 = t1_a.pk +on t1.c2 = t2.c1 +where t1_a.pk is null and t1_a.i1 != 3; +1 +explain extended select 1 +from t1 +left join +t2 join t1 as t1_a on t2.i1 = t1_a.pk +on t1.c2 = t2.c1 +where t1_a.pk is null and t1_a.i1 != 3; +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 1 AS `1` from `test`.`t1` join `test`.`t2` join `test`.`t1` `t1_a` where 0 +drop table t1,t2; set @@use_stat_tables=@save_use_stat_tables; # # MDEV-18755: possible RORI-plan and possible plan with range filter diff --git a/sql/sql_select.cc b/sql/sql_select.cc index cbf136b9b8b..b5b69a8f6c0 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1581,6 +1581,13 @@ bool JOIN::make_range_rowid_filters() { DBUG_ENTER("make_range_rowid_filters"); + /* + Do not build range filters with detected impossible WHERE. + Anyway conditions cannot be used anymore to extract ranges for filters. + */ + if (const_table_map != found_const_table_map) + DBUG_RETURN(0); + JOIN_TAB *tab; for (tab= first_linear_tab(this, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES); @@ -7051,6 +7058,7 @@ void set_position(JOIN *join,uint idx,JOIN_TAB *table,KEYUSE *key) // join->positions[idx].loosescan_key= MAX_KEY; /* Not a LooseScan */ join->positions[idx].sj_strategy= SJ_OPT_NONE; join->positions[idx].use_join_buffer= FALSE; + join->positions[idx].range_rowid_filter_info= 0; /* Move the const table as down as possible in best_ref */ JOIN_TAB **pos=join->best_ref+idx+1; |