Why does a recursive reachability report repeat the same downstream node?

Prepare the report input

Original report lists each node reachable from A once, excluding A itself. The base diamond reaches D through B and C. Recursive UNION ALL enqueues both paths; UNION over the node-only row suppresses repeated nodes.

Every record and report rule below is original and fictional. Reproduce the setup in a fresh SQLite test database; input previews include every table in this small fixture.

CREATE TABLE edges(source TEXT NOT NULL,target TEXT NOT NULL,PRIMARY KEY(source,target)); INSERT INTO edges VALUES('A','B'),('A','C'),('B','D'),('C','D');

Compare misleading and corrected results

WITH RECURSIVE reached(node) AS (VALUES('A') UNION ALL SELECT e.target FROM edges e JOIN reached r ON e.source=r.node) SELECT node FROM reached WHERE node<>'A' ORDER BY node;

WITH RECURSIVE reached(node) AS (VALUES('A') UNION SELECT e.target FROM edges e JOIN reached r ON e.source=r.node) SELECT node FROM reached WHERE node<>'A' ORDER BY node;

Bad list is B,C,D,D; corrected list B,C,D. Node-only rows are essential: adding path/depth columns would change duplicate equality and needs another traversal model.

Check a fresh boundary case

Add D→A in a fresh boundary copy and run only the corrected UNION query. It terminates with B,C,D. The misleading UNION ALL query is for the acyclic base only.

Recreate the original setup in another fresh database, then apply the change and corrected query below. For an empty result, the table displays a labelled empty-result marker rather than a data record.

INSERT INTO edges VALUES('D','A');
WITH RECURSIVE reached(node) AS (VALUES('A') UNION SELECT e.target FROM edges e JOIN reached r ON e.source=r.node) SELECT node FROM reached WHERE node<>'A' ORDER BY node;