enginetest

package
v0.6.1 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Jun 12, 2020 License: Apache-2.0 Imports: 18 Imported by: 0

Documentation

Overview

Example 
package main

import (
	"context"
	"fmt"
	"io"

	"github.com/fabiuslabs/go-mysql-server"
	"github.com/fabiuslabs/go-mysql-server/memory"
	"github.com/fabiuslabs/go-mysql-server/sql"
)

func main() {
	e := sqle.NewDefault()
	// Create a test memory database and register it to the default engine.
	e.AddDatabase(createTestDatabase())

	ctx := sql.NewContext(context.Background(), sql.WithIndexRegistry(sql.NewIndexRegistry()), sql.WithViewRegistry(sql.NewViewRegistry())).WithCurrentDB("test")

	_, r, err := e.Query(ctx, `SELECT name, count(*) FROM mytable
	WHERE name = 'John Doe'
	GROUP BY name`)
	checkIfError(err)

	// Iterate results and print them.
	for {
		row, err := r.Next()
		if err == io.EOF {
			break
		}
		checkIfError(err)

		name := row[0]
		count := row[1]

		fmt.Println(name, count)
	}

}

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

func createTestDatabase() sql.Database {
	db := memory.NewDatabase("test")
	table := memory.NewTable("mytable", sql.Schema{
		{Name: "name", Type: sql.Text, Source: "mytable"},
		{Name: "email", Type: sql.Text, Source: "mytable"},
	})
	db.AddTable("mytable", table)
	ctx := sql.NewEmptyContext()

	rows := []sql.Row{
		sql.NewRow("John Doe", "john@doe.com"),
		sql.NewRow("John Doe", "johnalt@doe.com"),
		sql.NewRow("Jane Doe", "jane@doe.com"),
		sql.NewRow("Evil Bob", "evilbob@gmail.com"),
	}

	for _, row := range rows {
		table.Insert(ctx, row)
	}

	return db
}

Output:

John Doe 2

Index

Examples

Constants

This section is empty.

Variables

