SQLite Recursive Queries Stop Repeating Only When Their State Stops Changing

A dependency table contains a cycle: A points to B, and B points back to A. A recursive query uses UNION, so the developer expects duplicate removal to stop the loop. Then they add a depth column for debugging, and the query keeps finding new rows.

The explanation is in the state being compared. A repeated node is not necessarily a repeated result row. (A, 0) and (A, 2) are different rows, even though both describe the same node.

This evergreen guide, checked on 31 October 2026, uses a tiny original directed graph to separate reachability, bounded walks and incomplete output. Its Python standard-library demonstration was executed with Python 3.12.14 and SQLite 3.53.1. Every recursive query below has either a finite deduplicated state space or an explicit bound.

Choose the result's unit before writing recursion

“Find all dependencies” can mean all reachable nodes, every path, the first path discovered or a shortest path. These are different outputs and can have very different sizes.

For our first task, the output unit is a distinct node reachable by following directed edges from A, including A itself. For the second, it is a pair consisting of a node and a walk length no greater than four. A walk may revisit a node.

The table is an original modeling aid. Selecting the state deliberately is more useful than choosing UNION solely because it sounds safer.

Understand what SQLite deduplicates

The SQLite WITH-clause reference describes recursive CTE processing and duplicate handling. UNION prevents an identical row from being added again, while UNION ALL allows repeats. The comparison concerns the complete row carried by the CTE.

For a finite graph with a node-only state, there are only finitely many distinct node identifiers to admit. Add a continually increasing depth, and that argument no longer applies. A row can become new solely because its depth changed.

That is a logical property of this representation, not a claim that UNION is defective. The operator is doing exactly the comparison the query requested.

Execute a bounded three-node example

The Python sqlite3 reference provides the connection and parameter-binding interface used here. The in-memory fixture has three original edges and no external files. The connection is closed explicitly.

import sqlite3

db = sqlite3.connect(":memory:")
try:
    db.execute("CREATE TABLE edge(src TEXT NOT NULL, dst TEXT NOT NULL)")
    db.executemany("INSERT INTO edge VALUES (?, ?)",
                   [("A", "B"), ("B", "A"), ("B", "C")])

    reachable = db.execute("""
        WITH RECURSIVE reach(node) AS (
            SELECT ?
            UNION
            SELECT edge.dst FROM edge JOIN reach ON edge.src = reach.node
        )
        SELECT node FROM reach ORDER BY node
    """, ("A",)).fetchall()
    assert reachable == [("A",), ("B",), ("C",)]

    walks = db.execute("""
        WITH RECURSIVE walk(node, depth) AS (
            SELECT ?, 0
            UNION
            SELECT edge.dst, walk.depth + 1
            FROM edge JOIN walk ON edge.src = walk.node
            WHERE walk.depth < ?
        )
        SELECT node, depth FROM walk ORDER BY depth, node
    """, ("A", 4)).fetchall()
    assert walks == [("A", 0), ("B", 1), ("A", 2), ("C", 2),
                     ("B", 3), ("A", 4), ("C", 4)]

    capped = db.execute("""
        WITH RECURSIVE reach(node) AS (
            SELECT ?
            UNION
            SELECT edge.dst FROM edge JOIN reach ON edge.src = reach.node
            LIMIT 2
        )
        SELECT node FROM reach ORDER BY node
    """, ("A",)).fetchall()
    assert capped == [("A",), ("B",)]
finally:
    db.close()

print("Three reachable nodes; seven depth-bounded states; capped result is partial")

The first query reaches C through B. The second also records returning to A at depths two and four. The third deliberately stops after admitting two rows, so it omits reachable C. A successfully completed query can therefore still produce an intentionally partial result.

Keep walk depth separate from shortest distance

The second query reports A at depths zero, two and four. Those are lengths of admitted walks, not three different shortest distances. A's shortest distance from the starting A is zero in this example.

If the application needs minimum distance, first specify whether edges have equal cost and whether a bounded search can establish the answer. Taking a minimum over truncated results can conceal an undiscovered route or an omitted node. Weighted shortest paths need an appropriate algorithm and stopping argument beyond this example.

Names in the output should preserve the distinction. Labeling the column depth does not turn an arbitrary recursive traversal into a shortest-path solver.

Make every bound's meaning visible

The depth guard admits transitions while the current depth is below four. It allows states at depth four but does not expand them. This bounds walk length; it is not a direct bound on the total number of states in a highly branching graph.

The SQLite reference also defines a recursive CTE's internal LIMIT as a cap on rows added to the recursive table. That differs from merely limiting rows displayed by the outer query. A result cap should be treated as a completeness constraint when the question asks for all reachable nodes.

In a real interface, report that the budget was reached and that completeness is unresolved. Reaching a limit does not prove the remaining graph is empty. Conversely, a budget not reached is not enough evidence if another stopping condition also truncated the search.

Control result order independently

The examples use an outer ORDER BY so assertions compare a declared output order. Do not rely on a traversal's accidental presentation order to define a business priority.

Likewise, “the first two results” is incomplete without specifying the relevant ordering and selection semantics. If an operational task needs all affected customers or permissions, a convenient display limit is a poor substitute for a complete query.

For ordinary report ordering, keep the business sort rule and tie-breaker explicit. For a graph search policy, examine which states are admitted and expanded rather than inferring the algorithm from the order a client happens to display.

Test graph shape and identity assumptions

Useful fixture variations include an isolated starting node, a self-loop, a cycle, duplicate edges, a diamond with two routes to one destination and a branch beyond the chosen depth bound. State whether the starting identifier counts when it does not appear in the edge table.

This fixture admits the starting value through its seed even when it is absent from the table. That is an explicit modeling choice. An application requiring registered nodes should validate against its node inventory before seeding the traversal.

The join-cardinality guide covers another representation issue: multiplying rows can alter the meaning of a count before recursion is even considered. Define the edge and result units, then test the graph shapes that challenge those definitions.

Sources and scope

• SQLite, The WITH Clause, checked 31 October 2026: recursive row processing, duplicate comparisons and internal-limit semantics.
• Python 3.12 sqlite3 reference, checked 31 October 2026: connection, execution and parameter binding.

The graph, queries and expected outputs are original. No execution-time guarantee, production graph capacity, shortest-path implementation or general graph-security policy is established by this small fixture.