database/sql: Multiple statements in Exec/Query

[ncruces]

An Exec/Query call with multiple parameterized statements results in a "sqlite3: multiple statements" error.

db, err := sql.Open("sqlite3", "data.db")

// works fine
_, err = db.Exec(`
	drop table if exists t1;
	drop table if exists t2;
	create table t1(id integer primary key, name1 text);
	create table t2(id integer primary key, name2 text);
`)

// fails with error: sqlite3: multiple statements
args := []any{"alice", "alice"}
_, err = db.Exec(`
	insert into t1(name1) values(?);
	insert into t2(name2) values(?);
`, args...)

This behavior is not consistent with mattn/go-sqlite3 and modernc.org/sqlite (both work fine).

[ncruces]

I wouldn't consider this a bug. I actually believe it's a misfeature of other drivers.

I don't see how this can be correctly implemented in the face of numbered (?NNN) parameters, and especially numbered (?NNN) and positional (?) parameters mixed together.

The problem is that SQLite doesn't allow to bind parameters to sqlite3_exec, and sqlite3_prepare_v3 does not accept multiple statements. So, suddenly, which parameters go to which statement is up to interpretation, and IMO there is no correct answer.

To see what I mean, consider this example:

_, err = db.Exec(`
	insert into t1(name1) values(?, ?3);
	insert into t2(name2) values(?, ?1);
`, 1, 2, 3, 4)

SQLite would see insert into t1(name1) values(?, ?3) with arguments 1, 2, 3 and 4, and bind 1 and 3.

But for the next statement, insert into t2(name2) values(?, ?1) what should happen? If this was a single statement (one big query) this fragment would get 4 and 1. But that's actually impossible to achieve, as for an isolated fragment these both get the same index (1) so they must have the same value.

The only sane solution I would see to this (and still accept multiple parametrized statements), would be to completely reject numbered (?NNN) parameters, and reject mixing positional (?) with named (:AAA) parameters.

Still it's a lot of complication, for a feature that brings little benefits, and where other drivers offer confusing and inconsistent behaviour.

https://sqlite.org/lang_expr.html#varparam