sqlx_test.go

// The following environment variables, if set, will be used:
//
//   - SQLX_SQLITE_DSN
//   - SQLX_POSTGRES_DSN
//   - SQLX_MYSQL_DSN
//
// Set any of these variables to 'skip' to skip them.  Note that for MySQL,
// the string '?parseTime=True' will be appended to the DSN if it's not there
// already.
package sqlx

import (
	"database/sql"
	"database/sql/driver"
	"encoding/json"
	"fmt"
	"log"
	"maps"
	"os"
	"reflect"
	"strings"
	"testing"
	"time"

	_ "github.com/go-sql-driver/mysql"
	_ "github.com/lib/pq"
	_ "github.com/mattn/go-sqlite3"
	"github.com/stretchr/testify/assert"
	"github.com/stretchr/testify/require"

	"github.com/vinovest/sqlx/reflectx"
)

/* compile time checks that Db, Tx, Stmt (qStmt) implement expected interfaces */
var _, _ Ext = &DB{}, &Tx{}
var _, _ ColScanner = &Row{}, &Rows{}
var _ Queryer = &qStmt[any]{}
var _ Execer = &qStmt[any]{}

var TestPostgres = true
var TestSqlite = true
var TestMysql = true

var sldb *DB
var pgdb *DB
var mysqldb *DB
var pgdsn string
var mydsn string
var sqdsn string

func init() {
	ConnectAll()
}

func ConnectAll() {
	var err error

	pgdsn = os.Getenv("SQLX_POSTGRES_DSN")
	mydsn = os.Getenv("SQLX_MYSQL_DSN")
	sqdsn = os.Getenv("SQLX_SQLITE_DSN")

	TestPostgres = pgdsn != "skip"
	TestMysql = mydsn != "skip"
	TestSqlite = sqdsn != "skip"

	if !strings.Contains(mydsn, "parseTime=true") {
		mydsn += "?parseTime=true"
	}

	if TestPostgres {
		pgdb, err = Connect("postgres", pgdsn)
		if err != nil {
			fmt.Printf("Disabling PG tests:\n    %v\n", err)
			TestPostgres = false
		}
	} else {
		fmt.Println("Disabling Postgres tests.")
	}

	if TestMysql {
		mysqldb, err = Connect("mysql", mydsn)
		if err != nil {
			fmt.Printf("Disabling MySQL tests:\n    %v\n", err)
			TestMysql = false
		}
	} else {
		fmt.Println("Disabling MySQL tests.")
	}

	if TestSqlite {
		sldb, err = Connect("sqlite3", sqdsn)
		if err != nil {
			fmt.Printf("Disabling SQLite:\n    %v\n", err)
			TestSqlite = false
		}
	} else {
		fmt.Println("Disabling SQLite tests.")
	}
}

type Schema struct {
	create string
	drop   string
}

func (s Schema) Postgres() (string, string, string) {
	return s.create, s.drop, `now()`
}

func (s Schema) MySQL() (string, string, string) {
	return strings.Replace(s.create, `"`, "`", -1), s.drop, `now()`
}

func (s Schema) Sqlite3() (string, string, string) {
	return strings.Replace(s.create, `now()`, `CURRENT_TIMESTAMP`, -1), s.drop, `CURRENT_TIMESTAMP`
}

var defaultSchema = Schema{
	create: `
CREATE TABLE person (
	first_name text,
	last_name text,
	email text,
	added_at timestamp default now()
);

CREATE TABLE place (
	country text,
	city text NULL,
	telcode integer
);

CREATE TABLE capplace (
	"COUNTRY" text,
	"CITY" text NULL,
	"TELCODE" integer
);

CREATE TABLE nullperson (
    first_name text NULL,
    last_name text NULL,
    email text NULL
);

CREATE TABLE employees (
	name text,
	id integer,
	boss_id integer
);

`,
	drop: `
drop table person;
drop table place;
drop table capplace;
drop table nullperson;
drop table employees;
`,
}

type Person struct {
	FirstName string `db:"first_name"`
	LastName  string `db:"last_name"`
	Email     string
	AddedAt   time.Time `db:"added_at"`
}

type Person2 struct {
	FirstName sql.NullString `db:"first_name"`
	LastName  sql.NullString `db:"last_name"`
	Email     sql.NullString
}

type Place struct {
	Country string
	City    sql.NullString
	TelCode int
}

type PlacePtr struct {
	Country string
	City    *string
	TelCode int
}

type PersonPlace struct {
	Person
	Place
}

type PersonPlacePtr struct {
	*Person
	*Place
}

type EmbedConflict struct {
	FirstName string `db:"first_name"`
	Person
}

type SliceMember struct {
	Country   string
	City      sql.NullString
	TelCode   int
	People    []Person `db:"-"`
	Addresses []Place  `db:"-"`
}

// Note that because of field map caching, we need a new type here
// if we've used Place already somewhere in sqlx
type CPlace Place

func MultiExec(e Execer, query string) {
	stmts := strings.Split(query, ";\n")
	if len(strings.Trim(stmts[len(stmts)-1], " \n\t\r")) == 0 {
		stmts = stmts[:len(stmts)-1]
	}
	for _, s := range stmts {
		_, err := e.Exec(s)
		if err != nil {
			fmt.Println(err, s)
		}
	}
}

func RunWithSchema(schema Schema, t *testing.T, test func(db *DB, t *testing.T, now string)) {
	runner := func(db *DB, t *testing.T, create, drop, now string) {
		defer func() {
			MultiExec(db, drop)
		}()

		MultiExec(db, create)
		test(db, t, now)
	}

	if TestPostgres {
		create, drop, now := schema.Postgres()
		runner(pgdb, t, create, drop, now)
	}
	if TestSqlite {
		create, drop, now := schema.Sqlite3()
		runner(sldb, t, create, drop, now)
	}
	if TestMysql {
		create, drop, now := schema.MySQL()
		runner(mysqldb, t, create, drop, now)
	}
}

func loadDefaultFixture(db *DB, t *testing.T) {
	tx := db.MustBegin()
	tx.MustExec(tx.Rebind("INSERT INTO person (first_name, last_name, email) VALUES (?, ?, ?)"), "Jason", "Moiron", "jmoiron@jmoiron.net")
	tx.MustExec(tx.Rebind("INSERT INTO person (first_name, last_name, email) VALUES (?, ?, ?)"), "John", "Doe", "johndoeDNE@gmail.net")
	tx.MustExec(tx.Rebind("INSERT INTO place (country, city, telcode) VALUES (?, ?, ?)"), "United States", "New York", "1")
	tx.MustExec(tx.Rebind("INSERT INTO place (country, telcode) VALUES (?, ?)"), "Hong Kong", "852")
	tx.MustExec(tx.Rebind("INSERT INTO place (country, telcode) VALUES (?, ?)"), "Singapore", "65")
	if db.DriverName() == "mysql" {
		tx.MustExec(tx.Rebind("INSERT INTO capplace (`COUNTRY`, `TELCODE`) VALUES (?, ?)"), "Sarf Efrica", "27")
	} else {
		tx.MustExec(tx.Rebind("INSERT INTO capplace (\"COUNTRY\", \"TELCODE\") VALUES (?, ?)"), "Sarf Efrica", "27")
	}
	tx.MustExec(tx.Rebind("INSERT INTO employees (name, id) VALUES (?, ?)"), "Peter", "4444")
	tx.MustExec(tx.Rebind("INSERT INTO employees (name, id, boss_id) VALUES (?, ?, ?)"), "Joe", "1", "4444")
	tx.MustExec(tx.Rebind("INSERT INTO employees (name, id, boss_id) VALUES (?, ?, ?)"), "Martin", "2", "4444")
	tx.Commit()
}

// tests the options on the DB struct
func TestUnsafeDBOptions(t *testing.T) {
	if TestPostgres {
		tdb, err := Connect("postgres", pgdsn, WithUnsafe())
		require.NoError(t, err)
		assert.True(t, getOptions(tdb).allowMissingFields())
		tdb, err = Connect("postgres", pgdsn, WithSetUnsafe(true))
		require.NoError(t, err)
		assert.True(t, getOptions(tdb).allowMissingFields())
	} else if TestSqlite {
		tdb, err := Connect("sqlite3", sqdsn, WithUnsafe())
		require.NoError(t, err)
		assert.True(t, getOptions(tdb).allowMissingFields())
		tdb, err = Connect("sqlite3", sqdsn, WithSetUnsafe(true))
		require.NoError(t, err)
		assert.True(t, getOptions(tdb).allowMissingFields())
	} else if TestMysql {
		tdb, err := Connect("mysql", mydsn, WithUnsafe())
		require.NoError(t, err)
		assert.True(t, getOptions(tdb).allowMissingFields())
		tdb, err = Connect("mysql", mydsn, WithUnsafe())
		require.NoError(t, err)
		assert.True(t, getOptions(tdb).allowMissingFields())
	}
}

