递归查询已经用了 UNION，为什么还是绕着同一圈跑？

"""All recursive queries are finite. The depth counterexample has an inner LIMIT."""
from contextlib import closing
import sqlite3


NODES = """
WITH RECURSIVE reach(node) AS (
  VALUES(1)
  UNION
  SELECT edge.dst FROM edge JOIN reach ON edge.src = reach.node
)
SELECT node FROM reach ORDER BY node
"""

DEPTH_LIMITED = """
WITH RECURSIVE reach(node, depth) AS (
  VALUES(1, 0)
  UNION
  SELECT edge.dst, reach.depth + 1
  FROM edge JOIN reach ON edge.src = reach.node
  LIMIT 8
)
SELECT node, depth FROM reach ORDER BY depth, node
"""

PATH_SAFE = """
WITH RECURSIVE walk(node, depth, path) AS (
  VALUES(1, 0, ',1,')
  UNION ALL
  SELECT edge.dst, walk.depth + 1, walk.path || edge.dst || ','
  FROM edge JOIN walk ON edge.src = walk.node
  WHERE instr(walk.path, ',' || edge.dst || ',') = 0
)
SELECT node, depth, path FROM walk ORDER BY depth, node, path
"""


def main():
    checks = 0

    def check(condition, message):
        nonlocal checks
        if not condition:
            raise AssertionError(message)
        checks += 1

    with closing(sqlite3.connect(":memory:", isolation_level=None)) as db:
        db.execute("CREATE TABLE edge(src INTEGER NOT NULL, dst INTEGER NOT NULL, PRIMARY KEY(src,dst))")
        db.executemany("INSERT INTO edge VALUES(?,?)", [(1, 2), (2, 3), (3, 1)])
        nodes = [row[0] for row in db.execute(NODES)]
        depth_rows = db.execute(DEPTH_LIMITED).fetchall()
        paths = db.execute(PATH_SAFE).fetchall()
        check(nodes == [1, 2, 3], "node-only UNION should terminate with three identities")
        check(depth_rows == [(i % 3 + 1, i) for i in range(8)], "depth makes each row different")
        check(paths == [(1, 0, ',1,'), (2, 1, ',1,2,'), (3, 2, ',1,2,3,')], "path prevents revisits")
        print(f"cycle_node_only={nodes}")
        print(f"cycle_with_depth_limited={depth_rows}")
        print(f"cycle_path_safe={paths}")

        # Different paths to one node are legitimate; this is not global dedup.
        db.execute("DELETE FROM edge")
        db.executemany("INSERT INTO edge VALUES(?,?)", [(1, 2), (1, 3), (2, 4), (3, 4)])
        diamond = db.execute(PATH_SAFE).fetchall()
        paths_to_four = [row[2] for row in diamond if row[0] == 4]
        check(paths_to_four == [',1,2,4,', ',1,3,4,'], "two routes must remain distinguishable")
        print(f"diamond_paths_to_4={paths_to_four}")

        # Delimiters prevent node 1 from being confused with node 11.
        db.execute("DELETE FROM edge")
        db.executemany("INSERT INTO edge VALUES(?,?)", [(1, 11), (11, 1)])
        delimiter_rows = db.execute(PATH_SAFE).fetchall()
        check([r[0] for r in delimiter_rows] == [1, 11], "whole-token path membership")
        print(f"delimiter_nodes={[r[0] for r in delimiter_rows]}")
    print(f"PASS checks={checks}")


if __name__ == "__main__":
    main()