Weird iteration behavior

[aplavin]

Create an empty DB:

julia> using SQLite, DBInterface
julia> db = SQLite.DB(":memory:")
SQLite.DB(":memory:")

Taking the first element of query results works:

julia> DBInterface.execute(db, "select 123 as x") |> first
SQLite.Row: (x = 123,)

Explicit iteration also works:

julia> for r in DBInterface.execute(db, "select 123 as x")
           @show r
       end
r = SQLite.Row:
 :x  123

But other related functions return results that make no sense.
E.g., collect() and only() return missings:

julia> DBInterface.execute(db, "select 123 as x") |> collect
1-element Vector{SQLite.Row}:
 SQLite.Row: (x = missing,)

julia> DBInterface.execute(db, "select 123 as x") |> only
SQLite.Row: (x = missing,)

[ulinares]

Same behavior here, any solutions?

[quinnj]

This behavior is mentioned in the docs:

Note that the returned result row iterator only supports a single-pass, forward-only iteration of the result rows. Calling SQLite.reset!(result) will re-execute the query and reset the iterator back to the beginning.

but maybe we can make it more clear. In short, you're only allowed to iterate each row once, and each row is only valid when it's currently being iterated. Once you iterate to the next row, any previous row "objects" are invalid. That's why collect doesn't work, because the rows become invalid. If you convert each row to something that "saves" the values, then that will work, like:

rows = [NamedTuple(row) for row in result]

[aplavin]

Thanks, I see. Using Tables.rowtable instead of collect does this transformation and saves row values.
It's just very unintuitive. Can row access after it's been invalidated be made an error somehow?

[quinnj]

PR to make invalid row access an explicit error: #262