// Test a new backwards compatible feature, that missing scan destinations
// will silently scan into sql.RawText rather than failing/panicing
func TestMissingNames(t *testing.T) {
	RunWithSchema(defaultSchema, t, func(db *DB, t *testing.T, now string) {
		loadDefaultFixture(db, t)
		type PersonPlus struct {
			FirstName string `db:"first_name"`
			LastName  string `db:"last_name"`
			Email     string
			// AddedAt time.Time `db:"added_at"`
		}

		{
			undb := db.Unsafe()
			assert.False(t, getOptions(db).allowMissingFields(), "expected db to still be safe")
			assert.True(t, getOptions(undb).allowMissingFields(), "expected db to still be unsafe after Unsafe()")
		}

		{
			// test Select first
			pps := []PersonPlus{}
			// pps lacks added_at destination
			err := db.Select(&pps, "SELECT * FROM person")
			if err == nil {
				t.Error("Expected missing name from Select to fail, but it did not.")
			}
		}

		{
			// test Get
			pp := PersonPlus{}
			err := db.Get(&pp, "SELECT * FROM person LIMIT 1")
			if err == nil {
				t.Error("Expected missing name Get to fail, but it did not.")
			}
		}

		{
			// test naked StructScan
			pps := []PersonPlus{}
			rows, err := db.Query("SELECT * FROM person LIMIT 1")
			if err != nil {
				t.Fatal(err)
			}
			rows.Next()
			err = StructScan(rows, &pps)
			if err == nil {
				t.Error("Expected missing name in StructScan to fail, but it did not.")
			}
			rows.Close()
		}

		{
			// now try various things with unsafe set.
			db := db.Unsafe()
			pps := []PersonPlus{}
			err := db.Select(&pps, "SELECT * FROM person")
			require.NoError(t, err)
		}

		{
			// test Get
			db := db.Unsafe()
			pp := PersonPlus{}
			err := db.Get(&pp, "SELECT * FROM person LIMIT 1")
			require.NoError(t, err)
		}

		{
			// test naked StructScan
			db := db.Unsafe()
			pps := []PersonPlus{}
			rowsx, err := db.Queryx("SELECT * FROM person LIMIT 1")
			if err != nil {
				t.Fatal(err)
			}
			rowsx.Next()
			err = StructScan(rowsx, &pps)
			if err != nil {
				t.Error(err)
			}
			rowsx.Close()
		}

		{
			// test Named stmt
			db := db.Unsafe()
			nstmt, err := db.PrepareNamed(`SELECT * FROM person WHERE first_name != :name`)
			require.NoError(t, err)
			// its internal stmt should be marked unsafe
			if !getOptions(nstmt.Stmt).allowMissingFields() {
				t.Error("expected NamedStmt to be unsafe but its underlying stmt did not inherit safety")
			}
			pps := []PersonPlus{}
			err = nstmt.Select(&pps, map[string]any{"name": "Jason"})
			require.NoError(t, err)
			if len(pps) != 1 {
				t.Errorf("Expected 1 person back, got %d", len(pps))
			}
		}

		{
			// test it with a safe db
			nstmt, err := db.PrepareNamed(`SELECT * FROM person WHERE first_name != :name`)
			require.NoError(t, err)
			// it should be safe
			if getOptions(nstmt).allowMissingFields() {
				t.Error("NamedStmt did not inherit safety")
			}
			nstmt = nstmt.Unsafe()
			if !getOptions(nstmt).allowMissingFields() {
				t.Error("expected newly unsafed NamedStmt to be unsafe")
			}
			pps := []PersonPlus{}
			err = nstmt.Select(&pps, map[string]any{"name": "Jason"})
			require.NoError(t, err)
			if len(pps) != 1 {
				t.Errorf("Expected 1 person back, got %d", len(pps))
			}
		}

		{
			// generic version, stmt
			db := db.Unsafe()
			rebound := db.Rebind(`SELECT * FROM person WHERE first_name != ?`)
			ps, err := Preparex[PersonPlus](db, rebound)
			require.NoError(t, err)
			// it should be unsafe
			if !getOptions(ps).allowMissingFields() {
				t.Error("NamedStmt did not inherit unsafe")
			}
			pps, err := ps.List("Jason")
			require.NoError(t, err)
			if len(pps) != 1 {
				t.Errorf("Expected 1 person back, got %d", len(pps))
			}
		}

		{
			// generic version
			db := db.Unsafe()
			ps, err := PrepareNamed[PersonPlus](db, `SELECT * FROM person WHERE first_name != :name`)
			require.NoError(t, err)
			// it should be unsafe
			if !getOptions(ps).allowMissingFields() {
				t.Error("NamedStmt did not inherit unsafe")
			}
			pps, err := ps.List(map[string]any{"name": "Jason"})
			require.NoError(t, err)
			if len(pps) != 1 {
				t.Errorf("Expected 1 person back, got %d", len(pps))
			}
		}
	})
}

func TestEmbeddedStructs(t *testing.T) {
	type Loop1 struct{ Person }
	type Loop2 struct{ Loop1 }
	type Loop3 struct{ Loop2 }

	RunWithSchema(defaultSchema, t, func(db *DB, t *testing.T, now string) {
		loadDefaultFixture(db, t)
		peopleAndPlaces := []PersonPlace{}
		err := db.Select(
			&peopleAndPlaces,
			`SELECT person.*, place.* FROM
             person natural join place`)
		if err != nil {
			t.Fatal(err)
		}
		for _, pp := range peopleAndPlaces {
			if len(pp.Person.FirstName) == 0 {
				t.Errorf("Expected non zero lengthed first name.")
			}
			if len(pp.Place.Country) == 0 {
				t.Errorf("Expected non zero lengthed country.")
			}
		}

		// test embedded structs with StructScan
		rows, err := db.Queryx(
			`SELECT person.*, place.* FROM
         person natural join place`)
		if err != nil {
			t.Error(err)
		}

		perp := PersonPlace{}
		rows.Next()
		err = rows.StructScan(&perp)
		if err != nil {
			t.Error(err)
		}

		if len(perp.Person.FirstName) == 0 {
			t.Errorf("Expected non zero lengthed first name.")
		}
		if len(perp.Place.Country) == 0 {
			t.Errorf("Expected non zero lengthed country.")
		}

		rows.Close()

		// test the same for embedded pointer structs
		peopleAndPlacesPtrs := []PersonPlacePtr{}
		err = db.Select(
			&peopleAndPlacesPtrs,
			`SELECT person.*, place.* FROM
             person natural join place`)
		if err != nil {
			t.Fatal(err)
		}
		for _, pp := range peopleAndPlacesPtrs {
			if len(pp.Person.FirstName) == 0 {
				t.Errorf("Expected non zero lengthed first name.")
			}
			if len(pp.Place.Country) == 0 {
				t.Errorf("Expected non zero lengthed country.")
			}
		}

		// test "deep nesting"
		l3s := []Loop3{}
		err = db.Select(&l3s, `select * from person`)
		if err != nil {
			t.Fatal(err)
		}
		for _, l3 := range l3s {
			if len(l3.Loop2.Loop1.Person.FirstName) == 0 {
				t.Errorf("Expected non zero lengthed first name.")
			}
		}

		// test "embed conflicts"
		ec := []EmbedConflict{}
		err = db.Select(&ec, `select * from person`)
		// I'm torn between erroring here or having some kind of working behavior
		// in order to allow for more flexibility in destination structs
		if err != nil {
			t.Errorf("Was not expecting an error on embed conflicts.")
		}
	})
}

func TestJoinQuery(t *testing.T) {
	type Employee struct {
		Name string
		ID   int64
		// BossID is an id into the employee table
		BossID sql.NullInt64 `db:"boss_id"`
	}
	type Boss Employee

	RunWithSchema(defaultSchema, t, func(db *DB, t *testing.T, now string) {
		loadDefaultFixture(db, t)

		var employees []struct {
			Employee
			Boss `db:"boss"`
		}

		err := db.Select(
			&employees,
			`SELECT employees.*, boss.id "boss.id", boss.name "boss.name" FROM employees
			  JOIN employees AS boss ON employees.boss_id = boss.id`)
		if err != nil {
			t.Fatal(err)
		}

		for _, em := range employees {
			if len(em.Employee.Name) == 0 {
				t.Errorf("Expected non zero lengthed name.")
			}
			if em.Employee.BossID.Int64 != em.Boss.ID {
				t.Errorf("Expected boss ids to match")
			}
		}
	})
}

func TestJoinNotEmbeddedQuery(t *testing.T) {
	type Employee struct {
		Name string
		ID   int64
		// BossID is an id into the employee table
		BossID *int64 `db:"boss_id"`
	}
	type Boss Employee

	RunWithSchema(defaultSchema, t, func(db *DB, t *testing.T, now string) {
		loadDefaultFixture(db, t)

		var employees []struct {
			Employee Employee
			Boss     Boss
		}

		err := db.Select(
			&employees,
			`SELECT employees.*, boss.*
			  FROM employees
			  JOIN employees AS boss
				  ON employees.boss_id = boss.id`)
		if err != nil {
			t.Fatal(err)
		}

		for _, em := range employees {
			if len(em.Employee.Name) == 0 {
				t.Errorf("Expected non zero lengthed name.")
			}
			if len(em.Boss.Name) == 0 {
				t.Errorf("Expected non zero lengthed name.")
			}
			if em.Employee.BossID != nil && *em.Employee.BossID != em.Boss.ID {
				t.Errorf("Expected boss ids to match")
			}
		}
	})
}

