revision-id: 0198afed649faef8ba1a99b3ccb5b2601dd551c5 (mariadb-10.4.5-123-g0198afed649) parent(s): c6dff51276b4c0a1c14df32c5d96ab65c846baa6 author: Varun Gupta committer: Varun Gupta timestamp: 2019-07-07 03:44:33 +0530 message: MDEV-12867: Full scan despite appropriate index Also consider that select items that participated in conditions in the where clause and are constant can be considered for the MIN/MAX optimzation --- mysql-test/main/func_misc.result | 33 +++++++++++++++++++++++++++++++++ mysql-test/main/func_misc.test | 31 +++++++++++++++++++++++++++++++ sql/opt_sum.cc | 13 +++++++++++-- sql/sql_select.cc | 2 +- sql/sql_select.h | 3 ++- 5 files changed, 78 insertions(+), 4 deletions(-) diff --git a/mysql-test/main/func_misc.result b/mysql-test/main/func_misc.result index 1d284e45545..c2caa6a2fdd 100644 --- a/mysql-test/main/func_misc.result +++ b/mysql-test/main/func_misc.result @@ -1614,5 +1614,38 @@ a y DROP TABLE t1; # +# MDEV-12867: Full scan despite appropriate index +# +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2(a int); +insert into t2 select A.a + B.a*10 from t1 A, t1 B; +CREATE TABLE t3 ( +a int NOT NULL, +b int unsigned NOT NULL, +c int unsigned NOT NULL, +PRIMARY KEY (a,b,c) +); +insert into t3 (a,b,c) values (0,0,1); +insert into t3 (a,b,c) values (1,2,3); +insert into t3 (a,b,c) values (1,3,5); +insert into t3 (a,b,c) values (1,5,3); +insert into t3 (a,b,c) values (0,1,9); +explain +SELECT a, MIN(b) FROM t3 WHERE a = 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +SELECT a, MIN(b) FROM t3 WHERE a = 0; +a MIN(b) +0 0 +explain +SELECT MAX(b) FROM t3 WHERE a = 0 ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +SELECT a, MAX(b) FROM t3 WHERE a = 0; +a MAX(b) +0 1 +drop table t1,t2,t3; +# # End of 10.4 tests # diff --git a/mysql-test/main/func_misc.test b/mysql-test/main/func_misc.test index 331293a9c95..da9bf730690 100644 --- a/mysql-test/main/func_misc.test +++ b/mysql-test/main/func_misc.test @@ -1253,6 +1253,37 @@ $$ DELIMITER ;$$ DROP TABLE t1; +--echo # +--echo # MDEV-12867: Full scan despite appropriate index +--echo # + +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t2(a int); +insert into t2 select A.a + B.a*10 from t1 A, t1 B; + +CREATE TABLE t3 ( + a int NOT NULL, + b int unsigned NOT NULL, + c int unsigned NOT NULL, + PRIMARY KEY (a,b,c) +); + +insert into t3 (a,b,c) values (0,0,1); +insert into t3 (a,b,c) values (1,2,3); +insert into t3 (a,b,c) values (1,3,5); +insert into t3 (a,b,c) values (1,5,3); +insert into t3 (a,b,c) values (0,1,9); + +explain +SELECT a, MIN(b) FROM t3 WHERE a = 0; +SELECT a, MIN(b) FROM t3 WHERE a = 0; +explain +SELECT MAX(b) FROM t3 WHERE a = 0 ; +SELECT a, MAX(b) FROM t3 WHERE a = 0; +drop table t1,t2,t3; + --echo # --echo # End of 10.4 tests --echo # diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc index 0a3c30a176d..5d6169fb81f 100644 --- a/sql/opt_sum.cc +++ b/sql/opt_sum.cc @@ -239,7 +239,8 @@ static int get_index_max_value(TABLE *table, TABLE_REF *ref, uint range_fl) */ int opt_sum_query(THD *thd, - List<TABLE_LIST> &tables, List<Item> &all_fields, COND *conds) + List<TABLE_LIST> &tables, List<Item> &all_fields, COND *conds, + COND_EQUAL *cond_equal) { List_iterator_fast<Item> it(all_fields); List_iterator<TABLE_LIST> ti(tables); @@ -472,9 +473,17 @@ int opt_sum_query(THD *thd, } else if (const_result) { + /* + Added this call here to find if the item belong to a multiple equality and if yes + does that Multiple equality have a constant value for this item. + */ + Item *res= item->propagate_equal_fields(thd, + Value_source:: + Context_identity(), + cond_equal); if (recalc_const_item) item->update_used_tables(); - if (!item->const_item() && item->type() != Item::WINDOW_FUNC_ITEM) + if (!item->const_item() && res == item && item->type() != Item::WINDOW_FUNC_ITEM) const_result= 0; } } diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 23827898160..32bc6c8608f 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2103,7 +2103,7 @@ JOIN::optimize_inner() If all items were resolved by opt_sum_query, there is no need to open any tables. */ - if ((res=opt_sum_query(thd, select_lex->leaf_tables, all_fields, conds))) + if ((res=opt_sum_query(thd, select_lex->leaf_tables, all_fields, conds, cond_equal))) { DBUG_ASSERT(res >= 0); if (res == HA_ERR_KEY_NOT_FOUND) diff --git a/sql/sql_select.h b/sql/sql_select.h index b7f870bf797..a165f7dcc0f 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -1852,7 +1852,8 @@ bool is_indexed_agg_distinct(JOIN *join, List<Item_field> *out_args); /* functions from opt_sum.cc */ bool simple_pred(Item_func *func_item, Item **args, bool *inv_order); int opt_sum_query(THD* thd, - List<TABLE_LIST> &tables, List<Item> &all_fields, COND *conds); + List<TABLE_LIST> &tables, List<Item> &all_fields, COND *conds, + COND_EQUAL *cond_equal); /* from sql_delete.cc, used by opt_range.cc */ extern "C" int refpos_order_cmp(void* arg, const void *a,const void *b);