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 /mysql-test | |
| 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.
Diffstat (limited to 'mysql-test')
25 files changed, 2479 insertions, 283 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 |