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