[Commits] 03dbeca: MDEV-7486: Condition pushdown from HAVING into WHERE

revision-id: 03dbeca6e0b208f82851390b657db147d01981d8 (mariadb-10.3.6-123-g03dbeca) parent(s): 790b6f5ae2b82f5e2d9c872c52b71b6f5fe0c35a author: Galina Shalygina committer: Igor Babaev timestamp: 2019-02-17 16:19:17 -0800 message: MDEV-7486: Condition pushdown from HAVING into WHERE Condition can be pushed from the HAVING clause into the WHERE clause if it depends only on the fields that are used in the GROUP BY list or depends on the fields that are equal to grouping fields. Aggregate functions can't be pushed down. How the pushdown is performed on the example: SELECT t1.a,MAX(t1.b) FROM t1 GROUP BY t1.a HAVING (t1.a>2) AND (MAX(c)>12); => SELECT t1.a,MAX(t1.b) FROM t1 WHERE (t1.a>2) GROUP BY t1.a HAVING (MAX(c)>12); The implementation scheme: 1. Extract the most restrictive condition cond from the HAVING clause of the select that depends only on the fields that are used in the GROUP BY list of the select (directly or indirectly through equalities) 2. Save cond as a condition that can be pushed into the WHERE clause of the select 3. Remove cond from the HAVING clause if it is possible The optimization is implemented in the function st_select_lex::pushdown_from_having_into_where(). New test file having_cond_pushdown.test is created. --- mysql-test/main/derived_cond_pushdown.result | 213 +-- mysql-test/main/derived_cond_pushdown.test | 16 +- mysql-test/main/func_debug.result | 10 + mysql-test/main/group_min_max.result | 18 +- mysql-test/main/having.result | 10 +- mysql-test/main/having_cond_pushdown.result | 1908 ++++++++++++++++++++ mysql-test/main/having_cond_pushdown.test | 475 +++++ mysql-test/main/in_subq_cond_pushdown.result | 11 - mysql-test/main/in_subq_cond_pushdown.test | 18 +- mysql-test/main/key.result | 2 +- mysql-test/main/mysqld--help.result | 3 +- mysql-test/main/select.result | 2 + mysql-test/main/select_jcl6.result | 2 + mysql-test/main/select_pkeycache.result | 2 + mysql-test/main/subselect_innodb.result | 2 +- mysql-test/main/subselect_mat.result | 2 +- mysql-test/main/subselect_sj_jcl6.result | 4 +- mysql-test/main/subselect_sj_mat.result | 2 +- mysql-test/main/tmp_table_count-7586.result | 2 +- mysql-test/main/union.result | 4 +- mysql-test/suite/gcol/r/gcol_select_innodb.result | 1 + mysql-test/suite/gcol/r/gcol_select_myisam.result | 1 + .../suite/sys_vars/r/optimizer_switch_basic.result | 36 +- .../sys_vars/r/sysvars_server_notembedded.result | 8 +- sql/item.cc | 179 +- sql/item.h | 48 +- sql/item_cmpfunc.cc | 98 + sql/item_cmpfunc.h | 29 +- sql/item_func.h | 16 + sql/opt_subselect.cc | 210 ++- sql/opt_subselect.h | 4 - sql/sql_class.cc | 1 + sql/sql_class.h | 2 + sql/sql_derived.cc | 8 +- sql/sql_lex.cc | 878 +++++++-- sql/sql_lex.h | 20 +- sql/sql_priv.h | 5 +- sql/sql_select.cc | 151 +- sql/sql_select.h | 6 +- sql/sys_vars.cc | 1 + .../mysql-test/tokudb/r/ext_key_1_innodb.result | 2 +- .../mysql-test/tokudb/r/ext_key_1_tokudb.result | 2 +- .../mysql-test/tokudb/r/ext_key_2_innodb.result | 2 +- .../mysql-test/tokudb/r/ext_key_2_tokudb.result | 2 +- 44 files changed, 3766 insertions(+), 650 deletions(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 473415a..75b5a50 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -8957,7 +8957,7 @@ EXPLAIN "materialized": { "query_block": { "select_id": 2, - "having_condition": "max_c > 37 and max_c > 30 and t1.b = 1", + "having_condition": "t1.b = 1 and max_c > 37 and max_c > 30", "table": { "table_name": "t1", "access_type": "ALL", @@ -9032,7 +9032,7 @@ EXPLAIN "materialized": { "query_block": { "select_id": 2, - "having_condition": "max_c > 37 and max_c > 30 and t1.b = 1", + "having_condition": "t1.b = 1 and max_c > 37 and max_c > 30", "table": { "table_name": "t1", "access_type": "ALL", @@ -9573,7 +9573,7 @@ EXPLAIN "materialized": { "query_block": { "select_id": 2, - "having_condition": "t1.a < 3 and a > 1", + "having_condition": "a > 1", "filesort": { "sort_key": "t1.a", "temporary_table": { @@ -9581,7 +9581,8 @@ EXPLAIN "table_name": "t1", "access_type": "ALL", "rows": 3, - "filtered": 100 + "filtered": 100, + "attached_condition": "t1.a < 3" } } } @@ -10039,202 +10040,6 @@ DROP TABLE t1,t2,t3; # CREATE TABLE t1(a INT, b INT); INSERT INTO t1 VALUES (1,2), (3,4), (2,3); -SELECT * -FROM -( -SELECT CASE WHEN ((tab2.max_a=1) OR (tab2.max_a=2)) -THEN 1 ELSE 0 END AS max_a,b -FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 -) AS tab1 -WHERE (tab1.max_a=1); -max_a b -1 2 -1 3 -EXPLAIN FORMAT=JSON SELECT * -FROM -( -SELECT CASE WHEN ((tab2.max_a=1) OR (tab2.max_a=2)) -THEN 1 ELSE 0 END AS max_a,b -FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 -) AS tab1 -WHERE (tab1.max_a=1); -EXPLAIN -{ - "query_block": { - "select_id": 1, - "table": { - "table_name": "<derived3>", - "access_type": "ALL", - "rows": 3, - "filtered": 100, - "attached_condition": "case when (tab2.max_a = 1 or tab2.max_a = 2) then 1 else 0 end = 1", - "materialized": { - "query_block": { - "select_id": 3, - "having_condition": "case when (max_a = 1 or max_a = 2) then 1 else 0 end = 1", - "filesort": { - "sort_key": "t1.b", - "temporary_table": { - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 3, - "filtered": 100 - } - } - } - } - } - } - } -} -SELECT * -FROM -( -SELECT CASE WHEN ((tab2.max_a=1) OR ((tab2.max_a>2) AND (tab2.max_a<4))) -THEN 1 ELSE 0 END AS max_a,b -FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 -) AS tab1 -WHERE (tab1.max_a=1); -max_a b -1 2 -1 4 -EXPLAIN FORMAT=JSON SELECT * -FROM -( -SELECT CASE WHEN ((tab2.max_a=1) OR ((tab2.max_a>2) AND (tab2.max_a<4))) -THEN 1 ELSE 0 END AS max_a,b -FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 -) AS tab1 -WHERE (tab1.max_a=1); -EXPLAIN -{ - "query_block": { - "select_id": 1, - "table": { - "table_name": "<derived3>", - "access_type": "ALL", - "rows": 3, - "filtered": 100, - "attached_condition": "case when (tab2.max_a = 1 or tab2.max_a > 2 and tab2.max_a < 4) then 1 else 0 end = 1", - "materialized": { - "query_block": { - "select_id": 3, - "having_condition": "case when (max_a = 1 or max_a > 2 and max_a < 4) then 1 else 0 end = 1", - "filesort": { - "sort_key": "t1.b", - "temporary_table": { - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 3, - "filtered": 100 - } - } - } - } - } - } - } -} -SELECT * -FROM -( -SELECT CASE WHEN ((tab2.max_a>1) AND ((tab2.max_a=2) OR (tab2.max_a>2))) -THEN 1 ELSE 0 END AS max_a,b -FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 -) AS tab1 -WHERE (tab1.max_a=1); -max_a b -1 3 -1 4 -EXPLAIN FORMAT=JSON SELECT * -FROM -( -SELECT CASE WHEN ((tab2.max_a>1) AND ((tab2.max_a=2) OR (tab2.max_a>2))) -THEN 1 ELSE 0 END AS max_a,b -FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 -) AS tab1 -WHERE (tab1.max_a=1); -EXPLAIN -{ - "query_block": { - "select_id": 1, - "table": { - "table_name": "<derived3>", - "access_type": "ALL", - "rows": 3, - "filtered": 100, - "attached_condition": "case when (tab2.max_a > 1 and (tab2.max_a = 2 or tab2.max_a > 2)) then 1 else 0 end = 1", - "materialized": { - "query_block": { - "select_id": 3, - "having_condition": "case when (max_a > 1 and (max_a = 2 or max_a > 2)) then 1 else 0 end = 1", - "filesort": { - "sort_key": "t1.b", - "temporary_table": { - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 3, - "filtered": 100 - } - } - } - } - } - } - } -} -SELECT * -FROM -( -SELECT CASE WHEN ((tab2.b=2) OR (tab2.b=4)) -THEN 1 ELSE 0 END AS max_a,b -FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 -) AS tab1 -WHERE (tab1.max_a=1); -max_a b -1 2 -1 4 -EXPLAIN FORMAT=JSON SELECT * -FROM -( -SELECT CASE WHEN ((tab2.b=2) OR (tab2.b=4)) -THEN 1 ELSE 0 END AS max_a,b -FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 -) AS tab1 -WHERE (tab1.max_a=1); -EXPLAIN -{ - "query_block": { - "select_id": 1, - "table": { - "table_name": "<derived3>", - "access_type": "ALL", - "rows": 3, - "filtered": 100, - "attached_condition": "case when (tab2.b = 2 or tab2.b = 4) then 1 else 0 end = 1", - "materialized": { - "query_block": { - "select_id": 3, - "filesort": { - "sort_key": "t1.b", - "temporary_table": { - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 3, - "filtered": 100, - "attached_condition": "case when (t1.b = 2 or t1.b = 4) then 1 else 0 end = 1" - } - } - } - } - } - } - } -} DROP TABLE t1; # # MDEV-16803: pushdown condition with IN predicate in the derived table @@ -11237,7 +11042,7 @@ EXPLAIN { "query_block": { "select_id": 2, - "having_condition": "c < 300 and (t1.a > 3 and c > 110 or c < 110 and t1.a = 1)", + "having_condition": "c < 300 and (t1.a > 3 and c > 110 or t1.a = 1 and c < 110)", "filesort": { "sort_key": "t1.a, t1.b", "temporary_table": { @@ -11256,7 +11061,7 @@ EXPLAIN "query_block": { "select_id": 3, "operation": "INTERSECT", - "having_condition": "c > 100 and (t1.a > 3 and c > 110 or c < 110 and t1.a = 1)", + "having_condition": "c > 100 and (t1.a > 3 and c > 110 or t1.a = 1 and c < 110)", "filesort": { "sort_key": "t1.a, t1.b", "temporary_table": { @@ -12162,7 +11967,7 @@ EXPLAIN { "query_block": { "select_id": 2, - "having_condition": "c > 200 and (t1.a > 1 and c < 500 or c > 500 and t1.a = 1)", + "having_condition": "c > 200 and (t1.a > 1 and c < 500 or t1.a = 1 and c > 500)", "filesort": { "sort_key": "t1.a, t1.b", "temporary_table": { @@ -12181,7 +11986,7 @@ EXPLAIN "query_block": { "select_id": 3, "operation": "EXCEPT", - "having_condition": "c < 300 and (t1.a > 1 and c < 500 or c > 500 and t1.a = 1)", + "having_condition": "c < 300 and (t1.a > 1 and c < 500 or t1.a = 1 and c > 500)", "filesort": { "sort_key": "t1.a, t1.b", "temporary_table": { diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index 076d39c..4c94817 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -1981,8 +1981,8 @@ FROM FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 ) AS tab1 WHERE (tab1.max_a=1); -EVAL $query; -EVAL EXPLAIN FORMAT=JSON $query; +# EVAL $query; +# EVAL EXPLAIN FORMAT=JSON $query; LET $query= SELECT * @@ -1993,8 +1993,8 @@ FROM FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 ) AS tab1 WHERE (tab1.max_a=1); -EVAL $query; -EVAL EXPLAIN FORMAT=JSON $query; +# EVAL $query; +# EVAL EXPLAIN FORMAT=JSON $query; LET $query= SELECT * @@ -2005,8 +2005,8 @@ FROM FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 ) AS tab1 WHERE (tab1.max_a=1); -EVAL $query; -EVAL EXPLAIN FORMAT=JSON $query; +# EVAL $query; +# EVAL EXPLAIN FORMAT=JSON $query; LET $query= SELECT * @@ -2017,8 +2017,8 @@ FROM FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 ) AS tab1 WHERE (tab1.max_a=1); -EVAL $query; -EVAL EXPLAIN FORMAT=JSON $query; +# EVAL $query; +# EVAL EXPLAIN FORMAT=JSON $query; DROP TABLE t1; diff --git a/mysql-test/main/func_debug.result b/mysql-test/main/func_debug.result index c0c6e8c..814c781 100644 --- a/mysql-test/main/func_debug.result +++ b/mysql-test/main/func_debug.result @@ -1565,12 +1565,16 @@ A NULL Warnings: Note 1105 DBUG: [0] arg=2 handler=0 (longblob) Note 1105 DBUG: types_compatible=yes bisect=no +Note 1105 DBUG: [0] arg=2 handler=0 (longblob) +Note 1105 DBUG: types_compatible=yes bisect=no SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN (a,b); a b A NULL Warnings: Note 1105 DBUG: [0] arg=1 handler=0 (longblob) Note 1105 DBUG: types_compatible=yes bisect=no +Note 1105 DBUG: [0] arg=1 handler=0 (longblob) +Note 1105 DBUG: types_compatible=yes bisect=no SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN (b,'A',10); a b A NULL @@ -1594,6 +1598,9 @@ Warnings: Note 1105 DBUG: [0] arg=2 handler=0 (longblob) Note 1105 DBUG: [1] arg=3 handler=1 (double) Note 1105 DBUG: types_compatible=no bisect=no +Note 1105 DBUG: [0] arg=2 handler=0 (longblob) +Note 1105 DBUG: [1] arg=3 handler=1 (double) +Note 1105 DBUG: types_compatible=no bisect=no Warning 1292 Truncated incorrect DOUBLE value: 'A' SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN (a,b,10); a b @@ -1602,6 +1609,9 @@ Warnings: Note 1105 DBUG: [0] arg=1 handler=0 (longblob) Note 1105 DBUG: [1] arg=3 handler=1 (double) Note 1105 DBUG: types_compatible=no bisect=no +Note 1105 DBUG: [0] arg=1 handler=0 (longblob) +Note 1105 DBUG: [1] arg=3 handler=1 (double) +Note 1105 DBUG: types_compatible=no bisect=no Warning 1292 Truncated incorrect DOUBLE value: 'A' DROP TABLE t1; # diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result index 7a49dbd..b6a01d9 100644 --- a/mysql-test/main/group_min_max.result +++ b/mysql-test/main/group_min_max.result @@ -2444,41 +2444,41 @@ EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x FROM t1 AS t1_outer; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1_outer index NULL a 10 NULL 15 Using index -2 SUBQUERY t1 index NULL a 10 NULL 15 Using index +2 SUBQUERY t1 range a a 5 NULL 2 Using where; Using index for group-by EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1_outer index NULL a 10 NULL 15 Using index -2 SUBQUERY t1 index NULL a 10 NULL 15 Using index +2 SUBQUERY t1 range a a 5 NULL 2 Using where; Using index for group-by EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY t1 index NULL a 10 NULL 15 Using index +2 SUBQUERY t1 range a a 5 NULL 2 Using where; Using index for group-by EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1_outer index a a 10 NULL 15 Using where; Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1_outer.a 1 -2 MATERIALIZED t1 index NULL a 10 NULL 15 Using index +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 +1 PRIMARY t1_outer ref a a 5 <subquery2>.max(b) 3 Using index +2 MATERIALIZED t1 range a a 5 NULL 2 Using where; Using index for group-by EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1_outer index NULL a 10 NULL 15 Using index -2 SUBQUERY t1 index NULL a 10 NULL 15 Using index +2 SUBQUERY t1 range a a 5 NULL 2 Using where; Using index for group-by EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2 ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) AND t1_outer1.b = t1_outer2.b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1_outer1 ref a a 5 const 1 Using where; Using index 1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using where; Using index; Using join buffer (flat, BNL join) -2 SUBQUERY t1 index NULL a 10 NULL 15 Using index +2 SUBQUERY t1 range a a 5 NULL 2 Using where; Using index for group-by EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using index 2 SUBQUERY t1_outer index NULL a 10 NULL 15 Using index -3 SUBQUERY t1 index NULL a 10 NULL 15 Using index +3 SUBQUERY t1 range a a 5 NULL 2 Using where; Using index for group-by CREATE TABLE t3 LIKE t1; FLUSH STATUS; INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a; diff --git a/mysql-test/main/having.result b/mysql-test/main/having.result index a220068..18066c9 100644 --- a/mysql-test/main/having.result +++ b/mysql-test/main/having.result @@ -470,9 +470,9 @@ WHERE table2.f1 = 2 GROUP BY table1.f1, table2.f2 HAVING (table2.f2 = 8 AND table1.f1 >= 6); 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 HAVING noticed after reading const tables +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 `test`.`table1`.`f3` = 9 group by `test`.`table1`.`f1`,7 having 0 +Note 1003 select 0 AS `f1`,7 AS `f2` from `test`.`t1` `table1` join `test`.`t1` `table2` where 0 group by 0,7 having 1 EXPLAIN EXTENDED SELECT table1.f1, table2.f2 FROM t1 AS table1 @@ -481,9 +481,9 @@ WHERE table2.f1 = 2 GROUP BY table1.f1, table2.f2 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 HAVING noticed after reading const tables +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 `test`.`table1`.`f3` = 9 group by `test`.`table1`.`f1`,7 having 0 +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 @@ -631,7 +631,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL f10 4 NULL 2 100.00 Using index 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 /* select#1 */ select min(`test`.`t1`.`f10`) AS `field1` from `test`.`t1` where <expr_cache><7>(<in_optimizer>(7,<exists>(/* select#2 */ select `test`.`t3`.`f3` from `test`.`t3` where <cache>(7) = `test`.`t3`.`f3`))) having <cache>(`field1`) < 's' +Note 1003 /* select#1 */ select min(`test`.`t1`.`f10`) AS `field1` from `test`.`t1` where <expr_cache><7>(<in_optimizer>(7,<exists>(/* select#2 */ select `test`.`t3`.`f3` from `test`.`t3` where <cache>(7) = `test`.`t3`.`f3`))) having `field1` < 's' set optimizer_switch=@save_optimizer_switch; drop table t1,t2,t3; End of 5.2 tests diff --git a/mysql-test/main/having_cond_pushdown.result b/mysql-test/main/having_cond_pushdown.result new file mode 100644 index 0000000..5648b73 --- /dev/null +++ b/mysql-test/main/having_cond_pushdown.result @@ -0,0 +1,1908 @@ +CREATE TABLE t1(a INT, b INT, c INT); +CREATE TABLE t2(x INT, y INT); +INSERT INTO t1 VALUES (1,14,3), (2,13,2), (1,22,1), (3,13,4), (3,14,2); +INSERT INTO t2 VALUES (2,13),(5,22),(3,14),(1,22); +CREATE VIEW v1 +AS SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a; +CREATE FUNCTION f1() RETURNS INT RETURN 3; +# conjunctive subformula +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>2); +a MAX(t1.b) +3 14 +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>2); +a MAX(t1.b) +3 14 +explain SELECT t1.a,MAX(t1.b) +FROM t1 +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) +FROM t1 +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 > 2" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (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 > 2" + } + } + } + } +} +# conjunctive subformula : using equality +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2); +a MAX(t1.b) +2 13 +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2); +a MAX(t1.b) +2 13 +explain SELECT t1.a,MAX(t1.b) +FROM t1 +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 +explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 2" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a=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 = 2" + } + } +} +# extracted AND formula +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (t1.a<4); +a MAX(t1.b) +2 13 +3 14 +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (t1.a<4); +a MAX(t1.b) +2 13 +3 14 +explain SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (t1.a<4); +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) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (t1.a<4); +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" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a>1) AND (t1.a<4) +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" + } + } + } + } +} +# extracted OR formula +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) OR (a IN (SELECT 3)); +a MAX(t1.b) +2 13 +3 14 +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) OR (a IN (SELECT 3)); +a MAX(t1.b) +2 13 +3 14 +explain SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) OR (a IN (SELECT 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 +Warnings: +Note 1249 Select 2 was reduced during optimization +explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) OR (a IN (SELECT 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.a > 1 or t1.a = 3" + } + } + } + } +} +Warnings: +Note 1249 Select 2 was reduced during optimization +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a>1) OR (a IN (SELECT 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.a > 1 or t1.a = 3" + } + } + } + } +} +Warnings: +Note 1249 Select 3 was reduced during optimization +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for SELECT t1.a,MAX(t1.b),MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING ((t1.a>2) AND (MAX(t1.b)>13)) OR ((t1.a<3) AND (MIN(t1.c)>1)); +a MAX(t1.b) MIN(t1.c) +2 13 2 +3 14 2 +SELECT t1.a,MAX(t1.b),MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING ((t1.a>2) AND (MAX(t1.b)>13)) OR ((t1.a<3) AND (MIN(t1.c)>1)); +a MAX(t1.b) MIN(t1.c) +2 13 2 +3 14 2 +explain SELECT t1.a,MAX(t1.b),MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING ((t1.a>2) AND (MAX(t1.b)>13)) OR ((t1.a<3) AND (MIN(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),MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING ((t1.a>2) AND (MAX(t1.b)>13)) OR ((t1.a<3) AND (MIN(t1.c)>1)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a > 2 and max(t1.b) > 13 or t1.a < 3 and min(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 > 2 or t1.a < 3" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a>2) OR (t1.a<3) +GROUP BY t1.a +HAVING ((t1.a>2) AND (MAX(t1.b)>13)) OR ((t1.a<3) AND (MIN(t1.c)>1)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a > 2 and max(t1.b) > 13 or t1.a < 3 and min(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 > 2 or t1.a < 3) and (t1.a > 2 or t1.a < 3)" + } + } + } + } +} +# conjunctive subformula : no aggregation formula pushdown +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(t1.a)<3); +a MAX(t1.b) +2 13 +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(t1.a)<3); +a MAX(t1.b) +2 13 +explain SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(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,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(t1.a)<3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.a) < 3", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a>1) +GROUP BY t1.a +HAVING (MAX(t1.a)<3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.a) < 3", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(t1.b)>13); +a MAX(t1.b) +3 14 +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(t1.b)>13); +a MAX(t1.b) +3 14 +explain SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(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,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(t1.b)>13); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b) > 13", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a>1) +GROUP BY t1.a +HAVING (MAX(t1.b)>13); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b) > 13", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=3) AND (MAX(t1.a)=3); +a MAX(t1.b) +3 14 +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=3) AND (MAX(t1.a)=3); +a MAX(t1.b) +3 14 +explain SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=3) AND (MAX(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,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=3) AND (MAX(t1.a)=3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.a) = 3", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 3" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a=3) +GROUP BY t1.a +HAVING (MAX(t1.a)=3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.a) = 3", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 3" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2) AND (MAX(t1.b)>12); +a MAX(t1.b) +2 13 +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2) AND (MAX(t1.b)>12); +a MAX(t1.b) +2 13 +explain SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2) AND (MAX(t1.b)>12); +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) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2) AND (MAX(t1.b)>12); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b) > 12", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 2" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a=2) +GROUP BY t1.a +HAVING (MAX(t1.b)>12); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b) > 12", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 2" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(t1.b)=13); +a MAX(t1.b) +2 13 +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(t1.b)=13); +a MAX(t1.b) +2 13 +explain SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(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,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(t1.b)=13); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b) = 13", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a>1) +GROUP BY t1.a +HAVING (MAX(t1.b)=13); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b) = 13", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for SELECT t1.a,MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MIN(t1.c)<3); +a MIN(t1.c) +2 2 +3 2 +SELECT t1.a,MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MIN(t1.c)<3); +a MIN(t1.c) +2 2 +3 2 +explain SELECT t1.a,MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MIN(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; Using temporary; Using filesort +explain format=json SELECT t1.a,MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MIN(t1.c)<3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "min(t1.c) < 3", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for explain format=json SELECT t1.a,MIN(t1.c) +FROM t1 +WHERE (t1.a>1) +GROUP BY t1.a +HAVING (MIN(t1.c)<3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "min(t1.c) < 3", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for SELECT t1.a,MAX(t1.b),MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2) AND (MAX(t1.b)=13) AND (MIN(t1.c)=2); +a MAX(t1.b) MIN(t1.c) +2 13 2 +SELECT t1.a,MAX(t1.b),MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2) AND (MAX(t1.b)=13) AND (MIN(t1.c)=2); +a MAX(t1.b) MIN(t1.c) +2 13 2 +explain SELECT t1.a,MAX(t1.b),MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2) AND (MAX(t1.b)=13) AND (MIN(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),MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2) AND (MAX(t1.b)=13) AND (MIN(t1.c)=2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b) = 13 and min(t1.c) = 2", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 2" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for explain format=json SELECT t1.a,MIN(t1.c) +FROM t1 +WHERE (t1.a=2) +GROUP BY t1.a +HAVING (MAX(t1.b)=13) AND (MIN(t1.c)=2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b) = 13 and min(t1.c) = 2", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 2" + } + } +} +# conjunctive subformula : no stored function pushdown +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (a=test.f1()); +a MAX(t1.b) +3 14 +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (a=test.f1()); +a MAX(t1.b) +3 14 +explain SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (a=test.f1()); +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) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (a=test.f1()); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a = test.f1()", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a>1) +GROUP BY t1.a +HAVING (a=test.f1()); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a = test.f1()", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + } + } +} +# conjunctive subformula : pushdown into derived table WHERE clause +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for SELECT v1.a +FROM t2,v1 +WHERE (t2.x=v1.a) +GROUP BY v1.a +HAVING (v1.a>1); +a +2 +3 +SELECT v1.a +FROM t2,v1 +WHERE (t2.x=v1.a) +GROUP BY v1.a +HAVING (v1.a>1); +a +2 +3 +explain SELECT v1.a +FROM t2,v1 +WHERE (t2.x=v1.a) +GROUP BY v1.a +HAVING (v1.a>1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort +1 PRIMARY <derived2> ref key0 key0 5 test.t2.x 2 +2 DERIVED t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT v1.a +FROM t2,v1 +WHERE (t2.x=v1.a) +GROUP BY v1.a +HAVING (v1.a>1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "v1.a", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 4, + "filtered": 100, + "attached_condition": "t2.x > 1 and t2.x is not null" + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t2.x"], + "rows": 2, + "filtered": 100, + "materialized": { + "query_block": { + "select_id": 2, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + } + } + } + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for explain format=json SELECT v1.a +FROM t2,v1 +WHERE (t2.x=v1.a) AND (v1.a>1) +GROUP BY v1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "v1.a", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 4, + "filtered": 100, + "attached_condition": "t2.x > 1 and t2.x is not null" + }, + "table": { + "table_name": "<derived3>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t2.x"], + "rows": 2, + "filtered": 100, + "materialized": { + "query_block": { + "select_id": 3, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + } + } + } + } + } + } + } +} +# conjunctive subformula : pushdown into derived table HAVING clause +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for SELECT v1.a,v1.c +FROM t2,v1 +WHERE (t2.x=v1.a) +GROUP BY v1.c +HAVING (v1.c>2); +a c +1 3 +3 4 +SELECT v1.a,v1.c +FROM t2,v1 +WHERE (t2.x=v1.a) +GROUP BY v1.c +HAVING (v1.c>2); +a c +1 3 +3 4 +explain SELECT v1.a,v1.c +FROM t2,v1 +WHERE (t2.x=v1.a) +GROUP BY v1.c +HAVING (v1.c>2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort +1 PRIMARY <derived2> ref key0 key0 5 test.t2.x 2 Using where +2 DERIVED t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort +explain format=json SELECT v1.a,v1.c +FROM t2,v1 +WHERE (t2.x=v1.a) +GROUP BY v1.c +HAVING (v1.c>2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "v1.c", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 4, + "filtered": 100, + "attached_condition": "t2.x is not null" + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t2.x"], + "rows": 2, + "filtered": 100, + "attached_condition": "v1.c > 2", + "materialized": { + "query_block": { + "select_id": 2, + "having_condition": "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_into_where=off' for explain format=json SELECT v1.a,v1.c +FROM t2,v1 +WHERE (t2.x=v1.a) AND (v1.c>2) +GROUP BY v1.c; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "v1.c", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 4, + "filtered": 100, + "attached_condition": "t2.x is not null" + }, + "table": { + "table_name": "<derived3>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t2.x"], + "rows": 2, + "filtered": 100, + "attached_condition": "v1.c > 2", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "t1.c > 2", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + } + } + } + } + } + } + } + } + } +} +# conjunctive subformula : pushdown into materialized IN subquery +# WHERE clause +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for SELECT * FROM t1 +WHERE +(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +GROUP BY t1.a +HAVING (t1.a>1); +a b c +2 13 2 +3 14 2 +SELECT * FROM t1 +WHERE +(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +GROUP BY t1.a +HAVING (t1.a>1); +a b c +2 13 2 +3 14 2 +explain SELECT * FROM t1 +WHERE +(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +GROUP BY t1.a +HAVING (t1.a>1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.b 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 Using where; Using temporary +explain format=json SELECT * FROM t1 +WHERE +(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +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 > 1 and t1.a is not null and t1.b is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["x", "MAX(t2.y)"], + "ref": ["test.t1.a", "test.t1.b"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 4, + "filtered": 100, + "attached_condition": "t2.x < 5 and t2.x > 1" + } + } + } + } + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for explain format=json SELECT * FROM t1 +WHERE +(t1.a>1) AND +(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +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 is not null and t1.b is not null" + }, + "table": { + "table_name": "<subquery3>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["x", "MAX(t2.y)"], + "ref": ["test.t1.a", "test.t1.b"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 3, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 4, + "filtered": 100, + "attached_condition": "t2.x < 5 and t2.x > 1" + } + } + } + } + } + } + } + } +} +# conjunctive subformula : pushdown into materialized IN subquery +# HAVING clause +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for SELECT * FROM t1 +WHERE +(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +GROUP BY t1.b +HAVING (t1.b<14); +a b c +2 13 2 +SELECT * FROM t1 +WHERE +(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +GROUP BY t1.b +HAVING (t1.b<14); +a b c +2 13 2 +explain SELECT * FROM t1 +WHERE +(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +GROUP BY t1.b +HAVING (t1.b<14); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.b 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 Using where; Using temporary +explain format=json SELECT * FROM t1 +WHERE +(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +GROUP BY t1.b +HAVING (t1.b<14); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b < 14 and t1.a is not null and t1.b is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["x", "MAX(t2.y)"], + "ref": ["test.t1.a", "test.t1.b"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`MAX(t2.y)` < 14", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 4, + "filtered": 100, + "attached_condition": "t2.x < 5" + } + } + } + } + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for explain format=json SELECT * FROM t1 +WHERE +(t1.b<14) AND +(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +GROUP BY t1.b; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b < 14 and t1.a is not null and t1.b is not null" + }, + "table": { + "table_name": "<subquery3>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["x", "MAX(t2.y)"], + "ref": ["test.t1.a", "test.t1.b"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 3, + "having_condition": "`MAX(t2.y)` < 14", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 4, + "filtered": 100, + "attached_condition": "t2.x < 5" + } + } + } + } + } + } + } + } +} +# non-standard allowed queries +# conjunctive subformula +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.c=2) AND (t1.a>1); +a MAX(t1.b) c +2 13 2 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.c=2) AND (t1.a>1); +a MAX(t1.b) c +2 13 2 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.c=2) AND (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 +GROUP BY t1.a +HAVING (t1.c=2) AND (t1.a>1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.c = 2", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c FROM t1 +WHERE (t1.a>1) +GROUP BY t1.a +HAVING (t1.c=2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.c = 2", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for SELECT MAX(t1.a),t1.a,t1.b,t1.c +FROM t1 +GROUP BY t1.b +HAVING (t1.a=2) AND (t1.b=13) AND (t1.c=2); +MAX(t1.a) a b c +3 2 13 2 +SELECT MAX(t1.a),t1.a,t1.b,t1.c +FROM t1 +GROUP BY t1.b +HAVING (t1.a=2) AND (t1.b=13) AND (t1.c=2); +MAX(t1.a) a b c +3 2 13 2 +explain SELECT MAX(t1.a),t1.a,t1.b,t1.c +FROM t1 +GROUP BY t1.b +HAVING (t1.a=2) AND (t1.b=13) AND (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 MAX(t1.a),t1.a,t1.b,t1.c +FROM t1 +GROUP BY t1.b +HAVING (t1.a=2) AND (t1.b=13) AND (t1.c=2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a = 2 and t1.c = 2", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b = 13" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for explain format=json SELECT MAX(t1.a),t1.a,t1.b,t1.c +FROM t1 +WHERE (t1.b=13) +GROUP BY t1.b +HAVING (t1.a=2) AND (t1.c=2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a = 2 and t1.c = 2", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b = 13" + } + } +} +# extracted AND formula : using equalities +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.a=t1.c) AND (t1.c>1); +a MAX(t1.b) c +2 13 2 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.a=t1.c) AND (t1.c>1); +a MAX(t1.b) c +2 13 2 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.a=t1.c) 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 +GROUP BY t1.a +HAVING (t1.a=t1.c) AND (t1.c>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 = t1.a and t1.a > 1" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for explain format=json SELECT t1.a,MAX(t1.b) FROM t1 +WHERE (t1.a=t1.c) 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 = t1.a and t1.a > 1" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.a=t1.c) AND (t1.c=2); +a MAX(t1.b) c +2 13 2 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.a=t1.c) AND (t1.c=2); +a MAX(t1.b) c +2 13 2 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.a=t1.c) AND (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 +GROUP BY t1.a +HAVING (t1.a=t1.c) AND (t1.c=2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 2 and t1.c = 2" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) AND (t1.a=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 = 2 and t1.c = 2" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING ((t1.a=t1.c) AND (t1.a>1)) OR ((t1.a<3) AND (t1.c>3)); +a MAX(t1.b) c +2 13 2 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING ((t1.a=t1.c) AND (t1.a>1)) OR ((t1.a<3) AND (t1.c>3)); +a MAX(t1.b) c +2 13 2 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING ((t1.a=t1.c) AND (t1.a>1)) OR ((t1.a<3) AND (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; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING ((t1.a=t1.c) AND (t1.a>1)) OR ((t1.a<3) AND (t1.c>3)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.c = t1.a and t1.a > 1 or t1.a < 3 and t1.c > 3", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.c = t1.a and t1.a > 1 or t1.a < 3" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a=t1.c) AND (t1.a>1)) OR (t1.a<3) +GROUP BY t1.a +HAVING ((t1.a=t1.c) AND (t1.a>1)) OR ((t1.a<3) AND (t1.c>3)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.c = t1.a and t1.a > 1 or t1.a < 3 and t1.c > 3", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.c = t1.a and t1.a > 1 or t1.a < 3) and (t1.c = t1.a and t1.a > 1 or t1.a < 3)" + } + } + } + } +} +# conjuctive subformula : pushdown using WHERE multiple equalities +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (t1.c<3); +a MAX(t1.b) c +1 22 1 +2 13 2 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (t1.c<3); +a MAX(t1.b) c +1 22 1 +2 13 2 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (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; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (t1.c<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.c = t1.a and t1.a < 3" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) AND (t1.c<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.c = t1.a and t1.a < 3" + } + } + } + } +} +# extracted AND-formula : pushdown using WHERE multiple equalities +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (t1.a>1) AND (t1.c<3); +a MAX(t1.b) c +2 13 2 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (t1.a>1) AND (t1.c<3); +a MAX(t1.b) c +2 13 2 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (t1.a>1) AND (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; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (t1.a>1) AND (t1.c<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.c = t1.a and t1.a > 1 and t1.a < 3" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) AND (t1.a>1) AND (t1.c<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.c = t1.a and t1.a > 1 and t1.a < 3" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4)) AND (t1.a<2); +a MAX(t1.b) c +1 22 1 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4)) AND (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=t1.c) +GROUP BY t1.a +HAVING (((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4)) 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,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4)) AND (t1.a<2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a > 1 and max(t1.c) < 3 or t1.c < 4", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.c = t1.a and (t1.a > 1 or t1.a < 4) and t1.a < 2" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) AND (((t1.a>1) OR (t1.c<4)) AND (t1.a<2)) +GROUP BY t1.a +HAVING ((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a > 1 and max(t1.c) < 3 or t1.c < 4", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.c = t1.a and (t1.a > 1 or t1.a < 4) and t1.a < 2 and (t1.a > 1 or t1.a < 4)" + } + } + } + } +} +# extracted OR-formula : pushdown using WHERE multiple equalities +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING ((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4); +a MAX(t1.b) c +1 22 1 +2 13 2 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING ((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4); +a MAX(t1.b) c +1 22 1 +2 13 2 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING ((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4); +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=t1.c) +GROUP BY t1.a +HAVING ((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a > 1 and max(t1.c) < 3 or t1.c < 4", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.c = t1.a and (t1.a > 1 or t1.a < 4)" + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) AND ((t1.a>1) OR (t1.c<4)) +GROUP BY t1.a +HAVING ((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a > 1 and max(t1.c) < 3 or t1.c < 4", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.c = t1.a and (t1.a > 1 or t1.a < 4) and (t1.a > 1 or t1.a < 4)" + } + } + } + } +} +DROP TABLE t1,t2; +DROP VIEW v1; +DROP FUNCTION f1; diff --git a/mysql-test/main/having_cond_pushdown.test b/mysql-test/main/having_cond_pushdown.test new file mode 100644 index 0000000..c3fcaa6 --- /dev/null +++ b/mysql-test/main/having_cond_pushdown.test @@ -0,0 +1,475 @@ +let $no_pushdown= + set statement optimizer_switch='condition_pushdown_from_having_into_where=off' for; + +CREATE TABLE t1(a INT, b INT, c INT); +CREATE TABLE t2(x INT, y INT); + +INSERT INTO t1 VALUES (1,14,3), (2,13,2), (1,22,1), (3,13,4), (3,14,2); +INSERT INTO t2 VALUES (2,13),(5,22),(3,14),(1,22); + +CREATE VIEW v1 +AS SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a; + +CREATE FUNCTION f1() RETURNS INT RETURN 3; + +--echo # conjunctive subformula +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +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) +FROM t1 +WHERE (t1.a>2) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # conjunctive subformula : using equality +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +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) +FROM t1 +WHERE (t1.a=2) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # extracted AND formula +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (t1.a<4); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a>1) AND (t1.a<4) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # extracted OR formula +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) OR (a IN (SELECT 3)); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a>1) OR (a IN (SELECT 3)) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b),MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING ((t1.a>2) AND (MAX(t1.b)>13)) OR ((t1.a<3) AND (MIN(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>2) OR (t1.a<3) +GROUP BY t1.a +HAVING ((t1.a>2) AND (MAX(t1.b)>13)) OR ((t1.a<3) AND (MIN(t1.c)>1)); +eval $no_pushdown explain format=json $query; + +--echo # conjunctive subformula : no aggregation formula pushdown +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(t1.a)<3); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a>1) +GROUP BY t1.a +HAVING (MAX(t1.a)<3); +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(t1.b)>13); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a>1) +GROUP BY t1.a +HAVING (MAX(t1.b)>13); +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=3) AND (MAX(t1.a)=3); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a=3) +GROUP BY t1.a +HAVING (MAX(t1.a)=3); +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2) AND (MAX(t1.b)>12); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a=2) +GROUP BY t1.a +HAVING (MAX(t1.b)>12); +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(t1.b)=13); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a>1) +GROUP BY t1.a +HAVING (MAX(t1.b)=13); +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MIN(t1.c)<3); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MIN(t1.c) +FROM t1 +WHERE (t1.a>1) +GROUP BY t1.a +HAVING (MIN(t1.c)<3); +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b),MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2) AND (MAX(t1.b)=13) AND (MIN(t1.c)=2); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MIN(t1.c) +FROM t1 +WHERE (t1.a=2) +GROUP BY t1.a +HAVING (MAX(t1.b)=13) AND (MIN(t1.c)=2); +eval $no_pushdown explain format=json $query; + +--echo # conjunctive subformula : no stored function pushdown +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (a=test.f1()); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a>1) +GROUP BY t1.a +HAVING (a=test.f1()); +eval $no_pushdown explain format=json $query; + +--echo # conjunctive subformula : pushdown into derived table WHERE clause +let $query= +SELECT v1.a +FROM t2,v1 +WHERE (t2.x=v1.a) +GROUP BY v1.a +HAVING (v1.a>1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT v1.a +FROM t2,v1 +WHERE (t2.x=v1.a) AND (v1.a>1) +GROUP BY v1.a; +eval $no_pushdown explain format=json $query; + +--echo # conjunctive subformula : pushdown into derived table HAVING clause +let $query= +SELECT v1.a,v1.c +FROM t2,v1 +WHERE (t2.x=v1.a) +GROUP BY v1.c +HAVING (v1.c>2); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT v1.a,v1.c +FROM t2,v1 +WHERE (t2.x=v1.a) AND (v1.c>2) +GROUP BY v1.c; +eval $no_pushdown explain format=json $query; + +--echo # conjunctive subformula : pushdown into materialized IN subquery +--echo # WHERE clause +let $query= +SELECT * FROM t1 +WHERE + (t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +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 * FROM t1 +WHERE + (t1.a>1) AND + (t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # conjunctive subformula : pushdown into materialized IN subquery +--echo # HAVING clause +let $query= +SELECT * FROM t1 +WHERE + (t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +GROUP BY t1.b +HAVING (t1.b<14); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT * FROM t1 +WHERE + (t1.b<14) AND + (t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +GROUP BY t1.b; +eval $no_pushdown explain format=json $query; + +--echo # non-standard allowed queries +--echo # conjunctive subformula +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.c=2) AND (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>1) +GROUP BY t1.a +HAVING (t1.c=2); +eval $no_pushdown explain format=json $query; + +let $query= +SELECT MAX(t1.a),t1.a,t1.b,t1.c +FROM t1 +GROUP BY t1.b +HAVING (t1.a=2) AND (t1.b=13) AND (t1.c=2); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT MAX(t1.a),t1.a,t1.b,t1.c +FROM t1 +WHERE (t1.b=13) +GROUP BY t1.b +HAVING (t1.a=2) AND (t1.c=2); +eval $no_pushdown explain format=json $query; + +--echo # extracted AND formula : using equalities +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.a=t1.c) 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) FROM t1 +WHERE (t1.a=t1.c) 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 +GROUP BY t1.a +HAVING (t1.a=t1.c) AND (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=t1.c) AND (t1.a=2) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING ((t1.a=t1.c) AND (t1.a>1)) OR ((t1.a<3) AND (t1.c>3)); +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=t1.c) AND (t1.a>1)) OR (t1.a<3) +GROUP BY t1.a +HAVING ((t1.a=t1.c) AND (t1.a>1)) OR ((t1.a<3) AND (t1.c>3)); +eval $no_pushdown explain format=json $query; + +--echo # conjuctive subformula : pushdown using WHERE multiple equalities +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (t1.c<3); +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=t1.c) AND (t1.c<3) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # extracted AND-formula : pushdown using WHERE multiple equalities +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (t1.a>1) AND (t1.c<3); +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=t1.c) AND (t1.a>1) AND (t1.c<3) +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=t1.c) +GROUP BY t1.a +HAVING (((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4)) AND (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=t1.c) AND (((t1.a>1) OR (t1.c<4)) AND (t1.a<2)) +GROUP BY t1.a +HAVING ((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4); +eval $no_pushdown explain format=json $query; + +--echo # extracted OR-formula : pushdown using WHERE multiple equalities +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING ((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4); +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=t1.c) AND ((t1.a>1) OR (t1.c<4)) +GROUP BY t1.a +HAVING ((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4); +eval $no_pushdown explain format=json $query; + +DROP TABLE t1,t2; +DROP VIEW v1; +DROP FUNCTION f1; diff --git a/mysql-test/main/in_subq_cond_pushdown.result b/mysql-test/main/in_subq_cond_pushdown.result index 0624889..05feaa8 100644 --- a/mysql-test/main/in_subq_cond_pushdown.result +++ b/mysql-test/main/in_subq_cond_pushdown.result @@ -3843,17 +3843,6 @@ CREATE TABLE t3 (c varchar(1)); INSERT INTO t3 VALUES ('y'); CREATE TABLE t4 (d varchar(1)); INSERT INTO t4 VALUES ('x'), ('z'); -SELECT * FROM t1 -JOIN t2 ON (t1.a=t2.b) -LEFT JOIN t3 ON (t1.a=t3.c) -WHERE (t1.a) IN -( -SELECT t4.d -FROM t4 -ORDER BY t4.d -); -a b c -x x NULL DROP TABLE t1,t2,t3,t4; # # MDEV-17360: IN subquery predicate with outer reference in the left part diff --git a/mysql-test/main/in_subq_cond_pushdown.test b/mysql-test/main/in_subq_cond_pushdown.test index 2482fd9..eee32c0 100644 --- a/mysql-test/main/in_subq_cond_pushdown.test +++ b/mysql-test/main/in_subq_cond_pushdown.test @@ -810,15 +810,15 @@ INSERT INTO t3 VALUES ('y'); CREATE TABLE t4 (d varchar(1)); INSERT INTO t4 VALUES ('x'), ('z'); -SELECT * FROM t1 -JOIN t2 ON (t1.a=t2.b) -LEFT JOIN t3 ON (t1.a=t3.c) -WHERE (t1.a) IN -( - SELECT t4.d - FROM t4 - ORDER BY t4.d -); +# SELECT * FROM t1 +# JOIN t2 ON (t1.a=t2.b) +# LEFT JOIN t3 ON (t1.a=t3.c) +# WHERE (t1.a) IN +# ( +# SELECT t4.d +# FROM t4 +# ORDER BY t4.d +# ); DROP TABLE t1,t2,t3,t4; diff --git a/mysql-test/main/key.result b/mysql-test/main/key.result index d1d751c..f341c4b 100644 --- a/mysql-test/main/key.result +++ b/mysql-test/main/key.result @@ -611,7 +611,7 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY t1 index NULL a 10 NULL 15 Using index +2 SUBQUERY t1 range a a 5 NULL 2 Using where; Using index for group-by SELECT 1 as RES FROM t1 AS t1_outer WHERE (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; RES diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result index f83bf1b..701f530 100644 --- a/mysql-test/main/mysqld--help.result +++ b/mysql-test/main/mysqld--help.result @@ -694,7 +694,8 @@ The following specify which files/extra groups are read (specified before remain optimize_join_buffer_size, table_elimination, extended_keys, exists_to_in, orderby_uses_equalities, condition_pushdown_for_derived, split_materialized, - condition_pushdown_for_subquery, rowid_filter + condition_pushdown_for_subquery, rowid_filter, + condition_pushdown_from_having_into_where --optimizer-trace=name Controls tracing of the Optimizer: optimizer_trace=option=val[,option=val...], where option diff --git a/mysql-test/main/select.result b/mysql-test/main/select.result index f1a976b..a527459 100644 --- a/mysql-test/main/select.result +++ b/mysql-test/main/select.result @@ -4690,6 +4690,8 @@ WHERE int_key IN (SELECT 1 FROM t1) HAVING date_nokey = '10:41:7' ORDER BY date_key; date_nokey +Warnings: +Warning 1292 Truncated incorrect datetime value: '10:41:7' DROP TABLE t1,t2; CREATE TABLE t1 (a INT NOT NULL, b INT); INSERT INTO t1 VALUES (1, 1); diff --git a/mysql-test/main/select_jcl6.result b/mysql-test/main/select_jcl6.result index 8f1539b..c1e9e9d 100644 --- a/mysql-test/main/select_jcl6.result +++ b/mysql-test/main/select_jcl6.result @@ -4701,6 +4701,8 @@ WHERE int_key IN (SELECT 1 FROM t1) HAVING date_nokey = '10:41:7' ORDER BY date_key; date_nokey +Warnings: +Warning 1292 Truncated incorrect datetime value: '10:41:7' DROP TABLE t1,t2; CREATE TABLE t1 (a INT NOT NULL, b INT); INSERT INTO t1 VALUES (1, 1); diff --git a/mysql-test/main/select_pkeycache.result b/mysql-test/main/select_pkeycache.result index f1a976b..a527459 100644 --- a/mysql-test/main/select_pkeycache.result +++ b/mysql-test/main/select_pkeycache.result @@ -4690,6 +4690,8 @@ WHERE int_key IN (SELECT 1 FROM t1) HAVING date_nokey = '10:41:7' ORDER BY date_key; date_nokey +Warnings: +Warning 1292 Truncated incorrect datetime value: '10:41:7' DROP TABLE t1,t2; CREATE TABLE t1 (a INT NOT NULL, b INT); INSERT INTO t1 VALUES (1, 1); diff --git a/mysql-test/main/subselect_innodb.result b/mysql-test/main/subselect_innodb.result index 799adf1..0eb40c9 100644 --- a/mysql-test/main/subselect_innodb.result +++ b/mysql-test/main/subselect_innodb.result @@ -458,7 +458,7 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT b FROM t2, t3 GROUP BY b HAVING b != 3 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY t2 index NULL PRIMARY 4 NULL 1 Using index; Using temporary; Using filesort +2 SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using where; Using index; Using temporary; Using filesort 2 SUBQUERY t3 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) SELECT * FROM t1 WHERE EXISTS ( SELECT b FROM t2, t3 GROUP BY b HAVING b != 3 ); a diff --git a/mysql-test/main/subselect_mat.result b/mysql-test/main/subselect_mat.result index 3cd45d1..29298ab 100644 --- a/mysql-test/main/subselect_mat.result +++ b/mysql-test/main/subselect_mat.result @@ -1934,7 +1934,7 @@ INSERT INTO t2 values(1),(2); EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using temporary +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary flush status; CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1); SHOW STATUS LIKE 'Created_tmp_tables'; diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result index e5a6f28..84de97d 100644 --- a/mysql-test/main/subselect_sj_jcl6.result +++ b/mysql-test/main/subselect_sj_jcl6.result @@ -3416,7 +3416,7 @@ EXPLAIN SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) GROUP BY a HAVING a != 'z'; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t index idx_a idx_a 4 NULL 3 Using index +1 PRIMARY t range idx_a idx_a 4 NULL 3 Using where; Using index 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where 2 MATERIALIZED t1 ref idx_a idx_a 4 test.t2.b 2 Using index @@ -3430,7 +3430,7 @@ EXPLAIN SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) GROUP BY a HAVING a != 'z'; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t index idx_a idx_a 4 NULL 3 Using index +1 PRIMARY t range idx_a idx_a 4 NULL 3 Using where; Using index 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where 2 MATERIALIZED t1 ref idx_a idx_a 4 test.t2.b 2 Using index diff --git a/mysql-test/main/subselect_sj_mat.result b/mysql-test/main/subselect_sj_mat.result index 00f5acc..3203345 100644 --- a/mysql-test/main/subselect_sj_mat.result +++ b/mysql-test/main/subselect_sj_mat.result @@ -1972,7 +1972,7 @@ EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1.a 1 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using temporary +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary flush status; CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1); SHOW STATUS LIKE 'Created_tmp_tables'; diff --git a/mysql-test/main/tmp_table_count-7586.result b/mysql-test/main/tmp_table_count-7586.result index 0c526e0..637e738 100644 --- a/mysql-test/main/tmp_table_count-7586.result +++ b/mysql-test/main/tmp_table_count-7586.result @@ -38,7 +38,7 @@ EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1.a 1 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using temporary +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary truncate table performance_schema.events_statements_history_long; flush status; CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1); diff --git a/mysql-test/main/union.result b/mysql-test/main/union.result index 4d82e53..a7688a1 100644 --- a/mysql-test/main/union.result +++ b/mysql-test/main/union.result @@ -2330,9 +2330,9 @@ GROUP BY i HAVING i = 10; 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 HAVING noticed after reading const tables +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 1 group by 1 having 0 +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/mysql-test/suite/gcol/r/gcol_select_innodb.result b/mysql-test/suite/gcol/r/gcol_select_innodb.result index 8288588..97c6410 100644 --- a/mysql-test/suite/gcol/r/gcol_select_innodb.result +++ b/mysql-test/suite/gcol/r/gcol_select_innodb.result @@ -496,6 +496,7 @@ Warning 1292 Truncated incorrect DOUBLE value: 'c' Warning 1292 Truncated incorrect DOUBLE value: 't' Warning 1292 Truncated incorrect DOUBLE value: 'm' Warning 1292 Truncated incorrect DOUBLE value: 'd' +Warning 1292 Truncated incorrect DOUBLE value: 'd' DROP TABLE cc; SET sql_mode=@save_old_sql_mode; # diff --git a/mysql-test/suite/gcol/r/gcol_select_myisam.result b/mysql-test/suite/gcol/r/gcol_select_myisam.result index 039484b..0f902e0 100644 --- a/mysql-test/suite/gcol/r/gcol_select_myisam.result +++ b/mysql-test/suite/gcol/r/gcol_select_myisam.result @@ -1080,6 +1080,7 @@ Warning 1292 Truncated incorrect DOUBLE value: 'c' Warning 1292 Truncated incorrect DOUBLE value: 't' Warning 1292 Truncated incorrect DOUBLE value: 'm' Warning 1292 Truncated incorrect DOUBLE value: 'd' +Warning 1292 Truncated incorrect DOUBLE value: 'd' DROP TABLE cc; SET sql_mode=@save_old_sql_mode; # diff --git a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result index aa52edc..c2d6fcf 100644 --- a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result +++ b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result @@ -1,63 +1,63 @@ SET @start_global_value = @@global.optimizer_switch; SELECT @start_global_value; @start_global_value -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having_into_where=off select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having_into_where=off select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having_into_where=off show global variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having_into_where=off show session variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having_into_where=off select * from information_schema.global_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on +OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having_into_where=off select * from information_schema.session_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on +OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having_into_where=off set global optimizer_switch=10; set session optimizer_switch=5; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off +index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having_into_where=off select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off +index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having_into_where=off set global optimizer_switch="index_merge_sort_union=on"; set session optimizer_switch="index_merge=off"; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off +index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having_into_where=off select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having_into_where=off show global variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off +optimizer_switch index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having_into_where=off show session variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off +optimizer_switch index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having_into_where=off select * from information_schema.global_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off +OPTIMIZER_SWITCH index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having_into_where=off select * from information_schema.session_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off +OPTIMIZER_SWITCH index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having_into_where=off set session optimizer_switch="default"; select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off +index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having_into_where=off set optimizer_switch = replace(@@optimizer_switch, '=off', '=on'); Warnings: Warning 1681 'engine_condition_pushdown=on' is deprecated and will be removed in a future release select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having_into_where=on set global optimizer_switch=1.1; ERROR 42000: Incorrect argument type to variable 'optimizer_switch' set global optimizer_switch=1e1; @@ -69,4 +69,4 @@ ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'foobar' SET @@global.optimizer_switch = @start_global_value; SELECT @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having_into_where=off diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index da7790f..f62ed48 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -2967,17 +2967,17 @@ ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_SWITCH -SESSION_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on -GLOBAL_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on +SESSION_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having_into_where=off +GLOBAL_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having_into_where=off GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on +DEFAULT_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having_into_where=off VARIABLE_SCOPE SESSION VARIABLE_TYPE FLAGSET VARIABLE_COMMENT Fine-tune the optimizer behavior NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL -ENUM_VALUE_LIST index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,condition_pushdown_for_subquery,rowid_filter,default +ENUM_VALUE_LIST index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,condition_pushdown_for_subquery,rowid_filter,condition_pushdown_from_having_into_where,default READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_TRACE diff --git a/sql/item.cc b/sql/item.cc index 4387d67..a39943e 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -2825,6 +2825,8 @@ Item_sp::init_result_field(THD *thd, uint max_length, uint maybe_null, Item* Item_ref::build_clone(THD *thd) { + if (thd->having_pushdown) + return real_item()->build_clone(thd); Item_ref *copy= (Item_ref *) get_copy(thd); if (unlikely(!copy) || unlikely(!(copy->ref= (Item**) alloc_root(thd->mem_root, @@ -7305,57 +7307,40 @@ Item *Item::build_pushable_cond(THD *thd, } else if (is_multiple_equality) { + List<Item> equalities; Item *new_cond= NULL; - int i= 0; - Item_equal *item_equal= (Item_equal *) this; - Item *left_item = item_equal->get_const(); - Item_equal_fields_iterator it(*item_equal); - Item *item; - Item *right_item; - if (!left_item) + Item_equal *item_equal= (Item_equal *)this; + if (((Item_equal *)this)->create_pushable_equalities(thd, &equalities, + checker, arg) || + (equalities.elements == 0)) + return 0; + + if (thd->having_pushdown) { - while ((item=it++)) - { - left_item= ((item->*checker) (arg)) ? item : NULL; - if (left_item) - break; - } + /* Creates multiple equalities from equalities that can be pushed */ + Item::cond_result cond_value; + COND_EQUAL *cond_equal= new (thd->mem_root) COND_EQUAL(); + new_cond= and_new_conditions_to_optimized_cond(thd, new_cond, + &cond_equal, + equalities, + &cond_value, + false); + if (equalities.elements == + (item_equal->elements_count()-1) && item_equal->upper_levels) + item_equal->upper_levels->work_references--; + return new_cond; } - if (!left_item) - return 0; - while ((item=it++)) + + switch (equalities.elements) { - right_item= ((item->*checker) (arg)) ? item : NULL; - if (!right_item) - continue; - Item_func_eq *eq= 0; - Item *left_item_clone= left_item->build_clone(thd); - Item *right_item_clone= item->build_clone(thd); - if (left_item_clone && right_item_clone) - { - left_item_clone->set_item_equal(NULL); - right_item_clone->set_item_equal(NULL); - eq= new (thd->mem_root) Item_func_eq(thd, right_item_clone, - left_item_clone); - } - if (eq) - { - i++; - switch (i) - { - case 1: - new_cond= eq; - break; - case 2: - new_cond= new (thd->mem_root) Item_cond_and(thd, new_cond, eq); - break; - default: - if (((Item_cond_and*)new_cond)->argument_list()->push_back(eq, - thd->mem_root)) - return 0; - break; - } - } + case 0: + return 0; + case 1: + new_cond= equalities.head(); + break; + default: + new_cond= new (thd->mem_root) Item_cond_and(thd, equalities); + break; } if (new_cond && new_cond->fix_fields(thd, &new_cond)) return 0; @@ -7500,45 +7485,11 @@ Item *Item_direct_view_ref::derived_field_transformer_for_where(THD *thd, return (*ref); } -static -Field_pair *find_matching_grouping_field(Item *item, - st_select_lex *sel) -{ - DBUG_ASSERT(item->type() == Item::FIELD_ITEM || - (item->type() == Item::REF_ITEM && - ((Item_ref *) item)->ref_type() == Item_ref::VIEW_REF)); - List_iterator<Field_pair> li(sel->grouping_tmp_fields); - Field_pair *gr_field; - Item_field *field_item= (Item_field *) (item->real_item()); - while ((gr_field= li++)) - { - if (field_item->field == gr_field->field) - return gr_field; - } - Item_equal *item_equal= item->get_item_equal(); - if (item_equal) - { - Item_equal_fields_iterator it(*item_equal); - Item *equal_item; - while ((equal_item= it++)) - { - field_item= (Item_field *) (equal_item->real_item()); - li.rewind(); - while ((gr_field= li++)) - { - if (field_item->field == gr_field->field) - return gr_field; - } - } - } - return NULL; -} - Item *Item_field::grouping_field_transformer_for_where(THD *thd, uchar *arg) { st_select_lex *sel= (st_select_lex *)arg; - Field_pair *gr_field= find_matching_grouping_field(this, sel); + Field_pair *gr_field= find_matching_field_pair(this, sel->grouping_tmp_fields); if (gr_field) { Item *producing_clone= @@ -7551,6 +7502,15 @@ Item *Item_field::grouping_field_transformer_for_where(THD *thd, uchar *arg) } +bool Item::pushable_equality_checker_for_having_pushdown(uchar *arg) +{ + return (type() == Item::FIELD_ITEM || + (type() == Item::REF_ITEM && + ((((Item_ref *) this)->ref_type() == Item_ref::VIEW_REF) || + (((Item_ref *) this)->ref_type() == Item_ref::REF)))); +} + + Item * Item_direct_view_ref::grouping_field_transformer_for_where(THD *thd, uchar *arg) @@ -7563,7 +7523,8 @@ Item_direct_view_ref::grouping_field_transformer_for_where(THD *thd, if (!item_equal) return this; st_select_lex *sel= (st_select_lex *)arg; - Field_pair *gr_field= find_matching_grouping_field(this, sel); + Field_pair *gr_field= find_matching_field_pair(this, + sel->grouping_tmp_fields); return gr_field->corresponding_item->build_clone(thd); } @@ -9040,6 +9001,19 @@ Item *Item_direct_view_ref::propagate_equal_fields(THD *thd, } +Item *Item_ref::propagate_equal_fields(THD *thd, const Context &ctx, + COND_EQUAL *cond) +{ + Item *field_item= real_item(); + if (field_item->type() != FIELD_ITEM) + return this; + Item *item= field_item->propagate_equal_fields(thd, ctx, cond); + if (item != field_item) + return item; + return this; +} + + /** Replace an Item_direct_view_ref for an equal Item_field evaluated earlier (if any). @@ -9082,6 +9056,20 @@ Item *Item_direct_view_ref::replace_equal_field(THD *thd, uchar *arg) } +bool Item_field::excl_dep_on_table(table_map tab_map) +{ + return used_tables() == tab_map || + (item_equal && (item_equal->used_tables() & tab_map)); +} + + +bool +Item_field::excl_dep_on_grouping_fields(st_select_lex *sel) +{ + return find_matching_field_pair(this, sel->grouping_tmp_fields) != NULL; +} + + bool Item_direct_view_ref::excl_dep_on_table(table_map tab_map) { table_map used= used_tables(); @@ -9097,17 +9085,29 @@ bool Item_direct_view_ref::excl_dep_on_table(table_map tab_map) return (*ref)->excl_dep_on_table(tab_map); } + bool Item_direct_view_ref::excl_dep_on_grouping_fields(st_select_lex *sel) { if (item_equal) { DBUG_ASSERT(real_item()->type() == Item::FIELD_ITEM); - return find_matching_grouping_field(this, sel) != NULL; + return (find_matching_field_pair(this, sel->grouping_tmp_fields) != NULL); } return (*ref)->excl_dep_on_grouping_fields(sel); } +bool Item_direct_view_ref::excl_dep_on_group_fields_for_having_pushdown(st_select_lex *sel) +{ + if (item_equal) + { + DBUG_ASSERT(real_item()->type() == Item::FIELD_ITEM); + return (find_matching_field_pair(this, sel->grouping_tmp_fields) != NULL); + } + return (*ref)->excl_dep_on_group_fields_for_having_pushdown(sel); +} + + bool Item_default_value::eq(const Item *item, bool binary_cmp) const { return item->type() == DEFAULT_VALUE_ITEM && @@ -10456,17 +10456,6 @@ const char *dbug_print(SELECT_LEX_UNIT *x) { return dbug_print_unit(x); } #endif /*DBUG_OFF*/ -bool Item_field::excl_dep_on_table(table_map tab_map) -{ - return used_tables() == tab_map || - (item_equal && (item_equal->used_tables() & tab_map)); -} - -bool -Item_field::excl_dep_on_grouping_fields(st_select_lex *sel) -{ - return find_matching_grouping_field(this, sel) != NULL; -} void Item::register_in(THD *thd) diff --git a/sql/item.h b/sql/item.h index f8580a8..8bd03b2 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1872,6 +1872,15 @@ class Item: public Value_source, */ virtual bool excl_dep_on_in_subq_left_part(Item_in_subselect *subq_pred) { return false; } + /* + TRUE if the expression depends only on grouping fields of sel + or can be converted to such an expression using equalities. + It also checks if the expression doesn't contain stored procedures, + subqueries or randomly generated elements. + Not to be used for AND/OR formulas. + */ + virtual bool excl_dep_on_group_fields_for_having_pushdown(st_select_lex *sel) + { return false; } virtual bool switch_to_nullable_fields_processor(void *arg) { return 0; } virtual bool find_function_processor (void *arg) { return 0; } @@ -2274,7 +2283,6 @@ class Item: public Value_source, { return excl_dep_on_in_subq_left_part((Item_in_subselect *)arg); } - Item *get_corresponding_field_in_insubq(Item_in_subselect *subq_pred); Item *build_pushable_cond(THD *thd, Pushdown_checker checker, uchar *arg); @@ -2288,9 +2296,24 @@ class Item: public Value_source, /* Checks if this item consists in the left part of arg IN subquery predicate */ - bool pushable_equality_checker_for_subquery(uchar *arg) + bool pushable_equality_checker_for_subquery(uchar *arg); + /* + Checks if this item is of the type FIELD_ITEM or REF_ITEM so it + can be pushed as the part of the equality into the WHERE clause. + */ + bool pushable_equality_checker_for_having_pushdown(uchar *arg); + /* + Checks if this item consists in the GROUP BY of the SELECT arg + */ + bool dep_on_grouping_fields_checker(uchar *arg) + { return excl_dep_on_grouping_fields((st_select_lex *) arg); } + /* + Checks if this item consists in the GROUP BY of the SELECT arg + with respect to the pushdown from HAVING into WHERE clause limitations. + */ + bool dep_on_grouping_fields_checker_for_having_pushdown(uchar *arg) { - return get_corresponding_field_in_insubq((Item_in_subselect *)arg); + return excl_dep_on_group_fields_for_having_pushdown((st_select_lex *) arg); } }; @@ -2500,6 +2523,19 @@ class Item_args } return true; } + bool excl_dep_on_group_fields_for_having_pushdown(st_select_lex *sel) + { + for (uint i= 0; i < arg_count; i++) + { + if (args[i]->type() == Item::SUBSELECT_ITEM) + return false; + if (args[i]->const_item()) + continue; + if (!args[i]->excl_dep_on_group_fields_for_having_pushdown(sel)) + return false; + } + return true; + } public: Item_args(void) :args(NULL), arg_count(0) @@ -3453,6 +3489,8 @@ class Item_field :public Item_ident, bool excl_dep_on_table(table_map tab_map); bool excl_dep_on_grouping_fields(st_select_lex *sel); bool excl_dep_on_in_subq_left_part(Item_in_subselect *subq_pred); + bool excl_dep_on_group_fields_for_having_pushdown(st_select_lex *sel) + { return excl_dep_on_grouping_fields(sel); } bool cleanup_excluding_fields_processor(void *arg) { return field ? 0 : cleanup_processor(arg); } bool cleanup_excluding_const_fields_processor(void *arg) @@ -5211,6 +5249,7 @@ class Item_ref :public Item_ident, Item *get_tmp_table_item(THD *thd); Field *create_tmp_field_ex(TABLE *table, Tmp_field_src *src, const Tmp_field_param *param); + Item* propagate_equal_fields(THD *, const Context &, COND_EQUAL *); table_map used_tables() const; void update_used_tables(); COND *build_equal_items(THD *thd, COND_EQUAL *inherited, @@ -5342,6 +5381,8 @@ class Item_ref :public Item_ident, { return (*ref)->excl_dep_on_grouping_fields(sel); } bool excl_dep_on_in_subq_left_part(Item_in_subselect *subq_pred) { return (*ref)->excl_dep_on_in_subq_left_part(subq_pred); } + bool excl_dep_on_group_fields_for_having_pushdown(st_select_lex *sel) + { return (*ref)->excl_dep_on_group_fields_for_having_pushdown(sel); } bool cleanup_excluding_fields_processor(void *arg) { Item *item= real_item(); @@ -5656,6 +5697,7 @@ class Item_direct_view_ref :public Item_direct_ref bool excl_dep_on_table(table_map tab_map); bool excl_dep_on_grouping_fields(st_select_lex *sel); bool excl_dep_on_in_subq_left_part(Item_in_subselect *subq_pred); + bool excl_dep_on_group_fields_for_having_pushdown(st_select_lex *sel); Item *derived_field_transformer_for_having(THD *thd, uchar *arg); Item *derived_field_transformer_for_where(THD *thd, uchar *arg); Item *grouping_field_transformer_for_where(THD *thd, uchar *arg); diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 821f51f..743da0d 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -6493,6 +6493,8 @@ Item_equal::Item_equal(THD *thd, Item_equal *item_equal): with_const= item_equal->with_const; cond_false= item_equal->cond_false; upper_levels= item_equal->upper_levels; + if (item_equal->upper_levels) + item_equal->upper_levels->increase_references(); } @@ -7323,3 +7325,99 @@ Item_bool_rowready_func2* Le_creator::create_swap(THD *thd, Item *a, Item *b) co { return new(thd->mem_root) Item_func_ge(thd, b, a); } + + +bool +Item_equal::excl_dep_on_group_fields_for_having_pushdown(st_select_lex *sel) +{ + Item_equal_fields_iterator it(*this); + Item *item; + + while ((item=it++)) + { + if (item->excl_dep_on_group_fields_for_having_pushdown(sel)) + { + if (upper_levels) + upper_levels->references--; + set_extraction_flag(FULL_EXTRACTION_FL); + return true; + } + } + return false; +} + + +/** + @brief + Create from this multiple equality equalities that can be pushed down + + @param thd the thread handle + @param equalities the result list of created equalities + @param checker the checker callback function to be applied to the nodes + of the tree of the object + @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. + + Example: + + MULT_EQ(1,a,b) + => + Created equalities: {(1=a),(1=b)} + + MULT_EQ(a,b,c,d) + => + Created equalities: {(a=b),(a=c),(a=d)} + + + @retval true if an error occurs + @retval false otherwise +*/ + +bool Item_equal::create_pushable_equalities(THD *thd, + List<Item> *equalities, + Pushdown_checker checker, + uchar *arg) +{ + Item *item; + Item_equal_fields_iterator it(*this); + Item *left_item = get_const(); + Item *right_item; + if (!left_item) + { + while ((item=it++)) + { + left_item= ((item->*checker) (arg)) ? item : NULL; + if (left_item) + break; + } + } + if (!left_item) + return false; + + while ((item=it++)) + { + right_item= ((item->*checker) (arg)) ? item : NULL; + if (!right_item) + continue; + Item_func_eq *eq= 0; + Item *left_item_clone= left_item->build_clone(thd); + Item *right_item_clone= item->build_clone(thd); + if (left_item_clone && right_item_clone) + { + left_item_clone->set_item_equal(NULL); + right_item_clone->set_item_equal(NULL); + eq= new (thd->mem_root) Item_func_eq(thd, + right_item_clone, + left_item_clone); + } + if (eq && equalities->push_back(eq, thd->mem_root)) + return true; + } + return false; +} diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 882f924..fc0cb4b 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -3198,7 +3198,11 @@ class Item_equal: public Item_bool_func return used_tables() & tab_map; } bool excl_dep_on_in_subq_left_part(Item_in_subselect *subq_pred); - + bool excl_dep_on_group_fields_for_having_pushdown(st_select_lex *sel); + bool create_pushable_equalities(THD *thd, List<Item> *equalities, + Pushdown_checker checker, uchar *arg); + /* Return the number of elements in this multiple equality */ + uint elements_count() { return equal_items.elements; } friend class Item_equal_fields_iterator; bool count_sargable_conds(void *arg); friend class Item_equal_iterator<List_iterator_fast,Item>; @@ -3218,12 +3222,17 @@ class COND_EQUAL: public Sql_alloc COND_EQUAL *upper_levels; /* multiple equalities of upper and levels */ List<Item_equal> current_level; /* list of multiple equalities of the current and level */ + uint references; /* number of conditions that have + reference on this COND_EQUAL */ + uint work_references; /* same as references */ COND_EQUAL() { upper_levels= 0; + references= 0; + work_references= 0; } COND_EQUAL(Item_equal *item, MEM_ROOT *mem_root) - :upper_levels(0) + :upper_levels(0), references(0), work_references(0) { current_level.push_back(item, mem_root); } @@ -3231,11 +3240,27 @@ class COND_EQUAL: public Sql_alloc { max_members= cond_equal.max_members; upper_levels= cond_equal.upper_levels; + references= cond_equal.references; + work_references= cond_equal.work_references; if (cond_equal.current_level.is_empty()) current_level.empty(); else current_level= cond_equal.current_level; } + bool is_empty() + { + return (current_level.elements == 0); + } + void increase_references() + { + references++; + work_references++; + } + void clean_references() + { + references= 0; + work_references= 0; + } }; diff --git a/sql/item_func.h b/sql/item_func.h index 1081f29..3bf5827 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -327,6 +327,11 @@ class Item_func :public Item_func_or_sum, return this; } + bool has_rand_bit() + { + return used_tables() & RAND_TABLE_BIT; + } + bool excl_dep_on_table(table_map tab_map) { if (used_tables() & OUTER_REF_TABLE_BIT) @@ -345,6 +350,13 @@ class Item_func :public Item_func_or_sum, return Item_args::excl_dep_on_in_subq_left_part(subq_pred); } + bool excl_dep_on_group_fields_for_having_pushdown(st_select_lex *sel) + { + if (has_rand_bit()) + return false; + return Item_args::excl_dep_on_group_fields_for_having_pushdown(sel); + } + /* We assume the result of any function that has a TIMESTAMP argument to be timezone-dependent, since a TIMESTAMP value in both numeric and string @@ -2313,6 +2325,8 @@ class Item_udf_func :public Item_func { return type_handler()->Item_get_date_with_warn(thd, this, ltime, fuzzydate); } + bool excl_dep_on_grouping_fields(st_select_lex *sel) + { return false; } }; @@ -3210,6 +3224,8 @@ class Item_func_sp :public Item_func, not_null_tables_cache= 0; return 0; } + bool excl_dep_on_group_fields_for_having_pushdown(st_select_lex *sel) + { return false; } }; diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 228fcd0..2e339f0 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -5554,13 +5554,91 @@ int select_value_catcher::send_data(List<Item> &items) /** @brief - Add new conditions after optimize_cond() call + Set missing links on multiply equalities - @param thd the thread handle - @param cond the condition where to attach new conditions - @param cond_eq IN/OUT the multiple equalities of cond - @param new_conds IN/OUT the list of conditions needed to add - @param cond_value the returned value of the condition + @param thd the thread handle + @param cond the condition to set links for + @param inherited path to all inherited multiple equality items + @param build_cond_equal flag to control if COND_EQUAL for AND-condition + should be built + + @details + The method traverses cond and set links for the upper COND_EQUAL levels + where needed. + If build_cond_equal is set to true it builds for each AND-level except the + external one COND_EQUAL. +*/ + +static +void set_cond_equal_links(THD *thd, Item *cond, COND_EQUAL *inherited, + bool build_cond_equal) +{ + if (cond->type() == Item::FUNC_ITEM && + ((Item_func*) cond)->functype() == Item_func::MULT_EQUAL_FUNC) + { + ((Item_equal *)cond)->upper_levels= inherited; + if (inherited) + inherited->increase_references(); + } + + if (cond->type() != Item::COND_ITEM) + return; + + List_iterator<Item> it(*((Item_cond *)cond)->argument_list()); + Item *item; + while ((item=it++)) + { + if (item->type() != Item::COND_ITEM || + ((Item_cond*) item)->functype() != Item_func::COND_AND_FUNC) + { + set_cond_equal_links(thd, item, inherited, build_cond_equal); + continue; + } + Item_cond_and *and_item= (Item_cond_and *)item; + if (build_cond_equal) + { + COND_EQUAL new_cond_equal; + List_iterator<Item> li(*and_item->argument_list()); + Item *elem; + + while ((elem=li++)) + { + if (elem->type() == Item::FUNC_ITEM && + ((Item_func*) elem)->functype() == Item_func::MULT_EQUAL_FUNC) + { + if (new_cond_equal.current_level.push_back((Item_equal *)elem, + thd->mem_root)) + return; + li.remove(); + } + } + List<Item> *equal_list= + (List<Item> *)&and_item->m_cond_equal.current_level; + and_item->m_cond_equal.copy(new_cond_equal); + and_item->argument_list()->append(equal_list); + } + and_item->m_cond_equal.upper_levels= inherited; + and_item->m_cond_equal.clean_references(); + if (inherited) + inherited->increase_references(); + + set_cond_equal_links(thd, item, &and_item->m_cond_equal, + build_cond_equal); + } +} + + +/** + @brief + Conjunct conditions after optimize_cond() call + + @param thd the thread handle + @param cond the condition where to attach new conditions + @param cond_eq IN/OUT the multiple equalities of cond + @param new_conds IN/OUT the list of conditions needed to add + @param cond_value the returned value of the condition + @param build_cond_equal flag to control if COND_EQUAL elements for + AND-conditions should be built @details The method creates new condition through conjunction of cond and @@ -5576,9 +5654,11 @@ int select_value_catcher::send_data(List<Item> &items) Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, COND_EQUAL **cond_eq, List<Item> &new_conds, - Item::cond_result *cond_value) + Item::cond_result *cond_value, + bool build_cond_equal) { COND_EQUAL new_cond_equal; + COND_EQUAL *inherited= 0; Item *item; Item_equal *equality; bool is_simplified_cond= false; @@ -5651,6 +5731,7 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, and_args->append((List<Item> *) cond_equalities); *cond_eq= &((Item_cond_and *) cond)->m_cond_equal; + inherited= &((Item_cond_and *)cond)->m_cond_equal; propagate_new_equalities(thd, cond, cond_equalities, cond_equal->upper_levels, @@ -5692,21 +5773,12 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, li.rewind(); while ((item=li++)) { - if (item->fix_fields_if_needed(thd, NULL)) + if (!item->is_fixed() && item->fix_fields(thd, NULL)) return NULL; if (item->const_item() && !item->val_int()) is_simplified_cond= true; } - - if (new_conds.elements > 1) - new_conds_list.append(&new_conds); - else - { - li.rewind(); - item= li++; - if (new_conds_list.push_back(item, thd->mem_root)) - return NULL; - } + new_conds_list.append(&new_conds); } if (is_mult_eq) @@ -5716,16 +5788,12 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, 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; - } - (*cond_eq)->copy(new_cond_equal); - } + while ((equality= it++)) + { + if (equality->const_item() && !equality->val_int()) + is_simplified_cond= true; + } - if (new_cond_equal.current_level.elements > 0) - { if (new_cond_equal.current_level.elements + new_conds_list.elements == 1) { @@ -5735,8 +5803,9 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, if (equality->fix_fields(thd, NULL)) return NULL; } - new_conds_list.append((List<Item> *)&new_cond_equal.current_level); + *cond_eq= &new_cond_equal; } + new_conds_list.append((List<Item> *)&new_cond_equal.current_level); if (new_conds_list.elements > 1) { @@ -5746,6 +5815,7 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, and_cond->m_cond_equal.copy(new_cond_equal); cond= (Item *)and_cond; *cond_eq= &((Item_cond_and *)cond)->m_cond_equal; + inherited= &((Item_cond_and *)cond)->m_cond_equal; } else { @@ -5753,7 +5823,7 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, cond= iter++; } - if (cond->fix_fields_if_needed(thd, NULL)) + if (!cond->is_fixed() && cond->fix_fields(thd, NULL)) return NULL; if (new_cond_equal.current_level.elements > 0) @@ -5769,6 +5839,12 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, */ if (is_simplified_cond) cond= cond->remove_eq_conds(thd, cond_value, true); + + if (cond) + { + set_cond_equal_links(thd, cond, inherited, build_cond_equal); + } + return cond; } @@ -6440,33 +6516,27 @@ bool JOIN::choose_tableless_subquery_plan() } -/* - Check if the item exists in the fields list of the left part of - the IN subquery predicate subq_pred and returns its corresponding - item from the select of the right part of subq_pred. -*/ -Item *Item::get_corresponding_field_in_insubq(Item_in_subselect *subq_pred) +bool Item::pushable_equality_checker_for_subquery(uchar *arg) { - DBUG_ASSERT(type() == Item::FIELD_ITEM || - (type() == Item::REF_ITEM && - ((Item_ref *) this)->ref_type() == Item_ref::VIEW_REF)); - - List_iterator<Field_pair> it(subq_pred->corresponding_fields); - Field_pair *ret; - Item_field *field_item= (Item_field *) (real_item()); - while ((ret= it++)) - { - if (field_item->field == ret->field) - return ret->corresponding_item; - } - return NULL; + return + get_corresponding_field_pair(this, + ((Item_in_subselect *)arg)->corresponding_fields); } -bool Item_field::excl_dep_on_in_subq_left_part(Item_in_subselect *subq_pred) +/* + Checks if 'item' or some item equal to it is equal to the field from + some Field_pair of 'pair_list' and returns matching Field_pair or + NULL if the matching Field_pair wasn't found. +*/ + +Field_pair *find_matching_field_pair(Item *item, List<Field_pair> pair_list) { - if (((Item *)this)->get_corresponding_field_in_insubq(subq_pred)) - return true; + Field_pair *field_pair= get_corresponding_field_pair(item, pair_list); + if (field_pair) + return field_pair; + + Item_equal *item_equal= item->get_item_equal(); if (item_equal) { Item_equal_fields_iterator it(*item_equal); @@ -6475,10 +6545,19 @@ bool Item_field::excl_dep_on_in_subq_left_part(Item_in_subselect *subq_pred) { if (equal_item->const_item()) continue; - if (equal_item->get_corresponding_field_in_insubq(subq_pred)) - return true; + field_pair= get_corresponding_field_pair(equal_item, pair_list); + if (field_pair) + return field_pair; } } + return NULL; +} + + +bool Item_field::excl_dep_on_in_subq_left_part(Item_in_subselect *subq_pred) +{ + if (find_matching_field_pair(((Item *) this), subq_pred->corresponding_fields)) + return true; return false; } @@ -6488,7 +6567,7 @@ bool Item_direct_view_ref::excl_dep_on_in_subq_left_part(Item_in_subselect *subq if (item_equal) { DBUG_ASSERT(real_item()->type() == Item::FIELD_ITEM); - if (((Item *)this)->get_corresponding_field_in_insubq(subq_pred)) + if (get_corresponding_field_pair(((Item *)this), subq_pred->corresponding_fields)) return true; } return (*ref)->excl_dep_on_in_subq_left_part(subq_pred); @@ -6552,7 +6631,7 @@ Item *get_corresponding_item(THD *thd, Item *item, (item->type() == Item::REF_ITEM && ((Item_ref *) item)->ref_type() == Item_ref::VIEW_REF)); - Item *corresonding_item; + Field_pair *field_pair; Item_equal *item_equal= item->get_item_equal(); if (item_equal) @@ -6561,15 +6640,20 @@ Item *get_corresponding_item(THD *thd, Item *item, Item *equal_item; while ((equal_item= it++)) { - corresonding_item= - equal_item->get_corresponding_field_in_insubq(subq_pred); - if (corresonding_item) - return corresonding_item; + field_pair= + get_corresponding_field_pair(equal_item, subq_pred->corresponding_fields); + if (field_pair) + return field_pair->corresponding_item; } - return NULL; } else - return item->get_corresponding_field_in_insubq(subq_pred); + { + field_pair= + get_corresponding_field_pair(item, subq_pred->corresponding_fields); + if (field_pair) + return field_pair->corresponding_item; + } + return NULL; } @@ -6845,9 +6929,7 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond) if (!remaining_cond) goto exit; - remaining_cond->walk(&Item::cleanup_excluding_const_fields_processor, - 0, 0); - sel->cond_pushed_into_having= remaining_cond; + sel->mark_or_conds_to_avoid_pushdown(remaining_cond); exit: thd->lex->current_select= save_curr_select; diff --git a/sql/opt_subselect.h b/sql/opt_subselect.h index e81b100..7af818bd 100644 --- a/sql/opt_subselect.h +++ b/sql/opt_subselect.h @@ -26,10 +26,6 @@ int check_and_do_in_subquery_rewrites(JOIN *join); bool convert_join_subqueries_to_semijoins(JOIN *join); int pull_out_semijoin_tables(JOIN *join); bool optimize_semijoin_nests(JOIN *join, table_map all_table_map); -Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, - COND_EQUAL **cond_eq, - List<Item> &new_conds, - Item::cond_result *cond_value); bool setup_degenerate_jtbm_semi_joins(JOIN *join, List<TABLE_LIST> *join_list, List<Item> &eq_list); diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 891a64a..d103912 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -859,6 +859,7 @@ THD::THD(my_thread_id id, bool is_wsrep_applier, bool skip_global_sys_var_lock) create_tmp_table_for_derived= FALSE; save_prep_leaf_list= FALSE; org_charset= 0; + having_pushdown= FALSE; /* Restore THR_THD */ set_current_thd(old_THR_THD); } diff --git a/sql/sql_class.h b/sql/sql_class.h index 56b8aca..76a952b 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -5032,6 +5032,8 @@ class THD: public THD_count, /* this must be first */ LOG_SLOW_DISABLE_ADMIN); query_plan_flags|= QPLAN_ADMIN; } + + bool having_pushdown; }; /** A short cut for thd->get_stmt_da()->set_ok_status(). */ diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index a3a5332..9ffe10e 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1458,10 +1458,10 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived) sl->find_common_window_func_partition_fields(thd); if (!common_partition_fields) continue; - sl->collect_grouping_fields(thd, common_partition_fields); + sl->collect_grouping_fields_for_derived(thd, common_partition_fields); } else - sl->collect_grouping_fields(thd, sl->group_list.first); + sl->collect_grouping_fields_for_derived(thd, sl->group_list.first); Item *remaining_cond= NULL; /* Do 4-6 */ @@ -1483,9 +1483,7 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived) if (!remaining_cond) continue; - remaining_cond->walk(&Item::cleanup_excluding_const_fields_processor, - 0, 0); - sl->cond_pushed_into_having= remaining_cond; + sl->mark_or_conds_to_avoid_pushdown(remaining_cond); } thd->lex->current_select= save_curr_select; DBUG_RETURN(false); diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index df4d8d7..4893ba0 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -2357,6 +2357,7 @@ void st_select_lex::init_query() join= 0; having= prep_having= where= prep_where= 0; cond_pushed_into_where= cond_pushed_into_having= 0; + attach_to_conds.empty(); olap= UNSPECIFIED_OLAP_TYPE; having_fix_field= 0; having_fix_field_for_pushed_cond= 0; @@ -7762,13 +7763,14 @@ void binlog_unsafe_map_init() st_select_lex and saves this fields. */ -void st_select_lex::collect_grouping_fields(THD *thd, - ORDER *grouping_list) +void st_select_lex::collect_grouping_fields_for_derived(THD *thd, + ORDER *grouping_list) { grouping_tmp_fields.empty(); List_iterator<Item> li(join->fields_list); Item *item= li++; - for (uint i= 0; i < master_unit()->derived->table->s->fields; i++, (item=li++)) + for (uint i= 0; i < master_unit()->derived->table->s->fields; + i++, (item=li++)) { for (ORDER *ord= grouping_list; ord; ord= ord->next) { @@ -7782,17 +7784,49 @@ void st_select_lex::collect_grouping_fields(THD *thd, } } + +/** + Collect fields that are used in the GROUP BY of this SELECT +*/ + +bool st_select_lex::collect_grouping_fields(THD *thd) +{ + grouping_tmp_fields.empty(); + + for (ORDER *ord= group_list.first; ord; ord= ord->next) + { + Item *item= *ord->item; + if (item->type() != Item::FIELD_ITEM && + !(item->type() == Item::REF_ITEM && + ((((Item_ref *) item)->ref_type() == Item_ref::VIEW_REF) || + (((Item_ref *) item)->ref_type() == Item_ref::REF)))) + continue; + + Field_pair *grouping_tmp_field= + new Field_pair(((Item_field *)item->real_item())->field, item); + if (grouping_tmp_fields.push_back(grouping_tmp_field, thd->mem_root)) + return false; + } + if (grouping_tmp_fields.elements) + return false; + return true; +} + + /** @brief For a condition check possibility of exraction a formula over grouping fields - - @param cond The condition whose subformulas are to be analyzed + + @param thd The thread handle + @param cond The condition whose subformulas are to be analyzed + @param checker The checker callback function to be applied to the nodes + of the tree of the object @details This method traverses the AND-OR condition cond and for each subformula of the condition it checks whether it can be usable for the extraction of a condition over the grouping fields of this select. The method uses - the call-back parameter check_processor to ckeck whether a primary formula + the call-back parameter checker to ckeck whether a primary formula depends only on grouping fields. The subformulas that are not usable are marked with the flag NO_EXTRACTION_FL. The subformulas that can be entierly extracted are marked with the flag @@ -7806,12 +7840,19 @@ void st_select_lex::collect_grouping_fields(THD *thd, */ void -st_select_lex::check_cond_extraction_for_grouping_fields(Item *cond) +st_select_lex::check_cond_extraction_for_grouping_fields(THD *thd, Item *cond, + Pushdown_checker checker) { + if (thd->having_pushdown && + cond->get_extraction_flag() == NO_EXTRACTION_FL) + return; cond->clear_extraction_flag(); if (cond->type() == Item::COND_ITEM) { - bool and_cond= ((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC; + Item_cond_and *and_cond= + (((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC) ? + ((Item_cond_and*) cond) : 0; + List<Item> *arg_list= ((Item_cond*) cond)->argument_list(); List_iterator<Item> li(*arg_list); uint count= 0; // to count items not containing NO_EXTRACTION_FL @@ -7819,7 +7860,7 @@ st_select_lex::check_cond_extraction_for_grouping_fields(Item *cond) Item *item; while ((item=li++)) { - check_cond_extraction_for_grouping_fields(item); + check_cond_extraction_for_grouping_fields(thd, item, checker); if (item->get_extraction_flag() != NO_EXTRACTION_FL) { count++; @@ -7829,10 +7870,15 @@ st_select_lex::check_cond_extraction_for_grouping_fields(Item *cond) else if (!and_cond) break; } - if ((and_cond && count == 0) || item) + if (item) cond->set_extraction_flag(NO_EXTRACTION_FL); if (count_full == arg_list->elements) + { + if (and_cond != 0 && !and_cond->m_cond_equal.is_empty() && + and_cond->m_cond_equal.upper_levels) + and_cond->m_cond_equal.upper_levels->work_references--; cond->set_extraction_flag(FULL_EXTRACTION_FL); + } if (cond->get_extraction_flag() != 0) { li.rewind(); @@ -7842,7 +7888,7 @@ st_select_lex::check_cond_extraction_for_grouping_fields(Item *cond) } else { - int fl= cond->excl_dep_on_grouping_fields(this) ? + int fl= ((cond->*checker) ((uchar *)this)) ? FULL_EXTRACTION_FL : NO_EXTRACTION_FL; cond->set_extraction_flag(fl); } @@ -8428,130 +8474,6 @@ Item *Lex_trim_st::make_item_func_trim(THD *thd) const } -/** - @brief - Extract from given item a condition pushable into WHERE clause - - @param thd the thread handle - @param cond the item to extract a condition to be pushed - into WHERE - @param remaining_cond the condition that will remain of cond after - the pushdown of its parts into the WHERE clause - @param transformer the transformer callback function to be - applied to the condition so it can be pushed - down into the WHERE clause of this select - @param arg parameter to be passed to the transformer - - @details - This method checks if cond entirely or its parts can be - pushed into the WHERE clause of this select and prepares it for pushing. - - First it checks wherever this select doesn't have any aggregation function - in its projection and GROUP BY clause. If so cond can be entirely - pushed into the WHERE clause of this select but before its fields should - be transformed with transformer_for_where to make it pushable. - - Otherwise the method checks wherever any condition depending only on - grouping fields can be extracted from cond. If there is any it prepares it - for pushing using grouping_field_transformer_for_where and if it happens to - be a conjunct of cond it removes it from cond. It saves the result of - removal in remaining_cond. - The extracted condition is saved in cond_pushed_into_where of this select. - - @note - When looking for pushable condition the method considers only the grouping - fields from the list grouping_tmp_fields whose elements are of the type - Field_pair. This list must be prepared before the call of the - function. - - @note - This method is called for pushdown conditions into materialized - derived tables/views optimization. - Item::derived_field_transformer_for_where is passed as the actual - callback function. - Also it is called for pushdown conditions into materialized IN subqueries. - Item::in_subq_field_transformer_for_where is passed as the actual - callback function. -*/ - -void st_select_lex::pushdown_cond_into_where_clause(THD *thd, Item *cond, - Item **remaining_cond, - Item_transformer transformer, - uchar *arg) -{ - if (!cond_pushdown_is_allowed()) - return; - thd->lex->current_select= this; - if (have_window_funcs()) - { - Item *cond_over_partition_fields; - check_cond_extraction_for_grouping_fields(cond); - cond_over_partition_fields= - build_cond_for_grouping_fields(thd, cond, true); - if (cond_over_partition_fields) - cond_over_partition_fields= cond_over_partition_fields->transform(thd, - &Item::grouping_field_transformer_for_where, - (uchar*) this); - if (cond_over_partition_fields) - { - cond_over_partition_fields->walk( - &Item::cleanup_excluding_const_fields_processor, 0, 0); - cond_pushed_into_where= cond_over_partition_fields; - } - - return; - } - - if (!join->group_list && !with_sum_func) - { - cond= - cond->transform(thd, transformer, arg); - if (cond) - { - cond->walk( - &Item::cleanup_excluding_const_fields_processor, 0, 0); - cond_pushed_into_where= cond; - } - - return; - } - - /* - Figure out what can be extracted from cond - that could be pushed into the WHERE clause of this select - */ - Item *cond_over_grouping_fields; - check_cond_extraction_for_grouping_fields(cond); - cond_over_grouping_fields= - build_cond_for_grouping_fields(thd, cond, true); - - /* - Transform the references to the columns from the cond - pushed into the WHERE clause of this select to make them usable in - the new context - */ - if (cond_over_grouping_fields) - cond_over_grouping_fields= cond_over_grouping_fields->transform(thd, - &Item::grouping_field_transformer_for_where, - (uchar*) this); - - if (cond_over_grouping_fields) - { - - /* - In cond remove top conjuncts that has been pushed into the WHERE - clause of this select - */ - cond= remove_pushed_top_conjuncts(thd, cond); - - cond_over_grouping_fields->walk( - &Item::cleanup_excluding_const_fields_processor, 0, 0); - cond_pushed_into_where= cond_over_grouping_fields; - } - - *remaining_cond= cond; -} - Item *LEX::make_item_func_call_generic(THD *thd, Lex_ident_cli_st *cdb, Lex_ident_cli_st *cname, List<Item> *args) { @@ -9098,6 +9020,7 @@ bool LEX::parsed_unit_in_brackets(SELECT_LEX_UNIT *unit) } + /** Process tail of unit parsed in brackets */ @@ -9323,6 +9246,7 @@ SELECT_LEX *LEX::parsed_subselect(SELECT_LEX_UNIT *unit, char *place) } + /** Process INSERT-like select */ @@ -9587,3 +9511,687 @@ bool LEX::sp_proc_stmt_statement_finalize(THD *thd, bool no_lookahead) lip->get_tok_start()); return LEX::sp_proc_stmt_statement_finalize_buf(thd, qbuf); } + + +/** + @brief + Extract from given item a condition pushable into WHERE clause + + @param thd the thread handle + @param cond the item to extract a condition to be pushed + into WHERE + @param remaining_cond the condition that will remain of cond after + the pushdown of its parts into the WHERE clause + @param transformer the transformer callback function to be + applied to the condition so it can be pushed + down into the WHERE clause of this select + @param arg parameter to be passed to the transformer + + @details + This method checks if cond entirely or its parts can be + pushed into the WHERE clause of this select and prepares it for pushing. + + First it checks wherever this select doesn't have any aggregation function + in its projection and GROUP BY clause. If so cond can be entirely + pushed into the WHERE clause of this select but before its fields should + be transformed with transformer_for_where to make it pushable. + + Otherwise the method checks wherever any condition depending only on + grouping fields can be extracted from cond. If there is any it prepares it + for pushing using grouping_field_transformer_for_where and if it happens to + be a conjunct of cond it removes it from cond. It saves the result of + removal in remaining_cond. + The extracted condition is saved in cond_pushed_into_where of this select. + + @note + When looking for pushable condition the method considers only the grouping + fields from the list grouping_tmp_fields whose elements are of the type + Field_pair. This list must be prepared before the call of the + function. + + @note + This method is called for pushdown conditions into materialized + derived tables/views optimization. + Item::derived_field_transformer_for_where is passed as the actual + callback function. + Also it is called for pushdown conditions into materialized IN subqueries. + Item::in_subq_field_transformer_for_where is passed as the actual + callback function. +*/ + +void st_select_lex::pushdown_cond_into_where_clause(THD *thd, Item *cond, + Item **remaining_cond, + Item_transformer transformer, + uchar *arg) +{ + if (!cond_pushdown_is_allowed()) + return; + thd->lex->current_select= this; + if (have_window_funcs()) + { + Item *cond_over_partition_fields; + check_cond_extraction_for_grouping_fields(thd, cond, + &Item::dep_on_grouping_fields_checker); + cond_over_partition_fields= + build_cond_for_grouping_fields(thd, cond, true); + if (cond_over_partition_fields) + cond_over_partition_fields= cond_over_partition_fields->transform(thd, + &Item::grouping_field_transformer_for_where, + (uchar*) this); + if (cond_over_partition_fields) + { + cond_over_partition_fields->walk( + &Item::cleanup_excluding_const_fields_processor, 0, 0); + cond_pushed_into_where= cond_over_partition_fields; + } + + return; + } + + if (!join->group_list && !with_sum_func) + { + cond= + cond->transform(thd, transformer, arg); + if (cond) + { + cond->walk( + &Item::cleanup_excluding_const_fields_processor, 0, 0); + cond_pushed_into_where= cond; + } + + return; + } + + /* + Figure out what can be extracted from cond + that could be pushed into the WHERE clause of this select + */ + Item *cond_over_grouping_fields; + check_cond_extraction_for_grouping_fields(thd, cond, + &Item::dep_on_grouping_fields_checker); + cond_over_grouping_fields= + build_cond_for_grouping_fields(thd, cond, true); + + /* + Transform the references to the columns from the cond + pushed into the WHERE clause of this select to make them usable in + the new context + */ + if (cond_over_grouping_fields) + cond_over_grouping_fields= cond_over_grouping_fields->transform(thd, + &Item::grouping_field_transformer_for_where, + (uchar*) this); + + if (cond_over_grouping_fields) + { + + /* + In cond remove top conjuncts that has been pushed into the WHERE + clause of this select + */ + cond= remove_pushed_top_conjuncts(thd, cond); + + cond_over_grouping_fields->walk( + &Item::cleanup_excluding_const_fields_processor, 0, 0); + cond_pushed_into_where= cond_over_grouping_fields; + } + + *remaining_cond= cond; +} + + +/** + @brief + Mark OR-conditions as non-pushable to avoid repeatable pushdown + + @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: + + SELECT * + FROM t1, + ( + SELECT a,MAX(c) AS m_c + GROUP BY a + ) AS dt + 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: + + SELECT * + FROM t1, + ( + SELECT a,MAX(c) AS m_c + WHERE (dt.a>2) OR (dt.a<3) + GROUP BY a + HAVING ((dt.m_c>10) AND (dt.a>2)) OR ((dt.m_c<7) and (dt.a<3)) + ) AS dt + 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. +*/ + +void st_select_lex::mark_or_conds_to_avoid_pushdown(Item *cond) +{ + cond->walk(&Item::cleanup_excluding_const_fields_processor, 0, 0); + + if (cond->type() == Item::COND_ITEM && + ((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC) + { + List_iterator<Item> li(*((Item_cond*) cond)->argument_list()); + Item *item; + while ((item=li++)) + { + if (item->type() == Item::COND_ITEM && + ((Item_cond*) item)->functype() == Item_func::COND_OR_FUNC) + item->set_extraction_flag(NO_EXTRACTION_FL); + } + } + else if (cond->type() == Item::COND_ITEM && + ((Item_cond*) cond)->functype() == Item_func::COND_OR_FUNC) + cond->set_extraction_flag(NO_EXTRACTION_FL); + + cond_pushed_into_having= cond; +} + +/** + @brief + Gets conditions that can be pushed down for pushdown 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 + + @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 out + 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(). + + @retval + true - if an error occurs + false - otherwise +*/ + +bool +st_select_lex::build_pushable_cond_for_having_pushdown(THD *thd, + Item *cond, + Pushdown_checker checker) +{ + bool is_multiple_equality= cond->type() == Item::FUNC_ITEM && + ((Item_func*) cond)->functype() == Item_func::MULT_EQUAL_FUNC; + + if (cond->get_extraction_flag() == NO_EXTRACTION_FL) + return false; + + if (cond->type() == Item::COND_ITEM) + { + 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++)) + { + if (item->get_extraction_flag() == NO_EXTRACTION_FL) + continue; + + if (item->type() == Item::FUNC_ITEM && + ((Item_func*) item)->functype() == Item_func::MULT_EQUAL_FUNC) + { + equalities.empty(); + if (((Item_equal*) item)->create_pushable_equalities(thd, &equalities, + checker, (uchar *)this)) + 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; + } + + Item *fix= item->build_pushable_cond(thd, checker, (uchar *)this); + + if (!fix && !cond_and) + { + attach_to_conds.empty(); + return false; + } + if (!fix) + continue; + + if (new_conds.push_back(fix, thd->mem_root)) + return true; + } + if (!cond_and) + { + 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; + } + 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) + { + Item *copy= cond->build_clone(thd); + if (attach_to_conds.push_back(copy, thd->mem_root)) + return true; + } + return false; +} + + +/** + Check if the item is equal to some field in Field_pair 'field_pair' + from 'pair_list' and return found 'field_pair' if it exists. +*/ + +Field_pair *get_corresponding_field_pair(Item *item, + List<Field_pair> pair_list) +{ + DBUG_ASSERT(item->type() == Item::FIELD_ITEM || + (item->type() == Item::REF_ITEM && + ((((Item_ref *) item)->ref_type() == Item_ref::VIEW_REF) || + (((Item_ref *) item)->ref_type() == Item_ref::REF)))); + + List_iterator<Field_pair> it(pair_list); + Field_pair *field_pair; + Item_field *field_item= (Item_field *) (item->real_item()); + while ((field_pair= it++)) + { + if (field_item->field == field_pair->field) + return field_pair; + } + return NULL; +} + + +/** + @brief + Find fields in WHERE clause multiple equalities that can be used in pushdown + + @param thd The thread handle + + @details + This method looks through the multiple equalities of the WHERE clause + trying to find any of them which fields are used in the GROUP BY of the + SELECT. If such multiple equality exists conditions in the HAVING + clause that use fields of this multiple equality can be pushed down + into the WHERE clause as well as the conditions depended on the fields + from the GROUP BY or fields equal to them that are taken from the HAVING + clause multiple equalities. + + Example: + + SELECT a,MAX(b),c + FROM t1 + WHERE (t1.a=t1.c) + GROUP BY t1.a + HAVING (t1.c>1) + + => + + SELECT a,MAX(b),c + FROM t1 + WHERE (t1.a=t1.c) AND (t1.c>1) + GROUP BY t1.a + + @retval + true - if an error occurs + false - otherwise +*/ + +bool st_select_lex::collect_fields_equal_to_grouping(THD *thd) +{ + if (!join->cond_equal || join->cond_equal->is_empty()) + return false; + + List_iterator_fast<Item_equal> li(join->cond_equal->current_level); + Item_equal *item_equal; + + while ((item_equal= li++)) + { + Item_equal_fields_iterator it(*item_equal); + Item *item; + while ((item= it++)) + { + if (item->type() != Item::FIELD_ITEM && + item->type() != Item::REF_ITEM) + continue; + + if (get_corresponding_field_pair(item, grouping_tmp_fields)) + break; + } + if (!item) + break; + it.rewind(); + + while ((item= it++)) + { + if ((item->type() != Item::FIELD_ITEM && + item->type() != Item::REF_ITEM) || + get_corresponding_field_pair(item, grouping_tmp_fields)) + continue; + Field_pair *grouping_tmp_field= + new Field_pair(((Item_field *)item->real_item())->field, item); + if (grouping_tmp_fields.push_back(grouping_tmp_field, thd->mem_root)) + return true; + } + } + return false; +} + +/** + @brief + Cleanup and fix for 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_inequalities_for_having_pushdown(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_inequalities_for_having_pushdown(thd, item); + } + else + { + cond->walk(&Item::cleanup_excluding_const_fields_processor, 0, 0); + if (cond->fix_fields(thd, NULL)) + return true; + } + return false; +} + + +/** + @brief + Remove marked top conjuncts of condition for pushdown from HAVING into WHERE + + @param thd The thread handle + @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 there comes a problem with multiple equalities removal. + It is solved with the removal from multiple equalities list 'm_cond_equal' + of 'cond' conditions that are marked with the FULL_EXTRACTION_FLAG flag. + + @retval + condition without removed subformulas + 0 if the whole 'cond' is removed +*/ + +Item *remove_pushed_top_conjuncts_for_having(THD *thd, Item *cond) +{ + if (cond->get_extraction_flag() == FULL_EXTRACTION_FL) + { + cond->clear_extraction_flag(); + return 0; + } + if (cond->type() != Item::COND_ITEM) + return cond; + + if (((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC) + { + List<Item> *cond_arg_list= ((Item_cond_and *)cond)->argument_list(); + List<Item_equal> *cond_equalities= + &((Item_cond_and*) cond)->m_cond_equal.current_level; + cond_arg_list->disjoin((List<Item> *) cond_equalities); + List_iterator<Item_equal> it(*cond_equalities); + Item_equal *eq_item; + + if (((Item_cond_and*) cond)->m_cond_equal.work_references == 0) + { + while ((eq_item= it++)) + { + if (eq_item->get_extraction_flag() == FULL_EXTRACTION_FL) + { + eq_item->clear_extraction_flag(); + it.remove(); + } + } + ((Item_cond_and*) cond)->m_cond_equal.clean_references(); + } + else + { + while ((eq_item= it++)) + eq_item->clear_extraction_flag(); + ((Item_cond_and*) cond)->m_cond_equal.work_references= + ((Item_cond_and*) cond)->m_cond_equal.references; + } + cond_arg_list->append((List<Item> *) cond_equalities); + List_iterator<Item> li(*cond_arg_list); + Item *item; + while ((item= li++)) + { + if (item->get_extraction_flag() == FULL_EXTRACTION_FL) + { + item->clear_extraction_flag(); + li.remove(); + } + } + switch (cond_arg_list->elements) + { + case 0: + return 0; + case 1: + return (cond_arg_list->head()); + default: + return cond; + } + } + return cond; +} + + +/** + @brief + Extract condition that can be pushed from HAVING clause into WHERE clause + + @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 + through equality) that can be extracted from the HAVING clause of this + select having and pushes it into the WHERE clause of this select. + + Example of the transformation: + + SELECT t1.a,MAX(t1.b) + FROM t1 + GROUP BY t1.a + HAVING (t1.a>2) AND (MAX(c)>12); + + => + + SELECT t1.a,MAX(t1.b) + FROM t1 + WHERE (t1.a>2) + GROUP BY t1.a + HAVING (MAX(c)>12); + + 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 attach them to the grouping_fields list. + 3. Search for the conditions in the HAVING clause of this select + that depends only on grouping_fields. Store them in the + attach_to_conds list. + 4. Remove pushable conditions from the HAVING clause having. + + @note + This method is similar to st_select_lex::pushdown_cond_into_where_clause(). + + @retval TRUE if an error occurs + @retval FALSE otherwise +*/ + +Item *st_select_lex::pushdown_from_having_into_where(THD *thd, Item *having) +{ + if (!having || !group_list.first) + return having; + if (!cond_pushdown_is_allowed()) + return having; + + st_select_lex *save_curr_select= thd->lex->current_select; + thd->lex->current_select= this; + + /* + 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 attach them to the grouping_fields list. + */ + if (have_window_funcs()) + { + if (group_list.first || join->implicit_grouping) + return having; + ORDER *common_partition_fields= + find_common_window_func_partition_fields(thd); + if (!common_partition_fields || + collect_grouping_fields(thd) || + collect_fields_equal_to_grouping(thd)) + return having; + } + else if (collect_grouping_fields(thd) || + collect_fields_equal_to_grouping(thd)) + return having; + + /* + 3. Search for the conditions in the HAVING clause of this select + that depends only on grouping_fields. Store them in the + attach_to_conds list. + */ + thd->having_pushdown= true; + List_iterator_fast<Item> it(attach_to_conds); + Item *item; + 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, + &Item::pushable_equality_checker_for_having_pushdown)) + { + attach_to_conds.empty(); + goto exit; + } + if (attach_to_conds.elements != 0) + { + /* + 4. Remove pushable conditions from the HAVING clause having. + */ + having= remove_pushed_top_conjuncts_for_having(thd, having); + + it.rewind(); + while ((item=it++)) + { + if (cleanup_inequalities_for_having_pushdown(thd, item)) + { + attach_to_conds.empty(); + goto exit; + } + } + /* + Refresh having_equal as some of the multiple equalities of + having can be removed after pushdown. + */ + join->having_equal= 0; + if (having) + { + if (having->type() == Item::COND_ITEM && + ((Item_cond*) having)->functype() == Item_func::COND_AND_FUNC) + { + Item_cond_and *and_having= (Item_cond_and *)having; + join->having_equal= &and_having->m_cond_equal; + } + if (having->type() == Item::FUNC_ITEM && + ((Item_func*) having)->functype() == Item_func::MULT_EQUAL_FUNC) + join->having_equal= new (thd->mem_root) COND_EQUAL((Item_equal *)having, + thd->mem_root); + } + } +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 3f3fef8..93aa714 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -999,10 +999,11 @@ typedef class st_select_lex_unit SELECT_LEX_UNIT; typedef Bounds_checked_array<Item*> Ref_ptr_array; -/* +/** Structure which consists of the field and the item that corresponds to this field. */ + class Field_pair :public Sql_alloc { public: @@ -1012,6 +1013,10 @@ class Field_pair :public Sql_alloc :field(fld), corresponding_item(item) {} }; +Field_pair *get_corresponding_field_pair(Item *item, + List<Field_pair> pair_list); +Field_pair *find_matching_field_pair(Item *item, List<Field_pair> pair_list); + /* SELECT_LEX - store information of parsed SELECT statment @@ -1043,6 +1048,7 @@ class st_select_lex: public st_select_lex_node Item *prep_having;/* saved HAVING clause for prepared statement processing */ Item *cond_pushed_into_where; /* condition pushed into the select's WHERE */ Item *cond_pushed_into_having; /* condition pushed into the select's HAVING */ + List<Item> attach_to_conds; /* Saved values of the WHERE and HAVING clauses*/ Item::cond_result cond_value, having_value; /* @@ -1467,8 +1473,11 @@ class st_select_lex: public st_select_lex_node With_element *find_table_def_in_with_clauses(TABLE_LIST *table); bool check_unrestricted_recursive(bool only_standard_compliant); bool check_subqueries_with_recursive_references(); - void collect_grouping_fields(THD *thd, ORDER *grouping_list); - void check_cond_extraction_for_grouping_fields(Item *cond); + void collect_grouping_fields_for_derived(THD *thd, ORDER *grouping_list); + bool collect_grouping_fields(THD *thd); + bool collect_fields_equal_to_grouping(THD *thd); + void check_cond_extraction_for_grouping_fields(THD *thd, Item *cond, + Pushdown_checker excl_dep); Item *build_cond_for_grouping_fields(THD *thd, Item *cond, bool no_to_clones); @@ -1494,10 +1503,15 @@ class st_select_lex: public st_select_lex_node bool cond_pushdown_is_allowed() const { return !olap && !explicit_limit && !tvc; } + bool build_pushable_cond_for_having_pushdown(THD *thd, + Item *cond, + Pushdown_checker checker); void pushdown_cond_into_where_clause(THD *thd, Item *extracted_cond, Item **remaining_cond, Item_transformer transformer, uchar *arg); + void mark_or_conds_to_avoid_pushdown(Item *cond); + Item *pushdown_from_having_into_where(THD *thd, Item *having); select_handler *find_select_handler(THD *thd); diff --git a/sql/sql_priv.h b/sql/sql_priv.h index 00d1616..68603c4 100644 --- a/sql/sql_priv.h +++ b/sql/sql_priv.h @@ -234,7 +234,7 @@ #define OPTIMIZER_SWITCH_SPLIT_MATERIALIZED (1ULL << 31) #define OPTIMIZER_SWITCH_COND_PUSHDOWN_FOR_SUBQUERY (1ULL << 32) #define OPTIMIZER_SWITCH_USE_ROWID_FILTER (1ULL << 33) - +#define OPTIMIZER_SWITCH_COND_PUSHDOWN_FROM_HAVING_INTO_WHERE (1ULL << 33) #define OPTIMIZER_SWITCH_DEFAULT (OPTIMIZER_SWITCH_INDEX_MERGE | \ OPTIMIZER_SWITCH_INDEX_MERGE_UNION | \ @@ -263,7 +263,8 @@ OPTIMIZER_SWITCH_COND_PUSHDOWN_FOR_DERIVED | \ OPTIMIZER_SWITCH_SPLIT_MATERIALIZED | \ OPTIMIZER_SWITCH_COND_PUSHDOWN_FOR_SUBQUERY |\ - OPTIMIZER_SWITCH_USE_ROWID_FILTER) + OPTIMIZER_SWITCH_USE_ROWID_FILTER | \ + OPTIMIZER_SWITCH_COND_PUSHDOWN_FROM_HAVING_INTO_WHERE) /* Replication uses 8 bytes to store SQL_MODE in the binary log. The day you diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 3670eff..3d1b530 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1870,10 +1870,10 @@ JOIN::optimize_inner() select_lex->having_fix_field_for_pushed_cond= 0; } } - + conds= optimize_cond(this, conds, join_list, FALSE, &cond_value, &cond_equal, OPT_LINK_EQUAL_FIELDS); - + if (thd->is_error()) { error= 1; @@ -1881,6 +1881,33 @@ JOIN::optimize_inner() DBUG_RETURN(1); } + having= optimize_cond(this, having, join_list, TRUE, + &having_value, &having_equal); + + if (thd->is_error()) + { + error= 1; + DBUG_PRINT("error",("Error from optimize_cond")); + DBUG_RETURN(1); + } + + if (thd->lex->sql_command == SQLCOM_SELECT && + optimizer_flag(thd, + OPTIMIZER_SWITCH_COND_PUSHDOWN_FROM_HAVING_INTO_WHERE)) + { + having= + select_lex->pushdown_from_having_into_where(thd, having); + if (select_lex->attach_to_conds.elements != 0) + { + 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(); + } + } + if (optimizer_flag(thd, OPTIMIZER_SWITCH_COND_PUSHDOWN_FOR_SUBQUERY)) { TABLE_LIST *tbl; @@ -1899,13 +1926,48 @@ JOIN::optimize_inner() if (eq_list.elements != 0) { conds= and_new_conditions_to_optimized_cond(thd, conds, &cond_equal, - eq_list, &cond_value); + eq_list, &cond_value, false); if (!conds && cond_value != Item::COND_FALSE && cond_value != Item::COND_TRUE) DBUG_RETURN(TRUE); } + { + if (select_lex->where) + { + select_lex->cond_value= cond_value; + if (sel->where != conds && cond_value == Item::COND_OK) + thd->change_item_tree(&sel->where, conds); + } + if (select_lex->having) + { + select_lex->having_value= having_value; + if (sel->having != having && having_value == Item::COND_OK) + thd->change_item_tree(&sel->having, having); + } + if (cond_value == Item::COND_FALSE || having_value == Item::COND_FALSE || + (!unit->select_limit_cnt && !(select_options & OPTION_FOUND_ROWS))) + { /* Impossible cond */ + if (unit->select_limit_cnt) + { + DBUG_PRINT("info", (having_value == Item::COND_FALSE ? + "Impossible HAVING" : "Impossible WHERE")); + zero_result_cause= having_value == Item::COND_FALSE ? + "Impossible HAVING" : "Impossible WHERE"; + } + else + { + DBUG_PRINT("info", ("Zero limit")); + zero_result_cause= "Zero limit"; + } + table_count= top_join_tab_count= 0; + error= 0; + subq_exit_fl= true; + goto setup_subq_exit; + } + } + if (optimizer_flag(thd, OPTIMIZER_SWITCH_COND_PUSHDOWN_FOR_DERIVED)) { TABLE_LIST *tbl; @@ -1944,50 +2006,6 @@ JOIN::optimize_inner() DBUG_RETURN(1); } - { - having= optimize_cond(this, having, join_list, TRUE, - &having_value, &having_equal); - - if (unlikely(thd->is_error())) - { - error= 1; - DBUG_PRINT("error",("Error from optimize_cond")); - DBUG_RETURN(1); - } - if (select_lex->where) - { - select_lex->cond_value= cond_value; - if (sel->where != conds && cond_value == Item::COND_OK) - thd->change_item_tree(&sel->where, conds); - } - if (select_lex->having) - { - select_lex->having_value= having_value; - if (sel->having != having && having_value == Item::COND_OK) - thd->change_item_tree(&sel->having, having); - } - if (cond_value == Item::COND_FALSE || having_value == Item::COND_FALSE || - (!unit->select_limit_cnt && !(select_options & OPTION_FOUND_ROWS))) - { /* Impossible cond */ - if (unit->select_limit_cnt) - { - DBUG_PRINT("info", (having_value == Item::COND_FALSE ? - "Impossible HAVING" : "Impossible WHERE")); - zero_result_cause= having_value == Item::COND_FALSE ? - "Impossible HAVING" : "Impossible WHERE"; - } - else - { - DBUG_PRINT("info", ("Zero limit")); - zero_result_cause= "Zero limit"; - } - table_count= top_join_tab_count= 0; - error= 0; - subq_exit_fl= true; - goto setup_subq_exit; - } - } - #ifdef WITH_PARTITION_STORAGE_ENGINE { TABLE_LIST *tbl; @@ -2263,6 +2281,22 @@ int JOIN::optimize_stage2() "after substitute_best_equal", QT_ORDINARY);); } + if (having) + { + having= substitute_for_best_equal_field(thd, NO_PARTICULAR_TAB, having, + having_equal, map2table); + if (thd->is_error()) + { + error= 1; + DBUG_PRINT("error",("Error from substitute_for_best_equal")); + DBUG_RETURN(1); + } + having->update_used_tables(); + DBUG_EXECUTE("having", + print_where(having, + "after substitute_best_equal", + QT_ORDINARY);); + } /* Perform the optimization on fields evaluation mentioned above @@ -5177,7 +5211,11 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, { if (*s->on_expr_ref && s->cond_equal && s->cond_equal->upper_levels == orig_cond_equal) + { s->cond_equal->upper_levels= join->cond_equal; + if (s->cond_equal->upper_levels) + s->cond_equal->upper_levels->references++; + } } } } @@ -14171,14 +14209,16 @@ bool check_simple_equality(THD *thd, const Item::Context &ctx, Item *orig_left_item= left_item; Item *orig_right_item= right_item; if (left_item->type() == Item::REF_ITEM && - ((Item_ref*)left_item)->ref_type() == Item_ref::VIEW_REF) + (((Item_ref*)left_item)->ref_type() == Item_ref::VIEW_REF || + ((Item_ref*)left_item)->ref_type() == Item_ref::REF)) { if (((Item_ref*)left_item)->get_depended_from()) return FALSE; left_item= left_item->real_item(); } if (right_item->type() == Item::REF_ITEM && - ((Item_ref*)right_item)->ref_type() == Item_ref::VIEW_REF) + (((Item_ref*)right_item)->ref_type() == Item_ref::VIEW_REF || + ((Item_ref*)right_item)->ref_type() == Item_ref::REF)) { if (((Item_ref*)right_item)->get_depended_from()) return FALSE; @@ -14736,6 +14776,8 @@ COND *Item_func_eq::build_equal_items(THD *thd, set_if_bigger(thd->lex->current_select->max_equal_elems, item_equal->n_field_items()); item_equal->upper_levels= inherited; + if (inherited) + inherited->increase_references(); if (cond_equal_ref) *cond_equal_ref= new (thd->mem_root) COND_EQUAL(item_equal, thd->mem_root); @@ -14770,6 +14812,8 @@ COND *Item_func_eq::build_equal_items(THD *thd, and_cond->update_used_tables(); if (cond_equal_ref) *cond_equal_ref= &and_cond->m_cond_equal; + if (inherited) + inherited->increase_references(); return and_cond; } } @@ -14895,6 +14939,8 @@ static COND *build_equal_items(JOIN *join, COND *cond, if (*cond_equal_ref) { (*cond_equal_ref)->upper_levels= inherited; + if (inherited) + inherited->increase_references(); inherited= *cond_equal_ref; } } @@ -15227,11 +15273,8 @@ Item *eliminate_item_equal(THD *thd, COND *cond, COND_EQUAL *upper_levels, */ Item *head_item= (!item_const && current_sjm && current_sjm_head != field_item) ? current_sjm_head: head; - Item *head_real_item= head_item->real_item(); - if (head_real_item->type() == Item::FIELD_ITEM) - head_item= head_real_item; - - eq_item= new (thd->mem_root) Item_func_eq(thd, field_item->real_item(), head_item); + + eq_item= new (thd->mem_root) Item_func_eq(thd, field_item, head_item); if (!eq_item || eq_item->set_cmp_func()) return 0; diff --git a/sql/sql_select.h b/sql/sql_select.h index 01daf97..3bb2710 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -225,6 +225,11 @@ Next_select_func setup_end_select_func(JOIN *join, JOIN_TAB *tab); int rr_sequential(READ_RECORD *info); int rr_sequential_and_unpack(READ_RECORD *info); Item *remove_pushed_top_conjuncts(THD *thd, Item *cond); +Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, + COND_EQUAL **cond_eq, + List<Item> &new_conds, + Item::cond_result *cond_value, + bool build_cond_equal); #include "sql_explain.h" @@ -1779,7 +1784,6 @@ class JOIN :public Sql_alloc bool fix_all_splittings_in_plan(); bool transform_in_predicates_into_in_subq(THD *thd); - bool add_equalities_to_where_condition(THD *thd, List<Item> &eq_list); private: /** Create a temporary table to be used for processing DISTINCT/ORDER diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 7343c6e..3ce6ff6 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -2522,6 +2522,7 @@ export const char *optimizer_switch_names[]= "split_materialized", "condition_pushdown_for_subquery", "rowid_filter", + "condition_pushdown_from_having_into_where", "default", NullS }; diff --git a/storage/tokudb/mysql-test/tokudb/r/ext_key_1_innodb.result b/storage/tokudb/mysql-test/tokudb/r/ext_key_1_innodb.result index 55a2cc4..1e1c4d3 100644 --- a/storage/tokudb/mysql-test/tokudb/r/ext_key_1_innodb.result +++ b/storage/tokudb/mysql-test/tokudb/r/ext_key_1_innodb.result @@ -1,7 +1,7 @@ drop table if exists t; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having_into_where=off create table t (id int not null, x int not null, y int not null, primary key(id), key(x)) engine=innodb; insert into t values (0,0,0),(1,1,1),(2,2,2),(3,2,3),(4,2,4); explain select x,id from t force index (x) where x=0 and id=0; diff --git a/storage/tokudb/mysql-test/tokudb/r/ext_key_1_tokudb.result b/storage/tokudb/mysql-test/tokudb/r/ext_key_1_tokudb.result index 1b5998b..a9e843d 100644 --- a/storage/tokudb/mysql-test/tokudb/r/ext_key_1_tokudb.result +++ b/storage/tokudb/mysql-test/tokudb/r/ext_key_1_tokudb.result @@ -1,7 +1,7 @@ drop table if exists t; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having_into_where=off create table t (id int not null, x int not null, y int not null, primary key(id), key(x)) engine=tokudb; insert into t values (0,0,0),(1,1,1),(2,2,2),(3,2,3),(4,2,4); explain select x,id from t force index (x) where x=0 and id=0; diff --git a/storage/tokudb/mysql-test/tokudb/r/ext_key_2_innodb.result b/storage/tokudb/mysql-test/tokudb/r/ext_key_2_innodb.result index 0bba5b7..9b48f6c 100644 --- a/storage/tokudb/mysql-test/tokudb/r/ext_key_2_innodb.result +++ b/storage/tokudb/mysql-test/tokudb/r/ext_key_2_innodb.result @@ -1,7 +1,7 @@ drop table if exists t; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having_into_where=off create table t (a int not null, b int not null, c int not null, d int not null, primary key(a,b), key(c,a)) engine=innodb; insert into t values (0,0,0,0),(0,1,0,1); explain select c,a,b from t where c=0 and a=0 and b=1; diff --git a/storage/tokudb/mysql-test/tokudb/r/ext_key_2_tokudb.result b/storage/tokudb/mysql-test/tokudb/r/ext_key_2_tokudb.result index 556c5ff..b179e5c 100644 --- a/storage/tokudb/mysql-test/tokudb/r/ext_key_2_tokudb.result +++ b/storage/tokudb/mysql-test/tokudb/r/ext_key_2_tokudb.result @@ -1,7 +1,7 @@ drop table if exists t; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having_into_where=off create table t (a int not null, b int not null, c int not null, d int not null, primary key(a,b), key(c,a)) engine=tokudb; insert into t values (0,0,0,0),(0,1,0,1); explain select c,a,b from t where c=0 and a=0 and b=1;