func TestJoinQueryNamedPointerStructs(t *testing.T) {
	type Employee struct {
		Name string
		ID   int64
		// BossID is an id into the employee table
		BossID sql.NullInt64 `db:"boss_id"`
	}
	type Boss Employee

	RunWithSchema(defaultSchema, t, func(db *DB, t *testing.T, now string) {
		loadDefaultFixture(db, t)

		var employees []struct {
			Emp1  *Employee `db:"emp1"`
			Emp2  *Employee `db:"emp2"`
			*Boss `db:"boss"`
		}

		err := db.Select(
			&employees,
			`SELECT emp.name "emp1.name", emp.id "emp1.id", emp.boss_id "emp1.boss_id",
			 emp.name "emp2.name", emp.id "emp2.id", emp.boss_id "emp2.boss_id",
			 boss.id "boss.id", boss.name "boss.name" FROM employees AS emp
			  JOIN employees AS boss ON emp.boss_id = boss.id
			  `)
		if err != nil {
			t.Fatal(err)
		}

		for _, em := range employees {
			if len(em.Emp1.Name) == 0 || len(em.Emp2.Name) == 0 {
				t.Errorf("Expected non zero lengthed name.")
			}
			if em.Emp1.BossID.Int64 != em.Boss.ID || em.Emp2.BossID.Int64 != em.Boss.ID {
				t.Errorf("Expected boss ids to match")
			}
		}
	})
}

func TestSelectSliceMapTime(t *testing.T) {
	RunWithSchema(defaultSchema, t, func(db *DB, t *testing.T, now string) {
		loadDefaultFixture(db, t)
		rows, err := db.Queryx("SELECT * FROM person")
		if err != nil {
			t.Fatal(err)
		}
		for rows.Next() {
			_, err := rows.SliceScan()
			if err != nil {
				t.Error(err)
			}
		}

		rows, err = db.Queryx("SELECT * FROM person")
		if err != nil {
			t.Fatal(err)
		}
		for rows.Next() {
			m := map[string]any{}
			err := rows.MapScan(m)
			if err != nil {
				t.Error(err)
			}
		}
	})
}

func TestNilReceiver(t *testing.T) {
	RunWithSchema(defaultSchema, t, func(db *DB, t *testing.T, now string) {
		loadDefaultFixture(db, t)
		var p *Person
		err := db.Get(p, "SELECT * FROM person LIMIT 1")
		if err == nil {
			t.Error("Expected error when getting into nil struct ptr.")
		}
		var pp *[]Person
		err = db.Select(pp, "SELECT * FROM person")
		if err == nil {
			t.Error("Expected an error when selecting into nil slice ptr.")
		}
	})
}

func TestNamedQuery(t *testing.T) {
	schema := Schema{
		create: `
			CREATE TABLE place (
				id integer PRIMARY KEY,
				name text NULL
			);
			CREATE TABLE person (
				first_name text NULL,
				last_name text NULL,
				email text NULL
			);
			CREATE TABLE placeperson (
				first_name text NULL,
				last_name text NULL,
				email text NULL,
				place_id integer NULL
			);
			CREATE TABLE jsperson (
				"FIRST" text NULL,
				last_name text NULL,
				"EMAIL" text NULL
			);`,
		drop: `
			drop table person;
			drop table jsperson;
			drop table place;
			drop table placeperson;
			`,
	}

	RunWithSchema(schema, t, func(db *DB, t *testing.T, now string) {
		type Person struct {
			FirstName sql.NullString `db:"first_name"`
			LastName  sql.NullString `db:"last_name"`
			Email     sql.NullString
		}

		p := Person{
			FirstName: sql.NullString{String: "ben", Valid: true},
			LastName:  sql.NullString{String: "doe", Valid: true},
			Email:     sql.NullString{String: "ben@doe.com", Valid: true},
		}

		q1 := `INSERT INTO person (first_name, last_name, email) VALUES (:first_name, :last_name, :email)`
		_, err := db.NamedExec(q1, p)
		if err != nil {
			log.Fatal(err)
		}

		p2 := &Person{}
		rows, err := db.NamedQuery("SELECT * FROM person WHERE first_name=:first_name", p)
		if err != nil {
			log.Fatal(err)
		}
		for rows.Next() {
			err = rows.StructScan(p2)
			if err != nil {
				t.Error(err)
			}
			if p2.FirstName.String != "ben" {
				t.Error("Expected first name of `ben`, got " + p2.FirstName.String)
			}
			if p2.LastName.String != "doe" {
				t.Error("Expected first name of `doe`, got " + p2.LastName.String)
			}
		}

		// these are tests for #73;  they verify that named queries work if you've
		// changed the db mapper.  This code checks both NamedQuery "ad-hoc" style
		// queries and NamedStmt queries, which use different code paths internally.
		old := (*db).Mapper

		type JSONPerson struct {
			FirstName sql.NullString `json:"FIRST"`
			LastName  sql.NullString `json:"last_name"`
			Email     sql.NullString
		}

		jp := JSONPerson{
			FirstName: sql.NullString{String: "ben", Valid: true},
			LastName:  sql.NullString{String: "smith", Valid: true},
			Email:     sql.NullString{String: "ben@smith.com", Valid: true},
		}

		db.Mapper = reflectx.NewMapperFunc("json", strings.ToUpper)

		// prepare queries for case sensitivity to test our ToUpper function.
		// postgres and sqlite accept "", but mysql uses ``;  since Go's multi-line
		// strings are `` we use "" by default and swap out for MySQL
		pdb := func(s string, db *DB) string {
			if db.DriverName() == "mysql" {
				return strings.Replace(s, `"`, "`", -1)
			}
			return s
		}

		q1 = `INSERT INTO jsperson ("FIRST", last_name, "EMAIL") VALUES (:FIRST, :last_name, :EMAIL)`
		_, err = db.NamedExec(pdb(q1, db), jp)
		if err != nil {
			t.Fatal(err, db.DriverName())
		}

		// Checks that a person pulled out of the db matches the one we put in
		check := func(t *testing.T, rows *Rows) {
			jp = JSONPerson{}
			for rows.Next() {
				err = rows.StructScan(&jp)
				if err != nil {
					t.Error(err)
				}
				if jp.FirstName.String != "ben" {
					t.Errorf("Expected first name of `ben`, got `%s` (%s) ", jp.FirstName.String, db.DriverName())
				}
				if jp.LastName.String != "smith" {
					t.Errorf("Expected LastName of `smith`, got `%s` (%s)", jp.LastName.String, db.DriverName())
				}
				if jp.Email.String != "ben@smith.com" {
					t.Errorf("Expected email of `ben@smith.com`, got `%s` (%s)", jp.Email.String, db.DriverName())
				}
			}
		}

		ns, err := db.PrepareNamed(pdb(`
			SELECT * FROM jsperson
			WHERE
				"FIRST"=:FIRST AND
				last_name=:last_name AND
				"EMAIL"=:EMAIL
		`, db))
		if err != nil {
			t.Fatal(err)
		}
		rows, err = ns.Queryx(jp)
		if err != nil {
			t.Fatal(err)
		}

		check(t, rows)

		// Check exactly the same thing, but with db.NamedQuery, which does not go
		// through the PrepareNamed/NamedStmt path.
		rows, err = db.NamedQuery(pdb(`
			SELECT * FROM jsperson
			WHERE
				"FIRST"=:FIRST AND
				last_name=:last_name AND
				"EMAIL"=:EMAIL
		`, db), jp)
		if err != nil {
			t.Fatal(err)
		}

		check(t, rows)

		// Test the new col mapper which maps the sql column names also.
		db.Mapper = reflectx.NewMapperTagColFunc("json", strings.ToLower, strings.ToLower, strings.ToLower)

		// Sqlite will return the column names with the same case as we wrote in the query.
		// So in queries with mixed casing, it will cause problems.
		rows, err = db.NamedQuery(pdb(`
			SELECT "FIRST" as fIrSt, last_name as LAST_NAME, "EMAIL" as email FROM jsperson
			WHERE
				"FIRST"=:FIRST AND
				last_name=:last_name AND
				"EMAIL"=:EMAIL
		`, db), jp)
		if err != nil {
			t.Fatal(err)
		}

		check(t, rows)

		db.Mapper = old

		// Test nested structs
		type Place struct {
			ID   int            `db:"id"`
			Name sql.NullString `db:"name"`
		}
		type PlacePerson struct {
			FirstName sql.NullString `db:"first_name"`
			LastName  sql.NullString `db:"last_name"`
			Email     sql.NullString
			Place     Place `db:"place"`
		}

		pl := Place{
			Name: sql.NullString{String: "myplace", Valid: true},
		}

		pp := PlacePerson{
			FirstName: sql.NullString{String: "ben", Valid: true},
			LastName:  sql.NullString{String: "doe", Valid: true},
			Email:     sql.NullString{String: "ben@doe.com", Valid: true},
		}

		q2 := `INSERT INTO place (id, name) VALUES (1, :name)`
		_, err = db.NamedExec(q2, pl)
		if err != nil {
			log.Fatal(err)
		}

		id := 1
		pp.Place.ID = id

		q3 := `INSERT INTO placeperson (first_name, last_name, email, place_id) VALUES (:first_name, :last_name, :email, :place.id)`
		_, err = db.NamedExec(q3, pp)
		if err != nil {
			log.Fatal(err)
		}

		pp2 := &PlacePerson{}
		rows, err = db.NamedQuery(`
			SELECT
				first_name,
				last_name,
				email,
				place.id AS "place.id",
				place.name AS "place.name"
			FROM placeperson
			INNER JOIN place ON place.id = placeperson.place_id
			WHERE
				place.id=:place.id`, pp)
		if err != nil {
			log.Fatal(err)
		}
		for rows.Next() {
			err = rows.StructScan(pp2)
			if err != nil {
				t.Error(err)
			}
			if pp2.FirstName.String != "ben" {
				t.Error("Expected first name of `ben`, got " + pp2.FirstName.String)
			}
			if pp2.LastName.String != "doe" {
				t.Error("Expected first name of `doe`, got " + pp2.LastName.String)
			}
			if pp2.Place.Name.String != "myplace" {
				t.Error("Expected place name of `myplace`, got " + pp2.Place.Name.String)
			}
			if pp2.Place.ID != pp.Place.ID {
				t.Errorf("Expected place name of %v, got %v", pp.Place.ID, pp2.Place.ID)
			}
		}
	})
}

