Unspecified named bind variables retain previous value.

[peterjeremy]

Given a prepared statement of the form INSERT INTO foo (a,b,c) VALUES($x,$y,$z) and successive Exec() calls passing []driver.NamedValue with the Name field specified, if not all the bind variables are specified then previous bindings are retained, rather than the unspecified variables being set to NULL. This behaviour is undocumented and counter-intuitive.

bind.go.txt contains sample code demonstrating the issue. When run with mattn/go-sqlite3 v1.14.14, it generates the output:

Fail: Row 6, col 2: 9 != <nil>
        args: []driver.NamedValue{driver.NamedValue{Name:"a", Ordinal:1, Value:666}, driver.NamedValue{Name:"x", Ordinal:2, Value:27}, driver.NamedValue{Name:"z", Ordinal:3, Value:"oueh"}}
        vals: []driver.Value{27, 9, "oueh"}
Fail: Row 7, col 1: 27 != <nil>
        args: []driver.NamedValue{driver.NamedValue{Name:"a", Ordinal:1, Value:666}, driver.NamedValue{Name:"y", Ordinal:2, Value:28}, driver.NamedValue{Name:"z", Ordinal:3, Value:"ontzz"}}
        vals: []driver.Value{27, 28, "ontzz"}
Fail: Row 8, col 3: ontzz != <nil>
        args: []driver.NamedValue{driver.NamedValue{Name:"a", Ordinal:1, Value:666}, driver.NamedValue{Name:"x", Ordinal:2, Value:30}, driver.NamedValue{Name:"y", Ordinal:3, Value:31}}
        vals: []driver.Value{30, 31, "ontzz"}

Looking at SQLite3 documentation, https://sqlite.org/c3ref/clear_bindings.html notes that sqlite3_reset() doesn't reset bindings and calling sqlite3_clear_bindings() is needed to do so. sqlite3_clear_bindings() is not exposed by mattn/go-sqlite3. Looking in the master code, sqlite3_clear_bindings() is only called before an error return from execSync(). I believe sqlite3_clear_bindings() needs to be called wherever sqlite3_reset() is called.

Note that (at least) github.com/mxk/go-sqlite/sqlite3 has the intuitive behaviour that unspecified bind variables default to NULL.