View Source 
var DeleteErrorTests = []GenericErrorQueryTest{
	{
		"invalid table",
		"DELETE FROM invalidtable WHERE x < 1;",
	},
	{
		"invalid column",
		"DELETE FROM mytable WHERE z = 'dne';",
	},
	{
		"negative limit",
		"DELETE FROM mytable LIMIT -1;",
	},
	{
		"negative offset",
		"DELETE FROM mytable LIMIT 1 OFFSET -1;",
	},
	{
		"missing keyword from",
		"DELETE mytable WHERE id = 1;",
	},
}
View Source 
var DeleteTests = []WriteQueryTest{
	{
		"DELETE FROM mytable;",
		[]sql.Row{{sql.NewOkResult(3)}},
		"SELECT * FROM mytable;",
		nil,
	},
	{
		"DELETE FROM mytable WHERE i = 2;",
		[]sql.Row{{sql.NewOkResult(1)}},
		"SELECT * FROM mytable;",
		[]sql.Row{{int64(1), "first row"}, {int64(3), "third row"}},
	},
	{
		"DELETE FROM mytable WHERE i < 3;",
		[]sql.Row{{sql.NewOkResult(2)}},
		"SELECT * FROM mytable;",
		[]sql.Row{{int64(3), "third row"}},
	},
	{
		"DELETE FROM mytable WHERE i > 1;",
		[]sql.Row{{sql.NewOkResult(2)}},
		"SELECT * FROM mytable;",
		[]sql.Row{{int64(1), "first row"}},
	},
	{
		"DELETE FROM mytable WHERE i <= 2;",
		[]sql.Row{{sql.NewOkResult(2)}},
		"SELECT * FROM mytable;",
		[]sql.Row{{int64(3), "third row"}},
	},
	{
		"DELETE FROM mytable WHERE i >= 2;",
		[]sql.Row{{sql.NewOkResult(2)}},
		"SELECT * FROM mytable;",
		[]sql.Row{{int64(1), "first row"}},
	},
	{
		"DELETE FROM mytable WHERE s = 'first row';",
		[]sql.Row{{sql.NewOkResult(1)}},
		"SELECT * FROM mytable;",
		[]sql.Row{{int64(2), "second row"}, {int64(3), "third row"}},
	},
	{
		"DELETE FROM mytable WHERE s <> 'dne';",
		[]sql.Row{{sql.NewOkResult(3)}},
		"SELECT * FROM mytable;",
		nil,
	},
	{
		"DELETE FROM mytable WHERE s LIKE '%row';",
		[]sql.Row{{sql.NewOkResult(3)}},
		"SELECT * FROM mytable;",
		nil,
	},
	{
		"DELETE FROM mytable WHERE s = 'dne';",
		[]sql.Row{{sql.NewOkResult(0)}},
		"SELECT * FROM mytable;",
		[]sql.Row{{int64(1), "first row"}, {int64(2), "second row"}, {int64(3), "third row"}},
	},
	{
		"DELETE FROM mytable WHERE i = 'invalid';",
		[]sql.Row{{sql.NewOkResult(0)}},
		"SELECT * FROM mytable;",
		[]sql.Row{{int64(1), "first row"}, {int64(2), "second row"}, {int64(3), "third row"}},
	},
	{
		"DELETE FROM mytable ORDER BY i ASC LIMIT 2;",
		[]sql.Row{{sql.NewOkResult(2)}},
		"SELECT * FROM mytable;",
		[]sql.Row{{int64(3), "third row"}},
	},
	{
		"DELETE FROM mytable ORDER BY i DESC LIMIT 1;",
		[]sql.Row{{sql.NewOkResult(1)}},
		"SELECT * FROM mytable;",
		[]sql.Row{{int64(1), "first row"}, {int64(2), "second row"}},
	},
	{
		"DELETE FROM mytable ORDER BY i DESC LIMIT 1 OFFSET 1;",
		[]sql.Row{{sql.NewOkResult(1)}},
		"SELECT * FROM mytable;",
		[]sql.Row{{int64(1), "first row"}, {int64(3), "third row"}},
	},
}
View Source 
var ExplodeQueries = []QueryTest{
	{
		`SELECT a, EXPLODE(b), c FROM t`,
		[]sql.Row{
			{int64(1), "a", "first"},
			{int64(1), "b", "first"},
			{int64(2), "c", "second"},
			{int64(2), "d", "second"},
			{int64(3), "e", "third"},
			{int64(3), "f", "third"},
		},
	},
	{
		`SELECT a, EXPLODE(b) AS x, c FROM t`,
		[]sql.Row{
			{int64(1), "a", "first"},
			{int64(1), "b", "first"},
			{int64(2), "c", "second"},
			{int64(2), "d", "second"},
			{int64(3), "e", "third"},
			{int64(3), "f", "third"},
		},
	},
	{
		`SELECT EXPLODE(SPLIT(c, "")) FROM t LIMIT 5`,
		[]sql.Row{
			{"f"},
			{"i"},
			{"r"},
			{"s"},
			{"t"},
		},
	},
	{
		`SELECT a, EXPLODE(b) AS x, c FROM t WHERE x = 'e'`,
		[]sql.Row{
			{int64(3), "e", "third"},
		},
	},
}
View Source 
var InfoSchemaQueries = []QueryTest{
	{
		`SHOW TABLE STATUS FROM mydb`,
		[]sql.Row{
			{"mytable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8_bin", nil, nil},
			{"othertable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8_bin", nil, nil},
			{"tabletest", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8_bin", nil, nil},
			{"bigtable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8_bin", nil, nil},
			{"floattable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8_bin", nil, nil},
			{"niltable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8_bin", nil, nil},
			{"newlinetable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8_bin", nil, nil},
		},
	},
	{
		`SHOW TABLE STATUS LIKE '%table'`,
		[]sql.Row{
			{"mytable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8_bin", nil, nil},
			{"othertable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8_bin", nil, nil},
			{"bigtable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8_bin", nil, nil},
			{"floattable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8_bin", nil, nil},
			{"niltable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8_bin", nil, nil},
			{"newlinetable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8_bin", nil, nil},
		},
	},
	{
		`SHOW TABLE STATUS WHERE Name = 'mytable'`,
		[]sql.Row{
			{"mytable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8_bin", nil, nil},
		},
	},
	{
		`SHOW TABLE STATUS`,
		[]sql.Row{
			{"mytable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8_bin", nil, nil},
			{"othertable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8_bin", nil, nil},
			{"tabletest", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8_bin", nil, nil},
			{"bigtable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8_bin", nil, nil},
			{"floattable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8_bin", nil, nil},
			{"niltable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8_bin", nil, nil},
			{"newlinetable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8_bin", nil, nil},
		},
	},
	{
		"SHOW TABLES",
		[]sql.Row{
			{"bigtable"},
			{"floattable"},
			{"mytable"},
			{"myview"},
			{"newlinetable"},
			{"niltable"},
			{"othertable"},
			{"tabletest"},
		},
	},
	{
		"SHOW FULL TABLES",
		[]sql.Row{
			{"bigtable", "BASE TABLE"},
			{"floattable", "BASE TABLE"},
			{"mytable", "BASE TABLE"},
			{"myview", "VIEW"},
			{"newlinetable", "BASE TABLE"},
			{"niltable", "BASE TABLE"},
			{"othertable", "BASE TABLE"},
			{"tabletest", "BASE TABLE"},
		},
	},
	{
		"SHOW TABLES FROM foo",
		[]sql.Row{
			{"other_table"},
		},
	},
	{
		"SHOW TABLES LIKE '%table'",
		[]sql.Row{
			{"mytable"},
			{"othertable"},
			{"bigtable"},
			{"floattable"},
			{"niltable"},
			{"newlinetable"},
		},
	},
	{
		`SHOW COLUMNS FROM mytable`,
		[]sql.Row{
			{"i", "BIGINT", "NO", "PRI", "", ""},
			{"s", "TEXT", "NO", "", "", ""},
		},
	},
	{
		`DESCRIBE mytable`,
		[]sql.Row{
			{"i", "BIGINT", "NO", "PRI", "", ""},
			{"s", "TEXT", "NO", "", "", ""},
		},
	},
	{
		`DESC mytable`,
		[]sql.Row{
			{"i", "BIGINT", "NO", "PRI", "", ""},
			{"s", "TEXT", "NO", "", "", ""},
		},
	},
	{
		`SHOW COLUMNS FROM mytable WHERE Field = 'i'`,
		[]sql.Row{
			{"i", "BIGINT", "NO", "PRI", "", ""},
		},
	},
	{
		`SHOW COLUMNS FROM mytable LIKE 'i'`,
		[]sql.Row{
			{"i", "BIGINT", "NO", "PRI", "", ""},
		},
	},
	{
		`SHOW FULL COLUMNS FROM mytable`,
		[]sql.Row{
			{"i", "BIGINT", nil, "NO", "PRI", "", "", "", ""},
			{"s", "TEXT", "utf8_bin", "NO", "", "", "", "", "column s"},
		},
	},
	{
		"SHOW TABLES WHERE `Table` = 'mytable'",
		[]sql.Row{
			{"mytable"},
		},
	},
	{
		`
		SELECT
			LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA
		FROM INFORMATION_SCHEMA.FILES
		WHERE FILE_TYPE = 'UNDO LOG'
			AND FILE_NAME IS NOT NULL
			AND LOGFILE_GROUP_NAME IS NOT NULL
		GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE
		ORDER BY LOGFILE_GROUP_NAME
		`,
		nil,
	},
	{
		`
		SELECT DISTINCT
			TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE
		FROM INFORMATION_SCHEMA.FILES
		WHERE FILE_TYPE = 'DATAFILE'
		ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
		`,
		nil,
	},
	{
		`
		SELECT
			COLUMN_NAME,
			JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')
		FROM information_schema.COLUMN_STATISTICS
		WHERE SCHEMA_NAME = 'mydb'
		AND TABLE_NAME = 'mytable'
		`,
		nil,
	},
	{
		`
		SELECT TABLE_NAME FROM information_schema.TABLES
		WHERE TABLE_SCHEMA='mydb' AND (TABLE_TYPE='BASE TABLE' OR TABLE_TYPE='VIEW')
		ORDER BY 1
		`,
		[]sql.Row{
			{"bigtable"},
			{"floattable"},
			{"mytable"},
			{"myview"},
			{"newlinetable"},
			{"niltable"},
			{"othertable"},
			{"tabletest"},
		},
	},
	{
		`
		SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.COLUMNS
		WHERE TABLE_SCHEMA='mydb' AND TABLE_NAME='mytable'
		`,
		[]sql.Row{
			{"s", "text"},
			{"i", "bigint"},
		},
	},
	{
		`
		SELECT COLUMN_NAME FROM information_schema.COLUMNS
		WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME LIKE '%table'
		GROUP BY COLUMN_NAME
		`,
		[]sql.Row{
			{"s"},
			{"i"},
			{"s2"},
			{"i2"},
			{"t"},
			{"n"},
			{"f32"},
			{"f64"},
			{"b"},
			{"f"},
		},
	},
	{
		`
		SELECT COLUMN_NAME FROM information_schema.COLUMNS
		WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME LIKE '%table'
		GROUP BY 1
		`,
		[]sql.Row{
			{"s"},
			{"i"},
			{"s2"},
			{"i2"},
			{"t"},
			{"n"},
			{"f32"},
			{"f64"},
			{"b"},
			{"f"},
		},
	},
	{
		`
		SELECT COLUMN_NAME AS COLUMN_NAME FROM information_schema.COLUMNS
		WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME LIKE '%table'
		GROUP BY 1
		`,
		[]sql.Row{
			{"s"},
			{"i"},
			{"s2"},
			{"i2"},
			{"t"},
			{"n"},
			{"f32"},
			{"f64"},
			{"b"},
			{"f"},
		},
	},
	{
		`
		SELECT COLUMN_NAME AS COLUMN_NAME FROM information_schema.COLUMNS
		WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME LIKE '%table'
		GROUP BY 1 HAVING SUBSTRING(COLUMN_NAME, 1, 1) = "s"
		`,
		[]sql.Row{{"s"}, {"s2"}},
	},
}
View Source 
var InsertErrorTests = []GenericErrorQueryTest{
	{
		"too few values",
		"INSERT INTO mytable (s, i) VALUES ('x');",
	},
	{
		"too many values one column",
		"INSERT INTO mytable (s) VALUES ('x', 999);",
	},
	{
		"too many values two columns",
		"INSERT INTO mytable (i, s) VALUES (999, 'x', 'y');",
	},
	{
		"too few values no columns specified",
		"INSERT INTO mytable VALUES (999);",
	},
	{
		"too many values no columns specified",
		"INSERT INTO mytable VALUES (999, 'x', 'y');",
	},
	{
		"non-existent column values",
		"INSERT INTO mytable (i, s, z) VALUES (999, 'x', 999);",
	},
	{
		"non-existent column set",
		"INSERT INTO mytable SET i = 999, s = 'x', z = 999;",
	},
	{
		"duplicate column",
		"INSERT INTO mytable (i, s, s) VALUES (999, 'x', 'x');",
	},
	{
		"duplicate column set",
		"INSERT INTO mytable SET i = 999, s = 'y', s = 'y';",
	},
	{
		"null given to non-nullable",
		"INSERT INTO mytable (i, s) VALUES (null, 'y');",
	},
	{
		"incompatible types",
		"INSERT INTO mytable (i, s) select * FROM othertable",
	},
	{
		"column count mismatch in select",
		"INSERT INTO mytable (i) select * FROM othertable",
	},
	{
		"column count mismatch in select",
		"INSERT INTO mytable select s FROM othertable",
	},
	{
		"column count mismatch in join select",
		"INSERT INTO mytable (s,i) SELECT * FROM othertable o JOIN mytable m ON m.i=o.i2",
	},
	{
		"duplicate key",
		"INSERT INTO mytable (i,s) values (1, 'hello')",
	},
	{
		"duplicate keys",
		"INSERT INTO mytable SELECT * from mytable",
	},
}
View Source 
var InsertQueries = []WriteQueryTest{
	{
		"INSERT INTO mytable (s, i) VALUES ('x', 999);",
		[]sql.Row{{sql.NewOkResult(1)}},
		"SELECT i FROM mytable WHERE s = 'x';",
		[]sql.Row{{int64(999)}},
	},
	{
		"INSERT INTO niltable (i, f) VALUES (10, 10.0), (12, 12.0);",
		[]sql.Row{{sql.NewOkResult(2)}},
		"SELECT i,f FROM niltable WHERE f IN (10.0, 12.0) ORDER BY f;",
		[]sql.Row{{int64(10), 10.0}, {int64(12), 12.0}},
	},
	{
		"INSERT INTO mytable SET s = 'x', i = 999;",
		[]sql.Row{{sql.NewOkResult(1)}},
		"SELECT i FROM mytable WHERE s = 'x';",
		[]sql.Row{{int64(999)}},
	},
	{
		"INSERT INTO mytable VALUES (999, 'x');",
		[]sql.Row{{sql.NewOkResult(1)}},
		"SELECT i FROM mytable WHERE s = 'x';",
		[]sql.Row{{int64(999)}},
	},
	{
		"INSERT INTO mytable SET i = 999, s = 'x';",
		[]sql.Row{{sql.NewOkResult(1)}},
		"SELECT i FROM mytable WHERE s = 'x';",
		[]sql.Row{{int64(999)}},
	},
	{
		`INSERT INTO typestable VALUES (
			999, 127, 32767, 2147483647, 9223372036854775807,
			255, 65535, 4294967295, 18446744073709551615,
			3.40282346638528859811704183484516925440e+38, 1.797693134862315708145274237317043567981e+308,
			'2037-04-05 12:51:36', '2231-11-07',
			'random text', true, '{"key":"value"}', 'blobdata'
			);`,
		[]sql.Row{{sql.NewOkResult(1)}},
		"SELECT * FROM typestable WHERE id = 999;",
		[]sql.Row{{
			int64(999), int8(math.MaxInt8), int16(math.MaxInt16), int32(math.MaxInt32), int64(math.MaxInt64),
			uint8(math.MaxUint8), uint16(math.MaxUint16), uint32(math.MaxUint32), uint64(math.MaxUint64),
			float32(math.MaxFloat32), float64(math.MaxFloat64),
			sql.Timestamp.MustConvert("2037-04-05 12:51:36"), sql.Date.MustConvert("2231-11-07"),
			"random text", sql.True, ([]byte)(`{"key":"value"}`), "blobdata",
		}},
	},
	{
		`INSERT INTO typestable SET
			id = 999, i8 = 127, i16 = 32767, i32 = 2147483647, i64 = 9223372036854775807,
			u8 = 255, u16 = 65535, u32 = 4294967295, u64 = 18446744073709551615,
			f32 = 3.40282346638528859811704183484516925440e+38, f64 = 1.797693134862315708145274237317043567981e+308,
			ti = '2037-04-05 12:51:36', da = '2231-11-07',
			te = 'random text', bo = true, js = '{"key":"value"}', bl = 'blobdata'
			;`,
		[]sql.Row{{sql.NewOkResult(1)}},
		"SELECT * FROM typestable WHERE id = 999;",
		[]sql.Row{{
			int64(999), int8(math.MaxInt8), int16(math.MaxInt16), int32(math.MaxInt32), int64(math.MaxInt64),
			uint8(math.MaxUint8), uint16(math.MaxUint16), uint32(math.MaxUint32), uint64(math.MaxUint64),
			float32(math.MaxFloat32), float64(math.MaxFloat64),
			sql.Timestamp.MustConvert("2037-04-05 12:51:36"), sql.Date.MustConvert("2231-11-07"),
			"random text", sql.True, ([]byte)(`{"key":"value"}`), "blobdata",
		}},
	},
	{
		`INSERT INTO typestable VALUES (
			999, -128, -32768, -2147483648, -9223372036854775808,
			0, 0, 0, 0,
			1.401298464324817070923729583289916131280e-45, 4.940656458412465441765687928682213723651e-324,
			'0000-00-00 00:00:00', '0000-00-00',
			'', false, '', ''
			);`,
		[]sql.Row{{sql.NewOkResult(1)}},
		"SELECT * FROM typestable WHERE id = 999;",
		[]sql.Row{{
			int64(999), int8(-math.MaxInt8 - 1), int16(-math.MaxInt16 - 1), int32(-math.MaxInt32 - 1), int64(-math.MaxInt64 - 1),
			uint8(0), uint16(0), uint32(0), uint64(0),
			float32(math.SmallestNonzeroFloat32), float64(math.SmallestNonzeroFloat64),
			sql.Timestamp.Zero(), sql.Date.Zero(),
			"", sql.False, ([]byte)(`""`), "",
		}},
	},
	{
		`INSERT INTO typestable SET
			id = 999, i8 = -128, i16 = -32768, i32 = -2147483648, i64 = -9223372036854775808,
			u8 = 0, u16 = 0, u32 = 0, u64 = 0,
			f32 = 1.401298464324817070923729583289916131280e-45, f64 = 4.940656458412465441765687928682213723651e-324,
			ti = '0000-00-00 00:00:00', da = '0000-00-00',
			te = '', bo = false, js = '', bl = ''
			;`,
		[]sql.Row{{sql.NewOkResult(1)}},
		"SELECT * FROM typestable WHERE id = 999;",
		[]sql.Row{{
			int64(999), int8(-math.MaxInt8 - 1), int16(-math.MaxInt16 - 1), int32(-math.MaxInt32 - 1), int64(-math.MaxInt64 - 1),
			uint8(0), uint16(0), uint32(0), uint64(0),
			float32(math.SmallestNonzeroFloat32), float64(math.SmallestNonzeroFloat64),
			sql.Timestamp.Zero(), sql.Date.Zero(),
			"", sql.False, ([]byte)(`""`), "",
		}},
	},
	{
		`INSERT INTO typestable VALUES (999, null, null, null, null, null, null, null, null,
			null, null, null, null, null, null, null, null);`,
		[]sql.Row{{sql.NewOkResult(1)}},
		"SELECT * FROM typestable WHERE id = 999;",
		[]sql.Row{{int64(999), nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil}},
	},
	{
		`INSERT INTO typestable SET id=999, i8=null, i16=null, i32=null, i64=null, u8=null, u16=null, u32=null, u64=null,
			f32=null, f64=null, ti=null, da=null, te=null, bo=null, js=null, bl=null;`,
		[]sql.Row{{sql.NewOkResult(1)}},
		"SELECT * FROM typestable WHERE id = 999;",
		[]sql.Row{{int64(999), nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil}},
	},
	{
		"INSERT INTO mytable SELECT i+100,s FROM mytable",
		[]sql.Row{{sql.NewOkResult(3)}},
		"SELECT * FROM mytable ORDER BY i",
		[]sql.Row{
			{int64(1), "first row"},
			{int64(2), "second row"},
			{int64(3), "third row"},
			{int64(101), "first row"},
			{int64(102), "second row"},
			{int64(103), "third row"},
		},
	},
	{
		"INSERT INTO emptytable SELECT * FROM mytable",
		[]sql.Row{{sql.NewOkResult(3)}},
		"SELECT * FROM emptytable ORDER BY i",
		[]sql.Row{
			{int64(1), "first row"},
			{int64(2), "second row"},
			{int64(3), "third row"},
		},
	},
	{
		"INSERT INTO emptytable SELECT * FROM mytable where mytable.i > 2",
		[]sql.Row{{sql.NewOkResult(1)}},
		"SELECT * FROM emptytable ORDER BY i",
		[]sql.Row{
			{int64(3), "third row"},
		},
	},
	{
		"INSERT INTO mytable (i,s) SELECT i+10, 'new' FROM mytable",
		[]sql.Row{{sql.NewOkResult(3)}},
		"SELECT * FROM mytable ORDER BY i",
		[]sql.Row{
			{int64(1), "first row"},
			{int64(2), "second row"},
			{int64(3), "third row"},
			{int64(11), "new"},
			{int64(12), "new"},
			{int64(13), "new"},
		},
	},
	{
		"INSERT INTO mytable SELECT i2+100, s2 FROM othertable",
		[]sql.Row{{sql.NewOkResult(3)}},
		"SELECT * FROM mytable ORDER BY i,s",
		[]sql.Row{
			{int64(1), "first row"},
			{int64(2), "second row"},
			{int64(3), "third row"},
			{int64(101), "third"},
			{int64(102), "second"},
			{int64(103), "first"},
		},
	},
	{
		"INSERT INTO emptytable (s,i) SELECT * FROM othertable",
		[]sql.Row{{sql.NewOkResult(3)}},
		"SELECT * FROM emptytable ORDER BY i,s",
		[]sql.Row{
			{int64(1), "third"},
			{int64(2), "second"},
			{int64(3), "first"},
		},
	},
	{
		"INSERT INTO emptytable (s,i) SELECT concat(m.s, o.s2), m.i FROM othertable o JOIN mytable m ON m.i=o.i2",
		[]sql.Row{{sql.NewOkResult(3)}},
		"SELECT * FROM emptytable ORDER BY i,s",
		[]sql.Row{
			{int64(1), "first rowthird"},
			{int64(2), "second rowsecond"},
			{int64(3), "third rowfirst"},
		},
	},
	{
		"INSERT INTO mytable (i,s) SELECT (i + 10.0) / 10.0 + 10 + i, concat(s, ' new') FROM mytable",
		[]sql.Row{{sql.NewOkResult(3)}},
		"SELECT * FROM mytable ORDER BY i, s",
		[]sql.Row{
			{int64(1), "first row"},
			{int64(2), "second row"},
			{int64(3), "third row"},
			{int64(12), "first row new"},
			{int64(13), "second row new"},
			{int64(14), "third row new"},
		},
	},
}
View Source 
var PlanTests = []QueryPlanTest{
	{
		Query: "SELECT i, i2, s2 FROM mytable INNER JOIN othertable ON i = i2",
		ExpectedPlan: "IndexedJoin(mytable.i = othertable.i2)\n" +
			" ├─ Table(mytable): Projected \n" +
			" └─ Table(othertable): Projected \n" +
			"",
	},
	{
		Query: "SELECT s2, i2, i FROM mytable INNER JOIN othertable ON i = i2",
		ExpectedPlan: "Project(othertable.s2, othertable.i2, mytable.i)\n" +
			" └─ IndexedJoin(mytable.i = othertable.i2)\n" +
			"     ├─ Table(mytable): Projected \n" +
			"     └─ Table(othertable): Projected \n" +
			"",
	},
	{
		Query: "SELECT i, i2, s2 FROM othertable JOIN mytable ON i = i2",
		ExpectedPlan: "Project(mytable.i, othertable.i2, othertable.s2)\n" +
			" └─ IndexedJoin(mytable.i = othertable.i2)\n" +
			"     ├─ Table(othertable): Projected \n" +
			"     └─ Table(mytable): Projected \n" +
			"",
	},
	{
		Query: "SELECT s2, i2, i FROM othertable JOIN mytable ON i = i2",
		ExpectedPlan: "IndexedJoin(mytable.i = othertable.i2)\n" +
			" ├─ Table(othertable): Projected \n" +
			" └─ Table(mytable): Projected \n" +
			"",
	},
	{
		Query: "SELECT i, i2, s2 FROM mytable INNER JOIN othertable ON i2 = i",
		ExpectedPlan: "IndexedJoin(othertable.i2 = mytable.i)\n" +
			" ├─ Table(mytable): Projected \n" +
			" └─ Table(othertable): Projected \n" +
			"",
	},
	{
		Query: "SELECT s2, i2, i FROM mytable INNER JOIN othertable ON i2 = i",
		ExpectedPlan: "Project(othertable.s2, othertable.i2, mytable.i)\n" +
			" └─ IndexedJoin(othertable.i2 = mytable.i)\n" +
			"     ├─ Table(mytable): Projected \n" +
			"     └─ Table(othertable): Projected \n" +
			"",
	},
	{
		Query: "SELECT i, i2, s2 FROM othertable JOIN mytable ON i2 = i",
		ExpectedPlan: "Project(mytable.i, othertable.i2, othertable.s2)\n" +
			" └─ IndexedJoin(othertable.i2 = mytable.i)\n" +
			"     ├─ Table(othertable): Projected \n" +
			"     └─ Table(mytable): Projected \n" +
			"",
	},
	{
		Query: "SELECT s2, i2, i FROM othertable JOIN mytable ON i2 = i",
		ExpectedPlan: "IndexedJoin(othertable.i2 = mytable.i)\n" +
			" ├─ Table(othertable): Projected \n" +
			" └─ Table(mytable): Projected \n" +
			"",
	},
	{
		Query: "SELECT * FROM mytable mt INNER JOIN othertable ot ON mt.i = ot.i2 AND mt.i > 2",
		ExpectedPlan: "IndexedJoin(mt.i = ot.i2)\n" +
			" ├─ TableAlias(mt)\n" +
			" │   └─ Table(mytable): Projected Filtered \n" +
			" └─ TableAlias(ot)\n" +
			"     └─ Table(othertable): Projected \n" +
			"",
	},
	{
		Query: "SELECT i, i2, s2 FROM mytable RIGHT JOIN othertable ON i = i2 - 1",
		ExpectedPlan: "Project(mytable.i, othertable.i2, othertable.s2)\n" +
			" └─ RightIndexedJoin(mytable.i = othertable.i2 - 1)\n" +
			"     ├─ Table(othertable)\n" +
			"     └─ Table(mytable)\n" +
			"",
	},
	{
		Query: "SELECT pk,pk1,pk2 FROM one_pk JOIN two_pk ON one_pk.pk=two_pk.pk1 AND one_pk.pk=two_pk.pk2",
		ExpectedPlan: "IndexedJoin(one_pk.pk = two_pk.pk1 AND one_pk.pk = two_pk.pk2)\n" +
			" ├─ Table(one_pk): Projected \n" +
			" └─ Table(two_pk): Projected \n" +
			"",
	},
	{
		Query: "SELECT pk,pk1,pk2 FROM one_pk opk JOIN two_pk tpk ON opk.pk=tpk.pk1 AND opk.pk=tpk.pk2",
		ExpectedPlan: "IndexedJoin(opk.pk = tpk.pk1 AND opk.pk = tpk.pk2)\n" +
			" ├─ TableAlias(opk)\n" +
			" │   └─ Table(one_pk): Projected \n" +
			" └─ TableAlias(tpk)\n" +
			"     └─ Table(two_pk): Projected \n" +
			"",
	},
	{
		Query: "SELECT pk,pk1,pk2 FROM one_pk LEFT JOIN two_pk ON one_pk.pk=two_pk.pk1 AND one_pk.pk=two_pk.pk2",
		ExpectedPlan: "Project(one_pk.pk, two_pk.pk1, two_pk.pk2)\n" +
			" └─ LeftIndexedJoin(one_pk.pk = two_pk.pk1 AND one_pk.pk = two_pk.pk2)\n" +
			"     ├─ Table(one_pk)\n" +
			"     └─ Table(two_pk)\n" +
			"",
	},
	{
		Query: "SELECT pk,pk1,pk2 FROM one_pk RIGHT JOIN two_pk ON one_pk.pk=two_pk.pk1 AND one_pk.pk=two_pk.pk2",
		ExpectedPlan: "Project(one_pk.pk, two_pk.pk1, two_pk.pk2)\n" +
			" └─ RightIndexedJoin(one_pk.pk = two_pk.pk1 AND one_pk.pk = two_pk.pk2)\n" +
			"     ├─ Table(two_pk)\n" +
			"     └─ Table(one_pk)\n" +
			"",
	},
	{
		Query: "SELECT i,pk1,pk2 FROM mytable JOIN two_pk ON i-1=pk1 AND i-2=pk2",
		ExpectedPlan: "IndexedJoin(mytable.i - 1 = two_pk.pk1 AND mytable.i - 2 = two_pk.pk2)\n" +
			" ├─ Table(mytable): Projected \n" +
			" └─ Table(two_pk): Projected \n" +
			"",
	},
	{
		Query: "SELECT pk,pk1,pk2 FROM one_pk LEFT JOIN two_pk ON pk=pk1",
		ExpectedPlan: "Project(one_pk.pk, two_pk.pk1, two_pk.pk2)\n" +
			" └─ LeftJoin(one_pk.pk = two_pk.pk1)\n" +
			"     ├─ Table(one_pk)\n" +
			"     └─ Table(two_pk)\n" +
			"",
	},
	{
		Query: "SELECT pk,i,f FROM one_pk LEFT JOIN niltable ON pk=i",
		ExpectedPlan: "Project(one_pk.pk, niltable.i, niltable.f)\n" +
			" └─ LeftIndexedJoin(one_pk.pk = niltable.i)\n" +
			"     ├─ Table(one_pk)\n" +
			"     └─ Table(niltable)\n" +
			"",
	},
	{
		Query: "SELECT pk,i,f FROM one_pk RIGHT JOIN niltable ON pk=i",
		ExpectedPlan: "Project(one_pk.pk, niltable.i, niltable.f)\n" +
			" └─ RightIndexedJoin(one_pk.pk = niltable.i)\n" +
			"     ├─ Table(niltable)\n" +
			"     └─ Table(one_pk)\n" +
			"",
	},
	{
		Query: "SELECT pk,i,f FROM one_pk LEFT JOIN niltable ON pk=i AND f IS NOT NULL",
		ExpectedPlan: "Project(one_pk.pk, niltable.i, niltable.f)\n" +
			" └─ LeftJoin(one_pk.pk = niltable.i AND NOT(niltable.f IS NULL))\n" +
			"     ├─ Table(one_pk)\n" +
			"     └─ Table(niltable)\n" +
			"",
	},
	{
		Query: "SELECT pk,i,f FROM one_pk RIGHT JOIN niltable ON pk=i and pk > 0",
		ExpectedPlan: "Project(one_pk.pk, niltable.i, niltable.f)\n" +
			" └─ RightJoin(one_pk.pk = niltable.i AND one_pk.pk > 0)\n" +
			"     ├─ Table(one_pk)\n" +
			"     └─ Table(niltable)\n" +
			"",
	},
	{
		Query: "SELECT pk,i,f FROM one_pk LEFT JOIN niltable ON pk=i WHERE f IS NOT NULL",
		ExpectedPlan: "Project(one_pk.pk, niltable.i, niltable.f)\n" +
			" └─ Filter(NOT(niltable.f IS NULL))\n" +
			"     └─ LeftIndexedJoin(one_pk.pk = niltable.i)\n" +
			"         ├─ Table(one_pk)\n" +
			"         └─ Table(niltable)\n" +
			"",
	},
	{
		Query: "SELECT pk,i,f FROM one_pk RIGHT JOIN niltable ON pk=i WHERE f IS NOT NULL",
		ExpectedPlan: "Project(one_pk.pk, niltable.i, niltable.f)\n" +
			" └─ Filter(NOT(niltable.f IS NULL))\n" +
			"     └─ RightIndexedJoin(one_pk.pk = niltable.i)\n" +
			"         ├─ Table(niltable)\n" +
			"         └─ Table(one_pk)\n" +
			"",
	},
	{
		Query: "SELECT pk,i,f FROM one_pk LEFT JOIN niltable ON pk=i WHERE pk > 1",
		ExpectedPlan: "Project(one_pk.pk, niltable.i, niltable.f)\n" +
			" └─ Filter(one_pk.pk > 1)\n" +
			"     └─ LeftIndexedJoin(one_pk.pk = niltable.i)\n" +
			"         ├─ Table(one_pk)\n" +
			"         └─ Table(niltable)\n" +
			"",
	},
	{
		Query: "SELECT pk,i,f FROM one_pk RIGHT JOIN niltable ON pk=i WHERE pk > 0",
		ExpectedPlan: "Project(one_pk.pk, niltable.i, niltable.f)\n" +
			" └─ Filter(one_pk.pk > 0)\n" +
			"     └─ RightIndexedJoin(one_pk.pk = niltable.i)\n" +
			"         ├─ Table(niltable)\n" +
			"         └─ Table(one_pk)\n" +
			"",
	},
	{
		Query: "SELECT pk,pk1,pk2 FROM one_pk JOIN two_pk ON pk=pk1",
		ExpectedPlan: "Project(one_pk.pk, two_pk.pk1, two_pk.pk2)\n" +
			" └─ IndexedJoin(one_pk.pk = two_pk.pk1)\n" +
			"     ├─ Table(two_pk): Projected \n" +
			"     └─ Table(one_pk): Projected \n",
	},
	{
		Query: "SELECT a.pk1,a.pk2,b.pk1,b.pk2 FROM two_pk a JOIN two_pk b ON a.pk1=b.pk1 AND a.pk2=b.pk2 ORDER BY 1,2,3",
		ExpectedPlan: "Sort(a.pk1 ASC, a.pk2 ASC, b.pk1 ASC)\n" +
			" └─ IndexedJoin(a.pk1 = b.pk1 AND a.pk2 = b.pk2)\n" +
			"     ├─ TableAlias(a)\n" +
			"     │   └─ Table(two_pk): Projected \n" +
			"     └─ TableAlias(b)\n" +
			"         └─ Table(two_pk): Projected \n" +
			"",
	},
	{
		Query: "SELECT a.pk1,a.pk2,b.pk1,b.pk2 FROM two_pk a JOIN two_pk b ON a.pk1=b.pk2 AND a.pk2=b.pk1 ORDER BY 1,2,3",
		ExpectedPlan: "Sort(a.pk1 ASC, a.pk2 ASC, b.pk1 ASC)\n" +
			" └─ IndexedJoin(a.pk1 = b.pk2 AND a.pk2 = b.pk1)\n" +
			"     ├─ TableAlias(a)\n" +
			"     │   └─ Table(two_pk): Projected \n" +
			"     └─ TableAlias(b)\n" +
			"         └─ Table(two_pk): Projected \n" +
			"",
	},
	{
		Query: "SELECT a.pk1,a.pk2,b.pk1,b.pk2 FROM two_pk a JOIN two_pk b ON b.pk1=a.pk1 AND a.pk2=b.pk2 ORDER BY 1,2,3",
		ExpectedPlan: "Sort(a.pk1 ASC, a.pk2 ASC, b.pk1 ASC)\n" +
			" └─ IndexedJoin(b.pk1 = a.pk1 AND a.pk2 = b.pk2)\n" +
			"     ├─ TableAlias(a)\n" +
			"     │   └─ Table(two_pk): Projected \n" +
			"     └─ TableAlias(b)\n" +
			"         └─ Table(two_pk): Projected \n" +
			"",
	},
	{
		Query: "SELECT a.pk1,a.pk2,b.pk1,b.pk2 FROM two_pk a JOIN two_pk b ON a.pk1+1=b.pk1 AND a.pk2+1=b.pk2 ORDER BY 1,2,3",
		ExpectedPlan: "Sort(a.pk1 ASC, a.pk2 ASC, b.pk1 ASC)\n" +
			" └─ IndexedJoin(a.pk1 + 1 = b.pk1 AND a.pk2 + 1 = b.pk2)\n" +
			"     ├─ TableAlias(a)\n" +
			"     │   └─ Table(two_pk): Projected \n" +
			"     └─ TableAlias(b)\n" +
			"         └─ Table(two_pk): Projected \n" +
			"",
	},
	{

		Query: "SELECT a.pk1,a.pk2,b.pk1,b.pk2 FROM two_pk a, two_pk b WHERE a.pk1=b.pk1 AND a.pk2=b.pk2 ORDER BY 1,2,3",
		ExpectedPlan: "Sort(a.pk1 ASC, a.pk2 ASC, b.pk1 ASC)\n" +
			" └─ Filter(a.pk1 = b.pk1 AND a.pk2 = b.pk2)\n" +
			"     └─ CrossJoin\n" +
			"         ├─ TableAlias(a)\n" +
			"         │   └─ Table(two_pk): Projected \n" +
			"         └─ TableAlias(b)\n" +
			"             └─ Table(two_pk): Projected \n" +
			"",
	},
	{

		Query: "SELECT a.pk1,a.pk2,b.pk1,b.pk2 FROM two_pk a, two_pk b WHERE a.pk1=b.pk2 AND a.pk2=b.pk1 ORDER BY 1,2,3",
		ExpectedPlan: "Sort(a.pk1 ASC, a.pk2 ASC, b.pk1 ASC)\n" +
			" └─ Filter(a.pk1 = b.pk2 AND a.pk2 = b.pk1)\n" +
			"     └─ CrossJoin\n" +
			"         ├─ TableAlias(a)\n" +
			"         │   └─ Table(two_pk): Projected \n" +
			"         └─ TableAlias(b)\n" +
			"             └─ Table(two_pk): Projected \n" +
			"",
	},
	{
		Query: "SELECT one_pk.c5,pk1,pk2 FROM one_pk JOIN two_pk ON pk=pk1 ORDER BY 1,2,3",
		ExpectedPlan: "Sort(one_pk.c5 ASC, two_pk.pk1 ASC, two_pk.pk2 ASC)\n" +
			" └─ Project(one_pk.c5, two_pk.pk1, two_pk.pk2)\n" +
			"     └─ IndexedJoin(one_pk.pk = two_pk.pk1)\n" +
			"         ├─ Table(two_pk): Projected \n" +
			"         └─ Table(one_pk): Projected \n" +
			"",
	},
	{
		Query: "SELECT opk.c5,pk1,pk2 FROM one_pk opk JOIN two_pk tpk ON opk.pk=tpk.pk1 ORDER BY 1,2,3",
		ExpectedPlan: "Sort(opk.c5 ASC, tpk.pk1 ASC, tpk.pk2 ASC)\n" +
			" └─ Project(opk.c5, tpk.pk1, tpk.pk2)\n" +
			"     └─ IndexedJoin(opk.pk = tpk.pk1)\n" +
			"         ├─ TableAlias(tpk)\n" +
			"         │   └─ Table(two_pk): Projected \n" +
			"         └─ TableAlias(opk)\n" +
			"             └─ Table(one_pk): Projected \n" +
			"",
	},
	{
		Query: "SELECT opk.c5,pk1,pk2 FROM one_pk opk JOIN two_pk tpk ON pk=pk1 ORDER BY 1,2,3",
		ExpectedPlan: "Sort(opk.c5 ASC, tpk.pk1 ASC, tpk.pk2 ASC)\n" +
			" └─ Project(opk.c5, tpk.pk1, tpk.pk2)\n" +
			"     └─ IndexedJoin(opk.pk = tpk.pk1)\n" +
			"         ├─ TableAlias(tpk)\n" +
			"         │   └─ Table(two_pk): Projected \n" +
			"         └─ TableAlias(opk)\n" +
			"             └─ Table(one_pk): Projected \n" +
			"",
	},
	{
		Query: "SELECT opk.c5,pk1,pk2 FROM one_pk opk, two_pk tpk WHERE pk=pk1 ORDER BY 1,2,3",
		ExpectedPlan: "Sort(opk.c5 ASC, tpk.pk1 ASC, tpk.pk2 ASC)\n" +
			" └─ Project(opk.c5, tpk.pk1, tpk.pk2)\n" +
			"     └─ Filter(opk.pk = tpk.pk1)\n" +
			"         └─ CrossJoin\n" +
			"             ├─ TableAlias(opk)\n" +
			"             │   └─ Table(one_pk): Projected \n" +
			"             └─ TableAlias(tpk)\n" +
			"                 └─ Table(two_pk): Projected \n" +
			"",
	},
	{

		Query: "SELECT one_pk.c5,pk1,pk2 FROM one_pk,two_pk WHERE pk=pk1 ORDER BY 1,2,3",
		ExpectedPlan: "Sort(one_pk.c5 ASC, two_pk.pk1 ASC, two_pk.pk2 ASC)\n" +
			" └─ Project(one_pk.c5, two_pk.pk1, two_pk.pk2)\n" +
			"     └─ Filter(one_pk.pk = two_pk.pk1)\n" +
			"         └─ CrossJoin\n" +
			"             ├─ Table(one_pk): Projected \n" +
			"             └─ Table(two_pk): Projected \n" +
			"",
	},
	{
		Query: "SELECT pk,i,f FROM one_pk LEFT JOIN niltable ON pk=i ORDER BY 1",
		ExpectedPlan: "Sort(one_pk.pk ASC)\n" +
			" └─ Project(one_pk.pk, niltable.i, niltable.f)\n" +
			"     └─ LeftIndexedJoin(one_pk.pk = niltable.i)\n" +
			"         ├─ Table(one_pk)\n" +
			"         └─ Table(niltable)\n" +
			"",
	},
	{
		Query: "SELECT pk,i,f FROM one_pk LEFT JOIN niltable ON pk=i WHERE f IS NOT NULL ORDER BY 1",
		ExpectedPlan: "Sort(one_pk.pk ASC)\n" +
			" └─ Project(one_pk.pk, niltable.i, niltable.f)\n" +
			"     └─ Filter(NOT(niltable.f IS NULL))\n" +
			"         └─ LeftIndexedJoin(one_pk.pk = niltable.i)\n" +
			"             ├─ Table(one_pk)\n" +
			"             └─ Table(niltable)\n" +
			"",
	},
	{
		Query: "SELECT pk,i,f FROM one_pk LEFT JOIN niltable ON pk=i WHERE pk > 1 ORDER BY 1",
		ExpectedPlan: "Sort(one_pk.pk ASC)\n" +
			" └─ Project(one_pk.pk, niltable.i, niltable.f)\n" +
			"     └─ Filter(one_pk.pk > 1)\n" +
			"         └─ LeftIndexedJoin(one_pk.pk = niltable.i)\n" +
			"             ├─ Table(one_pk)\n" +
			"             └─ Table(niltable)\n" +
			"",
	},
	{
		Query: "SELECT pk,i,f FROM one_pk RIGHT JOIN niltable ON pk=i ORDER BY 2,3",
		ExpectedPlan: "Sort(niltable.i ASC, niltable.f ASC)\n" +
			" └─ Project(one_pk.pk, niltable.i, niltable.f)\n" +
			"     └─ RightIndexedJoin(one_pk.pk = niltable.i)\n" +
			"         ├─ Table(niltable)\n" +
			"         └─ Table(one_pk)\n" +
			"",
	},
	{
		Query: "SELECT pk,i,f FROM one_pk RIGHT JOIN niltable ON pk=i WHERE f IS NOT NULL ORDER BY 2,3",
		ExpectedPlan: "Sort(niltable.i ASC, niltable.f ASC)\n" +
			" └─ Project(one_pk.pk, niltable.i, niltable.f)\n" +
			"     └─ Filter(NOT(niltable.f IS NULL))\n" +
			"         └─ RightIndexedJoin(one_pk.pk = niltable.i)\n" +
			"             ├─ Table(niltable)\n" +
			"             └─ Table(one_pk)\n" +
			"",
	},
	{
		Query: "SELECT pk,i,f FROM one_pk RIGHT JOIN niltable ON pk=i WHERE pk > 0 ORDER BY 2,3",
		ExpectedPlan: "Sort(niltable.i ASC, niltable.f ASC)\n" +
			" └─ Project(one_pk.pk, niltable.i, niltable.f)\n" +
			"     └─ Filter(one_pk.pk > 0)\n" +
			"         └─ RightIndexedJoin(one_pk.pk = niltable.i)\n" +
			"             ├─ Table(niltable)\n" +
			"             └─ Table(one_pk)\n" +
			"",
	},
	{

		Query: "SELECT pk,i,f FROM one_pk RIGHT JOIN niltable ON pk=i and pk > 0 ORDER BY 2,3",
		ExpectedPlan: "Sort(niltable.i ASC, niltable.f ASC)\n" +
			" └─ Project(one_pk.pk, niltable.i, niltable.f)\n" +
			"     └─ RightJoin(one_pk.pk = niltable.i AND one_pk.pk > 0)\n" +
			"         ├─ Table(one_pk)\n" +
			"         └─ Table(niltable)\n" +
			"",
	},
	{
		Query: "SELECT pk,pk1,pk2 FROM one_pk JOIN two_pk ON one_pk.pk=two_pk.pk1 AND one_pk.pk=two_pk.pk2 ORDER BY 1,2,3",
		ExpectedPlan: "Sort(one_pk.pk ASC, two_pk.pk1 ASC, two_pk.pk2 ASC)\n" +
			" └─ IndexedJoin(one_pk.pk = two_pk.pk1 AND one_pk.pk = two_pk.pk2)\n" +
			"     ├─ Table(one_pk): Projected \n" +
			"     └─ Table(two_pk): Projected \n" +
			"",
	},
	{
		Query: "SELECT pk,pk1,pk2 FROM one_pk JOIN two_pk ON pk1-pk>0 AND pk2<1",
		ExpectedPlan: "InnerJoin(two_pk.pk1 - one_pk.pk > 0)\n" +
			" ├─ Table(one_pk): Projected \n" +
			" └─ Table(two_pk): Projected Filtered \n" +
			"",
	},
	{
		Query: "SELECT pk,pk1,pk2 FROM one_pk JOIN two_pk ORDER BY 1,2,3",
		ExpectedPlan: "Sort(one_pk.pk ASC, two_pk.pk1 ASC, two_pk.pk2 ASC)\n" +
			" └─ CrossJoin\n" +
			"     ├─ Table(one_pk): Projected \n" +
			"     └─ Table(two_pk): Projected \n" +
			"",
	},
	{
		Query: "SELECT pk,pk1,pk2 FROM one_pk LEFT JOIN two_pk ON one_pk.pk=two_pk.pk1 AND one_pk.pk=two_pk.pk2 ORDER BY 1,2,3",
		ExpectedPlan: "Sort(one_pk.pk ASC, two_pk.pk1 ASC, two_pk.pk2 ASC)\n" +
			" └─ Project(one_pk.pk, two_pk.pk1, two_pk.pk2)\n" +
			"     └─ LeftIndexedJoin(one_pk.pk = two_pk.pk1 AND one_pk.pk = two_pk.pk2)\n" +
			"         ├─ Table(one_pk)\n" +
			"         └─ Table(two_pk)\n" +
			"",
	},
	{
		Query: "SELECT pk,pk1,pk2 FROM one_pk LEFT JOIN two_pk ON pk=pk1 ORDER BY 1,2,3",
		ExpectedPlan: "Sort(one_pk.pk ASC, two_pk.pk1 ASC, two_pk.pk2 ASC)\n" +
			" └─ Project(one_pk.pk, two_pk.pk1, two_pk.pk2)\n" +
			"     └─ LeftJoin(one_pk.pk = two_pk.pk1)\n" +
			"         ├─ Table(one_pk)\n" +
			"         └─ Table(two_pk)\n" +
			"",
	},
	{
		Query: "SELECT pk,pk1,pk2 FROM one_pk RIGHT JOIN two_pk ON one_pk.pk=two_pk.pk1 AND one_pk.pk=two_pk.pk2 ORDER BY 1,2,3",
		ExpectedPlan: "Sort(one_pk.pk ASC, two_pk.pk1 ASC, two_pk.pk2 ASC)\n" +
			" └─ Project(one_pk.pk, two_pk.pk1, two_pk.pk2)\n" +
			"     └─ RightIndexedJoin(one_pk.pk = two_pk.pk1 AND one_pk.pk = two_pk.pk2)\n" +
			"         ├─ Table(two_pk)\n" +
			"         └─ Table(one_pk)\n" +
			"",
	},
	{
		Query: "SELECT pk,pk1,pk2 FROM one_pk opk JOIN two_pk tpk ON opk.pk=tpk.pk1 AND opk.pk=tpk.pk2 ORDER BY 1,2,3",
		ExpectedPlan: "Sort(opk.pk ASC, tpk.pk1 ASC, tpk.pk2 ASC)\n" +
			" └─ IndexedJoin(opk.pk = tpk.pk1 AND opk.pk = tpk.pk2)\n" +
			"     ├─ TableAlias(opk)\n" +
			"     │   └─ Table(one_pk): Projected \n" +
			"     └─ TableAlias(tpk)\n" +
			"         └─ Table(two_pk): Projected \n" +
			"",
	},
	{
		Query: "SELECT pk,pk1,pk2 FROM one_pk opk JOIN two_pk tpk ON pk=tpk.pk1 AND pk=tpk.pk2 ORDER BY 1,2,3",
		ExpectedPlan: "Sort(opk.pk ASC, tpk.pk1 ASC, tpk.pk2 ASC)\n" +
			" └─ IndexedJoin(opk.pk = tpk.pk1 AND opk.pk = tpk.pk2)\n" +
			"     ├─ TableAlias(opk)\n" +
			"     │   └─ Table(one_pk): Projected \n" +
			"     └─ TableAlias(tpk)\n" +
			"         └─ Table(two_pk): Projected \n" +
			"",
	},
	{
		Query: "SELECT pk,pk1,pk2 FROM one_pk,two_pk WHERE one_pk.c1=two_pk.c1 ORDER BY 1,2,3",
		ExpectedPlan: "Sort(one_pk.pk ASC, two_pk.pk1 ASC, two_pk.pk2 ASC)\n" +
			" └─ Project(one_pk.pk, two_pk.pk1, two_pk.pk2)\n" +
			"     └─ Filter(one_pk.c1 = two_pk.c1)\n" +
			"         └─ CrossJoin\n" +
			"             ├─ Table(one_pk): Projected \n" +
			"             └─ Table(two_pk): Projected \n" +
			"",
	},
	{
		Query: "SELECT pk,pk1,pk2,one_pk.c1 AS foo, two_pk.c1 AS bar FROM one_pk JOIN two_pk ON one_pk.c1=two_pk.c1 ORDER BY 1,2,3",
		ExpectedPlan: "Sort(one_pk.pk ASC, two_pk.pk1 ASC, two_pk.pk2 ASC)\n" +
			" └─ Project(one_pk.pk, two_pk.pk1, two_pk.pk2, one_pk.c1 as foo, two_pk.c1 as bar)\n" +
			"     └─ InnerJoin(one_pk.c1 = two_pk.c1)\n" +
			"         ├─ Table(one_pk): Projected \n" +
			"         └─ Table(two_pk): Projected \n" +
			"",
	},
	{
		Query: "SELECT pk,pk1,pk2,one_pk.c1 AS foo,two_pk.c1 AS bar FROM one_pk JOIN two_pk ON one_pk.c1=two_pk.c1 WHERE one_pk.c1=10",
		ExpectedPlan: "Project(one_pk.pk, two_pk.pk1, two_pk.pk2, one_pk.c1 as foo, two_pk.c1 as bar)\n" +
			" └─ InnerJoin(one_pk.c1 = two_pk.c1)\n" +
			"     ├─ Table(one_pk): Projected Filtered \n" +
			"     └─ Table(two_pk): Projected \n" +
			"",
	},
	{
		Query: "SELECT pk,pk2 FROM one_pk t1, two_pk t2 WHERE pk=1 AND pk2=1 ORDER BY 1,2",
		ExpectedPlan: "Sort(t1.pk ASC, t2.pk2 ASC)\n" +
			" └─ CrossJoin\n" +
			"     ├─ TableAlias(t1)\n" +
			"     │   └─ Table(one_pk): Projected Filtered Indexed\n" +
			"     └─ TableAlias(t2)\n" +
			"         └─ Table(two_pk): Projected Filtered \n" +
			"",
	},
}

QueryPlanTest is a test of generating the right query plans for different queries in the presence of indexes and other features. TODO: the query plan printing behavior relies on integrator specific methods (sql.Table.String()), which makes it 

non-portable. Fix that.
View Source 
var QueryTests = []QueryTest{}/* 383 elements not displayed */
View Source 
var ReplaceErrorTests = []GenericErrorQueryTest{
	{
		"too few values",
		"REPLACE INTO mytable (s, i) VALUES ('x');",
	},
	{
		"too many values one column",
		"REPLACE INTO mytable (s) VALUES ('x', 999);",
	},
	{
		"too many values two columns",
		"REPLACE INTO mytable (i, s) VALUES (999, 'x', 'y');",
	},
	{
		"too few values no columns specified",
		"REPLACE INTO mytable VALUES (999);",
	},
	{
		"too many values no columns specified",
		"REPLACE INTO mytable VALUES (999, 'x', 'y');",
	},
	{
		"non-existent column values",
		"REPLACE INTO mytable (i, s, z) VALUES (999, 'x', 999);",
	},
	{
		"non-existent column set",
		"REPLACE INTO mytable SET i = 999, s = 'x', z = 999;",
	},
	{
		"duplicate column values",
		"REPLACE INTO mytable (i, s, s) VALUES (999, 'x', 'x');",
	},
	{
		"duplicate column set",
		"REPLACE INTO mytable SET i = 999, s = 'y', s = 'y';",
	},
	{
		"null given to non-nullable values",
		"INSERT INTO mytable (i, s) VALUES (null, 'y');",
	},
	{
		"null given to non-nullable set",
		"INSERT INTO mytable SET i = null, s = 'y';",
	},
}
View Source 
var ReplaceQueries = []WriteQueryTest{
	{
		"REPLACE INTO mytable VALUES (1, 'first row');",
		[]sql.Row{{sql.NewOkResult(2)}},
		"SELECT s FROM mytable WHERE i = 1;",
		[]sql.Row{{"first row"}},
	},
	{
		"REPLACE INTO mytable SET i = 1, s = 'first row';",
		[]sql.Row{{sql.NewOkResult(2)}},
		"SELECT s FROM mytable WHERE i = 1;",
		[]sql.Row{{"first row"}},
	},
	{
		"REPLACE INTO mytable VALUES (1, 'new row same i');",
		[]sql.Row{{sql.NewOkResult(2)}},
		"SELECT s FROM mytable WHERE i = 1;",
		[]sql.Row{{"new row same i"}},
	},
	{
		"REPLACE INTO mytable SET i = 1, s = 'new row same i';",
		[]sql.Row{{sql.NewOkResult(2)}},
		"SELECT s FROM mytable WHERE i = 1;",
		[]sql.Row{{"new row same i"}},
	},
	{
		"REPLACE INTO mytable (s, i) VALUES ('x', 999);",
		[]sql.Row{{sql.NewOkResult(1)}},
		"SELECT i FROM mytable WHERE s = 'x';",
		[]sql.Row{{int64(999)}},
	},
	{
		"REPLACE INTO mytable SET s = 'x', i = 999;",
		[]sql.Row{{sql.NewOkResult(1)}},
		"SELECT i FROM mytable WHERE s = 'x';",
		[]sql.Row{{int64(999)}},
	},
	{
		"REPLACE INTO mytable VALUES (999, 'x');",
		[]sql.Row{{sql.NewOkResult(1)}},
		"SELECT i FROM mytable WHERE s = 'x';",
		[]sql.Row{{int64(999)}},
	},
	{
		"REPLACE INTO mytable SET i = 999, s = 'x';",
		[]sql.Row{{sql.NewOkResult(1)}},
		"SELECT i FROM mytable WHERE s = 'x';",
		[]sql.Row{{int64(999)}},
	},
	{
		`REPLACE INTO typestable VALUES (
			999, 127, 32767, 2147483647, 9223372036854775807,
			255, 65535, 4294967295, 18446744073709551615,
			3.40282346638528859811704183484516925440e+38, 1.797693134862315708145274237317043567981e+308,
			'2037-04-05 12:51:36', '2231-11-07',
			'random text', true, '{"key":"value"}', 'blobdata'
			);`,
		[]sql.Row{{sql.NewOkResult(1)}},
		"SELECT * FROM typestable WHERE id = 999;",
		[]sql.Row{{
			int64(999), int8(math.MaxInt8), int16(math.MaxInt16), int32(math.MaxInt32), int64(math.MaxInt64),
			uint8(math.MaxUint8), uint16(math.MaxUint16), uint32(math.MaxUint32), uint64(math.MaxUint64),
			float32(math.MaxFloat32), float64(math.MaxFloat64),
			sql.Timestamp.MustConvert("2037-04-05 12:51:36"), sql.Date.MustConvert("2231-11-07"),
			"random text", sql.True, ([]byte)(`{"key":"value"}`), "blobdata",
		}},
	},
	{
		`REPLACE INTO typestable SET
			id = 999, i8 = 127, i16 = 32767, i32 = 2147483647, i64 = 9223372036854775807,
			u8 = 255, u16 = 65535, u32 = 4294967295, u64 = 18446744073709551615,
			f32 = 3.40282346638528859811704183484516925440e+38, f64 = 1.797693134862315708145274237317043567981e+308,
			ti = '2037-04-05 12:51:36', da = '2231-11-07',
			te = 'random text', bo = true, js = '{"key":"value"}', bl = 'blobdata'
			;`,
		[]sql.Row{{sql.NewOkResult(1)}},
		"SELECT * FROM typestable WHERE id = 999;",
		[]sql.Row{{
			int64(999), int8(math.MaxInt8), int16(math.MaxInt16), int32(math.MaxInt32), int64(math.MaxInt64),
			uint8(math.MaxUint8), uint16(math.MaxUint16), uint32(math.MaxUint32), uint64(math.MaxUint64),
			float32(math.MaxFloat32), float64(math.MaxFloat64),
			sql.Timestamp.MustConvert("2037-04-05 12:51:36"), sql.Date.MustConvert("2231-11-07"),
			"random text", sql.True, ([]byte)(`{"key":"value"}`), "blobdata",
		}},
	},
	{
		`REPLACE INTO typestable VALUES (
			999, -128, -32768, -2147483648, -9223372036854775808,
			0, 0, 0, 0,
			1.401298464324817070923729583289916131280e-45, 4.940656458412465441765687928682213723651e-324,
			'0000-00-00 00:00:00', '0000-00-00',
			'', false, '', ''
			);`,
		[]sql.Row{{sql.NewOkResult(1)}},
		"SELECT * FROM typestable WHERE id = 999;",
		[]sql.Row{{
			int64(999), int8(-math.MaxInt8 - 1), int16(-math.MaxInt16 - 1), int32(-math.MaxInt32 - 1), int64(-math.MaxInt64 - 1),
			uint8(0), uint16(0), uint32(0), uint64(0),
			float32(math.SmallestNonzeroFloat32), float64(math.SmallestNonzeroFloat64),
			sql.Timestamp.Zero(), sql.Date.Zero(),
			"", sql.False, ([]byte)(`""`), "",
		}},
	},
	{
		`REPLACE INTO typestable SET
			id = 999, i8 = -128, i16 = -32768, i32 = -2147483648, i64 = -9223372036854775808,
			u8 = 0, u16 = 0, u32 = 0, u64 = 0,
			f32 = 1.401298464324817070923729583289916131280e-45, f64 = 4.940656458412465441765687928682213723651e-324,
			ti = '0000-00-00 00:00:00', da = '0000-00-00',
			te = '', bo = false, js = '', bl = ''
			;`,
		[]sql.Row{{sql.NewOkResult(1)}},
		"SELECT * FROM typestable WHERE id = 999;",
		[]sql.Row{{
			int64(999), int8(-math.MaxInt8 - 1), int16(-math.MaxInt16 - 1), int32(-math.MaxInt32 - 1), int64(-math.MaxInt64 - 1),
			uint8(0), uint16(0), uint32(0), uint64(0),
			float32(math.SmallestNonzeroFloat32), float64(math.SmallestNonzeroFloat64),
			sql.Timestamp.Zero(), sql.Date.Zero(),
			"", sql.False, ([]byte)(`""`), "",
		}},
	},
	{
		`REPLACE INTO typestable VALUES (999, null, null, null, null, null, null, null, null,
			null, null, null, null, null, null, null, null);`,
		[]sql.Row{{sql.NewOkResult(1)}},
		"SELECT * FROM typestable WHERE id = 999;",
		[]sql.Row{{int64(999), nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil}},
	},
	{
		`REPLACE INTO typestable SET id=999, i8=null, i16=null, i32=null, i64=null, u8=null, u16=null, u32=null, u64=null,
			f32=null, f64=null, ti=null, da=null, te=null, bo=null, js=null, bl=null;`,
		[]sql.Row{{sql.NewOkResult(1)}},
		"SELECT * FROM typestable WHERE id = 999;",
		[]sql.Row{{int64(999), nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil}},
	},
}

TODO: none of these tests insert into tables without primary key columns, which have different semantics for 

REPLACE INTO queries. Add some tables / data without primary keys.
View Source 
var UpdateErrorTests = []GenericErrorQueryTest{
	{
		"invalid table",
		"UPDATE doesnotexist SET i = 0;",
	},
	{
		"invalid column set",
		"UPDATE mytable SET z = 0;",
	},
	{
		"invalid column set value",
		"UPDATE mytable SET i = z;",
	},
	{
		"invalid column where",
		"UPDATE mytable SET s = 'hi' WHERE z = 1;",
	},
	{
		"invalid column order by",
		"UPDATE mytable SET s = 'hi' ORDER BY z;",
	},
	{
		"negative limit",
		"UPDATE mytable SET s = 'hi' LIMIT -1;",
	},
	{
		"negative offset",
		"UPDATE mytable SET s = 'hi' LIMIT 1 OFFSET -1;",
	},
	{
		"set null on non-nullable",
		"UPDATE mytable SET s = NULL;",
	},
}
View Source 
var UpdateTests = []WriteQueryTest{
	{
		"UPDATE mytable SET s = 'updated';",
		[]sql.Row{{newUpdateResult(3, 3)}},
		"SELECT * FROM mytable;",
		[]sql.Row{{int64(1), "updated"}, {int64(2), "updated"}, {int64(3), "updated"}},
	},
	{
		"UPDATE mytable SET s = 'updated' WHERE i > 9999;",
		[]sql.Row{{newUpdateResult(0, 0)}},
		"SELECT * FROM mytable;",
		[]sql.Row{{int64(1), "first row"}, {int64(2), "second row"}, {int64(3), "third row"}},
	},
	{
		"UPDATE mytable SET s = 'updated' WHERE i = 1;",
		[]sql.Row{{newUpdateResult(1, 1)}},
		"SELECT * FROM mytable;",
		[]sql.Row{{int64(1), "updated"}, {int64(2), "second row"}, {int64(3), "third row"}},
	},
	{
		"UPDATE mytable SET s = 'updated' WHERE i <> 9999;",
		[]sql.Row{{newUpdateResult(3, 3)}},
		"SELECT * FROM mytable;",
		[]sql.Row{{int64(1), "updated"}, {int64(2), "updated"}, {int64(3), "updated"}},
	},
	{
		"UPDATE floattable SET f32 = f32 + f32, f64 = f32 * f64 WHERE i = 2;",
		[]sql.Row{{newUpdateResult(1, 1)}},
		"SELECT * FROM floattable WHERE i = 2;",
		[]sql.Row{{int64(2), float32(3.0), float64(4.5)}},
	},
	{
		"UPDATE floattable SET f32 = 5, f32 = 4 WHERE i = 1;",
		[]sql.Row{{newUpdateResult(1, 1)}},
		"SELECT f32 FROM floattable WHERE i = 1;",
		[]sql.Row{{float32(4.0)}},
	},
	{
		"UPDATE mytable SET s = 'first row' WHERE i = 1;",
		[]sql.Row{{newUpdateResult(1, 0)}},
		"SELECT * FROM mytable;",
		[]sql.Row{{int64(1), "first row"}, {int64(2), "second row"}, {int64(3), "third row"}},
	},
	{
		"UPDATE niltable SET b = NULL WHERE f IS NULL;",
		[]sql.Row{{newUpdateResult(3, 2)}},
		"SELECT i,b FROM niltable WHERE f IS NULL;",
		[]sql.Row{{int64(1), nil}, {int64(2), nil}, {int64(3), nil}},
	},
	{
		"UPDATE mytable SET s = 'updated' ORDER BY i ASC LIMIT 2;",
		[]sql.Row{{newUpdateResult(2, 2)}},
		"SELECT * FROM mytable;",
		[]sql.Row{{int64(1), "updated"}, {int64(2), "updated"}, {int64(3), "third row"}},
	},
	{
		"UPDATE mytable SET s = 'updated' ORDER BY i DESC LIMIT 2;",
		[]sql.Row{{newUpdateResult(2, 2)}},
		"SELECT * FROM mytable;",
		[]sql.Row{{int64(1), "first row"}, {int64(2), "updated"}, {int64(3), "updated"}},
	},
	{
		"UPDATE mytable SET s = 'updated' ORDER BY i LIMIT 1 OFFSET 1;",
		[]sql.Row{{newUpdateResult(1, 1)}},
		"SELECT * FROM mytable;",
		[]sql.Row{{int64(1), "first row"}, {int64(2), "updated"}, {int64(3), "third row"}},
	},
	{
		"UPDATE mytable SET s = 'updated';",
		[]sql.Row{{newUpdateResult(3, 3)}},
		"SELECT * FROM mytable;",
		[]sql.Row{{int64(1), "updated"}, {int64(2), "updated"}, {int64(3), "updated"}},
	},
	{
		"UPDATE typestable SET ti = '2020-03-06 00:00:00';",
		[]sql.Row{{newUpdateResult(1, 1)}},
		"SELECT * FROM typestable;",
		[]sql.Row{{
			int64(1),
			int8(2),
			int16(3),
			int32(4),
			int64(5),
			uint8(6),
			uint16(7),
			uint32(8),
			uint64(9),
			float32(10),
			float64(11),
			sql.Timestamp.MustConvert("2020-03-06 00:00:00"),
			sql.Date.MustConvert("2019-12-31"),
			"fourteen",
			false,
			nil,
			nil}},
	},
	{
		"UPDATE typestable SET ti = '2020-03-06 00:00:00', da = '2020-03-06';",
		[]sql.Row{{newUpdateResult(1, 1)}},
		"SELECT * FROM typestable;",
		[]sql.Row{{
			int64(1),
			int8(2),
			int16(3),
			int32(4),
			int64(5),
			uint8(6),
			uint16(7),
			uint32(8),
			uint64(9),
			float32(10),
			float64(11),
			sql.Timestamp.MustConvert("2020-03-06 00:00:00"),
			sql.Date.MustConvert("2020-03-06"),
			"fourteen",
			false,
			nil,
			nil}},
	},
	{
		"UPDATE typestable SET da = '0000-00-00', ti = '0000-00-00 00:00:00';",
		[]sql.Row{{newUpdateResult(1, 1)}},
		"SELECT * FROM typestable;",
		[]sql.Row{{
			int64(1),
			int8(2),
			int16(3),
			int32(4),
			int64(5),
			uint8(6),
			uint16(7),
			uint32(8),
			uint64(9),
			float32(10),
			float64(11),
			sql.Timestamp.Zero(),
			sql.Date.Zero(),
			"fourteen",
			false,
			nil,
			nil}},
	},
}
View Source 
var VersionedQueries = []QueryTest{
	{
		"SELECT *  FROM myhistorytable AS OF '2019-01-01' AS foo ORDER BY i",
		[]sql.Row{
			{int64(1), "first row, 1"},
			{int64(2), "second row, 1"},
			{int64(3), "third row, 1"},
		},
	},
	{
		"SELECT *  FROM myhistorytable AS OF '2019-01-02' foo ORDER BY i",
		[]sql.Row{
			{int64(1), "first row, 2"},
			{int64(2), "second row, 2"},
			{int64(3), "third row, 2"},
		},
	},
	{
		"SELECT *  FROM myhistorytable ORDER BY i",
		[]sql.Row{
			{int64(1), "first row, 2"},
			{int64(2), "second row, 2"},
			{int64(3), "third row, 2"},
		},
	},
}
View Source 
var VersionedViewTests = []QueryTest{
	{
		"SELECT * FROM myview1 ORDER BY i",
		[]sql.Row{
			sql.NewRow(int64(1), "first row, 2"),
			sql.NewRow(int64(2), "second row, 2"),
			sql.NewRow(int64(3), "third row, 2"),
		},
	},
	{
		"SELECT t.* FROM myview1 AS t ORDER BY i",
		[]sql.Row{
			sql.NewRow(int64(1), "first row, 2"),
			sql.NewRow(int64(2), "second row, 2"),
			sql.NewRow(int64(3), "third row, 2"),
		},
	},
	{
		"SELECT t.i FROM myview1 AS t ORDER BY i",
		[]sql.Row{
			sql.NewRow(int64(1)),
			sql.NewRow(int64(2)),
			sql.NewRow(int64(3)),
		},
	},
	{
		"SELECT * FROM myview1 AS OF '2019-01-01' ORDER BY i",
		[]sql.Row{
			sql.NewRow(int64(1), "first row, 1"),
			sql.NewRow(int64(2), "second row, 1"),
			sql.NewRow(int64(3), "third row, 1"),
		},
	},
	{
		"SELECT * FROM myview2",
		[]sql.Row{
			sql.NewRow(int64(1), "first row, 2"),
		},
	},
	{
		"SELECT i FROM myview2",
		[]sql.Row{
			sql.NewRow(int64(1)),
		},
	},
	{
		"SELECT myview2.i FROM myview2",
		[]sql.Row{
			sql.NewRow(int64(1)),
		},
	},
	{
		"SELECT myview2.* FROM myview2",
		[]sql.Row{
			sql.NewRow(int64(1), "first row, 2"),
		},
	},
	{
		"SELECT t.* FROM myview2 as t",
		[]sql.Row{
			sql.NewRow(int64(1), "first row, 2"),
		},
	},
	{
		"SELECT t.i FROM myview2 as t",
		[]sql.Row{
			sql.NewRow(int64(1)),
		},
	},
	{
		"SELECT * FROM myview2 AS OF '2019-01-01'",
		[]sql.Row{
			sql.NewRow(int64(1), "first row, 1"),
		},
	},

	{
		"select * from information_schema.views where table_schema = 'mydb'",
		[]sql.Row{
			sql.NewRow("def", "mydb", "myview", "SELECT * FROM mytable", "NONE", "YES", "", "DEFINER", "utf8mb4", "utf8_bin"),
			sql.NewRow("def", "mydb", "myview1", "SELECT * FROM myhistorytable", "NONE", "YES", "", "DEFINER", "utf8mb4", "utf8_bin"),
			sql.NewRow("def", "mydb", "myview2", "SELECT * FROM myview1 WHERE i = 1", "NONE", "YES", "", "DEFINER", "utf8mb4", "utf8_bin"),
		},
	},
	{
		"select table_name from information_schema.tables where table_schema = 'mydb' and table_type = 'VIEW' order by 1",
		[]sql.Row{
			sql.NewRow("myview"),
			sql.NewRow("myview1"),
			sql.NewRow("myview2"),
		},
	},
}
View Source 
var ViewTests = []QueryTest{
	{
		"SELECT * FROM myview ORDER BY i",
		[]sql.Row{
			sql.NewRow(int64(1), "first row"),
			sql.NewRow(int64(2), "second row"),
			sql.NewRow(int64(3), "third row"),
		},
	},
	{
		"SELECT myview.* FROM myview ORDER BY i",
		[]sql.Row{
			sql.NewRow(int64(1), "first row"),
			sql.NewRow(int64(2), "second row"),
			sql.NewRow(int64(3), "third row"),
		},
	},
	{
		"SELECT i FROM myview ORDER BY i",
		[]sql.Row{
			sql.NewRow(int64(1)),
			sql.NewRow(int64(2)),
			sql.NewRow(int64(3)),
		},
	},
	{
		"SELECT t.* FROM myview AS t ORDER BY i",
		[]sql.Row{
			sql.NewRow(int64(1), "first row"),
			sql.NewRow(int64(2), "second row"),
			sql.NewRow(int64(3), "third row"),
		},
	},
	{
		"SELECT t.i FROM myview AS t ORDER BY i",
		[]sql.Row{
			sql.NewRow(int64(1)),
			sql.NewRow(int64(2)),
			sql.NewRow(int64(3)),
		},
	},
	{
		"SELECT * FROM myview2",
		[]sql.Row{
			sql.NewRow(int64(1), "first row"),
		},
	},
	{
		"SELECT i FROM myview2",
		[]sql.Row{
			sql.NewRow(int64(1)),
		},
	},
	{
		"SELECT myview2.i FROM myview2",
		[]sql.Row{
			sql.NewRow(int64(1)),
		},
	},
	{
		"SELECT myview2.* FROM myview2",
		[]sql.Row{
			sql.NewRow(int64(1), "first row"),
		},
	},
	{
		"SELECT t.* FROM myview2 as t",
		[]sql.Row{
			sql.NewRow(int64(1), "first row"),
		},
	},
	{
		"SELECT t.i FROM myview2 as t",
		[]sql.Row{
			sql.NewRow(int64(1)),
		},
	},

	{
		"select * from information_schema.views where table_schema = 'mydb'",
		[]sql.Row{
			sql.NewRow("def", "mydb", "myview", "SELECT * FROM mytable", "NONE", "YES", "", "DEFINER", "utf8mb4", "utf8_bin"),
			sql.NewRow("def", "mydb", "myview2", "SELECT * FROM myview WHERE i = 1", "NONE", "YES", "", "DEFINER", "utf8mb4", "utf8_bin"),
		},
	},
	{
		"select table_name from information_schema.tables where table_schema = 'mydb' and table_type = 'VIEW' order by 1",
		[]sql.Row{
			sql.NewRow("myview"),
			sql.NewRow("myview2"),
		},
	},
}

Functions

func CreateSubsetTestData 

func CreateSubsetTestData(t *testing.T, harness Harness, includedTables []string) []sql.Database

createSubsetTestData creates test tables and data. Passing a non-nil slice for includedTables will restrict the table creation to just those tables named.

func CreateTestData 

func CreateTestData(t *testing.T, harness Harness) []sql.Database

createTestData uses the provided harness to create test tables and data for many of the other tests.

func DeleteRows 

func DeleteRows(t *testing.T, ctx *sql.Context, table sql.DeletableTable, rows ...sql.Row)

func InsertRows 

func InsertRows(t *testing.T, ctx *sql.Context, table sql.InsertableTable, rows ...sql.Row)

func NewBaseSession 

func NewBaseSession() sql.Session

Returns a new BaseSession compatible with these tests. Most tests will work with any session implementation, but for full compatibility use a session based on this one.

func NewContext 

func NewContext(harness Harness) *sql.Context

func NewContextWithEngine 

func NewContextWithEngine(harness Harness, engine *sqle.Engine) *sql.Context

func NewCtx 

func NewCtx(idxReg *sql.IndexRegistry) *sql.Context

func NewEngine 

func NewEngine(t *testing.T, harness Harness) *sqle.Engine

NewEngine creates test data and returns an engine using the harness provided.

func NewEngineWithDbs 

func NewEngineWithDbs(t *testing.T, parallelism int, databases []sql.Database, driver sql.IndexDriver) *sqle.Engine

NewEngineWithDbs returns a new engine with the databases provided. This is useful if you don't want to implement a full harness but want to run your own tests on DBs you create.

func TestAddColumn 

func TestAddColumn(t *testing.T, harness Harness)

func TestAmbiguousColumnResolution 

func TestAmbiguousColumnResolution(t *testing.T, harness Harness)

func TestClearWarnings 

func TestClearWarnings(t *testing.T, harness Harness)

func TestColumnAliases 

func TestColumnAliases(t *testing.T, harness Harness)

TestColumnAliases exercises the logic for naming and referring to column aliases, and unlike other tests in this file checks that the name of the columns in the result schema is correct.

func TestCreateTable 

func TestCreateTable(t *testing.T, harness Harness)

func TestDelete 

func TestDelete(t *testing.T, harness Harness)

func TestDeleteErrors 

func TestDeleteErrors(t *testing.T, harness Harness)

func TestDropColumn 

func TestDropColumn(t *testing.T, harness Harness)

func TestDropTable 

func TestDropTable(t *testing.T, harness Harness)

func TestExplode 

func TestExplode(t *testing.T, harness Harness)

func TestInfoSchema 

func TestInfoSchema(t *testing.T, harness Harness)

Runs tests of the information_schema database.

func TestInnerNestedInNaturalJoins 

func TestInnerNestedInNaturalJoins(t *testing.T, harness Harness)

func TestInsertInto 

func TestInsertInto(t *testing.T, harness Harness)

func TestInsertIntoErrors 

func TestInsertIntoErrors(t *testing.T, harness Harness)

func TestModifyColumn 

func TestModifyColumn(t *testing.T, harness Harness)

func TestNaturalJoin 

func TestNaturalJoin(t *testing.T, harness Harness)

func TestNaturalJoinDisjoint 

func TestNaturalJoinDisjoint(t *testing.T, harness Harness)

func TestNaturalJoinEqual 

func TestNaturalJoinEqual(t *testing.T, harness Harness)

func TestOrderByGroupBy 

func TestOrderByGroupBy(t *testing.T, harness Harness)

func TestQueries 

func TestQueries(t *testing.T, harness Harness)

Tests a variety of queries against databases and tables provided by the given harness.

func TestQuery 

func TestQuery(t *testing.T, harness Harness, e *sqle.Engine, q string, expected []sql.Row)

TestQuery runs a query on the engine given and asserts that results are as expected.

func TestQueryErrors 

func TestQueryErrors(t *testing.T, harness Harness)

func TestQueryPlan 

func TestQueryPlan(t *testing.T, ctx *sql.Context, engine *sqle.Engine, query string, expectedPlan string)

TestQueryPlan analyzes the query given and asserts that its printed plan matches the expected one.

func TestQueryPlans 

func TestQueryPlans(t *testing.T, harness Harness)

Tests generating the correct query plans for various queries using databases and tables provided by the given harness.

func TestQueryWithContext 

func TestQueryWithContext(t *testing.T, ctx *sql.Context, e *sqle.Engine, q string, expected []sql.Row)

func TestReadOnly 

func TestReadOnly(t *testing.T, harness Harness)

func TestRenameColumn 

func TestRenameColumn(t *testing.T, harness Harness)

func TestRenameTable 

func TestRenameTable(t *testing.T, harness Harness)

func TestReplaceInto 

func TestReplaceInto(t *testing.T, harness Harness)

func TestReplaceIntoErrors 

func TestReplaceIntoErrors(t *testing.T, harness Harness)

func TestSessionDefaults 

func TestSessionDefaults(t *testing.T, harness Harness)

func TestSessionSelectLimit 

func TestSessionSelectLimit(t *testing.T, harness Harness)

func TestSessionVariables 

func TestSessionVariables(t *testing.T, harness Harness)

func TestSessionVariablesONOFF 

func TestSessionVariablesONOFF(t *testing.T, harness Harness)

func TestTracing 

func TestTracing(t *testing.T, harness Harness)

func TestUpdate 

func TestUpdate(t *testing.T, harness Harness)

func TestUpdateErrors 

func TestUpdateErrors(t *testing.T, harness Harness)

func TestUse 

func TestUse(t *testing.T, harness Harness)

func TestVersionedQueries 

func TestVersionedQueries(t *testing.T, harness Harness)

Tests a variety of queries against databases and tables provided by the given harness.

func TestVersionedViews 

func TestVersionedViews(t *testing.T, harness Harness)

func TestViews 

func TestViews(t *testing.T, harness Harness)

func TestWarnings 

func TestWarnings(t *testing.T, harness Harness)

Types

type GenericErrorQueryTest 

type GenericErrorQueryTest struct {
	Name  string
	Query string
}

GenericErrorQueryTest is a query test that is used to assert an error occurs for some query, without specifying what the error was.

type Harness 

type Harness interface {
	// Parallelism returns how many parallel go routines to use when constructing an engine for test.
	Parallelism() int
	// NewDatabase returns a new sql.Database to use for a test.
	NewDatabase(name string) sql.Database
	// NewTable takes a database previously created by NewDatabase and returns a table created with the given schema.
	NewTable(db sql.Database, name string, schema sql.Schema) (sql.Table, error)
	// NewContext allows a harness to specify any sessions or context variables necessary for the proper functioning of
	// their engine implementation. Every harnessed engine test uses the context created by this method, with some
	// additional information (e.g. current DB) set uniformly. To replicated the behavior of tests during setup,
	// harnesses should generally dispatch to enginetest.NewContext(harness), rather than calling this method themselves.
	NewContext() *sql.Context
}

Harness provides a way for database integrators to validate their implementation against the standard set of queries used to develop and test the engine itself. See memory_engine_test.go for an example.

type IndexDriverHarness 

type IndexDriverHarness interface {
	Harness
	// IndexDriver returns an index driver for the databases given, which will have been created by calls to
	// NewDatabase().
	IndexDriver(dbs []sql.Database) sql.IndexDriver
}

IndexDriverHarness is an extension to Harness that lets an integrator test their implementation alongside an index driver they provide.

type IndexHarness 

type IndexHarness interface {
	Harness
	// SupportsNativeIndexCreation returns whether this harness should accept CREATE INDEX statements as part of test
	// setup.
	SupportsNativeIndexCreation() bool
}

IndexHarness is an extension to Harness that lets an integrator test their implementation with native (table-supplied) indexes. Integrator tables must implement sql.IndexAlterableTable.

type QueryErrorTest 

type QueryErrorTest struct {
	Query       string
	ExpectedErr *errors.Kind
}

type QueryPlanTest 

type QueryPlanTest struct {
	Query        string
	ExpectedPlan string
}

type QueryTest 

type QueryTest struct {
	Query    string
	Expected []sql.Row
}

type SkippingHarness 

type SkippingHarness interface {
	// SkipQueryTest returns whether to skip a test of the provided query string.
	SkipQueryTest(query string) bool
}

SkippingHarness provides a way for integrators to skip tests that are known to be broken. E.g., integrators that can't handle every possible SQL type.

type VersionedDBHarness 

type VersionedDBHarness interface {
	Harness
	// NewTableAsOf creates a new table with the given name and schema, optionally handling snapshotting with the asOf
	// identifier. NewTableAsOf must ignore tables that already exist in the database. Tables returned by this method do
	// not need to have any previously created data in them, but they can. This behavior is implementation specific, and
	// the harness works either way.
	NewTableAsOf(db sql.VersionedDatabase, name string, schema sql.Schema, asOf interface{}) sql.Table
	// SnapshotTable creates a snapshot of the table named with the given asOf label. Depending on the implementation,
	// NewTableAsOf might do all the necessary work to create such snapshots, so this could be a no-op.
	SnapshotTable(db sql.VersionedDatabase, name string, asOf interface{}) error
}

VersionedDBHarness is an extension to Harness that lets an integrator test their implementation of versioned (AS OF) queries. Integrators must implement sql.VersionedDatabase. For each table version being created, there will be a call to NewTableAsOf, some number of Delete and Insert operations, and then a call to SnapshotTable.

type WriteQueryTest 

type WriteQueryTest struct {
	WriteQuery          string
	ExpectedWriteResult []sql.Row
	SelectQuery         string
	ExpectedSelect      []sql.Row
}

WriteQueryTest is a query test for INSERT, UPDATE, etc. statements. It has a query to run and a select query to validate the results.

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.