Unexpected behaviour when executing multiple queries with a single execute

[fnorlin-skovik]

What happens?

Hi,

We have run into what we think are unexpected behaviours when executing multiple queries with a single execute statement.

To Reproduce

Errors not raised

require 'duckdb'

db = DuckDB::Database.open
con = db.connect

puts con.execute('CREATE TABLE test (v VARCHAR); CREATE TABLE test (v VARCHAR);').to_a

db.close
con.close

Will as expected raise the following error:

/.../.rbenv/versions/3.2.4/lib/ruby/gems/3.2.0/gems/duckdb-1.1.2.0/lib/duckdb/connection.rb:29:in `query_sql': Catalog Error: Table with name "test" already exists! (DuckDB::Error)
	from /.../.rbenv/versions/3.2.4/lib/ruby/gems/3.2.0/gems/duckdb-1.1.2.0/lib/duckdb/connection.rb:29:in `query'
	from multiple_queries.rb:6:in `<main>'

But if you add a select in any position in the query:

require 'duckdb'

db = DuckDB::Database.open
con = db.connect

# These all give the same result:
puts con.execute('CREATE TABLE test (v VARCHAR); CREATE TABLE test (v VARCHAR); SELECT 42;').to_a
# puts con.execute('CREATE TABLE test (v VARCHAR); SELECT 42; CREATE TABLE test (v VARCHAR);').to_a
# puts con.execute('SELECT 42; CREATE TABLE test (v VARCHAR); CREATE TABLE test (v VARCHAR);').to_a

db.close
con.close

The program suddenly does not raise an error any more, but instead prints 42.

I tested the corresponding code with the python API and that raises an error in the second example as well.

More queries are executed after the one returning the result

require 'duckdb'

db = DuckDB::Database.open
con = db.connect

con.execute('CREATE TABLE test (i INTEGER)')
puts con.execute('INSERT INTO test VALUES (1), (2); SELECT * FROM test; INSERT INTO test VALUES (3), (4); SELECT * FROM test;').to_a

puts '---'

puts con.execute('SELECT * FROM test;').to_a

db.close
con.close

Will print:

1
2
---
1
2
3
4

I tested this to with the python API and that will return:

┌───────┐
│   i   │
│ int32 │
├───────┤
│     1 │
│     2 │
│     3 │
│     4 │
└───────┘

┌───────┐
│   i   │
│ int32 │
├───────┤
│     1 │
│     2 │
│     3 │
│     4 │
└───────┘

Which seems more correct. It is a bit strange that two selects that are run after each other (before and after the line) will not return the same data.

OS:

macOS: 14.7 (23H124)

DuckDB version:

v1.1.2 f680b7d08f

ruby-duckdb version:

duckdb (1.1.2.0)

Ruby version:

ruby 3.2.4

[suketa]

@fnorlin-skovik

Unfortunately, ruby-duckdb does not support multiple queries currently. I think #650 is related.

[suketa]

@fnorlin-skovik

I updated ruby-duckdb. try the latest version.

ruby-duckdb supports multiple SQL statement, but does not accept multiple SQL statements with bind parameters. Because we don't know which parameters should be bind to which SQL statement. If you pass 2 or more arguments to DuckDB::Connection#execute, then the 1st argument is regarded as a single SQL statement.