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, 1908 insertions, 0 deletions

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;