From 7a77b221f18c74c6e6e04bf7a211647d22a7a8b7 Mon Sep 17 00:00:00 2001 From: Galina Shalygina Date: Sun, 17 Jun 2018 19:48:00 +0200 Subject: MDEV-7486: Condition pushdown from HAVING into WHERE Condition can be pushed from the HAVING clause into the WHERE clause if it depends only on the fields that are used in the GROUP BY list or depends on the fields that are equal to grouping fields. Aggregate functions can't be pushed down. How the pushdown is performed on the example: SELECT t1.a,MAX(t1.b) FROM t1 GROUP BY t1.a HAVING (t1.a>2) AND (MAX(c)>12); => SELECT t1.a,MAX(t1.b) FROM t1 WHERE (t1.a>2) GROUP BY t1.a HAVING (MAX(c)>12); The implementation scheme: 1. Extract the most restrictive condition cond from the HAVING clause of the select that depends only on the fields that are used in the GROUP BY list of the select (directly or indirectly through equalities) 2. Save cond as a condition that can be pushed into the WHERE clause of the select 3. Remove cond from the HAVING clause if it is possible The optimization is implemented in the function st_select_lex::pushdown_from_having_into_where(). New test file having_cond_pushdown.test is created. --- mysql-test/main/select.result | 2 ++ 1 file changed, 2 insertions(+) (limited to 'mysql-test/main/select.result') 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); -- cgit v1.2.1