|
A possible cause of the problem can be seen here:
MariaDB [test]> explain format=json
|
-> with recursive
|
-> reachables(p) as
|
-> (
|
-> select lp from t1 where lp = 'p1'
|
-> union
|
-> select t1.rp from reachables, t1
|
-> where 'p3' not in (select * from reachables) and
|
-> t1.lp = reachables.p
|
-> )
|
-> select * from reachables;
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| EXPLAIN |
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| {
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"table_name": "<derived2>",
|
"access_type": "ALL",
|
"rows": 4,
|
"filtered": 100,
|
"materialized": {
|
"query_block": {
|
"recursive_union": {
|
"table_name": "<union2,3>",
|
"access_type": "ALL",
|
"query_specifications": [
|
{
|
"query_block": {
|
"select_id": 2,
|
"table": {
|
"table_name": "t1",
|
"access_type": "ALL",
|
"rows": 4,
|
"filtered": 100,
|
"attached_condition": "t1.lp = 'p1'"
|
}
|
}
|
},
|
{
|
"query_block": {
|
"select_id": 3,
|
"const_condition": "<cache>(!<in_optimizer>('p3',<exists>(subquery#4)))",
|
"table": {
|
"table_name": "t1",
|
"access_type": "ALL",
|
"rows": 4,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "<derived2>",
|
"access_type": "ref",
|
"possible_keys": ["key0"],
|
"key": "key0",
|
"key_length": "4",
|
"used_key_parts": ["p"],
|
"ref": ["test.t1.lp"],
|
"rows": 2,
|
"filtered": 100
|
},
|
"subqueries": [
|
{
|
"query_block": {
|
"select_id": 4,
|
"table": {
|
"table_name": "<derived2>",
|
"access_type": "ALL",
|
"rows": 4,
|
"filtered": 100,
|
"attached_condition": "<cache>(convert('p3' using latin1)) = reachables.p"
|
}
|
}
|
}
|
]
|
}
|
}
|
]
|
}
|
}
|
}
|
}
|
}
|
} |
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
The predicate
'p3' not in (select * from reachables)
|
is considered as a constant condition. It is evaluated only once at the first iteration. Then its result is cached and reused by each next iteration.
|