[Commits] 0aa2c0a1cf3: MDEV-19245: Impossible WHERE should be noticed earlier after HAVING pushdown

Galina galina.shalygina at mariadb.com
Mon Apr 15 20:47:24 EEST 2019


revision-id: 0aa2c0a1cf393d9cafc3504ea7f24be77aec0076 (mariadb-10.4.4-4-g0aa2c0a1cf3)
parent(s): 1e7ad5bb1c69dba8c7d721a2cfbbe98c7e900015
author: Galina Shalygina
committer: Galina Shalygina
timestamp: 2019-04-15 20:47:24 +0300
message:

MDEV-19245: Impossible WHERE should be noticed earlier after HAVING pushdown

The bug appears because not all conditions are found to be knowingly
true or false in WHERE after HAVING pushdown optimization.
Impossible WHERE can be found much earlier compared with how it is done now.

To fix it and_new_conditions_to_optimized_cond() is changed.

---
 mysql-test/main/having_cond_pushdown.result | 43 +++++++++++++++++++++++++++++
 mysql-test/main/having_cond_pushdown.test   | 31 +++++++++++++++++++++
 sql/item.h                                  |  5 ++++
 sql/item_func.h                             |  2 ++
 sql/opt_subselect.cc                        | 18 ++++--------
 5 files changed, 87 insertions(+), 12 deletions(-)

diff --git a/mysql-test/main/having_cond_pushdown.result b/mysql-test/main/having_cond_pushdown.result
index 85ca0342dee..20e3567f8ee 100644
--- a/mysql-test/main/having_cond_pushdown.result
+++ b/mysql-test/main/having_cond_pushdown.result
@@ -4657,3 +4657,46 @@ GROUP BY v1.pk
 HAVING (v1.pk = 1);
 DROP TABLE t1,t2,tmp1;
 DROP VIEW v1;
+#
+# MDEV-19245: Impossible WHERE should be noticed earlier
+#             after HAVING pushdown
+#
+CREATE TABLE t1 (a INT, b INT, c INT);
+INSERT INTO t1 VALUES (1,2,1),(3,2,2),(5,6,4),(3,4,1);
+EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
+WHERE t1.a > 3 GROUP BY t1.a HAVING t1.a = 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
+WHERE t1.a = 3 GROUP BY t1.a HAVING t1.a > 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
+WHERE t1.a > 3 AND t1.a = 3 GROUP BY t1.a ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
+WHERE (t1.a < 2 OR t1.c > 1) GROUP BY t1.a HAVING t1.a = 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
+WHERE t1.a = 3 GROUP BY t1.a HAVING (t1.a < 2 OR t1.a > 3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
+WHERE t1.a = 3 AND (t1.a < 2 OR t1.a > 3) GROUP BY t1.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1
+WHERE (t1.a < 2 AND t1.c > 1) GROUP BY t1.a HAVING t1.a = 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1
+WHERE t1.a = 3 GROUP BY t1.a HAVING (t1.a < 2 AND t1.c > 1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1
+WHERE t1.a = 3 AND (t1.a < 2 AND t1.b > 3) GROUP BY t1.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+DROP TABLE t1;
diff --git a/mysql-test/main/having_cond_pushdown.test b/mysql-test/main/having_cond_pushdown.test
index 257e5cb4875..ed466455808 100644
--- a/mysql-test/main/having_cond_pushdown.test
+++ b/mysql-test/main/having_cond_pushdown.test
@@ -1340,3 +1340,34 @@ HAVING (v1.pk = 1);
 
 DROP TABLE t1,t2,tmp1;
 DROP VIEW v1;
+
+--echo #
+--echo # MDEV-19245: Impossible WHERE should be noticed earlier
+--echo #             after HAVING pushdown
+--echo #
+
+CREATE TABLE t1 (a INT, b INT, c INT);
+INSERT INTO t1 VALUES (1,2,1),(3,2,2),(5,6,4),(3,4,1);
+ 
+EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
+WHERE t1.a > 3 GROUP BY t1.a HAVING t1.a = 3;
+EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
+WHERE t1.a = 3 GROUP BY t1.a HAVING t1.a > 3;
+EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
+WHERE t1.a > 3 AND t1.a = 3 GROUP BY t1.a ;
+
+EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
+WHERE (t1.a < 2 OR t1.c > 1) GROUP BY t1.a HAVING t1.a = 3;
+EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
+WHERE t1.a = 3 GROUP BY t1.a HAVING (t1.a < 2 OR t1.a > 3);
+EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
+WHERE t1.a = 3 AND (t1.a < 2 OR t1.a > 3) GROUP BY t1.a;
+
+EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1
+WHERE (t1.a < 2 AND t1.c > 1) GROUP BY t1.a HAVING t1.a = 3;
+EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1
+WHERE t1.a = 3 GROUP BY t1.a HAVING (t1.a < 2 AND t1.c > 1);
+EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1
+WHERE t1.a = 3 AND (t1.a < 2 AND t1.b > 3) GROUP BY t1.a;
+
+DROP TABLE t1;
diff --git a/sql/item.h b/sql/item.h
index 97d31e6ba34..2d05f640a8d 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1943,6 +1943,11 @@ class Item: public Value_source,
   virtual bool check_partition_func_processor(void *arg) { return 1;}
   virtual bool post_fix_fields_part_expr_processor(void *arg) { return 0; }
   virtual bool rename_fields_processor(void *arg) { return 0; }
+  /*
+    TRUE if the function is knowingly TRUE or FALSE.
+    Not to be used for AND/OR formulas.
+  */
+  virtual bool is_simplified_cond_processor(void *arg) { return false; }
   /** Processor used to check acceptability of an item in the defining
       expression for a virtual column 
 
diff --git a/sql/item_func.h b/sql/item_func.h
index 44e9691c9df..27cb245db6b 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -398,6 +398,8 @@ class Item_func :public Item_func_or_sum,
   bool with_sum_func() const { return m_with_sum_func; }
   With_sum_func_cache* get_with_sum_func_cache() { return this; }
   Item_func *get_item_func() { return this; }
+  bool is_simplified_cond_processor(void *arg)
+  { return const_item() && !val_int(); }
 };
 
 
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 32b70b41eb3..4c912154e95 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -5723,12 +5723,6 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond,
                                       &((Item_cond_and *) cond)->m_cond_equal,
                                       false, NULL);
       }
-      /*
-        Check if equalities that can't be transformed into multiple
-        equalities are knowingly true or false.
-      */
-      if (item->const_item() && !item->val_int())
-        is_simplified_cond= true;
       and_args->push_back(item, thd->mem_root);
     }
     and_args->append((List<Item> *) cond_equalities);
@@ -5821,12 +5815,6 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond,
       {
         item= item->build_equal_items(thd, inherited, false, NULL);
       }
-      /*
-        Check if equalities that can't be transformed into multiple
-        equalities are knowingly true or false.
-      */
-      if (item->const_item() && !item->val_int())
-        is_simplified_cond= true;
       new_conds_list.push_back(item, thd->mem_root);
     }
     new_conds_list.append((List<Item> *)&new_cond_equal.current_level);
@@ -5870,7 +5858,13 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond,
     cond= cond->propagate_equal_fields(thd,
                                        Item::Context_boolean(),
                                        *cond_eq);
+    cond->update_used_tables();
   }
+  /* Check if conds has knowingly true or false parts. */
+  if (cond &&
+      !is_simplified_cond &&
+      cond->walk(&Item::is_simplified_cond_processor, 0, 0))
+    is_simplified_cond= true;
 
   /*
     If it was found that there are some knowingly true or false equalities


More information about the commits mailing list