I'm got an adjacency list 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 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?
WHERE recursive.child_id = cte.child_id
should beWHERE recursive.parent_id = cte.child_id
Unrelated, but: the parentheses around the individual selects are useless. – a_horse_with_no_name Oct 6 '14 at 6:02General error: "java.lang.IndexOutOfBoundsException: Index: 0, Size: 0"
– a_horse_with_no_name Oct 6 '14 at 6:21