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;
#
# MDEV-18668: pushdown from HAVING into impossible WHERE
#
CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES (1,1),(2,2);
SELECT a FROM t1 WHERE b = 1 AND b = 2 GROUP BY a HAVING a <= 3;
a
EXPLAIN
SELECT a FROM t1 WHERE b = 1 AND b = 2 GROUP BY a HAVING a <= 3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
DROP TABLE t1;
#
# MDEV-18769: unfixed OR condition pushed from HAVING into WHERE
#
CREATE TABLE t1(a INT, b INT, c INT);
CREATE TABLE t3(a INT, b INT, c INT, d INT);
INSERT INTO t1 VALUES (1,14,3), (2,13,2), (1,22,1), (3,13,4), (3,14,2);
INSERT INTO t3 VALUES (1,2,16,1), (1,3,11,2), (2,3,10,2);
# nothing to push
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a
HAVING t1.b = 13 AND MAX(t1.c) > 2;
a	b	MAX(t1.c)
3	13	4
SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a
HAVING t1.b = 13 AND MAX(t1.c) > 2;
a	b	MAX(t1.c)
3	13	4
explain SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a
HAVING t1.b = 13 AND MAX(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 temporary; Using filesort
explain format=json SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a
HAVING t1.b = 13 AND MAX(t1.c) > 2;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "having_condition": "t1.b = 13 and max(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 t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a
HAVING t1.b = 13 AND MAX(t1.c) > 2;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "having_condition": "t1.b = 13 and max(t1.c) > 2",
    "filesort": {
      "sort_key": "t1.a",
      "temporary_table": {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows": 5,
          "filtered": 100
        }
      }
    }
  }
}
# extracted AND formula
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.b
HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14);
a	b	MAX(t1.c)
2	13	2
3	13	4
SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.b
HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14);
a	b	MAX(t1.c)
2	13	2
3	13	4
explain SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.b
HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14);
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,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.b
HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "t1.a, t1.b",
      "temporary_table": {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows": 5,
          "filtered": 100,
          "attached_condition": "(t1.a = 1 or t1.b > 10) and t1.b < 14"
        }
      }
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.a = 1 OR t1.b > 10) AND (t1.b < 14)
GROUP BY t1.a,t1.b;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "t1.a, t1.b",
      "temporary_table": {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows": 5,
          "filtered": 100,
          "attached_condition": "(t1.a = 1 or t1.b > 10) and t1.b < 14"
        }
      }
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.b
HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15);
a	b	MAX(t1.c)
1	22	1
2	13	2
3	13	4
SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.b
HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15);
a	b	MAX(t1.c)
1	22	1
2	13	2
3	13	4
explain SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.b
HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15);
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,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.b
HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "t1.a, t1.b",
      "temporary_table": {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows": 5,
          "filtered": 100,
          "attached_condition": "(t1.a = 1 or t1.b > 10) and (t1.b < 14 or t1.b > 15)"
        }
      }
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15)
GROUP BY t1.a,t1.b;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "t1.a, t1.b",
      "temporary_table": {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows": 5,
          "filtered": 100,
          "attached_condition": "(t1.a = 1 or t1.b > 10) and (t1.b < 14 or t1.b > 15)"
        }
      }
    }
  }
}
# extracted AND formula : equality in the inner AND formula
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.b
HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2));
a	b	MAX(t1.c)
2	13	2
3	13	4
SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.b
HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2));
a	b	MAX(t1.c)
2	13	2
3	13	4
explain SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.b
HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 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,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.b
HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2));
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "t1.a, t1.b",
      "temporary_table": {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows": 5,
          "filtered": 100,
          "attached_condition": "(t1.a = 1 or t1.b > 10) and (t1.b < 14 or t1.a = 2 and t1.b > 15)"
        }
      }
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2))
GROUP BY t1.a,t1.b;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "t1.a, t1.b",
      "temporary_table": {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows": 5,
          "filtered": 100,
          "attached_condition": "(t1.a = 1 or t1.b > 10) and (t1.b < 14 or t1.a = 2 and t1.b > 15)"
        }
      }
    }
  }
}
# extracted OR formula
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.b
HAVING (t1.a < 2) OR (t1.b = 13 AND t1.a > 2);
a	b	MAX(t1.c)
1	14	3
1	22	1
3	13	4
SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.b
HAVING (t1.a < 2) OR (t1.b = 13 AND t1.a > 2);
a	b	MAX(t1.c)
1	14	3
1	22	1
3	13	4
explain SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.b
HAVING (t1.a < 2) OR (t1.b = 13 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,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.b
HAVING (t1.a < 2) OR (t1.b = 13 AND t1.a > 2);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "t1.a, t1.b",
      "temporary_table": {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows": 5,
          "filtered": 100,
          "attached_condition": "t1.a < 2 or t1.b = 13 and t1.a > 2"
        }
      }
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.a < 2) OR (t1.b = 13 AND t1.a > 2)
GROUP BY t1.a,t1.b;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "t1.a, t1.b",
      "temporary_table": {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows": 5,
          "filtered": 100,
          "attached_condition": "t1.a < 2 or t1.b = 13 and t1.a > 2"
        }
      }
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.b
HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13);
a	b	MAX(t1.c)
1	14	3
3	13	4
SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.b
HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13);
a	b	MAX(t1.c)
1	14	3
3	13	4
explain SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.b
HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND 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,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.b
HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "t1.a, t1.b",
      "temporary_table": {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows": 5,
          "filtered": 100,
          "attached_condition": "t1.b = 14 and t1.a < 2 or t1.b = 13 and t1.a > 2"
        }
      }
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13)
GROUP BY t1.a,t1.b;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "t1.a, t1.b",
      "temporary_table": {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows": 5,
          "filtered": 100,
          "attached_condition": "t1.b = 14 and t1.a < 2 or t1.b = 13 and t1.a > 2"
        }
      }
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.b
HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14));
a	b	MAX(t1.c)
1	14	3
3	13	4
3	14	2
SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.b
HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14));
a	b	MAX(t1.c)
1	14	3
3	13	4
3	14	2
explain SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.b
HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14));
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,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.b
HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14));
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "t1.a, t1.b",
      "temporary_table": {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows": 5,
          "filtered": 100,
          "attached_condition": "t1.b = 14 and t1.a < 2 or t1.a > 2 and (t1.b = 13 or t1.b = 14)"
        }
      }
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14))
GROUP BY t1.a,t1.b;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "t1.a, t1.b",
      "temporary_table": {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows": 5,
          "filtered": 100,
          "attached_condition": "t1.b = 14 and t1.a < 2 or t1.a > 2 and (t1.b = 13 or t1.b = 14)"
        }
      }
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a
HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2));
a	b	MAX(t1.c)
1	14	3
SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a
HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2));
a	b	MAX(t1.c)
1	14	3
explain SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a
HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR 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,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a
HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2));
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "having_condition": "t1.a < 2 and max(t1.c) = 2 or max(t1.c) > 2 and (t1.a = 1 or t1.a = 2)",
    "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 = 1 or t1.a = 2"
        }
      }
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.a < 2) OR (t1.a = 1 OR t1.a = 2)
GROUP BY t1.a
HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2));
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "having_condition": "t1.a < 2 and max(t1.c) = 2 or max(t1.c) > 2 and (t1.a = 1 or t1.a = 2)",
    "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 = 1 or t1.a = 2"
        }
      }
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a
HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2));
a	b	MAX(t1.c)
1	14	3
2	13	2
SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a
HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2));
a	b	MAX(t1.c)
1	14	3
2	13	2
explain SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a
HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR 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,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a
HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2));
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "having_condition": "t1.a = 2 and max(t1.c) = 2 or max(t1.c) > 2 and (t1.a = 1 or t1.a = 2)",
    "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 = 1 or t1.a = 2"
        }
      }
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.a = 2) OR (t1.a = 1 OR t1.a = 2)
GROUP BY t1.a
HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2));
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "having_condition": "t1.a = 2 and max(t1.c) = 2 or max(t1.c) > 2 and (t1.a = 1 or t1.a = 2)",
    "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 = 1 or t1.a = 2"
        }
      }
    }
  }
}
# conjunctive subformula : equality pushdown
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a
HAVING (t1.a = 1) AND (MAX(t1.c) = 3);
a	b	MAX(t1.c)
1	14	3
SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a
HAVING (t1.a = 1) AND (MAX(t1.c) = 3);
a	b	MAX(t1.c)
1	14	3
explain SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a
HAVING (t1.a = 1) AND (MAX(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
explain format=json SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a
HAVING (t1.a = 1) AND (MAX(t1.c) = 3);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "having_condition": "max(t1.c) = 3",
    "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,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.a = 1)
GROUP BY t1.a
HAVING (MAX(t1.c) = 3);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "having_condition": "max(t1.c) = 3",
    "table": {
      "table_name": "t1",
      "access_type": "ALL",
      "rows": 5,
      "filtered": 100,
      "attached_condition": "t1.a = 1"
    }
  }
}
# conjunctive subformula : equalities pushdown
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.c
HAVING (t1.a = 1) AND (t1.c = 3) AND MAX(t1.b = 14);
a	b	MAX(t1.c)
1	14	3
SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.c
HAVING (t1.a = 1) AND (t1.c = 3) AND MAX(t1.b = 14);
a	b	MAX(t1.c)
1	14	3
explain SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.c
HAVING (t1.a = 1) AND (t1.c = 3) AND MAX(t1.b = 14);
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,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.c
HAVING (t1.a = 1) AND (t1.c = 3) AND MAX(t1.b = 14);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "having_condition": "max(t1.b = 14)",
    "table": {
      "table_name": "t1",
      "access_type": "ALL",
      "rows": 5,
      "filtered": 100,
      "attached_condition": "t1.a = 1 and t1.c = 3"
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.a = 1) AND (t1.c = 3)
GROUP BY t1.a,t1.c
HAVING (MAX(t1.b) = 14);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "having_condition": "max(t1.b) = 14",
    "table": {
      "table_name": "t1",
      "access_type": "ALL",
      "rows": 5,
      "filtered": 100,
      "attached_condition": "t1.a = 1 and t1.c = 3"
    }
  }
}
# conjunctive subformula : multiple equality consists of
two equalities pushdown
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.c
HAVING (t1.a = 1) AND (t1.c = 1) AND MAX(t1.b = 14);
a	b	MAX(t1.c)
SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.c
HAVING (t1.a = 1) AND (t1.c = 1) AND MAX(t1.b = 14);
a	b	MAX(t1.c)
explain SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.c
HAVING (t1.a = 1) AND (t1.c = 1) AND MAX(t1.b = 14);
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,t1.b,MAX(t1.c)
FROM t1
GROUP BY t1.a,t1.c
HAVING (t1.a = 1) AND (t1.c = 1) AND MAX(t1.b = 14);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "having_condition": "max(t1.b = 14)",
    "table": {
      "table_name": "t1",
      "access_type": "ALL",
      "rows": 5,
      "filtered": 100,
      "attached_condition": "t1.a = 1 and t1.c = 1"
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.a = 1) AND (t1.c = 1)
GROUP BY t1.a,t1.c
HAVING (MAX(t1.b) = 14);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "having_condition": "max(t1.b) = 14",
    "table": {
      "table_name": "t1",
      "access_type": "ALL",
      "rows": 5,
      "filtered": 100,
      "attached_condition": "t1.a = 1 and t1.c = 1"
    }
  }
}
#
#  Pushdown from HAVING into non-empty WHERE
#
# inequality : inequality in WHERE
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.b > 2)
GROUP BY t1.a
HAVING (t1.a < 3);
a	b	MAX(t1.c)
1	14	3
2	13	2
SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.b > 2)
GROUP BY t1.a
HAVING (t1.a < 3);
a	b	MAX(t1.c)
1	14	3
2	13	2
explain SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.b > 2)
GROUP BY t1.a
HAVING (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,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.b > 2)
GROUP BY t1.a
HAVING (t1.a < 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.b > 2 and t1.a < 3"
        }
      }
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.b > 2) AND (t1.a < 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.b > 2 and t1.a < 3"
        }
      }
    }
  }
}
# equality : inequality in WHERE
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.b > 2)
GROUP BY t1.a
HAVING (t1.a = 3);
a	b	MAX(t1.c)
3	13	4
SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.b > 2)
GROUP BY t1.a
HAVING (t1.a = 3);
a	b	MAX(t1.c)
3	13	4
explain SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.b > 2)
GROUP BY t1.a
HAVING (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,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.b > 2)
GROUP BY t1.a
HAVING (t1.a = 3);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "t1",
      "access_type": "ALL",
      "rows": 5,
      "filtered": 100,
      "attached_condition": "t1.a = 3 and t1.b > 2"
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.b > 2) AND (t1.a = 3)
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 = 3 and t1.b > 2"
    }
  }
}
# inequality : equality in WHERE
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.b = 14)
GROUP BY t1.a
HAVING (t1.a < 3);
a	b	MAX(t1.c)
1	14	3
SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.b = 14)
GROUP BY t1.a
HAVING (t1.a < 3);
a	b	MAX(t1.c)
1	14	3
explain SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.b = 14)
GROUP BY t1.a
HAVING (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,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.b = 14)
GROUP BY t1.a
HAVING (t1.a < 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.b = 14 and t1.a < 3"
        }
      }
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.b = 14) AND (t1.a < 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.b = 14 and t1.a < 3"
        }
      }
    }
  }
}
# equality : equality in WHERE
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.b = 14)
GROUP BY t1.a
HAVING (t1.a = 1);
a	b	MAX(t1.c)
1	14	3
SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.b = 14)
GROUP BY t1.a
HAVING (t1.a = 1);
a	b	MAX(t1.c)
1	14	3
explain SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.b = 14)
GROUP BY t1.a
HAVING (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
explain format=json SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.b = 14)
GROUP BY t1.a
HAVING (t1.a = 1);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "t1",
      "access_type": "ALL",
      "rows": 5,
      "filtered": 100,
      "attached_condition": "t1.a = 1 and t1.b = 14"
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
FROM t1
WHERE (t1.b = 14) AND (t1.a = 1)
GROUP BY t1.a;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "t1",
      "access_type": "ALL",
      "rows": 5,
      "filtered": 100,
      "attached_condition": "t1.b = 14 and t1.a = 1"
    }
  }
}
# equality : equality in WHERE, impossible WHERE
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.c)
FROM t1
WHERE (t1.a = 3)
GROUP BY t1.a
HAVING (t1.a = 1);
a	MAX(t1.c)
SELECT t1.a,MAX(t1.c)
FROM t1
WHERE (t1.a = 3)
GROUP BY t1.a
HAVING (t1.a = 1);
a	MAX(t1.c)
explain SELECT t1.a,MAX(t1.c)
FROM t1
WHERE (t1.a = 3)
GROUP BY t1.a
HAVING (t1.a = 1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
explain format=json SELECT t1.a,MAX(t1.c)
FROM t1
WHERE (t1.a = 3)
GROUP BY t1.a
HAVING (t1.a = 1);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "message": "Impossible WHERE"
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.c)
FROM t1
WHERE (t1.a = 3) AND (t1.a = 1)
GROUP BY t1.a;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "message": "Impossible WHERE"
    }
  }
}
# equality : equality in WHERE (equal through constant)
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.c = 1)
GROUP BY t1.a
HAVING (t1.a = 1);
a	MAX(t1.b)	c
1	22	1
SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.c = 1)
GROUP BY t1.a
HAVING (t1.a = 1);
a	MAX(t1.b)	c
1	22	1
explain SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.c = 1)
GROUP BY t1.a
HAVING (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
explain format=json SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.c = 1)
GROUP BY t1.a
HAVING (t1.a = 1);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "t1",
      "access_type": "ALL",
      "rows": 5,
      "filtered": 100,
      "attached_condition": "t1.a = 1 and t1.c = 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.c = 1) AND (t1.a = 1)
