v1.61.0 (SQLite 3.54): prepared statements are re-prepared on every execution because of sqlite3_clear_bindings

Since v1.61.0, a statement prepared through database/sql (db.Prepare, tx.Prepare, any statement cache) is fully re-prepared on every execution: parsed and planned again. This happens when the statement joins tables or has an ORDER BY, and has a col = ? term. A simple join takes twice as long per execution as on v1.60.1.

Cause

This is upstream SQLite 3.54 behavior that the driver triggers. It reproduces with native C SQLite 3.54.1 (repro.c below).

whereLoopOutputAdjust() (where.c:3096) now calls sqlite3ExprIsInteger(pRight, &k, pParse, 1) for col = ? terms. 3.53.4 passed pParse = 0 here. It returns early for a single table without ORDER BY.
With a parse context, that call reaches sqlite3VdbeReprepareOnBind(v, iVar, 1) (expr.c:3000). That sets the parameter's expmask bit and the new smimask bit, meaning "re-prepare only if the value changes to or from 0/1".
The bind functions honor smimask (vdbeUnbind, sqlite3_bind_int64). sqlite3_clear_bindings() still does if( p->expmask ) p->expired = 1; (vdbeapi.c:176), so the next sqlite3_step re-prepares.
The driver calls reset and then clearBindings after every execution of a cached statement: stmt.exec (stmt.go:185), the stmt.query error paths and rows.Close (rows.go:94). So every execution pays a full re-prepare.

Measurements

Apple M2, darwin/arm64, otherwise idle, main.go below.

database/sql, db.Prepare plus 100,000 QueryRow calls, µs per execution, median of 3 interleaved rounds:

query	v1.60.1 (3.53.4)	v1.61.0 (3.54.1)	v1.61.0 + fix below
`SELECT a.v FROM a JOIN b ON b.aid = a.id WHERE b.x = ?`	6.4	12.5	6.7
`SELECT aid FROM b WHERE x = ? ORDER BY aid`	6.6	11.5	6.9
`SELECT aid FROM b WHERE x = ?` (control)	6.1	6.1	6.1

C API, SQLITE_STMTSTATUS_REPREPARE after 1000 rounds of bind_int64(1, 5), step, reset, then X:

X	3.54.1, modernc	3.54.1, native C
`sqlite3_clear_bindings`	999	999
`sqlite3_bind_null` on each parameter	0	0
nothing	0	0
`sqlite3_clear_bindings` with `SQLITE_DBCONFIG_ENABLE_QPSG`	0

The control query shows 0 in every case.

Suggested fix in the driver

