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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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,
    "nested_loop": [
      {
        "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,
    "nested_loop": [
      {
        "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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",
    "nested_loop": [
      {
        "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",
    "nested_loop": [
      {
        "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",
    "nested_loop": [
      {
        "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",
    "nested_loop": [
      {
        "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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",
    "nested_loop": [
      {
        "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",
    "nested_loop": [
      {
        "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
                      "nested_loop": [
                        {
                          "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": {
        "nested_loop": [
          {
            "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": {
                      "nested_loop": [
                        {
                          "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": {
        "nested_loop": [
          {
            "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": {
                      "nested_loop": [
                        {
                          "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": {
        "nested_loop": [
          {
            "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": {
                      "nested_loop": [
                        {
                          "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": {
        "nested_loop": [
          {
            "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": {
                    "nested_loop": [
                      {
                        "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": {
        "nested_loop": [
          {
            "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": {
                    "nested_loop": [
                      {
                        "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": {
        "nested_loop": [
          {
            "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": {
                    "nested_loop": [
                      {
                        "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": {
        "nested_loop": [
          {
            "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": {
                    "nested_loop": [
                      {
                        "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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",
    "nested_loop": [
      {
        "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",
    "nested_loop": [
      {
        "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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,
    "nested_loop": [
      {
        "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,
    "nested_loop": [
      {
        "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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",
    "nested_loop": [
      {
        "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",
    "nested_loop": [
      {
        "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)",
    "nested_loop": [
      {
        "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",
    "nested_loop": [
      {
        "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)",
    "nested_loop": [
      {
        "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",
    "nested_loop": [
      {
        "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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,
    "nested_loop": [
      {
        "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,
    "nested_loop": [
      {
        "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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,
    "nested_loop": [
      {
        "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,
    "nested_loop": [
      {
        "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,
    "nested_loop": [
      {
        "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,
    "nested_loop": [
      {
        "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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,
    "nested_loop": [
      {
        "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,
    "nested_loop": [
      {
        "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",
    "nested_loop": [
      {
        "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,
    "nested_loop": [
      {
        "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,
    "nested_loop": [
      {
        "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,
    "nested_loop": [
      {
        "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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",
    "nested_loop": [
      {
        "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,
    "nested_loop": [
      {
        "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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",
    "nested_loop": [
      {
        "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,
    "nested_loop": [
      {
        "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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",
    "nested_loop": [
      {
        "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,
    "nested_loop": [
      {
        "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",
    "nested_loop": [
      {
        "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",
    "nested_loop": [
      {
        "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",
    "nested_loop": [
      {
        "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,
    "nested_loop": [
      {
        "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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,
    "nested_loop": [
      {
        "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,
    "nested_loop": [
      {
        "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,
    "nested_loop": [
      {
        "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,
    "nested_loop": [
      {
        "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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": {
        "nested_loop": [
          {
            "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;
#
# MDEV-21184: Constant subquery in condition movable to WHERE
#
CREATE TABLE t1(a int, b int);
INSERT INTO t1 VALUES
(1,10), (2,20), (1,11), (1,15), (2,20), (1,10), (2,21);
CREATE TABLE t2 (c INT);
INSERT INTO t2 VALUES (2),(3);
EXPLAIN FORMAT=JSON SELECT a FROM t1 GROUP BY a HAVING a = 8 OR a = ( SELECT MIN(c) FROM t2 );
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "t1.a",
      "temporary_table": {
        "nested_loop": [
          {
            "table": {
              "table_name": "t1",
              "access_type": "ALL",
              "rows": 7,
              "filtered": 100,
              "attached_condition": "t1.a = 8 or t1.a = (subquery#2)"
            }
          }
        ],
        "subqueries": [
          {
            "query_block": {
              "select_id": 2,
              "nested_loop": [
                {
                  "table": {
                    "table_name": "t2",
                    "access_type": "ALL",
                    "rows": 2,
                    "filtered": 100
                  }
                }
              ]
            }
          }
        ]
      }
    }
  }
}
SELECT a FROM t1 GROUP BY a HAVING a = 8 OR a = ( SELECT MIN(c) FROM t2 );
a
2
EXPLAIN FORMAT=JSON SELECT a FROM t1 GROUP BY a,b
HAVING ( a = 8 OR a = ( SELECT MIN(c) FROM t2 ) ) and b < 20;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "t1.a, t1.b",
      "temporary_table": {
        "nested_loop": [
          {
            "table": {
              "table_name": "t1",
              "access_type": "ALL",
              "rows": 7,
              "filtered": 100,
              "attached_condition": "(t1.a = 8 or t1.a = (subquery#2)) and t1.b < 20"
            }
          }
        ],
        "subqueries": [
          {
            "query_block": {
              "select_id": 2,
              "nested_loop": [
                {
                  "table": {
                    "table_name": "t2",
                    "access_type": "ALL",
                    "rows": 2,
                    "filtered": 100
                  }
                }
              ]
            }
          }
        ]
      }
    }
  }
}
SELECT a FROM t1 GROUP BY a,b
HAVING ( a = 8 OR a = ( SELECT MIN(c) FROM t2 ) ) and b < 20;
a
EXPLAIN FORMAT=JSON SELECT a FROM t1 GROUP BY a
HAVING ( a = 8 OR a = ( SELECT MIN(c) FROM t2 ) ) and SUM(b) > 20;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "having_condition": "sum(t1.b) > 20",
    "filesort": {
      "sort_key": "t1.a",
      "temporary_table": {
        "nested_loop": [
          {
            "table": {
              "table_name": "t1",
              "access_type": "ALL",
              "rows": 7,
              "filtered": 100,
              "attached_condition": "t1.a = 8 or t1.a = (subquery#2)"
            }
          }
        ],
        "subqueries": [
          {
            "query_block": {
              "select_id": 2,
              "nested_loop": [
                {
                  "table": {
                    "table_name": "t2",
                    "access_type": "ALL",
                    "rows": 2,
                    "filtered": 100
                  }
                }
              ]
            }
          }
        ]
      }
    }
  }
}
SELECT a FROM t1 GROUP BY a
HAVING ( a = 8 OR a = ( SELECT MIN(c) FROM t2 ) ) and SUM(b) > 20;
a
2
EXPLAIN FORMAT=JSON SELECT a FROM t1 GROUP BY a HAVING a = ( SELECT MIN(c) FROM t2 );
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows": 7,
          "filtered": 100,
          "attached_condition": "t1.a = (subquery#2)"
        }
      }
    ],
    "subqueries": [
      {
        "query_block": {
          "select_id": 2,
          "nested_loop": [
            {
              "table": {
                "table_name": "t2",
                "access_type": "ALL",
                "rows": 2,
                "filtered": 100
              }
            }
          ]
        }
      }
    ]
  }
}
SELECT a FROM t1 GROUP BY a HAVING a = ( SELECT MIN(c) FROM t2 );
a
2
DROP TABLE t1,t2;
#
# MDEV-26402: A SEGV in Item_field::used_tables/update_depend_map_for_order or Assertion `fixed == 1'
#
CREATE TABLE t1 (i int NOT NULL);
SELECT * FROM t1 GROUP BY i HAVING i IN ( i IS NULL);
i
SELECT * FROM t1 GROUP BY i HAVING i IN ( i IS NULL AND 'x' = 0);
i
SELECT * FROM t1 GROUP BY i HAVING i='1' IN ( i IS NULL AND 'x' = 0);
i
DROP TABLE t1;
#
# MDEV-28080: HAVING with NOT EXIST predicate in an equality
# (fixed by the patch for MDEV-26402)
#
CREATE TABLE t1 (a int);
CREATE TABLE t2 (b int);
INSERT INTO t1 VALUES (0), (1), (1), (0);
INSERT INTO t2 VALUES (3), (7);
SELECT a FROM t1
GROUP BY a HAVING a= (NOT EXISTS (SELECT b FROM t2 WHERE b = 1));
a
1
SELECT a FROM t1
GROUP BY a HAVING a= (NOT EXISTS (SELECT b FROM t2 WHERE b = 7));
a
0
DROP TABLE t1, t2;
#
# MDEV-28082: HAVING with IS NULL predicate in an equality
# (fixed by the patch for MDEV-26402)
#
CREATE TABLE t1 (a int, b int NOT NULL) ;
INSERT INTO t1 VALUES (1,10), (0,11), (0,11), (1,10);
SELECT a,b FROM t1 GROUP BY a HAVING a = (b IS NULL);
a	b
0	11
SELECT a,b FROM t1 GROUP BY a,b HAVING a = (b IS NULL);
a	b
0	11
DROP TABLE t1;
End of 10.4 tests