summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/main/having_cond_pushdown.result43
-rw-r--r--mysql-test/main/having_cond_pushdown.test31
-rw-r--r--sql/item.h6
-rw-r--r--sql/item_func.h2
-rw-r--r--sql/opt_subselect.cc19
5 files changed, 89 insertions, 12 deletions
diff --git a/mysql-test/main/having_cond_pushdown.result b/mysql-test/main/having_cond_pushdown.result
index d7c9d936627..82a4813b156 100644
--- a/mysql-test/main/having_cond_pushdown.result
+++ b/mysql-test/main/having_cond_pushdown.result
@@ -4733,3 +4733,46 @@ EXPLAIN
}
}
drop table t1;
+#
+# 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 5088dad734d..f1bf70627f6 100644
--- a/mysql-test/main/having_cond_pushdown.test
+++ b/mysql-test/main/having_cond_pushdown.test
@@ -1370,3 +1370,34 @@ eval explain extended $q2;
eval explain format=json $q2;
drop table t1;
+
+--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 0d407353d60..d88216141ce 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1947,6 +1947,12 @@ public:
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 @@ public:
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..2fedd8a4ed3 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,14 @@ 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
View Lorry Controller Status