func TestNilInserts(t *testing.T) {
	schema := Schema{
		create: `
			CREATE TABLE tt (
				id integer,
				value text NULL DEFAULT NULL
			);`,
		drop: "drop table tt;",
	}

	RunWithSchema(schema, t, func(db *DB, t *testing.T, now string) {
		type TT struct {
			ID    int
			Value *string
		}
		var v, v2 TT
		r := db.Rebind

		db.MustExec(r(`INSERT INTO tt (id) VALUES (1)`))
		db.Get(&v, r(`SELECT * FROM tt`))
		if v.ID != 1 {
			t.Errorf("Expecting id of 1, got %v", v.ID)
		}
		if v.Value != nil {
			t.Errorf("Expecting NULL to map to nil, got %s", *v.Value)
		}

		v.ID = 2
		// NOTE: this incidentally uncovered a bug which was that named queries with
		// pointer destinations would not work if the passed value here was not addressable,
		// as reflectx.FieldByIndexes attempts to allocate nil pointer receivers for
		// writing.  This was fixed by creating & using the reflectx.FieldByIndexesReadOnly
		// function.  This next line is important as it provides the only coverage for this.
		db.NamedExec(`INSERT INTO tt (id, value) VALUES (:id, :value)`, v)

		db.Get(&v2, r(`SELECT * FROM tt WHERE id=2`))
		if v.ID != v2.ID {
			t.Errorf("%v != %v", v.ID, v2.ID)
		}
		if v2.Value != nil {
			t.Errorf("Expecting NULL to map to nil, got %s", *v.Value)
		}
	})
}

func TestScanError(t *testing.T) {
	schema := Schema{
		create: `
			CREATE TABLE kv (
				k text,
				v integer
			);`,
		drop: `drop table kv;`,
	}

	RunWithSchema(schema, t, func(db *DB, t *testing.T, now string) {
		type WrongTypes struct {
			K int
			V string
		}
		_, err := db.Exec(db.Rebind("INSERT INTO kv (k, v) VALUES (?, ?)"), "hi", 1)
		if err != nil {
			t.Error(err)
		}

		rows, err := db.Queryx("SELECT * FROM kv")
		if err != nil {
			t.Error(err)
		}
		for rows.Next() {
			var wt WrongTypes
			err := rows.StructScan(&wt)
			if err == nil {
				t.Errorf("%s: Scanning wrong types into keys should have errored.", db.DriverName())
			}
		}
	})
}

func TestMultiInsert(t *testing.T) {
	RunWithSchema(defaultSchema, t, func(db *DB, t *testing.T, now string) {
		loadDefaultFixture(db, t)
		q := db.Rebind(`INSERT INTO employees (name, id) VALUES (?, ?), (?, ?);`)
		db.MustExec(q,
			"Name1", 400,
			"name2", 500,
		)
	})
}

// FIXME: this function is kinda big but it slows things down to be constantly
// loading and reloading the schema..

