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 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": "", "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": "", "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 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": "", "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": "", "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 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": "", "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": "", "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 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": "", "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": "", "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 (inet_aton('1978-04-27')) and ('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 (inet_aton('1978-04-27')) and ('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 (0) and (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 (0) and (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