Use a recursive CTE to generate a short integer series

By Batu · English technical notes

Use a recursive CTE whose recursive member contains WHERE n < 5 to generate the inclusive integer series 1 through 5. Without that termination predicate, the recursive member keeps producing successors. SQLite’s WITH-clause documentation describes recursive CTEs, and Python’s sqlite3 documentation documents the in-memory connection used by this standard-library example.

The naive SQL is deliberately constrained by an outer LIMIT 6 so the program can demonstrate the error safely. It returns [1, 2, 3, 4, 5, 6]: the sixth value proves that the recursive member has passed the intended stop value and would continue without the limit. The limit is only an observation tool here; it is not the requested series boundary.

The corrected statement puts WHERE n < ? inside the recursive member and supplies the stop value as a bound parameter. Its output is exactly [1, 2, 3, 4, 5]. The assertions prove these two results for this synthetic seed and stop value, not that any upper bound is suitable for production data. Parameter binding avoids constructing the stop value into SQL text. Recursive CTEs require SQLite 3.8.3 or newer; verify sqlite3.sqlite_version if a deployment may link an older SQLite library. The f-strings in this script require Python 3.6+.

AI assistance disclosure: AI assisted drafting this reproducible local-only experiment.

import sqlite3

connection = sqlite3.connect(":memory:")
seed = 1
stop = 5
naive = connection.execute(
    """
    WITH RECURSIVE series(n) AS (
        SELECT ?
        UNION ALL
        SELECT n + 1 FROM series
    )
    SELECT n FROM series LIMIT 6
    """,
    (seed,),
).fetchall()
corrected = connection.execute(
    """
    WITH RECURSIVE series(n) AS (
        SELECT ?
        UNION ALL
        SELECT n + 1 FROM series WHERE n < ?
    )
    SELECT n FROM series
    """,
    (seed, stop),
).fetchall()

assert naive == [(1,), (2,), (3,), (4,), (5,), (6,)]
assert corrected == [(1,), (2,), (3,), (4,), (5,)]
print("schema: series(n INTEGER)")
print(f"seed: {seed}; stop: {stop}")
print(f"naive no-termination result: {[n for (n,) in naive]}")
print(f"corrected bounded result: {[n for (n,) in corrected]}")

schema: series(n INTEGER)
seed: 1; stop: 5
naive no-termination result: [1, 2, 3, 4, 5, 6]
corrected bounded result: [1, 2, 3, 4, 5]