Details
Major Description
create table t1 (a int, b int); |
create table t2 (x int, y int); |
|
insert into t1 values (1,1),(2,2); |
insert into t2 values (1,1),(2,2),(2,3); |
|
prepare stmt from " |
EXPLAIN FORMAT=JSON
|
SELECT * FROM t1
|
WHERE a = b
|
AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE @a=1 GROUP BY t2.x);"; |
|
|
set @a=2; |
execute stmt; |
set @a=1; |
execute stmt; |
MariaDB [test]> prepare stmt from "
|
EXPLAIN FORMAT=JSON SELECT * FROM t1
|
WHERE a = b
|
AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE @a=1 GROUP BY t2.x);";
|
Query OK, 0 rows affected (0.023 sec)
|
Statement prepared
|
|
|
MariaDB [test]> set @a=2;
|
Query OK, 0 rows affected (0.001 sec)
|
|
|
MariaDB [test]> execute stmt;
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| EXPLAIN |
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| {
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"message": "Impossible WHERE noticed after reading const tables"
|
},
|
"subqueries": [
|
{
|
"query_block": {
|
"select_id": 2,
|
"table": {
|
"message": "Impossible WHERE"
|
}
|
}
|
}
|
]
|
}
|
} |
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (2.257 sec)
|
|
|
MariaDB [test]> set @a=1;
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [test]> execute stmt;
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| EXPLAIN |
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| {
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"table_name": "t1",
|
"access_type": "ALL",
|
"rows": 4,
|
"filtered": 100,
|
"attached_condition": "t1.b = t1.a and t1.a is not null and t1.a is not null"
|
},
|
"table": {
|
"table_name": "<subquery2>",
|
"access_type": "eq_ref",
|
"possible_keys": ["distinct_key"],
|
"key": "distinct_key",
|
"key_length": "12",
|
"used_key_parts": ["x", "COUNT(t2.y)"],
|
"ref": ["test.t1.a", "test.t1.a"],
|
"rows": 1,
|
"filtered": 100,
|
"attached_condition": "t1.a = `<subquery2>`.`COUNT(t2.y)`",
|
"materialized": {
|
"unique": 1,
|
"query_block": {
|
"select_id": 2,
|
"temporary_table": {
|
"table": {
|
"table_name": "t2",
|
"access_type": "ALL",
|
"rows": 6,
|
"filtered": 100
|
}
|
}
|
}
|
}
|
}
|
}
|
} |
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.166 sec)
|
For the second execution pushdown into IN subquery is not made while it should be made. Condition should be pushed down into HAVING.
How it looks if to run stmt with @a=1 in the first execution:
MariaDB [test]> execute stmt;
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| EXPLAIN |
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| {
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"table_name": "t1",
|
"access_type": "ALL",
|
"rows": 4,
|
"filtered": 100,
|
"attached_condition": "t1.b = t1.a and t1.a is not null and t1.a is not null"
|
},
|
"table": {
|
"table_name": "<subquery2>",
|
"access_type": "eq_ref",
|
"possible_keys": ["distinct_key"],
|
"key": "distinct_key",
|
"key_length": "12",
|
"used_key_parts": ["x", "COUNT(t2.y)"],
|
"ref": ["test.t1.a", "test.t1.a"],
|
"rows": 1,
|
"filtered": 100,
|
"attached_condition": "t1.a = `<subquery2>`.`COUNT(t2.y)`",
|
"materialized": {
|
"unique": 1,
|
"query_block": {
|
"select_id": 2,
|
"having_condition": "`COUNT(t2.y)` = t2.x",
|
"temporary_table": {
|
"table": {
|
"table_name": "t2",
|
"access_type": "ALL",
|
"rows": 6,
|
"filtered": 100
|
}
|
}
|
}
|
}
|
}
|
}
|
} |
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (4.812 sec)
|