diff options
Diffstat (limited to 'mysql-test/main/having_cond_pushdown.result')
| -rw-r--r-- | mysql-test/main/having_cond_pushdown.result | 5568 |
1 files changed, 5568 insertions, 0 deletions
diff --git a/mysql-test/main/having_cond_pushdown.result b/mysql-test/main/having_cond_pushdown.result new file mode 100644 index 00000000..7ebf9945 --- /dev/null +++ b/mysql-test/main/having_cond_pushdown.result @@ -0,0 +1,5568 @@ +CREATE TABLE t1(a INT, b INT, c INT); +CREATE TABLE t2(x INT, y INT); +INSERT INTO t1 VALUES (1,14,3), (2,13,2), (1,22,1), (3,13,4), (3,14,2); +INSERT INTO t2 VALUES (2,13),(5,22),(3,14),(1,22); +CREATE VIEW v1 +AS SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a; +CREATE FUNCTION f1() RETURNS INT RETURN 3; +# conjunctive subformula +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>2); +a MAX(t1.b) +3 14 +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>2); +a MAX(t1.b) +3 14 +explain SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 2" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a>2) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 2" + } + } + ] + } + } + } +} +# conjunctive subformula : using equality +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2); +a MAX(t1.b) +2 13 +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2); +a MAX(t1.b) +2 13 +explain SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 2" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a=2) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 2" + } + } + ] + } +} +# extracted AND formula +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (t1.a<4); +a MAX(t1.b) +2 13 +3 14 +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (t1.a<4); +a MAX(t1.b) +2 13 +3 14 +explain SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (t1.a<4); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (t1.a<4); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a < 4" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a>1) AND (t1.a<4) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a < 4" + } + } + ] + } + } + } +} +# extracted OR formula +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) OR (a IN (SELECT 3)); +a MAX(t1.b) +2 13 +3 14 +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) OR (a IN (SELECT 3)); +a MAX(t1.b) +2 13 +3 14 +explain SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) OR (a IN (SELECT 3)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +Warnings: +Note 1249 Select 2 was reduced during optimization +explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) OR (a IN (SELECT 3)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 or t1.a = 3" + } + } + ] + } + } + } +} +Warnings: +Note 1249 Select 2 was reduced during optimization +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a>1) OR (a IN (SELECT 3)) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 or t1.a = 3" + } + } + ] + } + } + } +} +Warnings: +Note 1249 Select 3 was reduced during optimization +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING ((t1.a>2) AND (MAX(t1.b)>13)) OR ((t1.a<3) AND (MIN(t1.c)>1)); +a MAX(t1.b) MIN(t1.c) +2 13 2 +3 14 2 +SELECT t1.a,MAX(t1.b),MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING ((t1.a>2) AND (MAX(t1.b)>13)) OR ((t1.a<3) AND (MIN(t1.c)>1)); +a MAX(t1.b) MIN(t1.c) +2 13 2 +3 14 2 +explain SELECT t1.a,MAX(t1.b),MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING ((t1.a>2) AND (MAX(t1.b)>13)) OR ((t1.a<3) AND (MIN(t1.c)>1)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING ((t1.a>2) AND (MAX(t1.b)>13)) OR ((t1.a<3) AND (MIN(t1.c)>1)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a > 2 and max(t1.b) > 13 or t1.a < 3 and min(t1.c) > 1", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 2 or t1.a < 3" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a>2) OR (t1.a<3) +GROUP BY t1.a +HAVING ((t1.a>2) AND (MAX(t1.b)>13)) OR ((t1.a<3) AND (MIN(t1.c)>1)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a > 2 and max(t1.b) > 13 or t1.a < 3 and min(t1.c) > 1", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 2 or t1.a < 3" + } + } + ] + } + } + } +} +# conjunctive subformula : no aggregation formula pushdown +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(t1.a)<3); +a MAX(t1.b) +2 13 +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(t1.a)<3); +a MAX(t1.b) +2 13 +explain SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(t1.a)<3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(t1.a)<3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.a) < 3", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a>1) +GROUP BY t1.a +HAVING (MAX(t1.a)<3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.a) < 3", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(t1.b)>13); +a MAX(t1.b) +3 14 +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(t1.b)>13); +a MAX(t1.b) +3 14 +explain SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(t1.b)>13); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(t1.b)>13); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b) > 13", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a>1) +GROUP BY t1.a +HAVING (MAX(t1.b)>13); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b) > 13", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=3) AND (MAX(t1.a)=3); +a MAX(t1.b) +3 14 +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=3) AND (MAX(t1.a)=3); +a MAX(t1.b) +3 14 +explain SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=3) AND (MAX(t1.a)=3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=3) AND (MAX(t1.a)=3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.a) = 3", + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 3" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a=3) +GROUP BY t1.a +HAVING (MAX(t1.a)=3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.a) = 3", + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 3" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2) AND (MAX(t1.b)>12); +a MAX(t1.b) +2 13 +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2) AND (MAX(t1.b)>12); +a MAX(t1.b) +2 13 +explain SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2) AND (MAX(t1.b)>12); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2) AND (MAX(t1.b)>12); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b) > 12", + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 2" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a=2) +GROUP BY t1.a +HAVING (MAX(t1.b)>12); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b) > 12", + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 2" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(t1.b)=13); +a MAX(t1.b) +2 13 +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(t1.b)=13); +a MAX(t1.b) +2 13 +explain SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(t1.b)=13); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(t1.b)=13); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b) = 13", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a>1) +GROUP BY t1.a +HAVING (MAX(t1.b)=13); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b) = 13", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MIN(t1.c)<3); +a MIN(t1.c) +2 2 +3 2 +SELECT t1.a,MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MIN(t1.c)<3); +a MIN(t1.c) +2 2 +3 2 +explain SELECT t1.a,MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MIN(t1.c)<3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MIN(t1.c)<3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "min(t1.c) < 3", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MIN(t1.c) +FROM t1 +WHERE (t1.a>1) +GROUP BY t1.a +HAVING (MIN(t1.c)<3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "min(t1.c) < 3", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2) AND (MAX(t1.b)=13) AND (MIN(t1.c)=2); +a MAX(t1.b) MIN(t1.c) +2 13 2 +SELECT t1.a,MAX(t1.b),MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2) AND (MAX(t1.b)=13) AND (MIN(t1.c)=2); +a MAX(t1.b) MIN(t1.c) +2 13 2 +explain SELECT t1.a,MAX(t1.b),MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2) AND (MAX(t1.b)=13) AND (MIN(t1.c)=2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,MAX(t1.b),MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2) AND (MAX(t1.b)=13) AND (MIN(t1.c)=2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b) = 13 and min(t1.c) = 2", + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 2" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MIN(t1.c) +FROM t1 +WHERE (t1.a=2) +GROUP BY t1.a +HAVING (MAX(t1.b)=13) AND (MIN(t1.c)=2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b) = 13 and min(t1.c) = 2", + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 2" + } + } + ] + } +} +# conjunctive subformula : no stored function pushdown +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (a=test.f1()); +a MAX(t1.b) +3 14 +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (a=test.f1()); +a MAX(t1.b) +3 14 +explain SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (a=test.f1()); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (a=test.f1()); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a = test.f1()", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a>1) +GROUP BY t1.a +HAVING (a=test.f1()); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a = test.f1()", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + ] + } + } + } +} +# conjunctive subformula : pushdown into derived table WHERE clause +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT v1.a +FROM t2,v1 +WHERE (t2.x=v1.a) +GROUP BY v1.a +HAVING (v1.a>1); +a +2 +3 +SELECT v1.a +FROM t2,v1 +WHERE (t2.x=v1.a) +GROUP BY v1.a +HAVING (v1.a>1); +a +2 +3 +explain SELECT v1.a +FROM t2,v1 +WHERE (t2.x=v1.a) +GROUP BY v1.a +HAVING (v1.a>1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort +1 PRIMARY <derived2> ref key0 key0 5 test.t2.x 2 +2 DERIVED t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT v1.a +FROM t2,v1 +WHERE (t2.x=v1.a) +GROUP BY v1.a +HAVING (v1.a>1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "v1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 4, + "filtered": 100, + "attached_condition": "t2.x > 1 and t2.x is not null" + } + }, + { + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t2.x"], + "rows": 2, + "filtered": 100, + "materialized": { + "query_block": { + "select_id": 2, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + ] + } + } + } + } + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT v1.a +FROM t2,v1 +WHERE (t2.x=v1.a) AND (v1.a>1) +GROUP BY v1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "v1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 4, + "filtered": 100, + "attached_condition": "t2.x > 1 and t2.x is not null" + } + }, + { + "table": { + "table_name": "<derived3>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t2.x"], + "rows": 2, + "filtered": 100, + "materialized": { + "query_block": { + "select_id": 3, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + ] + } + } + } + } + } + } + ] + } + } + } +} +# conjunctive subformula : pushdown into derived table HAVING clause +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT v1.a,v1.c +FROM t2,v1 +WHERE (t2.x=v1.a) +GROUP BY v1.c +HAVING (v1.c>2); +a c +1 3 +3 4 +SELECT v1.a,v1.c +FROM t2,v1 +WHERE (t2.x=v1.a) +GROUP BY v1.c +HAVING (v1.c>2); +a c +1 3 +3 4 +explain SELECT v1.a,v1.c +FROM t2,v1 +WHERE (t2.x=v1.a) +GROUP BY v1.c +HAVING (v1.c>2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort +1 PRIMARY <derived2> ref key0 key0 5 test.t2.x 2 Using where +2 DERIVED t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort +explain format=json SELECT v1.a,v1.c +FROM t2,v1 +WHERE (t2.x=v1.a) +GROUP BY v1.c +HAVING (v1.c>2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "v1.c", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 4, + "filtered": 100, + "attached_condition": "t2.x is not null" + } + }, + { + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t2.x"], + "rows": 2, + "filtered": 100, + "attached_condition": "v1.c > 2", + "materialized": { + "query_block": { + "select_id": 2, + "having_condition": "t1.c > 2", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + } + } + ] + } + } + } + } + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT v1.a,v1.c +FROM t2,v1 +WHERE (t2.x=v1.a) AND (v1.c>2) +GROUP BY v1.c; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "v1.c", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 4, + "filtered": 100, + "attached_condition": "t2.x is not null" + } + }, + { + "table": { + "table_name": "<derived3>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t2.x"], + "rows": 2, + "filtered": 100, + "attached_condition": "v1.c > 2", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "t1.c > 2", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + } + } + ] + } + } + } + } + } + } + ] + } + } + } +} +# conjunctive subformula : pushdown into materialized IN subquery +# WHERE clause +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT * FROM t1 +WHERE +(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +GROUP BY t1.a +HAVING (t1.a>1); +a b c +2 13 2 +3 14 2 +SELECT * FROM t1 +WHERE +(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +GROUP BY t1.a +HAVING (t1.a>1); +a b c +2 13 2 +3 14 2 +explain SELECT * FROM t1 +WHERE +(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +GROUP BY t1.a +HAVING (t1.a>1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.b 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 Using where; Using temporary +explain format=json SELECT * FROM t1 +WHERE +(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +GROUP BY t1.a +HAVING (t1.a>1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a is not null and t1.b is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["x", "MAX(t2.y)"], + "ref": ["test.t1.a", "test.t1.b"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 4, + "filtered": 100, + "attached_condition": "t2.x < 5 and t2.x > 1" + } + } + ] + } + } + } + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT * FROM t1 +WHERE +(t1.a>1) AND +(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a is not null and t1.b is not null" + } + }, + { + "table": { + "table_name": "<subquery3>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["x", "MAX(t2.y)"], + "ref": ["test.t1.a", "test.t1.b"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 3, + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 4, + "filtered": 100, + "attached_condition": "t2.x < 5 and t2.x > 1" + } + } + ] + } + } + } + } + } + ] + } + } + } +} +# conjunctive subformula : pushdown into materialized IN subquery +# HAVING clause +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT * FROM t1 +WHERE +(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +GROUP BY t1.b +HAVING (t1.b<14); +a b c +2 13 2 +SELECT * FROM t1 +WHERE +(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +GROUP BY t1.b +HAVING (t1.b<14); +a b c +2 13 2 +explain SELECT * FROM t1 +WHERE +(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +GROUP BY t1.b +HAVING (t1.b<14); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.b 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 Using where; Using temporary +explain format=json SELECT * FROM t1 +WHERE +(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +GROUP BY t1.b +HAVING (t1.b<14); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.b", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b < 14 and t1.a is not null and t1.b is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["x", "MAX(t2.y)"], + "ref": ["test.t1.a", "test.t1.b"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`MAX(t2.y)` < 14", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 4, + "filtered": 100, + "attached_condition": "t2.x < 5" + } + } + ] + } + } + } + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT * FROM t1 +WHERE +(t1.b<14) AND +(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +GROUP BY t1.b; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.b", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b < 14 and t1.a is not null and t1.b is not null" + } + }, + { + "table": { + "table_name": "<subquery3>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["x", "MAX(t2.y)"], + "ref": ["test.t1.a", "test.t1.b"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 3, + "having_condition": "`MAX(t2.y)` < 14", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 4, + "filtered": 100, + "attached_condition": "t2.x < 5" + } + } + ] + } + } + } + } + } + ] + } + } + } +} +# non-standard allowed queries +# conjunctive subformula +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.c=2) AND (t1.a>1); +a MAX(t1.b) c +2 13 2 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.c=2) AND (t1.a>1); +a MAX(t1.b) c +2 13 2 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.c=2) AND (t1.a>1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.c=2) AND (t1.a>1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.c = 2", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c FROM t1 +WHERE (t1.a>1) +GROUP BY t1.a +HAVING (t1.c=2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.c = 2", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT MAX(t1.a),t1.a,t1.b,t1.c +FROM t1 +GROUP BY t1.b +HAVING (t1.a=2) AND (t1.b=13) AND (t1.c=2); +MAX(t1.a) a b c +3 2 13 2 +SELECT MAX(t1.a),t1.a,t1.b,t1.c +FROM t1 +GROUP BY t1.b +HAVING (t1.a=2) AND (t1.b=13) AND (t1.c=2); +MAX(t1.a) a b c +3 2 13 2 +explain SELECT MAX(t1.a),t1.a,t1.b,t1.c +FROM t1 +GROUP BY t1.b +HAVING (t1.a=2) AND (t1.b=13) AND (t1.c=2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT MAX(t1.a),t1.a,t1.b,t1.c +FROM t1 +GROUP BY t1.b +HAVING (t1.a=2) AND (t1.b=13) AND (t1.c=2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a = 2 and t1.c = 2", + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b = 13" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT MAX(t1.a),t1.a,t1.b,t1.c +FROM t1 +WHERE (t1.b=13) +GROUP BY t1.b +HAVING (t1.a=2) AND (t1.c=2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a = 2 and t1.c = 2", + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b = 13" + } + } + ] + } +} +# extracted AND formula : using equalities +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.a=t1.c) AND (t1.c>1); +a MAX(t1.b) c +2 13 2 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.a=t1.c) AND (t1.c>1); +a MAX(t1.b) c +2 13 2 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.a=t1.c) AND (t1.c>1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.a=t1.c) AND (t1.c>1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.c = t1.a and t1.a > 1" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b) FROM t1 +WHERE (t1.a=t1.c) AND (t1.a>1) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.c = t1.a and t1.a > 1" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.a=t1.c) AND (t1.c=2); +a MAX(t1.b) c +2 13 2 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.a=t1.c) AND (t1.c=2); +a MAX(t1.b) c +2 13 2 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.a=t1.c) AND (t1.c=2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.a=t1.c) AND (t1.c=2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 2 and t1.c = 2" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) AND (t1.a=2) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 2 and t1.c = 2" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING ((t1.a=t1.c) AND (t1.a>1)) OR ((t1.a<3) AND (t1.c>3)); +a MAX(t1.b) c +2 13 2 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING ((t1.a=t1.c) AND (t1.a>1)) OR ((t1.a<3) AND (t1.c>3)); +a MAX(t1.b) c +2 13 2 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING ((t1.a=t1.c) AND (t1.a>1)) OR ((t1.a<3) AND (t1.c>3)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING ((t1.a=t1.c) AND (t1.a>1)) OR ((t1.a<3) AND (t1.c>3)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.c = t1.a and t1.a > 1 or t1.a < 3 and t1.c > 3", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.c = t1.a and t1.a > 1 or t1.a < 3" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a=t1.c) AND (t1.a>1)) OR (t1.a<3) +GROUP BY t1.a +HAVING ((t1.a=t1.c) AND (t1.a>1)) OR ((t1.a<3) AND (t1.c>3)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.c = t1.a and t1.a > 1 or t1.a < 3 and t1.c > 3", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.c = t1.a and t1.a > 1 or t1.a < 3" + } + } + ] + } + } + } +} +# conjuctive subformula : pushdown using WHERE multiple equalities +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (t1.c<3); +a MAX(t1.b) c +1 22 1 +2 13 2 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (t1.c<3); +a MAX(t1.b) c +1 22 1 +2 13 2 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (t1.c<3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (t1.c<3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.c = t1.a and t1.a < 3" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) AND (t1.c<3) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.c = t1.a and t1.a < 3" + } + } + ] + } + } + } +} +# extracted AND-formula : pushdown using WHERE multiple equalities +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (t1.a>1) AND (t1.c<3); +a MAX(t1.b) c +2 13 2 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (t1.a>1) AND (t1.c<3); +a MAX(t1.b) c +2 13 2 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (t1.a>1) AND (t1.c<3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (t1.a>1) AND (t1.c<3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.c = t1.a and t1.a > 1 and t1.a < 3" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) AND (t1.a>1) AND (t1.c<3) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.c = t1.a and t1.a > 1 and t1.a < 3" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4)) AND (t1.a<2); +a MAX(t1.b) c +1 22 1 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4)) AND (t1.a<2); +a MAX(t1.b) c +1 22 1 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4)) AND (t1.a<2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4)) AND (t1.a<2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a > 1 and max(t1.c) < 3 or t1.c < 4", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.c = t1.a and (t1.a > 1 or t1.a < 4) and t1.a < 2" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) AND (((t1.a>1) OR (t1.c<4)) AND (t1.a<2)) +GROUP BY t1.a +HAVING ((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a > 1 and max(t1.c) < 3 or t1.c < 4", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.c = t1.a and (t1.a > 1 or t1.a < 4) and t1.a < 2" + } + } + ] + } + } + } +} +# extracted OR-formula : pushdown using WHERE multiple equalities +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING ((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4); +a MAX(t1.b) c +1 22 1 +2 13 2 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING ((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4); +a MAX(t1.b) c +1 22 1 +2 13 2 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING ((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING ((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a > 1 and max(t1.c) < 3 or t1.c < 4", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.c = t1.a and (t1.a > 1 or t1.a < 4)" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) AND ((t1.a>1) OR (t1.c<4)) +GROUP BY t1.a +HAVING ((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a > 1 and max(t1.c) < 3 or t1.c < 4", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.c = t1.a and (t1.a > 1 or t1.a < 4)" + } + } + ] + } + } + } +} +DROP TABLE t1,t2; +DROP VIEW v1; +DROP FUNCTION f1; +# +# MDEV-18668: pushdown from HAVING into impossible WHERE +# +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1),(2,2); +SELECT a FROM t1 WHERE b = 1 AND b = 2 GROUP BY a HAVING a <= 3; +a +EXPLAIN +SELECT a FROM t1 WHERE b = 1 AND b = 2 GROUP BY a HAVING a <= 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +DROP TABLE t1; +# +# MDEV-18769: unfixed OR condition pushed from HAVING into WHERE +# +CREATE TABLE t1(a INT, b INT, c INT); +CREATE TABLE t3(a INT, b INT, c INT, d INT); +INSERT INTO t1 VALUES (1,14,3), (2,13,2), (1,22,1), (3,13,4), (3,14,2); +INSERT INTO t3 VALUES (1,2,16,1), (1,3,11,2), (2,3,10,2); +# nothing to push +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING t1.b = 13 AND MAX(t1.c) > 2; +a b MAX(t1.c) +3 13 4 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING t1.b = 13 AND MAX(t1.c) > 2; +a b MAX(t1.c) +3 13 4 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING t1.b = 13 AND MAX(t1.c) > 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING t1.b = 13 AND MAX(t1.c) > 2; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.b = 13 and max(t1.c) > 2", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING t1.b = 13 AND MAX(t1.c) > 2; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.b = 13 and max(t1.c) > 2", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + } + } + ] + } + } + } +} +# extracted AND formula +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14); +a b MAX(t1.c) +2 13 2 +3 13 4 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14); +a b MAX(t1.c) +2 13 2 +3 13 4 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a = 1 or t1.b > 10) and t1.b < 14" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1 OR t1.b > 10) AND (t1.b < 14) +GROUP BY t1.a,t1.b; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a = 1 or t1.b > 10) and t1.b < 14" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15); +a b MAX(t1.c) +1 22 1 +2 13 2 +3 13 4 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15); +a b MAX(t1.c) +1 22 1 +2 13 2 +3 13 4 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a = 1 or t1.b > 10) and (t1.b < 14 or t1.b > 15)" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15) +GROUP BY t1.a,t1.b; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a = 1 or t1.b > 10) and (t1.b < 14 or t1.b > 15)" + } + } + ] + } + } + } +} +# extracted AND formula : equality in the inner AND formula +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2)); +a b MAX(t1.c) +2 13 2 +3 13 4 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2)); +a b MAX(t1.c) +2 13 2 +3 13 4 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a = 1 or t1.b > 10) and (t1.b < 14 or t1.a = 2 and t1.b > 15)" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2)) +GROUP BY t1.a,t1.b; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a = 1 or t1.b > 10) and (t1.b < 14 or t1.a = 2 and t1.b > 15)" + } + } + ] + } + } + } +} +# extracted OR formula +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2) OR (t1.b = 13 AND t1.a > 2); +a b MAX(t1.c) +1 14 3 +1 22 1 +3 13 4 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2) OR (t1.b = 13 AND t1.a > 2); +a b MAX(t1.c) +1 14 3 +1 22 1 +3 13 4 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2) OR (t1.b = 13 AND t1.a > 2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2) OR (t1.b = 13 AND t1.a > 2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a < 2 or t1.b = 13 and t1.a > 2" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a < 2) OR (t1.b = 13 AND t1.a > 2) +GROUP BY t1.a,t1.b; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a < 2 or t1.b = 13 and t1.a > 2" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13); +a b MAX(t1.c) +1 14 3 +3 13 4 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13); +a b MAX(t1.c) +1 14 3 +3 13 4 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b = 14 and t1.a < 2 or t1.b = 13 and t1.a > 2" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13) +GROUP BY t1.a,t1.b; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b = 14 and t1.a < 2 or t1.b = 13 and t1.a > 2" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14)); +a b MAX(t1.c) +1 14 3 +3 13 4 +3 14 2 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14)); +a b MAX(t1.c) +1 14 3 +3 13 4 +3 14 2 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b = 14 and t1.a < 2 or t1.a > 2 and (t1.b = 13 or t1.b = 14)" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14)) +GROUP BY t1.a,t1.b; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b = 14 and t1.a < 2 or t1.a > 2 and (t1.b = 13 or t1.b = 14)" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +a b MAX(t1.c) +1 14 3 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +a b MAX(t1.c) +1 14 3 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a < 2 and max(t1.c) = 2 or max(t1.c) > 2 and (t1.a = 1 or t1.a = 2)", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a < 2 or t1.a = 1 or t1.a = 2" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a < 2) OR (t1.a = 1 OR t1.a = 2) +GROUP BY t1.a +HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a < 2 and max(t1.c) = 2 or max(t1.c) > 2 and (t1.a = 1 or t1.a = 2)", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a < 2 or t1.a = 1 or t1.a = 2" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +a b MAX(t1.c) +1 14 3 +2 13 2 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +a b MAX(t1.c) +1 14 3 +2 13 2 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a = 2 and max(t1.c) = 2 or max(t1.c) > 2 and (t1.a = 1 or t1.a = 2)", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 2 or t1.a = 1 or t1.a = 2" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 2) OR (t1.a = 1 OR t1.a = 2) +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a = 2 and max(t1.c) = 2 or max(t1.c) > 2 and (t1.a = 1 or t1.a = 2)", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 2 or t1.a = 1 or t1.a = 2" + } + } + ] + } + } + } +} +# conjunctive subformula : equality pushdown +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 1) AND (MAX(t1.c) = 3); +a b MAX(t1.c) +1 14 3 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 1) AND (MAX(t1.c) = 3); +a b MAX(t1.c) +1 14 3 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 1) AND (MAX(t1.c) = 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 1) AND (MAX(t1.c) = 3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.c) = 3", + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (MAX(t1.c) = 3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.c) = 3", + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1" + } + } + ] + } +} +# conjunctive subformula : equalities pushdown +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 3) AND MAX(t1.b = 14); +a b MAX(t1.c) +1 14 3 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 3) AND MAX(t1.b = 14); +a b MAX(t1.c) +1 14 3 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 3) AND MAX(t1.b = 14); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 3) AND MAX(t1.b = 14); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b = 14)", + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 3" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 3) +GROUP BY t1.a,t1.c +HAVING (MAX(t1.b) = 14); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b) = 14", + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 3" + } + } + ] + } +} +# conjunctive subformula : multiple equality consists of +two equalities pushdown +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 1) AND MAX(t1.b = 14); +a b MAX(t1.c) +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 1) AND MAX(t1.b = 14); +a b MAX(t1.c) +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 1) AND MAX(t1.b = 14); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 1) AND MAX(t1.b = 14); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b = 14)", + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 1" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 1) +GROUP BY t1.a,t1.c +HAVING (MAX(t1.b) = 14); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "max(t1.b) = 14", + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 1" + } + } + ] + } +} +# +# Pushdown from HAVING into non-empty WHERE +# +# inequality : inequality in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a < 3); +a b MAX(t1.c) +1 14 3 +2 13 2 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a < 3); +a b MAX(t1.c) +1 14 3 +2 13 2 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a < 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a < 3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b > 2 and t1.a < 3" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) AND (t1.a < 3) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b > 2 and t1.a < 3" + } + } + ] + } + } + } +} +# equality : inequality in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a = 3); +a b MAX(t1.c) +3 13 4 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a = 3); +a b MAX(t1.c) +3 13 4 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a = 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a = 3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 3 and t1.b > 2" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) AND (t1.a = 3) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 3 and t1.b > 2" + } + } + ] + } +} +# inequality : equality in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a < 3); +a b MAX(t1.c) +1 14 3 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a < 3); +a b MAX(t1.c) +1 14 3 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a < 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a < 3); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b = 14 and t1.a < 3" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) AND (t1.a < 3) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b = 14 and t1.a < 3" + } + } + ] + } + } + } +} +# equality : equality in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a = 1); +a b MAX(t1.c) +1 14 3 +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a = 1); +a b MAX(t1.c) +1 14 3 +explain SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a = 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a = 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.b = 14" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) AND (t1.a = 1) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.b = 14 and t1.a = 1" + } + } + ] + } +} +# equality : equality in WHERE, impossible WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.c) +FROM t1 +WHERE (t1.a = 3) +GROUP BY t1.a +HAVING (t1.a = 1); +a MAX(t1.c) +SELECT t1.a,MAX(t1.c) +FROM t1 +WHERE (t1.a = 3) +GROUP BY t1.a +HAVING (t1.a = 1); +a MAX(t1.c) +explain SELECT t1.a,MAX(t1.c) +FROM t1 +WHERE (t1.a = 3) +GROUP BY t1.a +HAVING (t1.a = 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +explain format=json SELECT t1.a,MAX(t1.c) +FROM t1 +WHERE (t1.a = 3) +GROUP BY t1.a +HAVING (t1.a = 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "message": "Impossible WHERE" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.c) +FROM t1 +WHERE (t1.a = 3) AND (t1.a = 1) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "message": "Impossible WHERE" + } + } +} +# equality : equality in WHERE (equal through constant) +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c = 1) +GROUP BY t1.a +HAVING (t1.a = 1); +a MAX(t1.b) c +1 22 1 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c = 1) +GROUP BY t1.a +HAVING (t1.a = 1); +a MAX(t1.b) c +1 22 1 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c = 1) +GROUP BY t1.a +HAVING (t1.a = 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c = 1) +GROUP BY t1.a +HAVING (t1.a = 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 1" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c = 1) AND (t1.a = 1) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.c = 1 and t1.a = 1" + } + } + ] + } +} +# inequality : AND formula in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a > 1); +a MAX(t1.b) c +2 13 2 +3 14 2 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a > 1); +a MAX(t1.b) c +2 13 2 +3 14 2 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.c > 0 and t1.c < 3 and t1.a > 1" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) AND (t1.a > 1) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.c > 0 and t1.c < 3 and t1.a > 1" + } + } + ] + } + } + } +} +# equality : AND formula in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +a MAX(t1.b) c +1 22 1 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +a MAX(t1.b) c +1 22 1 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c > 0 and t1.c < 3" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) AND (t1.a = 1) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c > 0 and t1.c < 3" + } + } + ] + } +} +# equality : AND formula in WHERE, impossible WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +a MAX(t1.b) c +1 22 1 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +a MAX(t1.b) c +1 22 1 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "const_condition": "1", + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c < 3" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) AND (t1.a = 1) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c < 3" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a = 0) AND (t1.a = 3) +GROUP BY t1.a +HAVING (t1.a = 1); +a MAX(t1.b) +SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a = 0) AND (t1.a = 3) +GROUP BY t1.a +HAVING (t1.a = 1); +a MAX(t1.b) +explain SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a = 0) AND (t1.a = 3) +GROUP BY t1.a +HAVING (t1.a = 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a = 0) AND (t1.a = 3) +GROUP BY t1.a +HAVING (t1.a = 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "message": "Impossible WHERE" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a = 0) AND (t1.a = 3) AND (t1.a = 1) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "message": "Impossible WHERE" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t3.a,t3.b,MAX(t3.c),t3.d +FROM t3 +WHERE (t3.b = 2) AND (t3.d = 1) +GROUP BY t3.a,t3.b,t3.d +HAVING (t3.a = 1); +a b MAX(t3.c) d +1 2 16 1 +SELECT t3.a,t3.b,MAX(t3.c),t3.d +FROM t3 +WHERE (t3.b = 2) AND (t3.d = 1) +GROUP BY t3.a,t3.b,t3.d +HAVING (t3.a = 1); +a b MAX(t3.c) d +1 2 16 1 +explain SELECT t3.a,t3.b,MAX(t3.c),t3.d +FROM t3 +WHERE (t3.b = 2) AND (t3.d = 1) +GROUP BY t3.a,t3.b,t3.d +HAVING (t3.a = 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where +explain format=json SELECT t3.a,t3.b,MAX(t3.c),t3.d +FROM t3 +WHERE (t3.b = 2) AND (t3.d = 1) +GROUP BY t3.a,t3.b,t3.d +HAVING (t3.a = 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "t3.b = 2 and t3.d = 1 and t3.a = 1" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t3.a,t3.b,MAX(t3.c),t3.d +FROM t3 +WHERE (t3.b = 2) AND (t3.d = 1) AND (t3.a = 1) +GROUP BY t3.a,t3.b,t3.d; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "t3.b = 2 and t3.d = 1 and t3.a = 1" + } + } + ] + } +} +# inequality : OR formula in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 2); +a MAX(t1.b) c +1 22 1 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 2); +a MAX(t1.b) c +1 22 1 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a > 1 or t1.c < 3) and t1.a < 2" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) OR (t1.c < 3)) AND (t1.a < 2) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a > 1 or t1.c < 3) and t1.a < 2" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a = 1) OR (t1.a = 3) +GROUP BY t1.a +HAVING (t1.a = 2); +a MAX(t1.b) +SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a = 1) OR (t1.a = 3) +GROUP BY t1.a +HAVING (t1.a = 2); +a MAX(t1.b) +explain SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a = 1) OR (t1.a = 3) +GROUP BY t1.a +HAVING (t1.a = 2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a = 1) OR (t1.a = 3) +GROUP BY t1.a +HAVING (t1.a = 2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "message": "Impossible WHERE" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE ((t1.a = 1) OR (t1.a = 3)) AND (t1.a = 2) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "message": "Impossible WHERE" + } + } +} +# AND formula : inequality in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.a > 0); +a MAX(t1.b) c +2 13 2 +3 14 4 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.a > 0); +a MAX(t1.b) c +2 13 2 +3 14 4 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.a > 0); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.a > 0); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a < 4 and t1.a > 0" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.a < 4) AND (t1.a > 0) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a < 4 and t1.a > 0" + } + } + ] + } + } + } +} +# AND formula : equality in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.a > 0); +a MAX(t1.b) c +1 22 3 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.a > 0); +a MAX(t1.b) c +1 22 3 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.a > 0); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.a > 0); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "const_condition": "1 and 1", + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a < 4) AND (t1.a > 0) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1" + } + } + ] + } +} +# OR formula : inequality in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +a MAX(t1.b) c +2 13 2 +3 14 4 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +a MAX(t1.b) c +2 13 2 +3 14 4 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 and (t1.a < 4 or t1.a > 0)" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND ((t1.a < 4) OR (t1.a > 0)) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 and (t1.a < 4 or t1.a > 0)" + } + } + ] + } + } + } +} +# OR formula : equality in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +a MAX(t1.b) c +1 22 3 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +a MAX(t1.b) c +1 22 3 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "const_condition": "1", + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND ((t1.a < 4) OR (t1.a > 0)) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1" + } + } + ] + } +} +# AND formula : AND formula in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +a MAX(t1.b) c +2 13 2 +3 14 2 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +a MAX(t1.b) c +2 13 2 +3 14 2 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.c > 1", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.c < 3 and t1.a < 4" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) AND (t1.c < 3)) AND +(t1.a < 4) +GROUP BY t1.a +HAVING (t1.c > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.c > 1", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.c < 3 and t1.a < 4" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +a MAX(t1.b) c +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +a MAX(t1.b) c +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "const_condition": "1", + "filesort": { + "sort_key": "t1.c", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c < 3 and t1.c > 1" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a = 1) AND (t1.c < 3)) AND +((t1.a < 4) AND (t1.c > 1)) +GROUP BY t1.a,t1.c; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.c", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c < 3 and t1.c > 1" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +a MAX(t1.b) c +1 14 3 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +a MAX(t1.b) c +1 14 3 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "const_condition": "1 and 1", + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 3" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a = 1) AND (t1.c = 3)) AND +((t1.a < 4) AND (t1.c > 1)) +GROUP BY t1.a,t1.c; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 3" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t3.a,t3.b,MAX(t3.c),t3.d +FROM t3 +WHERE (t3.a = 1) AND (t3.d = 1) +GROUP BY t3.a,t3.b +HAVING (t3.b = 2) AND (t3.d > 0); +a b MAX(t3.c) d +1 2 16 1 +SELECT t3.a,t3.b,MAX(t3.c),t3.d +FROM t3 +WHERE (t3.a = 1) AND (t3.d = 1) +GROUP BY t3.a,t3.b +HAVING (t3.b = 2) AND (t3.d > 0); +a b MAX(t3.c) d +1 2 16 1 +explain SELECT t3.a,t3.b,MAX(t3.c),t3.d +FROM t3 +WHERE (t3.a = 1) AND (t3.d = 1) +GROUP BY t3.a,t3.b +HAVING (t3.b = 2) AND (t3.d > 0); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where +explain format=json SELECT t3.a,t3.b,MAX(t3.c),t3.d +FROM t3 +WHERE (t3.a = 1) AND (t3.d = 1) +GROUP BY t3.a,t3.b +HAVING (t3.b = 2) AND (t3.d > 0); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t3.d > 0", + "nested_loop": [ + { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "t3.a = 1 and t3.d = 1 and t3.b = 2" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t3.a,t3.b,MAX(t3.c),t3.d +FROM t3 +WHERE (t3.a = 1) AND (t3.d = 1) AND +(t3.b = 2) +GROUP BY t3.a,t3.b +HAVING (t3.d > 0); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t3.d > 0", + "nested_loop": [ + { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "t3.a = 1 and t3.d = 1 and t3.b = 2" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t3.a,t3.b,MAX(t3.c),t3.d +FROM t3 +WHERE (t3.a = 1) AND (t3.d = 1) +GROUP BY t3.a,t3.b,t3.d +HAVING (t3.b = 2) AND (t3.d > 0); +a b MAX(t3.c) d +1 2 16 1 +SELECT t3.a,t3.b,MAX(t3.c),t3.d +FROM t3 +WHERE (t3.a = 1) AND (t3.d = 1) +GROUP BY t3.a,t3.b,t3.d +HAVING (t3.b = 2) AND (t3.d > 0); +a b MAX(t3.c) d +1 2 16 1 +explain SELECT t3.a,t3.b,MAX(t3.c),t3.d +FROM t3 +WHERE (t3.a = 1) AND (t3.d = 1) +GROUP BY t3.a,t3.b,t3.d +HAVING (t3.b = 2) AND (t3.d > 0); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where +explain format=json SELECT t3.a,t3.b,MAX(t3.c),t3.d +FROM t3 +WHERE (t3.a = 1) AND (t3.d = 1) +GROUP BY t3.a,t3.b,t3.d +HAVING (t3.b = 2) AND (t3.d > 0); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "const_condition": "1", + "nested_loop": [ + { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "t3.a = 1 and t3.d = 1 and t3.b = 2" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t3.a,t3.b,MAX(t3.c),t3.d +FROM t3 +WHERE (t3.a = 1) AND (t3.d = 1) AND +(t3.b = 2) AND (t3.d > 0) +GROUP BY t3.a,t3.b,t3.d; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "t3.a = 1 and t3.d = 1 and t3.b = 2" + } + } + ] + } +} +# AND formula : OR formula in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +a MAX(t1.b) c +2 13 2 +3 14 4 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +a MAX(t1.b) c +2 13 2 +3 14 4 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.c > 1", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a > 1 or t1.c < 3) and t1.a < 4" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) OR (t1.c < 3)) AND +(t1.a < 4) +GROUP BY t1.a +HAVING (t1.c > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.c > 1", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a > 1 or t1.c < 3) and t1.a < 4" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +a MAX(t1.b) c +2 13 2 +3 14 2 +3 13 4 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +a MAX(t1.b) c +2 13 2 +3 14 2 +3 13 4 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.c", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a > 1 or t1.c < 3) and t1.a < 4 and t1.c > 1" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) OR (t1.c < 3)) AND +(t1.a < 4) AND (t1.c > 1) +GROUP BY t1.a,t1.c; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.c", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a > 1 or t1.c < 3) and t1.a < 4 and t1.c > 1" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) OR (t1.a = 3) +GROUP BY t1.a,t1.c +HAVING (t1.a = 4) OR (t1.c > 1); +a MAX(t1.b) c +1 14 3 +3 14 2 +3 13 4 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) OR (t1.a = 3) +GROUP BY t1.a,t1.c +HAVING (t1.a = 4) OR (t1.c > 1); +a MAX(t1.b) c +1 14 3 +3 14 2 +3 13 4 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) OR (t1.a = 3) +GROUP BY t1.a,t1.c +HAVING (t1.a = 4) OR (t1.c > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) OR (t1.a = 3) +GROUP BY t1.a,t1.c +HAVING (t1.a = 4) OR (t1.c > 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.c", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a = 1 or t1.a = 3) and (t1.a = 4 or t1.c > 1)" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a = 1) OR (t1.a = 3)) AND +((t1.a = 4) OR (t1.c > 1)) +GROUP BY t1.a,t1.c; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.c", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "(t1.a = 1 or t1.a = 3) and (t1.a = 4 or t1.c > 1)" + } + } + ] + } + } + } +} +# equality : pushdown through equality in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1); +a MAX(t1.b) c +1 22 1 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1); +a MAX(t1.b) c +1 22 1 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 1" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) AND (t1.c = 1) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 1" + } + } + ] + } +} +# OR formula : pushdown through equality +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1) OR (t1.c = 2); +a MAX(t1.b) c +1 22 1 +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1) OR (t1.c = 2); +a MAX(t1.b) c +1 22 1 +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1) OR (t1.c = 2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1) OR (t1.c = 2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 1" + } + } + ] + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) AND +((t1.c = 1) OR (t1.c = 2)) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.c = 1" + } + } + ] + } +} +# OR formula : pushdown through equality, impossible WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 3) OR (t1.c = 2); +a MAX(t1.b) c +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 3) OR (t1.c = 2); +a MAX(t1.b) c +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 3) OR (t1.c = 2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 3) OR (t1.c = 2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "message": "Impossible WHERE" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) AND +((t1.c = 3) OR (t1.c = 2)) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "message": "Impossible WHERE" + } + } +} +# AND formula : pushdown through equality, impossible WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a,t1.c +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +a MAX(t1.b) c +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a,t1.c +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +a MAX(t1.b) c +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a,t1.c +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a,t1.c +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "message": "Impossible WHERE" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 3) AND +(t1.a > 2) AND (t1.a = t1.c) +GROUP BY t1.a,t1.c; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "message": "Impossible WHERE" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +a MAX(t1.b) c +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +a MAX(t1.b) c +explain SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "message": "Impossible WHERE" + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 3) AND +(t1.a > 2) AND (t1.a = t1.c) +GROUP BY t1.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "message": "Impossible WHERE" + } + } +} +# AND formula with OR subformula : AND condition in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t3.a,MAX(t3.b),t3.c,t3.d +FROM t3 +WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2)) +GROUP BY t3.a +HAVING (t3.a = t3.d) AND ((t3.d = 1) OR (t3.d > 1)); +a MAX(t3.b) c d +SELECT t3.a,MAX(t3.b),t3.c,t3.d +FROM t3 +WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2)) +GROUP BY t3.a +HAVING (t3.a = t3.d) AND ((t3.d = 1) OR (t3.d > 1)); +a MAX(t3.b) c d +explain SELECT t3.a,MAX(t3.b),t3.c,t3.d +FROM t3 +WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2)) +GROUP BY t3.a +HAVING (t3.a = t3.d) AND ((t3.d = 1) OR (t3.d > 1)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using temporary; Using filesort +explain format=json SELECT t3.a,MAX(t3.b),t3.c,t3.d +FROM t3 +WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2)) +GROUP BY t3.a +HAVING (t3.a = t3.d) AND ((t3.d = 1) OR (t3.d > 1)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t3.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "t3.d = t3.a and t3.a > 1 and (t3.c = 3 or t3.c < 2) and (t3.a = 1 or t3.a > 1)" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t3.a,MAX(t3.c),t3.d +FROM t3 +WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2)) AND +(t3.a = t3.d) AND ((t3.d = 1) OR (t3.d > 1)) +GROUP BY t3.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t3.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "t3.d = t3.a and t3.a > 1 and (t3.c = 3 or t3.c < 2) and (t3.a = 1 or t3.a > 1)" + } + } + ] + } + } + } +} +# AND formula with OR subformula : AND condition in WHERE +set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t3.a,MAX(t3.b),t3.c,t3.d +FROM t3 +WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2)) +GROUP BY t3.a +HAVING (t3.a = t3.d) AND (((t3.d = t3.c) AND (t3.c < 15)) OR (t3.d > 1)); +a MAX(t3.b) c d +SELECT t3.a,MAX(t3.b),t3.c,t3.d +FROM t3 +WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2)) +GROUP BY t3.a +HAVING (t3.a = t3.d) AND (((t3.d = t3.c) AND (t3.c < 15)) OR (t3.d > 1)); +a MAX(t3.b) c d +explain SELECT t3.a,MAX(t3.b),t3.c,t3.d +FROM t3 +WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2)) +GROUP BY t3.a +HAVING (t3.a = t3.d) AND (((t3.d = t3.c) AND (t3.c < 15)) OR (t3.d > 1)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using temporary; Using filesort +explain format=json SELECT t3.a,MAX(t3.b),t3.c,t3.d +FROM t3 +WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2)) +GROUP BY t3.a +HAVING (t3.a = t3.d) AND (((t3.d = t3.c) AND (t3.c < 15)) OR (t3.d > 1)); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t3.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "t3.d = t3.a and t3.a > 1 and (t3.c = 3 or t3.c < 2) and (t3.c = t3.a and t3.c < 15 or t3.a > 1)" + } + } + ] + } + } + } +} +set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t3.a,t3.b,MAX(t3.c),t3.d +FROM t3 +WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2)) AND +(t3.a = t3.d) AND (((t3.d = t3.c) AND (t3.c < 15)) OR (t3.d > 1)) +GROUP BY t3.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t3.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "t3.d = t3.a and t3.a > 1 and (t3.c = 3 or t3.c < 2) and (t3.c = t3.a and t3.a < 15 or t3.a > 1)" + } + } + ] + } + } + } +} +# prepare statement +PREPARE stmt1 from " +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 1) +"; +execute stmt1; +a MAX(t1.b) c +1 22 3 +execute stmt1; +a MAX(t1.b) c +1 22 3 +deallocate prepare stmt1; +DROP TABLE t1,t3; +# +# MDEV-19185: pushdown constant function with subquery +# +CREATE TABLE t1 (pk INT, c1 VARCHAR(64)); +INSERT INTO t1 VALUES (1,'bbb'),(2,'aaa'),(3,'ccc'); +CREATE VIEW v1 AS SELECT * FROM t1; +SELECT pk +FROM t1 +GROUP BY pk +HAVING (1 NOT IN (SELECT COUNT(t1.c1) FROM (v1, t1))); +pk +1 +2 +3 +DROP TABLE t1; +DROP VIEW v1; +# +# MDEV-19186: temporary table defined with view field in HAVING +# +CREATE TABLE t1 (pk INT, x VARCHAR(10)); +INSERT INTO t1 VALUES (1,'y'),(2,'s'),(3,'aaa'); +CREATE VIEW v1 AS SELECT * FROM t1; +CREATE TABLE t2 (pk INT, x VARCHAR(10)); +INSERT INTO t2 VALUES (1,'aa'),(2,'t'),(3,'bb'); +CREATE TABLE tmp1 +SELECT v1.pk +FROM t2,v1 +WHERE v1.x = t2.x +GROUP BY v1.pk +HAVING (v1.pk = 1); +DROP TABLE t1,t2,tmp1; +DROP VIEW v1; +# +# MDEV-19164: pushdown of condition with cached items +# +create table t1 (d1 date); +insert into t1 values (null),('1971-03-06'),('1993-06-05'),('1998-07-08'); +select d1 from t1 +group by d1 +having d1 between (inet_aton('1978-04-27')) and '2018-08-26'; +d1 +explain extended select d1 from t1 +group by d1 +having d1 between (inet_aton('1978-04-27')) and '2018-08-26'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 Using where; Using temporary; Using filesort +Warnings: +Note 1003 select `test`.`t1`.`d1` AS `d1` from `test`.`t1` where `test`.`t1`.`d1` between <cache>(inet_aton('1978-04-27')) and <cache>('2018-08-26') group by `test`.`t1`.`d1` having 1 +explain format=json select d1 from t1 +group by d1 +having d1 between (inet_aton('1978-04-27')) and '2018-08-26'; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.d1", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 4, + "filtered": 100, + "attached_condition": "t1.d1 between <cache>(inet_aton('1978-04-27')) and <cache>('2018-08-26')" + } + } + ] + } + } + } +} +delete from t1; +insert into t1 values ('2018-01-15'),('2018-02-20'); +select d1 from t1 +group by d1 +having d1 not between 0 AND exp(0); +d1 +2018-01-15 +2018-02-20 +Warnings: +Warning 1292 Truncated incorrect datetime value: '1' +explain extended select d1 from t1 +group by d1 +having d1 not between 0 AND exp(0); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort +Warnings: +Note 1003 select `test`.`t1`.`d1` AS `d1` from `test`.`t1` where `test`.`t1`.`d1` not between <cache>(0) and <cache>(exp(0)) group by `test`.`t1`.`d1` having 1 +explain format=json select d1 from t1 +group by d1 +having d1 not between 0 AND exp(0); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.d1", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "t1.d1 not between <cache>(0) and <cache>(exp(0))" + } + } + ] + } + } + } +} +drop table t1; +# +# MDEV-19245: Impossible WHERE should be noticed earlier +# after HAVING pushdown +# +CREATE TABLE t1 (a INT, b INT, c INT); +INSERT INTO t1 VALUES (1,2,1),(3,2,2),(5,6,4),(3,4,1); +EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 +WHERE t1.a > 3 GROUP BY t1.a HAVING t1.a = 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 +WHERE t1.a = 3 GROUP BY t1.a HAVING t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 +WHERE t1.a > 3 AND t1.a = 3 GROUP BY t1.a ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 +WHERE (t1.a < 2 OR t1.c > 1) GROUP BY t1.a HAVING t1.a = 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where +EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 +WHERE t1.a = 3 GROUP BY t1.a HAVING (t1.a < 2 OR t1.a > 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 +WHERE t1.a = 3 AND (t1.a < 2 OR t1.a > 3) GROUP BY t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1 +WHERE (t1.a < 2 AND t1.c > 1) GROUP BY t1.a HAVING t1.a = 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1 +WHERE t1.a = 3 GROUP BY t1.a HAVING (t1.a < 2 AND t1.c > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1 +WHERE t1.a = 3 AND (t1.a < 2 AND t1.b > 3) GROUP BY t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +DROP TABLE t1; +# +# MDEV-21184: Constant subquery in condition movable to WHERE +# +CREATE TABLE t1(a int, b int); +INSERT INTO t1 VALUES +(1,10), (2,20), (1,11), (1,15), (2,20), (1,10), (2,21); +CREATE TABLE t2 (c INT); +INSERT INTO t2 VALUES (2),(3); +EXPLAIN FORMAT=JSON SELECT a FROM t1 GROUP BY a HAVING a = 8 OR a = ( SELECT MIN(c) FROM t2 ); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 7, + "filtered": 100, + "attached_condition": "t1.a = 8 or t1.a = (subquery#2)" + } + } + ], + "subqueries": [ + { + "query_block": { + "select_id": 2, + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 2, + "filtered": 100 + } + } + ] + } + } + ] + } + } + } +} +SELECT a FROM t1 GROUP BY a HAVING a = 8 OR a = ( SELECT MIN(c) FROM t2 ); +a +2 +EXPLAIN FORMAT=JSON SELECT a FROM t1 GROUP BY a,b +HAVING ( a = 8 OR a = ( SELECT MIN(c) FROM t2 ) ) and b < 20; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.a, t1.b", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 7, + "filtered": 100, + "attached_condition": "(t1.a = 8 or t1.a = (subquery#2)) and t1.b < 20" + } + } + ], + "subqueries": [ + { + "query_block": { + "select_id": 2, + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 2, + "filtered": 100 + } + } + ] + } + } + ] + } + } + } +} +SELECT a FROM t1 GROUP BY a,b +HAVING ( a = 8 OR a = ( SELECT MIN(c) FROM t2 ) ) and b < 20; +a +EXPLAIN FORMAT=JSON SELECT a FROM t1 GROUP BY a +HAVING ( a = 8 OR a = ( SELECT MIN(c) FROM t2 ) ) and SUM(b) > 20; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "sum(t1.b) > 20", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 7, + "filtered": 100, + "attached_condition": "t1.a = 8 or t1.a = (subquery#2)" + } + } + ], + "subqueries": [ + { + "query_block": { + "select_id": 2, + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 2, + "filtered": 100 + } + } + ] + } + } + ] + } + } + } +} +SELECT a FROM t1 GROUP BY a +HAVING ( a = 8 OR a = ( SELECT MIN(c) FROM t2 ) ) and SUM(b) > 20; +a +2 +EXPLAIN FORMAT=JSON SELECT a FROM t1 GROUP BY a HAVING a = ( SELECT MIN(c) FROM t2 ); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 7, + "filtered": 100, + "attached_condition": "t1.a = (subquery#2)" + } + } + ], + "subqueries": [ + { + "query_block": { + "select_id": 2, + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 2, + "filtered": 100 + } + } + ] + } + } + ] + } +} +SELECT a FROM t1 GROUP BY a HAVING a = ( SELECT MIN(c) FROM t2 ); +a +2 +DROP TABLE t1,t2; +# +# MDEV-26402: A SEGV in Item_field::used_tables/update_depend_map_for_order or Assertion `fixed == 1' +# +CREATE TABLE t1 (i int NOT NULL); +SELECT * FROM t1 GROUP BY i HAVING i IN ( i IS NULL); +i +SELECT * FROM t1 GROUP BY i HAVING i IN ( i IS NULL AND 'x' = 0); +i +SELECT * FROM t1 GROUP BY i HAVING i='1' IN ( i IS NULL AND 'x' = 0); +i +DROP TABLE t1; +# +# MDEV-28080: HAVING with NOT EXIST predicate in an equality +# (fixed by the patch for MDEV-26402) +# +CREATE TABLE t1 (a int); +CREATE TABLE t2 (b int); +INSERT INTO t1 VALUES (0), (1), (1), (0); +INSERT INTO t2 VALUES (3), (7); +SELECT a FROM t1 +GROUP BY a HAVING a= (NOT EXISTS (SELECT b FROM t2 WHERE b = 1)); +a +1 +SELECT a FROM t1 +GROUP BY a HAVING a= (NOT EXISTS (SELECT b FROM t2 WHERE b = 7)); +a +0 +DROP TABLE t1, t2; +# +# MDEV-28082: HAVING with IS NULL predicate in an equality +# (fixed by the patch for MDEV-26402) +# +CREATE TABLE t1 (a int, b int NOT NULL) ; +INSERT INTO t1 VALUES (1,10), (0,11), (0,11), (1,10); +SELECT a,b FROM t1 GROUP BY a HAVING a = (b IS NULL); +a b +0 11 +SELECT a,b FROM t1 GROUP BY a,b HAVING a = (b IS NULL); +a b +0 11 +DROP TABLE t1; +End of 10.4 tests |