GROUP BY t1.a;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "t1",
      "access_type": "ALL",
      "rows": 5,
      "filtered": 100,
      "attached_condition": "t1.c = 1 and t1.a = 1"
    }
  }
}
# inequality : AND formula in WHERE
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.c > 0) AND (t1.c < 3)
GROUP BY t1.a
HAVING (t1.a > 1);
a	MAX(t1.b)	c
2	13	2
3	14	2
SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.c > 0) AND (t1.c < 3)
GROUP BY t1.a
HAVING (t1.a > 1);
a	MAX(t1.b)	c
2	13	2
3	14	2
explain SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.c > 0) AND (t1.c < 3)
GROUP BY t1.a
HAVING (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
WHERE (t1.c > 0) AND (t1.c < 3)
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.c > 0 and t1.c < 3 and 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.c > 0) AND (t1.c < 3) 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 > 0 and t1.c < 3 and t1.a > 1"
        }
      }
    }
  }
}
# equality : AND formula in WHERE
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.c > 0) AND (t1.c < 3)
GROUP BY t1.a
HAVING (t1.a = 1);
a	MAX(t1.b)	c
1	22	1
SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.c > 0) AND (t1.c < 3)
GROUP BY t1.a
HAVING (t1.a = 1);
a	MAX(t1.b)	c
1	22	1
explain SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.c > 0) AND (t1.c < 3)
GROUP BY t1.a
HAVING (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
explain format=json SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.c > 0) AND (t1.c < 3)
GROUP BY t1.a
HAVING (t1.a = 1);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "t1",
      "access_type": "ALL",
      "rows": 5,
      "filtered": 100,
      "attached_condition": "t1.a = 1 and t1.c > 0 and t1.c < 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.c > 0) AND (t1.c < 3) AND (t1.a = 1)
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 = 1 and t1.c > 0 and t1.c < 3"
    }
  }
}
# equality : AND formula in WHERE, impossible WHERE
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a > 0) AND (t1.c < 3)
GROUP BY t1.a
HAVING (t1.a = 1);
a	MAX(t1.b)	c
1	22	1
SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a > 0) AND (t1.c < 3)
GROUP BY t1.a
HAVING (t1.a = 1);
a	MAX(t1.b)	c
1	22	1
explain SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a > 0) AND (t1.c < 3)
GROUP BY t1.a
HAVING (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
explain format=json SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a > 0) AND (t1.c < 3)
GROUP BY t1.a
HAVING (t1.a = 1);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "const_condition": "1",
    "table": {
      "table_name": "t1",
      "access_type": "ALL",
      "rows": 5,
      "filtered": 100,
      "attached_condition": "t1.a = 1 and t1.c < 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 > 0) AND (t1.c < 3) AND (t1.a = 1)
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 = 1 and t1.c < 3"
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b)
FROM t1
WHERE (t1.a = 0) AND (t1.a = 3)
GROUP BY t1.a
HAVING (t1.a = 1);
a	MAX(t1.b)
SELECT t1.a,MAX(t1.b)
FROM t1
WHERE (t1.a = 0) AND (t1.a = 3)
GROUP BY t1.a
HAVING (t1.a = 1);
a	MAX(t1.b)
explain SELECT t1.a,MAX(t1.b)
FROM t1
WHERE (t1.a = 0) AND (t1.a = 3)
GROUP BY t1.a
HAVING (t1.a = 1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
explain format=json SELECT t1.a,MAX(t1.b)
FROM t1
WHERE (t1.a = 0) AND (t1.a = 3)
GROUP BY t1.a
HAVING (t1.a = 1);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "message": "Impossible WHERE"
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b)
FROM t1
WHERE (t1.a = 0) AND (t1.a = 3) AND (t1.a = 1)
GROUP BY t1.a;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "message": "Impossible WHERE"
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t3.a,t3.b,MAX(t3.c),t3.d
FROM t3
WHERE (t3.b = 2) AND (t3.d = 1)
GROUP BY t3.a,t3.b,t3.d
HAVING (t3.a = 1);
a	b	MAX(t3.c)	d
1	2	16	1
SELECT t3.a,t3.b,MAX(t3.c),t3.d
FROM t3
WHERE (t3.b = 2) AND (t3.d = 1)
GROUP BY t3.a,t3.b,t3.d
HAVING (t3.a = 1);
a	b	MAX(t3.c)	d
1	2	16	1
explain SELECT t3.a,t3.b,MAX(t3.c),t3.d
FROM t3
WHERE (t3.b = 2) AND (t3.d = 1)
GROUP BY t3.a,t3.b,t3.d
HAVING (t3.a = 1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
explain format=json SELECT t3.a,t3.b,MAX(t3.c),t3.d
FROM t3
WHERE (t3.b = 2) AND (t3.d = 1)
GROUP BY t3.a,t3.b,t3.d
HAVING (t3.a = 1);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "t3",
      "access_type": "ALL",
      "rows": 3,
      "filtered": 100,
      "attached_condition": "t3.b = 2 and t3.d = 1 and t3.a = 1"
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t3.a,t3.b,MAX(t3.c),t3.d
FROM t3
WHERE (t3.b = 2) AND (t3.d = 1) AND (t3.a = 1)
GROUP BY t3.a,t3.b,t3.d;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "t3",
      "access_type": "ALL",
      "rows": 3,
      "filtered": 100,
      "attached_condition": "t3.b = 2 and t3.d = 1 and t3.a = 1"
    }
  }
}
# inequality : OR formula in WHERE
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a > 1) OR (t1.c < 3)
GROUP BY t1.a
HAVING (t1.a < 2);
a	MAX(t1.b)	c
1	22	1
SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a > 1) OR (t1.c < 3)
GROUP BY t1.a
HAVING (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 > 1) OR (t1.c < 3)
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),t1.c
FROM t1
WHERE (t1.a > 1) OR (t1.c < 3)
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 > 1 or t1.c < 3) 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 > 1) OR (t1.c < 3)) AND (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 > 1 or t1.c < 3) and t1.a < 2"
        }
      }
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b)
FROM t1
WHERE (t1.a = 1) OR (t1.a = 3)
GROUP BY t1.a
HAVING (t1.a = 2);
a	MAX(t1.b)
SELECT t1.a,MAX(t1.b)
FROM t1
WHERE (t1.a = 1) OR (t1.a = 3)
GROUP BY t1.a
HAVING (t1.a = 2);
a	MAX(t1.b)
explain SELECT t1.a,MAX(t1.b)
FROM t1
WHERE (t1.a = 1) OR (t1.a = 3)
GROUP BY t1.a
HAVING (t1.a = 2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
explain format=json SELECT t1.a,MAX(t1.b)
FROM t1
WHERE (t1.a = 1) OR (t1.a = 3)
GROUP BY t1.a
HAVING (t1.a = 2);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "message": "Impossible WHERE"
    }
  }
}
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 (t1.a = 3)) AND (t1.a = 2)
GROUP BY t1.a;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "message": "Impossible WHERE"
    }
  }
}
# AND formula : inequality in WHERE
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a > 1)
GROUP BY t1.a
HAVING (t1.a < 4) AND (t1.a > 0);
a	MAX(t1.b)	c
2	13	2
3	14	4
SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a > 1)
GROUP BY t1.a
HAVING (t1.a < 4) AND (t1.a > 0);
a	MAX(t1.b)	c
2	13	2
3	14	4
explain SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a > 1)
GROUP BY t1.a
HAVING (t1.a < 4) AND (t1.a > 0);
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 > 1)
GROUP BY t1.a
HAVING (t1.a < 4) AND (t1.a > 0);
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 and t1.a > 0"
        }
      }
    }
  }
}
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) AND (t1.a < 4) AND (t1.a > 0)
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 and t1.a > 0"
        }
      }
    }
  }
}
# AND formula : equality in WHERE
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1)
GROUP BY t1.a
HAVING (t1.a < 4) AND (t1.a > 0);
a	MAX(t1.b)	c
1	22	3
SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1)
GROUP BY t1.a
HAVING (t1.a < 4) AND (t1.a > 0);
a	MAX(t1.b)	c
1	22	3
explain SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1)
GROUP BY t1.a
HAVING (t1.a < 4) AND (t1.a > 0);
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
WHERE (t1.a = 1)
GROUP BY t1.a
HAVING (t1.a < 4) AND (t1.a > 0);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "const_condition": "1 and 1",
    "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) AND (t1.a < 4) AND (t1.a > 0)
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 = 1"
    }
  }
}
# OR formula : inequality in WHERE
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a > 1)
GROUP BY t1.a
HAVING (t1.a < 4) OR (t1.a > 0);
a	MAX(t1.b)	c
2	13	2
3	14	4
SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a > 1)
GROUP BY t1.a
HAVING (t1.a < 4) OR (t1.a > 0);
a	MAX(t1.b)	c
2	13	2
3	14	4
explain SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a > 1)
GROUP BY t1.a
HAVING (t1.a < 4) OR (t1.a > 0);
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 > 1)
GROUP BY t1.a
HAVING (t1.a < 4) OR (t1.a > 0);
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 or t1.a > 0)"
        }
      }
    }
  }
}
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) AND ((t1.a < 4) OR (t1.a > 0))
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 or t1.a > 0)"
        }
      }
    }
  }
}
# OR formula : equality in WHERE
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1)
GROUP BY t1.a
HAVING (t1.a < 4) OR (t1.a > 0);
a	MAX(t1.b)	c
1	22	3
SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1)
GROUP BY t1.a
HAVING (t1.a < 4) OR (t1.a > 0);
a	MAX(t1.b)	c
1	22	3
explain SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1)
GROUP BY t1.a
HAVING (t1.a < 4) OR (t1.a > 0);
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
WHERE (t1.a = 1)
GROUP BY t1.a
HAVING (t1.a < 4) OR (t1.a > 0);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "const_condition": "1",
    "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) AND ((t1.a < 4) OR (t1.a > 0))
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 = 1"
    }
  }
}
# AND formula : AND formula in WHERE
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a > 1) AND (t1.c < 3)
GROUP BY t1.a
HAVING (t1.a < 4) AND (t1.c > 1);
a	MAX(t1.b)	c
2	13	2
3	14	2
SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a > 1) AND (t1.c < 3)
GROUP BY t1.a
HAVING (t1.a < 4) AND (t1.c > 1);
a	MAX(t1.b)	c
2	13	2
3	14	2
explain SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a > 1) AND (t1.c < 3)
GROUP BY t1.a
HAVING (t1.a < 4) 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
WHERE (t1.a > 1) AND (t1.c < 3)
GROUP BY t1.a
HAVING (t1.a < 4) AND (t1.c > 1);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "having_condition": "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 > 1 and t1.c < 3 and 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 > 1) AND (t1.c < 3)) AND
(t1.a < 4)
GROUP BY t1.a
HAVING (t1.c > 1);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "having_condition": "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 > 1 and t1.c < 3 and t1.a < 4"
        }
      }
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1) AND (t1.c < 3)
GROUP BY t1.a,t1.c
HAVING (t1.a < 4) AND (t1.c > 1);
a	MAX(t1.b)	c
SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1) AND (t1.c < 3)
GROUP BY t1.a,t1.c
HAVING (t1.a < 4) AND (t1.c > 1);
a	MAX(t1.b)	c
explain SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1) AND (t1.c < 3)
GROUP BY t1.a,t1.c
HAVING (t1.a < 4) 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
WHERE (t1.a = 1) AND (t1.c < 3)
GROUP BY t1.a,t1.c
HAVING (t1.a < 4) AND (t1.c > 1);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "const_condition": "1",
    "filesort": {
      "sort_key": "t1.c",
      "temporary_table": {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows": 5,
          "filtered": 100,
          "attached_condition": "t1.a = 1 and t1.c < 3 and t1.c > 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) AND (t1.c < 3)) AND
((t1.a < 4) AND (t1.c > 1))
GROUP BY t1.a,t1.c;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "t1.c",
      "temporary_table": {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows": 5,
          "filtered": 100,
          "attached_condition": "t1.a = 1 and t1.c < 3 and t1.c > 1"
        }
      }
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1) AND (t1.c = 3)
GROUP BY t1.a,t1.c
HAVING (t1.a < 4) AND (t1.c > 1);
a	MAX(t1.b)	c
1	14	3
SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1) AND (t1.c = 3)
GROUP BY t1.a,t1.c
HAVING (t1.a < 4) AND (t1.c > 1);
a	MAX(t1.b)	c
1	14	3
explain SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1) AND (t1.c = 3)
GROUP BY t1.a,t1.c
HAVING (t1.a < 4) 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
explain format=json SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1) AND (t1.c = 3)
GROUP BY t1.a,t1.c
HAVING (t1.a < 4) AND (t1.c > 1);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "const_condition": "1 and 1",
    "table": {
      "table_name": "t1",
      "access_type": "ALL",
      "rows": 5,
      "filtered": 100,
      "attached_condition": "t1.a = 1 and t1.c = 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 = 1) AND (t1.c = 3)) AND
((t1.a < 4) AND (t1.c > 1))
GROUP BY t1.a,t1.c;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "t1",
      "access_type": "ALL",
      "rows": 5,
      "filtered": 100,
      "attached_condition": "t1.a = 1 and t1.c = 3"
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t3.a,t3.b,MAX(t3.c),t3.d
FROM t3
WHERE (t3.a = 1) AND (t3.d = 1)
GROUP BY t3.a,t3.b
HAVING (t3.b = 2) AND (t3.d > 0);
a	b	MAX(t3.c)	d
1	2	16	1
SELECT t3.a,t3.b,MAX(t3.c),t3.d
FROM t3
WHERE (t3.a = 1) AND (t3.d = 1)
GROUP BY t3.a,t3.b
HAVING (t3.b = 2) AND (t3.d > 0);
a	b	MAX(t3.c)	d
1	2	16	1
explain SELECT t3.a,t3.b,MAX(t3.c),t3.d
FROM t3
WHERE (t3.a = 1) AND (t3.d = 1)
GROUP BY t3.a,t3.b
HAVING (t3.b = 2) AND (t3.d > 0);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
explain format=json SELECT t3.a,t3.b,MAX(t3.c),t3.d
FROM t3
WHERE (t3.a = 1) AND (t3.d = 1)
GROUP BY t3.a,t3.b
HAVING (t3.b = 2) AND (t3.d > 0);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "having_condition": "t3.d > 0",
    "table": {
      "table_name": "t3",
      "access_type": "ALL",
      "rows": 3,
      "filtered": 100,
      "attached_condition": "t3.a = 1 and t3.d = 1 and t3.b = 2"
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t3.a,t3.b,MAX(t3.c),t3.d
FROM t3
WHERE (t3.a = 1) AND (t3.d = 1) AND
(t3.b = 2)
GROUP BY t3.a,t3.b
HAVING (t3.d > 0);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "having_condition": "t3.d > 0",
    "table": {
      "table_name": "t3",
      "access_type": "ALL",
      "rows": 3,
      "filtered": 100,
      "attached_condition": "t3.a = 1 and t3.d = 1 and t3.b = 2"
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t3.a,t3.b,MAX(t3.c),t3.d
FROM t3
WHERE (t3.a = 1) AND (t3.d = 1)
GROUP BY t3.a,t3.b,t3.d
HAVING (t3.b = 2) AND (t3.d > 0);
a	b	MAX(t3.c)	d
1	2	16	1
SELECT t3.a,t3.b,MAX(t3.c),t3.d
FROM t3
WHERE (t3.a = 1) AND (t3.d = 1)
GROUP BY t3.a,t3.b,t3.d
HAVING (t3.b = 2) AND (t3.d > 0);
a	b	MAX(t3.c)	d
1	2	16	1
explain SELECT t3.a,t3.b,MAX(t3.c),t3.d
FROM t3
WHERE (t3.a = 1) AND (t3.d = 1)
GROUP BY t3.a,t3.b,t3.d
HAVING (t3.b = 2) AND (t3.d > 0);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
explain format=json SELECT t3.a,t3.b,MAX(t3.c),t3.d
FROM t3
WHERE (t3.a = 1) AND (t3.d = 1)
GROUP BY t3.a,t3.b,t3.d
HAVING (t3.b = 2) AND (t3.d > 0);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "const_condition": "1",
    "table": {
      "table_name": "t3",
      "access_type": "ALL",
      "rows": 3,
      "filtered": 100,
      "attached_condition": "t3.a = 1 and t3.d = 1 and t3.b = 2"
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t3.a,t3.b,MAX(t3.c),t3.d
FROM t3
WHERE (t3.a = 1) AND (t3.d = 1) AND
(t3.b = 2) AND (t3.d > 0)
GROUP BY t3.a,t3.b,t3.d;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "t3",
      "access_type": "ALL",
      "rows": 3,
      "filtered": 100,
      "attached_condition": "t3.a = 1 and t3.d = 1 and t3.b = 2"
    }
  }
}
# AND formula : OR formula in WHERE
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a > 1) OR (t1.c < 3)
GROUP BY t1.a
HAVING (t1.a < 4) AND (t1.c > 1);
a	MAX(t1.b)	c
2	13	2
3	14	4
SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a > 1) OR (t1.c < 3)
GROUP BY t1.a
HAVING (t1.a < 4) AND (t1.c > 1);
a	MAX(t1.b)	c
2	13	2
3	14	4
explain SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a > 1) OR (t1.c < 3)
GROUP BY t1.a
HAVING (t1.a < 4) 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
WHERE (t1.a > 1) OR (t1.c < 3)
GROUP BY t1.a
HAVING (t1.a < 4) AND (t1.c > 1);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "having_condition": "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 > 1 or t1.c < 3) and 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 > 1) OR (t1.c < 3)) AND
(t1.a < 4)
GROUP BY t1.a
HAVING (t1.c > 1);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "having_condition": "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 > 1 or t1.c < 3) and t1.a < 4"
        }
      }
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a > 1) OR (t1.c < 3)
GROUP BY t1.a,t1.c
HAVING (t1.a < 4) AND (t1.c > 1);
a	MAX(t1.b)	c
2	13	2
3	14	2
3	13	4
SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a > 1) OR (t1.c < 3)
GROUP BY t1.a,t1.c
HAVING (t1.a < 4) AND (t1.c > 1);
a	MAX(t1.b)	c
2	13	2
3	14	2
3	13	4
explain SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a > 1) OR (t1.c < 3)
GROUP BY t1.a,t1.c
HAVING (t1.a < 4) 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
WHERE (t1.a > 1) OR (t1.c < 3)
GROUP BY t1.a,t1.c
HAVING (t1.a < 4) AND (t1.c > 1);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "t1.a, t1.c",
      "temporary_table": {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows": 5,
          "filtered": 100,
          "attached_condition": "(t1.a > 1 or t1.c < 3) and t1.a < 4 and t1.c > 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) OR (t1.c < 3)) AND
(t1.a < 4) AND (t1.c > 1)
GROUP BY t1.a,t1.c;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "t1.a, t1.c",
      "temporary_table": {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows": 5,
          "filtered": 100,
          "attached_condition": "(t1.a > 1 or t1.c < 3) and t1.a < 4 and t1.c > 1"
        }
      }
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1) OR (t1.a = 3)
GROUP BY t1.a,t1.c
HAVING (t1.a = 4) OR (t1.c > 1);
a	MAX(t1.b)	c
1	14	3
3	14	2
3	13	4
SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1) OR (t1.a = 3)
GROUP BY t1.a,t1.c
HAVING (t1.a = 4) OR (t1.c > 1);
a	MAX(t1.b)	c
1	14	3
3	14	2
3	13	4
explain SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1) OR (t1.a = 3)
GROUP BY t1.a,t1.c
HAVING (t1.a = 4) OR (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
WHERE (t1.a = 1) OR (t1.a = 3)
GROUP BY t1.a,t1.c
HAVING (t1.a = 4) OR (t1.c > 1);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "t1.a, t1.c",
      "temporary_table": {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows": 5,
          "filtered": 100,
          "attached_condition": "(t1.a = 1 or t1.a = 3) and (t1.a = 4 or t1.c > 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) OR (t1.a = 3)) AND
((t1.a = 4) OR (t1.c > 1))
GROUP BY t1.a,t1.c;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "t1.a, t1.c",
      "temporary_table": {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows": 5,
          "filtered": 100,
          "attached_condition": "(t1.a = 1 or t1.a = 3) and (t1.a = 4 or t1.c > 1)"
        }
      }
    }
  }
}
# equality : pushdown through equality in WHERE
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1) AND (t1.a = t1.c)
GROUP BY t1.a
HAVING (t1.c = 1);
a	MAX(t1.b)	c
1	22	1
SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1) AND (t1.a = t1.c)
GROUP BY t1.a
HAVING (t1.c = 1);
a	MAX(t1.b)	c
1	22	1
explain SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1) AND (t1.a = t1.c)
GROUP BY t1.a
HAVING (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
explain format=json SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1) AND (t1.a = t1.c)
GROUP BY t1.a
HAVING (t1.c = 1);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "t1",
      "access_type": "ALL",
      "rows": 5,
      "filtered": 100,
      "attached_condition": "t1.a = 1 and t1.c = 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) AND (t1.a = t1.c) AND (t1.c = 1)
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 = 1 and t1.c = 1"
    }
  }
}
# OR formula : pushdown through equality
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1) AND (t1.a = t1.c)
GROUP BY t1.a
HAVING (t1.c = 1) OR (t1.c = 2);
a	MAX(t1.b)	c
1	22	1
SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1) AND (t1.a = t1.c)
GROUP BY t1.a
HAVING (t1.c = 1) OR (t1.c = 2);
a	MAX(t1.b)	c
1	22	1
explain SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1) AND (t1.a = t1.c)
GROUP BY t1.a
HAVING (t1.c = 1) OR (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
WHERE (t1.a = 1) AND (t1.a = t1.c)
GROUP BY t1.a
HAVING (t1.c = 1) OR (t1.c = 2);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "t1",
      "access_type": "ALL",
      "rows": 5,
      "filtered": 100,
      "attached_condition": "t1.a = 1 and t1.c = 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) AND (t1.a = t1.c) AND
((t1.c = 1) OR (t1.c = 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 = 1 and t1.c = 1"
    }
  }
}
# OR formula : pushdown through equality, impossible WHERE
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1) AND (t1.a = t1.c)
GROUP BY t1.a
HAVING (t1.c = 3) OR (t1.c = 2);
a	MAX(t1.b)	c
SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1) AND (t1.a = t1.c)
GROUP BY t1.a
HAVING (t1.c = 3) OR (t1.c = 2);
a	MAX(t1.b)	c
explain SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1) AND (t1.a = t1.c)
GROUP BY t1.a
HAVING (t1.c = 3) OR (t1.c = 2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
explain format=json SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1) AND (t1.a = t1.c)
GROUP BY t1.a
HAVING (t1.c = 3) OR (t1.c = 2);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "message": "Impossible WHERE"
    }
  }
}
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) AND (t1.a = t1.c) AND
((t1.c = 3) OR (t1.c = 2))
GROUP BY t1.a;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "message": "Impossible WHERE"
    }
  }
}
# AND formula : pushdown through equality, impossible WHERE
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1)
GROUP BY t1.a,t1.c
HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c);
a	MAX(t1.b)	c
SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1)
GROUP BY t1.a,t1.c
HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c);
a	MAX(t1.b)	c
explain SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1)
GROUP BY t1.a,t1.c
HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
explain format=json SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1)
GROUP BY t1.a,t1.c
HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "message": "Impossible WHERE"
    }
  }
}
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) AND (t1.c = 3) AND
(t1.a > 2) AND (t1.a = t1.c)
GROUP BY t1.a,t1.c;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "message": "Impossible WHERE"
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1)
GROUP BY t1.a
HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c);
a	MAX(t1.b)	c
SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1)
GROUP BY t1.a
HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c);
a	MAX(t1.b)	c
explain SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1)
GROUP BY t1.a
HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
explain format=json SELECT t1.a,MAX(t1.b),t1.c
FROM t1
WHERE (t1.a = 1)
GROUP BY t1.a
HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "message": "Impossible WHERE"
    }
  }
}
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) AND (t1.c = 3) AND
(t1.a > 2) AND (t1.a = t1.c)
GROUP BY t1.a;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "message": "Impossible WHERE"
    }
  }
}
# AND formula with OR subformula : AND condition in WHERE
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t3.a,MAX(t3.b),t3.c,t3.d
FROM t3
WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2))
GROUP BY t3.a
HAVING (t3.a = t3.d) AND ((t3.d = 1) OR (t3.d > 1));
a	MAX(t3.b)	c	d
SELECT t3.a,MAX(t3.b),t3.c,t3.d
FROM t3
WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2))
GROUP BY t3.a
HAVING (t3.a = t3.d) AND ((t3.d = 1) OR (t3.d > 1));
a	MAX(t3.b)	c	d
explain SELECT t3.a,MAX(t3.b),t3.c,t3.d
FROM t3
WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2))
GROUP BY t3.a
HAVING (t3.a = t3.d) AND ((t3.d = 1) OR (t3.d > 1));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	3	Using where; Using temporary; Using filesort
explain format=json SELECT t3.a,MAX(t3.b),t3.c,t3.d
FROM t3
WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2))
GROUP BY t3.a
HAVING (t3.a = t3.d) AND ((t3.d = 1) OR (t3.d > 1));
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "t3.a",
      "temporary_table": {
        "table": {
          "table_name": "t3",
          "access_type": "ALL",
          "rows": 3,
          "filtered": 100,
          "attached_condition": "t3.d = t3.a and t3.a > 1 and (t3.c = 3 or t3.c < 2) and (t3.a = 1 or t3.a > 1)"
        }
      }
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t3.a,MAX(t3.c),t3.d
FROM t3
WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2)) AND
(t3.a = t3.d) AND ((t3.d = 1) OR (t3.d > 1))
GROUP BY t3.a;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "t3.a",
      "temporary_table": {
        "table": {
          "table_name": "t3",
          "access_type": "ALL",
          "rows": 3,
          "filtered": 100,
          "attached_condition": "t3.d = t3.a and t3.a > 1 and (t3.c = 3 or t3.c < 2) and (t3.a = 1 or t3.a > 1)"
        }
      }
    }
  }
}
# AND formula with OR subformula : AND condition in WHERE
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t3.a,MAX(t3.b),t3.c,t3.d
FROM t3
WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2))
GROUP BY t3.a
HAVING (t3.a = t3.d) AND (((t3.d = t3.c) AND (t3.c < 15)) OR (t3.d > 1));
a	MAX(t3.b)	c	d
SELECT t3.a,MAX(t3.b),t3.c,t3.d
FROM t3
WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2))
GROUP BY t3.a
HAVING (t3.a = t3.d) AND (((t3.d = t3.c) AND (t3.c < 15)) OR (t3.d > 1));
a	MAX(t3.b)	c	d
explain SELECT t3.a,MAX(t3.b),t3.c,t3.d
FROM t3
WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2))
GROUP BY t3.a
HAVING (t3.a = t3.d) AND (((t3.d = t3.c) AND (t3.c < 15)) OR (t3.d > 1));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	3	Using where; Using temporary; Using filesort
explain format=json SELECT t3.a,MAX(t3.b),t3.c,t3.d
FROM t3
WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2))
GROUP BY t3.a
HAVING (t3.a = t3.d) AND (((t3.d = t3.c) AND (t3.c < 15)) OR (t3.d > 1));
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "t3.a",
      "temporary_table": {
        "table": {
          "table_name": "t3",
          "access_type": "ALL",
          "rows": 3,
          "filtered": 100,
          "attached_condition": "t3.d = t3.a and t3.a > 1 and (t3.c = 3 or t3.c < 2) and (t3.c = t3.a and t3.c < 15 or t3.a > 1)"
        }
      }
    }
  }
}
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t3.a,t3.b,MAX(t3.c),t3.d
FROM t3
WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2)) AND
(t3.a = t3.d) AND (((t3.d = t3.c) AND (t3.c < 15)) OR (t3.d > 1))
GROUP BY t3.a;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "t3.a",
      "temporary_table": {
        "table": {
          "table_name": "t3",
          "access_type": "ALL",
          "rows": 3,
          "filtered": 100,
          "attached_condition": "t3.d = t3.a and t3.a > 1 and (t3.c = 3 or t3.c < 2) and (t3.c = t3.a and t3.a < 15 or t3.a > 1)"
        }
      }
    }
  }
}
# prepare statement
PREPARE stmt1 from "
SELECT t1.a,MAX(t1.b),t1.c
FROM t1
GROUP BY t1.a
HAVING (t1.a = 1)
";
execute stmt1;
a	MAX(t1.b)	c
1	22	3
execute stmt1;
a	MAX(t1.b)	c
1	22	3
deallocate prepare stmt1;
DROP TABLE t1,t3;
#
# MDEV-19185: pushdown constant function with subquery
#
CREATE TABLE t1 (pk INT, c1 VARCHAR(64));
INSERT INTO t1 VALUES (1,'bbb'),(2,'aaa'),(3,'ccc');
CREATE VIEW v1 AS SELECT * FROM t1;
SELECT pk
FROM t1
GROUP BY pk
HAVING (1 NOT IN (SELECT COUNT(t1.c1) FROM (v1, t1)));
pk
1
2
3
DROP TABLE t1;
DROP VIEW v1;
#
# MDEV-19186: temporary table defined with view field in HAVING
#
CREATE TABLE t1 (pk INT, x VARCHAR(10));
INSERT INTO t1 VALUES  (1,'y'),(2,'s'),(3,'aaa');
CREATE VIEW v1 AS SELECT * FROM t1;
CREATE TABLE t2 (pk INT, x VARCHAR(10));
INSERT INTO t2 VALUES (1,'aa'),(2,'t'),(3,'bb');
CREATE TABLE tmp1
SELECT v1.pk
FROM t2,v1
WHERE v1.x = t2.x
GROUP BY v1.pk
HAVING (v1.pk = 1);
DROP TABLE t1,t2,tmp1;
DROP VIEW v1;
#
# MDEV-19164: pushdown of condition with cached items
#
create table t1 (d1 date);
insert into t1 values (null),('1971-03-06'),('1993-06-05'),('1998-07-08');
select d1 from t1
group by d1
having d1 between (inet_aton('1978-04-27')) and '2018-08-26';
d1
explain extended select d1 from t1
group by d1
having d1 between (inet_aton('1978-04-27')) and '2018-08-26';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using temporary; Using filesort
Warnings:
Note	1003	select `test`.`t1`.`d1` AS `d1` from `test`.`t1` where `test`.`t1`.`d1` between <cache>(inet_aton('1978-04-27')) and <cache>('2018-08-26') group by `test`.`t1`.`d1` having 1
explain format=json select d1 from t1
group by d1
having d1 between (inet_aton('1978-04-27')) and '2018-08-26';
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "t1.d1",
      "temporary_table": {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows": 4,
          "filtered": 100,
          "attached_condition": "t1.d1 between <cache>(inet_aton('1978-04-27')) and <cache>('2018-08-26')"
        }
      }
    }
  }
}
delete from t1;
insert into t1 values ('2018-01-15'),('2018-02-20');
select d1 from t1
group by d1
having d1 not between 0 AND exp(0);
d1
2018-01-15
2018-02-20
Warnings:
Warning	1292	Truncated incorrect datetime value: '1'
explain extended select d1 from t1
group by d1
having d1 not between 0 AND exp(0);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using temporary; Using filesort
Warnings:
Note	1003	select `test`.`t1`.`d1` AS `d1` from `test`.`t1` where `test`.`t1`.`d1` not between <cache>(0) and <cache>(exp(0)) group by `test`.`t1`.`d1` having 1
explain format=json select d1 from t1
group by d1
having d1 not between 0 AND exp(0);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "t1.d1",
      "temporary_table": {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows": 2,
          "filtered": 100,
          "attached_condition": "t1.d1 not between <cache>(0) and <cache>(exp(0))"
        }
      }
    }
  }
}
drop table t1;
#
# MDEV-19245: Impossible WHERE should be noticed earlier
#             after HAVING pushdown
#
CREATE TABLE t1 (a INT, b INT, c INT);
INSERT INTO t1 VALUES (1,2,1),(3,2,2),(5,6,4),(3,4,1);
EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
WHERE t1.a > 3 GROUP BY t1.a HAVING t1.a = 3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
WHERE t1.a = 3 GROUP BY t1.a HAVING t1.a > 3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
WHERE t1.a > 3 AND t1.a = 3 GROUP BY t1.a ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
WHERE (t1.a < 2 OR t1.c > 1) GROUP BY t1.a HAVING t1.a = 3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
WHERE t1.a = 3 GROUP BY t1.a HAVING (t1.a < 2 OR t1.a > 3);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
WHERE t1.a = 3 AND (t1.a < 2 OR t1.a > 3) GROUP BY t1.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1
WHERE (t1.a < 2 AND t1.c > 1) GROUP BY t1.a HAVING t1.a = 3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1
WHERE t1.a = 3 GROUP BY t1.a HAVING (t1.a < 2 AND t1.c > 1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1
WHERE t1.a = 3 AND (t1.a < 2 AND t1.b > 3) GROUP BY t1.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
DROP TABLE t1;
