bug: CTE with WHERE clause does not work

[rad-pat]

Search before asking

• I had searched in the issues and found no similar issues.

Version

v1.2.670-nightly

What's Wrong?

Query table with recursive CTE and where clause, get wrong result.

How to Reproduce?

CREATE TABLE parent_child 
(
  parent VARCHAR(30),
  child VARCHAR(30)
);
INSERT INTO parent_child 
VALUES ('Org','Org'),('Org','Global'),('Global','North'),
('Global','South'),('Global','East'),('Global','West'),
('Global','Org detail'),('North','North East'),('North','North West');

WITH RECURSIVE tree_values
    (parent, child)
AS (
    
    SELECT parent, child
    FROM parent_child
    WHERE parent = child
        
    UNION ALL
    
    SELECT c.parent, c.child
    FROM parent_child c
    INNER JOIN tree_values p
    ON p.child = c.parent        
    WHERE c.parent != c.child
)
select parent, child from tree_values
where parent = 'Global'

Expect 5 rows, actually returns no rows. Remove where clause, works as expected

Are you willing to submit PR?

• Yes I am willing to submit a PR!