mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
6306 lines
157 KiB
Text
6306 lines
157 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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.a > 2 and max(t1.b) > 13 or t1.a < 3 and min(t1.c) > 1",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.a > 2 and max(t1.b) > 13 or t1.a < 3 and min(t1.c) > 1",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "max(t1.a) < 3",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "max(t1.a) < 3",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "max(t1.b) > 13",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "max(t1.b) > 13",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "max(t1.a) = 3",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "max(t1.a) = 3",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "max(t1.b) > 12",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "max(t1.b) > 12",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "max(t1.b) = 13",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "max(t1.b) = 13",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "min(t1.c) < 3",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "min(t1.c) < 3",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "max(t1.b) = 13 and min(t1.c) = 2",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "max(t1.b) = 13 and min(t1.c) = 2",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.a = test.f1()",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.a = test.f1()",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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 1
|
|
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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "v1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 4,
|
|
"cost": "COST_REPLACED",
|
|
"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"],
|
|
"loops": 4,
|
|
"rows": 1,
|
|
"cost": "COST_REPLACED",
|
|
"filtered": 100,
|
|
"materialized": {
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "v1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 4,
|
|
"cost": "COST_REPLACED",
|
|
"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"],
|
|
"loops": 4,
|
|
"rows": 1,
|
|
"cost": "COST_REPLACED",
|
|
"filtered": 100,
|
|
"materialized": {
|
|
"query_block": {
|
|
"select_id": 3,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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 1 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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "v1.c",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 4,
|
|
"cost": "COST_REPLACED",
|
|
"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"],
|
|
"loops": 4,
|
|
"rows": 1,
|
|
"cost": "COST_REPLACED",
|
|
"filtered": 100,
|
|
"attached_condition": "v1.c > 2",
|
|
"materialized": {
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.c > 2",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "v1.c",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 4,
|
|
"cost": "COST_REPLACED",
|
|
"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"],
|
|
"loops": 4,
|
|
"rows": 1,
|
|
"cost": "COST_REPLACED",
|
|
"filtered": 100,
|
|
"attached_condition": "v1.c > 2",
|
|
"materialized": {
|
|
"query_block": {
|
|
"select_id": 3,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.c > 2",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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"],
|
|
"loops": 5,
|
|
"rows": 1,
|
|
"cost": "COST_REPLACED",
|
|
"filtered": 100,
|
|
"materialized": {
|
|
"unique": 1,
|
|
"materialization": {
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost": "COST_REPLACED",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 4,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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"],
|
|
"loops": 5,
|
|
"rows": 1,
|
|
"cost": "COST_REPLACED",
|
|
"filtered": 100,
|
|
"materialized": {
|
|
"unique": 1,
|
|
"materialization": {
|
|
"query_block": {
|
|
"select_id": 3,
|
|
"cost": "COST_REPLACED",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 4,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.b",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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"],
|
|
"loops": 5,
|
|
"rows": 1,
|
|
"cost": "COST_REPLACED",
|
|
"filtered": 100,
|
|
"materialized": {
|
|
"unique": 1,
|
|
"materialization": {
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "`MAX(t2.y)` < 14",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 4,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.b",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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"],
|
|
"loops": 5,
|
|
"rows": 1,
|
|
"cost": "COST_REPLACED",
|
|
"filtered": 100,
|
|
"materialized": {
|
|
"unique": 1,
|
|
"materialization": {
|
|
"query_block": {
|
|
"select_id": 3,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "`MAX(t2.y)` < 14",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 4,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.c = 2",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.c = 2",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.a = 2 and t1.c = 2",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.a = 2 and t1.c = 2",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.c = t1.a and t1.a > 1 or t1.a < 3 and t1.c > 3",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.c = t1.a and t1.a > 1 or t1.a < 3 and t1.c > 3",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"filtered": 100,
|
|
"attached_condition": "t1.c = t1.a and t1.a > 1 or t1.a < 3"
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
# conjunctive 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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.a > 1 and max(t1.c) < 3 or t1.c < 4",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.a > 1 and max(t1.c) < 3 or t1.c < 4",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.a > 1 and max(t1.c) < 3 or t1.c < 4",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.a > 1 and max(t1.c) < 3 or t1.c < 4",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.b = 13 and max(t1.c) > 2",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.b = 13 and max(t1.c) > 2",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.b",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.b",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.b",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.b",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.b",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.b",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.b",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.b",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.b",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.b",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.b",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.b",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.a < 2 and max(t1.c) = 2 or max(t1.c) > 2 and (t1.a = 1 or t1.a = 2)",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.a < 2 and max(t1.c) = 2 or max(t1.c) > 2 and (t1.a = 1 or t1.a = 2)",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.a = 2 and max(t1.c) = 2 or max(t1.c) > 2 and (t1.a = 1 or t1.a = 2)",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.a = 2 and max(t1.c) = 2 or max(t1.c) > 2 and (t1.a = 1 or t1.a = 2)",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "max(t1.c) = 3",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "max(t1.c) = 3",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "max(t1.b = 14)",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "max(t1.b) = 14",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "max(t1.b = 14)",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "max(t1.b) = 14",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"const_condition": "1",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 3,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 3,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"const_condition": "1 and 1",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"const_condition": "1",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.c > 1",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.c > 1",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"const_condition": "1",
|
|
"filesort": {
|
|
"sort_key": "t1.c",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.c",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"const_condition": "1 and 1",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t3.d > 0",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 3,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t3.d > 0",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 3,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"const_condition": "1",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 3,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 3,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.c > 1",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.c > 1",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.c",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.c",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.c",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.c",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 5,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t3.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 3,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t3.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 3,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t3.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 3,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t3.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 3,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.d1",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 4,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.d1",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 2,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 7,
|
|
"cost": "COST_REPLACED",
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 8 or t1.a = (subquery#2)"
|
|
}
|
|
}
|
|
],
|
|
"subqueries": [
|
|
{
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 2,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.a, t1.b",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 7,
|
|
"cost": "COST_REPLACED",
|
|
"filtered": 100,
|
|
"attached_condition": "(t1.a = 8 or t1.a = (subquery#2)) and t1.b < 20"
|
|
}
|
|
}
|
|
],
|
|
"subqueries": [
|
|
{
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 2,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "sum(t1.b) > 20",
|
|
"filesort": {
|
|
"sort_key": "t1.a",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 7,
|
|
"cost": "COST_REPLACED",
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = 8 or t1.a = (subquery#2)"
|
|
}
|
|
}
|
|
],
|
|
"subqueries": [
|
|
{
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 2,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 7,
|
|
"cost": "COST_REPLACED",
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = (subquery#2)"
|
|
}
|
|
}
|
|
],
|
|
"subqueries": [
|
|
{
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 2,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.b, t1.c",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 2,
|
|
"cost": "COST_REPLACED",
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = (subquery#2) and (t1.b = (subquery#2) or t1.c = (subquery#2))"
|
|
}
|
|
}
|
|
],
|
|
"subqueries": [
|
|
{
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 2,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"filesort": {
|
|
"sort_key": "t1.b",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 2,
|
|
"cost": "COST_REPLACED",
|
|
"filtered": 100,
|
|
"attached_condition": "t1.a = (subquery#2) and (1 or (subquery#2) > t1.b)"
|
|
}
|
|
}
|
|
],
|
|
"subqueries": [
|
|
{
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 2,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.a = (subquery#2)",
|
|
"filesort": {
|
|
"sort_key": "t1.b",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 2,
|
|
"cost": "COST_REPLACED",
|
|
"filtered": 100,
|
|
"attached_condition": "(subquery#2) + t1.b = 3"
|
|
}
|
|
}
|
|
],
|
|
"subqueries": [
|
|
{
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 2,
|
|
"cost": "COST_REPLACED",
|
|
"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,
|
|
"cost": "COST_REPLACED",
|
|
"having_condition": "t1.a = (subquery#2)",
|
|
"filesort": {
|
|
"sort_key": "t1.b",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 2,
|
|
"cost": "COST_REPLACED",
|
|
"filtered": 100,
|
|
"attached_condition": "(subquery#2) > t1.b"
|
|
}
|
|
}
|
|
],
|
|
"subqueries": [
|
|
{
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost": "COST_REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"rows": 2,
|
|
"cost": "COST_REPLACED",
|
|
"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
|