func TestUsage(t *testing.T) {
	RunWithSchema(defaultSchema, t, func(db *DB, t *testing.T, now string) {
		loadDefaultFixture(db, t)
		slicemembers := []SliceMember{}
		err := db.Select(&slicemembers, "SELECT * FROM place ORDER BY telcode ASC")
		if err != nil {
			t.Fatal(err)
		}

		people := []Person{}

		err = db.Select(&people, "SELECT * FROM person ORDER BY first_name ASC")
		if err != nil {
			t.Fatal(err)
		}

		jason, john := people[0], people[1]
		if jason.FirstName != "Jason" {
			t.Errorf("Expecting FirstName of Jason, got %s", jason.FirstName)
		}
		if jason.LastName != "Moiron" {
			t.Errorf("Expecting LastName of Moiron, got %s", jason.LastName)
		}
		if jason.Email != "jmoiron@jmoiron.net" {
			t.Errorf("Expecting Email of jmoiron@jmoiron.net, got %s", jason.Email)
		}
		if john.FirstName != "John" || john.LastName != "Doe" || john.Email != "johndoeDNE@gmail.net" {
			t.Errorf("John Doe's person record not what expected:  Got %v\n", john)
		}

		jason = Person{}
		err = db.Get(&jason, db.Rebind("SELECT * FROM person WHERE first_name=?"), "Jason")

		if err != nil {
			t.Fatal(err)
		}
		if jason.FirstName != "Jason" {
			t.Errorf("Expecting to get back Jason, but got %v\n", jason.FirstName)
		}

		err = db.Get(&jason, db.Rebind("SELECT * FROM person WHERE first_name=?"), "Foobar")
		if err == nil {
			t.Errorf("Expecting an error, got nil\n")
		}
		if err != sql.ErrNoRows {
			t.Errorf("Expected sql.ErrNoRows, got %v\n", err)
		}

		// The following tests check statement reuse, which was actually a problem
		// due to copying being done when creating Stmt's which was eventually removed
		stmt1, err := db.Preparex(db.Rebind("SELECT * FROM person WHERE first_name=?"))
		if err != nil {
			t.Fatal(err)
		}
		jason = Person{}

		row := stmt1.QueryRowx("DoesNotExist")
		row.Scan(&jason)
		row = stmt1.QueryRowx("DoesNotExist")
		row.Scan(&jason)

		err = stmt1.Get(&jason, "DoesNotExist User")
		if err == nil {
			t.Error("Expected an error")
		}
		err = stmt1.Get(&jason, "DoesNotExist User 2")
		if err == nil {
			t.Fatal(err)
		}

		stmt2, err := db.Preparex(db.Rebind("SELECT * FROM person WHERE first_name=?"))
		if err != nil {
			t.Fatal(err)
		}
		jason = Person{}
		tx, err := db.Beginx()
		if err != nil {
			t.Fatal(err)
		}
		tstmt2 := tx.Stmtx(stmt2)
		row2 := tstmt2.QueryRowx("Jason")
		err = row2.StructScan(&jason)
		if err != nil {
			t.Error(err)
		}
		tx.Commit()

		places := []*Place{}
		err = db.Select(&places, "SELECT telcode FROM place ORDER BY telcode ASC")
		if err != nil {
			t.Fatal(err)
		}

		usa, singsing, honkers := places[0], places[1], places[2]

		if usa.TelCode != 1 || honkers.TelCode != 852 || singsing.TelCode != 65 {
			t.Errorf("Expected integer telcodes to work, got %#v", places)
		}

		placesptr := []PlacePtr{}
		err = db.Select(&placesptr, "SELECT * FROM place ORDER BY telcode ASC")
		if err != nil {
			t.Error(err)
		}
		// fmt.Printf("%#v\n%#v\n%#v\n", placesptr[0], placesptr[1], placesptr[2])

		// if you have null fields and use SELECT *, you must use sql.Null* in your struct
		// this test also verifies that you can use either a []Struct{} or a []*Struct{}
		places2 := []Place{}
		err = db.Select(&places2, "SELECT * FROM place ORDER BY telcode ASC")
		if err != nil {
			t.Fatal(err)
		}

		usa, singsing, honkers = &places2[0], &places2[1], &places2[2]

		// this should return a type error that &p is not a pointer to a struct slice
		p := Place{}
		err = db.Select(&p, "SELECT * FROM place ORDER BY telcode ASC")
		if err == nil {
			t.Errorf("Expected an error, argument to select should be a pointer to a struct slice")
		}

		// this should be an error
		pl := []Place{}
		err = db.Select(pl, "SELECT * FROM place ORDER BY telcode ASC")
		if err == nil {
			t.Errorf("Expected an error, argument to select should be a pointer to a struct slice, not a slice.")
		}

		if usa.TelCode != 1 || honkers.TelCode != 852 || singsing.TelCode != 65 {
			t.Errorf("Expected integer telcodes to work, got %#v", places)
		}

		stmt, err := db.Preparex(db.Rebind("SELECT country, telcode FROM place WHERE telcode > ? ORDER BY telcode ASC"))
		if err != nil {
			t.Error(err)
		}

		places = []*Place{}
		err = stmt.Select(&places, 10)
		if len(places) != 2 {
			t.Error("Expected 2 places, got 0.")
		}
		if err != nil {
			t.Fatal(err)
		}
		singsing, honkers = places[0], places[1]
		if singsing.TelCode != 65 || honkers.TelCode != 852 {
			t.Errorf("Expected the right telcodes, got %#v", places)
		}

		rows, err := db.Queryx("SELECT * FROM place")
		if err != nil {
			t.Fatal(err)
		}
		place := Place{}
		for rows.Next() {
			err = rows.StructScan(&place)
			if err != nil {
				t.Fatal(err)
			}
		}

		rows, err = db.Queryx("SELECT * FROM place")
		if err != nil {
			t.Fatal(err)
		}
		m := map[string]any{}
		for rows.Next() {
			err = rows.MapScan(m)
			if err != nil {
				t.Fatal(err)
			}
			_, ok := m["country"]
			if !ok {
				t.Errorf("Expected key `country` in map but could not find it (%#v)\n", m)
			}
		}

		rows, err = db.Queryx("SELECT * FROM place")
		if err != nil {
			t.Fatal(err)
		}
		for rows.Next() {
			s, err := rows.SliceScan()
			if err != nil {
				t.Error(err)
			}
			if len(s) != 3 {
				t.Errorf("Expected 3 columns in result, got %d\n", len(s))
			}
		}

		// test advanced querying
		// test that NamedExec works with a map as well as a struct
		_, err = db.NamedExec("INSERT INTO person (first_name, last_name, email) VALUES (:first, :last, :email)", map[string]any{
			"first": "Bin",
			"last":  "Smuth",
			"email": "bensmith@ex.co",
		})
		if err != nil {
			t.Fatal(err)
		}

		// ensure that if the named param happens right at the end it still works
		// ensure that NamedQuery works with a map[string]interface{}
		rows, err = db.NamedQuery("SELECT * FROM person WHERE first_name=:first", map[string]any{"first": "Bin"})
		if err != nil {
			t.Fatal(err)
		}

		ben := &Person{}
		for rows.Next() {
			err = rows.StructScan(ben)
			if err != nil {
				t.Fatal(err)
			}
			if ben.FirstName != "Bin" {
				t.Fatal("Expected first name of `Bin`, got " + ben.FirstName)
			}
			if ben.LastName != "Smuth" {
				t.Fatal("Expected first name of `Smuth`, got " + ben.LastName)
			}
		}

		ben.FirstName = "Ben"
		ben.LastName = "Smith"
		ben.Email = "binsmuth@ex.co"

		// Insert via a named query using the struct
		_, err = db.NamedExec("INSERT INTO person (first_name, last_name, email) VALUES (:first_name, :last_name, :email)", ben)

		if err != nil {
			t.Fatal(err)
		}

		rows, err = db.NamedQuery("SELECT * FROM person WHERE first_name=:first_name", ben)
		if err != nil {
			t.Fatal(err)
		}
		for rows.Next() {
			err = rows.StructScan(ben)
			if err != nil {
				t.Fatal(err)
			}
			if ben.FirstName != "Ben" {
				t.Fatal("Expected first name of `Ben`, got " + ben.FirstName)
			}
			if ben.LastName != "Smith" {
				t.Fatal("Expected first name of `Smith`, got " + ben.LastName)
			}
		}
		// ensure that Get does not panic on emppty result set
		person := &Person{}
		err = db.Get(person, "SELECT * FROM person WHERE first_name=$1", "does-not-exist")
		if err == nil {
			t.Fatal("Should have got an error for Get on non-existent row.")
		}

		// lets test prepared statements some more

		stmt, err = db.Preparex(db.Rebind("SELECT * FROM person WHERE first_name=?"))
		if err != nil {
			t.Fatal(err)
		}
		rows, err = stmt.Queryx("Ben")
		if err != nil {
			t.Fatal(err)
		}
		for rows.Next() {
			err = rows.StructScan(ben)
			if err != nil {
				t.Fatal(err)
			}
			if ben.FirstName != "Ben" {
				t.Fatal("Expected first name of `Ben`, got " + ben.FirstName)
			}
			if ben.LastName != "Smith" {
				t.Fatal("Expected first name of `Smith`, got " + ben.LastName)
			}
		}

		john = Person{}
		stmt, err = db.Preparex(db.Rebind("SELECT * FROM person WHERE first_name=?"))
		if err != nil {
			t.Error(err)
		}
		err = stmt.Get(&john, "John")
		if err != nil {
			t.Error(err)
		}

		// test name mapping
		// THIS USED TO WORK BUT WILL NO LONGER WORK.
		db.MapperFunc(strings.ToUpper)
		rsa := CPlace{}
		err = db.Get(&rsa, "SELECT * FROM capplace;")
		if err != nil {
			t.Error(err, "in db:", db.DriverName())
		}
		db.MapperFunc(strings.ToLower)

		// create a copy and change the mapper, then verify the copy behaves
		// differently from the original.
		dbCopy := NewDb(db.DB, db.DriverName())
		dbCopy.MapperFunc(strings.ToUpper)
		err = dbCopy.Get(&rsa, "SELECT * FROM capplace;")
		if err != nil {
			fmt.Println(db.DriverName())
			t.Error(err)
		}

		err = db.Get(&rsa, "SELECT * FROM cappplace;")
		if err == nil {
			t.Error("Expected no error, got ", err)
		}

		// test base type slices
		var sdest []string
		rows, err = db.Queryx("SELECT email FROM person ORDER BY email ASC;")
		if err != nil {
			t.Error(err)
		}
		err = scanAll(rows, &sdest, false)
		if err != nil {
			t.Error(err)
		}

		// test Get with base types
		var count int
		err = db.Get(&count, "SELECT count(*) FROM person;")
		if err != nil {
			t.Error(err)
		}
		if count != len(sdest) {
			t.Errorf("Expected %d == %d (count(*) vs len(SELECT ..)", count, len(sdest))
		}

		// test Get and Select with time.Time, #84
		var addedAt time.Time
		err = db.Get(&addedAt, "SELECT added_at FROM person LIMIT 1;")
		if err != nil {
			t.Error(err)
		}

		var addedAts []time.Time
		err = db.Select(&addedAts, "SELECT added_at FROM person;")
		if err != nil {
			t.Error(err)
		}

		// test it on a double pointer
		var pcount *int
		err = db.Get(&pcount, "SELECT count(*) FROM person;")
		if err != nil {
			t.Error(err)
		}
		if *pcount != count {
			t.Errorf("expected %d = %d", *pcount, count)
		}

		// test Select...
		sdest = []string{}
		err = db.Select(&sdest, "SELECT first_name FROM person ORDER BY first_name ASC;")
		if err != nil {
			t.Error(err)
		}
		expected := []string{"Ben", "Bin", "Jason", "John"}
		for i, got := range sdest {
			if got != expected[i] {
				t.Errorf("Expected %d result to be %s, but got %s", i, expected[i], got)
			}
		}

		var nsdest []sql.NullString
		err = db.Select(&nsdest, "SELECT city FROM place ORDER BY city ASC")
		if err != nil {
			t.Error(err)
		}
		for _, val := range nsdest {
			if val.Valid && val.String != "New York" {
				t.Errorf("expected single valid result to be `New York`, but got %s", val.String)
			}
		}

		city, err := One[string](db, "SELECT city FROM place where city is not null")
		assert.NoError(t, err)
		assert.Equal(t, "New York", city)

		cities, err := List[string](db, "SELECT city FROM place where city is not null")
		assert.NoError(t, err)
		assert.Equal(t, []string{"New York"}, cities)
	})
}

type Product struct {
	ProductID int
}

// tests that sqlx will not panic when the wrong driver is passed because
// of an automatic nil dereference in sqlx.Open(), which was fixed.
func TestDoNotPanicOnConnect(t *testing.T) {
	db, err := Connect("bogus", "hehe")
	if err == nil {
		t.Errorf("Should return error when using bogus driverName")
	}
	if db != nil {
		t.Errorf("Should not return the db on a connect failure")
	}
}

func TestRebind(t *testing.T) {
	q1 := `INSERT INTO foo (a, b, c, d, e, f, g, h, i) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`
	q2 := `INSERT INTO foo (a, b, c) VALUES (?, ?, "foo"), ("Hi", ?, ?)`

	s1 := Rebind(DOLLAR, q1)
	s2 := Rebind(DOLLAR, q2)

	if s1 != `INSERT INTO foo (a, b, c, d, e, f, g, h, i) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)` {
		t.Errorf("q1 failed")
	}

	if s2 != `INSERT INTO foo (a, b, c) VALUES ($1, $2, "foo"), ("Hi", $3, $4)` {
		t.Errorf("q2 failed")
	}

	s1 = Rebind(AT, q1)
	s2 = Rebind(AT, q2)

	if s1 != `INSERT INTO foo (a, b, c, d, e, f, g, h, i) VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10)` {
		t.Errorf("q1 failed")
	}

	if s2 != `INSERT INTO foo (a, b, c) VALUES (@p1, @p2, "foo"), ("Hi", @p3, @p4)` {
		t.Errorf("q2 failed")
	}

	s1 = Rebind(NAMED, q1)
	s2 = Rebind(NAMED, q2)

	ex1 := `INSERT INTO foo (a, b, c, d, e, f, g, h, i) VALUES ` +
		`(:arg1, :arg2, :arg3, :arg4, :arg5, :arg6, :arg7, :arg8, :arg9, :arg10)`
	if s1 != ex1 {
		t.Errorf("q1 failed on Named params: %s", s1)
	}

	ex2 := `INSERT INTO foo (a, b, c) VALUES (:arg1, :arg2, "foo"), ("Hi", :arg3, :arg4)`
	if s2 != ex2 {
		t.Error("q2 failed on Named params")
	}
}

