[Commits] 0198afed649: MDEV-12867: Full scan despite appropriate index

Varun varunraiko1803 at gmail.com
Sun Jul 7 01:14:42 EEST 2019


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);


More information about the commits mailing list