revision-id: 39bc7ab0c120281c574631f1a2a335ee78863c26 (mariadb-10.4.3-52-g39bc7ab0c12) parent(s): 5a796f1f41a5bc0afb638cc342095e59a5bb15df author: Galina Shalygina committer: Galina Shalygina timestamp: 2019-03-18 23:36:33 +0300 message: MDEV-18769 Assertion `fixed == 1' failed in Item_cond_or::val_int This bug is caused by pushdown from HAVING into WHERE. It appears because condition that is pushed wasn't fixed. It is also discovered that condition pushdown from HAVING into WHERE is done wrong. There is no need to build clones for some conditions that can be pushed. They can be simply moved from HAVING into WHERE without cloning. build_pushable_cond_for_having_pushdown(), remove_pushed_top_conjuncts_for_having() methods are changed. It is found that there is no transformation made for fields of pushed condition. field_transformer_for_having_pushdown transformer is added. New tests are added. Some comments are changed. --- mysql-test/main/derived_cond_pushdown.result | 31 +- mysql-test/main/having.result | 2 +- mysql-test/main/having_cond_pushdown.result | 2313 ++++++++++++++++++++++++++ mysql-test/main/having_cond_pushdown.test | 670 ++++++++ mysql-test/main/union.result | 2 +- sql/item.h | 8 + sql/item_cmpfunc.cc | 57 +- sql/item_cmpfunc.h | 1 + sql/opt_subselect.cc | 157 +- sql/sql_lex.cc | 386 ++--- sql/sql_lex.h | 2 +- sql/sql_select.cc | 3 +- 12 files changed, 3348 insertions(+), 284 deletions(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index a13ce8575de..54068c4d6dd 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -8218,12 +8218,10 @@ EXPLAIN "query_block": { "select_id": 1, "table": { - "table_name": "<subquery2>", - "access_type": "system", - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, + "message": "Impossible WHERE" + }, + "subqueries": [ + { "query_block": { "select_id": 2, "table": { @@ -8231,26 +8229,7 @@ EXPLAIN } } } - }, - "table": { - "table_name": "<derived3>", - "access_type": "ALL", - "rows": 2, - "filtered": 100, - "attached_condition": "v1.c = NULL", - "materialized": { - "query_block": { - "select_id": 3, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 2, - "filtered": 100, - "attached_condition": "t1.c = NULL" - } - } - } - } + ] } } DROP VIEW v1; diff --git a/mysql-test/main/having.result b/mysql-test/main/having.result index dd710db715a..837940a55ef 100644 --- a/mysql-test/main/having.result +++ b/mysql-test/main/having.result @@ -483,7 +483,7 @@ HAVING (table2.f2 = 8); 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 `test`.`table1`.`f1` AS `f1`,7 AS `f2` from `test`.`t1` `table1` join `test`.`t1` `table2` where 0 group by `test`.`table1`.`f1`,7 having multiple equal(8, 7) +Note 1003 select `test`.`table1`.`f1` AS `f1`,7 AS `f2` from `test`.`t1` `table1` join `test`.`t1` `table2` where 0 group by `test`.`table1`.`f1`,7 having 1 DROP TABLE t1; # # Bug#52336 Segfault / crash in 5.1 copy_fields (param=0x9872980) at sql_select.cc:15355 diff --git a/mysql-test/main/having_cond_pushdown.result b/mysql-test/main/having_cond_pushdown.result index ef7368bd1fb..8bcceb59d72 100644 --- a/mysql-test/main/having_cond_pushdown.result +++ b/mysql-test/main/having_cond_pushdown.result @@ -1918,3 +1918,2316 @@ SELECT a FROM t1 WHERE b = 1 AND b = 2 GROUP BY a HAVING 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 DROP TABLE t1; +# +# MDEV-18769: unfixed OR condition pushed from HAVING into WHERE +# +CREATE TABLE t1(a INT, b INT, c INT); +INSERT INTO t1 VALUES (1,14,3), (2,13,2), (1,22,1), (3,13,4), (3,14,2); +# nothing to push +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING t1.b = 13 AND MAX(t1.c) > 2; +a b MAX(t1.c) +3 13 4 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING t1.b = 13 AND MAX(t1.c) > 2; +a b MAX(t1.c) +3 13 4 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING t1.b = 13 AND MAX(t1.c) > 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING t1.b = 13 AND MAX(t1.c) > 2; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.b = 13 and max(t1.c) > 2", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING t1.b = 13 AND MAX(t1.c) > 2; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.b = 13 and max(t1.c) > 2", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + } + } + } + } +} +# extracted AND formula +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14); +a b MAX(t1.c) +2 13 2 +3 13 4 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14); +a b MAX(t1.c) +2 13 2 +3 13 4 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a = 1 or t1.b > 10) and t1.b < 14" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1 OR t1.b > 10) AND (t1.b < 14) +GROUP BY t1.a,t1.b; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a = 1 or t1.b > 10) and t1.b < 14" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15); +a b MAX(t1.c) +1 22 1 +2 13 2 +3 13 4 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15); +a b MAX(t1.c) +1 22 1 +2 13 2 +3 13 4 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a = 1 or t1.b > 10) and (t1.b < 14 or t1.b > 15)" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15) +GROUP BY t1.a,t1.b; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a = 1 or t1.b > 10) and (t1.b < 14 or t1.b > 15)" + } + } + } + } +} +# extracted AND formula : equality in the inner AND formula +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2)); +a b MAX(t1.c) +2 13 2 +3 13 4 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2)); +a b MAX(t1.c) +2 13 2 +3 13 4 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a = 1 or t1.b > 10) and (t1.b < 14 or t1.a = 2 and t1.b > 15)" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2)) +GROUP BY t1.a,t1.b; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a = 1 or t1.b > 10) and (t1.b < 14 or t1.a = 2 and t1.b > 15)" + } + } + } + } +} +# extracted OR formula +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2) OR (t1.b = 13 AND t1.a > 2); +a b MAX(t1.c) +1 14 3 +1 22 1 +3 13 4 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2) OR (t1.b = 13 AND t1.a > 2); +a b MAX(t1.c) +1 14 3 +1 22 1 +3 13 4 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2) OR (t1.b = 13 AND t1.a > 2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2) OR (t1.b = 13 AND t1.a > 2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a < 2 or t1.b = 13 and t1.a > 2" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a < 2) OR (t1.b = 13 AND t1.a > 2) +GROUP BY t1.a,t1.b; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a < 2 or t1.b = 13 and t1.a > 2" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13); +a b MAX(t1.c) +1 14 3 +3 13 4 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13); +a b MAX(t1.c) +1 14 3 +3 13 4 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b = 14 and t1.a < 2 or t1.b = 13 and t1.a > 2" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13) +GROUP BY t1.a,t1.b; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b = 14 and t1.a < 2 or t1.b = 13 and t1.a > 2" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14)); +a b MAX(t1.c) +1 14 3 +3 13 4 +3 14 2 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14)); +a b MAX(t1.c) +1 14 3 +3 13 4 +3 14 2 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b = 14 and t1.a < 2 or t1.a > 2 and (t1.b = 13 or t1.b = 14)" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14)) +GROUP BY t1.a,t1.b; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b = 14 and t1.a < 2 or t1.a > 2 and (t1.b = 13 or t1.b = 14)" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +a b MAX(t1.c) +1 14 3 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +a b MAX(t1.c) +1 14 3 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a < 2 and max(t1.c) = 2 or max(t1.c) > 2 and (t1.a = 1 or t1.a = 2)", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a < 2 or t1.a = 1 or t1.a = 2" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a < 2) OR (t1.a = 1 OR t1.a = 2) +GROUP BY t1.a +HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a < 2 and max(t1.c) = 2 or max(t1.c) > 2 and (t1.a = 1 or t1.a = 2)", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a < 2 or t1.a = 1 or t1.a = 2" + } + } + } + } +} +# extracted OR formula : one multiple equality in the left part +of OR condition, part of it pushed +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +a b MAX(t1.c) +1 14 3 +2 13 2 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +a b MAX(t1.c) +1 14 3 +2 13 2 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a = 2 and max(t1.c) = 2 or max(t1.c) > 2 and (t1.a = 1 or t1.a = 2)", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 2 or t1.a = 1 or t1.a = 2" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 2) OR (t1.a = 1 OR t1.a = 2) +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a = 2 and max(t1.c) = 2 or max(t1.c) > 2 and (t1.a = 1 or t1.a = 2)", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 2 or t1.a = 1 or t1.a = 2" + } + } + } + } +} +# extracted OR formula : two multiple equalities in the left part +of OR condition, one pushed +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 3) OR (MAX(t1.c) < 5 AND (t1.a = 1 OR t1.a = 2)); +a b MAX(t1.c) +1 14 3 +2 13 2 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 3) OR (MAX(t1.c) < 5 AND (t1.a = 1 OR t1.a = 2)); +a b MAX(t1.c) +1 14 3 +2 13 2 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 3) OR (MAX(t1.c) < 5 AND (t1.a = 1 OR t1.a = 2)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 3) OR (MAX(t1.c) < 5 AND (t1.a = 1 OR t1.a = 2)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a = 2 and max(t1.c) = 3 or max(t1.c) < 5 and (t1.a = 1 or t1.a = 2)", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 2 or t1.a = 1 or t1.a = 2" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 2) OR (t1.a = 1 OR t1.a = 2) +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 3) OR (MAX(t1.c) < 5 AND (t1.a = 1 OR t1.a = 2)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a = 2 and max(t1.c) = 3 or max(t1.c) < 5 and (t1.a = 1 or t1.a = 2)", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 2 or t1.a = 1 or t1.a = 2" + } + } + } + } +} +# conjunctive subformula : equality pushdown +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 1) AND (MAX(t1.c) = 3); +a b MAX(t1.c) +1 14 3 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 1) AND (MAX(t1.c) = 3); +a b MAX(t1.c) +1 14 3 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 1) AND (MAX(t1.c) = 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 1) AND (MAX(t1.c) = 3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.c) = 3", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (MAX(t1.c) = 3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.c) = 3", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1" + } + } +} +# conjunctive subformula : equalities pushdown +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 3) AND MAX(t1.b = 14); +a b MAX(t1.c) +1 14 3 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 3) AND MAX(t1.b = 14); +a b MAX(t1.c) +1 14 3 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 3) AND MAX(t1.b = 14); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 3) AND MAX(t1.b = 14); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b = 14)", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 3" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 3) +GROUP BY t1.a,t1.c +HAVING (MAX(t1.b) = 14); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b) = 14", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 3" + } + } +} +# conjunctive subformula : multiple equality consists of +two equalities pushdown +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 1) AND MAX(t1.b = 14); +a b MAX(t1.c) +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 1) AND MAX(t1.b = 14); +a b MAX(t1.c) +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 1) AND MAX(t1.b = 14); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 1) AND MAX(t1.b = 14); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b = 14)", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 1" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 1) +GROUP BY t1.a,t1.c +HAVING (MAX(t1.b) = 14); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b) = 14", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 1" + } + } +} +# +# Pushdown from HAVING into non-empty WHERE +# +# inequality : inequality in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a < 3); +a b MAX(t1.c) +1 14 3 +2 13 2 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a < 3); +a b MAX(t1.c) +1 14 3 +2 13 2 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +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 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a < 3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b > 2 and t1.a < 3" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) AND (t1.a < 3) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b > 2 and t1.a < 3" + } + } + } + } +} +# equality : inequality in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a = 3); +a b MAX(t1.c) +3 13 4 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a = 3); +a b MAX(t1.c) +3 13 4 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +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 5 Using where +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a = 3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 3 and t1.b > 2" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) AND (t1.a = 3) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 3 and t1.b > 2" + } + } +} +# inequality : equality in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a < 3); +a b MAX(t1.c) +1 14 3 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a < 3); +a b MAX(t1.c) +1 14 3 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +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 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a < 3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b = 14 and t1.a < 3" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) AND (t1.a < 3) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b = 14 and t1.a < 3" + } + } + } + } +} +# equality : equality in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a = 1); +a b MAX(t1.c) +1 14 3 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a = 1); +a b MAX(t1.c) +1 14 3 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a = 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a = 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.b = 14" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) AND (t1.a = 1) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b = 14 and t1.a = 1" + } + } +} +# equality : equality in WHERE (equal through constant) +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c = 1) +GROUP BY t1.a +HAVING (t1.a = 1); +a MAX(t1.b) c +1 22 1 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c = 1) +GROUP BY t1.a +HAVING (t1.a = 1); +a MAX(t1.b) c +1 22 1 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c = 1) +GROUP BY t1.a +HAVING (t1.a = 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c = 1) +GROUP BY t1.a +HAVING (t1.a = 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 1" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c = 1) AND (t1.a = 1) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.c = 1 and t1.a = 1" + } + } +} +# inequality : AND formula in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a > 1); +a MAX(t1.b) c +2 13 2 +3 14 2 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a > 1); +a MAX(t1.b) c +2 13 2 +3 14 2 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.c > 0 and t1.c < 3 and t1.a > 1" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) AND (t1.a > 1) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.c > 0 and t1.c < 3 and t1.a > 1" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a > 1); +a MAX(t1.b) c +2 13 2 +3 14 2 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a > 1); +a MAX(t1.b) c +2 13 2 +3 14 2 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 0 and t1.c < 3 and t1.a > 1" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) AND (t1.a > 1) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 0 and t1.c < 3 and t1.a > 1" + } + } + } + } +} +# equality : AND formula in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +a MAX(t1.b) c +1 22 1 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +a MAX(t1.b) c +1 22 1 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c > 0 and t1.c < 3" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) AND (t1.a = 1) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c > 0 and t1.c < 3" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +a MAX(t1.b) c +1 22 1 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +a MAX(t1.b) c +1 22 1 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "const_condition": "1", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c < 3" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) AND (t1.a = 1) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c < 3" + } + } +} +# inequality : OR formula in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 2); +a MAX(t1.b) c +1 22 1 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 2); +a MAX(t1.b) c +1 22 1 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a > 1 or t1.c < 3) and t1.a < 2" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) OR (t1.c < 3)) AND (t1.a < 2) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a > 1 or t1.c < 3) and t1.a < 2" + } + } + } + } +} +# AND formula : inequality in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.a > 0); +a MAX(t1.b) c +2 13 2 +3 14 4 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.a > 0); +a MAX(t1.b) c +2 13 2 +3 14 4 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.a > 0); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.a > 0); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a < 4 and t1.a > 0" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.a < 4) AND (t1.a > 0) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a < 4 and t1.a > 0" + } + } + } + } +} +# OR formula : inequality in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +a MAX(t1.b) c +2 13 2 +3 14 4 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +a MAX(t1.b) c +2 13 2 +3 14 4 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 and (t1.a < 4 or t1.a > 0)" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND ((t1.a < 4) OR (t1.a > 0)) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 and (t1.a < 4 or t1.a > 0)" + } + } + } + } +} +# OR formula : equality in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +a MAX(t1.b) c +1 22 3 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +a MAX(t1.b) c +1 22 3 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "const_condition": "1", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND ((t1.a < 4) OR (t1.a > 0)) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1" + } + } +} +# AND formula : AND formula in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +a MAX(t1.b) c +2 13 2 +3 14 2 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +a MAX(t1.b) c +2 13 2 +3 14 2 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.c > 1", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.c < 3 and t1.a < 4" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) AND (t1.c < 3)) AND +(t1.a < 4) +GROUP BY t1.a +HAVING (t1.c > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.c > 1", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.c < 3 and t1.a < 4" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +a MAX(t1.b) c +2 13 2 +3 14 2 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +a MAX(t1.b) c +2 13 2 +3 14 2 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.c", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.c < 3 and t1.a < 4 and t1.c > 1" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) AND (t1.c < 3)) AND +((t1.a < 4) AND (t1.c > 1)) +GROUP BY t1.a,t1.c; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.c", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.c < 3 and t1.a < 4 and t1.c > 1" + } + } + } + } +} +# AND formula : OR formula in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +a MAX(t1.b) c +2 13 2 +3 14 4 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +a MAX(t1.b) c +2 13 2 +3 14 4 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.c > 1", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a > 1 or t1.c < 3) and t1.a < 4" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) OR (t1.c < 3)) AND +(t1.a < 4) +GROUP BY t1.a +HAVING (t1.c > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.c > 1", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a > 1 or t1.c < 3) and t1.a < 4" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +a MAX(t1.b) c +2 13 2 +3 14 2 +3 13 4 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +a MAX(t1.b) c +2 13 2 +3 14 2 +3 13 4 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.c", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a > 1 or t1.c < 3) and t1.a < 4 and t1.c > 1" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) OR (t1.c < 3)) AND +(t1.a < 4) AND (t1.c > 1) +GROUP BY t1.a,t1.c; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.c", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a > 1 or t1.c < 3) and t1.a < 4 and t1.c > 1" + } + } + } + } +} +# OR formula : OR formula in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.a < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) OR (t1.c > 1); +a MAX(t1.b) c +1 22 1 +1 14 3 +2 13 2 +3 14 2 +3 13 4 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.a < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) OR (t1.c > 1); +a MAX(t1.b) c +1 22 1 +1 14 3 +2 13 2 +3 14 2 +3 13 4 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.a < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) OR (t1.c > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.a < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) OR (t1.c > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.c", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a > 1 or t1.a < 3) and (t1.a < 4 or t1.c > 1)" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) OR (t1.a < 3)) AND +((t1.a < 4) OR (t1.c > 1)) +GROUP BY t1.a,t1.c; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.c", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a > 1 or t1.a < 3) and (t1.a < 4 or t1.c > 1)" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) OR (t1.a = 3) +GROUP BY t1.a,t1.c +HAVING (t1.a = 4) OR (t1.c > 1); +a MAX(t1.b) c +1 14 3 +3 14 2 +3 13 4 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) OR (t1.a = 3) +GROUP BY t1.a,t1.c +HAVING (t1.a = 4) OR (t1.c > 1); +a MAX(t1.b) c +1 14 3 +3 14 2 +3 13 4 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) OR (t1.a = 3) +GROUP BY t1.a,t1.c +HAVING (t1.a = 4) OR (t1.c > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) OR (t1.a = 3) +GROUP BY t1.a,t1.c +HAVING (t1.a = 4) OR (t1.c > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.c", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a = 1 or t1.a = 3) and (t1.a = 4 or t1.c > 1)" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a = 1) OR (t1.a = 3)) AND +((t1.a = 4) OR (t1.c > 1)) +GROUP BY t1.a,t1.c; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.c", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a = 1 or t1.a = 3) and (t1.a = 4 or t1.c > 1)" + } + } + } + } +} +# equality : pushdown through equality +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1); +a MAX(t1.b) c +1 22 1 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1); +a MAX(t1.b) c +1 22 1 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 1" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) AND (t1.c = 1) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 1" + } + } +} +# OR formula : pushdown through equality +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1) OR (t1.c = 2); +a MAX(t1.b) c +1 22 1 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1) OR (t1.c = 2); +a MAX(t1.b) c +1 22 1 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1) OR (t1.c = 2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1) OR (t1.c = 2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 1" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) AND +((t1.c = 1) OR (t1.c = 2)) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 1" + } + } +} +# AND formula : pushdown through equality +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a,t1.c +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +a MAX(t1.b) c +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a,t1.c +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +a MAX(t1.b) c +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a,t1.c +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a,t1.c +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "message": "Impossible WHERE noticed after reading const tables" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 3) AND +(t1.a > 2) AND (t1.a = t1.c) +GROUP BY t1.a,t1.c; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "message": "Impossible WHERE" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +a MAX(t1.b) c +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +a MAX(t1.b) c +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "message": "Impossible WHERE noticed after reading const tables" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 3) AND +(t1.a > 2) AND (t1.a = t1.c) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "message": "Impossible WHERE" + } + } +} +# prepare statement +PREPARE stmt1 from " +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 1) +"; +execute stmt1; +a MAX(t1.b) c +1 22 3 +execute stmt1; +a MAX(t1.b) c +1 22 3 +deallocate prepare stmt1; +DROP TABLE t1; diff --git a/mysql-test/main/having_cond_pushdown.test b/mysql-test/main/having_cond_pushdown.test index 2fbb5708c50..3b556a01687 100644 --- a/mysql-test/main/having_cond_pushdown.test +++ b/mysql-test/main/having_cond_pushdown.test @@ -486,3 +486,673 @@ EXPLAIN SELECT a FROM t1 WHERE b = 1 AND b = 2 GROUP BY a HAVING a <= 3; DROP TABLE t1; + +--echo # +--echo # MDEV-18769: unfixed OR condition pushed from HAVING into WHERE +--echo # + +CREATE TABLE t1(a INT, b INT, c INT); +INSERT INTO t1 VALUES (1,14,3), (2,13,2), (1,22,1), (3,13,4), (3,14,2); + +--echo # nothing to push +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING t1.b = 13 AND MAX(t1.c) > 2; +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING t1.b = 13 AND MAX(t1.c) > 2; +eval $no_pushdown explain format=json $query; + +--echo # extracted AND formula +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1 OR t1.b > 10) AND (t1.b < 14) +GROUP BY t1.a,t1.b; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15) +GROUP BY t1.a,t1.b; +eval $no_pushdown explain format=json $query; + +--echo # extracted AND formula : equality in the inner AND formula +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2)); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2)) +GROUP BY t1.a,t1.b; +eval $no_pushdown explain format=json $query; + +--echo # extracted OR formula +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2) OR (t1.b = 13 AND t1.a > 2); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a < 2) OR (t1.b = 13 AND t1.a > 2) +GROUP BY t1.a,t1.b; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13) +GROUP BY t1.a,t1.b; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14)); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14)) +GROUP BY t1.a,t1.b; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a < 2) OR (t1.a = 1 OR t1.a = 2) +GROUP BY t1.a +HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +eval $no_pushdown explain format=json $query; + +--echo # extracted OR formula : one multiple equality in the left part +--echo of OR condition, part of it pushed + +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 2) OR (t1.a = 1 OR t1.a = 2) +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +eval $no_pushdown explain format=json $query; + +--echo # extracted OR formula : two multiple equalities in the left part +--echo of OR condition, one pushed +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 3) OR (MAX(t1.c) < 5 AND (t1.a = 1 OR t1.a = 2)); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 2) OR (t1.a = 1 OR t1.a = 2) +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 3) OR (MAX(t1.c) < 5 AND (t1.a = 1 OR t1.a = 2)); +eval $no_pushdown explain format=json $query; + +--echo # conjunctive subformula : equality pushdown +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 1) AND (MAX(t1.c) = 3); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (MAX(t1.c) = 3); +eval $no_pushdown explain format=json $query; + +--echo # conjunctive subformula : equalities pushdown +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 3) AND MAX(t1.b = 14); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 3) +GROUP BY t1.a,t1.c +HAVING (MAX(t1.b) = 14); +eval $no_pushdown explain format=json $query; + +--echo # conjunctive subformula : multiple equality consists of +--echo two equalities pushdown +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 1) AND MAX(t1.b = 14); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 1) +GROUP BY t1.a,t1.c +HAVING (MAX(t1.b) = 14); +eval $no_pushdown explain format=json $query; + +--echo # +--echo # Pushdown from HAVING into non-empty WHERE +--echo # + +--echo # inequality : inequality in WHERE +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a < 3); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) AND (t1.a < 3) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # equality : inequality in WHERE +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a = 3); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) AND (t1.a = 3) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # inequality : equality in WHERE +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a < 3); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) AND (t1.a < 3) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # equality : equality in WHERE +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a = 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) AND (t1.a = 1) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # equality : equality in WHERE (equal through constant) +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c = 1) +GROUP BY t1.a +HAVING (t1.a = 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c = 1) AND (t1.a = 1) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # inequality : AND formula in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a > 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) AND (t1.a > 1) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a > 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) AND (t1.a > 1) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # equality : AND formula in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) AND (t1.a = 1) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) AND (t1.a = 1) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # inequality : OR formula in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 2); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) OR (t1.c < 3)) AND (t1.a < 2) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # AND formula : inequality in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.a > 0); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.a < 4) AND (t1.a > 0) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # OR formula : inequality in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND ((t1.a < 4) OR (t1.a > 0)) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # OR formula : equality in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND ((t1.a < 4) OR (t1.a > 0)) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # AND formula : AND formula in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) AND (t1.c < 3)) AND + (t1.a < 4) +GROUP BY t1.a +HAVING (t1.c > 1); +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) AND (t1.c < 3)) AND + ((t1.a < 4) AND (t1.c > 1)) +GROUP BY t1.a,t1.c; +eval $no_pushdown explain format=json $query; + +--echo # AND formula : OR formula in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) OR (t1.c < 3)) AND + (t1.a < 4) +GROUP BY t1.a +HAVING (t1.c > 1); +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) OR (t1.c < 3)) AND + (t1.a < 4) AND (t1.c > 1) +GROUP BY t1.a,t1.c; +eval $no_pushdown explain format=json $query; + +--echo # OR formula : OR formula in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.a < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) OR (t1.c > 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) OR (t1.a < 3)) AND + ((t1.a < 4) OR (t1.c > 1)) +GROUP BY t1.a,t1.c; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) OR (t1.a = 3) +GROUP BY t1.a,t1.c +HAVING (t1.a = 4) OR (t1.c > 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a = 1) OR (t1.a = 3)) AND + ((t1.a = 4) OR (t1.c > 1)) +GROUP BY t1.a,t1.c; +eval $no_pushdown explain format=json $query; + +--echo # equality : pushdown through equality +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) AND (t1.c = 1) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # OR formula : pushdown through equality +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1) OR (t1.c = 2); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) AND + ((t1.c = 1) OR (t1.c = 2)) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # AND formula : pushdown through equality +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a,t1.c +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 3) AND + (t1.a > 2) AND (t1.a = t1.c) +GROUP BY t1.a,t1.c; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 3) AND + (t1.a > 2) AND (t1.a = t1.c) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # prepare statement +PREPARE stmt1 from " +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 1) +"; +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +DROP TABLE t1; diff --git a/mysql-test/main/union.result b/mysql-test/main/union.result index ce14b68ae2d..a0421bae922 100644 --- a/mysql-test/main/union.result +++ b/mysql-test/main/union.result @@ -2332,7 +2332,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 UNION NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: -Note 1003 /* select#1 */ select 1 AS `1`,2 AS `2` union all /* select#2 */ select 1 AS `i`,count(0) AS `COUNT(*)` from `test`.`t2` where 0 group by 1 having multiple equal(10, `i`) +Note 1003 /* select#1 */ select 1 AS `1`,2 AS `2` union all /* select#2 */ select 1 AS `i`,count(0) AS `COUNT(*)` from `test`.`t2` where 0 group by 1 having 1 DROP TABLE t1,t2; # # Start of 10.3 tests diff --git a/sql/item.h b/sql/item.h index 0ffbd2f8bc4..b382272863e 100644 --- a/sql/item.h +++ b/sql/item.h @@ -2077,6 +2077,10 @@ class Item: public Value_source, { return this; } virtual Item *in_predicate_to_in_subs_transformer(THD *thd, uchar *arg) { return this; } + virtual Item *field_transformer_for_having_pushdown(THD *thd, uchar *arg) + { return this; } + virtual Item *multiple_equality_transformer(THD *thd, uchar *arg) + { return this; } virtual bool expr_cache_is_needed(THD *) { return FALSE; } virtual Item *safe_charset_converter(THD *thd, CHARSET_INFO *tocs); bool needs_charset_converter(uint32 length, CHARSET_INFO *tocs) const @@ -5350,6 +5354,8 @@ class Item_ref :public Item_ident, } bool with_sum_func() const { return m_with_sum_func; } With_sum_func_cache* get_with_sum_func_cache() { return this; } + Item *field_transformer_for_having_pushdown(THD *thd, uchar *arg) + { return (*ref)->field_transformer_for_having_pushdown(thd, arg); } }; @@ -5742,6 +5748,8 @@ class Item_direct_view_ref :public Item_direct_ref } Item *get_copy(THD *thd) { return get_item_copy<Item_direct_view_ref>(thd, this); } + Item *field_transformer_for_having_pushdown(THD *thd, uchar *arg) + { return this; } }; diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 86725de8ae7..1463e1bf2cf 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -7378,31 +7378,37 @@ Item_equal::excl_dep_on_group_fields_for_having_pushdown(st_select_lex *sel) /** @brief - Create from this multiple equality equalities that can be pushed down + Transform multiple equality into the list of equalities @param thd the thread handle - @param equalities the result list of created equalities + @param equalities the list where created equalities are stored @param checker the checker callback function to be applied to the nodes - of the tree of the object + of the tree of the object to check if multiple equality + elements can be used to create equalities @param arg parameter to be passed to the checker @details - The method traverses this multiple equality trying to create from it - new equalities that can be pushed down. It creates equalities with - the constant used in this multiple equality if it exists or the first - item for which checker returns non-NULL result and all other items - in this multiple equality for which checker returns non-NULL result. + The method transforms multiple equality into the list of equalities in + such way: + it goes through the elements of the multiple equality and checks with + checker if this elements can be used in equality creation. + If the element can be used the method creates equality using this + element and: - Example: + 1. Constant, if there is a constant defined in this multiple equality. MULT_EQ(1,a,b) => Created equalities: {(1=a),(1=b)} + or 2. The first element in this multiple equality that is passed + by checker. + MULT_EQ(a,b,c,d) => Created equalities: {(a=b),(a=c),(a=d)} + All created equalities are collected into the equalities list. @retval true if an error occurs @retval false otherwise @@ -7450,3 +7456,36 @@ bool Item_equal::create_pushable_equalities(THD *thd, } return false; } + + +/** + Transform multiple equality into the AND condition of equalities. + + MULT_EQ(1,a,b) + => + (a = 1) AND (b = 1) + + Equalities are built in Item_equal::create_pushable_equalities() method + using elements of this multiple equality. +*/ + +Item *Item_equal::multiple_equality_transformer(THD *thd, uchar *arg) +{ + List<Item> equalities; + Pushdown_checker checker= + &Item::pushable_equality_checker_for_having_pushdown; + if (create_pushable_equalities(thd, &equalities, checker, (uchar *)this)) + return 0; + + switch (equalities.elements) + { + case 0: + return 0; + case 1: + return equalities.head(); + break; + default: + return new (thd->mem_root) Item_cond_and(thd, equalities); + break; + } +} diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index d78977544c7..7145fcf3bfa 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -3207,6 +3207,7 @@ class Item_equal: public Item_bool_func uint elements_count() { return equal_items.elements; } friend class Item_equal_fields_iterator; bool count_sargable_conds(void *arg); + Item *multiple_equality_transformer(THD *thd, uchar *arg); friend class Item_equal_iterator<List_iterator_fast,Item>; friend class Item_equal_iterator<List_iterator,Item>; friend Item *eliminate_item_equal(THD *thd, COND *cond, diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index ddccddd55ff..a49c56e49e1 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -5673,16 +5673,12 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, It checks if after the merge the multiple equalities are knowingly true or false equalities. It attaches to cond the conditions from new_conds list and the result - of the merge of multiple equalities. The multiple equalities are - attached only to the upper level of AND-condition cond. So they - should be pushed down to the inner levels of cond AND-condition - if needed. It is done by propagate_new_equalities(). + of the merge of multiple equalities. */ COND_EQUAL *cond_equal= &((Item_cond_and *) cond)->m_cond_equal; List<Item_equal> *cond_equalities= &cond_equal->current_level; List<Item> *and_args= ((Item_cond_and *)cond)->argument_list(); and_args->disjoin((List<Item> *) cond_equalities); - and_args->append(&new_conds); while ((equality= it++)) { @@ -5692,22 +5688,40 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, List_iterator_fast<Item_equal> ei(*cond_equalities); while ((equality= ei++)) { - if (equality->const_item() && !equality->val_int()) - is_simplified_cond= true; - equality->fixed= 0; + equality->unfix_fields(); if (equality->fix_fields(thd, NULL)) return NULL; } + li.rewind(); + while ((item=li++)) + { + /** + If and_new_conditions_to_optimized_cond() is called for + HAVING pushdown optimization there can be some equalities + on inner levels of new_conds elements that are still not + transformed into the multiple equalities. + To transform them build_equal_items() is called. + */ + if (thd->having_pushdown && + item->type() == Item::COND_ITEM && + ((Item_cond *)item)->functype() == Item_func::COND_OR_FUNC) + { + COND_EQUAL *cond_eq_or= 0; + item= item->build_equal_items(thd, + &((Item_cond_and *) cond)->m_cond_equal, + MY_TEST(OPT_LINK_EQUAL_FIELDS), + &cond_eq_or); + item->unfix_fields(); + if (item->fix_fields(thd, NULL)) + return NULL; + } + 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); *cond_eq= &((Item_cond_and *) cond)->m_cond_equal; - - propagate_new_equalities(thd, cond, cond_equalities, - cond_equal->upper_levels, - &is_simplified_cond); - cond= cond->propagate_equal_fields(thd, - Item::Context_boolean(), - cond_equal); } else { @@ -5737,67 +5751,104 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, new_conds_list.push_back(cond, thd->mem_root)) return NULL; - if (new_conds.elements > 0) - { - li.rewind(); - while ((item=li++)) - { - if (!item->is_fixed() && item->fix_fields(thd, NULL)) - return NULL; - if (item->const_item() && !item->val_int()) - is_simplified_cond= true; - } - new_conds_list.append(&new_conds); - } - if (is_mult_eq) { Item_equal *eq_cond= (Item_equal *)cond; eq_cond->upper_levels= 0; eq_cond->merge_into_list(thd, &new_cond_equal.current_level, false, false); + } - while ((equality= it++)) - { - if (equality->const_item() && !equality->val_int()) - is_simplified_cond= true; - } + List_iterator_fast<Item_equal> ei(new_cond_equal.current_level); + while ((equality=ei++)) + { + equality->unfix_fields(); + if (equality->fix_fields(thd, NULL)) + return NULL; + } - if (new_cond_equal.current_level.elements + - new_conds_list.elements == 1) + Item_cond_and *and_cond= 0; + COND_EQUAL *inherited= 0; + if (new_conds_list.elements + + new_conds.elements + + new_cond_equal.current_level.elements > 1) + { + and_cond= new (thd->mem_root) Item_cond_and(thd); + and_cond->m_cond_equal.copy(new_cond_equal); + inherited= &and_cond->m_cond_equal; + } + + li.rewind(); + while ((item=li++)) + { + /** + If and_new_conditions_to_optimized_cond() is called for + HAVING pushdown optimization there can be some equalities + on inner levels of new_conds elements that are still not + transformed into the multiple equalities. + To transform them build_equal_items() is called. + */ + if (thd->having_pushdown && + item->type() == Item::COND_ITEM && + ((Item_cond *)item)->functype() == Item_func::COND_OR_FUNC) { - it.rewind(); - equality= it++; - equality->fixed= 0; - if (equality->fix_fields(thd, NULL)) + COND_EQUAL *cond_eq_or= 0; + item= item->build_equal_items(thd, + inherited, + MY_TEST(OPT_LINK_EQUAL_FIELDS), + &cond_eq_or); + item->unfix_fields(); + if (item->fix_fields(thd, NULL)) return NULL; } - (*cond_eq)->copy(new_cond_equal); + 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); - if (new_conds_list.elements > 1) + if (and_cond) { - Item_cond_and *and_cond= - new (thd->mem_root) Item_cond_and(thd, new_conds_list); - - and_cond->m_cond_equal.copy(new_cond_equal); + and_cond->argument_list()->append(&new_conds_list); cond= (Item *)and_cond; - *cond_eq= &((Item_cond_and *)cond)->m_cond_equal; + *cond_eq= &((Item_cond_and *) cond)->m_cond_equal; } else { List_iterator_fast<Item> iter(new_conds_list); cond= iter++; + if (cond->type() == Item::FUNC_ITEM && + ((Item_func *)cond)->functype() == Item_func::MULT_EQUAL_FUNC) + { + if (!(*cond_eq)) + *cond_eq= new COND_EQUAL(); + (*cond_eq)->copy(new_cond_equal); + } + else + *cond_eq= 0; } + } - if (!cond->is_fixed() && cond->fix_fields(thd, NULL)) - return NULL; + if (!cond) + return NULL; - if (new_cond_equal.current_level.elements > 0) - cond= cond->propagate_equal_fields(thd, - Item::Context_boolean(), - &new_cond_equal); + if (cond->fix_fields_if_needed(thd, NULL)) + return NULL; + + if (*cond_eq) + { + /** + The multiple equalities are attached only to the upper level + of AND-condition cond. So they hould be pushed down to the + inner levels of cond AND-condition if needed. + */ + propagate_new_equalities(thd, cond, + &(*cond_eq)->current_level, + 0, + &is_simplified_cond); + cond= cond->propagate_equal_fields(thd, + Item::Context_boolean(), + *cond_eq); } /* @@ -5805,7 +5856,7 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, true or false equalities the method calls removes_eq_cond() to remove them from cond and set the cond_value to the appropriate value. */ - if (is_simplified_cond) + if (cond && is_simplified_cond) cond= cond->remove_eq_conds(thd, cond_value, true); return cond; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index c204f96f303..1b1e0c9f135 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -9721,11 +9721,11 @@ void st_select_lex::pushdown_cond_into_where_clause(THD *thd, Item *cond, @param cond The condition that should be marked (or its subformulas) @details - In the case when OR-condition can be pushed into the HAVING clause - of the materialized derived table/view/IN subquery and some of - its parts can be pushed into the WHERE clause it can cause - repeatable pushdown in the pushdown from HAVING into WHERE clause. - Example: + Consider pushdown into the materialized derived table/view/IN subquery. + Consider OR condition that can be pushed into HAVING and some + parts of this OR condition that can be pushed into WHERE. + + On example: SELECT * FROM t1, @@ -9736,8 +9736,15 @@ void st_select_lex::pushdown_cond_into_where_clause(THD *thd, Item *cond, WHERE ((dt.m_c>10) AND (dt.a>2)) OR ((dt.m_c<7) and (dt.a<3)) AND (t1.a=v1.a); - after the pushdown into the materialized views/derived tables optimization - is done: + + Here ((dt.m_c>10) AND (dt.a>2)) OR ((dt.m_c<7) and (dt.a<3)) or1 + can be pushed down into the HAVING of the materialized + derived table dt. + + (dt.a>2) OR (dt.a<3) part of or1 depends only on grouping fields + of dt and can be pushed into WHERE. + + As a result: SELECT * FROM t1, @@ -9750,15 +9757,15 @@ void st_select_lex::pushdown_cond_into_where_clause(THD *thd, Item *cond, WHERE ((dt.m_c>10) AND (dt.a>2)) OR ((dt.m_c<7) and (dt.a<3)) AND (t1.a=v1.a); - In the optimization stage for the select that defines derived table - in the pushdown from HAVING into WHERE optimization - (dt.a>2) OR (dt.a<3) will be again extracted from - ((dt.m_c>10) AND (dt.a>2)) OR ((dt.m_c<7) and (dt.a<3)) - and pushed into the WHERE clause of the select that defines derived table. - To avoid it after conditions are pushed into the materialized derived - tables/views or IN subqueries OR-conditions that were pushed are marked - with NO_EXTRACTION_FL flag to avoid repeatable pushdown. + Here (dt.a>2) OR (dt.a<3) also remains in HAVING of dt. + When SELECT that defines df is processed HAVING pushdown optimization + is made. In HAVING pushdown optimization it will extract + (dt.a>2) OR (dt.a<3) condition from or1 again and push it into WHERE. + This will cause duplicate conditions in WHERE of dt. + + To avoid repeatable pushdown such OR conditions as or1 describen + above are marked with NO_EXTRACTION_FL. */ void st_select_lex::mark_or_conds_to_avoid_pushdown(Item *cond) @@ -9786,31 +9793,53 @@ void st_select_lex::mark_or_conds_to_avoid_pushdown(Item *cond) /** @brief - Gets conditions that can be pushed down for pushdown from HAVING into WHERE + Get conditions that can be pushed from HAVING into WHERE - @param thd The thread handle - @param cond The condition from which the condition depended on grouping - fields is to be extracted - @param checker The checker callback function to be applied to the nodes - of the tree of the object + @param thd The thread handle + @param cond The condition from which the condition that + can be pushed is extracted + @param conds_moved_from_where The map of conditions that are extracted from + WHERE @details - The method finds out what conditions can be extracted from cond depended - only on the grouping fields of this SELECT or fields equal to them. - If the condition that can be pushed is AND-condition it is splitted up - and for each its element it is checked if it can be pushed. - Pushable elements are attached to the attach_to_conds list. - If the condition isn't AND-condition it is entirely pushed into - the attach_to_conds list. If the condition that is extracted is a multiple - equality it is transformed into the set of equalities. - - attach_to_conds list is created to be passed to - and_new_conditions_to_optimized_cond() method so extracted conditions can - be joined to the already optimized WHERE clause in the right way. - - @note - The method is similar to st_select_lex::build_cond_for_grouping_fields() and - Item::build_pushable_cond(). + The method collects in attach_to_conds list conditions from cond + that can be pushed from HAVING into WHERE. + Conditions that can be pushed are collected in attach_to_conds in this way: + 1. if cond is an AND condition its parts that can be pushed into WHERE + are added to attach_to_conds list separately. + 2. in all other cases conditions are pushed into the list entirely. + + Conditions that can be pushed were marked with FULL_EXTRACTION_FL in + check_cond_extraction_for_grouping_fields() method. + Conditions that can't be pushed were marked with NO_EXTRACTION_FL. + Conditions which parts can be pushed weren't marked. + + There are two types of conditions that can be pushed: + 1. Condition that can be simply moved from HAVING + (if cond is marked with FULL_EXTRACTION_FL or + cond is an AND condition and some of its parts are marked with + FULL_EXTRACTION_FL) + In this case condition is transformed and pushed into attach_to_conds + list. + 2. Part of some other condition c1 that can't be entirely pushed + (if с1 isn't marked with any flag). + + For example: + + SELECT t1.a,MAX(t1.b),t1.c + FROM t1 + GROUP BY t1.a + HAVING ((t1.a > 5) AND (t1.c < 3)) OR (t1.a = 3); + + Here (t1.a > 5) OR (t1.a = 3) in HAVING can be pushed into WHERE. + + In this case build_pushable_cond() is called for c1. + This method builds a clone of the c1 part that is marked with + FULL_EXTRACTION_FL flag. This clone is added to attach_to_conds list. + + Transformation mentioned above is made with multiple_equality_transformer + transformer. It transforms all multiple equalities in the extracted + condition into the set of equalities. @retval true - if an error occurs @@ -9819,92 +9848,92 @@ void st_select_lex::mark_or_conds_to_avoid_pushdown(Item *cond) bool st_select_lex::build_pushable_cond_for_having_pushdown(THD *thd, - Item *cond) + Item *cond, Bitmap<64> *conds_moved_from_where) { + conds_moved_from_where->clear_all(); + List<Item> equalities; Pushdown_checker checker= &Item::pushable_equality_checker_for_having_pushdown; - - bool is_multiple_equality= cond->type() == Item::FUNC_ITEM && - ((Item_func*) cond)->functype() == Item_func::MULT_EQUAL_FUNC; + bool and_cond= + (cond->type() == Item::COND_ITEM && + ((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC) + ? true : false; if (cond->get_extraction_flag() == NO_EXTRACTION_FL) return false; - if (cond->type() == Item::COND_ITEM) + if (cond->get_extraction_flag() == FULL_EXTRACTION_FL) { - bool cond_and= false; - if (((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC) - cond_and= true; - List<Item> equalities; - List<Item> new_conds; - List_iterator<Item> li(*((Item_cond*) cond)->argument_list()); - Item *item; - - while ((item=li++)) + Item *result= cond->transform(thd, + &Item::multiple_equality_transformer, + (uchar *)this); + if (!result) + return true; + if (!and_cond) { - if (item->get_extraction_flag() == NO_EXTRACTION_FL) - continue; - - if (item->type() == Item::FUNC_ITEM && - ((Item_func*) item)->functype() == Item_func::MULT_EQUAL_FUNC) + if (attach_to_conds.push_back(result, thd->mem_root)) + return true; + conds_moved_from_where->set_bit(attach_to_conds.elements - 1); + } + else + { + List_iterator<Item> li(*((Item_cond*) cond)->argument_list()); + Item *item; + while ((item=li++)) { - equalities.empty(); - if (((Item_equal*) item)->create_pushable_equalities(thd, &equalities, - checker, (uchar *)this)) + if (attach_to_conds.push_back(item, thd->mem_root)) return true; - if (equalities.elements != 0) - { - if (cond_and) - new_conds.append(&equalities); - else - { - Item_cond_and *new_cond= - new (thd->mem_root) Item_cond_and(thd, equalities); - if (!new_cond || new_conds.push_back(new_cond, thd->mem_root)) - return true; - } - } - else if (!cond_and) - return true; - continue; + conds_moved_from_where->set_bit(attach_to_conds.elements - 1); } + } + return false; + } - Item *fix= item->build_pushable_cond(thd, checker, (uchar *)this); - - if (!fix && !cond_and) - { - attach_to_conds.empty(); - return false; - } + if (cond->type() == Item::COND_ITEM) + { + if (!and_cond) + { + Item *fix= cond->build_pushable_cond(thd, checker, (uchar *)this); if (!fix) - continue; - - if (new_conds.push_back(fix, thd->mem_root)) + return false; + if (attach_to_conds.push_back(fix, thd->mem_root)) return true; } - if (!cond_and) + else { - Item_cond_or *new_cond= new (thd->mem_root) Item_cond_or(thd, new_conds); - if (attach_to_conds.push_back(new_cond, thd->mem_root)) - return true; + List_iterator<Item> li(*((Item_cond*) cond)->argument_list()); + Item *item; + while ((item=li++)) + { + if (item->get_extraction_flag() == NO_EXTRACTION_FL) + continue; + else if (item->get_extraction_flag() == FULL_EXTRACTION_FL) + { + Item *result= item->transform(thd, + &Item::multiple_equality_transformer, + (uchar *)item); + if (!result || attach_to_conds.push_back(result, thd->mem_root)) + return true; + conds_moved_from_where->set_bit(attach_to_conds.elements - 1); + } + else + { + Item *fix= item->build_pushable_cond(thd, checker, (uchar *)this); + if (!fix) + continue; + if (attach_to_conds.push_back(fix, thd->mem_root)) + return true; + } + } } - else - attach_to_conds.append(&new_conds); - } - else if (is_multiple_equality) - { - List<Item> equalities; - Item_equal *item_equal= (Item_equal *)cond; - if (item_equal->create_pushable_equalities(thd, &equalities, - checker, (uchar *)this)) - return true; - attach_to_conds.append(&equalities); return false; } - else if (cond->get_extraction_flag() != NO_EXTRACTION_FL) + else { - Item *copy= cond->build_clone(thd); - if (attach_to_conds.push_back(copy, thd->mem_root)) + Item *fix= cond->build_pushable_cond(thd, checker, (uchar *)this); + if (!fix) + return false; + if (attach_to_conds.push_back(fix, thd->mem_root)) return true; } return false; @@ -9912,7 +9941,7 @@ st_select_lex::build_pushable_cond_for_having_pushdown(THD *thd, /** - Check if the item is equal to some field in Field_pair 'field_pair' + Check if item is equal to some field in Field_pair 'field_pair' from 'pair_list' and return found 'field_pair' if it exists. */ @@ -9938,15 +9967,17 @@ Field_pair *get_corresponding_field_pair(Item *item, /** @brief - Collect fields in multiple equalities usable for pushdown from having + Collect fields from multiple equalities which fields are equal to grouping @param thd The thread handle @details - This method looks through the multiple equalities of the WHERE clause - trying to find any of them whose fields are used in the GROUP BY of the - SELECT. Any field from these multiple equality is included into the - the list of fields against which any candidate for pushing is checked. + This method checks multiple equalities of the WHERE clause of this SELECT + trying to find those of them which has at least one element that + participates in the GROUP BY of this select. + + If such multiple equality is found the method saves information on + all elements of this multiple equality. @retval true - if an error occurs @@ -9987,48 +10018,6 @@ bool st_select_lex::collect_fields_equal_to_grouping(THD *thd) return false; } -/** - @brief - Cleanup and fix of the condition that is ready to be pushed down - - @param thd The thread handle - @param cond The condition to be processed - - @details - This method recursively traverses cond making cleanup and fix - where needed. - There is no need to make cleanup and fix for multiple equalities as - they are created so they can be immediately pushed down. - - @retval - true - if an error occurs - false - otherwise -*/ - -static -bool cleanup_condition_pushed_from_having(THD *thd, Item *cond) -{ - if (cond->type() == Item::FUNC_ITEM && - ((Item_func*) cond)->functype() == Item_func::MULT_EQUAL_FUNC) - return false; - - if (cond->type() == Item::COND_ITEM) - { - List_iterator_fast<Item> it(*((Item_cond *)cond)->argument_list()); - Item *item; - - while ((item=it++)) - cleanup_condition_pushed_from_having(thd, item); - } - else - { - cond->walk(&Item::cleanup_excluding_const_fields_processor, 0, 0); - if (cond->fix_fields(thd, NULL)) - return true; - } - return false; -} - /** @brief @@ -10038,17 +10027,8 @@ bool cleanup_condition_pushed_from_having(THD *thd, Item *cond) @param cond The condition which subformulas are to be removed @details - The function behavior is similar to remove_pushed_top_conjuncts() - except the case when 'cond' is the AND-condition. - As in the pushdown from HAVING into WHERE conditions are not just cloned - so they can be later pushed down as it is for pushdown into materialized - derived tables/views or IN subqueries, but also should be removed from - the HAVING clause. - The multiple equalities of the HAVING clause are not removed in this - function, but rather marked as to be removed later. Their removal is - done in substitute_for_best_equal_field() called for HAVING at the moment - when all multiple equalities referencing the top level multiple equalities - have been already eliminated. + This method removes from cond all subformulas that can be moved from HAVING + into WHERE. @retval condition without removed subformulas @@ -10057,43 +10037,44 @@ bool cleanup_condition_pushed_from_having(THD *thd, Item *cond) Item *remove_pushed_top_conjuncts_for_having(THD *thd, Item *cond) { + if (cond->get_extraction_flag() == NO_EXTRACTION_FL) + { + cond->clear_extraction_flag(); + return cond; + } if (cond->get_extraction_flag() == FULL_EXTRACTION_FL) { cond->clear_extraction_flag(); - if (cond->type() == Item::FUNC_ITEM && - ((Item_func*) cond)->functype() == Item_func::MULT_EQUAL_FUNC) - { - cond->set_extraction_flag(DELETION_FL); - return cond; - } return 0; } - if (cond->type() != Item::COND_ITEM) - return cond; - if (((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC) + if (cond->type() == Item::COND_ITEM && + ((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC) { - List<Item> *cond_arg_list= ((Item_cond_and *)cond)->argument_list(); - List_iterator<Item> li(*cond_arg_list); + List_iterator<Item> li(*((Item_cond*) cond)->argument_list()); Item *item; - while ((item= li++)) + while ((item=li++)) { - if (item->get_extraction_flag() == FULL_EXTRACTION_FL) - { + if (item->get_extraction_flag() == NO_EXTRACTION_FL) item->clear_extraction_flag(); + else if (item->get_extraction_flag() == FULL_EXTRACTION_FL) + { if (item->type() == Item::FUNC_ITEM && ((Item_func*) item)->functype() == Item_func::MULT_EQUAL_FUNC) item->set_extraction_flag(DELETION_FL); else + { + item->clear_extraction_flag(); li.remove(); + } } } - switch (cond_arg_list->elements) + switch (((Item_cond*) cond)->argument_list()->elements) { case 0: return 0; case 1: - return (cond_arg_list->head()); + return (((Item_cond*) cond)->argument_list()->head()); default: return cond; } @@ -10104,19 +10085,20 @@ Item *remove_pushed_top_conjuncts_for_having(THD *thd, Item *cond) /** @brief - Extract condition that can be pushed from HAVING clause into WHERE clause + Extract condition that can be pushed from HAVING into WHERE - @param thd the thread handle - @param having the HAVING clause of this select - @param having_equal multiple equalities of HAVING + @param thd The thread handle + @param having The HAVING clause of this select + @param having_equal Multiple equalities of HAVING @details - This function builds the most restrictive condition depending only on - the fields used in the GROUP BY of this select (directly or indirectly + This method builds a set of the conditions dependent only on + fields used in the GROUP BY of this select (directly or indirectly through equality) that can be extracted from the HAVING clause of this - select and pushes it into the WHERE clause of this select. + select. It saves these conditions into attach_to_conds list and removes + some of them from HAVING. - Example of the transformation: + Example of the HAVING pushdown transformation: SELECT t1.a,MAX(t1.b) FROM t1 @@ -10131,14 +10113,23 @@ Item *remove_pushed_top_conjuncts_for_having(THD *thd, Item *cond) GROUP BY t1.a HAVING (MAX(c)>12); + In this method (t1.a>2) is not attached to the WHERE clause. + It is pushed into the attach_to_conds list to be attached to + the WHERE clause later. + In details: 1. Collect fields used in the GROUP BY grouping_fields of this SELECT 2. Collect fields equal to grouping_fields from the WHERE clause of this SELECT and add them to the grouping_fields list. 3. Extract the most restrictive condition from the HAVING clause of this select that depends only on the grouping fields (directly or indirectly - through equality). Store it in the attach_to_conds list. + through equality). + + Partition it in parts if possible and store these parts in + the attach_to_conds list. + Condition is partitioned if it is AND condition. 4. Remove pushable conditions from the HAVING clause if it's possible. + 5. Unwrap fields of pushable conditions so they can be pushed into WHERE. @note This method is similar to st_select_lex::pushdown_cond_into_where_clause(). @@ -10169,14 +10160,16 @@ Item *st_select_lex::pushdown_from_having_into_where(THD *thd, Item *having) /* 3. Extract the most restrictive condition from the HAVING clause of this select that depends only on the grouping fields (directly or indirectly - through equality). Store it in the attach_to_conds list. + through equality). */ thd->having_pushdown= true; List_iterator_fast<Item> it(attach_to_conds); Item *item; + Bitmap<64> conds_moved_from_where; check_cond_extraction_for_grouping_fields(thd, having, &Item::dep_on_grouping_fields_checker_for_having_pushdown); - if (build_pushable_cond_for_having_pushdown(thd, having)) + if (build_pushable_cond_for_having_pushdown(thd, having, + &conds_moved_from_where)) { attach_to_conds.empty(); goto exit; @@ -10187,15 +10180,27 @@ Item *st_select_lex::pushdown_from_having_into_where(THD *thd, Item *having) 4. Remove pushable conditions from the HAVING clause if it's possible. */ having= remove_pushed_top_conjuncts_for_having(thd, having); - it.rewind(); + uint i= 0; while ((item=it++)) { - if (cleanup_condition_pushed_from_having(thd, item)) + /* + 5. Unwrap fields of pushable conditions so they can be pushed + into WHERE. + */ + item= item->transform(thd, + &Item::field_transformer_for_having_pushdown, + (uchar *)this); + + if (conds_moved_from_where.is_set(i)) + item->walk(&Item::cleanup_processor, 0, 0); + item->unfix_fields(); + if (item->fix_fields_if_needed(thd, NULL)) { attach_to_conds.empty(); goto exit; } + i++; } /* Refresh having_equal as some of the multiple equalities of @@ -10218,7 +10223,6 @@ Item *st_select_lex::pushdown_from_having_into_where(THD *thd, Item *having) } exit: thd->lex->current_select= save_curr_select; - thd->having_pushdown= false; return having; } diff --git a/sql/sql_lex.h b/sql/sql_lex.h index b78a010d4b7..b62841284a2 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1522,7 +1522,7 @@ class st_select_lex: public st_select_lex_node { return !olap && !explicit_limit && !tvc; } bool build_pushable_cond_for_having_pushdown(THD *thd, - Item *cond); + Item *cond, Bitmap<64> *conds_moved_from_where); void pushdown_cond_into_where_clause(THD *thd, Item *extracted_cond, Item **remaining_cond, Item_transformer transformer, diff --git a/sql/sql_select.cc b/sql/sql_select.cc index b5b69a8f6c0..aef2c01c8a3 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1949,10 +1949,9 @@ JOIN::optimize_inner() conds= and_new_conditions_to_optimized_cond(thd, conds, &cond_equal, select_lex->attach_to_conds, &cond_value, true); - if (conds && !conds->is_fixed() && conds->fix_fields(thd, &conds)) - DBUG_RETURN(1); sel->attach_to_conds.empty(); } + thd->having_pushdown= false; } if (optimizer_flag(thd, OPTIMIZER_SWITCH_COND_PUSHDOWN_FOR_SUBQUERY))