func TestBindMap(t *testing.T) {
	// Test that it works..
	q1 := `INSERT INTO foo (a, b, c, d) VALUES (:name, :age, :first, :last)`
	am := map[string]any{
		"name":  "Jason Moiron",
		"age":   30,
		"first": "Jason",
		"last":  "Moiron",
	}

	bq, args, _ := bindMap(QUESTION, q1, am)
	expect := `INSERT INTO foo (a, b, c, d) VALUES (?, ?, ?, ?)`
	if bq != expect {
		t.Errorf("Interpolation of query failed: got `%v`, expected `%v`\n", bq, expect)
	}

	if args[0].(string) != "Jason Moiron" {
		t.Errorf("Expected `Jason Moiron`, got %v\n", args[0])
	}

	if args[1].(int) != 30 {
		t.Errorf("Expected 30, got %v\n", args[1])
	}

	if args[2].(string) != "Jason" {
		t.Errorf("Expected Jason, got %v\n", args[2])
	}

	if args[3].(string) != "Moiron" {
		t.Errorf("Expected Moiron, got %v\n", args[3])
	}
}

// Test for #117, embedded nil maps

type Message struct {
	Text       string      `db:"string"`
	Properties PropertyMap `db:"properties"` // Stored as JSON in the database
}

type PropertyMap map[string]string

// Implement driver.Valuer and sql.Scanner interfaces on PropertyMap
func (p PropertyMap) Value() (driver.Value, error) {
	if len(p) == 0 {
		return nil, nil
	}
	return json.Marshal(p)
}

func (p PropertyMap) Scan(src any) error {
	v := reflect.ValueOf(src)
	if !v.IsValid() || v.CanAddr() && v.IsNil() {
		return nil
	}
	switch ts := src.(type) {
	case []byte:
		return json.Unmarshal(ts, &p)
	case string:
		return json.Unmarshal([]byte(ts), &p)
	default:
		return fmt.Errorf("Could not not decode type %T -> %T", src, p)
	}
}

func TestEmbeddedMaps(t *testing.T) {
	schema := Schema{
		create: `
			CREATE TABLE message (
				string text,
				properties text
			);`,
		drop: `drop table message;`,
	}

	RunWithSchema(schema, t, func(db *DB, t *testing.T, now string) {
		messages := []Message{
			{"Hello, World", PropertyMap{"one": "1", "two": "2"}},
			{"Thanks, Joy", PropertyMap{"pull": "request"}},
		}
		q1 := `INSERT INTO message (string, properties) VALUES (:string, :properties);`
		for _, m := range messages {
			_, err := db.NamedExec(q1, m)
			if err != nil {
				t.Fatal(err)
			}
		}
		var count int
		err := db.Get(&count, "SELECT count(*) FROM message")
		if err != nil {
			t.Fatal(err)
		}
		if count != len(messages) {
			t.Fatalf("Expected %d messages in DB, found %d", len(messages), count)
		}

		var m Message
		err = db.Get(&m, "SELECT * FROM message LIMIT 1;")
		if err != nil {
			t.Fatal(err)
		}
		if m.Properties == nil {
			t.Fatal("Expected m.Properties to not be nil, but it was.")
		}
	})
}

type Valuer struct {
	Val string
}

func (v Valuer) Value() (driver.Value, error) {
	return v.Val, nil
}

func TestIn(t *testing.T) {
	// some quite normal situations
	type tr struct {
		q    string
		args []any
		c    int
	}
	tests := []tr{
		{
			"SELECT * FROM foo WHERE x = ? AND v in (?) AND y = ?",
			[]any{"foo", []int{0, 5, 7, 2, 9}, "bar"},
			7,
		},
		{
			"SELECT * FROM foo WHERE x in (?)",
			[]any{[]int{1, 2, 3, 4, 5, 6, 7, 8}},
			8,
		},
		{
			"SELECT * FROM foo WHERE x = ? AND y in (?)",
			[]any{[]byte("foo"), []int{0, 5, 3}},
			4,
		},
		{
			"SELECT * FROM foo WHERE x = ? AND y IN (?)",
			[]any{sql.NullString{Valid: false}, []string{"a", "b"}},
			3,
		},
	}
	for _, test := range tests {
		q, a, err := In(test.q, test.args...)
		if err != nil {
			t.Error(err)
		}
		if len(a) != test.c {
			t.Errorf("Expected %d args, but got %d (%+v)", test.c, len(a), a)
		}
		if strings.Count(q, "?") != test.c {
			t.Errorf("Expected %d bindVars, got %d", test.c, strings.Count(q, "?"))
		}
	}

	// nil driver.Valuer
	t.Run("with nil driver.Valuer", func(t *testing.T) {
		query := `SELECT * FROM foo WHERE x = ? or y IN (?)`
		_, _, err := In(query,
			(*Valuer)(nil), // a non-inited pointer to valuer
			[]any{
				"a",                 // a usual value
				nil,                 // a nil value
				Valuer{Val: "foo"},  // a Valuer
				&Valuer{Val: "foo"}, // a pointer to valuer
				(*Valuer)(nil),      // a non-inited pointer to valuer
			},
		)
		if err != nil {
			t.Error(err)
		}
	})

	// too many bindVars, but no slices, so short circuits parsing
	// i'm not sure if this is the right behavior;  this query/arg combo
	// might not work, but we shouldn't parse if we don't need to
	{
		orig := "SELECT * FROM foo WHERE x = ? AND y = ?"
		q, a, err := In(orig, "foo", "bar", "baz")
		if err != nil {
			t.Error(err)
		}
		if len(a) != 3 {
			t.Errorf("Expected 3 args, but got %d (%+v)", len(a), a)
		}
		if q != orig {
			t.Error("Expected unchanged query.")
		}
	}

	tests = []tr{
		// too many bindvars;  slice present so should return error during parse
		{
			"SELECT * FROM foo WHERE x = ? and y = ?",
			[]any{"foo", []int{1, 2, 3}, "bar"},
			0,
		},
		// empty slice, should return error before parse
		{
			"SELECT * FROM foo WHERE x = ?",
			[]any{[]int{}},
			0,
		},
		// too *few* bindvars, should return an error
		{
			"SELECT * FROM foo WHERE x = ? AND y in (?)",
			[]any{[]int{1, 2, 3}},
			0,
		},
	}
	for _, test := range tests {
		_, _, err := In(test.q, test.args...)
		if err == nil {
			t.Error("Expected an error, but got nil.")
		}
	}
	RunWithSchema(defaultSchema, t, func(db *DB, t *testing.T, now string) {
		loadDefaultFixture(db, t)
		// tx.MustExec(tx.Rebind("INSERT INTO place (country, city, telcode) VALUES (?, ?, ?)"), "United States", "New York", "1")
		// tx.MustExec(tx.Rebind("INSERT INTO place (country, telcode) VALUES (?, ?)"), "Hong Kong", "852")
		// tx.MustExec(tx.Rebind("INSERT INTO place (country, telcode) VALUES (?, ?)"), "Singapore", "65")
		telcodes := []int{852, 65}
		q := "SELECT * FROM place WHERE telcode IN(?) ORDER BY telcode"
		query, args, err := In(q, telcodes)
		if err != nil {
			t.Error(err)
		}
		query = db.Rebind(query)
		places := []Place{}
		err = db.Select(&places, query, args...)
		if err != nil {
			t.Error(err)
		}
		if len(places) != 2 {
			t.Fatalf("Expecting 2 results, got %d", len(places))
		}
		if places[0].TelCode != 65 {
			t.Errorf("Expecting singapore first, but got %#v", places[0])
		}
		if places[1].TelCode != 852 {
			t.Errorf("Expecting hong kong second, but got %#v", places[1])
		}
	})
}

