Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.1(EOL)
Description
Current EXPLAIN FORMAT=JSON output doesn't make it clear whether the subquery is correlated or not.
create table t0 (a int);
|
INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
create table t1 (a int, b int);
|
insert into t1 select a,a from t0;
|
MariaDB produces:
EXPLAIN: {
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"update": 1,
|
"table_name": "t1",
|
"access_type": "ALL",
|
"rows": 10,
|
"attached_condition": "(t1.a < 5)"
|
},
|
"subqueries": [
|
{
|
"query_block": {
|
"select_id": 2,
|
"table": {
|
"table_name": "t0",
|
"access_type": "ALL",
|
"rows": 10,
|
"filtered": 100,
|
"attached_condition": "(t0.a < t1.b)"
|
}
|
}
|
}
|
]
|
}
|
}
|
MySQL produces:
EXPLAIN: {
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"update": true,
|
"table_name": "t1",
|
"access_type": "ALL",
|
"rows": 10,
|
"filtered": 100,
|
"attached_condition": "(`test`.`t1`.`a` < 5)"
|
},
|
"update_value_subqueries": [
|
{
|
"dependent": true,
|
"cacheable": false,
|
"query_block": {
|
"select_id": 2,
|
"table": {
|
"table_name": "t0",
|
"access_type": "ALL",
|
"rows": 10,
|
"filtered": 100,
|
"attached_condition": "(`test`.`t0`.`a` < `test`.`t1`.`b`)"
|
}
|
}
|
}
|
]
|
}
|
}
|
Note the
"dependent": true,
|
"cacheable": false,
|
these two look weird (does dependent == !cacheable always ?), but not being able to tell between correlated and uncorrelated subquery at all is not a solution.
I think there are two possible options (correlated/non-correlated).