Invalid memory access if query inside rows iteration

[rsk700]

Crystal 1.0.0 [dd40a2442] (2021-03-22)
LLVM: 10.0.0
Default target: x86_64-unknown-linux-gnu
sqlite3: version: 0.18.0

Running this code with query inside iteration, fails with Invalid memory access error:

require "sqlite3"

DB.open "sqlite3://%3amemory%3a" do |db|
    db.exec "create table t1(id integer primary key autoincrement, t text)"
    db.exec "insert into t1 (t) values ('v1')"
    db.query "select id, t from t1" do |rs|
        rs.each do
            v = db.scalar "select id from t1 limit 1"
        end
    end
end

and error:

Invalid memory access (signal 11) at address 0x0
[0x55e447408396] *Exception::CallStack::print_backtrace:(Int32 | Nil) +118
[0x55e4473f783c] __crystal_sigfault_handler +316
[0x7fb5022963c0] ???
[0x7fb50228cfc4] pthread_mutex_lock +4
[0x7fb5024ea28f] sqlite3_reset +31
[0x55e4474da30b] *SQLite3::ResultSet#do_close:Int32 +27
[0x55e4474da2bf] *SQLite3::ResultSet +63
[0x55e4473e605a] __crystal_main +1658
[0x55e4474db386] *Crystal::main_user_code<Int32, Pointer(Pointer(UInt8))>:Nil +6
[0x55e4474db1fc] *Crystal::main<Int32, Pointer(Pointer(UInt8))>:Int32 +44
[0x55e4473f15e6] main +6
[0x7fb50203d0b3] __libc_start_main +243
[0x55e4473e591e] _start +46
[0x0] ???

[plambert]

I also see this, and can reproduce it easily.

As far as I can tell, the iteration actually works fine, everything works fine, and the crash occurs when closing the ResultSet.

Is there any workaround that'd still let me query the database inside of an iteration over the results of a query? I have a use case where I'm iterating hundreds of thousands of rows, and don't want to load them all into memory, or take the performance hit of using LIMIT/OFFSET to "page" through the results.

[plambert]

I think I have found a workaround that usually works. By ensuring the connection pool has at least two members, and checking out a connection outside of the main query, but using the checked-out connection for the read-only query inside the loop, it doesn't seem to fail.

However, with an in-memory database, it fails to see the table that was created. So I am using an on-disk file in this test:

require "sqlite3"

DB.open "sqlite3://./test.sqlite3?max_idle_pool_size=2&initial_pool_size=2" do |db|
    db.exec "create table t1(id integer primary key autoincrement, t text)"
    db.exec "insert into t1 (t) values ('v1')"
    db.using_connection do |connection|
      db.query "select id, t from t1" do |rs|
        rs.each do
          v = connection.scalar "select id from t1 limit 1"
        end
      end
    end
end

This seems to work pretty consistently.