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