func TestBindStruct(t *testing.T) {
	var err error

	q1 := `INSERT INTO foo (a, b, c, d) VALUES (:name, :age, :first, :last)`

	type tt struct {
		Name  string
		Age   int
		First string
		Last  string
	}

	type tt2 struct {
		Field1 string `db:"field_1"`
		Field2 string `db:"field_2"`
	}

	type tt3 struct {
		tt2
		Name string
	}

	am := tt{"Jason Moiron", 30, "Jason", "Moiron"}

	bq, args, _ := bindStruct(QUESTION, q1, am, mapper())
	expect := `INSERT INTO foo (a, b, c, d) VALUES (?, ?, ?, ?)`
	if bq != expect {
		t.Errorf("Interpolation of query failed: got `%v`, expected `%v`\n", bq, expect)
	}

	if args[0].(string) != "Jason Moiron" {
		t.Errorf("Expected `Jason Moiron`, got %v\n", args[0])
	}

	if args[1].(int) != 30 {
		t.Errorf("Expected 30, got %v\n", args[1])
	}

	if args[2].(string) != "Jason" {
		t.Errorf("Expected Jason, got %v\n", args[2])
	}

	if args[3].(string) != "Moiron" {
		t.Errorf("Expected Moiron, got %v\n", args[3])
	}

	am2 := tt2{"Hello", "World"}
	bq, args, _ = bindStruct(QUESTION, "INSERT INTO foo (a, b) VALUES (:field_2, :field_1)", am2, mapper())
	expect = `INSERT INTO foo (a, b) VALUES (?, ?)`
	if bq != expect {
		t.Errorf("Interpolation of query failed: got `%v`, expected `%v`\n", bq, expect)
	}

	if args[0].(string) != "World" {
		t.Errorf("Expected 'World', got %s\n", args[0].(string))
	}
	if args[1].(string) != "Hello" {
		t.Errorf("Expected 'Hello', got %s\n", args[1].(string))
	}

	am3 := tt3{Name: "Hello!"}
	am3.Field1 = "Hello"
	am3.Field2 = "World"

	bq, args, err = bindStruct(QUESTION, "INSERT INTO foo (a, b, c) VALUES (:name, :field_1, :field_2)", am3, mapper())

	if err != nil {
		t.Fatal(err)
	}

	expect = `INSERT INTO foo (a, b, c) VALUES (?, ?, ?)`
	if bq != expect {
		t.Errorf("Interpolation of query failed: got `%v`, expected `%v`\n", bq, expect)
	}

	if args[0].(string) != "Hello!" {
		t.Errorf("Expected 'Hello!', got %s\n", args[0].(string))
	}
	if args[1].(string) != "Hello" {
		t.Errorf("Expected 'Hello', got %s\n", args[1].(string))
	}
	if args[2].(string) != "World" {
		t.Errorf("Expected 'World', got %s\n", args[0].(string))
	}
}

func TestEmbeddedLiterals(t *testing.T) {
	schema := Schema{
		create: `
			CREATE TABLE x (
				k text
			);`,
		drop: `drop table x;`,
	}

	RunWithSchema(schema, t, func(db *DB, t *testing.T, now string) {
		type t1 struct {
			K *string
		}
		type t2 struct {
			Inline struct {
				F string
			}
			K *string
		}

		db.MustExec(db.Rebind("INSERT INTO x (k) VALUES (?), (?), (?);"), "one", "two", "three")

		target := t1{}
		err := db.Get(&target, db.Rebind("SELECT * FROM x WHERE k=?"), "one")
		if err != nil {
			t.Error(err)
		}
		if *target.K != "one" {
			t.Error("Expected target.K to be `one`, got ", target.K)
		}

		target2 := t2{}
		err = db.Get(&target2, db.Rebind("SELECT * FROM x WHERE k=?"), "one")
		if err != nil {
			t.Error(err)
		}
		if *target2.K != "one" {
			t.Errorf("Expected target2.K to be `one`, got `%v`", target2.K)
		}
	})
}

// TestGet tests to ensure that Get behaves correctly for
// single row and multi row results.
func TestGet(t *testing.T) {
	var schema = Schema{
		create: `CREATE TABLE tst (v integer);`,
		drop:   `drop table tst;`,
	}

	RunWithSchema(schema, t, func(db *DB, t *testing.T, now string) {
		for _, v := range []int{1, 2} {
			_, err := db.Exec(db.Rebind("INSERT INTO tst (v) VALUES (?)"), v)
			if err != nil {
				t.Error(err)
			}
		}

		tests := []struct {
			name string
			val  int
			err  bool
		}{
			{"multi-rows", 1, true},
			{"single-row", 2, false},
		}
		for _, tc := range tests {
			t.Run(tc.name, func(t *testing.T) {
				var v int
				err := db.Get(&v, db.Rebind("SELECT v FROM tst WHERE v >= ?"), tc.val)
				if tc.err {
					if err == nil {
						t.Error("expected error but got nil")
					}
				} else if err != nil {
					t.Error("unexpected error:", err)
				}
			})
		}
	})
}

// TestOne tests to ensure that One behaves correctly for
// single row and multi row results.
func TestOne(t *testing.T) {
	var schema = Schema{
		create: `CREATE TABLE tst (v integer);`,
		drop:   `drop table tst;`,
	}

	RunWithSchema(schema, t, func(db *DB, t *testing.T, now string) {
		for v := range 3 {
			_, err := db.Exec(db.Rebind("INSERT INTO tst (v) VALUES (?)"), v)
			require.NoError(t, err)
		}

		tests := []struct {
			name string
			val  int
			err  bool
		}{
			{"multi-rows", 1, true},
			{"single-row", 2, false},
		}
		for _, tc := range tests {
			t.Run(tc.name, func(t *testing.T) {
				_, err := One[int](db, db.Rebind("SELECT v FROM tst WHERE v >= ?"), tc.val)
				if tc.err {
					if err == nil {
						t.Error("expected error but got nil")
					}
				} else if err != nil {
					t.Error("unexpected error:", err)
				}
			})
		}
	})
}

// TestList tests to ensure that List behaves correctly for
// single row and multi row results.
func TestList(t *testing.T) {
	var schema = Schema{
		create: `CREATE TABLE testlist (v integer);`,
		drop:   `drop table testlist;`,
	}

	RunWithSchema(schema, t, func(db *DB, t *testing.T, now string) {
		for v := range 4 {
			_, err := db.Exec(db.Rebind("INSERT INTO testlist (v) VALUES (?)"), v)
			require.NoError(t, err)
		}

		tests := []struct {
			name     string
			expected []int
			val      int
			err      bool
		}{
			{"multi-rows", []int{1, 2, 3}, 1, false},
			{"single-row", []int{}, 22, false},
		}
		for _, tc := range tests {
			t.Run(tc.name, func(t *testing.T) {
				val, err := List[int](db, db.Rebind("SELECT v FROM testlist WHERE v >= ?"), tc.val)
				if tc.err {
					require.Error(t, err)
				} else if err != nil {
					require.NoError(t, err)
					assert.Equal(t, tc.expected, val, "expected %v, got %v", tc.expected, val)
				}
			})
		}
	})
}

func BenchmarkBindStruct(b *testing.B) {
	b.StopTimer()
	q1 := `INSERT INTO foo (a, b, c, d) VALUES (:name, :age, :first, :last)`
	type t struct {
		Name  string
		Age   int
		First string
		Last  string
	}
	am := t{"Jason Moiron", 30, "Jason", "Moiron"}
	b.StartTimer()
	for i := 0; i < b.N; i++ {
		bindStruct(DOLLAR, q1, am, mapper())
	}
}

func TestBindNamedMapper(t *testing.T) {
	type A map[string]any
	m := reflectx.NewMapperFunc("db", NameMapper)
	query, args, err := bindNamedMapper(DOLLAR, `select :x`, A{
		"x": "X!",
	}, m)
	if err != nil {
		t.Fatal(err)
	}

	got := fmt.Sprintf("%s %s", query, args)
	want := `select $1 [X!]`
	if got != want {
		t.Errorf("\ngot:  %q\nwant: %q", got, want)
	}

	_, _, err = bindNamedMapper(DOLLAR, `select :x`, map[string]string{
		"x": "X!",
	}, m)
	if err == nil {
		t.Fatal("err is nil")
	}
	if !strings.Contains(err.Error(), "unsupported map type") {
		t.Errorf("wrong error: %s", err)
	}
}

func BenchmarkBindMap(b *testing.B) {
	b.StopTimer()
	q1 := `INSERT INTO foo (a, b, c, d) VALUES (:name, :age, :first, :last)`
	am := map[string]any{
		"name":  "Jason Moiron",
		"age":   30,
		"first": "Jason",
		"last":  "Moiron",
	}
	b.StartTimer()
	for i := 0; i < b.N; i++ {
		bindMap(DOLLAR, q1, am)
	}
}

func BenchmarkIn(b *testing.B) {
	q := `SELECT * FROM foo WHERE x = ? AND v in (?) AND y = ?`

	for i := 0; i < b.N; i++ {
		_, _, _ = In(q, []any{"foo", []int{0, 5, 7, 2, 9}, "bar"}...)
	}
}

func BenchmarkIn1k(b *testing.B) {
	q := `SELECT * FROM foo WHERE x = ? AND v in (?) AND y = ?`

	var vals [1000]any

	for i := 0; i < b.N; i++ {
		_, _, _ = In(q, []any{"foo", vals[:], "bar"}...)
	}
}

func BenchmarkIn1kInt(b *testing.B) {
	q := `SELECT * FROM foo WHERE x = ? AND v in (?) AND y = ?`

	var vals [1000]int

	for i := 0; i < b.N; i++ {
		_, _, _ = In(q, []any{"foo", vals[:], "bar"}...)
	}
}

