CTE returns an empty set even though anchor is non-empty

4 votes

1 answer

1925 views

                          I'm got an [adjacency list](https://stackoverflow.com/q/4048151/14731)  consisting of two tables:

    CREATE TABLE permission (id SMALLINT AUTO_INCREMENT(-32768, 1) PRIMARY KEY);
    CREATE TABLE permission_graph (parent_id SMALLINT NOT NULL, child_id SMALLINT NOT NULL,
    	UNIQUE KEY (parent_id, child_id),
        FOREIGN KEY (parent_id) REFERENCES permission(id) ON DELETE CASCADE,
    	FOREIGN KEY (child_id) REFERENCES permission(id) ON DELETE CASCADE);

When I run the following [CTE](http://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL#Common_table_expression)  I get an empty set:

    WITH RECURSIVE cte (parent_id, child_id)
    AS
    (
      (
        SELECT anchor.parent_id, anchor.child_id
        FROM permission_graph anchor
        WHERE anchor.child_id = -32763
      )
      UNION ALL
      (
        SELECT recursive.parent_id, recursive.child_id
        FROM cte, permission_graph recursive
        WHERE recursive.child_id = cte.child_id
      )
    )
    SELECT cte.parent_id, cte.child_id
    FROM cte

But if I run the anchor condition:

    SELECT anchor.parent_id, anchor.child_id
    FROM permission_graph anchor
    WHERE anchor.child_id = -32763

I get:

    [parent_id = -32767, child_id = -32763]
    [parent_id = -32768, child_id = -32763]

**Why is the CTE returning an empty set when the anchor result is non-empty?** Shouldn't the CTE result contain the anchor result?

                        

Asked by Gili (1059 rep)

Oct 6, 2014, 04:38 AM
Last activity: Oct 6, 2014, 01:52 PM

CTE returns an empty set even though anchor is non-empty

Related Questions