Unbind with sqlite3_bind_null per parameter instead of sqlite3_clear_bindings. Every caller has already reset the statement, which bind_null requires. It still drops the statement's references to the bind buffers (bound SQLITE_STATIC and freed after the step), and it goes through vdbeUnbind, which applies the smimask rule. A parameter whose last value was 0 or 1 still expires the statement, as SQLite intends. With this change, go test of the package passes (darwin/arm64).

 func (c *conn) clearBindings(pstmt uintptr) error {
-	if rc := sqlite3.Xsqlite3_clear_bindings(c.tls, pstmt); rc != sqlite3.SQLITE_OK {
-		return c.errstr(rc)
+	// Not sqlite3_clear_bindings: since SQLite 3.54 it expires any statement
+	// with a plan-sensitive parameter, forcing a re-prepare on the next step.
+	// Callers have already reset pstmt, as sqlite3_bind_null requires.
+	for i := sqlite3.Xsqlite3_bind_parameter_count(c.tls, pstmt); i > 0; i-- {
+		if rc := sqlite3.Xsqlite3_bind_null(c.tls, pstmt, i); rc != sqlite3.SQLITE_OK {
+			return c.errstr(rc)
+		}
 	}
 	return nil
 }

Upstream, sqlite3_clear_bindings should probably apply the same per-parameter smimask check as vdbeUnbind. repro.c is a native C repro for that report.

Repro

go mod init repro
go get modernc.org/sqlite@v1.61.0 modernc.org/libc@v1.79.0 && go run .  # SQLite 3.54.1
go get modernc.org/sqlite@v1.60.1 modernc.org/libc@v1.77.1 && go run .  # SQLite 3.53.4

Native: cc -O2 -o repro repro.c sqlite3.c && ./repro, using the 3.54.1 amalgamation.

main.go

// SQLite 3.54 re-prepares a statement on every execution after
// sqlite3_clear_bindings when its WHERE clause has a "col = ?" term and the
// query joins tables or has an ORDER BY.
//
//	go mod init repro
//	go get modernc.org/sqlite@v1.61.0 modernc.org/libc@v1.79.0 && go run .  # SQLite 3.54.1
//	go get modernc.org/sqlite@v1.60.1 modernc.org/libc@v1.77.1 && go run .  # SQLite 3.53.4
package main

import (
	"database/sql"
	"fmt"
	"os"
	"path/filepath"
	"time"
	"unsafe"

	"modernc.org/libc"
	_ "modernc.org/sqlite"
	lib "modernc.org/sqlite/lib"
)

var queries = []string{
	"SELECT a.v FROM a JOIN b ON b.aid = a.id WHERE b.x = ?",
	"SELECT aid FROM b WHERE x = ? ORDER BY aid",
	"SELECT aid FROM b WHERE x = ?", // control: single table, no ORDER BY
}

const schema = `CREATE TABLE a(id INTEGER PRIMARY KEY, v);
	CREATE TABLE b(x, aid);
	CREATE INDEX b_x ON b(x);
	WITH RECURSIVE c(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM c WHERE i<10000)
	INSERT INTO a SELECT i, i FROM c;
	INSERT INTO b SELECT id, id FROM a;`

func main() {
	dir, err := os.MkdirTemp("", "reprepare")
	check(err)
	defer os.RemoveAll(dir)
	path := filepath.Join(dir, "t.db")

	db, err := sql.Open("sqlite", path)
	check(err)
	_, err = db.Exec(schema)
	check(err)
	var v string
	check(db.QueryRow("SELECT sqlite_version()").Scan(&v))
	fmt.Println("SQLite", v)
	fmt.Println("database/sql, prepared statement run 100000 times:")
	for _, q := range queries {
		st, err := db.Prepare(q)
		check(err)
		var n int64
		start := time.Now()
		for i := 0; i < 100000; i++ {
			check(st.QueryRow(1 + i%10000).Scan(&n))
		}
		fmt.Printf("  %6.2f µs/run  %s\n", float64(time.Since(start).Microseconds())/100000, q)
		st.Close()
	}
	db.Close()

	fmt.Println("C API, re-prepares (SQLITE_STMTSTATUS_REPREPARE) over 1000 runs of bind_int64(5) + step + reset + ...:")
	for _, q := range queries {
		fmt.Printf("  clear_bindings %4d   bind_null loop %4d   nothing %4d   clear_bindings+QPSG %4d   %s\n",
			reprepares(path, q, "clear", false), reprepares(path, q, "null", false),
			reprepares(path, q, "none", false), reprepares(path, q, "clear", true), q)
	}
}

func reprepares(path, query, after string, qpsg bool) int32 {
	tls := libc.NewTLS()
	defer tls.Close()
	cs := func(s string) uintptr { p, err := libc.CString(s); check(err); return p }
	pp := tls.Alloc(8)
	defer tls.Free(8)
	rc(lib.Xsqlite3_open(tls, cs(path), pp))
	db := *(*uintptr)(unsafe.Pointer(pp))
	defer lib.Xsqlite3_close(tls, db)
	if qpsg {
		va := libc.NewVaList(int32(1), uintptr(0))
		rc(lib.Xsqlite3_db_config(tls, db, lib.SQLITE_DBCONFIG_ENABLE_QPSG, va))
		libc.Xfree(tls, va)
	}
	rc(lib.Xsqlite3_prepare_v3(tls, db, cs(query), -1, lib.SQLITE_PREPARE_PERSISTENT, pp, 0))
	stmt := *(*uintptr)(unsafe.Pointer(pp))
	defer lib.Xsqlite3_finalize(tls, stmt)
	for i := 0; i < 1000; i++ {
		rc(lib.Xsqlite3_bind_int64(tls, stmt, 1, 5))
		for lib.Xsqlite3_step(tls, stmt) == lib.SQLITE_ROW {
		}
		rc(lib.Xsqlite3_reset(tls, stmt))
		switch after {
		case "clear":
			rc(lib.Xsqlite3_clear_bindings(tls, stmt))
		case "null":
			rc(lib.Xsqlite3_bind_null(tls, stmt, 1))
		}
	}
	return lib.Xsqlite3_stmt_status(tls, stmt, lib.SQLITE_STMTSTATUS_REPREPARE, 0)
}

func rc(code int32) {
	if code != lib.SQLITE_OK {
		panic(fmt.Sprintf("sqlite rc %d", code))
	}
}

func check(err error) {
	if err != nil {
		panic(err)
	}
}

repro.c

/* sqlite3_clear_bindings() forces a re-prepare on every run of a statement
** whose "col = ?" term was marked by whereLoopOutputAdjust() (3.54.0+).
**   cc -O2 -o repro repro.c sqlite3.c && ./repro
*/
#include <stdio.h>
#include "sqlite3.h"

static const char *zQuery[] = {
  "SELECT a.v FROM a JOIN b ON b.aid = a.id WHERE b.x = ?",
  "SELECT aid FROM b WHERE x = ? ORDER BY aid",
  "SELECT aid FROM b WHERE x = ?",  /* control: single table, no ORDER BY */
};

/* mode 0: clear_bindings, 1: bind_null, 2: nothing */
static int reprepares(sqlite3 *db, const char *zSql, int mode){
  sqlite3_stmt *p;
  int i, n;
  sqlite3_prepare_v3(db, zSql, -1, SQLITE_PREPARE_PERSISTENT, &p, 0);
  for(i=0; i<1000; i++){
    sqlite3_bind_int64(p, 1, 5);
    while( sqlite3_step(p)==SQLITE_ROW ){}
    sqlite3_reset(p);
    if( mode==0 ) sqlite3_clear_bindings(p);
    if( mode==1 ) sqlite3_bind_null(p, 1);
  }
  n = sqlite3_stmt_status(p, SQLITE_STMTSTATUS_REPREPARE, 0);
  sqlite3_finalize(p);
  return n;
}

int main(void){
  sqlite3 *db;
  int i;
  sqlite3_open(":memory:", &db);
  sqlite3_exec(db,
    "CREATE TABLE a(id INTEGER PRIMARY KEY, v);"
    "CREATE TABLE b(x, aid);"
    "CREATE INDEX b_x ON b(x);"
    "WITH RECURSIVE c(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM c WHERE i<10000)"
    " INSERT INTO a SELECT i, i FROM c;"
    "INSERT INTO b SELECT id, id FROM a;", 0, 0, 0);
  printf("SQLite %s, re-prepares per 1000 runs of bind_int64(5)+step+reset+...\n",
         sqlite3_libversion());
  for(i=0; i<3; i++){
    printf("  clear_bindings %4d  bind_null %4d  nothing %4d  %s\n",
           reprepares(db, zQuery[i], 0), reprepares(db, zQuery[i], 1),
           reprepares(db, zQuery[i], 2), zQuery[i]);
  }
  sqlite3_close(db);
  return 0;
}