Skip to content

search_sql() exhausts iterator columns before building the outer projection #942

Open

Description

@pengpengyi92

Table.search_sql(columns=...) accepts Iterable[str], but a one-shot iterator is consumed while constructing the CTE projection and is then empty when constructing the outer projection. The resulting SQL has an empty SELECT list and raises sqlite3.OperationalError: near "from": syntax error.

Reproduction on the current main (9c66bafa52ae23d40499c74b0db3b635bcddb876), sqlite-utils 4.2.1, Python 3.12.13 / SQLite 3.53.1:

from sqlite_utils import Database

db = Database(memory=True)
try:
    table = db["books"]
    table.insert({"title": "sqlite handbook", "author": "A"})
    table.enable_fts(["title"], fts_version="FTS5")

    sql = table.search_sql(columns=["title"])
    print(list(db.query(sql, {"query": "sqlite"})))
    # [{'title': 'sqlite handbook'}]

    sql = table.search_sql(columns=iter(["title"]))
    print(sql)
    print(list(db.query(sql, {"query": "sqlite"})))
    # OperationalError: near "from": syntax error
finally:
    db.conn.close()

The same failure occurs with FTS4, generators, and table.search(..., columns=iter(["title"])). The index is enabled with a list, so this is a separate path from #902's enable_fts issue. A list or tuple projection works.

Materializing the column iterable once inside search_sql would allow both projections to reuse it. Empty iterables should follow the existing empty-list behavior (all columns).

AI assistance: reproduced with OpenAI Codex against real in-memory SQLite; no maintainer acceptance or hosted CI result is claimed.

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions