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.

1 files changed, 475 insertions, 0 deletions

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;