Details
Major
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).
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Where is the query?
The query is
analyze format=json update t1 set b=(select max(a) from t0 where a< t1.b) where t1.a<5 \G
(ping Oleksandr Byelkin).
Because the transformer run only for selects.
Oleksandr Byelkin, any ideas why the subquery is not using subquery cache in this example?