If in a CTE specification the WHERE condition of a recursive SELECT contains NOT IN subquery predicate with recursive reference then this predicate is completely ignored when the query that uses this CTE is executed.
This problem can be demonstrated with the following simple test case.
create table t1 (lp char(4) not null, rp char(4) not null);
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.
Igor Babaev
added a comment - 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.
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.