Repository: mattn/go-sqlite3 (c)

Issue:
Recent bug with nil TEXT and BLOB columns
I made a quick reproduction of the bug. tl;dr: A null value in a text or blob column returns a []byte{0} presumably something wants to return an empty string in such cases now.


```go
package repro

import (
        "database/sql"
        "fmt"

        _ "github.com/mattn/go-sqlite3"
)

func Example() {
        db, _ := sql.Open("sqlite3", "main.db")
        db.Exec("create table tbl (txt text)")
        var b []byte
        db.Exec("insert into tbl (txt) values (?)", &b)
        rows, _ := db.Query("select txt from tbl ")
        rows.Next()
        rows.Scan(&b)
        fmt.Printf("%#v\n", b)

        // Output:
        // []byte(nil)
}

// Actual:
// --- FAIL: Example (0.01s)
// got:
// []byte{0x0}
// want:
// []byte(nil)
// FAIL
// exit status 1
// FAIL repro   0.011s
```

Pull request:
Fix to better handle NULL values in TEXT and BLOB columns.
Fixes #453 

Patch:
diff --git a/sqlite3.go b/sqlite3.go
index d0327b8..bff6b7c 100644
--- a/sqlite3.go
+++ b/sqlite3.go
@@ -867,10 +867,11 @@ func (s *SQLiteStmt) bind(args []namedValue) error {
 		case float64:
 			rv = C.sqlite3_bind_double(s.s, n, C.double(v))
 		case []byte:
-			if len(v) == 0 {
+			ln := len(v)
+			if ln == 0 {
 				v = placeHolder
 			}
-			rv = C._sqlite3_bind_blob(s.s, n, unsafe.Pointer(&v[0]), C.int(len(v)))
+			rv = C._sqlite3_bind_blob(s.s, n, unsafe.Pointer(&v[0]), C.int(ln))
 		case time.Time:
 			b := []byte(v.Format(SQLiteTimestampFormats[0]))
 			rv = C._sqlite3_bind_text(s.s, n, (*C.char)(unsafe.Pointer(&b[0])), C.int(len(b)))


Test patch:
diff --git a/sqlite3_test.go b/sqlite3_test.go
index 7c545e1..09e6727 100644
--- a/sqlite3_test.go
+++ b/sqlite3_test.go
@@ -6,6 +6,7 @@
 package sqlite3
 
 import (
+	"bytes"
 	"database/sql"
 	"database/sql/driver"
 	"errors"
@@ -1343,6 +1344,61 @@ func TestUpdateAndTransactionHooks(t *testing.T) {
 	}
 }
 
+func TestNilAndEmptyBytes(t *testing.T) {
+	db, err := sql.Open("sqlite3", ":memory:")
+	if err != nil {
+		t.Fatal(err)
+	}
+	defer db.Close()
+	actualNil := []byte("use this to use an actual nil not a reference to nil")
+	emptyBytes := []byte{}
+	for tsti, tst := range []struct {
+		name          string
+		columnType    string
+		insertBytes   []byte
+		expectedBytes []byte
+	}{
+		{"actual nil blob", "blob", actualNil, nil},
+		{"referenced nil blob", "blob", nil, nil},
+		{"empty blob", "blob", emptyBytes, emptyBytes},
+		{"actual nil text", "text", actualNil, nil},
+		{"referenced nil text", "text", nil, nil},
+		{"empty text", "text", emptyBytes, emptyBytes},
+	} {
+		if _, err = db.Exec(fmt.Sprintf("create table tbl%d (txt %s)", tsti, tst.columnType)); err != nil {
+			t.Fatal(tst.name, err)
+		}
+		if bytes.Equal(tst.insertBytes, actualNil) {
+			if _, err = db.Exec(fmt.Sprintf("insert into tbl%d (txt) values (?)", tsti), nil); err != nil {
+				t.Fatal(tst.name, err)
+			}
+		} else {
+			if _, err = db.Exec(fmt.Sprintf("insert into tbl%d (txt) values (?)", tsti), &tst.insertBytes); err != nil {
+				t.Fatal(tst.name, err)
+			}
+		}
+		rows, err := db.Query(fmt.Sprintf("select txt from tbl%d", tsti))
+		if err != nil {
+			t.Fatal(tst.name, err)
+		}
+		if !rows.Next() {
+			t.Fatal(tst.name, "no rows")
+		}
+		var scanBytes []byte
+		if err = rows.Scan(&scanBytes); err != nil {
+			t.Fatal(tst.name, err)
+		}
+		if err = rows.Err(); err != nil {
+			t.Fatal(tst.name, err)
+		}
+		if tst.expectedBytes == nil && scanBytes != nil {
+			t.Errorf("%s: %#v != %#v", tst.name, scanBytes, tst.expectedBytes)
+		} else if !bytes.Equal(scanBytes, tst.expectedBytes) {
+			t.Errorf("%s: %#v != %#v", tst.name, scanBytes, tst.expectedBytes)
+		}
+	}
+}
+
 var customFunctionOnce sync.Once
 
 func BenchmarkCustomFunctions(b *testing.B) {