mirror of
https://github.com/MariaDB/server.git
synced 2026-04-06 08:26:32 +02:00
a8471426f4
Problem: When building a pushable condition that can be pushed from HAVING into WHERE, the server tries to clone aggregate functions. This is not necessary because aggregate functions can not be pushed into WHERE anyway. Fix: This fix introduces a check within `Item::build_pushable_cond` to skip cloning aggregate functions. Also fixes assert failure in MDEV-38492, by adding a missing copy method for `Item_aggregate_ref`.
5299 lines
130 KiB
Text
5299 lines
130 KiB
Text
CREATE TABLE t1(a INT, b INT, c INT);
|
|
CREATE TABLE t2(x INT, y INT);
|
|
INSERT INTO t1 VALUES (1,14,3), (2,13,2), (1,22,1), (3,13,4), (3,14,2);
|
|
INSERT INTO t2 VALUES (2,13),(5,22),(3,14),(1,22);
|
|
CREATE VIEW v1
|
|
AS SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
GROUP BY t1.a;
|
|
CREATE FUNCTION f1() RETURNS INT RETURN 3;
|
|
# conjunctive subformula
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>2);
|
|
a MAX(t1.b)
|
|
3 14
|
|
SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>2);
|
|
a MAX(t1.b)
|
|
3 14
|
|
explain SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>2);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>2);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 2"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
WHERE (t1.a>2)
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 2"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
# conjunctive subformula : using equality
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a=2);
|
|
a MAX(t1.b)
|
|
2 13
|
|
SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a=2);
|
|
a MAX(t1.b)
|
|
2 13
|
|
explain SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a=2);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
|
|
explain format=json SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a=2);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 2"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
WHERE (t1.a=2)
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 2"
|
|
}
|
|
}
|
|
}
|
|
# extracted AND formula
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (t1.a<4);
|
|
a MAX(t1.b)
|
|
2 13
|
|
3 14
|
|
SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (t1.a<4);
|
|
a MAX(t1.b)
|
|
2 13
|
|
3 14
|
|
explain SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (t1.a<4);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (t1.a<4);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 1 and t1.a < 4"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
WHERE (t1.a>1) AND (t1.a<4)
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 1 and t1.a < 4"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
# extracted OR formula
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) OR (a IN (SELECT 3));
|
|
a MAX(t1.b)
|
|
2 13
|
|
3 14
|
|
SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) OR (a IN (SELECT 3));
|
|
a MAX(t1.b)
|
|
2 13
|
|
3 14
|
|
explain SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) OR (a IN (SELECT 3));
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
Warnings:
|
|
Note 1249 Select 2 was reduced during optimization
|
|
explain format=json SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) OR (a IN (SELECT 3));
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 1 or t1.a = 3"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1249 Select 2 was reduced during optimization
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
WHERE (t1.a>1) OR (a IN (SELECT 3))
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 1 or t1.a = 3"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1249 Select 3 was reduced during optimization
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),MIN(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING ((t1.a>2) AND (MAX(t1.b)>13)) OR ((t1.a<3) AND (MIN(t1.c)>1));
|
|
a MAX(t1.b) MIN(t1.c)
|
|
2 13 2
|
|
3 14 2
|
|
SELECT t1.a,MAX(t1.b),MIN(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING ((t1.a>2) AND (MAX(t1.b)>13)) OR ((t1.a<3) AND (MIN(t1.c)>1));
|
|
a MAX(t1.b) MIN(t1.c)
|
|
2 13 2
|
|
3 14 2
|
|
explain SELECT t1.a,MAX(t1.b),MIN(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING ((t1.a>2) AND (MAX(t1.b)>13)) OR ((t1.a<3) AND (MIN(t1.c)>1));
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,MAX(t1.b),MIN(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING ((t1.a>2) AND (MAX(t1.b)>13)) OR ((t1.a<3) AND (MIN(t1.c)>1));
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t1.a > 2 and max(t1.b) > 13 or t1.a < 3 and min(t1.c) > 1",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 2 or t1.a < 3"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a>2) OR (t1.a<3)
|
|
GROUP BY t1.a
|
|
HAVING ((t1.a>2) AND (MAX(t1.b)>13)) OR ((t1.a<3) AND (MIN(t1.c)>1));
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t1.a > 2 and max(t1.b) > 13 or t1.a < 3 and min(t1.c) > 1",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 2 or t1.a < 3"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
# conjunctive subformula : no aggregation formula pushdown
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (MAX(t1.a)<3);
|
|
a MAX(t1.b)
|
|
2 13
|
|
SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (MAX(t1.a)<3);
|
|
a MAX(t1.b)
|
|
2 13
|
|
explain SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (MAX(t1.a)<3);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (MAX(t1.a)<3);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "max(t1.a) < 3",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 1"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
WHERE (t1.a>1)
|
|
GROUP BY t1.a
|
|
HAVING (MAX(t1.a)<3);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "max(t1.a) < 3",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 1"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (MAX(t1.b)>13);
|
|
a MAX(t1.b)
|
|
3 14
|
|
SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (MAX(t1.b)>13);
|
|
a MAX(t1.b)
|
|
3 14
|
|
explain SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (MAX(t1.b)>13);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (MAX(t1.b)>13);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "max(t1.b) > 13",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 1"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
WHERE (t1.a>1)
|
|
GROUP BY t1.a
|
|
HAVING (MAX(t1.b)>13);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "max(t1.b) > 13",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 1"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a=3) AND (MAX(t1.a)=3);
|
|
a MAX(t1.b)
|
|
3 14
|
|
SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a=3) AND (MAX(t1.a)=3);
|
|
a MAX(t1.b)
|
|
3 14
|
|
explain SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a=3) AND (MAX(t1.a)=3);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
|
|
explain format=json SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a=3) AND (MAX(t1.a)=3);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "max(t1.a) = 3",
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 3"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
WHERE (t1.a=3)
|
|
GROUP BY t1.a
|
|
HAVING (MAX(t1.a)=3);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "max(t1.a) = 3",
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 3"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a=2) AND (MAX(t1.b)>12);
|
|
a MAX(t1.b)
|
|
2 13
|
|
SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a=2) AND (MAX(t1.b)>12);
|
|
a MAX(t1.b)
|
|
2 13
|
|
explain SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a=2) AND (MAX(t1.b)>12);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
|
|
explain format=json SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a=2) AND (MAX(t1.b)>12);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "max(t1.b) > 12",
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 2"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
WHERE (t1.a=2)
|
|
GROUP BY t1.a
|
|
HAVING (MAX(t1.b)>12);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "max(t1.b) > 12",
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 2"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (MAX(t1.b)=13);
|
|
a MAX(t1.b)
|
|
2 13
|
|
SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (MAX(t1.b)=13);
|
|
a MAX(t1.b)
|
|
2 13
|
|
explain SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (MAX(t1.b)=13);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (MAX(t1.b)=13);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "max(t1.b) = 13",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 1"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
WHERE (t1.a>1)
|
|
GROUP BY t1.a
|
|
HAVING (MAX(t1.b)=13);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "max(t1.b) = 13",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 1"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MIN(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (MIN(t1.c)<3);
|
|
a MIN(t1.c)
|
|
2 2
|
|
3 2
|
|
SELECT t1.a,MIN(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (MIN(t1.c)<3);
|
|
a MIN(t1.c)
|
|
2 2
|
|
3 2
|
|
explain SELECT t1.a,MIN(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (MIN(t1.c)<3);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,MIN(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (MIN(t1.c)<3);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "min(t1.c) < 3",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 1"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MIN(t1.c)
|
|
FROM t1
|
|
WHERE (t1.a>1)
|
|
GROUP BY t1.a
|
|
HAVING (MIN(t1.c)<3);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "min(t1.c) < 3",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 1"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),MIN(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a=2) AND (MAX(t1.b)=13) AND (MIN(t1.c)=2);
|
|
a MAX(t1.b) MIN(t1.c)
|
|
2 13 2
|
|
SELECT t1.a,MAX(t1.b),MIN(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a=2) AND (MAX(t1.b)=13) AND (MIN(t1.c)=2);
|
|
a MAX(t1.b) MIN(t1.c)
|
|
2 13 2
|
|
explain SELECT t1.a,MAX(t1.b),MIN(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a=2) AND (MAX(t1.b)=13) AND (MIN(t1.c)=2);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
|
|
explain format=json SELECT t1.a,MAX(t1.b),MIN(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a=2) AND (MAX(t1.b)=13) AND (MIN(t1.c)=2);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "max(t1.b) = 13 and min(t1.c) = 2",
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 2"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MIN(t1.c)
|
|
FROM t1
|
|
WHERE (t1.a=2)
|
|
GROUP BY t1.a
|
|
HAVING (MAX(t1.b)=13) AND (MIN(t1.c)=2);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "max(t1.b) = 13 and min(t1.c) = 2",
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 2"
|
|
}
|
|
}
|
|
}
|
|
# conjunctive subformula : no stored function pushdown
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (a=test.f1());
|
|
a MAX(t1.b)
|
|
3 14
|
|
SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (a=test.f1());
|
|
a MAX(t1.b)
|
|
3 14
|
|
explain SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (a=test.f1());
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (a=test.f1());
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t1.a = test.f1()",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 1"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
WHERE (t1.a>1)
|
|
GROUP BY t1.a
|
|
HAVING (a=test.f1());
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t1.a = test.f1()",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 1"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
# conjunctive subformula : pushdown into derived table WHERE clause
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT v1.a
|
|
FROM t2,v1
|
|
WHERE (t2.x=v1.a)
|
|
GROUP BY v1.a
|
|
HAVING (v1.a>1);
|
|
a
|
|
2
|
|
3
|
|
SELECT v1.a
|
|
FROM t2,v1
|
|
WHERE (t2.x=v1.a)
|
|
GROUP BY v1.a
|
|
HAVING (v1.a>1);
|
|
a
|
|
2
|
|
3
|
|
explain SELECT v1.a
|
|
FROM t2,v1
|
|
WHERE (t2.x=v1.a)
|
|
GROUP BY v1.a
|
|
HAVING (v1.a>1);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort
|
|
1 PRIMARY <derived2> ref key0 key0 5 test.t2.x 2
|
|
2 DERIVED t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT v1.a
|
|
FROM t2,v1
|
|
WHERE (t2.x=v1.a)
|
|
GROUP BY v1.a
|
|
HAVING (v1.a>1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "v1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"rows": 4,
|
|
"filtered": 100,
|
|
"attached_condition": "t2.x > 1 and t2.x is not null"
|
|
},
|
|
"table": {
|
|
"table_name": "<derived2>",
|
|
"access_type": "ref",
|
|
"possible_keys": ["key0"],
|
|
"key": "key0",
|
|
"key_length": "5",
|
|
"used_key_parts": ["a"],
|
|
"ref": ["test.t2.x"],
|
|
"rows": 2,
|
|
"filtered": 100,
|
|
"materialized": {
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 1"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT v1.a
|
|
FROM t2,v1
|
|
WHERE (t2.x=v1.a) AND (v1.a>1)
|
|
GROUP BY v1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "v1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"rows": 4,
|
|
"filtered": 100,
|
|
"attached_condition": "t2.x > 1 and t2.x is not null"
|
|
},
|
|
"table": {
|
|
"table_name": "<derived3>",
|
|
"access_type": "ref",
|
|
"possible_keys": ["key0"],
|
|
"key": "key0",
|
|
"key_length": "5",
|
|
"used_key_parts": ["a"],
|
|
"ref": ["test.t2.x"],
|
|
"rows": 2,
|
|
"filtered": 100,
|
|
"materialized": {
|
|
"query_block": {
|
|
"select_id": 3,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 1"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
# conjunctive subformula : pushdown into derived table HAVING clause
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT v1.a,v1.c
|
|
FROM t2,v1
|
|
WHERE (t2.x=v1.a)
|
|
GROUP BY v1.c
|
|
HAVING (v1.c>2);
|
|
a c
|
|
1 3
|
|
3 4
|
|
SELECT v1.a,v1.c
|
|
FROM t2,v1
|
|
WHERE (t2.x=v1.a)
|
|
GROUP BY v1.c
|
|
HAVING (v1.c>2);
|
|
a c
|
|
1 3
|
|
3 4
|
|
explain SELECT v1.a,v1.c
|
|
FROM t2,v1
|
|
WHERE (t2.x=v1.a)
|
|
GROUP BY v1.c
|
|
HAVING (v1.c>2);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; Using temporary; Using filesort
|
|
1 PRIMARY <derived2> ref key0 key0 5 test.t2.x 2 Using where
|
|
2 DERIVED t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort
|
|
explain format=json SELECT v1.a,v1.c
|
|
FROM t2,v1
|
|
WHERE (t2.x=v1.a)
|
|
GROUP BY v1.c
|
|
HAVING (v1.c>2);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "v1.c",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"rows": 4,
|
|
"filtered": 100,
|
|
"attached_condition": "t2.x is not null"
|
|
},
|
|
"table": {
|
|
"table_name": "<derived2>",
|
|
"access_type": "ref",
|
|
"possible_keys": ["key0"],
|
|
"key": "key0",
|
|
"key_length": "5",
|
|
"used_key_parts": ["a"],
|
|
"ref": ["test.t2.x"],
|
|
"rows": 2,
|
|
"filtered": 100,
|
|
"attached_condition": "v1.c > 2",
|
|
"materialized": {
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"having_condition": "t1.c > 2",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT v1.a,v1.c
|
|
FROM t2,v1
|
|
WHERE (t2.x=v1.a) AND (v1.c>2)
|
|
GROUP BY v1.c;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "v1.c",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"rows": 4,
|
|
"filtered": 100,
|
|
"attached_condition": "t2.x is not null"
|
|
},
|
|
"table": {
|
|
"table_name": "<derived3>",
|
|
"access_type": "ref",
|
|
"possible_keys": ["key0"],
|
|
"key": "key0",
|
|
"key_length": "5",
|
|
"used_key_parts": ["a"],
|
|
"ref": ["test.t2.x"],
|
|
"rows": 2,
|
|
"filtered": 100,
|
|
"attached_condition": "v1.c > 2",
|
|
"materialized": {
|
|
"query_block": {
|
|
"select_id": 3,
|
|
"having_condition": "t1.c > 2",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
# conjunctive subformula : pushdown into materialized IN subquery
|
|
# WHERE clause
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT * FROM t1
|
|
WHERE
|
|
(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1);
|
|
a b c
|
|
2 13 2
|
|
3 14 2
|
|
SELECT * FROM t1
|
|
WHERE
|
|
(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1);
|
|
a b c
|
|
2 13 2
|
|
3 14 2
|
|
explain SELECT * FROM t1
|
|
WHERE
|
|
(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.b 1
|
|
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 Using where; Using temporary
|
|
explain format=json SELECT * FROM t1
|
|
WHERE
|
|
(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 1 and t1.a is not null and t1.b is not null"
|
|
},
|
|
"table": {
|
|
"table_name": "<subquery2>",
|
|
"access_type": "eq_ref",
|
|
"possible_keys": ["distinct_key"],
|
|
"key": "distinct_key",
|
|
"key_length": "8",
|
|
"used_key_parts": ["x", "MAX(t2.y)"],
|
|
"ref": ["test.t1.a", "test.t1.b"],
|
|
"rows": 1,
|
|
"filtered": 100,
|
|
"materialized": {
|
|
"unique": 1,
|
|
"materialization": {
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"rows": 4,
|
|
"filtered": 100,
|
|
"attached_condition": "t2.x < 5 and t2.x > 1"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT * FROM t1
|
|
WHERE
|
|
(t1.a>1) AND
|
|
(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x)
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 1 and t1.a is not null and t1.b is not null"
|
|
},
|
|
"table": {
|
|
"table_name": "<subquery3>",
|
|
"access_type": "eq_ref",
|
|
"possible_keys": ["distinct_key"],
|
|
"key": "distinct_key",
|
|
"key_length": "8",
|
|
"used_key_parts": ["x", "MAX(t2.y)"],
|
|
"ref": ["test.t1.a", "test.t1.b"],
|
|
"rows": 1,
|
|
"filtered": 100,
|
|
"materialized": {
|
|
"unique": 1,
|
|
"materialization": {
|
|
"query_block": {
|
|
"select_id": 3,
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"rows": 4,
|
|
"filtered": 100,
|
|
"attached_condition": "t2.x < 5 and t2.x > 1"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
# conjunctive subformula : pushdown into materialized IN subquery
|
|
# HAVING clause
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT * FROM t1
|
|
WHERE
|
|
(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x)
|
|
GROUP BY t1.b
|
|
HAVING (t1.b<14);
|
|
a b c
|
|
2 13 2
|
|
SELECT * FROM t1
|
|
WHERE
|
|
(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x)
|
|
GROUP BY t1.b
|
|
HAVING (t1.b<14);
|
|
a b c
|
|
2 13 2
|
|
explain SELECT * FROM t1
|
|
WHERE
|
|
(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x)
|
|
GROUP BY t1.b
|
|
HAVING (t1.b<14);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.b 1
|
|
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 Using where; Using temporary
|
|
explain format=json SELECT * FROM t1
|
|
WHERE
|
|
(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x)
|
|
GROUP BY t1.b
|
|
HAVING (t1.b<14);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.b",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.b < 14 and t1.a is not null and t1.b is not null"
|
|
},
|
|
"table": {
|
|
"table_name": "<subquery2>",
|
|
"access_type": "eq_ref",
|
|
"possible_keys": ["distinct_key"],
|
|
"key": "distinct_key",
|
|
"key_length": "8",
|
|
"used_key_parts": ["x", "MAX(t2.y)"],
|
|
"ref": ["test.t1.a", "test.t1.b"],
|
|
"rows": 1,
|
|
"filtered": 100,
|
|
"materialized": {
|
|
"unique": 1,
|
|
"materialization": {
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"having_condition": "`MAX(t2.y)` < 14",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"rows": 4,
|
|
"filtered": 100,
|
|
"attached_condition": "t2.x < 5"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT * FROM t1
|
|
WHERE
|
|
(t1.b<14) AND
|
|
(t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x)
|
|
GROUP BY t1.b;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.b",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.b < 14 and t1.a is not null and t1.b is not null"
|
|
},
|
|
"table": {
|
|
"table_name": "<subquery3>",
|
|
"access_type": "eq_ref",
|
|
"possible_keys": ["distinct_key"],
|
|
"key": "distinct_key",
|
|
"key_length": "8",
|
|
"used_key_parts": ["x", "MAX(t2.y)"],
|
|
"ref": ["test.t1.a", "test.t1.b"],
|
|
"rows": 1,
|
|
"filtered": 100,
|
|
"materialized": {
|
|
"unique": 1,
|
|
"materialization": {
|
|
"query_block": {
|
|
"select_id": 3,
|
|
"having_condition": "`MAX(t2.y)` < 14",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"rows": 4,
|
|
"filtered": 100,
|
|
"attached_condition": "t2.x < 5"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
# non-standard allowed queries
|
|
# conjunctive subformula
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.c=2) AND (t1.a>1);
|
|
a MAX(t1.b) c
|
|
2 13 2
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.c=2) AND (t1.a>1);
|
|
a MAX(t1.b) c
|
|
2 13 2
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.c=2) AND (t1.a>1);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.c=2) AND (t1.a>1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t1.c = 2",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 1"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c FROM t1
|
|
WHERE (t1.a>1)
|
|
GROUP BY t1.a
|
|
HAVING (t1.c=2);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t1.c = 2",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 1"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT MAX(t1.a),t1.a,t1.b,t1.c
|
|
FROM t1
|
|
GROUP BY t1.b
|
|
HAVING (t1.a=2) AND (t1.b=13) AND (t1.c=2);
|
|
MAX(t1.a) a b c
|
|
3 2 13 2
|
|
SELECT MAX(t1.a),t1.a,t1.b,t1.c
|
|
FROM t1
|
|
GROUP BY t1.b
|
|
HAVING (t1.a=2) AND (t1.b=13) AND (t1.c=2);
|
|
MAX(t1.a) a b c
|
|
3 2 13 2
|
|
explain SELECT MAX(t1.a),t1.a,t1.b,t1.c
|
|
FROM t1
|
|
GROUP BY t1.b
|
|
HAVING (t1.a=2) AND (t1.b=13) AND (t1.c=2);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
|
|
explain format=json SELECT MAX(t1.a),t1.a,t1.b,t1.c
|
|
FROM t1
|
|
GROUP BY t1.b
|
|
HAVING (t1.a=2) AND (t1.b=13) AND (t1.c=2);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t1.a = 2 and t1.c = 2",
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.b = 13"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT MAX(t1.a),t1.a,t1.b,t1.c
|
|
FROM t1
|
|
WHERE (t1.b=13)
|
|
GROUP BY t1.b
|
|
HAVING (t1.a=2) AND (t1.c=2);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t1.a = 2 and t1.c = 2",
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.b = 13"
|
|
}
|
|
}
|
|
}
|
|
# extracted AND formula : using equalities
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a=t1.c) AND (t1.c>1);
|
|
a MAX(t1.b) c
|
|
2 13 2
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a=t1.c) AND (t1.c>1);
|
|
a MAX(t1.b) c
|
|
2 13 2
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a=t1.c) AND (t1.c>1);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a=t1.c) AND (t1.c>1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.c = t1.a and t1.a > 1"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b) FROM t1
|
|
WHERE (t1.a=t1.c) AND (t1.a>1)
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.c = t1.a and t1.a > 1"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a=t1.c) AND (t1.c=2);
|
|
a MAX(t1.b) c
|
|
2 13 2
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a=t1.c) AND (t1.c=2);
|
|
a MAX(t1.b) c
|
|
2 13 2
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a=t1.c) AND (t1.c=2);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a=t1.c) AND (t1.c=2);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 2 and t1.c = 2"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a=t1.c) AND (t1.a=2)
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 2 and t1.c = 2"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING ((t1.a=t1.c) AND (t1.a>1)) OR ((t1.a<3) AND (t1.c>3));
|
|
a MAX(t1.b) c
|
|
2 13 2
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING ((t1.a=t1.c) AND (t1.a>1)) OR ((t1.a<3) AND (t1.c>3));
|
|
a MAX(t1.b) c
|
|
2 13 2
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING ((t1.a=t1.c) AND (t1.a>1)) OR ((t1.a<3) AND (t1.c>3));
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING ((t1.a=t1.c) AND (t1.a>1)) OR ((t1.a<3) AND (t1.c>3));
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t1.c = t1.a and t1.a > 1 or t1.a < 3 and t1.c > 3",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.c = t1.a and t1.a > 1 or t1.a < 3"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE ((t1.a=t1.c) AND (t1.a>1)) OR (t1.a<3)
|
|
GROUP BY t1.a
|
|
HAVING ((t1.a=t1.c) AND (t1.a>1)) OR ((t1.a<3) AND (t1.c>3));
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t1.c = t1.a and t1.a > 1 or t1.a < 3 and t1.c > 3",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.c = t1.a and t1.a > 1 or t1.a < 3"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
# conjuctive subformula : pushdown using WHERE multiple equalities
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a=t1.c)
|
|
GROUP BY t1.a
|
|
HAVING (t1.c<3);
|
|
a MAX(t1.b) c
|
|
1 22 1
|
|
2 13 2
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a=t1.c)
|
|
GROUP BY t1.a
|
|
HAVING (t1.c<3);
|
|
a MAX(t1.b) c
|
|
1 22 1
|
|
2 13 2
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a=t1.c)
|
|
GROUP BY t1.a
|
|
HAVING (t1.c<3);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a=t1.c)
|
|
GROUP BY t1.a
|
|
HAVING (t1.c<3);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.c = t1.a and t1.a < 3"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a=t1.c) AND (t1.c<3)
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.c = t1.a and t1.a < 3"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
# extracted AND-formula : pushdown using WHERE multiple equalities
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a=t1.c)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (t1.c<3);
|
|
a MAX(t1.b) c
|
|
2 13 2
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a=t1.c)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (t1.c<3);
|
|
a MAX(t1.b) c
|
|
2 13 2
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a=t1.c)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (t1.c<3);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a=t1.c)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a>1) AND (t1.c<3);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.c = t1.a and t1.a > 1 and t1.a < 3"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a=t1.c) AND (t1.a>1) AND (t1.c<3)
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.c = t1.a and t1.a > 1 and t1.a < 3"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a=t1.c)
|
|
GROUP BY t1.a
|
|
HAVING (((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4)) AND (t1.a<2);
|
|
a MAX(t1.b) c
|
|
1 22 1
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a=t1.c)
|
|
GROUP BY t1.a
|
|
HAVING (((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4)) AND (t1.a<2);
|
|
a MAX(t1.b) c
|
|
1 22 1
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a=t1.c)
|
|
GROUP BY t1.a
|
|
HAVING (((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4)) AND (t1.a<2);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a=t1.c)
|
|
GROUP BY t1.a
|
|
HAVING (((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4)) AND (t1.a<2);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t1.a > 1 and max(t1.c) < 3 or t1.c < 4",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.c = t1.a and (t1.a > 1 or t1.a < 4) and t1.a < 2"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a=t1.c) AND (((t1.a>1) OR (t1.c<4)) AND (t1.a<2))
|
|
GROUP BY t1.a
|
|
HAVING ((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t1.a > 1 and max(t1.c) < 3 or t1.c < 4",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.c = t1.a and (t1.a > 1 or t1.a < 4) and t1.a < 2"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
# extracted OR-formula : pushdown using WHERE multiple equalities
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a=t1.c)
|
|
GROUP BY t1.a
|
|
HAVING ((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4);
|
|
a MAX(t1.b) c
|
|
1 22 1
|
|
2 13 2
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a=t1.c)
|
|
GROUP BY t1.a
|
|
HAVING ((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4);
|
|
a MAX(t1.b) c
|
|
1 22 1
|
|
2 13 2
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a=t1.c)
|
|
GROUP BY t1.a
|
|
HAVING ((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a=t1.c)
|
|
GROUP BY t1.a
|
|
HAVING ((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t1.a > 1 and max(t1.c) < 3 or t1.c < 4",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.c = t1.a and (t1.a > 1 or t1.a < 4)"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a=t1.c) AND ((t1.a>1) OR (t1.c<4))
|
|
GROUP BY t1.a
|
|
HAVING ((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t1.a > 1 and max(t1.c) < 3 or t1.c < 4",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.c = t1.a and (t1.a > 1 or t1.a < 4)"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
DROP TABLE t1,t2;
|
|
DROP VIEW v1;
|
|
DROP FUNCTION f1;
|
|
#
|
|
# MDEV-18668: pushdown from HAVING into impossible WHERE
|
|
#
|
|
CREATE TABLE t1 (a INT, b INT);
|
|
INSERT INTO t1 VALUES (1,1),(2,2);
|
|
SELECT a FROM t1 WHERE b = 1 AND b = 2 GROUP BY a HAVING a <= 3;
|
|
a
|
|
EXPLAIN
|
|
SELECT a FROM t1 WHERE b = 1 AND b = 2 GROUP BY a HAVING a <= 3;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-18769: unfixed OR condition pushed from HAVING into WHERE
|
|
#
|
|
CREATE TABLE t1(a INT, b INT, c INT);
|
|
CREATE TABLE t3(a INT, b INT, c INT, d INT);
|
|
INSERT INTO t1 VALUES (1,14,3), (2,13,2), (1,22,1), (3,13,4), (3,14,2);
|
|
INSERT INTO t3 VALUES (1,2,16,1), (1,3,11,2), (2,3,10,2);
|
|
# nothing to push
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING t1.b = 13 AND MAX(t1.c) > 2;
|
|
a b MAX(t1.c)
|
|
3 13 4
|
|
SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING t1.b = 13 AND MAX(t1.c) > 2;
|
|
a b MAX(t1.c)
|
|
3 13 4
|
|
explain SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING t1.b = 13 AND MAX(t1.c) > 2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING t1.b = 13 AND MAX(t1.c) > 2;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t1.b = 13 and max(t1.c) > 2",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING t1.b = 13 AND MAX(t1.c) > 2;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t1.b = 13 and max(t1.c) > 2",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
# extracted AND formula
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.b
|
|
HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14);
|
|
a b MAX(t1.c)
|
|
2 13 2
|
|
3 13 4
|
|
SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.b
|
|
HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14);
|
|
a b MAX(t1.c)
|
|
2 13 2
|
|
3 13 4
|
|
explain SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.b
|
|
HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.b
|
|
HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.b",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "(t1.a = 1 or t1.b > 10) and t1.b < 14"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.a = 1 OR t1.b > 10) AND (t1.b < 14)
|
|
GROUP BY t1.a,t1.b;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.b",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "(t1.a = 1 or t1.b > 10) and t1.b < 14"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.b
|
|
HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15);
|
|
a b MAX(t1.c)
|
|
1 22 1
|
|
2 13 2
|
|
3 13 4
|
|
SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.b
|
|
HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15);
|
|
a b MAX(t1.c)
|
|
1 22 1
|
|
2 13 2
|
|
3 13 4
|
|
explain SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.b
|
|
HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.b
|
|
HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.b",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "(t1.a = 1 or t1.b > 10) and (t1.b < 14 or t1.b > 15)"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15)
|
|
GROUP BY t1.a,t1.b;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.b",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "(t1.a = 1 or t1.b > 10) and (t1.b < 14 or t1.b > 15)"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
# extracted AND formula : equality in the inner AND formula
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.b
|
|
HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2));
|
|
a b MAX(t1.c)
|
|
2 13 2
|
|
3 13 4
|
|
SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.b
|
|
HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2));
|
|
a b MAX(t1.c)
|
|
2 13 2
|
|
3 13 4
|
|
explain SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.b
|
|
HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2));
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.b
|
|
HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2));
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.b",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "(t1.a = 1 or t1.b > 10) and (t1.b < 14 or t1.a = 2 and t1.b > 15)"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2))
|
|
GROUP BY t1.a,t1.b;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.b",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "(t1.a = 1 or t1.b > 10) and (t1.b < 14 or t1.a = 2 and t1.b > 15)"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
# extracted OR formula
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.b
|
|
HAVING (t1.a < 2) OR (t1.b = 13 AND t1.a > 2);
|
|
a b MAX(t1.c)
|
|
1 14 3
|
|
1 22 1
|
|
3 13 4
|
|
SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.b
|
|
HAVING (t1.a < 2) OR (t1.b = 13 AND t1.a > 2);
|
|
a b MAX(t1.c)
|
|
1 14 3
|
|
1 22 1
|
|
3 13 4
|
|
explain SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.b
|
|
HAVING (t1.a < 2) OR (t1.b = 13 AND t1.a > 2);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.b
|
|
HAVING (t1.a < 2) OR (t1.b = 13 AND t1.a > 2);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.b",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a < 2 or t1.b = 13 and t1.a > 2"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.a < 2) OR (t1.b = 13 AND t1.a > 2)
|
|
GROUP BY t1.a,t1.b;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.b",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a < 2 or t1.b = 13 and t1.a > 2"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.b
|
|
HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13);
|
|
a b MAX(t1.c)
|
|
1 14 3
|
|
3 13 4
|
|
SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.b
|
|
HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13);
|
|
a b MAX(t1.c)
|
|
1 14 3
|
|
3 13 4
|
|
explain SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.b
|
|
HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.b
|
|
HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.b",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.b = 14 and t1.a < 2 or t1.b = 13 and t1.a > 2"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13)
|
|
GROUP BY t1.a,t1.b;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.b",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.b = 14 and t1.a < 2 or t1.b = 13 and t1.a > 2"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.b
|
|
HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14));
|
|
a b MAX(t1.c)
|
|
1 14 3
|
|
3 13 4
|
|
3 14 2
|
|
SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.b
|
|
HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14));
|
|
a b MAX(t1.c)
|
|
1 14 3
|
|
3 13 4
|
|
3 14 2
|
|
explain SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.b
|
|
HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14));
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.b
|
|
HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14));
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.b",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.b = 14 and t1.a < 2 or t1.a > 2 and (t1.b = 13 or t1.b = 14)"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14))
|
|
GROUP BY t1.a,t1.b;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.b",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.b = 14 and t1.a < 2 or t1.a > 2 and (t1.b = 13 or t1.b = 14)"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2));
|
|
a b MAX(t1.c)
|
|
1 14 3
|
|
SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2));
|
|
a b MAX(t1.c)
|
|
1 14 3
|
|
explain SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2));
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2));
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t1.a < 2 and max(t1.c) = 2 or max(t1.c) > 2 and (t1.a = 1 or t1.a = 2)",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a < 2 or t1.a = 1 or t1.a = 2"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.a < 2) OR (t1.a = 1 OR t1.a = 2)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2));
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t1.a < 2 and max(t1.c) = 2 or max(t1.c) > 2 and (t1.a = 1 or t1.a = 2)",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a < 2 or t1.a = 1 or t1.a = 2"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2));
|
|
a b MAX(t1.c)
|
|
1 14 3
|
|
2 13 2
|
|
SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2));
|
|
a b MAX(t1.c)
|
|
1 14 3
|
|
2 13 2
|
|
explain SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2));
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2));
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t1.a = 2 and max(t1.c) = 2 or max(t1.c) > 2 and (t1.a = 1 or t1.a = 2)",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 2 or t1.a = 1 or t1.a = 2"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.a = 2) OR (t1.a = 1 OR t1.a = 2)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2));
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t1.a = 2 and max(t1.c) = 2 or max(t1.c) > 2 and (t1.a = 1 or t1.a = 2)",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 2 or t1.a = 1 or t1.a = 2"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
# conjunctive subformula : equality pushdown
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1) AND (MAX(t1.c) = 3);
|
|
a b MAX(t1.c)
|
|
1 14 3
|
|
SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1) AND (MAX(t1.c) = 3);
|
|
a b MAX(t1.c)
|
|
1 14 3
|
|
explain SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1) AND (MAX(t1.c) = 3);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
|
|
explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1) AND (MAX(t1.c) = 3);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "max(t1.c) = 3",
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 1"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.a = 1)
|
|
GROUP BY t1.a
|
|
HAVING (MAX(t1.c) = 3);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "max(t1.c) = 3",
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 1"
|
|
}
|
|
}
|
|
}
|
|
# conjunctive subformula : equalities pushdown
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.a = 1) AND (t1.c = 3) AND MAX(t1.b = 14);
|
|
a b MAX(t1.c)
|
|
1 14 3
|
|
SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.a = 1) AND (t1.c = 3) AND MAX(t1.b = 14);
|
|
a b MAX(t1.c)
|
|
1 14 3
|
|
explain SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.a = 1) AND (t1.c = 3) AND MAX(t1.b = 14);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
|
|
explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.a = 1) AND (t1.c = 3) AND MAX(t1.b = 14);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "max(t1.b = 14)",
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 1 and t1.c = 3"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.c = 3)
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (MAX(t1.b) = 14);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "max(t1.b) = 14",
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 1 and t1.c = 3"
|
|
}
|
|
}
|
|
}
|
|
# conjunctive subformula : multiple equality consists of
|
|
two equalities pushdown
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.a = 1) AND (t1.c = 1) AND MAX(t1.b = 14);
|
|
a b MAX(t1.c)
|
|
SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.a = 1) AND (t1.c = 1) AND MAX(t1.b = 14);
|
|
a b MAX(t1.c)
|
|
explain SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.a = 1) AND (t1.c = 1) AND MAX(t1.b = 14);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
|
|
explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.a = 1) AND (t1.c = 1) AND MAX(t1.b = 14);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "max(t1.b = 14)",
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 1 and t1.c = 1"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.c = 1)
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (MAX(t1.b) = 14);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "max(t1.b) = 14",
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 1 and t1.c = 1"
|
|
}
|
|
}
|
|
}
|
|
#
|
|
# Pushdown from HAVING into non-empty WHERE
|
|
#
|
|
# inequality : inequality in WHERE
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.b > 2)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 3);
|
|
a b MAX(t1.c)
|
|
1 14 3
|
|
2 13 2
|
|
SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.b > 2)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 3);
|
|
a b MAX(t1.c)
|
|
1 14 3
|
|
2 13 2
|
|
explain SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.b > 2)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 3);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.b > 2)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 3);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.b > 2 and t1.a < 3"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.b > 2) AND (t1.a < 3)
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.b > 2 and t1.a < 3"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
# equality : inequality in WHERE
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.b > 2)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 3);
|
|
a b MAX(t1.c)
|
|
3 13 4
|
|
SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.b > 2)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 3);
|
|
a b MAX(t1.c)
|
|
3 13 4
|
|
explain SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.b > 2)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 3);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
|
|
explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.b > 2)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 3);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 3 and t1.b > 2"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.b > 2) AND (t1.a = 3)
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 3 and t1.b > 2"
|
|
}
|
|
}
|
|
}
|
|
# inequality : equality in WHERE
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.b = 14)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 3);
|
|
a b MAX(t1.c)
|
|
1 14 3
|
|
SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.b = 14)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 3);
|
|
a b MAX(t1.c)
|
|
1 14 3
|
|
explain SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.b = 14)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 3);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.b = 14)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 3);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.b = 14 and t1.a < 3"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.b = 14) AND (t1.a < 3)
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.b = 14 and t1.a < 3"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
# equality : equality in WHERE
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.b = 14)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1);
|
|
a b MAX(t1.c)
|
|
1 14 3
|
|
SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.b = 14)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1);
|
|
a b MAX(t1.c)
|
|
1 14 3
|
|
explain SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.b = 14)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
|
|
explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.b = 14)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 1 and t1.b = 14"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,t1.b,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.b = 14) AND (t1.a = 1)
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.b = 14 and t1.a = 1"
|
|
}
|
|
}
|
|
}
|
|
# equality : equality in WHERE, impossible WHERE
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.a = 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1);
|
|
a MAX(t1.c)
|
|
SELECT t1.a,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.a = 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1);
|
|
a MAX(t1.c)
|
|
explain SELECT t1.a,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.a = 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
explain format=json SELECT t1.a,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.a = 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"message": "Impossible WHERE"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.c)
|
|
FROM t1
|
|
WHERE (t1.a = 3) AND (t1.a = 1)
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"message": "Impossible WHERE"
|
|
}
|
|
}
|
|
}
|
|
# equality : equality in WHERE (equal through constant)
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.c = 1)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1);
|
|
a MAX(t1.b) c
|
|
1 22 1
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.c = 1)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1);
|
|
a MAX(t1.b) c
|
|
1 22 1
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.c = 1)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.c = 1)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 1 and t1.c = 1"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.c = 1) AND (t1.a = 1)
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.c = 1 and t1.a = 1"
|
|
}
|
|
}
|
|
}
|
|
# inequality : AND formula in WHERE
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.c > 0) AND (t1.c < 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a > 1);
|
|
a MAX(t1.b) c
|
|
2 13 2
|
|
3 14 2
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.c > 0) AND (t1.c < 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a > 1);
|
|
a MAX(t1.b) c
|
|
2 13 2
|
|
3 14 2
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.c > 0) AND (t1.c < 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a > 1);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.c > 0) AND (t1.c < 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a > 1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.c > 0 and t1.c < 3 and t1.a > 1"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.c > 0) AND (t1.c < 3) AND (t1.a > 1)
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.c > 0 and t1.c < 3 and t1.a > 1"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
# equality : AND formula in WHERE
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.c > 0) AND (t1.c < 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1);
|
|
a MAX(t1.b) c
|
|
1 22 1
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.c > 0) AND (t1.c < 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1);
|
|
a MAX(t1.b) c
|
|
1 22 1
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.c > 0) AND (t1.c < 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.c > 0) AND (t1.c < 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 1 and t1.c > 0 and t1.c < 3"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.c > 0) AND (t1.c < 3) AND (t1.a = 1)
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 1 and t1.c > 0 and t1.c < 3"
|
|
}
|
|
}
|
|
}
|
|
# equality : AND formula in WHERE, impossible WHERE
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 0) AND (t1.c < 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1);
|
|
a MAX(t1.b) c
|
|
1 22 1
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 0) AND (t1.c < 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1);
|
|
a MAX(t1.b) c
|
|
1 22 1
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 0) AND (t1.c < 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 0) AND (t1.c < 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"const_condition": "1",
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 1 and t1.c < 3"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 0) AND (t1.c < 3) AND (t1.a = 1)
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 1 and t1.c < 3"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
WHERE (t1.a = 0) AND (t1.a = 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1);
|
|
a MAX(t1.b)
|
|
SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
WHERE (t1.a = 0) AND (t1.a = 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1);
|
|
a MAX(t1.b)
|
|
explain SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
WHERE (t1.a = 0) AND (t1.a = 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
explain format=json SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
WHERE (t1.a = 0) AND (t1.a = 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"message": "Impossible WHERE"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
WHERE (t1.a = 0) AND (t1.a = 3) AND (t1.a = 1)
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"message": "Impossible WHERE"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t3.a,t3.b,MAX(t3.c),t3.d
|
|
FROM t3
|
|
WHERE (t3.b = 2) AND (t3.d = 1)
|
|
GROUP BY t3.a,t3.b,t3.d
|
|
HAVING (t3.a = 1);
|
|
a b MAX(t3.c) d
|
|
1 2 16 1
|
|
SELECT t3.a,t3.b,MAX(t3.c),t3.d
|
|
FROM t3
|
|
WHERE (t3.b = 2) AND (t3.d = 1)
|
|
GROUP BY t3.a,t3.b,t3.d
|
|
HAVING (t3.a = 1);
|
|
a b MAX(t3.c) d
|
|
1 2 16 1
|
|
explain SELECT t3.a,t3.b,MAX(t3.c),t3.d
|
|
FROM t3
|
|
WHERE (t3.b = 2) AND (t3.d = 1)
|
|
GROUP BY t3.a,t3.b,t3.d
|
|
HAVING (t3.a = 1);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where
|
|
explain format=json SELECT t3.a,t3.b,MAX(t3.c),t3.d
|
|
FROM t3
|
|
WHERE (t3.b = 2) AND (t3.d = 1)
|
|
GROUP BY t3.a,t3.b,t3.d
|
|
HAVING (t3.a = 1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ALL",
|
|
"rows": 3,
|
|
"filtered": 100,
|
|
"attached_condition": "t3.b = 2 and t3.d = 1 and t3.a = 1"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t3.a,t3.b,MAX(t3.c),t3.d
|
|
FROM t3
|
|
WHERE (t3.b = 2) AND (t3.d = 1) AND (t3.a = 1)
|
|
GROUP BY t3.a,t3.b,t3.d;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ALL",
|
|
"rows": 3,
|
|
"filtered": 100,
|
|
"attached_condition": "t3.b = 2 and t3.d = 1 and t3.a = 1"
|
|
}
|
|
}
|
|
}
|
|
# inequality : OR formula in WHERE
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 1) OR (t1.c < 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 2);
|
|
a MAX(t1.b) c
|
|
1 22 1
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 1) OR (t1.c < 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 2);
|
|
a MAX(t1.b) c
|
|
1 22 1
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 1) OR (t1.c < 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 2);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 1) OR (t1.c < 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 2);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "(t1.a > 1 or t1.c < 3) and t1.a < 2"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE ((t1.a > 1) OR (t1.c < 3)) AND (t1.a < 2)
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "(t1.a > 1 or t1.c < 3) and t1.a < 2"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
WHERE (t1.a = 1) OR (t1.a = 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 2);
|
|
a MAX(t1.b)
|
|
SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
WHERE (t1.a = 1) OR (t1.a = 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 2);
|
|
a MAX(t1.b)
|
|
explain SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
WHERE (t1.a = 1) OR (t1.a = 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 2);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
explain format=json SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
WHERE (t1.a = 1) OR (t1.a = 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 2);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"message": "Impossible WHERE"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b)
|
|
FROM t1
|
|
WHERE ((t1.a = 1) OR (t1.a = 3)) AND (t1.a = 2)
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"message": "Impossible WHERE"
|
|
}
|
|
}
|
|
}
|
|
# AND formula : inequality in WHERE
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 1)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 4) AND (t1.a > 0);
|
|
a MAX(t1.b) c
|
|
2 13 2
|
|
3 14 4
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 1)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 4) AND (t1.a > 0);
|
|
a MAX(t1.b) c
|
|
2 13 2
|
|
3 14 4
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 1)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 4) AND (t1.a > 0);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 1)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 4) AND (t1.a > 0);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 1 and t1.a < 4 and t1.a > 0"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 1) AND (t1.a < 4) AND (t1.a > 0)
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 1 and t1.a < 4 and t1.a > 0"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
# AND formula : equality in WHERE
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 4) AND (t1.a > 0);
|
|
a MAX(t1.b) c
|
|
1 22 3
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 4) AND (t1.a > 0);
|
|
a MAX(t1.b) c
|
|
1 22 3
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 4) AND (t1.a > 0);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 4) AND (t1.a > 0);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"const_condition": "1 and 1",
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 1"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.a < 4) AND (t1.a > 0)
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 1"
|
|
}
|
|
}
|
|
}
|
|
# OR formula : inequality in WHERE
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 1)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 4) OR (t1.a > 0);
|
|
a MAX(t1.b) c
|
|
2 13 2
|
|
3 14 4
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 1)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 4) OR (t1.a > 0);
|
|
a MAX(t1.b) c
|
|
2 13 2
|
|
3 14 4
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 1)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 4) OR (t1.a > 0);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 1)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 4) OR (t1.a > 0);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 1 and (t1.a < 4 or t1.a > 0)"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 1) AND ((t1.a < 4) OR (t1.a > 0))
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 1 and (t1.a < 4 or t1.a > 0)"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
# OR formula : equality in WHERE
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 4) OR (t1.a > 0);
|
|
a MAX(t1.b) c
|
|
1 22 3
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 4) OR (t1.a > 0);
|
|
a MAX(t1.b) c
|
|
1 22 3
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 4) OR (t1.a > 0);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 4) OR (t1.a > 0);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"const_condition": "1",
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 1"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND ((t1.a < 4) OR (t1.a > 0))
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 1"
|
|
}
|
|
}
|
|
}
|
|
# AND formula : AND formula in WHERE
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 1) AND (t1.c < 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 4) AND (t1.c > 1);
|
|
a MAX(t1.b) c
|
|
2 13 2
|
|
3 14 2
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 1) AND (t1.c < 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 4) AND (t1.c > 1);
|
|
a MAX(t1.b) c
|
|
2 13 2
|
|
3 14 2
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 1) AND (t1.c < 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 4) AND (t1.c > 1);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 1) AND (t1.c < 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 4) AND (t1.c > 1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t1.c > 1",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 1 and t1.c < 3 and t1.a < 4"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE ((t1.a > 1) AND (t1.c < 3)) AND
|
|
(t1.a < 4)
|
|
GROUP BY t1.a
|
|
HAVING (t1.c > 1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t1.c > 1",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a > 1 and t1.c < 3 and t1.a < 4"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.c < 3)
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.a < 4) AND (t1.c > 1);
|
|
a MAX(t1.b) c
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.c < 3)
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.a < 4) AND (t1.c > 1);
|
|
a MAX(t1.b) c
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.c < 3)
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.a < 4) AND (t1.c > 1);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.c < 3)
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.a < 4) AND (t1.c > 1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"const_condition": "1",
|
|
"filesort": {
|
|
"sort_key": "t1.c",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 1 and t1.c < 3 and t1.c > 1"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE ((t1.a = 1) AND (t1.c < 3)) AND
|
|
((t1.a < 4) AND (t1.c > 1))
|
|
GROUP BY t1.a,t1.c;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.c",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 1 and t1.c < 3 and t1.c > 1"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.c = 3)
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.a < 4) AND (t1.c > 1);
|
|
a MAX(t1.b) c
|
|
1 14 3
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.c = 3)
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.a < 4) AND (t1.c > 1);
|
|
a MAX(t1.b) c
|
|
1 14 3
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.c = 3)
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.a < 4) AND (t1.c > 1);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.c = 3)
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.a < 4) AND (t1.c > 1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"const_condition": "1 and 1",
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 1 and t1.c = 3"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE ((t1.a = 1) AND (t1.c = 3)) AND
|
|
((t1.a < 4) AND (t1.c > 1))
|
|
GROUP BY t1.a,t1.c;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 1 and t1.c = 3"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t3.a,t3.b,MAX(t3.c),t3.d
|
|
FROM t3
|
|
WHERE (t3.a = 1) AND (t3.d = 1)
|
|
GROUP BY t3.a,t3.b
|
|
HAVING (t3.b = 2) AND (t3.d > 0);
|
|
a b MAX(t3.c) d
|
|
1 2 16 1
|
|
SELECT t3.a,t3.b,MAX(t3.c),t3.d
|
|
FROM t3
|
|
WHERE (t3.a = 1) AND (t3.d = 1)
|
|
GROUP BY t3.a,t3.b
|
|
HAVING (t3.b = 2) AND (t3.d > 0);
|
|
a b MAX(t3.c) d
|
|
1 2 16 1
|
|
explain SELECT t3.a,t3.b,MAX(t3.c),t3.d
|
|
FROM t3
|
|
WHERE (t3.a = 1) AND (t3.d = 1)
|
|
GROUP BY t3.a,t3.b
|
|
HAVING (t3.b = 2) AND (t3.d > 0);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where
|
|
explain format=json SELECT t3.a,t3.b,MAX(t3.c),t3.d
|
|
FROM t3
|
|
WHERE (t3.a = 1) AND (t3.d = 1)
|
|
GROUP BY t3.a,t3.b
|
|
HAVING (t3.b = 2) AND (t3.d > 0);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t3.d > 0",
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ALL",
|
|
"rows": 3,
|
|
"filtered": 100,
|
|
"attached_condition": "t3.a = 1 and t3.d = 1 and t3.b = 2"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t3.a,t3.b,MAX(t3.c),t3.d
|
|
FROM t3
|
|
WHERE (t3.a = 1) AND (t3.d = 1) AND
|
|
(t3.b = 2)
|
|
GROUP BY t3.a,t3.b
|
|
HAVING (t3.d > 0);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t3.d > 0",
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ALL",
|
|
"rows": 3,
|
|
"filtered": 100,
|
|
"attached_condition": "t3.a = 1 and t3.d = 1 and t3.b = 2"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t3.a,t3.b,MAX(t3.c),t3.d
|
|
FROM t3
|
|
WHERE (t3.a = 1) AND (t3.d = 1)
|
|
GROUP BY t3.a,t3.b,t3.d
|
|
HAVING (t3.b = 2) AND (t3.d > 0);
|
|
a b MAX(t3.c) d
|
|
1 2 16 1
|
|
SELECT t3.a,t3.b,MAX(t3.c),t3.d
|
|
FROM t3
|
|
WHERE (t3.a = 1) AND (t3.d = 1)
|
|
GROUP BY t3.a,t3.b,t3.d
|
|
HAVING (t3.b = 2) AND (t3.d > 0);
|
|
a b MAX(t3.c) d
|
|
1 2 16 1
|
|
explain SELECT t3.a,t3.b,MAX(t3.c),t3.d
|
|
FROM t3
|
|
WHERE (t3.a = 1) AND (t3.d = 1)
|
|
GROUP BY t3.a,t3.b,t3.d
|
|
HAVING (t3.b = 2) AND (t3.d > 0);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where
|
|
explain format=json SELECT t3.a,t3.b,MAX(t3.c),t3.d
|
|
FROM t3
|
|
WHERE (t3.a = 1) AND (t3.d = 1)
|
|
GROUP BY t3.a,t3.b,t3.d
|
|
HAVING (t3.b = 2) AND (t3.d > 0);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"const_condition": "1",
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ALL",
|
|
"rows": 3,
|
|
"filtered": 100,
|
|
"attached_condition": "t3.a = 1 and t3.d = 1 and t3.b = 2"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t3.a,t3.b,MAX(t3.c),t3.d
|
|
FROM t3
|
|
WHERE (t3.a = 1) AND (t3.d = 1) AND
|
|
(t3.b = 2) AND (t3.d > 0)
|
|
GROUP BY t3.a,t3.b,t3.d;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ALL",
|
|
"rows": 3,
|
|
"filtered": 100,
|
|
"attached_condition": "t3.a = 1 and t3.d = 1 and t3.b = 2"
|
|
}
|
|
}
|
|
}
|
|
# AND formula : OR formula in WHERE
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 1) OR (t1.c < 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 4) AND (t1.c > 1);
|
|
a MAX(t1.b) c
|
|
2 13 2
|
|
3 14 4
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 1) OR (t1.c < 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 4) AND (t1.c > 1);
|
|
a MAX(t1.b) c
|
|
2 13 2
|
|
3 14 4
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 1) OR (t1.c < 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 4) AND (t1.c > 1);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 1) OR (t1.c < 3)
|
|
GROUP BY t1.a
|
|
HAVING (t1.a < 4) AND (t1.c > 1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t1.c > 1",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "(t1.a > 1 or t1.c < 3) and t1.a < 4"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE ((t1.a > 1) OR (t1.c < 3)) AND
|
|
(t1.a < 4)
|
|
GROUP BY t1.a
|
|
HAVING (t1.c > 1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t1.c > 1",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "(t1.a > 1 or t1.c < 3) and t1.a < 4"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 1) OR (t1.c < 3)
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.a < 4) AND (t1.c > 1);
|
|
a MAX(t1.b) c
|
|
2 13 2
|
|
3 14 2
|
|
3 13 4
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 1) OR (t1.c < 3)
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.a < 4) AND (t1.c > 1);
|
|
a MAX(t1.b) c
|
|
2 13 2
|
|
3 14 2
|
|
3 13 4
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 1) OR (t1.c < 3)
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.a < 4) AND (t1.c > 1);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a > 1) OR (t1.c < 3)
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.a < 4) AND (t1.c > 1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.c",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "(t1.a > 1 or t1.c < 3) and t1.a < 4 and t1.c > 1"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE ((t1.a > 1) OR (t1.c < 3)) AND
|
|
(t1.a < 4) AND (t1.c > 1)
|
|
GROUP BY t1.a,t1.c;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.c",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "(t1.a > 1 or t1.c < 3) and t1.a < 4 and t1.c > 1"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) OR (t1.a = 3)
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.a = 4) OR (t1.c > 1);
|
|
a MAX(t1.b) c
|
|
1 14 3
|
|
3 14 2
|
|
3 13 4
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) OR (t1.a = 3)
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.a = 4) OR (t1.c > 1);
|
|
a MAX(t1.b) c
|
|
1 14 3
|
|
3 14 2
|
|
3 13 4
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) OR (t1.a = 3)
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.a = 4) OR (t1.c > 1);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) OR (t1.a = 3)
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.a = 4) OR (t1.c > 1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.c",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "(t1.a = 1 or t1.a = 3) and (t1.a = 4 or t1.c > 1)"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE ((t1.a = 1) OR (t1.a = 3)) AND
|
|
((t1.a = 4) OR (t1.c > 1))
|
|
GROUP BY t1.a,t1.c;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.c",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "(t1.a = 1 or t1.a = 3) and (t1.a = 4 or t1.c > 1)"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
# equality : pushdown through equality in WHERE
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.a = t1.c)
|
|
GROUP BY t1.a
|
|
HAVING (t1.c = 1);
|
|
a MAX(t1.b) c
|
|
1 22 1
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.a = t1.c)
|
|
GROUP BY t1.a
|
|
HAVING (t1.c = 1);
|
|
a MAX(t1.b) c
|
|
1 22 1
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.a = t1.c)
|
|
GROUP BY t1.a
|
|
HAVING (t1.c = 1);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.a = t1.c)
|
|
GROUP BY t1.a
|
|
HAVING (t1.c = 1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 1 and t1.c = 1"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.a = t1.c) AND (t1.c = 1)
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 1 and t1.c = 1"
|
|
}
|
|
}
|
|
}
|
|
# OR formula : pushdown through equality
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.a = t1.c)
|
|
GROUP BY t1.a
|
|
HAVING (t1.c = 1) OR (t1.c = 2);
|
|
a MAX(t1.b) c
|
|
1 22 1
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.a = t1.c)
|
|
GROUP BY t1.a
|
|
HAVING (t1.c = 1) OR (t1.c = 2);
|
|
a MAX(t1.b) c
|
|
1 22 1
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.a = t1.c)
|
|
GROUP BY t1.a
|
|
HAVING (t1.c = 1) OR (t1.c = 2);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.a = t1.c)
|
|
GROUP BY t1.a
|
|
HAVING (t1.c = 1) OR (t1.c = 2);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 1 and t1.c = 1"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.a = t1.c) AND
|
|
((t1.c = 1) OR (t1.c = 2))
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 5,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 1 and t1.c = 1"
|
|
}
|
|
}
|
|
}
|
|
# OR formula : pushdown through equality, impossible WHERE
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.a = t1.c)
|
|
GROUP BY t1.a
|
|
HAVING (t1.c = 3) OR (t1.c = 2);
|
|
a MAX(t1.b) c
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.a = t1.c)
|
|
GROUP BY t1.a
|
|
HAVING (t1.c = 3) OR (t1.c = 2);
|
|
a MAX(t1.b) c
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.a = t1.c)
|
|
GROUP BY t1.a
|
|
HAVING (t1.c = 3) OR (t1.c = 2);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.a = t1.c)
|
|
GROUP BY t1.a
|
|
HAVING (t1.c = 3) OR (t1.c = 2);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"message": "Impossible WHERE"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.a = t1.c) AND
|
|
((t1.c = 3) OR (t1.c = 2))
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"message": "Impossible WHERE"
|
|
}
|
|
}
|
|
}
|
|
# AND formula : pushdown through equality, impossible WHERE
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1)
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c);
|
|
a MAX(t1.b) c
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1)
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c);
|
|
a MAX(t1.b) c
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1)
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1)
|
|
GROUP BY t1.a,t1.c
|
|
HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"message": "Impossible WHERE"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.c = 3) AND
|
|
(t1.a > 2) AND (t1.a = t1.c)
|
|
GROUP BY t1.a,t1.c;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"message": "Impossible WHERE"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1)
|
|
GROUP BY t1.a
|
|
HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c);
|
|
a MAX(t1.b) c
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1)
|
|
GROUP BY t1.a
|
|
HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c);
|
|
a MAX(t1.b) c
|
|
explain SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1)
|
|
GROUP BY t1.a
|
|
HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1)
|
|
GROUP BY t1.a
|
|
HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"message": "Impossible WHERE"
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
WHERE (t1.a = 1) AND (t1.c = 3) AND
|
|
(t1.a > 2) AND (t1.a = t1.c)
|
|
GROUP BY t1.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"message": "Impossible WHERE"
|
|
}
|
|
}
|
|
}
|
|
# AND formula with OR subformula : AND condition in WHERE
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t3.a,MAX(t3.b),t3.c,t3.d
|
|
FROM t3
|
|
WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2))
|
|
GROUP BY t3.a
|
|
HAVING (t3.a = t3.d) AND ((t3.d = 1) OR (t3.d > 1));
|
|
a MAX(t3.b) c d
|
|
SELECT t3.a,MAX(t3.b),t3.c,t3.d
|
|
FROM t3
|
|
WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2))
|
|
GROUP BY t3.a
|
|
HAVING (t3.a = t3.d) AND ((t3.d = 1) OR (t3.d > 1));
|
|
a MAX(t3.b) c d
|
|
explain SELECT t3.a,MAX(t3.b),t3.c,t3.d
|
|
FROM t3
|
|
WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2))
|
|
GROUP BY t3.a
|
|
HAVING (t3.a = t3.d) AND ((t3.d = 1) OR (t3.d > 1));
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t3.a,MAX(t3.b),t3.c,t3.d
|
|
FROM t3
|
|
WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2))
|
|
GROUP BY t3.a
|
|
HAVING (t3.a = t3.d) AND ((t3.d = 1) OR (t3.d > 1));
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t3.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ALL",
|
|
"rows": 3,
|
|
"filtered": 100,
|
|
"attached_condition": "t3.d = t3.a and t3.a > 1 and (t3.c = 3 or t3.c < 2) and (t3.a = 1 or t3.a > 1)"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t3.a,MAX(t3.c),t3.d
|
|
FROM t3
|
|
WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2)) AND
|
|
(t3.a = t3.d) AND ((t3.d = 1) OR (t3.d > 1))
|
|
GROUP BY t3.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t3.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ALL",
|
|
"rows": 3,
|
|
"filtered": 100,
|
|
"attached_condition": "t3.d = t3.a and t3.a > 1 and (t3.c = 3 or t3.c < 2) and (t3.a = 1 or t3.a > 1)"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
# AND formula with OR subformula : AND condition in WHERE
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for SELECT t3.a,MAX(t3.b),t3.c,t3.d
|
|
FROM t3
|
|
WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2))
|
|
GROUP BY t3.a
|
|
HAVING (t3.a = t3.d) AND (((t3.d = t3.c) AND (t3.c < 15)) OR (t3.d > 1));
|
|
a MAX(t3.b) c d
|
|
SELECT t3.a,MAX(t3.b),t3.c,t3.d
|
|
FROM t3
|
|
WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2))
|
|
GROUP BY t3.a
|
|
HAVING (t3.a = t3.d) AND (((t3.d = t3.c) AND (t3.c < 15)) OR (t3.d > 1));
|
|
a MAX(t3.b) c d
|
|
explain SELECT t3.a,MAX(t3.b),t3.c,t3.d
|
|
FROM t3
|
|
WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2))
|
|
GROUP BY t3.a
|
|
HAVING (t3.a = t3.d) AND (((t3.d = t3.c) AND (t3.c < 15)) OR (t3.d > 1));
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using temporary; Using filesort
|
|
explain format=json SELECT t3.a,MAX(t3.b),t3.c,t3.d
|
|
FROM t3
|
|
WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2))
|
|
GROUP BY t3.a
|
|
HAVING (t3.a = t3.d) AND (((t3.d = t3.c) AND (t3.c < 15)) OR (t3.d > 1));
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t3.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ALL",
|
|
"rows": 3,
|
|
"filtered": 100,
|
|
"attached_condition": "t3.d = t3.a and t3.a > 1 and (t3.c = 3 or t3.c < 2) and (t3.c = t3.a and t3.c < 15 or t3.a > 1)"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set statement optimizer_switch='condition_pushdown_from_having=off' for explain format=json SELECT t3.a,t3.b,MAX(t3.c),t3.d
|
|
FROM t3
|
|
WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2)) AND
|
|
(t3.a = t3.d) AND (((t3.d = t3.c) AND (t3.c < 15)) OR (t3.d > 1))
|
|
GROUP BY t3.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t3.a",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ALL",
|
|
"rows": 3,
|
|
"filtered": 100,
|
|
"attached_condition": "t3.d = t3.a and t3.a > 1 and (t3.c = 3 or t3.c < 2) and (t3.c = t3.a and t3.a < 15 or t3.a > 1)"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
# prepare statement
|
|
PREPARE stmt1 from "
|
|
SELECT t1.a,MAX(t1.b),t1.c
|
|
FROM t1
|
|
GROUP BY t1.a
|
|
HAVING (t1.a = 1)
|
|
";
|
|
execute stmt1;
|
|
a MAX(t1.b) c
|
|
1 22 3
|
|
execute stmt1;
|
|
a MAX(t1.b) c
|
|
1 22 3
|
|
deallocate prepare stmt1;
|
|
DROP TABLE t1,t3;
|
|
#
|
|
# MDEV-19185: pushdown constant function with subquery
|
|
#
|
|
CREATE TABLE t1 (pk INT, c1 VARCHAR(64));
|
|
INSERT INTO t1 VALUES (1,'bbb'),(2,'aaa'),(3,'ccc');
|
|
CREATE VIEW v1 AS SELECT * FROM t1;
|
|
SELECT pk
|
|
FROM t1
|
|
GROUP BY pk
|
|
HAVING (1 NOT IN (SELECT COUNT(t1.c1) FROM (v1, t1)));
|
|
pk
|
|
1
|
|
2
|
|
3
|
|
DROP TABLE t1;
|
|
DROP VIEW v1;
|
|
#
|
|
# MDEV-19186: temporary table defined with view field in HAVING
|
|
#
|
|
CREATE TABLE t1 (pk INT, x VARCHAR(10));
|
|
INSERT INTO t1 VALUES (1,'y'),(2,'s'),(3,'aaa');
|
|
CREATE VIEW v1 AS SELECT * FROM t1;
|
|
CREATE TABLE t2 (pk INT, x VARCHAR(10));
|
|
INSERT INTO t2 VALUES (1,'aa'),(2,'t'),(3,'bb');
|
|
CREATE TABLE tmp1
|
|
SELECT v1.pk
|
|
FROM t2,v1
|
|
WHERE v1.x = t2.x
|
|
GROUP BY v1.pk
|
|
HAVING (v1.pk = 1);
|
|
DROP TABLE t1,t2,tmp1;
|
|
DROP VIEW v1;
|
|
#
|
|
# MDEV-19164: pushdown of condition with cached items
|
|
#
|
|
create table t1 (d1 date);
|
|
insert into t1 values (null),('1971-03-06'),('1993-06-05'),('1998-07-08');
|
|
select d1 from t1
|
|
group by d1
|
|
having d1 between (inet_aton('1978-04-27')) and '2018-08-26';
|
|
d1
|
|
explain extended select d1 from t1
|
|
group by d1
|
|
having d1 between (inet_aton('1978-04-27')) and '2018-08-26';
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 Using where; Using temporary; Using filesort
|
|
Warnings:
|
|
Note 1003 select `test`.`t1`.`d1` AS `d1` from `test`.`t1` where `test`.`t1`.`d1` between <cache>(inet_aton('1978-04-27')) and <cache>('2018-08-26') group by `test`.`t1`.`d1` having 1
|
|
explain format=json select d1 from t1
|
|
group by d1
|
|
having d1 between (inet_aton('1978-04-27')) and '2018-08-26';
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.d1",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 4,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.d1 between <cache>(inet_aton('1978-04-27')) and <cache>('2018-08-26')"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
delete from t1;
|
|
insert into t1 values ('2018-01-15'),('2018-02-20');
|
|
select d1 from t1
|
|
group by d1
|
|
having d1 not between 0 AND exp(0);
|
|
d1
|
|
2018-01-15
|
|
2018-02-20
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect datetime value: '1'
|
|
explain extended select d1 from t1
|
|
group by d1
|
|
having d1 not between 0 AND exp(0);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort
|
|
Warnings:
|
|
Note 1003 select `test`.`t1`.`d1` AS `d1` from `test`.`t1` where `test`.`t1`.`d1` not between <cache>(0) and <cache>(exp(0)) group by `test`.`t1`.`d1` having 1
|
|
explain format=json select d1 from t1
|
|
group by d1
|
|
having d1 not between 0 AND exp(0);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.d1",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 2,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.d1 not between <cache>(0) and <cache>(exp(0))"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
drop table t1;
|
|
#
|
|
# MDEV-19245: Impossible WHERE should be noticed earlier
|
|
# after HAVING pushdown
|
|
#
|
|
CREATE TABLE t1 (a INT, b INT, c INT);
|
|
INSERT INTO t1 VALUES (1,2,1),(3,2,2),(5,6,4),(3,4,1);
|
|
EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
|
|
WHERE t1.a > 3 GROUP BY t1.a HAVING t1.a = 3;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
|
|
WHERE t1.a = 3 GROUP BY t1.a HAVING t1.a > 3;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
|
|
WHERE t1.a > 3 AND t1.a = 3 GROUP BY t1.a ;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
|
|
WHERE (t1.a < 2 OR t1.c > 1) GROUP BY t1.a HAVING t1.a = 3;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where
|
|
EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
|
|
WHERE t1.a = 3 GROUP BY t1.a HAVING (t1.a < 2 OR t1.a > 3);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1
|
|
WHERE t1.a = 3 AND (t1.a < 2 OR t1.a > 3) GROUP BY t1.a;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1
|
|
WHERE (t1.a < 2 AND t1.c > 1) GROUP BY t1.a HAVING t1.a = 3;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1
|
|
WHERE t1.a = 3 GROUP BY t1.a HAVING (t1.a < 2 AND t1.c > 1);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1
|
|
WHERE t1.a = 3 AND (t1.a < 2 AND t1.b > 3) GROUP BY t1.a;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
DROP TABLE t1;
|
|
#
|
|
# 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": {
|
|
"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,
|
|
"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": {
|
|
"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,
|
|
"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": {
|
|
"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,
|
|
"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,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 7,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = (subquery#2)"
|
|
},
|
|
"subqueries": [
|
|
{
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"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;
|
|
#
|
|
# MDEV-19520 Extend condition normalization to include 'NOT a'
|
|
# having Item_func_not in item tree breaks assumptions during the
|
|
# optimization phase about transformation possibilities in fix_fields().
|
|
# Remove Item_func_not by extending normalization during parsing.
|
|
#
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (0),(1);
|
|
SELECT a FROM t1 GROUP BY a HAVING NOT a;
|
|
a
|
|
0
|
|
DROP TABLE t1;
|
|
End of 10.4 tests
|
|
#
|
|
# MDEV-29363: Constant subquery causing a crash in pushdown optimization
|
|
#
|
|
CREATE TABLE t1 (a INT, b INT, c INT);
|
|
INSERT INTO t1 VALUES (3, 3, 4), (NULL, NULL, 2);
|
|
EXPLAIN FORMAT=JSON SELECT a,b,c FROM t1 GROUP BY a,b,c
|
|
HAVING a = (SELECT MIN(b) AS min_b FROM t1) and (a = b or a = c);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.b, t1.c",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 2,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = (subquery#2) and (t1.b = (subquery#2) or t1.c = (subquery#2))"
|
|
},
|
|
"subqueries": [
|
|
{
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 2,
|
|
"filtered": 100
|
|
}
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
SELECT a,b,c FROM t1 GROUP BY a,b,c
|
|
HAVING a = (SELECT MIN(b) AS min_b FROM t1) and (a = b or a = c);
|
|
a b c
|
|
3 3 4
|
|
EXPLAIN FORMAT=JSON SELECT a FROM t1 GROUP BY a,b
|
|
HAVING a = (SELECT MIN(a) AS min_a FROM t1) AND (a = 3 or a > b);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"filesort": {
|
|
"sort_key": "t1.b",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 2,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = (subquery#2) and (1 or (subquery#2) > t1.b)"
|
|
},
|
|
"subqueries": [
|
|
{
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 2,
|
|
"filtered": 100
|
|
}
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
SELECT a FROM t1 GROUP BY a,b
|
|
HAVING a = (SELECT MIN(a) AS min_a FROM t1) AND (a = 3 or a > b);
|
|
a
|
|
3
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-32424: Pushdown: server crashes at JOIN::save_explain_data()
|
|
# (fixed by the patch for MDEV-29363)
|
|
#
|
|
CREATE TABLE t1 (a INT, b INT, c INT);
|
|
INSERT INTO t1 VALUES (1, 1, 3), (3, 2, 3);
|
|
SELECT a,b,c FROM t1 GROUP BY a,b,c
|
|
HAVING a = (SELECT MIN(b) AS min_b FROM t1) and a IN (b, c);
|
|
a b c
|
|
1 1 3
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-32293: Pushdown: server crashes at check_simple_equality()
|
|
# (fixed by the patch for MDEV-29363)
|
|
#
|
|
CREATE VIEW v1 AS SELECT 1 AS a;
|
|
SELECT * FROM v1 GROUP BY a HAVING a = 'b' AND a = (a IS NULL);
|
|
a
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect DECIMAL value: 'b'
|
|
DROP VIEW v1;
|
|
#
|
|
# MDEV-32304: Pushdown: server crashes at Item_field::used_tables()
|
|
# (fixed by the patch for MDEV-29363)
|
|
#
|
|
CREATE VIEW v1 AS SELECT 1 AS a;
|
|
SELECT * FROM v1
|
|
GROUP BY a HAVING a = (a IS NULL OR a IS NULL);
|
|
a
|
|
DROP VIEW v1;
|
|
#
|
|
# MDEV-32608: Expression with constant subquery causes a crash
|
|
# in pushdown from HAVING
|
|
#
|
|
CREATE TABLE t1 (a INT, b INT);
|
|
INSERT INTO t1 VALUES (2, 1), (3, 2);
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1
|
|
GROUP BY b
|
|
HAVING (SELECT MAX(b) FROM t1) = a AND a + b = 3;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t1.a = (subquery#2)",
|
|
"filesort": {
|
|
"sort_key": "t1.b",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 2,
|
|
"filtered": 100,
|
|
"attached_condition": "(subquery#2) + t1.b = 3"
|
|
},
|
|
"subqueries": [
|
|
{
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 2,
|
|
"filtered": 100
|
|
}
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
SELECT * FROM t1
|
|
GROUP BY b
|
|
HAVING (SELECT MAX(b) FROM t1) = a AND a + b = 3;
|
|
a b
|
|
2 1
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1
|
|
GROUP BY b
|
|
HAVING (SELECT MAX(b) FROM t1) = a AND a > b;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t1.a = (subquery#2)",
|
|
"filesort": {
|
|
"sort_key": "t1.b",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 2,
|
|
"filtered": 100,
|
|
"attached_condition": "(subquery#2) > t1.b"
|
|
},
|
|
"subqueries": [
|
|
{
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows": 2,
|
|
"filtered": 100
|
|
}
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
SELECT * FROM t1
|
|
GROUP BY b
|
|
HAVING (SELECT MAX(b) FROM t1) = a AND a > b;
|
|
a b
|
|
2 1
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-25084: Moving equality with constant right side
|
|
# from HAVING to WHERE
|
|
# (fixed by the patch for MDEV-26402)
|
|
#
|
|
CREATE TABLE t1 (a CHAR(3)) CHARSET=sjis;
|
|
INSERT INTO t1 VALUES ('foo'),('bar');
|
|
SELECT LOAD_FILE('') AS f, a FROM t1 GROUP BY f, a HAVING f = a;
|
|
f a
|
|
DROP TABLE t1;
|
|
End of 10.5 tests
|
|
#
|
|
# MDEV-38487: Prevent aggregate functions cloning when pushing HAVING into WHERE
|
|
#
|
|
CREATE TABLE t (c INT);
|
|
insert into t values (1), (2), (3);
|
|
explain format=JSON SELECT * FROM t GROUP BY c HAVING c=c OR TIMESTAMPADD(SECOND, c, AVG(c) IS NULL IS NULL) AND c LIKE (SELECT 1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t.c = t.c or <cache>(/*always not null*/ 1 is null) + interval t.c second and t.c like 1",
|
|
"filesort": {
|
|
"sort_key": "t.c",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t",
|
|
"access_type": "ALL",
|
|
"rows": 3,
|
|
"filtered": 100
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
SELECT * FROM t GROUP BY c HAVING c=c OR TIMESTAMPADD(SECOND, c, AVG(c) IS NULL IS NULL) AND c LIKE (SELECT 1);
|
|
c
|
|
1
|
|
2
|
|
3
|
|
explain format=JSON SELECT c FROM t GROUP BY c HAVING c > 1 AND AVG(c) > 0;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "avg(t.c) > 0",
|
|
"filesort": {
|
|
"sort_key": "t.c",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t",
|
|
"access_type": "ALL",
|
|
"rows": 3,
|
|
"filtered": 100,
|
|
"attached_condition": "t.c > 1"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
SELECT c FROM t GROUP BY c HAVING c > 1 AND AVG(c) > 0;
|
|
c
|
|
2
|
|
3
|
|
explain format=JSON SELECT c FROM t GROUP BY c HAVING c > 0 AND (c < 10 AND AVG(c) IS NOT NULL);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "avg(t.c) is not null",
|
|
"filesort": {
|
|
"sort_key": "t.c",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t",
|
|
"access_type": "ALL",
|
|
"rows": 3,
|
|
"filtered": 100,
|
|
"attached_condition": "t.c > 0 and t.c < 10"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
SELECT c FROM t GROUP BY c HAVING c > 0 AND (c < 10 AND AVG(c) IS NOT NULL);
|
|
c
|
|
1
|
|
2
|
|
3
|
|
explain format=JSON SELECT c FROM t GROUP BY c HAVING c = 1 OR AVG(c) > 2;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"having_condition": "t.c = 1 or avg(t.c) > 2",
|
|
"filesort": {
|
|
"sort_key": "t.c",
|
|
"temporary_table": {
|
|
"table": {
|
|
"table_name": "t",
|
|
"access_type": "ALL",
|
|
"rows": 3,
|
|
"filtered": 100
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
SELECT c FROM t GROUP BY c HAVING c = 1 OR AVG(c) > 2;
|
|
c
|
|
1
|
|
3
|
|
DROP TABLE t;
|
|
# End of 10.6 tests
|