|
With the following tables
create table value_nodes (v char(4));
|
insert into value_nodes values
|
('v1'), ('v2'), ('v3'), ('v4'), ('v5'), ('v6'), ('v7'), ('v8'), ('v9'),
|
('v10'), ('v11'), ('v12'), ('v13'), ('v14'), ('v15'), ('v16');
|
create table module_nodes(m char(4));
|
insert into module_nodes values
|
('m1'), ('m2'), ('m3'), ('m4'), ('m5'), ('m6'), ('m7');
|
create table module_arguments(m char(4), v char(4));
|
insert into module_arguments values
|
('m1','v3'), ('m1','v9'),
|
('m2','v4'), ('m2','v3'), ('m2','v7'),
|
('m3','v6'),
|
('m4','v4'), ('m4','v1'),
|
('m5','v10'), ('m5','v8'), ('m5','v3'),
|
('m6','v8'), ('m6','v1'),
|
('m7','v11'), ('m7','v12');
|
create table module_results(m char(4), v char(4));
|
insert into module_results values
|
('m1','v4'),
|
('m2','v1'), ('m2','v6'),
|
('m3','v10'),
|
('m4','v8'),
|
('m5','v11'), ('m5','v9'),
|
('m6','v12'), ('m6','v4'),
|
('m7','v2');
|
and with
set standard_compliant_cte=0
|
EXPLAIN for the query
with recursive
|
reached_values as
|
(
|
select v from value_nodes where v in ('v3','v7','v9')
|
union
|
select module_results.v from module_results, applied_modules
|
where module_results.m = applied_modules.m
|
),
|
applied_modules as
|
(
|
select * from module_nodes where 1=0
|
union
|
select module_nodes.m
|
from
|
module_nodes
|
left join
|
(
|
module_arguments
|
left join
|
reached_values
|
on module_arguments.v = reached_values.v
|
)
|
on reached_values.v is null and
|
module_nodes.m = module_arguments.m
|
where module_arguments.m is null
|
)
|
select * from reached_values;
|
returns
+------+-----------------+------------------+------+---------------+------+---------+-------------------------+------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-----------------+------------------+------+---------------+------+---------+-------------------------+------+-------------------------------------------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 16 | |
|
| 4 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
|
| 5 | RECURSIVE UNION | module_nodes | ALL | NULL | NULL | NULL | NULL | 7 | |
|
| 5 | RECURSIVE UNION | module_arguments | ALL | NULL | NULL | NULL | NULL | 15 | Using where |
|
| 5 | RECURSIVE UNION | <derived2> | ref | key0 | key0 | 5 | test.module_arguments.v | 2 | Using where |
|
| NULL | UNION RESULT | <union4,5> | ALL | NULL | NULL | NULL | NULL | NULL | |
|
| 2 | SUBQUERY | value_nodes | ALL | NULL | NULL | NULL | NULL | 16 | Using where |
|
| 3 | RECURSIVE UNION | <derived4> | ALL | NULL | NULL | NULL | NULL | 2 | |
|
| 3 | RECURSIVE UNION | module_results | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using join buffer (flat, BNL join) |
|
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | |
|
+------+-----------------+------------------+------+---------------+------+---------+-------------------------+------+-------------------------------------------------+
|
Yet in EXPLAIN FORMAT=JSON some steps of the above execution plan
are missing:
{
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"table_name": "<derived2>",
|
"access_type": "ALL",
|
"rows": 16,
|
"filtered": 100,
|
"materialized": {
|
"query_block": {
|
"recursive_union": {
|
"table_name": "<union2,3>",
|
"access_type": "ALL",
|
"query_specifications": [
|
{
|
"query_block": {
|
"select_id": 2,
|
"table": {
|
"table_name": "value_nodes",
|
"access_type": "ALL",
|
"rows": 16,
|
"filtered": 100,
|
"attached_condition": "value_nodes.v in ('v3','v7','v9')"
|
}
|
}
|
},
|
{
|
"query_block": {
|
"select_id": 3,
|
"table": {
|
"table_name": "<derived4>",
|
"access_type": "ALL",
|
"rows": 2,
|
"filtered": 100
|
},
|
"block-nl-join": {
|
"table": {
|
"table_name": "module_results",
|
"access_type": "ALL",
|
"rows": 10,
|
"filtered": 100
|
},
|
"buffer_type": "flat",
|
"buffer_size": "256Kb",
|
"join_type": "BNL",
|
"attached_condition": "module_results.m = applied_modules.m"
|
}
|
}
|
}
|
]
|
}
|
}
|
}
|
}
|
}
|
} |
|
|