summaryrefslogtreecommitdiff
path: root/mysql-test/main/union.result
diff options
context:
space:
mode:
authorGalina Shalygina <galina.shalygina@mariadb.com>2018-06-17 19:48:00 +0200
committerIgor Babaev <igor@askmonty.org>2019-02-17 23:38:44 -0800
commit7a77b221f18c74c6e6e04bf7a211647d22a7a8b7 (patch)
tree9eddb8103ee76d7b8a3bb42d92fa3c6f6523e6e2 /mysql-test/main/union.result
parent790b6f5ae2b82f5e2d9c872c52b71b6f5fe0c35a (diff)
downloadmariadb-git-7a77b221f18c74c6e6e04bf7a211647d22a7a8b7.tar.gz
MDEV-7486: Condition pushdown from HAVING into WHERE
Condition can be pushed from the HAVING clause into the WHERE clause if it depends only on the fields that are used in the GROUP BY list or depends on the fields that are equal to grouping fields. Aggregate functions can't be pushed down. How the pushdown is performed on the example: SELECT t1.a,MAX(t1.b) FROM t1 GROUP BY t1.a HAVING (t1.a>2) AND (MAX(c)>12); => SELECT t1.a,MAX(t1.b) FROM t1 WHERE (t1.a>2) GROUP BY t1.a HAVING (MAX(c)>12); The implementation scheme: 1. Extract the most restrictive condition cond from the HAVING clause of the select that depends only on the fields that are used in the GROUP BY list of the select (directly or indirectly through equalities) 2. Save cond as a condition that can be pushed into the WHERE clause of the select 3. Remove cond from the HAVING clause if it is possible The optimization is implemented in the function st_select_lex::pushdown_from_having_into_where(). New test file having_cond_pushdown.test is created.
Diffstat (limited to 'mysql-test/main/union.result')
-rw-r--r--mysql-test/main/union.result4
1 files changed, 2 insertions, 2 deletions
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