Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.5
-
None
Description
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);
|
insert into t1 values
|
('p1','p2'), ('p2','p3'), ('p3','p4'), ('p4','p5');
|
|
|
set standard_compliant_cte=0;
|
|
|
with recursive
|
reachables(p) as
|
(
|
select lp from t1 where lp = 'p1'
|
union
|
select t1.rp from reachables, t1
|
where t1.lp = reachables.p
|
)
|
select * from reachables;
|
|
|
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;
|
While for the first query the result set is correct:
MariaDB [test]> with recursive
|
-> reachables(p) as
|
-> (
|
-> select lp from t1 where lp = 'p1'
|
-> union
|
-> select t1.rp from reachables, t1
|
-> where t1.lp = reachables.p
|
-> )
|
-> select * from reachables;
|
+----+
|
| p |
|
+----+
|
| p1 |
|
| p2 |
|
| p3 |
|
| p4 |
|
| p5 |
|
+----+
|
the output from the second query is not what is expected:
MariaDB [test]> 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;
|
+----+
|
| p |
|
+----+
|
| p1 |
|
| p2 |
|
| p3 |
|
| p4 |
|
| p5 |
|
+----+
|
Expected:
MariaDB [test]> 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;
|
+----+
|
| p |
|
+----+
|
| p1 |
|
| p2 |
|
| p3 |
|
+----+
|