diff options
| author | Galina Shalygina <galina.shalygina@mariadb.com> | 2018-06-17 19:48:00 +0200 |
|---|---|---|
| committer | Igor Babaev <igor@askmonty.org> | 2019-02-17 23:38:44 -0800 |
| commit | 7a77b221f18c74c6e6e04bf7a211647d22a7a8b7 (patch) | |
| tree | 9eddb8103ee76d7b8a3bb42d92fa3c6f6523e6e2 | |
| parent | 790b6f5ae2b82f5e2d9c872c52b71b6f5fe0c35a (diff) | |
| download | mariadb-git-7a77b221f18c74c6e6e04bf7a211647d22a7a8b7.tar.gz | |
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.
45 files changed, 3770 insertions, 655 deletions
diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 473415ac099..75b5a50ffa7 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 076d39c1abd..4c9481748c5 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 c0c6e8c6a7e..814c781e0d0 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 7a49dbdf3f8..b6a01d91087 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 a220068bc38..18066c99134 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 00000000000..15556387a7d --- /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=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=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=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=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=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=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=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=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=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=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" + } + } + } + } +} +# conjunctive subformula : no aggregation formula pushdown +set statement optimizer_switch='condition_pushdown_from_having=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=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=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=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=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=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=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=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=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=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=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=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=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=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=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=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=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=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=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=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=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=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=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=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=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=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=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=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=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=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=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=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=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=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" + } + } + } + } +} +# conjuctive subformula : pushdown using WHERE multiple equalities +set statement optimizer_switch='condition_pushdown_from_having=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=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=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=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=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=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" + } + } + } + } +} +# extracted OR-formula : pushdown using WHERE multiple equalities +set statement optimizer_switch='condition_pushdown_from_having=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=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)" + } + } + } + } +} +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 00000000000..2af9d58bc4d --- /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=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 06248895bd3..05feaa8c774 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 2482fd91103..eee32c05a4b 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 d1d751c1c91..f341c4be2c6 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 f83bf1bf61e..6b0b3576c8a 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 --optimizer-trace=name Controls tracing of the Optimizer: optimizer_trace=option=val[,option=val...], where option @@ -1571,7 +1572,7 @@ old-style-user-limits FALSE optimizer-prune-level 1 optimizer-search-depth 62 optimizer-selectivity-sampling-limit 100 -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=on optimizer-trace optimizer-trace-max-mem-size 1048576 optimizer-use-condition-selectivity 4 diff --git a/mysql-test/main/select.result b/mysql-test/main/select.result index f1a976b4b8e..a527459657a 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 8f1539b4ea6..c1e9e9d3ad5 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 f1a976b4b8e..a527459657a 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 799adf1d116..0eb40c9be00 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 3cd45d11a62..29298ab24a5 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 e5a6f28aa71..84de97d6b8e 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 00f5acc8803..320334565be 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 0c526e0d4a3..637e7385685 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 4d82e53c0e8..a7688a14fce 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 8288588f6db..97c6410ef5d 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 039484b31a7..0f902e09d63 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 aa52edcc8fb..c6b782edaf9 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=on 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=on 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=on 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=on 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=on 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=on 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=on 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=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=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=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=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=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=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=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=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=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=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=on diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result index 628ba002550..723008d1ffd 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -2743,17 +2743,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=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,condition_pushdown_from_having=on 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=on 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,default READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_TRACE 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 da7790f26b7..f2031d7e6b9 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=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,condition_pushdown_from_having=on 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=on 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,default READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_TRACE diff --git a/sql/item.cc b/sql/item.cc index 4387d67e615..a39943e7e36 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 f8580a80edf..8bd03b23fee 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1872,6 +1872,15 @@ public: */ 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 @@ public: { 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 @@ public: /* 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 @@ protected: } 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 @@ public: 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 @@ public: 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 @@ public: { 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 @@ public: 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 821f51ffaf5..743da0d37dd 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 882f92465b1..fc0cb4bf8d0 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -3198,7 +3198,11 @@ public: 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 @@ public: 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 @@ public: { 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 1081f2919c8..3bf58275f52 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -327,6 +327,11 @@ public: 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 @@ public: 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 @@ public: { 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 @@ public: 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 228fcd0f7e6..2e339f03540 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 e81b100e2a1..7af818bd62d 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 891a64a5748..d10391271a9 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 56b8aca19ab..76a952becee 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -5032,6 +5032,8 @@ public: 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 a3a53320ac0..9ffe10e115d 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 df4d8d7b94c..4893ba0549d 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 3f3fef8a414..93aa7140226 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 @@ public: :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 @@ public: 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 @@ public: 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 @@ public: 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 00d1616df1e..d9908d2f2b5 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 (1ULL << 34) #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) /* 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 3670eff574e..c598532cc9d 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,32 @@ 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)) + { + 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 +1925,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 +2005,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 +2280,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 +5210,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 +14208,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 +14775,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 +14811,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 +14938,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 +15272,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 01daf972b0b..3bb2710d3a1 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 @@ public: 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 7343c6ea2e7..0472e4881cc 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", "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 55a2cc4b34b..b156f662099 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=on 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 1b5998b1534..0c0f8dbd76f 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=on 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 0bba5b7c47f..1193a27a5d8 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=on 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 556c5ffd897..21284ed4514 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=on 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; |