func BenchmarkIn1kString(b *testing.B) {
	q := `SELECT * FROM foo WHERE x = ? AND v in (?) AND y = ?`

	var vals [1000]string

	for i := 0; i < b.N; i++ {
		_, _, _ = In(q, []any{"foo", vals[:], "bar"}...)
	}
}

func BenchmarkRebind(b *testing.B) {
	b.StopTimer()
	q1 := `INSERT INTO foo (a, b, c, d, e, f, g, h, i) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`
	q2 := `INSERT INTO foo (a, b, c) VALUES (?, ?, "foo"), ("Hi", ?, ?)`
	b.StartTimer()

	for i := 0; i < b.N; i++ {
		Rebind(DOLLAR, q1)
		Rebind(DOLLAR, q2)
	}
}

func TestIn130Regression(t *testing.T) {
	t.Run("[]interface{}{}", func(t *testing.T) {
		q, args, err := In("SELECT * FROM people WHERE name IN (?)", []any{[]string{"gopher"}}...)
		if err != nil {
			t.Fatal(err)
		}
		if q != "SELECT * FROM people WHERE name IN (?)" {
			t.Errorf("got=%v", q)
		}
		t.Log(args)
		for _, a := range args {
			switch a := a.(type) {
			case string:
				t.Log("ok: string", a)
			case *string:
				t.Error("ng: string pointer", a, *a)
			}
		}
	})

	t.Run("[]string{}", func(t *testing.T) {
		q, args, err := In("SELECT * FROM people WHERE name IN (?)", []string{"gopher"})
		if err != nil {
			t.Fatal(err)
		}
		if q != "SELECT * FROM people WHERE name IN (?)" {
			t.Errorf("got=%v", q)
		}
		t.Log(args)
		for _, a := range args {
			switch a := a.(type) {
			case string:
				t.Log("ok: string", a)
			case *string:
				t.Error("ng: string pointer", a, *a)
			}
		}
	})
}

func TestMultiResultSet(t *testing.T) {
	RunWithSchema(defaultSchema, t, func(db *DB, t *testing.T, now string) {
		var sp, sql string

		loadDefaultFixture(db, t)

		switch db.DriverName() {
		case "mysql":
			sp = `
				CREATE PROCEDURE multiQuery()
				BEGIN
					SELECT first_name, last_name, email FROM person LIMIT 1;
					SELECT country, telcode FROM place LIMIT 1;
				END
			`
			sql = "CALL multiQuery();"
		case "postgres":
			sql = `
				SELECT first_name, last_name, email FROM person LIMIT 1;
				SELECT country, telcode FROM place LIMIT 1;
			`
		default:
			// sqlite3 and postgres do not support multiple result sets with different row layout.
			return
		}

		type Person struct {
			FirstName string `db:"first_name"`
			LastName  string `db:"last_name"`
			Email     string `db:"email"`
		}
		type Place struct {
			Country string `db:"country"`
			TelCode string `db:"telcode"`
		}

		if sp != "" {
			if _, err := db.Exec("DROP PROCEDURE IF EXISTS multiQuery"); err != nil {
				t.Fatal(err)
			}

			if _, err := db.Exec(sp); err != nil {
				t.Fatal(err)
			}
		}

		rows, err := db.Queryx(sql)
		if err != nil {
			t.Fatal(err)
		}
		defer rows.Close()

		var (
			person Person
			place  Place
		)

		var i int
		for rows.Next() {
			i++
			if err = rows.StructScan(&person); err != nil {
				t.Fatal(err)
			}
		}
		if i < 1 {
			t.Errorf("[%s] Expected at least one record, got none", db.DriverName())
		}

		if !rows.NextResultSet() {
			t.Fatalf("[%s] Expected a second recordset, got one", db.DriverName())
		}

		i = 0
		for rows.Next() {
			i++
			if err = rows.StructScan(&place); err != nil {
				t.Fatal(err)
			}
		}
		if i < 1 {
			t.Errorf("Expected at least one records, got none")
		}

		// nest no next
		rows2, err := db.Queryx("SELECT first_name, last_name, email FROM person LIMIT 2;")
		if err != nil {
			t.Fatal(err)
		}
		defer rows2.Close()

		i = 0
		for rows2.Next() {
			i++
			if err = rows2.StructScan(&person); err != nil {
				t.Fatal(err)
			}
		}
		if i < 2 {
			t.Errorf("[%s] Expected at least 2 records, got %d", db.DriverName(), i)
		}
		if rows.NextResultSet() {
			t.Fatalf("[%s] Did not expected a second recordset", db.DriverName())
		}
	})
}

func TestSelectReset(t *testing.T) {
	RunWithSchema(defaultSchema, t, func(db *DB, t *testing.T, now string) {
		loadDefaultFixture(db, t)

		filledDest := []string{"a", "b", "c"}
		err := db.Select(&filledDest, "SELECT first_name FROM person ORDER BY first_name ASC;")
		if err != nil {
			t.Fatal(err)
		}
		if len(filledDest) != 2 {
			t.Errorf("Expected 2 first names, got %d.", len(filledDest))
		}
		expected := []string{"Jason", "John"}
		for i, got := range filledDest {
			if got != expected[i] {
				t.Errorf("Expected %d result to be %s, but got %s.", i, expected[i], got)
			}
		}

		var emptyDest []string
		err = db.Select(&emptyDest, "SELECT first_name FROM person WHERE first_name = 'Jack';")
		if err != nil {
			t.Fatal(err)
		}
		// Verify that selecting 0 rows into a nil target didn't create a
		// non-nil slice.
		if emptyDest != nil {
			t.Error("Expected emptyDest to be nil")
		}
	})
}

func TestQueryable(t *testing.T) {
	sqlDBType := reflect.TypeFor[*sql.DB]()
	dbType := reflect.TypeFor[*DB]()
	sqlTxType := reflect.TypeFor[*sql.Tx]()
	txType := reflect.TypeFor[*Tx]()

	dbMethods := exportableMethods(sqlDBType)
	maps.Copy(dbMethods, exportableMethods(dbType))

	txMethods := exportableMethods(sqlTxType)
	maps.Copy(txMethods, exportableMethods(txType))

	sharedMethods := make([]string, 0)

	for name, dbMethod := range dbMethods {
		if txMethod, ok := txMethods[name]; ok {
			if methodsEqual(dbMethod.Type, txMethod.Type) {
				sharedMethods = append(sharedMethods, name)
			}
		}
	}

	queryableType := reflect.TypeFor[Queryable]()
	queryableMethods := exportableMethods(queryableType)

	for _, sharedMethodName := range sharedMethods {
		if _, ok := queryableMethods[sharedMethodName]; !ok {
			t.Errorf("Queryable does not include shared DB/Tx method: %s", sharedMethodName)
		}
	}
}

func TestAllRows(t *testing.T) {
	type Simpleton struct {
		FirstName   string `db:"first_name"`
		LastName    string `db:"last_name"`
		Unscannable []string
	}

	testCases := []struct {
		name     string
		sql      string
		expected []Simpleton
		wantErr  string
	}{
		{
			name: "Two rows",
			sql:  "SELECT first_name, last_name FROM person order by first_name",
			expected: []Simpleton{
				{FirstName: "Jason", LastName: "Moiron"},
				{FirstName: "John", LastName: "Doe"},
			},
		},
		{
			name:     "No rows",
			sql:      "SELECT first_name, last_name FROM person where first_name = 'nope'",
			expected: []Simpleton{},
		},
		{
			name:    "Has struct scan error",
			sql:     "SELECT first_name, 2 as unscannable FROM person",
			wantErr: "sql: Scan error on column index 1, name \"unscannable\": unsupported Scan, storing driver.Value type int64 into type *[]string",
		},
	}

	for _, tc := range testCases {
		t.Run(tc.name, func(t *testing.T) {
			RunWithSchema(defaultSchema, t, func(db *DB, t *testing.T, now string) {
				loadDefaultFixture(db, t)
				rows, err := db.Queryx(tc.sql)
				if err != nil {
					t.Fatalf("Failed to query database: %v", err)
				}

				// Collect the results
				results := make([]Simpleton, 0)
				for person, err := range AllRows[Simpleton](rows) {
					if tc.wantErr == "" {
						require.NoError(t, err)
					} else if err != nil && tc.wantErr != "" {
						require.Equal(t, tc.wantErr, err.Error())
					}
					results = append(results, person)
				}

				if tc.wantErr == "" {
					assert.Equal(t, tc.expected, results)
				}
			})
		})
	}
}

func exportableMethods(t reflect.Type) map[string]reflect.Method {
	methods := make(map[string]reflect.Method)

	for i := 0; i < t.NumMethod(); i++ {
		method := t.Method(i)

		if method.IsExported() {
			methods[method.Name] = method
		}
	}

	return methods
}

func methodsEqual(t reflect.Type, ot reflect.Type) bool {
	if t.NumIn() != ot.NumIn() || t.NumOut() != ot.NumOut() || t.IsVariadic() != ot.IsVariadic() {
		return false
	}

	// Start at 1 to avoid comparing receiver argument
	for i := 1; i < t.NumIn(); i++ {
		if t.In(i) != ot.In(i) {
			return false
		}
	}

	for i := 0; i < t.NumOut(); i++ {
		if t.Out(i) != ot.Out(i) {
			return false
		}
	}

	return true
}