enginetest

package

v0.8.0 Latest Latest Go to latest Published: Feb 1, 2021 License: Apache-2.0 Imports: 20 Imported by: 7

Details

Valid go.mod file
Redistributable license
Tagged version
Stable version
Learn more about best practices

Repository

github.com/dolthub/go-mysql-server

Links

Open Source Insights

Documentation ¶

Overview ¶

Example ¶

package main

import (
	"context"
	"fmt"
	"io"

	sqle "github.com/dolthub/go-mysql-server"
	"github.com/dolthub/go-mysql-server/memory"
	"github.com/dolthub/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 ¶

Variables
func AssertErr(t *testing.T, e *sqle.Engine, harness Harness, query string, ...)
func CreateSubsetTestData(t *testing.T, harness Harness, includedTables []string) []sql.Database
func CreateTestData(t *testing.T, harness Harness) []sql.Database
func DeleteRows(t *testing.T, ctx *sql.Context, table sql.DeletableTable, rows ...sql.Row)
func InsertRows(t *testing.T, ctx *sql.Context, table sql.InsertableTable, rows ...sql.Row)
func NewBaseSession() sql.Session
func NewContext(harness Harness) *sql.Context
func NewContextWithEngine(harness Harness, engine *sqle.Engine) *sql.Context
func NewEngine(t *testing.T, harness Harness) *sqle.Engine
func NewEngineWithDbs(t *testing.T, harness Harness, databases []sql.Database, ...) *sqle.Engine
func RunQuery(t *testing.T, e *sqle.Engine, harness Harness, query string)
func RunQueryTests(t *testing.T, harness Harness, queries []QueryTest)
func TestAddColumn(t *testing.T, harness Harness)
func TestAmbiguousColumnResolution(t *testing.T, harness Harness)
func TestClearWarnings(t *testing.T, harness Harness)
func TestColumnAliases(t *testing.T, harness Harness)
func TestColumnDefaults(t *testing.T, harness Harness)
func TestCreateForeignKeys(t *testing.T, harness Harness)
func TestCreateTable(t *testing.T, harness Harness)
func TestDelete(t *testing.T, harness Harness)
func TestDeleteErrors(t *testing.T, harness Harness)
func TestDropColumn(t *testing.T, harness Harness)
func TestDropForeignKeys(t *testing.T, harness Harness)
func TestDropTable(t *testing.T, harness Harness)
func TestExplode(t *testing.T, harness Harness)
func TestInfoSchema(t *testing.T, harness Harness)
func TestInnerNestedInNaturalJoins(t *testing.T, harness Harness)
func TestInsertInto(t *testing.T, harness Harness)
func TestInsertIntoErrors(t *testing.T, harness Harness)
func TestModifyColumn(t *testing.T, harness Harness)
func TestNaturalJoin(t *testing.T, harness Harness)
func TestNaturalJoinDisjoint(t *testing.T, harness Harness)
func TestNaturalJoinEqual(t *testing.T, harness Harness)
func TestOrderByGroupBy(t *testing.T, harness Harness)
func TestQueries(t *testing.T, harness Harness)
func TestQuery(t *testing.T, harness Harness, e *sqle.Engine, q string, expected []sql.Row, ...)
func TestQueryErrors(t *testing.T, harness Harness)
func TestQueryPlan(t *testing.T, ctx *sql.Context, engine *sqle.Engine, query string, ...)
func TestQueryPlans(t *testing.T, harness Harness)
func TestQueryWithContext(t *testing.T, ctx *sql.Context, e *sqle.Engine, q string, expected []sql.Row, ...)
func TestReadOnly(t *testing.T, harness Harness)
func TestRenameColumn(t *testing.T, harness Harness)
func TestRenameTable(t *testing.T, harness Harness)
func TestReplaceInto(t *testing.T, harness Harness)
func TestReplaceIntoErrors(t *testing.T, harness Harness)
func TestScript(t *testing.T, harness Harness, script ScriptTest) bool
func TestScriptWithEngine(t *testing.T, e *sqle.Engine, harness Harness, script ScriptTest)
func TestScripts(t *testing.T, harness Harness)
func TestSessionSelectLimit(t *testing.T, harness Harness)
func TestTracing(t *testing.T, harness Harness)
func TestTriggerErrors(t *testing.T, harness Harness)
func TestTriggers(t *testing.T, harness Harness)
func TestTruncate(t *testing.T, harness Harness)
func TestUpdate(t *testing.T, harness Harness)
func TestUpdateErrors(t *testing.T, harness Harness)
func TestUse(t *testing.T, harness Harness)
func TestVariableErrors(t *testing.T, harness Harness)
func TestVariables(t *testing.T, harness Harness)
func TestVersionedQueries(t *testing.T, harness Harness)
func TestVersionedViews(t *testing.T, harness Harness)
func TestViews(t *testing.T, harness Harness)
func TestWarnings(t *testing.T, harness Harness)
func WidenRow(row sql.Row) sql.Row
func WidenRows(rows []sql.Row) []sql.Row
type ForeignKeyHarness
type GenericErrorQueryTest
type Harness
type IndexDriverHarness
type IndexDriverInitalizer
type IndexHarness
type KeylessTableHarness
type MemoryHarness
- func NewDefaultMemoryHarness() *MemoryHarness
- func NewMemoryHarness(name string, parallelism int, numTablePartitions int, useNativeIndexes bool, ...) *MemoryHarness
- func (m *MemoryHarness) IndexDriver(dbs []sql.Database) sql.IndexDriver
- func (m *MemoryHarness) NewContext() *sql.Context
- func (m *MemoryHarness) NewDatabase(name string) sql.Database
- func (m *MemoryHarness) NewTable(db sql.Database, name string, schema sql.Schema) (sql.Table, error)
- func (m *MemoryHarness) NewTableAsOf(db sql.VersionedDatabase, name string, schema sql.Schema, asOf interface{}) sql.Table
- func (m *MemoryHarness) Parallelism() int
- func (m *MemoryHarness) SnapshotTable(db sql.VersionedDatabase, name string, asOf interface{}) error
- func (m *MemoryHarness) SupportsForeignKeys() bool
- func (m *MemoryHarness) SupportsKeylessTables() bool
- func (m *MemoryHarness) SupportsNativeIndexCreation() bool
type QueryErrorTest
type QueryPlanTest
type QueryTest
type ScriptTest
type ScriptTestAssertion
type SkippingHarness
type SkippingMemoryHarness
- func NewSkippingMemoryHarness() *SkippingMemoryHarness
- func (s SkippingMemoryHarness) SkipQueryTest(query string) bool
type VersionedDBHarness
type WriteQueryTest

Examples ¶

Package

Constants ¶

This section is empty.

Variables ¶

View Source

var BrokenQueries = []QueryTest{
	{
		Query:    "SELECT pk1, SUM(c1) FROM two_pk",
		Expected: []sql.Row{{0, 60.0}},
	},

	{
		Query: `SELECT pk, (SELECT max(pk) FROM one_pk WHERE pk < opk.pk) AS x 
						FROM one_pk opk WHERE x > 0 ORDER BY x`,
		Expected: []sql.Row{
			{2, 1},
			{3, 2},
		},
	},
	{
		Query: `SELECT pk,
					(SELECT max(pk) FROM one_pk WHERE pk < opk.pk) AS min,
					(SELECT min(pk) FROM one_pk WHERE pk > opk.pk) AS max
					FROM one_pk opk
					WHERE max > 1
					ORDER BY max;`,
		Expected: []sql.Row{
			{1, 0, 2},
			{2, 1, 3},
		},
	},

	{
		Query: `SELECT pk,
						(SELECT sum(c1) FROM two_pk WHERE c1 IN (SELECT c4 FROM two_pk WHERE c3 > opk.c5)) AS sum,
						(SELECT avg(c1) FROM two_pk WHERE pk2 IN (SELECT pk2 FROM two_pk WHERE c1 < opk.c2)) AS avg
					FROM one_pk opk ORDER BY pk`,
		Expected: []sql.Row{
			{0, 60.0, nil},
			{1, 50.0, 10.0},
			{2, 30.0, 15.0},
			{3, nil, 15.0},
		},
	},

	{
		Query: `SELECT pk,pk2, 
							(SELECT opk.c5 FROM one_pk opk JOIN two_pk tpk ON pk=pk1 ORDER BY 1 LIMIT 1) 
							FROM one_pk t1, two_pk t2 WHERE pk=1 AND pk2=1 ORDER BY 1,2`,
		Expected: []sql.Row{
			{1, 1, 4},
			{1, 1, 4},
		},
	},

	{
		Query: `SELECT pk,pk2, 
							(SELECT opk.c5 FROM one_pk opk JOIN two_pk tpk ON opk.c5=tpk.c5 ORDER BY 1 LIMIT 1) 
							FROM one_pk t1, two_pk t2 WHERE pk=1 AND pk2=1 ORDER BY 1,2`,
		Expected: []sql.Row{
			{1, 1, 4},
			{1, 1, 4},
		},
	},

	{
		Query: `SELECT pk,tpk.pk1,tpk2.pk1,tpk.pk2,tpk2.pk2 FROM one_pk 
						LEFT JOIN two_pk tpk ON one_pk.pk=tpk.pk1 AND one_pk.pk=tpk.pk2 
						JOIN two_pk tpk2 ON tpk2.pk1=TPK.pk2 AND TPK2.pk2=tpk.pk1`,
		Expected: []sql.Row{
			{0, 0, 0, 0, 0},
			{1, 1, 1, 1, 1},
		},
	},

	{
		Query: `SELECT pk,nt.i,nt2.i FROM one_pk
						RIGHT JOIN niltable nt ON pk=nt.i
						RIGHT JOIN niltable nt2 ON pk=nt2.i - 1
						ORDER BY 3`,
		Expected: []sql.Row{
			{nil, nil, 1},
			{1, 1, 2},
			{2, 2, 3},
			{3, 3, 4},
			{nil, nil, 5},
			{nil, nil, 6},
		},
	},
}

Queries that are known to be broken in the engine.

View Source

var DeleteErrorTests = []GenericErrorQueryTest{
	{
		Name:  "invalid table",
		Query: "DELETE FROM invalidtable WHERE x < 1;",
	},
	{
		Name:  "invalid column",
		Query: "DELETE FROM mytable WHERE z = 'dne';",
	},
	{
		Name:  "missing binding",
		Query: "DELETE FROM mytable WHERE i = ?;",
	},
	{
		Name:  "negative limit",
		Query: "DELETE FROM mytable LIMIT -1;",
	},
	{
		Name:  "negative offset",
		Query: "DELETE FROM mytable LIMIT 1 OFFSET -1;",
	},
	{
		Name:  "missing keyword from",
		Query: "DELETE mytable WHERE id = 1;",
	},
}

View Source

var DeleteTests = []WriteQueryTest{
	{
		WriteQuery:          "DELETE FROM mytable;",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(3)}},
		SelectQuery:         "SELECT * FROM mytable;",
		ExpectedSelect:      nil,
	},
	{
		WriteQuery:          "DELETE FROM mytable WHERE i = 2;",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM mytable;",
		ExpectedSelect:      []sql.Row{{int64(1), "first row"}, {int64(3), "third row"}},
	},
	{
		WriteQuery:          "DELETE FROM mytable WHERE i = ?;",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM mytable;",
		ExpectedSelect:      []sql.Row{{int64(1), "first row"}, {int64(3), "third row"}},
		Bindings: map[string]sql.Expression{
			"v1": expression.NewLiteral(int64(2), sql.Int64),
		},
	},
	{
		WriteQuery:          "DELETE FROM mytable WHERE i < 3;",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(2)}},
		SelectQuery:         "SELECT * FROM mytable;",
		ExpectedSelect:      []sql.Row{{int64(3), "third row"}},
	},
	{
		WriteQuery:          "DELETE FROM mytable WHERE i > 1;",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(2)}},
		SelectQuery:         "SELECT * FROM mytable;",
		ExpectedSelect:      []sql.Row{{int64(1), "first row"}},
	},
	{
		WriteQuery:          "DELETE FROM mytable WHERE i <= 2;",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(2)}},
		SelectQuery:         "SELECT * FROM mytable;",
		ExpectedSelect:      []sql.Row{{int64(3), "third row"}},
	},
	{
		WriteQuery:          "DELETE FROM mytable WHERE i >= 2;",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(2)}},
		SelectQuery:         "SELECT * FROM mytable;",
		ExpectedSelect:      []sql.Row{{int64(1), "first row"}},
	},
	{
		WriteQuery:          "DELETE FROM mytable WHERE s = 'first row';",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM mytable;",
		ExpectedSelect:      []sql.Row{{int64(2), "second row"}, {int64(3), "third row"}},
	},
	{
		WriteQuery:          "DELETE FROM mytable WHERE s <> 'dne';",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(3)}},
		SelectQuery:         "SELECT * FROM mytable;",
		ExpectedSelect:      nil,
	},
	{
		WriteQuery:          "DELETE FROM mytable WHERE i in (2,3);",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(2)}},
		SelectQuery:         "SELECT * FROM mytable;",
		ExpectedSelect:      []sql.Row{{int64(1), "first row"}},
	},
	{
		WriteQuery:          "DELETE FROM mytable WHERE s LIKE '%row';",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(3)}},
		SelectQuery:         "SELECT * FROM mytable;",
		ExpectedSelect:      nil,
	},
	{
		WriteQuery:          "DELETE FROM mytable WHERE s = 'dne';",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(0)}},
		SelectQuery:         "SELECT * FROM mytable;",
		ExpectedSelect:      []sql.Row{{int64(1), "first row"}, {int64(2), "second row"}, {int64(3), "third row"}},
	},
	{
		WriteQuery:          "DELETE FROM mytable WHERE i = 'invalid';",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(0)}},
		SelectQuery:         "SELECT * FROM mytable;",
		ExpectedSelect:      []sql.Row{{int64(1), "first row"}, {int64(2), "second row"}, {int64(3), "third row"}},
	},
	{
		WriteQuery:          "DELETE FROM mytable ORDER BY i ASC LIMIT 2;",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(2)}},
		SelectQuery:         "SELECT * FROM mytable;",
		ExpectedSelect:      []sql.Row{{int64(3), "third row"}},
	},
	{
		WriteQuery:          "DELETE FROM mytable ORDER BY i DESC LIMIT 1;",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM mytable;",
		ExpectedSelect:      []sql.Row{{int64(1), "first row"}, {int64(2), "second row"}},
	},
	{
		WriteQuery:          "DELETE FROM mytable ORDER BY i DESC LIMIT 1 OFFSET 1;",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM mytable;",
		ExpectedSelect:      []sql.Row{{int64(1), "first row"}, {int64(3), "third row"}},
	},
	{
		WriteQuery:          "DELETE FROM mytable WHERE (i,s) = (1, 'first row');",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM mytable;",
		ExpectedSelect:      []sql.Row{{int64(2), "second row"}, {int64(3), "third row"}},
	},
}

View Source

var ExplodeQueries = []QueryTest{
	{
		Query: `SELECT a, EXPLODE(b), c FROM t`,
		Expected: []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"},
		},
	},
	{
		Query: `SELECT a, EXPLODE(b) AS x, c FROM t`,
		Expected: []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"},
		},
	},
	{
		Query: `SELECT EXPLODE(SPLIT(c, "")) FROM t LIMIT 5`,
		Expected: []sql.Row{
			{"f"},
			{"i"},
			{"r"},
			{"s"},
			{"t"},
		},
	},
	{
		Query: `SELECT a, EXPLODE(b) AS x, c FROM t WHERE x = 'e'`,
		Expected: []sql.Row{
			{int64(3), "e", "third"},
		},
	},
	{
		Query: `SELECT HEX(UNHEX(375));`,
		Expected: []sql.Row{
			{"0375"},
		},
	},
}

View Source

var InfoSchemaQueries = []QueryTest{
	{
		Query: `SHOW TABLE STATUS FROM mydb`,
		Expected: []sql.Row{
			{"auto_increment_tbl", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8mb4_0900_ai_ci", nil, nil},
			{"mytable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8mb4_0900_ai_ci", nil, nil},
			{"othertable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8mb4_0900_ai_ci", nil, nil},
			{"tabletest", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8mb4_0900_ai_ci", nil, nil},
			{"bigtable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8mb4_0900_ai_ci", nil, nil},
			{"floattable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8mb4_0900_ai_ci", nil, nil},
			{"fk_tbl", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8mb4_0900_ai_ci", nil, nil},
			{"niltable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8mb4_0900_ai_ci", nil, nil},
			{"newlinetable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8mb4_0900_ai_ci", nil, nil},
			{"people", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8mb4_0900_ai_ci", nil, nil},
		},
	},
	{
		Query: `SHOW TABLE STATUS LIKE '%table'`,
		Expected: []sql.Row{
			{"mytable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8mb4_0900_ai_ci", nil, nil},
			{"othertable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8mb4_0900_ai_ci", nil, nil},
			{"bigtable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8mb4_0900_ai_ci", nil, nil},
			{"floattable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8mb4_0900_ai_ci", nil, nil},
			{"niltable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8mb4_0900_ai_ci", nil, nil},
			{"newlinetable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8mb4_0900_ai_ci", nil, nil},
		},
	},
	{
		Query: `SHOW TABLE STATUS WHERE Name = 'mytable'`,
		Expected: []sql.Row{
			{"mytable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8mb4_0900_ai_ci", nil, nil},
		},
	},
	{
		Query: `SHOW TABLE STATUS`,
		Expected: []sql.Row{
			{"auto_increment_tbl", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8mb4_0900_ai_ci", nil, nil},
			{"mytable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8mb4_0900_ai_ci", nil, nil},
			{"othertable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8mb4_0900_ai_ci", nil, nil},
			{"tabletest", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8mb4_0900_ai_ci", nil, nil},
			{"bigtable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8mb4_0900_ai_ci", nil, nil},
			{"fk_tbl", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8mb4_0900_ai_ci", nil, nil},
			{"floattable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8mb4_0900_ai_ci", nil, nil},
			{"niltable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8mb4_0900_ai_ci", nil, nil},
			{"newlinetable", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8mb4_0900_ai_ci", nil, nil},
			{"people", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8mb4_0900_ai_ci", nil, nil},
		},
	},
	{
		Query: "SHOW TABLES",
		Expected: []sql.Row{
			{"auto_increment_tbl"},
			{"bigtable"},
			{"floattable"},
			{"fk_tbl"},
			{"mytable"},
			{"myview"},
			{"newlinetable"},
			{"niltable"},
			{"othertable"},
			{"tabletest"},
			{"people"},
		},
	},
	{
		Query: "SHOW FULL TABLES",
		Expected: []sql.Row{
			{"auto_increment_tbl", "BASE TABLE"},
			{"bigtable", "BASE TABLE"},
			{"fk_tbl", "BASE TABLE"},
			{"floattable", "BASE TABLE"},
			{"mytable", "BASE TABLE"},
			{"myview", "VIEW"},
			{"newlinetable", "BASE TABLE"},
			{"niltable", "BASE TABLE"},
			{"othertable", "BASE TABLE"},
			{"tabletest", "BASE TABLE"},
			{"people", "BASE TABLE"},
		},
	},
	{
		Query: "SHOW TABLES FROM foo",
		Expected: []sql.Row{
			{"other_table"},
		},
	},
	{
		Query: "SHOW TABLES LIKE '%table'",
		Expected: []sql.Row{
			{"mytable"},
			{"othertable"},
			{"bigtable"},
			{"floattable"},
			{"niltable"},
			{"newlinetable"},
		},
	},
	{
		Query: `SHOW COLUMNS FROM mytable`,
		Expected: []sql.Row{
			{"i", "bigint", "NO", "PRI", "", ""},
			{"s", "varchar(20)", "NO", "UNI", "", ""},
		},
	},
	{
		Query: `DESCRIBE mytable`,
		Expected: []sql.Row{
			{"i", "bigint", "NO", "PRI", "", ""},
			{"s", "varchar(20)", "NO", "UNI", "", ""},
		},
	},
	{
		Query: `DESC mytable`,
		Expected: []sql.Row{
			{"i", "bigint", "NO", "PRI", "", ""},
			{"s", "varchar(20)", "NO", "UNI", "", ""},
		},
	},
	{
		Query: `DESCRIBE auto_increment_tbl`,
		Expected: []sql.Row{
			{"pk", "bigint", "NO", "PRI", "", "auto_increment"},
			{"c0", "bigint", "YES", "", "", ""},
		},
	},
	{
		Query: `SHOW COLUMNS FROM mytable WHERE Field = 'i'`,
		Expected: []sql.Row{
			{"i", "bigint", "NO", "PRI", "", ""},
		},
	},
	{
		Query: `SHOW COLUMNS FROM mytable LIKE 'i'`,
		Expected: []sql.Row{
			{"i", "bigint", "NO", "PRI", "", ""},
		},
	},
	{
		Query: `SHOW FULL COLUMNS FROM mytable`,
		Expected: []sql.Row{
			{"i", "bigint", nil, "NO", "PRI", "", "", "", ""},
			{"s", "varchar(20)", "utf8mb4_0900_ai_ci", "NO", "UNI", "", "", "", "column s"},
		},
	},
	{
		Query: "SHOW TABLES WHERE `Table` = 'mytable'",
		Expected: []sql.Row{
			{"mytable"},
		},
	},
	{
		Query: `
		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
		`,
		Expected: nil,
	},
	{
		Query: `
		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
		`,
		Expected: nil,
	},
	{
		Query: `
		SELECT
			COLUMN_NAME,
			JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')
		FROM information_schema.COLUMN_STATISTICS
		WHERE SCHEMA_NAME = 'mydb'
		AND TABLE_NAME = 'mytable'
		`,
		Expected: nil,
	},
	{
		Query: `
		SELECT TABLE_NAME FROM information_schema.TABLES
		WHERE TABLE_SCHEMA='mydb' AND (TABLE_TYPE='BASE TABLE' OR TABLE_TYPE='VIEW')
		ORDER BY 1
		`,
		Expected: []sql.Row{
			{"auto_increment_tbl"},
			{"bigtable"},
			{"fk_tbl"},
			{"floattable"},
			{"mytable"},
			{"myview"},
			{"newlinetable"},
			{"niltable"},
			{"othertable"},
			{"people"},
			{"tabletest"},
		},
	},
	{
		Query: `
		SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.COLUMNS
		WHERE TABLE_SCHEMA='mydb' AND TABLE_NAME='mytable'
		`,
		Expected: []sql.Row{
			{"s", "varchar(20)"},
			{"i", "bigint"},
		},
	},
	{
		Query: `
		SELECT COLUMN_NAME FROM information_schema.COLUMNS
		WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME LIKE '%table'
		GROUP BY COLUMN_NAME
		`,
		Expected: []sql.Row{
			{"s"},
			{"i"},
			{"s2"},
			{"i2"},
			{"t"},
			{"n"},
			{"f32"},
			{"f64"},
			{"b"},
			{"f"},
		},
	},
	{
		Query: `
		SELECT COLUMN_NAME FROM information_schema.COLUMNS
		WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME LIKE '%table'
		GROUP BY 1
		`,
		Expected: []sql.Row{
			{"s"},
			{"i"},
			{"s2"},
			{"i2"},
			{"t"},
			{"n"},
			{"f32"},
			{"f64"},
			{"b"},
			{"f"},
		},
	},
	{
		Query: `
		SELECT COLUMN_NAME AS COLUMN_NAME FROM information_schema.COLUMNS
		WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME LIKE '%table'
		GROUP BY 1
		`,
		Expected: []sql.Row{
			{"s"},
			{"i"},
			{"s2"},
			{"i2"},
			{"t"},
			{"n"},
			{"f32"},
			{"f64"},
			{"b"},
			{"f"},
		},
	},
	{
		Query: `
		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"
		`,
		Expected: []sql.Row{{"s"}, {"s2"}},
	},
	{
		Query: `SHOW INDEXES FROM mytaBLE`,
		Expected: []sql.Row{
			{"mytable", 0, "PRIMARY", 1, "i", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil},
			{"mytable", 0, "mytable_s", 1, "s", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil},
			{"mytable", 1, "mytable_i_s", 1, "i", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil},
			{"mytable", 1, "mytable_i_s", 2, "s", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil},
		},
	},
	{
		Query: `SHOW KEYS FROM mytaBLE`,
		Expected: []sql.Row{
			{"mytable", 0, "PRIMARY", 1, "i", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil},
			{"mytable", 0, "mytable_s", 1, "s", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil},
			{"mytable", 1, "mytable_i_s", 1, "i", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil},
			{"mytable", 1, "mytable_i_s", 2, "s", nil, 0, nil, nil, "", "BTREE", "", "", "YES", nil},
		},
	},
	{
		Query: `SHOW CREATE TABLE mytaBLE`,
		Expected: []sql.Row{
			{"mytable", "CREATE TABLE `mytable` (\n" +
				"  `i` bigint NOT NULL,\n" +
				"  `s` varchar(20) NOT NULL COMMENT 'column s',\n" +
				"  PRIMARY KEY (`i`),\n" +
				"  KEY `mytable_i_s` (`i`,`s`),\n" +
				"  UNIQUE KEY `mytable_s` (`s`)\n" +
				") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"},
		},
	},
	{
		Query: `SHOW CREATE TABLE fk_TBL`,
		Expected: []sql.Row{
			{"fk_tbl", "CREATE TABLE `fk_tbl` (\n" +
				"  `pk` bigint NOT NULL,\n" +
				"  `a` bigint,\n" +
				"  `b` varchar(20),\n" +
				"  PRIMARY KEY (`pk`),\n" +
				"  CONSTRAINT `fk1` FOREIGN KEY (`a`,`b`) REFERENCES `mytable` (`i`,`s`) ON DELETE CASCADE\n" +
				") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"},
		},
	},
	{

		Query: "SELECT table_name, `auto_increment` FROM information_schema.tables " +
			"WHERE TABLE_SCHEMA='mydb' AND TABLE_TYPE='BASE TABLE' ORDER BY 1",
		Expected: []sql.Row{
			{"auto_increment_tbl", 4},
			{"bigtable", nil},
			{"fk_tbl", nil},
			{"floattable", nil},
			{"mytable", nil},
			{"newlinetable", nil},
			{"niltable", nil},
			{"othertable", nil},
			{"people", nil},
			{"tabletest", nil},
		},
	},
}

View Source

var InfoSchemaScripts = []ScriptTest{
	{
		Name: "describe auto_increment table",
		SetUpScript: []string{
			"create table auto (pk int primary key auto_increment)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "describe auto;",
				Expected: []sql.Row{
					{"pk", "int", "NO", "PRI", "", "auto_increment"},
				},
			},
		},
	},
}

View Source

var InsertErrorScripts = []ScriptTest{
	{
		Name:        "create table with non-pk auto_increment column",
		Query:       "create table bad (pk int primary key, c0 int auto_increment);",
		ExpectedErr: parse.ErrInvalidAutoIncCols,
	},
	{
		Name:        "create multiple auto_increment columns",
		Query:       "create table bad (pk1 int auto_increment, pk2 int auto_increment, primary key (pk1,pk2));",
		ExpectedErr: parse.ErrInvalidAutoIncCols,
	},
	{
		Name:        "create auto_increment column with default",
		Query:       "create table bad (pk1 int auto_increment default 10, c0 int);",
		ExpectedErr: parse.ErrInvalidAutoIncCols,
	},
}

View Source

var InsertErrorTests = []GenericErrorQueryTest{
	{
		Name:  "too few values",
		Query: "INSERT INTO mytable (s, i) VALUES ('x');",
	},
	{
		Name:  "too many values one column",
		Query: "INSERT INTO mytable (s) VALUES ('x', 999);",
	},
	{
		Name:  "missing binding",
		Query: "INSERT INTO mytable (s) VALUES (?);",
	},
	{
		Name:  "too many values two columns",
		Query: "INSERT INTO mytable (i, s) VALUES (999, 'x', 'y');",
	},
	{
		Name:  "too few values no columns specified",
		Query: "INSERT INTO mytable VALUES (999);",
	},
	{
		Name:  "too many values no columns specified",
		Query: "INSERT INTO mytable VALUES (999, 'x', 'y');",
	},
	{
		Name:  "non-existent column values",
		Query: "INSERT INTO mytable (i, s, z) VALUES (999, 'x', 999);",
	},
	{
		Name:  "non-existent column set",
		Query: "INSERT INTO mytable SET i = 999, s = 'x', z = 999;",
	},
	{
		Name:  "duplicate column",
		Query: "INSERT INTO mytable (i, s, s) VALUES (999, 'x', 'x');",
	},
	{
		Name:  "duplicate column set",
		Query: "INSERT INTO mytable SET i = 999, s = 'y', s = 'y';",
	},
	{
		Name:  "null given to non-nullable",
		Query: "INSERT INTO mytable (i, s) VALUES (null, 'y');",
	},
	{
		Name:  "incompatible types",
		Query: "INSERT INTO mytable (i, s) select * FROM othertable",
	},
	{
		Name:  "column count mismatch in select",
		Query: "INSERT INTO mytable (i) select * FROM othertable",
	},
	{
		Name:  "column count mismatch in select",
		Query: "INSERT INTO mytable select s FROM othertable",
	},
	{
		Name:  "column count mismatch in join select",
		Query: "INSERT INTO mytable (s,i) SELECT * FROM othertable o JOIN mytable m ON m.i=o.i2",
	},
	{
		Name:  "duplicate key",
		Query: "INSERT INTO mytable (i,s) values (1, 'hello')",
	},
	{
		Name:  "duplicate keys",
		Query: "INSERT INTO mytable SELECT * from mytable",
	},
	{
		Name:  "bad column in on duplicate key update clause",
		Query: "INSERT INTO mytable values (10, 'b') ON DUPLICATE KEY UPDATE notExist = 1",
	},
}

View Source

var InsertQueries = []WriteQueryTest{
	{
		WriteQuery:          "INSERT INTO mytable (s, i) VALUES ('x', 999);",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT i FROM mytable WHERE s = 'x';",
		ExpectedSelect:      []sql.Row{{int64(999)}},
	},
	{
		WriteQuery:          "INSERT INTO niltable (i, f) VALUES (10, 10.0), (12, 12.0);",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(2)}},
		SelectQuery:         "SELECT i,f FROM niltable WHERE f IN (10.0, 12.0) ORDER BY f;",
		ExpectedSelect:      []sql.Row{{int64(10), 10.0}, {int64(12), 12.0}},
	},
	{
		WriteQuery:          "INSERT INTO mytable SET s = 'x', i = 999;",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT i FROM mytable WHERE s = 'x';",
		ExpectedSelect:      []sql.Row{{int64(999)}},
	},
	{
		WriteQuery:          "INSERT INTO mytable VALUES (999, 'x');",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT i FROM mytable WHERE s = 'x';",
		ExpectedSelect:      []sql.Row{{int64(999)}},
	},
	{
		WriteQuery:          "INSERT INTO mytable VALUES (?, ?);",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT i FROM mytable WHERE s = 'x';",
		ExpectedSelect:      []sql.Row{{int64(999)}},
		Bindings: map[string]sql.Expression{
			"v1": expression.NewLiteral(int64(999), sql.Int64),
			"v2": expression.NewLiteral("x", sql.Text),
		},
	},
	{
		WriteQuery:          "INSERT INTO mytable VALUES (:col1, :col2);",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT i FROM mytable WHERE s = 'x';",
		ExpectedSelect:      []sql.Row{{int64(999)}},
		Bindings: map[string]sql.Expression{
			"col1": expression.NewLiteral(int64(999), sql.Int64),
			"col2": expression.NewLiteral("x", sql.Text),
		},
	},
	{
		WriteQuery:          "INSERT INTO mytable SET i = 999, s = 'x';",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT i FROM mytable WHERE s = 'x';",
		ExpectedSelect:      []sql.Row{{int64(999)}},
	},
	{
		WriteQuery: `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'
			);`,
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM typestable WHERE id = 999;",
		ExpectedSelect: []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",
		}},
	},
	{
		WriteQuery: `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'
			;`,
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM typestable WHERE id = 999;",
		ExpectedSelect: []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",
		}},
	},
	{
		WriteQuery: `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, '', ''
			);`,
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM typestable WHERE id = 999;",
		ExpectedSelect: []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)(`""`), "",
		}},
	},
	{
		WriteQuery: `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 = ''
			;`,
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM typestable WHERE id = 999;",
		ExpectedSelect: []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)(`""`), "",
		}},
	},
	{
		WriteQuery:          `INSERT INTO mytable (i,s) VALUES (10, 'NULL')`,
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM mytable WHERE i = 10;",
		ExpectedSelect:      []sql.Row{{int64(10), "NULL"}},
	},
	{
		WriteQuery: `INSERT INTO typestable VALUES (999, null, null, null, null, null, null, null, null,
			null, null, null, null, null, null, null, null);`,
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM typestable WHERE id = 999;",
		ExpectedSelect:      []sql.Row{{int64(999), nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil}},
	},
	{
		WriteQuery: `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;`,
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM typestable WHERE id = 999;",
		ExpectedSelect:      []sql.Row{{int64(999), nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil}},
	},
	{
		WriteQuery:          "INSERT INTO mytable SELECT i+100,s FROM mytable",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(3)}},
		SelectQuery:         "SELECT * FROM mytable ORDER BY i",
		ExpectedSelect: []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"},
		},
	},
	{
		WriteQuery:          "INSERT INTO emptytable SELECT * FROM mytable",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(3)}},
		SelectQuery:         "SELECT * FROM emptytable ORDER BY i",
		ExpectedSelect: []sql.Row{
			{int64(1), "first row"},
			{int64(2), "second row"},
			{int64(3), "third row"},
		},
	},
	{
		WriteQuery:          "INSERT INTO emptytable SELECT * FROM mytable where mytable.i > 2",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM emptytable ORDER BY i",
		ExpectedSelect: []sql.Row{
			{int64(3), "third row"},
		},
	},
	{
		WriteQuery:          "INSERT INTO niltable (i,f) SELECT i+10, NULL FROM mytable where mytable.i > 2",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM niltable where i > 10 ORDER BY i",
		ExpectedSelect: []sql.Row{
			{13, nil, nil, nil},
		},
	},
	{
		WriteQuery:          "INSERT INTO mytable (i,s) SELECT i+10, 'new' FROM mytable",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(3)}},
		SelectQuery:         "SELECT * FROM mytable ORDER BY i",
		ExpectedSelect: []sql.Row{
			{int64(1), "first row"},
			{int64(2), "second row"},
			{int64(3), "third row"},
			{int64(11), "new"},
			{int64(12), "new"},
			{int64(13), "new"},
		},
	},
	{
		WriteQuery:          "INSERT INTO mytable SELECT i2+100, s2 FROM othertable",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(3)}},
		SelectQuery:         "SELECT * FROM mytable ORDER BY i,s",
		ExpectedSelect: []sql.Row{
			{int64(1), "first row"},
			{int64(2), "second row"},
			{int64(3), "third row"},
			{int64(101), "third"},
			{int64(102), "second"},
			{int64(103), "first"},
		},
	},
	{
		WriteQuery:          "INSERT INTO emptytable (s,i) SELECT * FROM othertable",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(3)}},
		SelectQuery:         "SELECT * FROM emptytable ORDER BY i,s",
		ExpectedSelect: []sql.Row{
			{int64(1), "third"},
			{int64(2), "second"},
			{int64(3), "first"},
		},
	},
	{
		WriteQuery:          "INSERT INTO emptytable (s,i) SELECT concat(m.s, o.s2), m.i FROM othertable o JOIN mytable m ON m.i=o.i2",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(3)}},
		SelectQuery:         "SELECT * FROM emptytable ORDER BY i,s",
		ExpectedSelect: []sql.Row{
			{int64(1), "first rowthird"},
			{int64(2), "second rowsecond"},
			{int64(3), "third rowfirst"},
		},
	},
	{
		WriteQuery: `INSERT INTO emptytable (s,i) SELECT s,i from mytable where i = 1 
			union select s,i from mytable where i = 3`,
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(2)}},
		SelectQuery:         "SELECT * FROM emptytable ORDER BY i,s",
		ExpectedSelect: []sql.Row{
			{int64(1), "first row"},
			{int64(3), "third row"},
		},
	},
	{
		WriteQuery: `INSERT INTO emptytable (s,i) SELECT s,i from mytable where i = 1 
			union select s,i from mytable where i = 3 
			union select s,i from mytable where i > 2`,
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(2)}},
		SelectQuery:         "SELECT * FROM emptytable ORDER BY i,s",
		ExpectedSelect: []sql.Row{
			{int64(1), "first row"},
			{int64(3), "third row"},
		},
	},
	{
		WriteQuery: `INSERT INTO emptytable (s,i) 
			SELECT s,i from mytable where i = 1 
			union all select s,i+1 from mytable where i < 2 
			union all select s,i+2 from mytable where i in (1)`,
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(3)}},
		SelectQuery:         "SELECT * FROM emptytable ORDER BY i,s",
		ExpectedSelect: []sql.Row{
			{int64(1), "first row"},
			{int64(2), "first row"},
			{int64(3), "first row"},
		},
	},
	{
		WriteQuery:          "INSERT INTO emptytable (s,i) SELECT distinct s,i from mytable",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(3)}},
		SelectQuery:         "SELECT * FROM emptytable ORDER BY i,s",
		ExpectedSelect: []sql.Row{
			{int64(1), "first row"},
			{int64(2), "second row"},
			{int64(3), "third row"},
		},
	},
	{
		WriteQuery:          "INSERT INTO mytable (i,s) SELECT (i + 10.0) / 10.0 + 10 + i, concat(s, ' new') FROM mytable",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(3)}},
		SelectQuery:         "SELECT * FROM mytable ORDER BY i, s",
		ExpectedSelect: []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"},
		},
	},
	{
		WriteQuery:          "INSERT INTO mytable (i,s) SELECT CHAR_LENGTH(s), concat('numrows: ', count(*)) from mytable group by 1",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(2)}},
		SelectQuery:         "SELECT * FROM mytable ORDER BY i, s",
		ExpectedSelect: []sql.Row{
			{1, "first row"},
			{2, "second row"},
			{3, "third row"},
			{9, "numrows: 2"},
			{10, "numrows: 1"},
		},
	},

	{
		WriteQuery:          "INSERT INTO mytable (i,s) SELECT CHAR_LENGTH(s), concat('numrows: ', count(*)) from mytable group by 1 HAVING CHAR_LENGTH(s)  > 9",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM mytable ORDER BY i, s",
		ExpectedSelect: []sql.Row{
			{1, "first row"},
			{2, "second row"},
			{3, "third row"},
			{10, "numrows: 1"},
		},
	},
	{
		WriteQuery:          "INSERT INTO mytable (i,s) SELECT i * 2, concat(s,s) from mytable order by 1 desc limit 1",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM mytable ORDER BY i, s",
		ExpectedSelect: []sql.Row{
			{1, "first row"},
			{2, "second row"},
			{3, "third row"},
			{6, "third rowthird row"},
		},
	},
	{
		WriteQuery:          "INSERT INTO mytable (i,s) SELECT i + 3, concat(s,s) from mytable order by 1 desc",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(3)}},
		SelectQuery:         "SELECT * FROM mytable ORDER BY i, s",
		ExpectedSelect: []sql.Row{
			{1, "first row"},
			{2, "second row"},
			{3, "third row"},
			{4, "first rowfirst row"},
			{5, "second rowsecond row"},
			{6, "third rowthird row"},
		},
	},
	{
		WriteQuery:          "INSERT INTO mytable (i,s) values (1, 'hello') ON DUPLICATE KEY UPDATE s='hello'",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(2)}},
		SelectQuery:         "SELECT * FROM mytable WHERE i = 1",
		ExpectedSelect:      []sql.Row{{int64(1), "hello"}},
	},
	{
		WriteQuery:          "INSERT INTO mytable (i,s) values (1, 'hello2') ON DUPLICATE KEY UPDATE s='hello3'",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(2)}},
		SelectQuery:         "SELECT * FROM mytable WHERE i = 1",
		ExpectedSelect:      []sql.Row{{int64(1), "hello3"}},
	},
	{
		WriteQuery:          "INSERT INTO mytable (i,s) values (1, 'hello') ON DUPLICATE KEY UPDATE i=10",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(2)}},
		SelectQuery:         "SELECT * FROM mytable WHERE i = 10",
		ExpectedSelect:      []sql.Row{{int64(10), "first row"}},
	},
	{
		WriteQuery:          "INSERT INTO mytable (i,s) values (1, 'hello2') ON DUPLICATE KEY UPDATE s='hello3'",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(2)}},
		SelectQuery:         "SELECT * FROM mytable WHERE i = 1",
		ExpectedSelect:      []sql.Row{{int64(1), "hello3"}},
	},
	{
		WriteQuery:          "INSERT INTO mytable (i,s) values (1, 'hello2'), (2, 'hello3'), (4, 'no conflict') ON DUPLICATE KEY UPDATE s='hello4'",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(5)}},
		SelectQuery:         "SELECT * FROM mytable ORDER BY 1",
		ExpectedSelect: []sql.Row{
			{1, "hello4"},
			{2, "hello4"},
			{3, "third row"},
			{4, "no conflict"},
		},
	},
	{
		WriteQuery:          "INSERT INTO mytable (i,s) values (10, 'hello') ON DUPLICATE KEY UPDATE s='hello'",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM mytable ORDER BY 1",
		ExpectedSelect: []sql.Row{
			{1, "first row"},
			{2, "second row"},
			{3, "third row"},
			{10, "hello"},
		},
	},
	{
		WriteQuery:          "INSERT INTO mytable (i,s) values (1,'hi') ON DUPLICATE KEY UPDATE s=VALUES(s)",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(2)}},
		SelectQuery:         "SELECT * FROM mytable WHERE i = 1",
		ExpectedSelect:      []sql.Row{{int64(1), "hi"}},
	},
	{
		WriteQuery:          "INSERT INTO mytable (s,i) values ('dup',1) ON DUPLICATE KEY UPDATE s=CONCAT(VALUES(s), 'licate')",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(2)}},
		SelectQuery:         "SELECT * FROM mytable WHERE i = 1",
		ExpectedSelect:      []sql.Row{{int64(1), "duplicate"}},
	},
	{
		WriteQuery:          "INSERT INTO mytable (i,s) values (1,'mar'), (2,'par') ON DUPLICATE KEY UPDATE s=CONCAT(VALUES(s), 'tial')",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(4)}},
		SelectQuery:         "SELECT * FROM mytable WHERE i IN (1,2) ORDER BY i",
		ExpectedSelect:      []sql.Row{{int64(1), "martial"}, {int64(2), "partial"}},
	},
	{
		WriteQuery:          "INSERT INTO mytable (i,s) values (1,'maybe') ON DUPLICATE KEY UPDATE i=VALUES(i)+8000, s=VALUES(s)",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(2)}},
		SelectQuery:         "SELECT * FROM mytable WHERE i = 8001",
		ExpectedSelect:      []sql.Row{{int64(8001), "maybe"}},
	},
	{
		WriteQuery:          "INSERT INTO auto_increment_tbl (c0) values (44)",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM auto_increment_tbl ORDER BY pk",
		ExpectedSelect: []sql.Row{
			{1, 11},
			{2, 22},
			{3, 33},
			{4, 44},
		},
	},
	{
		WriteQuery:          "INSERT INTO auto_increment_tbl (c0) values (44),(55)",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(2)}},
		SelectQuery:         "SELECT * FROM auto_increment_tbl ORDER BY pk",
		ExpectedSelect: []sql.Row{
			{1, 11},
			{2, 22},
			{3, 33},
			{4, 44},
			{5, 55},
		},
	},
	{
		WriteQuery:          "INSERT INTO auto_increment_tbl values (NULL, 44)",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM auto_increment_tbl ORDER BY pk",
		ExpectedSelect: []sql.Row{
			{1, 11},
			{2, 22},
			{3, 33},
			{4, 44},
		},
	},
	{
		WriteQuery:          "INSERT INTO auto_increment_tbl values (0, 44)",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM auto_increment_tbl ORDER BY pk",
		ExpectedSelect: []sql.Row{
			{1, 11},
			{2, 22},
			{3, 33},
			{4, 44},
		},
	},
	{
		WriteQuery:          "INSERT INTO auto_increment_tbl values (5, 44)",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM auto_increment_tbl ORDER BY pk",
		ExpectedSelect: []sql.Row{
			{1, 11},
			{2, 22},
			{3, 33},
			{5, 44},
		},
	},
	{
		WriteQuery: "INSERT INTO auto_increment_tbl values " +
			"(NULL, 44), (NULL, 55), (9, 99), (NULL, 110), (NULL, 121)",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(5)}},
		SelectQuery:         "SELECT * FROM auto_increment_tbl ORDER BY pk",
		ExpectedSelect: []sql.Row{
			{1, 11},
			{2, 22},
			{3, 33},
			{4, 44},
			{5, 55},
			{9, 99},
			{10, 110},
			{11, 121},
		},
	},
}

View Source

var InsertScripts = []ScriptTest{
	{
		Name: "insert into sparse auto_increment table",
		SetUpScript: []string{
			"create table auto (pk int primary key auto_increment)",
			"insert into auto values (10), (20), (30)",
			"insert into auto values (NULL)",
			"insert into auto values (40)",
			"insert into auto values (0)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select * from auto order by 1",
				Expected: []sql.Row{
					{10}, {20}, {30}, {31}, {40}, {41},
				},
			},
		},
	},
	{
		Name: "auto increment table handles deletes",
		SetUpScript: []string{
			"create table auto (pk int primary key auto_increment)",
			"insert into auto values (10)",
			"delete from auto where pk = 10",
			"insert into auto values (NULL)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select * from auto order by 1",
				Expected: []sql.Row{
					{11},
				},
			},
		},
	},
	{
		Name: "create auto_increment table with out-of-line primary key def",
		SetUpScript: []string{
			`create table auto (
				pk int auto_increment,
				c0 int,
				primary key(pk)
			);`,
			"insert into auto values (NULL,10), (NULL,20), (NULL,30)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select * from auto order by 1",
				Expected: []sql.Row{
					{1, 10}, {2, 20}, {3, 30},
				},
			},
		},
	},
	{
		Name: "alter auto_increment value",
		SetUpScript: []string{
			`create table auto (
				pk int auto_increment,
				c0 int,
				primary key(pk)
			);`,
			"insert into auto values (NULL,10), (NULL,20), (NULL,30)",
			"alter table auto auto_increment 9;",
			"insert into auto values (NULL,90)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select * from auto order by 1",
				Expected: []sql.Row{
					{1, 10}, {2, 20}, {3, 30}, {9, 90},
				},
			},
		},
	},
	{
		Name: "alter auto_increment value to float",
		SetUpScript: []string{
			`create table auto (
				pk int auto_increment,
				c0 int,
				primary key(pk)
			);`,
			"insert into auto values (NULL,10), (NULL,20), (NULL,30)",
			"alter table auto auto_increment = 19.9;",
			"insert into auto values (NULL,190)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select * from auto order by 1",
				Expected: []sql.Row{
					{1, 10}, {2, 20}, {3, 30}, {19, 190},
				},
			},
		},
	},
	{
		Name: "auto increment on tinyint",
		SetUpScript: []string{
			"create table auto (pk tinyint primary key auto_increment)",
			"insert into auto values (NULL),(10),(0)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select * from auto order by 1",
				Expected: []sql.Row{
					{1}, {10}, {11},
				},
			},
		},
	},
	{
		Name: "auto increment on smallint",
		SetUpScript: []string{
			"create table auto (pk smallint primary key auto_increment)",
			"insert into auto values (NULL),(10),(0)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select * from auto order by 1",
				Expected: []sql.Row{
					{1}, {10}, {11},
				},
			},
		},
	},
	{
		Name: "auto increment on mediumint",
		SetUpScript: []string{
			"create table auto (pk mediumint primary key auto_increment)",
			"insert into auto values (NULL),(10),(0)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select * from auto order by 1",
				Expected: []sql.Row{
					{1}, {10}, {11},
				},
			},
		},
	},
	{
		Name: "auto increment on int",
		SetUpScript: []string{
			"create table auto (pk int primary key auto_increment)",
			"insert into auto values (NULL),(10),(0)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select * from auto order by 1",
				Expected: []sql.Row{
					{1}, {10}, {11},
				},
			},
		},
	},
	{
		Name: "auto increment on bigint",
		SetUpScript: []string{
			"create table auto (pk bigint primary key auto_increment)",
			"insert into auto values (NULL),(10),(0)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select * from auto order by 1",
				Expected: []sql.Row{
					{1}, {10}, {11},
				},
			},
		},
	},
	{
		Name: "auto increment on tinyint unsigned",
		SetUpScript: []string{
			"create table auto (pk tinyint unsigned primary key auto_increment)",
			"insert into auto values (NULL),(10),(0)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select * from auto order by 1",
				Expected: []sql.Row{
					{uint64(1)}, {uint64(10)}, {uint64(11)},
				},
			},
		},
	},
	{
		Name: "auto increment on smallint unsigned",
		SetUpScript: []string{
			"create table auto (pk smallint unsigned primary key auto_increment)",
			"insert into auto values (NULL),(10),(0)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select * from auto order by 1",
				Expected: []sql.Row{
					{uint64(1)}, {uint64(10)}, {uint64(11)},
				},
			},
		},
	},
	{
		Name: "auto increment on mediumint unsigned",
		SetUpScript: []string{
			"create table auto (pk mediumint unsigned primary key auto_increment)",
			"insert into auto values (NULL),(10),(0)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select * from auto order by 1",
				Expected: []sql.Row{
					{uint64(1)}, {uint64(10)}, {uint64(11)},
				},
			},
		},
	},
	{
		Name: "auto increment on int unsigned",
		SetUpScript: []string{
			"create table auto (pk int unsigned primary key auto_increment)",
			"insert into auto values (NULL),(10),(0)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select * from auto order by 1",
				Expected: []sql.Row{
					{uint64(1)}, {uint64(10)}, {uint64(11)},
				},
			},
		},
	},
	{
		Name: "auto increment on bigint unsigned",
		SetUpScript: []string{
			"create table auto (pk bigint unsigned primary key auto_increment)",
			"insert into auto values (NULL),(10),(0)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select * from auto order by 1",
				Expected: []sql.Row{
					{uint64(1)}, {uint64(10)}, {uint64(11)},
				},
			},
		},
	},
	{
		Name: "auto increment on float",
		SetUpScript: []string{
			"create table auto (pk float primary key auto_increment)",
			"insert into auto values (NULL),(10),(0)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select * from auto order by 1",
				Expected: []sql.Row{
					{float64(1)}, {float64(10)}, {float64(11)},
				},
			},
		},
	},
	{
		Name: "auto increment on double",
		SetUpScript: []string{
			"create table auto (pk double primary key auto_increment)",
			"insert into auto values (NULL),(10),(0)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select * from auto order by 1",
				Expected: []sql.Row{
					{float64(1)}, {float64(10)}, {float64(11)},
				},
			},
		},
	},
}

View Source

var KeylessQueries = []QueryTest{
	{
		Query: "SELECT * FROM keyless ORDER BY c0",
		Expected: []sql.Row{
			{0, 0},
			{1, 1},
			{1, 1},
			{2, 2},
		},
	},
	{
		Query: "SELECT * FROM keyless ORDER BY c1 DESC",
		Expected: []sql.Row{
			{2, 2},
			{1, 1},
			{1, 1},
			{0, 0},
		},
	},
	{
		Query: "SELECT * FROM keyless JOIN myTable where c0 = i",
		Expected: []sql.Row{
			{1, 1, 1, "first row"},
			{1, 1, 1, "first row"},
			{2, 2, 2, "second row"},
		},
	},
	{
		Query: "SELECT * FROM myTable JOIN keyless WHERE i = c0 ORDER BY i",
		Expected: []sql.Row{
			{1, "first row", 1, 1},
			{1, "first row", 1, 1},
			{2, "second row", 2, 2},
		},
	},
	{
		Query: "DESCRIBE keyless",
		Expected: []sql.Row{
			{"c0", "bigint", "YES", "", "", ""},
			{"c1", "bigint", "YES", "", "", ""},
		},
	},
	{
		Query: "SHOW COLUMNS FROM keyless",
		Expected: []sql.Row{
			{"c0", "bigint", "YES", "", "", ""},
			{"c1", "bigint", "YES", "", "", ""},
		},
	},
	{
		Query: "SHOW FULL COLUMNS FROM keyless",
		Expected: []sql.Row{
			{"c0", "bigint", nil, "YES", "", "", "", "", ""},
			{"c1", "bigint", nil, "YES", "", "", "", "", ""},
		},
	},
	{
		Query: "SHOW CREATE TABLE keyless",
		Expected: []sql.Row{
			{"keyless", "CREATE TABLE `keyless` (\n  `c0` bigint,\n  `c1` bigint\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"},
		},
	},
}

View Source

var PlanTests = []QueryPlanTest{
	{
		Query: "SELECT t1.i FROM mytable t1 JOIN mytable t2 on t1.i = t2.i + 1 where t1.i = 2 and t2.i = 1",
		ExpectedPlan: "Project(t1.i)\n" +
			" └─ IndexedJoin(t1.i = t2.i + 1)\n" +
			"     ├─ Filter(t2.i = 1)\n" +
			"     │   └─ TableAlias(t2)\n" +
			"     │       └─ IndexedTableAccess(mytable on [mytable.i])\n" +
			"     └─ Filter(t1.i = 2)\n" +
			"         └─ TableAlias(t1)\n" +
			"             └─ IndexedTableAccess(mytable on [mytable.i])\n" +
			"",
	},
	{
		Query: "SELECT i, i2, s2 FROM mytable INNER JOIN othertable ON i = i2",
		ExpectedPlan: "Project(mytable.i, othertable.i2, othertable.s2)\n" +
			" └─ IndexedJoin(mytable.i = othertable.i2)\n" +
			"     ├─ Table(mytable)\n" +
			"     └─ IndexedTableAccess(othertable on [othertable.i2])\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)\n" +
			"     └─ IndexedTableAccess(othertable on [othertable.i2])\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)\n" +
			"     └─ IndexedTableAccess(mytable on [mytable.i])\n" +
			"",
	},
	{
		Query: "SELECT s2, i2, i FROM othertable JOIN mytable ON i = i2",
		ExpectedPlan: "Project(othertable.s2, othertable.i2, mytable.i)\n" +
			" └─ IndexedJoin(mytable.i = othertable.i2)\n" +
			"     ├─ Table(othertable)\n" +
			"     └─ IndexedTableAccess(mytable on [mytable.i])\n" +
			"",
	},
	{
		Query: "SELECT i, i2, s2 FROM mytable INNER JOIN othertable ON i2 = i",
		ExpectedPlan: "Project(mytable.i, othertable.i2, othertable.s2)\n" +
			" └─ IndexedJoin(othertable.i2 = mytable.i)\n" +
			"     ├─ Table(mytable)\n" +
			"     └─ IndexedTableAccess(othertable on [othertable.i2])\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)\n" +
			"     └─ IndexedTableAccess(othertable on [othertable.i2])\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)\n" +
			"     └─ IndexedTableAccess(mytable on [mytable.i])\n" +
			"",
	},
	{
		Query: "SELECT s2, i2, i FROM othertable JOIN mytable ON i2 = i",
		ExpectedPlan: "Project(othertable.s2, othertable.i2, mytable.i)\n" +
			" └─ IndexedJoin(othertable.i2 = mytable.i)\n" +
			"     ├─ Table(othertable)\n" +
			"     └─ IndexedTableAccess(mytable on [mytable.i])\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" +
			" ├─ Filter(mt.i > 2)\n" +
			" │   └─ TableAlias(mt)\n" +
			" │       └─ IndexedTableAccess(mytable on [mytable.i])\n" +
			" └─ TableAlias(ot)\n" +
			"     └─ IndexedTableAccess(othertable on [othertable.i2])\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" +
			"     └─ IndexedTableAccess(mytable on [mytable.i])\n" +
			"",
	},
	{

		Query: "SELECT t1.timestamp FROM reservedWordsTable t1 JOIN reservedWordsTable t2 ON t1.TIMESTAMP = t2.tImEstamp",
		ExpectedPlan: "Project(t1.Timestamp)\n" +
			" └─ IndexedJoin(t1.Timestamp = t2.Timestamp)\n" +
			"     ├─ TableAlias(t1)\n" +
			"     │   └─ Table(reservedWordsTable)\n" +
			"     └─ TableAlias(t2)\n" +
			"         └─ IndexedTableAccess(reservedWordsTable on [reservedWordsTable.Timestamp])\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: "Project(one_pk.pk, two_pk.pk1, two_pk.pk2)\n" +
			" └─ IndexedJoin(one_pk.pk = two_pk.pk1 AND one_pk.pk = two_pk.pk2)\n" +
			"     ├─ Table(one_pk)\n" +
			"     └─ IndexedTableAccess(two_pk on [two_pk.pk1,two_pk.pk2])\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: "Project(opk.pk, tpk.pk1, tpk.pk2)\n" +
			" └─ IndexedJoin(opk.pk = tpk.pk1 AND opk.pk = tpk.pk2)\n" +
			"     ├─ TableAlias(opk)\n" +
			"     │   └─ Table(one_pk)\n" +
			"     └─ TableAlias(tpk)\n" +
			"         └─ IndexedTableAccess(two_pk on [two_pk.pk1,two_pk.pk2])\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" +
			"     └─ IndexedTableAccess(two_pk on [two_pk.pk1,two_pk.pk2])\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" +
			"     └─ IndexedTableAccess(one_pk on [one_pk.pk])\n" +
			"",
	},
	{
		Query: `SELECT pk FROM one_pk
						JOIN two_pk tpk ON one_pk.pk=tpk.pk1 AND one_pk.pk=tpk.pk2
						JOIN two_pk tpk2 ON tpk2.pk1=TPK.pk2 AND TPK2.pk2=tpk.pk1`,
		ExpectedPlan: "Project(one_pk.pk)\n" +
			" └─ IndexedJoin(one_pk.pk = tpk.pk1 AND one_pk.pk = tpk.pk2)\n" +
			"     ├─ Table(one_pk)\n" +
			"     └─ IndexedJoin(tpk2.pk1 = tpk.pk2 AND tpk2.pk2 = tpk.pk1)\n" +
			"         ├─ TableAlias(tpk)\n" +
			"         │   └─ IndexedTableAccess(two_pk on [two_pk.pk1,two_pk.pk2])\n" +
			"         └─ TableAlias(tpk2)\n" +
			"             └─ IndexedTableAccess(two_pk on [two_pk.pk1,two_pk.pk2])\n" +
			"",
	},
	{
		Query: `SELECT pk,tpk.pk1,tpk2.pk1,tpk.pk2,tpk2.pk2 FROM one_pk 
						JOIN two_pk tpk ON pk=tpk.pk1 AND pk-1=tpk.pk2 
						JOIN two_pk tpk2 ON pk-1=TPK2.pk1 AND pk=tpk2.pk2
						ORDER BY 1`,
		ExpectedPlan: "Sort(one_pk.pk ASC)\n" +
			" └─ Project(one_pk.pk, tpk.pk1, tpk2.pk1, tpk.pk2, tpk2.pk2)\n" +
			"     └─ IndexedJoin(one_pk.pk - 1 = tpk2.pk1 AND one_pk.pk = tpk2.pk2)\n" +
			"         ├─ IndexedJoin(one_pk.pk = tpk.pk1 AND one_pk.pk - 1 = tpk.pk2)\n" +
			"         │   ├─ Table(one_pk)\n" +
			"         │   └─ TableAlias(tpk)\n" +
			"         │       └─ IndexedTableAccess(two_pk on [two_pk.pk1,two_pk.pk2])\n" +
			"         └─ TableAlias(tpk2)\n" +
			"             └─ IndexedTableAccess(two_pk on [two_pk.pk1,two_pk.pk2])\n" +
			"",
	},
	{
		Query: `SELECT pk FROM one_pk
						LEFT JOIN two_pk tpk ON one_pk.pk=tpk.pk1 AND one_pk.pk=tpk.pk2
						LEFT JOIN two_pk tpk2 ON tpk2.pk1=TPK.pk2 AND TPK2.pk2=tpk.pk1`,
		ExpectedPlan: "Project(one_pk.pk)\n" +
			" └─ LeftIndexedJoin(one_pk.pk = tpk.pk1 AND one_pk.pk = tpk.pk2)\n" +
			"     ├─ Table(one_pk)\n" +
			"     └─ LeftIndexedJoin(tpk2.pk1 = tpk.pk2 AND tpk2.pk2 = tpk.pk1)\n" +
			"         ├─ TableAlias(tpk)\n" +
			"         │   └─ IndexedTableAccess(two_pk on [two_pk.pk1,two_pk.pk2])\n" +
			"         └─ TableAlias(tpk2)\n" +
			"             └─ IndexedTableAccess(two_pk on [two_pk.pk1,two_pk.pk2])\n" +
			"",
	},

	{
		Query: `SELECT pk FROM one_pk
						LEFT JOIN two_pk tpk ON one_pk.pk=tpk.pk1 AND one_pk.pk=tpk.pk2
						JOIN two_pk tpk2 ON tpk2.pk1=TPK.pk2 AND TPK2.pk2=tpk.pk1`,
		ExpectedPlan: "Project(one_pk.pk)\n" +
			" └─ LeftIndexedJoin(one_pk.pk = tpk.pk1 AND one_pk.pk = tpk.pk2)\n" +
			"     ├─ Table(one_pk)\n" +
			"     └─ IndexedJoin(tpk2.pk1 = tpk.pk2 AND tpk2.pk2 = tpk.pk1)\n" +
			"         ├─ TableAlias(tpk)\n" +
			"         │   └─ IndexedTableAccess(two_pk on [two_pk.pk1,two_pk.pk2])\n" +
			"         └─ TableAlias(tpk2)\n" +
			"             └─ IndexedTableAccess(two_pk on [two_pk.pk1,two_pk.pk2])\n" +
			"",
	},
	{
		Query: `SELECT pk FROM one_pk
						JOIN two_pk tpk ON one_pk.pk=tpk.pk1 AND one_pk.pk=tpk.pk2
						LEFT JOIN two_pk tpk2 ON tpk2.pk1=TPK.pk2 AND TPK2.pk2=tpk.pk1`,
		ExpectedPlan: "Project(one_pk.pk)\n" +
			" └─ IndexedJoin(one_pk.pk = tpk.pk1 AND one_pk.pk = tpk.pk2)\n" +
			"     ├─ Table(one_pk)\n" +
			"     └─ LeftIndexedJoin(tpk2.pk1 = tpk.pk2 AND tpk2.pk2 = tpk.pk1)\n" +
			"         ├─ TableAlias(tpk)\n" +
			"         │   └─ IndexedTableAccess(two_pk on [two_pk.pk1,two_pk.pk2])\n" +
			"         └─ TableAlias(tpk2)\n" +
			"             └─ IndexedTableAccess(two_pk on [two_pk.pk1,two_pk.pk2])\n" +
			"",
	},
	{
		Query: `SELECT pk FROM one_pk 
						RIGHT JOIN two_pk tpk ON one_pk.pk=tpk.pk1 AND one_pk.pk=tpk.pk2
						RIGHT JOIN two_pk tpk2 ON tpk.pk1=TPk2.pk2 AND tpk.pk2=TPK2.pk1`,
		ExpectedPlan: "Project(one_pk.pk)\n" +
			" └─ RightIndexedJoin(tpk.pk1 = tpk2.pk2 AND tpk.pk2 = tpk2.pk1)\n" +
			"     ├─ TableAlias(tpk2)\n" +
			"     │   └─ Table(two_pk)\n" +
			"     └─ RightIndexedJoin(one_pk.pk = tpk.pk1 AND one_pk.pk = tpk.pk2)\n" +
			"         ├─ TableAlias(tpk)\n" +
			"         │   └─ IndexedTableAccess(two_pk on [two_pk.pk1,two_pk.pk2])\n" +
			"         └─ IndexedTableAccess(one_pk on [one_pk.pk])\n" +
			"",
	},
	{
		Query: "SELECT i,pk1,pk2 FROM mytable JOIN two_pk ON i-1=pk1 AND i-2=pk2",
		ExpectedPlan: "Project(mytable.i, two_pk.pk1, two_pk.pk2)\n" +
			" └─ IndexedJoin(mytable.i - 1 = two_pk.pk1 AND mytable.i - 2 = two_pk.pk2)\n" +
			"     ├─ Table(mytable)\n" +
			"     └─ IndexedTableAccess(two_pk on [two_pk.pk1,two_pk.pk2])\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" +
			"     └─ IndexedTableAccess(niltable on [niltable.i])\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" +
			"     └─ IndexedTableAccess(one_pk on [one_pk.pk])\n" +
			"",
	},
	{
		Query: `SELECT pk,nt.i,nt2.i FROM one_pk 
						RIGHT JOIN niltable nt ON pk=nt.i
						RIGHT JOIN niltable nt2 ON pk=nt2.i + 1`,
		ExpectedPlan: "Project(one_pk.pk, nt.i, nt2.i)\n" +
			" └─ RightIndexedJoin(one_pk.pk = nt.i)\n" +
			"     ├─ TableAlias(nt)\n" +
			"     │   └─ Table(niltable)\n" +
			"     └─ RightIndexedJoin(one_pk.pk = nt2.i + 1)\n" +
			"         ├─ TableAlias(nt2)\n" +
			"         │   └─ Table(niltable)\n" +
			"         └─ IndexedTableAccess(one_pk on [one_pk.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" +
			"         └─ IndexedTableAccess(niltable on [niltable.i])\n" +
			"",
	},
	{
		Query: "SELECT pk,i,f FROM one_pk LEFT JOIN niltable ON pk=i WHERE i2 > 1",
		ExpectedPlan: "Project(one_pk.pk, niltable.i, niltable.f)\n" +
			" └─ Filter(niltable.i2 > 1)\n" +
			"     └─ LeftIndexedJoin(one_pk.pk = niltable.i)\n" +
			"         ├─ Table(one_pk)\n" +
			"         └─ IndexedTableAccess(niltable on [niltable.i])\n" +
			"",
	},
	{
		Query: "SELECT pk,i,f FROM one_pk LEFT JOIN niltable ON pk=i WHERE i > 1",
		ExpectedPlan: "Project(one_pk.pk, niltable.i, niltable.f)\n" +
			" └─ Filter(niltable.i > 1)\n" +
			"     └─ LeftIndexedJoin(one_pk.pk = niltable.i)\n" +
			"         ├─ Table(one_pk)\n" +
			"         └─ IndexedTableAccess(niltable on [niltable.i])\n" +
			"",
	},
	{
		Query: "SELECT pk,i,f FROM one_pk LEFT JOIN niltable ON pk=i WHERE c1 > 10",
		ExpectedPlan: "Project(one_pk.pk, niltable.i, niltable.f)\n" +
			" └─ LeftIndexedJoin(one_pk.pk = niltable.i)\n" +
			"     ├─ Filter(one_pk.c1 > 10)\n" +
			"     │   └─ Table(one_pk)\n" +
			"     └─ IndexedTableAccess(niltable on [niltable.i])\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" +
			" └─ RightIndexedJoin(one_pk.pk = niltable.i)\n" +
			"     ├─ Filter(NOT(niltable.f IS NULL))\n" +
			"     │   └─ Table(niltable)\n" +
			"     └─ IndexedTableAccess(one_pk on [one_pk.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" +
			" └─ LeftIndexedJoin(one_pk.pk = niltable.i)\n" +
			"     ├─ Filter(one_pk.pk > 1)\n" +
			"     │   └─ IndexedTableAccess(one_pk on [one_pk.pk])\n" +
			"     └─ IndexedTableAccess(niltable on [niltable.i])\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" +
			"         └─ IndexedTableAccess(one_pk on [one_pk.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)\n" +
			"     └─ IndexedTableAccess(one_pk on [one_pk.pk])\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" +
			" └─ Project(a.pk1, a.pk2, b.pk1, b.pk2)\n" +
			"     └─ IndexedJoin(a.pk1 = b.pk1 AND a.pk2 = b.pk2)\n" +
			"         ├─ TableAlias(a)\n" +
			"         │   └─ Table(two_pk)\n" +
			"         └─ TableAlias(b)\n" +
			"             └─ IndexedTableAccess(two_pk on [two_pk.pk1,two_pk.pk2])\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" +
			" └─ Project(a.pk1, a.pk2, b.pk1, b.pk2)\n" +
			"     └─ IndexedJoin(a.pk1 = b.pk2 AND a.pk2 = b.pk1)\n" +
			"         ├─ TableAlias(a)\n" +
			"         │   └─ Table(two_pk)\n" +
			"         └─ TableAlias(b)\n" +
			"             └─ IndexedTableAccess(two_pk on [two_pk.pk1,two_pk.pk2])\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" +
			" └─ Project(a.pk1, a.pk2, b.pk1, b.pk2)\n" +
			"     └─ IndexedJoin(b.pk1 = a.pk1 AND a.pk2 = b.pk2)\n" +
			"         ├─ TableAlias(a)\n" +
			"         │   └─ Table(two_pk)\n" +
			"         └─ TableAlias(b)\n" +
			"             └─ IndexedTableAccess(two_pk on [two_pk.pk1,two_pk.pk2])\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" +
			" └─ Project(a.pk1, a.pk2, b.pk1, b.pk2)\n" +
			"     └─ IndexedJoin(a.pk1 + 1 = b.pk1 AND a.pk2 + 1 = b.pk2)\n" +
			"         ├─ TableAlias(a)\n" +
			"         │   └─ Table(two_pk)\n" +
			"         └─ TableAlias(b)\n" +
			"             └─ IndexedTableAccess(two_pk on [two_pk.pk1,two_pk.pk2])\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" +
			" └─ Project(a.pk1, a.pk2, b.pk1, b.pk2)\n" +
			"     └─ Filter(a.pk1 = b.pk1 AND a.pk2 = b.pk2)\n" +
			"         └─ CrossJoin\n" +
			"             ├─ TableAlias(a)\n" +
			"             │   └─ Table(two_pk)\n" +
			"             └─ TableAlias(b)\n" +
			"                 └─ Table(two_pk)\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" +
			" └─ Project(a.pk1, a.pk2, b.pk1, b.pk2)\n" +
			"     └─ Filter(a.pk1 = b.pk2 AND a.pk2 = b.pk1)\n" +
			"         └─ CrossJoin\n" +
			"             ├─ TableAlias(a)\n" +
			"             │   └─ Table(two_pk)\n" +
			"             └─ TableAlias(b)\n" +
			"                 └─ Table(two_pk)\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)\n" +
			"         └─ IndexedTableAccess(one_pk on [one_pk.pk])\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)\n" +
			"         └─ TableAlias(opk)\n" +
			"             └─ IndexedTableAccess(one_pk on [one_pk.pk])\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)\n" +
			"         └─ TableAlias(opk)\n" +
			"             └─ IndexedTableAccess(one_pk on [one_pk.pk])\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)\n" +
			"             └─ TableAlias(tpk)\n" +
			"                 └─ Table(two_pk)\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)\n" +
			"             └─ Table(two_pk)\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" +
			"         └─ IndexedTableAccess(niltable on [niltable.i])\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" +
			"             └─ IndexedTableAccess(niltable on [niltable.i])\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" +
			"     └─ LeftIndexedJoin(one_pk.pk = niltable.i)\n" +
			"         ├─ Filter(one_pk.pk > 1)\n" +
			"         │   └─ IndexedTableAccess(one_pk on [one_pk.pk])\n" +
			"         └─ IndexedTableAccess(niltable on [niltable.i])\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" +
			"         └─ IndexedTableAccess(one_pk on [one_pk.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" +
			"     └─ RightIndexedJoin(one_pk.pk = niltable.i)\n" +
			"         ├─ Filter(NOT(niltable.f IS NULL))\n" +
			"         │   └─ Table(niltable)\n" +
			"         └─ IndexedTableAccess(one_pk on [one_pk.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" +
			"             └─ IndexedTableAccess(one_pk on [one_pk.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" +
			" └─ Project(one_pk.pk, two_pk.pk1, two_pk.pk2)\n" +
			"     └─ IndexedJoin(one_pk.pk = two_pk.pk1 AND one_pk.pk = two_pk.pk2)\n" +
			"         ├─ Table(one_pk)\n" +
			"         └─ IndexedTableAccess(two_pk on [two_pk.pk1,two_pk.pk2])\n" +
			"",
	},
	{
		Query: "SELECT pk,pk1,pk2 FROM one_pk JOIN two_pk ON pk1-pk>0 AND pk2<1",
		ExpectedPlan: "Project(one_pk.pk, two_pk.pk1, two_pk.pk2)\n" +
			" └─ InnerJoin(two_pk.pk1 - one_pk.pk > 0)\n" +
			"     ├─ Table(one_pk)\n" +
			"     └─ Filter(two_pk.pk2 < 1)\n" +
			"         └─ Table(two_pk)\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" +
			" └─ Project(one_pk.pk, two_pk.pk1, two_pk.pk2)\n" +
			"     └─ CrossJoin\n" +
			"         ├─ Table(one_pk)\n" +
			"         └─ Table(two_pk)\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" +
			"         └─ IndexedTableAccess(two_pk on [two_pk.pk1,two_pk.pk2])\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" +
			"         └─ IndexedTableAccess(one_pk on [one_pk.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" +
			" └─ Project(opk.pk, tpk.pk1, tpk.pk2)\n" +
			"     └─ IndexedJoin(opk.pk = tpk.pk1 AND opk.pk = tpk.pk2)\n" +
			"         ├─ TableAlias(opk)\n" +
			"         │   └─ Table(one_pk)\n" +
			"         └─ TableAlias(tpk)\n" +
			"             └─ IndexedTableAccess(two_pk on [two_pk.pk1,two_pk.pk2])\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" +
			" └─ Project(opk.pk, tpk.pk1, tpk.pk2)\n" +
			"     └─ IndexedJoin(opk.pk = tpk.pk1 AND opk.pk = tpk.pk2)\n" +
			"         ├─ TableAlias(opk)\n" +
			"         │   └─ Table(one_pk)\n" +
			"         └─ TableAlias(tpk)\n" +
			"             └─ IndexedTableAccess(two_pk on [two_pk.pk1,two_pk.pk2])\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)\n" +
			"             └─ Table(two_pk)\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)\n" +
			"         └─ Table(two_pk)\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" +
			"     ├─ Filter(one_pk.c1 = 10)\n" +
			"     │   └─ Table(one_pk)\n" +
			"     └─ Table(two_pk)\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" +
			" └─ Project(t1.pk, t2.pk2)\n" +
			"     └─ CrossJoin\n" +
			"         ├─ Filter(t1.pk = 1)\n" +
			"         │   └─ TableAlias(t1)\n" +
			"         │       └─ IndexedTableAccess(one_pk on [one_pk.pk])\n" +
			"         └─ Filter(t2.pk2 = 1)\n" +
			"             └─ TableAlias(t2)\n" +
			"                 └─ Table(two_pk)\n" +
			"",
	},
	{

		Query: "SELECT pk,pk1,pk2 FROM one_pk t1, two_pk t2 WHERE pk=1 AND pk2=1 AND pk1=1 ORDER BY 1,2",
		ExpectedPlan: "Sort(t1.pk ASC, t2.pk1 ASC)\n" +
			" └─ Project(t1.pk, t2.pk1, t2.pk2)\n" +
			"     └─ CrossJoin\n" +
			"         ├─ Filter(t1.pk = 1)\n" +
			"         │   └─ TableAlias(t1)\n" +
			"         │       └─ IndexedTableAccess(one_pk on [one_pk.pk])\n" +
			"         └─ Filter(t2.pk2 = 1 AND t2.pk1 = 1)\n" +
			"             └─ TableAlias(t2)\n" +
			"                 └─ Table(two_pk)\n",
	},
	{
		Query: `SELECT i FROM mytable mt
		WHERE (SELECT i FROM mytable where i = mt.i and i > 2) IS NOT NULL
		AND (SELECT i2 FROM othertable where i2 = i) IS NOT NULL`,
		ExpectedPlan: "Project(mt.i)\n" +
			" └─ Filter(NOT((Project(mytable.i)\n" +
			"     └─ Filter(mytable.i = mt.i AND mytable.i > 2)\n" +
			"         └─ IndexedTableAccess(mytable on [mytable.i])\n" +
			"    ) IS NULL) AND NOT((Project(othertable.i2)\n" +
			"     └─ Filter(othertable.i2 = mt.i)\n" +
			"         └─ IndexedTableAccess(othertable on [othertable.i2])\n" +
			"    ) IS NULL))\n" +
			"     └─ TableAlias(mt)\n" +
			"         └─ Table(mytable)\n" +
			"",
	},
	{
		Query: `SELECT i FROM mytable mt
		WHERE (SELECT i FROM mytable where i = mt.i) IS NOT NULL
		AND (SELECT i2 FROM othertable where i2 = i and i > 2) IS NOT NULL`,
		ExpectedPlan: "Project(mt.i)\n" +
			" └─ Filter(NOT((Project(mytable.i)\n" +
			"     └─ Filter(mytable.i = mt.i)\n" +
			"         └─ IndexedTableAccess(mytable on [mytable.i])\n" +
			"    ) IS NULL) AND NOT((Project(othertable.i2)\n" +
			"     └─ Filter(othertable.i2 = mt.i AND mt.i > 2)\n" +
			"         └─ IndexedTableAccess(othertable on [othertable.i2])\n" +
			"    ) IS NULL))\n" +
			"     └─ TableAlias(mt)\n" +
			"         └─ Table(mytable)\n" +
			"",
	},
	{
		Query: "SELECT pk,pk2, (SELECT pk from one_pk where pk = 1 limit 1) 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" +
			" └─ Project(t1.pk, t2.pk2, (Limit(1)\n" +
			"     └─ Project(one_pk.pk)\n" +
			"         └─ Filter(one_pk.pk = 1)\n" +
			"             └─ IndexedTableAccess(one_pk on [one_pk.pk])\n" +
			"    ))\n" +
			"     └─ CrossJoin\n" +
			"         ├─ Filter(t1.pk = 1)\n" +
			"         │   └─ TableAlias(t1)\n" +
			"         │       └─ IndexedTableAccess(one_pk on [one_pk.pk])\n" +
			"         └─ Filter(t2.pk2 = 1)\n" +
			"             └─ TableAlias(t2)\n" +
			"                 └─ Table(two_pk)\n" +
			"",
	},
	{
		Query: "DELETE FROM two_pk WHERE c1 > 1",
		ExpectedPlan: "Delete\n" +
			" └─ Filter(two_pk.c1 > 1)\n" +
			"     └─ Table(two_pk)\n" +
			"",
	},
	{
		Query: "DELETE FROM two_pk WHERE pk1 = 1 AND pk2 = 2",
		ExpectedPlan: "Delete\n" +
			" └─ Filter(two_pk.pk1 = 1 AND two_pk.pk2 = 2)\n" +
			"     └─ IndexedTableAccess(two_pk on [two_pk.pk1,two_pk.pk2])\n",
	},
	{
		Query: "UPDATE two_pk SET c1 = 1 WHERE c1 > 1",
		ExpectedPlan: "Update\n" +
			" └─ UpdateSource(SET two_pk.c1 = 1)\n" +
			"     └─ Filter(two_pk.c1 > 1)\n" +
			"         └─ Table(two_pk)\n" +
			"",
	},
	{
		Query: "UPDATE two_pk SET c1 = 1 WHERE pk1 = 1 AND pk2 = 2",
		ExpectedPlan: "Update\n" +
			" └─ UpdateSource(SET two_pk.c1 = 1)\n" +
			"     └─ Filter(two_pk.pk1 = 1 AND two_pk.pk2 = 2)\n" +
			"         └─ IndexedTableAccess(two_pk on [two_pk.pk1,two_pk.pk2])\n",
	},
}

QueryPlanTest is a test of generating the right query plans for different queries in the presence of indexes and other features. These tests are fragile because they rely on string representations of query plans, but they're much easier to construct this way.

View Source

var QueryTests = []QueryTest{}/* 542 elements not displayed */

View Source

var ReplaceErrorTests = []GenericErrorQueryTest{
	{
		Name:  "too few values",
		Query: "REPLACE INTO mytable (s, i) VALUES ('x');",
	},
	{
		Name:  "too many values one column",
		Query: "REPLACE INTO mytable (s) VALUES ('x', 999);",
	},
	{
		Name:  "too many values two columns",
		Query: "REPLACE INTO mytable (i, s) VALUES (999, 'x', 'y');",
	},
	{
		Name:  "too few values no columns specified",
		Query: "REPLACE INTO mytable VALUES (999);",
	},
	{
		Name:  "too many values no columns specified",
		Query: "REPLACE INTO mytable VALUES (999, 'x', 'y');",
	},
	{
		Name:  "non-existent column values",
		Query: "REPLACE INTO mytable (i, s, z) VALUES (999, 'x', 999);",
	},
	{
		Name:  "non-existent column set",
		Query: "REPLACE INTO mytable SET i = 999, s = 'x', z = 999;",
	},
	{
		Name:  "duplicate column values",
		Query: "REPLACE INTO mytable (i, s, s) VALUES (999, 'x', 'x');",
	},
	{
		Name:  "duplicate column set",
		Query: "REPLACE INTO mytable SET i = 999, s = 'y', s = 'y';",
	},
	{
		Name:  "null given to non-nullable values",
		Query: "INSERT INTO mytable (i, s) VALUES (null, 'y');",
	},
	{
		Name:  "null given to non-nullable set",
		Query: "INSERT INTO mytable SET i = null, s = 'y';",
	},
}

View Source

var ReplaceQueries = []WriteQueryTest{
	{
		WriteQuery:          "REPLACE INTO mytable VALUES (1, 'first row');",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(2)}},
		SelectQuery:         "SELECT s FROM mytable WHERE i = 1;",
		ExpectedSelect:      []sql.Row{{"first row"}},
	},
	{
		WriteQuery:          "REPLACE INTO mytable SET i = 1, s = 'first row';",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(2)}},
		SelectQuery:         "SELECT s FROM mytable WHERE i = 1;",
		ExpectedSelect:      []sql.Row{{"first row"}},
	},
	{
		WriteQuery:          "REPLACE INTO mytable VALUES (1, 'new row same i');",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(2)}},
		SelectQuery:         "SELECT s FROM mytable WHERE i = 1;",
		ExpectedSelect:      []sql.Row{{"new row same i"}},
	},
	{
		WriteQuery:          "REPLACE INTO mytable SET i = 1, s = 'new row same i';",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(2)}},
		SelectQuery:         "SELECT s FROM mytable WHERE i = 1;",
		ExpectedSelect:      []sql.Row{{"new row same i"}},
	},
	{
		WriteQuery:          "REPLACE INTO mytable (s, i) VALUES ('x', 999);",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT i FROM mytable WHERE s = 'x';",
		ExpectedSelect:      []sql.Row{{int64(999)}},
	},
	{
		WriteQuery:          "REPLACE INTO mytable SET s = 'x', i = 999;",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT i FROM mytable WHERE s = 'x';",
		ExpectedSelect:      []sql.Row{{int64(999)}},
	},
	{
		WriteQuery:          "REPLACE INTO mytable VALUES (999, 'x');",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT i FROM mytable WHERE s = 'x';",
		ExpectedSelect:      []sql.Row{{int64(999)}},
	},
	{
		WriteQuery:          "REPLACE INTO mytable SET i = 999, s = 'x';",
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT i FROM mytable WHERE s = 'x';",
		ExpectedSelect:      []sql.Row{{int64(999)}},
	},
	{
		WriteQuery: `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'
			);`,
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM typestable WHERE id = 999;",
		ExpectedSelect: []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",
		}},
	},
	{
		WriteQuery: `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'
			;`,
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM typestable WHERE id = 999;",
		ExpectedSelect: []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",
		}},
	},
	{
		WriteQuery: `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, '', ''
			);`,
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM typestable WHERE id = 999;",
		ExpectedSelect: []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)(`""`), "",
		}},
	},
	{
		WriteQuery: `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 = ''
			;`,
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM typestable WHERE id = 999;",
		ExpectedSelect: []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)(`""`), "",
		}},
	},
	{
		WriteQuery: `REPLACE INTO typestable VALUES (999, null, null, null, null, null, null, null, null,
			null, null, null, null, null, null, null, null);`,
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM typestable WHERE id = 999;",
		ExpectedSelect:      []sql.Row{{int64(999), nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil}},
	},
	{
		WriteQuery: `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;`,
		ExpectedWriteResult: []sql.Row{{sql.NewOkResult(1)}},
		SelectQuery:         "SELECT * FROM typestable WHERE id = 999;",
		ExpectedSelect:      []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 ScriptTests = []ScriptTest{
	{
		Name: "delete with in clause",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"insert into a values (1), (3), (5)",
			"delete from a where x in (1, 3)",
		},
		Query: "select x from a order by 1",
		Expected: []sql.Row{
			{5},
		},
	},
	{
		Name: "sqllogictest evidence/slt_lang_aggfunc.test",
		SetUpScript: []string{
			"CREATE TABLE t1( x INTEGER, y VARCHAR(8) )",
			"INSERT INTO t1 VALUES(1,'true')",
			"INSERT INTO t1 VALUES(0,'false')",
			"INSERT INTO t1 VALUES(NULL,'NULL')",
		},
		Query: "SELECT count(DISTINCT x) FROM t1",
		Expected: []sql.Row{
			{2},
		},
	},
	{
		Name: "sqllogictest index/commute/10/slt_good_1.test",
		SetUpScript: []string{
			"CREATE TABLE tab0(pk INTEGER PRIMARY KEY, col0 INTEGER, col1 FLOAT, col2 TEXT, col3 INTEGER, col4 FLOAT, col5 TEXT)",
			"INSERT INTO tab0 VALUES(0,42,58.92,'fnbtk',54,68.41,'xmttf')",
			"INSERT INTO tab0 VALUES(1,31,46.55,'sksjf',46,53.20,'wiuva')",
			"INSERT INTO tab0 VALUES(2,30,31.11,'oldqn',41,5.26,'ulaay')",
			"INSERT INTO tab0 VALUES(3,77,44.90,'pmsir',70,84.14,'vcmyo')",
			"INSERT INTO tab0 VALUES(4,23,95.26,'qcwxh',32,48.53,'rvtbr')",
			"INSERT INTO tab0 VALUES(5,43,6.75,'snvwg',3,14.38,'gnfxz')",
			"INSERT INTO tab0 VALUES(6,47,98.26,'bzzva',60,15.2,'imzeq')",
			"INSERT INTO tab0 VALUES(7,98,40.9,'lsrpi',78,66.30,'ephwy')",
			"INSERT INTO tab0 VALUES(8,19,15.16,'ycvjz',55,38.70,'dnkkz')",
			"INSERT INTO tab0 VALUES(9,7,84.4,'ptovf',17,2.46,'hrxsf')",
			"CREATE TABLE tab1(pk INTEGER PRIMARY KEY, col0 INTEGER, col1 FLOAT, col2 TEXT, col3 INTEGER, col4 FLOAT, col5 TEXT)",
			"CREATE INDEX idx_tab1_0 on tab1 (col0)",
			"CREATE INDEX idx_tab1_1 on tab1 (col1)",
			"CREATE INDEX idx_tab1_3 on tab1 (col3)",
			"CREATE INDEX idx_tab1_4 on tab1 (col4)",
			"INSERT INTO tab1 SELECT * FROM tab0",
			"CREATE TABLE tab2(pk INTEGER PRIMARY KEY, col0 INTEGER, col1 FLOAT, col2 TEXT, col3 INTEGER, col4 FLOAT, col5 TEXT)",
			"CREATE UNIQUE INDEX idx_tab2_1 ON tab2 (col4 DESC,col3)",
			"CREATE UNIQUE INDEX idx_tab2_2 ON tab2 (col3 DESC,col0)",
			"CREATE UNIQUE INDEX idx_tab2_3 ON tab2 (col3 DESC,col1)",
			"INSERT INTO tab2 SELECT * FROM tab0",
			"CREATE TABLE tab3(pk INTEGER PRIMARY KEY, col0 INTEGER, col1 FLOAT, col2 TEXT, col3 INTEGER, col4 FLOAT, col5 TEXT)",
			"CREATE INDEX idx_tab3_0 ON tab3 (col3 DESC)",
			"INSERT INTO tab3 SELECT * FROM tab0",
			"CREATE TABLE tab4(pk INTEGER PRIMARY KEY, col0 INTEGER, col1 FLOAT, col2 TEXT, col3 INTEGER, col4 FLOAT, col5 TEXT)",
			"CREATE INDEX idx_tab4_0 ON tab4 (col0 DESC)",
			"CREATE UNIQUE INDEX idx_tab4_2 ON tab4 (col4 DESC,col3)",
			"CREATE INDEX idx_tab4_3 ON tab4 (col3 DESC)",
			"INSERT INTO tab4 SELECT * FROM tab0",
		},
		Query: "SELECT pk FROM tab2 WHERE 78 < col0 AND 19 < col3",
		Expected: []sql.Row{
			{7},
		},
	},
	{
		Name: "3 tables, linear join",
		SetUpScript: []string{
			"create table a (xa int primary key, ya int, za int)",
			"create table b (xb int primary key, yb int, zb int)",
			"create table c (xc int primary key, yc int, zc int)",
			"insert into a values (1,2,3)",
			"insert into b values (1,2,3)",
			"insert into c values (1,2,3)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "select ya from a join b on ya - 1= xb join c on xc = zb - 2",
				Expected: []sql.Row{{2}},
			},
		},
	},
	{
		Name: "3 tables, v join",
		SetUpScript: []string{
			"create table a (xa int primary key, ya int, za int)",
			"create table b (xb int primary key, yb int, zb int)",
			"create table c (xc int primary key, yc int, zc int)",
			"insert into a values (1,2,3)",
			"insert into b values (1,2,3)",
			"insert into c values (1,2,3)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "select za from a join b on ya - 1 = xb join c on xa = xc",
				Expected: []sql.Row{{3}},
			},
		},
	},
	{
		Name: "3 tables, linear join, indexes on A,C",
		SetUpScript: []string{
			"create table a (xa int primary key, ya int, za int)",
			"create table b (xb int primary key, yb int, zb int)",
			"create table c (xc int primary key, yc int, zc int)",
			"insert into a values (1,2,3)",
			"insert into b values (1,2,3)",
			"insert into c values (1,2,3)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "select xa from a join b on xa = yb - 1 join c on yb - 1 = xc",
				Expected: []sql.Row{{1}},
			},
		},
	},
	{
		Name: "4 tables, linear join",
		SetUpScript: []string{
			"create table a (xa int primary key, ya int, za int)",
			"create table b (xb int primary key, yb int, zb int)",
			"create table c (xc int primary key, yc int, zc int)",
			"create table d (xd int primary key, yd int, zd int)",
			"insert into a values (1,2,3)",
			"insert into b values (1,2,3)",
			"insert into c values (1,2,3)",
			"insert into d values (1,2,3)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "select xa from a join b on ya - 1 = xb join c on xb = xc join d on xc = xd",
				Expected: []sql.Row{{1}},
			},
		},
	},
	{
		Name: "4 tables, linear join, index on D",
		SetUpScript: []string{
			"create table a (xa int primary key, ya int, za int)",
			"create table b (xb int primary key, yb int, zb int)",
			"create table c (xc int primary key, yc int, zc int)",
			"create table d (xd int primary key, yd int, zd int)",
			"insert into a values (1,2,3)",
			"insert into b values (1,2,3)",
			"insert into c values (1,2,3)",
			"insert into d values (1,2,3)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "select xa from a join b on ya = yb join c on yb = yc join d on yc - 1 = xd",
				Expected: []sql.Row{{1}},
			},
		},
	},
	{
		Name: "4 tables, left join, indexes on all tables",
		SetUpScript: []string{
			"create table a (xa int primary key, ya int, za int)",
			"create table b (xb int primary key, yb int, zb int)",
			"create table c (xc int primary key, yc int, zc int)",
			"create table d (xd int primary key, yd int, zd int)",
			"insert into a values (1,2,3)",
			"insert into b values (1,2,3)",
			"insert into c values (1,2,3)",
			"insert into d values (1,2,3)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "select xa from a left join b on ya = yb left join c on yb = yc left join d on yc - 1 = xd",
				Expected: []sql.Row{{1}},
			},
		},
	},
	{
		Name: "4 tables, linear join, index on B, D",
		SetUpScript: []string{
			"create table a (xa int primary key, ya int, za int)",
			"create table b (xb int primary key, yb int, zb int)",
			"create table c (xc int primary key, yc int, zc int)",
			"create table d (xd int primary key, yd int, zd int)",
			"insert into a values (1,2,3)",
			"insert into b values (1,2,3)",
			"insert into c values (1,2,3)",
			"insert into d values (1,2,3)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "select xa from a join b on ya - 1 = xb join c on yc = za - 1 join d on yc - 1 = xd",
				Expected: []sql.Row{{1}},
			},
		},
	},
	{
		Name: "4 tables, all joined to A",
		SetUpScript: []string{
			"create table a (xa int primary key, ya int, za int)",
			"create table b (xb int primary key, yb int, zb int)",
			"create table c (xc int primary key, yc int, zc int)",
			"create table d (xd int primary key, yd int, zd int)",
			"insert into a values (1,2,3)",
			"insert into b values (1,2,3)",
			"insert into c values (1,2,3)",
			"insert into d values (1,2,3)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "select xa from a join b on xa = xb join c on ya - 1 = xc join d on za - 2 = xd",
				Expected: []sql.Row{{1}},
			},
		},
	},

	{
		Name: "4 tables, all joined to D",
		SetUpScript: []string{
			"create table a (xa int primary key, ya int, za int)",
			"create table b (xb int primary key, yb int, zb int)",
			"create table c (xc int primary key, yc int, zc int)",
			"create table d (xd int primary key, yd int, zd int)",
			"insert into a values (1,2,3)",
			"insert into b values (1,2,3)",
			"insert into c values (1,2,3)",
			"insert into d values (1,2,3)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "select xa from d join a on yd - 1 = xa join c on zd - 2 = xc join b on xb = zd - 2",
				Expected: []sql.Row{{1}},
			},
		},
	},
	{
		Name: "5 tables, complex join conditions",
		SetUpScript: []string{
			"create table a (xa int primary key, ya int, za int)",
			"create table b (xb int primary key, yb int, zb int)",
			"create table c (xc int primary key, yc int, zc int)",
			"create table d (xd int primary key, yd int, zd int)",
			"create table e (xe int, ye int, ze int, primary key(xe, ye))",
			"insert into a values (1,2,3)",
			"insert into b values (1,2,3)",
			"insert into c values (1,2,3)",
			"insert into d values (1,2,3)",
			"insert into e values (1,2,3)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: `select xa from a 
									join b on ya - 1 = xb 
									join c on xc = za - 2 
									join d on xd = yb - 1 
									join e on xe = zb - 2 and ye = yc`,
				Expected: []sql.Row{{1}},
			},
		},
	},
}

Unlike other engine tests, ScriptTests must be self-contained. No other tables are created outside the definition of the tests.

View Source

var TriggerErrorTests = []ScriptTest{
	{
		Name: "table doesn't exist",
		SetUpScript: []string{
			"create table x (a int primary key, b int, c int)",
		},
		Query:       "create trigger not_found before insert on y for each row set new.a = new.a + 1",
		ExpectedErr: sql.ErrTableNotFound,
	},
	{
		Name: "trigger errors on execution",
		SetUpScript: []string{
			"create table x (a int primary key, b int)",
			"create table y (c int primary key not null)",
			"create trigger trigger_has_error before insert on x for each row insert into y values (null)",
		},
		Query:       "insert into x values (1,2)",
		ExpectedErr: plan.ErrInsertIntoNonNullableProvidedNull,
	},
	{
		Name: "self update on insert",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create trigger a1 before insert on a for each row insert into a values (new.x * 2)",
		},
		Query:       "insert into a values (1), (2), (3)",
		ExpectedErr: sql.ErrTriggerTableInUse,
	},
	{
		Name: "self update on delete",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create trigger a1 before delete on a for each row delete from a",
		},
		Query:       "delete from a",
		ExpectedErr: sql.ErrTriggerTableInUse,
	},
	{
		Name: "self update on update",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create trigger a1 before update on a for each row update a set x = 1",
		},
		Query:       "update a set x = 2",
		ExpectedErr: sql.ErrTriggerTableInUse,
	},
	{
		Name: "circular dependency",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"create trigger a1 before insert on a for each row insert into b values (new.x * 2)",
			"create trigger b1 before insert on b for each row insert into a values (new.y * 7)",
		},
		Query:       "insert into a values (1), (2), (3)",
		ExpectedErr: sql.ErrTriggerTableInUse,
	},
	{
		Name: "circular dependency, nested two deep",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"create table c (z int primary key)",
			"create trigger a1 before insert on a for each row insert into b values (new.x * 2)",
			"create trigger b1 before insert on b for each row insert into c values (new.y * 5)",
			"create trigger c1 before insert on c for each row insert into a values (new.z * 7)",
		},
		Query:       "insert into a values (1), (2), (3)",
		ExpectedErr: sql.ErrTriggerTableInUse,
	},
	{
		Name: "reference to old on insert",
		SetUpScript: []string{
			"create table x (a int primary key, b int, c int)",
		},
		Query:       "create trigger old_on_insert before insert on x for each row set new.c = old.a + 1",
		ExpectedErr: sql.ErrInvalidUseOfOldNew,
	},
	{
		Name: "reference to new on delete",
		SetUpScript: []string{
			"create table x (a int primary key, b int, c int)",
		},
		Query:       "create trigger new_on_delete before delete on x for each row set new.c = old.a + 1",
		ExpectedErr: sql.ErrInvalidUseOfOldNew,
	},
	{
		Name: "set old row on update",
		SetUpScript: []string{
			"create table x (a int primary key, b int, c int)",
		},
		Query:       "create trigger update_old before update on x for each row set old.c = new.a + 1",
		ExpectedErr: sql.ErrInvalidUpdateOfOldRow,
	},
	{
		Name: "set old row on update, begin block",
		SetUpScript: []string{
			"create table x (a int primary key, b int, c int)",
		},
		Query:       "create trigger update_old before update on x for each row BEGIN set old.c = new.a + 1; END",
		ExpectedErr: sql.ErrInvalidUpdateOfOldRow,
	},
	{
		Name: "set new row after insert",
		SetUpScript: []string{
			"create table x (a int primary key, b int, c int)",
		},
		Query:       "create trigger update_new after insert on x for each row set new.c = new.a + 1",
		ExpectedErr: sql.ErrInvalidUpdateInAfterTrigger,
	},
	{
		Name: "set new row after update",
		SetUpScript: []string{
			"create table x (a int primary key, b int, c int)",
		},
		Query:       "create trigger update_new after update on x for each row set new.c = new.a + 1",
		ExpectedErr: sql.ErrInvalidUpdateInAfterTrigger,
	},
	{
		Name: "set new row after update, begin block",
		SetUpScript: []string{
			"create table x (a int primary key, b int, c int)",
		},
		Query:       "create trigger update_new after update on x for each row BEGIN set new.c = new.a + 1; END",
		ExpectedErr: sql.ErrInvalidUpdateInAfterTrigger,
	},
}

View Source

var TriggerTests = []ScriptTest{

	{
		Name: "trigger after insert, insert into other table",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"create trigger insert_into_b after insert on a for each row insert into b values (new.x + 1)",
			"insert into a values (1), (3), (5)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select x from a order by 1",
				Expected: []sql.Row{
					{1}, {3}, {5},
				},
			},
			{
				Query: "select y from b order by 1",
				Expected: []sql.Row{
					{2}, {4}, {6},
				},
			},
			{
				Query: "insert into a values (7), (9)",
				Expected: []sql.Row{
					{sql.OkResult{RowsAffected: 2}},
				},
			},
		},
	},
	{
		Name: "trigger after insert, delete from other table",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"insert into b values (0), (2), (4), (6), (8)",
			"create trigger insert_into_b after insert on a for each row delete from b where y = (new.x + 1)",
			"insert into a values (1), (3), (5)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select x from a order by 1",
				Expected: []sql.Row{
					{1}, {3}, {5},
				},
			},
			{
				Query: "select y from b order by 1",
				Expected: []sql.Row{
					{0}, {8},
				},
			},
			{
				Query: "insert into a values (7), (9)",
				Expected: []sql.Row{
					{sql.OkResult{RowsAffected: 2}},
				},
			},
		},
	},
	{
		Name: "trigger after insert, update other table",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"insert into b values (0), (2), (4), (6), (8)",
			"create trigger insert_into_b after insert on a for each row update b set y = new.x where y = new.x + 1",
			"insert into a values (1), (3), (5)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select x from a order by 1",
				Expected: []sql.Row{
					{1}, {3}, {5},
				},
			},
			{
				Query: "select y from b order by 1",
				Expected: []sql.Row{
					{0}, {1}, {3}, {5}, {8},
				},
			},
		},
	},
	{
		Name: "trigger before insert, insert into other table",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"create trigger insert_into_b before insert on a for each row insert into b values (new.x + 1)",
			"insert into a values (1), (3), (5)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select x from a order by 1",
				Expected: []sql.Row{
					{1}, {3}, {5},
				},
			},
			{
				Query: "select y from b order by 1",
				Expected: []sql.Row{
					{2}, {4}, {6},
				},
			},
			{
				Query: "insert into a values (7), (9)",
				Expected: []sql.Row{
					{sql.OkResult{RowsAffected: 2}},
				},
			},
		},
	},
	{
		Name: "trigger before insert, delete from other table",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"insert into b values (0), (2), (4), (6), (8)",
			"create trigger insert_into_b before insert on a for each row delete from b where y = (new.x + 1)",
			"insert into a values (1), (3), (5)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select x from a order by 1",
				Expected: []sql.Row{
					{1}, {3}, {5},
				},
			},
			{
				Query: "select y from b order by 1",
				Expected: []sql.Row{
					{0}, {8},
				},
			},
			{
				Query: "insert into a values (7), (9)",
				Expected: []sql.Row{
					{sql.OkResult{RowsAffected: 2}},
				},
			},
		},
	},
	{
		Name: "trigger before insert, update other table",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"insert into b values (0), (2), (4), (6), (8)",
			"create trigger insert_into_b before insert on a for each row update b set y = new.x where y = new.x + 1",
			"insert into a values (1), (3), (5)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select x from a order by 1",
				Expected: []sql.Row{
					{1}, {3}, {5},
				},
			},
			{
				Query: "select y from b order by 1",
				Expected: []sql.Row{
					{0}, {1}, {3}, {5}, {8},
				},
			},
		},
	},
	{
		Name: "trigger before insert, alter inserted value",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create trigger insert_into_a before insert on a for each row set new.x = new.x + 1",
			"insert into a values (1)",
		},
		Query: "select x from a order by 1",
		Expected: []sql.Row{
			{2},
		},
	},
	{
		Name: "trigger before insert, alter inserted value, multiple columns",
		SetUpScript: []string{
			"create table x (a int primary key, b int, c int)",
			"create trigger insert_into_x before insert on x for each row set new.a = new.a + 1, new.b = new.c, new.c = 0",
			"insert into x values (1, 10, 100)",
		},
		Query: "select * from x order by 1",
		Expected: []sql.Row{
			{2, 100, 0},
		},
	},
	{
		Name: "trigger before insert, alter inserted value, multiple columns, system var",
		SetUpScript: []string{
			"create table x (a int primary key, b int, c int)",
			"set @@auto_increment_increment = 1",
			"create trigger insert_into_x before insert on x for each row " +
				"set new.a = new.a + 1, new.b = new.c, new.c = 0, @@auto_increment_increment = @@auto_increment_increment + 1",
			"insert into x values (1, 10, 100), (2, 20, 200)",
		},
		Query: "select *, @@auto_increment_increment from x order by 1",
		Expected: []sql.Row{
			{2, 100, 0, 3},
			{3, 200, 0, 3},
		},
	},
	{
		Name: "trigger before insert, alter inserted value, out of order insertion",
		SetUpScript: []string{
			"create table a (x int primary key, y int)",
			"create trigger a1 before insert on a for each row set new.x = new.x * 2, new.y = new.y * 3",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "insert into a (y, x) values (5,7), (9,11)",
				Expected: []sql.Row{
					{sql.OkResult{RowsAffected: 2}},
				},
			},
			{
				Query: "select x, y from a order by 1",
				Expected: []sql.Row{
					{14, 15},
					{22, 27},
				},
			},
		},
	},
	{
		Name: "trigger before insert, alter inserted value, incomplete insertion",
		SetUpScript: []string{
			"create table a (x int primary key, y int, z int default 5)",
			"create trigger a1 before insert on a for each row set new.x = new.x * 2, new.y = new.y * 3, new.z = new.z * 5",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "insert into a (y, x) values (5,7), (9,11)",
				Expected: []sql.Row{
					{sql.OkResult{RowsAffected: 2}},
				},
			},
			{
				Query: "select x, y, z from a order by 1",
				Expected: []sql.Row{
					{14, 15, 25},
					{22, 27, 25},
				},
			},
		},
	},

	{
		Name: "trigger after update, insert into other table",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"insert into a values (1), (3), (5)",
			"create trigger insert_into_b after update on a for each row insert into b values (old.x + new.x + 1)",
			"update a set x = x + 1 where x in (1, 3)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select x from a order by 1",
				Expected: []sql.Row{
					{2}, {4}, {5},
				},
			},
			{
				Query: "select y from b order by 1",
				Expected: []sql.Row{
					{4}, {8},
				},
			},
			{
				Query: "update a set x = x + 1 where x = 5",
				Expected: []sql.Row{
					{sql.OkResult{
						RowsAffected: 1,
						Info: plan.UpdateInfo{
							Matched: 1,
							Updated: 1,
						},
					}},
				},
			},
		},
	},
	{
		Name: "trigger after update, delete from other table",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"insert into a values (0), (2), (4), (6), (8)",
			"insert into b values (1), (3), (5), (7), (9)",
			"create trigger delete_from_b after update on a for each row delete from b where y = old.x + new.x",
			"update a set x = x + 1 where x in (2,4)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select x from a order by 1",
				Expected: []sql.Row{
					{0}, {3}, {5}, {6}, {8},
				},
			},
			{
				Query: "select y from b order by 1",
				Expected: []sql.Row{
					{1}, {3}, {7},
				},
			},
		},
	},
	{
		Name: "trigger after update, update other table",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"insert into a values (0), (2), (4), (6), (8)",
			"insert into b values (0), (2), (4), (8)",
			"create trigger update_b after update on a for each row update b set y = old.x + new.x + 1 where y = old.x",
			"update a set x = x + 1 where x in (2, 4)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select x from a order by 1",
				Expected: []sql.Row{
					{0}, {3}, {5}, {6}, {8},
				},
			},
			{
				Query: "select y from b order by 1",
				Expected: []sql.Row{
					{0}, {6}, {8}, {10},
				},
			},
		},
	},
	{
		Name: "trigger before update, insert into other table",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"insert into a values (1), (3), (5)",
			"create trigger insert_into_b before update on a for each row insert into b values (old.x + new.x + 1)",
			"update a set x = x + 1 where x in (1, 3)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select x from a order by 1",
				Expected: []sql.Row{
					{2}, {4}, {5},
				},
			},
			{
				Query: "select y from b order by 1",
				Expected: []sql.Row{
					{4}, {8},
				},
			},
			{
				Query: "update a set x = x + 1 where x = 5",
				Expected: []sql.Row{
					{sql.OkResult{
						RowsAffected: 1,
						Info: plan.UpdateInfo{
							Matched: 1,
							Updated: 1,
						},
					}},
				},
			},
		},
	},
	{
		Name: "trigger before update, delete from other table",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"insert into a values (0), (2), (4), (6), (8)",
			"insert into b values (1), (3), (5), (7), (9)",
			"create trigger delete_from_b before update on a for each row delete from b where y = old.x + new.x",
			"update a set x = x + 1 where x in (2,4)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select x from a order by 1",
				Expected: []sql.Row{
					{0}, {3}, {5}, {6}, {8},
				},
			},
			{
				Query: "select y from b order by 1",
				Expected: []sql.Row{
					{1}, {3}, {7},
				},
			},
		},
	},
	{
		Name: "trigger before update, update other table",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"insert into a values (0), (2), (4), (6), (8)",
			"insert into b values (0), (2), (4), (8)",
			"create trigger update_b before update on a for each row update b set y = old.x + new.x + 1 where y = old.x",
			"update a set x = x + 1 where x in (2, 4)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select x from a order by 1",
				Expected: []sql.Row{
					{0}, {3}, {5}, {6}, {8},
				},
			},
			{
				Query: "select y from b order by 1",
				Expected: []sql.Row{
					{0}, {6}, {8}, {10},
				},
			},
		},
	},
	{
		Name: "trigger before update, set new value",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"insert into a values (1), (10)",
			"create trigger update_a before update on a for each row set new.x = new.x + old.x",
			"update a set x = x + 1",
		},
		Query: "select x from a order by 1",
		Expected: []sql.Row{
			{3}, {21},
		},
	},
	{
		Name: "trigger before update, set new value to old value",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"insert into a values (1), (10)",
			"create trigger no_step_on_snek before update on a for each row set new.x = old.x",
			"update a set x = x + 1",
		},
		Query: "select x from a order by 1",
		Expected: []sql.Row{
			{1}, {10},
		},
	},
	{
		Name: "trigger before update, set new values, multiple cols",
		SetUpScript: []string{
			"create table a (x int primary key, y int)",
			"insert into a values (1,3), (10,20)",
			"create trigger update_a before update on a for each row set new.x = new.x + old.y, new.y = new.y + old.x",
			"update a set x = x + 1, y = y + 1",
		},
		Query: "select x, y from a order by 1",
		Expected: []sql.Row{
			{5, 5},
			{31, 31},
		},
	},
	{
		Name: "trigger before update, set new values, multiple cols (2)",
		SetUpScript: []string{
			"create table a (x int primary key, y int)",
			"insert into a values (1,3), (10,20)",
			"create trigger update_a before update on a for each row set new.x = new.x + new.y, new.y = new.y + old.y",
			"update a set x = x + 1, y = y + 1",
		},
		Query: "select x, y from a order by 1",
		Expected: []sql.Row{
			{6, 7},
			{32, 41},
		},
	},
	{
		Name: "trigger before update, with indexed update",
		SetUpScript: []string{
			"create table a (x int primary key, y int, unique key (y))",
			"create table b (z int primary key)",
			"insert into a values (1,3), (10,20)",
			"create trigger insert_b before update on a for each row insert into b values (old.x * 10)",
			"update a set x = x + 1 where y = 20",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select x, y from a order by 1",
				Expected: []sql.Row{
					{1, 3},
					{11, 20},
				},
			},
			{
				Query: "select z from b",
				Expected: []sql.Row{
					{100},
				},
			},
		},
	},

	{
		Name: "trigger after delete, insert into other table",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"insert into a values (1), (3), (5)",
			"create trigger insert_into_b after delete on a for each row insert into b values (old.x + 1)",
			"delete from a where x in (1, 3)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select x from a order by 1",
				Expected: []sql.Row{
					{5},
				},
			},
			{
				Query: "select y from b order by 1",
				Expected: []sql.Row{
					{2}, {4},
				},
			},
			{
				Query: "delete from a where x = 5",
				Expected: []sql.Row{
					{sql.OkResult{RowsAffected: 1}},
				},
			},
		},
	},
	{
		Name: "trigger after delete, delete from other table",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"insert into a values (0), (2), (4), (6), (8)",
			"insert into b values (0), (2), (4), (6), (8)",
			"create trigger delete_from_b after delete on a for each row delete from b where y = old.x",
			"delete from a where x in (2,4,6)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select x from a order by 1",
				Expected: []sql.Row{
					{0}, {8},
				},
			},
			{
				Query: "select y from b order by 1",
				Expected: []sql.Row{
					{0}, {8},
				},
			},
		},
	},
	{
		Name: "trigger after delete, update other table",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"insert into a values (0), (2), (4), (6), (8)",
			"insert into b values (0), (2), (4), (6), (8)",
			"create trigger update_b after delete on a for each row update b set y = old.x + 1 where y = old.x",
			"delete from a where x in (2,4,6)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select x from a order by 1",
				Expected: []sql.Row{
					{0}, {8},
				},
			},
			{
				Query: "select y from b order by 1",
				Expected: []sql.Row{
					{0}, {3}, {5}, {7}, {8},
				},
			},
		},
	},
	{
		Name: "trigger before delete, insert into other table",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"insert into a values (0), (2), (4), (6), (8)",
			"create trigger insert_into_b before delete on a for each row insert into b values (old.x + 1)",
			"delete from a where x in (2, 4, 6)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select x from a order by 1",
				Expected: []sql.Row{
					{0}, {8},
				},
			},
			{
				Query: "select y from b order by 1",
				Expected: []sql.Row{
					{3}, {5}, {7},
				},
			},
			{
				Query: "delete from a where x = 0",
				Expected: []sql.Row{
					{sql.OkResult{RowsAffected: 1}},
				},
			},
		},
	},
	{
		Name: "trigger before delete, delete from other table",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"insert into a values (0), (2), (4), (6), (8)",
			"insert into b values (1), (3), (5), (7), (9)",
			"create trigger delete_from_b before delete on a for each row delete from b where y = (old.x + 1)",
			"delete from a where x in (2, 4, 6)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select x from a order by 1",
				Expected: []sql.Row{
					{0}, {8},
				},
			},
			{
				Query: "select y from b order by 1",
				Expected: []sql.Row{
					{1}, {9},
				},
			},
		},
	},
	{
		Name: "trigger before delete, update other table",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"insert into a values (0), (2), (4), (6), (8)",
			"insert into b values (1), (3), (5), (7), (9)",
			"create trigger update_b before delete on a for each row update b set y = old.x where y = old.x + 1",
			"delete from a where x in (2, 4, 6)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select x from a order by 1",
				Expected: []sql.Row{
					{0}, {8},
				},
			},
			{
				Query: "select y from b order by 1",
				Expected: []sql.Row{
					{1}, {2}, {4}, {6}, {9},
				},
			},
		},
	},
	{
		Name: "trigger before delete, delete with index",
		SetUpScript: []string{
			"create table a (x int primary key, z int, unique key (z))",
			"create table b (y int primary key)",
			"insert into a values (0,1), (2,3), (4,5)",
			"create trigger insert_b before delete on a for each row insert into b values (old.x * 2)",
			"delete from a where z > 2",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select x from a order by 1",
				Expected: []sql.Row{
					{0},
				},
			},
			{
				Query: "select y from b order by 1",
				Expected: []sql.Row{
					{4}, {8},
				},
			},
		},
	},

	{
		Name: "triggers before and after insert",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"create trigger a1 before insert on a for each row insert into b values (NEW.x * 7)",
			"create trigger a2 after insert on a for each row insert into b values (New.x * 11)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "insert into a values (2), (3), (5)",
				Expected: []sql.Row{
					{sql.NewOkResult(3)},
				},
			},
			{
				Query: "select x from a order by 1",
				Expected: []sql.Row{
					{2}, {3}, {5},
				},
			},
			{
				Query: "select y from b order by 1",
				Expected: []sql.Row{
					{14}, {21}, {22}, {33}, {35}, {55},
				},
			},
		},
	},
	{
		Name: "multiple triggers before insert",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create trigger a1 before insert on a for each row set new.x = New.x + 1",
			"create trigger a2 before insert on a for each row set new.x = New.x * 2",
			"create trigger a3 before insert on a for each row set new.x = New.x - 5",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "insert into a values (1), (3)",
				Expected: []sql.Row{
					{sql.NewOkResult(2)},
				},
			},
			{
				Query: "select x from a order by 1",
				Expected: []sql.Row{
					{-1}, {3},
				},
			},
		},
	},
	{
		Name: "multiple triggers before insert, with precedes / follows",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create trigger a1 before insert on a for each row set new.x = New.x + 1",
			"create trigger a2 before insert on a for each row precedes a1 set new.x = New.x * 2",
			"create trigger a3 before insert on a for each row precedes a2 set new.x = New.x - 5",
			"create trigger a4 before insert on a for each row follows a2 set new.x = New.x * 3",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "insert into a values (1), (3)",
				Expected: []sql.Row{
					{sql.NewOkResult(2)},
				},
			},
			{
				Query: "select x from a order by 1",
				Expected: []sql.Row{
					{-23}, {-11},
				},
			},
		},
	},
	{
		Name: "triggers before and after update",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"create trigger a1 before update on a for each row insert into b values (old.x * 7)",
			"create trigger a2 after update on a for each row insert into b values (old.x * 11)",
			"insert into a values (2), (3), (5)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "update a set x = x * 2",
				Expected: []sql.Row{
					{sql.OkResult{
						RowsAffected: 3,
						Info: plan.UpdateInfo{
							Matched: 3,
							Updated: 3,
						},
					}},
				},
			},
			{
				Query: "select x from a order by 1",
				Expected: []sql.Row{
					{4}, {6}, {10},
				},
			},
			{
				Query: "select y from b order by 1",
				Expected: []sql.Row{
					{14}, {21}, {22}, {33}, {35}, {55},
				},
			},
		},
	},
	{
		Name: "multiple triggers before and after update",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"create trigger a1 before update on a for each row insert into b values (old.x * 7)",
			"create trigger a2 after update on a for each row insert into b values (old.x * 11)",
			"create trigger a3 before update on a for each row insert into b values (old.x * 13)",
			"create trigger a4 after update on a for each row insert into b values (old.x * 17)",
			"insert into a values (2), (3), (5)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "update a set x = x * 2",
				Expected: []sql.Row{
					{sql.OkResult{
						RowsAffected: 3,
						Info: plan.UpdateInfo{
							Matched: 3,
							Updated: 3,
						},
					}},
				},
			},
			{
				Query: "select x from a order by 1",
				Expected: []sql.Row{
					{4}, {6}, {10},
				},
			},
			{
				Query: "select y from b order by 1",
				Expected: []sql.Row{
					{14}, {21}, {22}, {26}, {33}, {34}, {35}, {39}, {51}, {55}, {65}, {85},
				},
			},
		},
	},
	{
		Name: "triggers before and after delete",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"create trigger a1 before delete on a for each row insert into b values (old.x * 7)",
			"create trigger a2 after delete on a for each row insert into b values (old.x * 11)",
			"insert into a values (2), (3), (5)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "delete from a",
				Expected: []sql.Row{
					{sql.NewOkResult(3)},
				},
			},
			{
				Query:    "select x from a order by 1",
				Expected: []sql.Row{},
			},
			{
				Query: "select y from b order by 1",
				Expected: []sql.Row{
					{14}, {21}, {22}, {33}, {35}, {55},
				},
			},
		},
	},
	{
		Name: "multiple triggers before and after delete",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"create trigger a1 before delete on a for each row insert into b values (old.x * 7)",
			"create trigger a2 after delete on a for each row insert into b values (old.x * 11)",
			"create trigger a3 before delete on a for each row insert into b values (old.x * 13)",
			"create trigger a4 after delete on a for each row insert into b values (old.x * 17)",
			"insert into a values (2), (3), (5)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "delete from a",
				Expected: []sql.Row{
					{sql.NewOkResult(3)},
				},
			},
			{
				Query:    "select x from a order by 1",
				Expected: []sql.Row{},
			},
			{
				Query: "select y from b order by 1",
				Expected: []sql.Row{
					{14}, {21}, {22}, {26}, {33}, {34}, {35}, {39}, {51}, {55}, {65}, {85},
				},
			},
		},
	},
	{
		Name: "multiple triggers before and after insert, with precedes / follows",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"insert into b values (1), (3)",
			"create trigger a1 before insert on a for each row set new.x = New.x + 1",
			"create trigger a2 before insert on a for each row precedes a1 set new.x = New.x * 2",
			"create trigger a3 before insert on a for each row precedes a2 set new.x = New.x - 5",
			"create trigger a4 before insert on a for each row follows a2 set new.x = New.x * 3",

			"create trigger a5 after insert on a for each row update b set y = y + 1 order by y asc",
			"create trigger a6 after insert on a for each row precedes a5 update b set y = y * 2 order by y asc",
			"create trigger a7 after insert on a for each row precedes a6 update b set y = y - 5 order by y asc",
			"create trigger a8 after insert on a for each row follows a6 update b set y = y * 3 order by y asc",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "insert into a values (1), (3)",
				Expected: []sql.Row{
					{sql.NewOkResult(2)},
				},
			},
			{
				Query: "select x from a order by 1",
				Expected: []sql.Row{
					{-23}, {-11},
				},
			},
			{
				Query: "select y from b order by 1",

				Expected: []sql.Row{
					{-167}, {-95},
				},
			},
		},
	},

	{
		Name: "trigger before insert, multiple triggers defined",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"create table c (z int primary key)",

			"create trigger a1 before insert on a for each row insert into b values (new.x * 2)",
			"create trigger a2 before update on a for each row insert into b values (new.x * 3)",
			"create trigger a3 before delete on a for each row insert into b values (old.x * 5)",
			"create trigger b1 before insert on b for each row insert into c values (new.y * 7)",
			"create trigger b2 before update on b for each row insert into c values (new.y * 11)",
			"create trigger b3 before delete on b for each row insert into c values (old.y * 13)",
			"insert into a values (1), (2), (3)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select x from a order by 1",
				Expected: []sql.Row{
					{1}, {2}, {3},
				},
			},
			{
				Query: "select y from b order by 1",
				Expected: []sql.Row{
					{2}, {4}, {6},
				},
			},
			{
				Query: "select z from c order by 1",
				Expected: []sql.Row{
					{14}, {28}, {42},
				},
			},
		},
	},

	{
		Name: "infoschema for multiple triggers before and after insert, with precedes / follows",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create table b (y int primary key)",
			"insert into b values (1), (3)",
			"create trigger a1 before insert on a for each row set new.x = New.x + 1",
			"create trigger a2 before insert on a for each row precedes a1 set new.x = New.x * 2",
			"create trigger a3 before insert on a for each row precedes a2 set new.x = New.x - 5",
			"create trigger a4 before insert on a for each row follows a2 set new.x = New.x * 3",

			"create trigger a5 after insert on a for each row update b set y = y + 1 order by y asc",
			"create trigger a6 after insert on a for each row precedes a5 update b set y = y * 2 order by y asc",
			"create trigger a7 after insert on a for each row precedes a6 update b set y = y - 5 order by y asc",
			"create trigger a8 after insert on a for each row follows a6 update b set y = y * 3 order by y asc",

			"insert into a values (1), (3)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "select * from information_schema.triggers",
				Expected: []sql.Row{
					{
						"def",
						"mydb",
						"a1",
						"INSERT",
						"def",
						"mydb",
						"a",
						int64(4),
						nil,
						"set new.x = New.x + 1",
						"ROW",
						"BEFORE",
						nil,
						nil,
						"OLD",
						"NEW",
						time.Unix(0, 0).UTC(),
						"",
						"",
						sql.Collation_Default.CharacterSet().String(),
						sql.Collation_Default.String(),
						sql.Collation_Default.String(),
					},
					{
						"def",
						"mydb",
						"a2",
						"INSERT",
						"def",
						"mydb",
						"a",
						int64(2),
						nil,
						"set new.x = New.x * 2",
						"ROW",
						"BEFORE",
						nil,
						nil,
						"OLD",
						"NEW",
						time.Unix(0, 0).UTC(),
						"",
						"",
						sql.Collation_Default.CharacterSet().String(),
						sql.Collation_Default.String(),
						sql.Collation_Default.String(),
					},
					{
						"def",
						"mydb",
						"a3",
						"INSERT",
						"def",
						"mydb",
						"a",
						int64(1),
						nil,
						"set new.x = New.x - 5",
						"ROW",
						"BEFORE",
						nil,
						nil,
						"OLD",
						"NEW",
						time.Unix(0, 0).UTC(),
						"",
						"",
						sql.Collation_Default.CharacterSet().String(),
						sql.Collation_Default.String(),
						sql.Collation_Default.String(),
					},
					{
						"def",
						"mydb",
						"a4",
						"INSERT",
						"def",
						"mydb",
						"a",
						int64(3),
						nil,
						"set new.x = New.x * 3",
						"ROW",
						"BEFORE",
						nil,
						nil,
						"OLD",
						"NEW",
						time.Unix(0, 0).UTC(),
						"",
						"",
						sql.Collation_Default.CharacterSet().String(),
						sql.Collation_Default.String(),
						sql.Collation_Default.String(),
					},
					{
						"def",
						"mydb",
						"a5",
						"INSERT",
						"def",
						"mydb",
						"a",
						int64(4),
						nil,
						"update b set y = y + 1 order by y asc",
						"ROW",
						"AFTER",
						nil,
						nil,
						"OLD",
						"NEW",
						time.Unix(0, 0).UTC(),
						"",
						"",
						sql.Collation_Default.CharacterSet().String(),
						sql.Collation_Default.String(),
						sql.Collation_Default.String(),
					},
					{
						"def",
						"mydb",
						"a6",
						"INSERT",
						"def",
						"mydb",
						"a",
						int64(2),
						nil,
						"update b set y = y * 2 order by y asc",
						"ROW",
						"AFTER",
						nil,
						nil,
						"OLD",
						"NEW",
						time.Unix(0, 0).UTC(),
						"",
						"",
						sql.Collation_Default.CharacterSet().String(),
						sql.Collation_Default.String(),
						sql.Collation_Default.String(),
					},
					{
						"def",
						"mydb",
						"a7",
						"INSERT",
						"def",
						"mydb",
						"a",
						int64(1),
						nil,
						"update b set y = y - 5 order by y asc",
						"ROW",
						"AFTER",
						nil,
						nil,
						"OLD",
						"NEW",
						time.Unix(0, 0).UTC(),
						"",
						"",
						sql.Collation_Default.CharacterSet().String(),
						sql.Collation_Default.String(),
						sql.Collation_Default.String(),
					},
					{
						"def",
						"mydb",
						"a8",
						"INSERT",
						"def",
						"mydb",
						"a",
						int64(3),
						nil,
						"update b set y = y * 3 order by y asc",
						"ROW",
						"AFTER",
						nil,
						nil,
						"OLD",
						"NEW",
						time.Unix(0, 0).UTC(),
						"",
						"",
						sql.Collation_Default.CharacterSet().String(),
						sql.Collation_Default.String(),
						sql.Collation_Default.String(),
					},
				},
			},
		},
	},

	{
		Name: "show create triggers",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create trigger a1 before insert on a for each row set new.x = new.x + 1",
			"create table b (y int primary key)",
			"create trigger b1 before insert on b for each row set new.x = new.x + 2",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "show create trigger a1",
				Expected: []sql.Row{
					{
						"a1",
						"",
						"create trigger a1 before insert on a for each row set new.x = new.x + 1",
						sql.Collation_Default.CharacterSet().String(),
						sql.Collation_Default.String(),
						sql.Collation_Default.String(),
						time.Unix(0, 0).UTC(),
					},
				},
			},
			{
				Query: "show create trigger b1",
				Expected: []sql.Row{
					{
						"b1",
						"",
						"create trigger b1 before insert on b for each row set new.x = new.x + 2",
						sql.Collation_Default.CharacterSet().String(),
						sql.Collation_Default.String(),
						sql.Collation_Default.String(),
						time.Unix(0, 0).UTC(),
					},
				},
			},
			{
				Query:       "show create trigger b2",
				ExpectedErr: sql.ErrTriggerDoesNotExist,
			},
		},
	},

	{
		Name: "show triggers",
		SetUpScript: []string{
			"create table abb (x int primary key)",
			"create table acc (y int primary key)",
			"create trigger t1 before insert on abb for each row set new.x = new.x + 1",
			"create trigger t2 before insert on abb for each row set new.x = new.x + 2",
			"create trigger t3 after insert on acc for each row insert into abb values (new.y)",
			"create trigger t4 before update on acc for each row set new.y = old.y + 2",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "show triggers",
				Expected: []sql.Row{
					{
						"t1",
						"INSERT",
						"abb",
						"set new.x = new.x + 1",
						"BEFORE",
						time.Unix(0, 0).UTC(),
						"",
						"",
						sql.Collation_Default.CharacterSet().String(),
						sql.Collation_Default.String(),
						sql.Collation_Default.String(),
					},
					{
						"t2",
						"INSERT",
						"abb",
						"set new.x = new.x + 2",
						"BEFORE",
						time.Unix(0, 0).UTC(),
						"",
						"",
						sql.Collation_Default.CharacterSet().String(),
						sql.Collation_Default.String(),
						sql.Collation_Default.String(),
					},
					{
						"t3",
						"INSERT",
						"acc",
						"insert into abb values (new.y)",
						"AFTER",
						time.Unix(0, 0).UTC(),
						"",
						"",
						sql.Collation_Default.CharacterSet().String(),
						sql.Collation_Default.String(),
						sql.Collation_Default.String(),
					},
					{
						"t4",
						"UPDATE",
						"acc",
						"set new.y = old.y + 2",
						"BEFORE",
						time.Unix(0, 0).UTC(),
						"",
						"",
						sql.Collation_Default.CharacterSet().String(),
						sql.Collation_Default.String(),
						sql.Collation_Default.String(),
					},
				},
			},
			{
				Query: "show triggers from mydb",
				Expected: []sql.Row{
					{
						"t1",
						"INSERT",
						"abb",
						"set new.x = new.x + 1",
						"BEFORE",
						time.Unix(0, 0).UTC(),
						"",
						"",
						sql.Collation_Default.CharacterSet().String(),
						sql.Collation_Default.String(),
						sql.Collation_Default.String(),
					},
					{
						"t2",
						"INSERT",
						"abb",
						"set new.x = new.x + 2",
						"BEFORE",
						time.Unix(0, 0).UTC(),
						"",
						"",
						sql.Collation_Default.CharacterSet().String(),
						sql.Collation_Default.String(),
						sql.Collation_Default.String(),
					},
					{
						"t3",
						"INSERT",
						"acc",
						"insert into abb values (new.y)",
						"AFTER",
						time.Unix(0, 0).UTC(),
						"",
						"",
						sql.Collation_Default.CharacterSet().String(),
						sql.Collation_Default.String(),
						sql.Collation_Default.String(),
					},
					{
						"t4",
						"UPDATE",
						"acc",
						"set new.y = old.y + 2",
						"BEFORE",
						time.Unix(0, 0).UTC(),
						"",
						"",
						sql.Collation_Default.CharacterSet().String(),
						sql.Collation_Default.String(),
						sql.Collation_Default.String(),
					},
				},
			},
			{
				Query: "show triggers like '%cc'",
				Expected: []sql.Row{
					{
						"t3",
						"INSERT",
						"acc",
						"insert into abb values (new.y)",
						"AFTER",
						time.Unix(0, 0).UTC(),
						"",
						"",
						sql.Collation_Default.CharacterSet().String(),
						sql.Collation_Default.String(),
						sql.Collation_Default.String(),
					},
					{
						"t4",
						"UPDATE",
						"acc",
						"set new.y = old.y + 2",
						"BEFORE",
						time.Unix(0, 0).UTC(),
						"",
						"",
						sql.Collation_Default.CharacterSet().String(),
						sql.Collation_Default.String(),
						sql.Collation_Default.String(),
					},
				},
			},
			{
				Query: "show triggers where event = 'INSERT'",
				Expected: []sql.Row{
					{
						"t1",
						"INSERT",
						"abb",
						"set new.x = new.x + 1",
						"BEFORE",
						time.Unix(0, 0).UTC(),
						"",
						"",
						sql.Collation_Default.CharacterSet().String(),
						sql.Collation_Default.String(),
						sql.Collation_Default.String(),
					},
					{
						"t2",
						"INSERT",
						"abb",
						"set new.x = new.x + 2",
						"BEFORE",
						time.Unix(0, 0).UTC(),
						"",
						"",
						sql.Collation_Default.CharacterSet().String(),
						sql.Collation_Default.String(),
						sql.Collation_Default.String(),
					},
					{
						"t3",
						"INSERT",
						"acc",
						"insert into abb values (new.y)",
						"AFTER",
						time.Unix(0, 0).UTC(),
						"",
						"",
						sql.Collation_Default.CharacterSet().String(),
						sql.Collation_Default.String(),
						sql.Collation_Default.String(),
					},
				},
			},
			{
				Query: "show triggers where timing = 'AFTER'",
				Expected: []sql.Row{
					{
						"t3",
						"INSERT",
						"acc",
						"insert into abb values (new.y)",
						"AFTER",
						time.Unix(0, 0).UTC(),
						"",
						"",
						sql.Collation_Default.CharacterSet().String(),
						sql.Collation_Default.String(),
						sql.Collation_Default.String(),
					},
				},
			},
			{
				Query: "show triggers where timing = 'BEFORE' and `Table` like '%bb'",
				Expected: []sql.Row{
					{
						"t1",
						"INSERT",
						"abb",
						"set new.x = new.x + 1",
						"BEFORE",
						time.Unix(0, 0).UTC(),
						"",
						"",
						sql.Collation_Default.CharacterSet().String(),
						sql.Collation_Default.String(),
						sql.Collation_Default.String(),
					},
					{
						"t2",
						"INSERT",
						"abb",
						"set new.x = new.x + 2",
						"BEFORE",
						time.Unix(0, 0).UTC(),
						"",
						"",
						sql.Collation_Default.CharacterSet().String(),
						sql.Collation_Default.String(),
						sql.Collation_Default.String(),
					},
				},
			},
		},
	},

	{
		Name: "drop trigger",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create trigger t1 before insert on a for each row set new.x = new.x * 1",
			"create trigger t2 before insert on a for each row follows t1 set new.x = new.x * 2",
			"create trigger t3 before insert on a for each row set new.x = new.x * 3",
			"create trigger t4 before insert on a for each row precedes t3 set new.x = new.x * 5",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:       "drop trigger t1",
				ExpectedErr: sql.ErrTriggerCannotBeDropped,
			},
			{
				Query:       "drop trigger t3",
				ExpectedErr: sql.ErrTriggerCannotBeDropped,
			},
			{
				Query:    "drop trigger t4",
				Expected: []sql.Row{},
			},
			{
				Query:    "drop trigger t3",
				Expected: []sql.Row{},
			},
			{
				Query:    "drop trigger if exists t5",
				Expected: []sql.Row{},
			},
			{
				Query:       "drop trigger t5",
				ExpectedErr: sql.ErrTriggerDoesNotExist,
			},
			{
				Query: "select trigger_name from information_schema.triggers order by 1",
				Expected: []sql.Row{
					{"t1"},
					{"t2"},
				},
			},
			{
				Query:    "drop trigger if exists t2",
				Expected: []sql.Row{},
			},
			{
				Query: "select trigger_name from information_schema.triggers order by 1",
				Expected: []sql.Row{
					{"t1"},
				},
			},
		},
	},

	{
		Name: "drop table referenced in triggers",
		SetUpScript: []string{
			"create table a (w int primary key)",
			"create table b (x int primary key)",
			"create table c (y int primary key)",
			"create table d (z int primary key)",
			"create trigger t1 before insert on a for each row set new.w = new.w",
			"create trigger t2 before insert on a for each row set new.w = new.w * 100",
			"create trigger t3 before insert on b for each row set new.x = new.x",
			"create trigger t4 before insert on b for each row set new.x = new.x * 100",
			"create trigger t5 before insert on c for each row set new.y = new.y",
			"create trigger t6 before insert on c for each row set new.y = new.y * 100",
			"create trigger t7 before insert on d for each row set new.z = new.z",
			"create trigger t8 before insert on d for each row set new.z = new.z * 100",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "drop table a",
				Expected: []sql.Row{},
			},
			{
				Query: "select trigger_name from information_schema.triggers order by 1",
				Expected: []sql.Row{
					{"t3"},
					{"t4"},
					{"t5"},
					{"t6"},
					{"t7"},
					{"t8"},
				},
			},
			{
				Query:    "drop table if exists b, d, e",
				Expected: []sql.Row{},
			},
			{
				Query: "select trigger_name from information_schema.triggers order by 1",
				Expected: []sql.Row{
					{"t5"},
					{"t6"},
				},
			},
		},
	},
	{
		Name: "drop table referenced in triggers with follows/precedes",
		SetUpScript: []string{
			"create table a (x int primary key)",
			"create trigger t1 before insert on a for each row set new.x = new.x",
			"create trigger t2 before insert on a for each row follows t1 set new.x = new.x * 10",
			"create trigger t3 before insert on a for each row precedes t1 set new.x = new.x * 100",
			"create trigger t4 before insert on a for each row follows t3 set new.x = new.x * 1000",
			"create trigger t5 before insert on a for each row precedes t2 set new.x = new.x * 10000",
			"create trigger t6 before insert on a for each row follows t4 set new.x = new.x * 100000",
			"create trigger t7 before insert on a for each row precedes t1 set new.x = new.x * 1000000",
			"create trigger t8 before insert on a for each row follows t6 set new.x = new.x * 10000000",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "drop table a",
				Expected: []sql.Row{},
			},
			{
				Query:    "show triggers",
				Expected: []sql.Row{},
			},
		},
	},
}

View Source

var UpdateErrorTests = []GenericErrorQueryTest{
	{
		Name:  "invalid table",
		Query: "UPDATE doesnotexist SET i = 0;",
	},
	{
		Name:  "missing binding",
		Query: "UPDATE mytable SET i = ?;",
	},
	{
		Name:  "wrong number of columns",
		Query: `UPDATE mytable SET i = ("one", "two");`,
	},
	{
		Name:  "type mismatch: string -> int",
		Query: `UPDATE mytable SET i = "one"`,
	},
	{
		Name:  "type mismatch: string -> float",
		Query: `UPDATE floattable SET f64 = "one"`,
	},
	{
		Name:  "type mismatch: string -> uint",
		Query: `UPDATE typestable SET f64 = "one"`,
	},
	{
		Name:  "invalid column set",
		Query: "UPDATE mytable SET z = 0;",
	},
	{
		Name:  "invalid column set value",
		Query: "UPDATE mytable SET i = z;",
	},
	{
		Name:  "invalid column where",
		Query: "UPDATE mytable SET s = 'hi' WHERE z = 1;",
	},
	{
		Name:  "invalid column order by",
		Query: "UPDATE mytable SET s = 'hi' ORDER BY z;",
	},
	{
		Name:  "negative limit",
		Query: "UPDATE mytable SET s = 'hi' LIMIT -1;",
	},
	{
		Name:  "negative offset",
		Query: "UPDATE mytable SET s = 'hi' LIMIT 1 OFFSET -1;",
	},
	{
		Name:  "set null on non-nullable",
		Query: "UPDATE mytable SET s = NULL;",
	},
}

View Source

var UpdateTests = []WriteQueryTest{
	{
		WriteQuery:          "UPDATE mytable SET s = 'updated';",
		ExpectedWriteResult: []sql.Row{{newUpdateResult(3, 3)}},
		SelectQuery:         "SELECT * FROM mytable;",
		ExpectedSelect:      []sql.Row{{int64(1), "updated"}, {int64(2), "updated"}, {int64(3), "updated"}},
	},
	{
		WriteQuery:          "UPDATE mytable SET s = ?;",
		ExpectedWriteResult: []sql.Row{{newUpdateResult(3, 3)}},
		SelectQuery:         "SELECT * FROM mytable;",
		ExpectedSelect:      []sql.Row{{int64(1), "updated"}, {int64(2), "updated"}, {int64(3), "updated"}},
		Bindings: map[string]sql.Expression{
			"v1": expression.NewLiteral("updated", sql.Text),
		},
	},
	{
		WriteQuery:          "UPDATE mytable SET s = 'updated' WHERE i > 9999;",
		ExpectedWriteResult: []sql.Row{{newUpdateResult(0, 0)}},
		SelectQuery:         "SELECT * FROM mytable;",
		ExpectedSelect:      []sql.Row{{int64(1), "first row"}, {int64(2), "second row"}, {int64(3), "third row"}},
	},
	{
		WriteQuery:          "UPDATE mytable SET s = 'updated' WHERE i = 1;",
		ExpectedWriteResult: []sql.Row{{newUpdateResult(1, 1)}},
		SelectQuery:         "SELECT * FROM mytable;",
		ExpectedSelect:      []sql.Row{{int64(1), "updated"}, {int64(2), "second row"}, {int64(3), "third row"}},
	},
	{
		WriteQuery:          "UPDATE mytable SET s = 'updated' WHERE i <> 9999;",
		ExpectedWriteResult: []sql.Row{{newUpdateResult(3, 3)}},
		SelectQuery:         "SELECT * FROM mytable;",
		ExpectedSelect:      []sql.Row{{int64(1), "updated"}, {int64(2), "updated"}, {int64(3), "updated"}},
	},
	{
		WriteQuery:          "UPDATE floattable SET f32 = f32 + f32, f64 = f32 * f64 WHERE i = 2;",
		ExpectedWriteResult: []sql.Row{{newUpdateResult(1, 1)}},
		SelectQuery:         "SELECT * FROM floattable WHERE i = 2;",
		ExpectedSelect:      []sql.Row{{int64(2), float32(3.0), float64(4.5)}},
	},
	{
		WriteQuery:          "UPDATE floattable SET f32 = 5, f32 = 4 WHERE i = 1;",
		ExpectedWriteResult: []sql.Row{{newUpdateResult(1, 1)}},
		SelectQuery:         "SELECT f32 FROM floattable WHERE i = 1;",
		ExpectedSelect:      []sql.Row{{float32(4.0)}},
	},
	{
		WriteQuery:          "UPDATE mytable SET s = 'first row' WHERE i = 1;",
		ExpectedWriteResult: []sql.Row{{newUpdateResult(1, 0)}},
		SelectQuery:         "SELECT * FROM mytable;",
		ExpectedSelect:      []sql.Row{{int64(1), "first row"}, {int64(2), "second row"}, {int64(3), "third row"}},
	},
	{
		WriteQuery:          "UPDATE niltable SET b = NULL WHERE f IS NULL;",
		ExpectedWriteResult: []sql.Row{{newUpdateResult(3, 2)}},
		SelectQuery:         "SELECT i,b FROM niltable WHERE f IS NULL;",
		ExpectedSelect:      []sql.Row{{int64(1), nil}, {int64(2), nil}, {int64(3), nil}},
	},
	{
		WriteQuery:          "UPDATE mytable SET s = 'updated' ORDER BY i ASC LIMIT 2;",
		ExpectedWriteResult: []sql.Row{{newUpdateResult(2, 2)}},
		SelectQuery:         "SELECT * FROM mytable;",
		ExpectedSelect:      []sql.Row{{int64(1), "updated"}, {int64(2), "updated"}, {int64(3), "third row"}},
	},
	{
		WriteQuery:          "UPDATE mytable SET s = 'updated' ORDER BY i DESC LIMIT 2;",
		ExpectedWriteResult: []sql.Row{{newUpdateResult(2, 2)}},
		SelectQuery:         "SELECT * FROM mytable;",
		ExpectedSelect:      []sql.Row{{int64(1), "first row"}, {int64(2), "updated"}, {int64(3), "updated"}},
	},
	{
		WriteQuery:          "UPDATE mytable SET s = 'updated' ORDER BY i LIMIT 1 OFFSET 1;",
		ExpectedWriteResult: []sql.Row{{newUpdateResult(1, 1)}},
		SelectQuery:         "SELECT * FROM mytable;",
		ExpectedSelect:      []sql.Row{{int64(1), "first row"}, {int64(2), "updated"}, {int64(3), "third row"}},
	},
	{
		WriteQuery:          "UPDATE mytable SET s = 'updated';",
		ExpectedWriteResult: []sql.Row{{newUpdateResult(3, 3)}},
		SelectQuery:         "SELECT * FROM mytable;",
		ExpectedSelect:      []sql.Row{{int64(1), "updated"}, {int64(2), "updated"}, {int64(3), "updated"}},
	},
	{
		WriteQuery:          "UPDATE typestable SET ti = '2020-03-06 00:00:00';",
		ExpectedWriteResult: []sql.Row{{newUpdateResult(1, 1)}},
		SelectQuery:         "SELECT * FROM typestable;",
		ExpectedSelect: []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}},
	},
	{
		WriteQuery:          "UPDATE typestable SET ti = '2020-03-06 00:00:00', da = '2020-03-06';",
		ExpectedWriteResult: []sql.Row{{newUpdateResult(1, 1)}},
		SelectQuery:         "SELECT * FROM typestable;",
		ExpectedSelect: []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}},
	},
	{
		WriteQuery:          "UPDATE typestable SET da = '0000-00-00', ti = '0000-00-00 00:00:00';",
		ExpectedWriteResult: []sql.Row{{newUpdateResult(1, 1)}},
		SelectQuery:         "SELECT * FROM typestable;",
		ExpectedSelect: []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 VariableErrorTests = []QueryErrorTest{

	{
		Query:       "set @myvar = bareword",
		ExpectedErr: sql.ErrColumnNotFound,
	},
}

View Source

var VariableQueries = []ScriptTest{
	{
		Name: "set system variables",
		SetUpScript: []string{
			"set @@auto_increment_increment = 100, sql_select_limit = 1",
		},
		Query: "SELECT @@auto_increment_increment, @@sql_select_limit",
		Expected: []sql.Row{
			{100, 1},
		},
	},
	{
		Name: "set system variables mixed case",
		SetUpScript: []string{
			"set @@auto_increment_INCREMENT = 100, sql_select_LIMIT = 1",
		},
		Query: "SELECT @@auto_increment_increment, @@sql_select_limit",
		Expected: []sql.Row{
			{100, 1},
		},
	},
	{
		Name: "set system variable defaults",
		SetUpScript: []string{
			"set @@auto_increment_increment = 100, sql_select_limit = 1",
			"set @@auto_increment_increment = default, sql_select_limit = default",
		},
		Query: "SELECT @@auto_increment_increment, @@sql_select_limit",
		Expected: []sql.Row{
			{1, math.MaxInt32},
		},
	},
	{
		Name: "set system variable ON / OFF",
		SetUpScript: []string{
			"set @@autocommit = ON, sql_mode = OFF",
		},
		Query: "SELECT @@autocommit, @@session.sql_mode",
		Expected: []sql.Row{
			{1, 0},
		},
	},
	{
		Name: "set system variable true / false quoted",
		SetUpScript: []string{
			`set @@autocommit = "true", sql_mode = "false"`,
		},
		Query: "SELECT @@autocommit, @@session.sql_mode",
		Expected: []sql.Row{
			{1, 0},
		},
	},
	{
		Name: "set system variable true / false",
		SetUpScript: []string{
			`set @@autocommit = true, sql_mode = false`,
		},
		Query: "SELECT @@autocommit, @@session.sql_mode",
		Expected: []sql.Row{
			{1, 0},
		},
	},
	{
		Name: "set system variable with expressions",
		SetUpScript: []string{
			`set sql_mode = "123", @@auto_increment_increment = 1`,
			`set sql_mode = concat(@@sql_mode, "456"), @@auto_increment_increment = @@auto_increment_increment + 3`,
		},
		Query: "SELECT @@sql_mode, @@auto_increment_increment",
		Expected: []sql.Row{
			{"123456", 4},
		},
	},
	{
		Name: "set system variable to another system variable",
		SetUpScript: []string{
			`set @@auto_increment_increment = 123`,
			`set @@sql_select_limit = @@auto_increment_increment`,
		},
		Query: "SELECT @@sql_select_limit",
		Expected: []sql.Row{
			{123},
		},
	},
	{
		Name: "set names",
		SetUpScript: []string{
			`set names utf8mb4`,
		},
		Query: "SELECT @@character_set_client, @@character_set_connection, @@character_set_results",
		Expected: []sql.Row{
			{"utf8mb4", "utf8mb4", "utf8mb4"},
		},
	},

	{
		Name: "set names quoted",
		SetUpScript: []string{
			`set NAMES "charset"`,
		},
		Query: "SELECT @@character_set_client, @@character_set_connection, @@character_set_results",
		Expected: []sql.Row{
			{"charset", "charset", "charset"},
		},
	},
	{
		Name: "set system variable to bareword",
		SetUpScript: []string{
			`set @@sql_mode = some_mode`,
		},
		Query: "SELECT @@sql_mode",
		Expected: []sql.Row{
			{"some_mode"},
		},
	},
	{
		Name: "set system variable to bareword, unqualified",
		SetUpScript: []string{
			`set sql_mode = some_mode`,
		},
		Query: "SELECT @@sql_mode",
		Expected: []sql.Row{
			{"some_mode"},
		},
	},

	{
		Name: "set unknown system variable",
		SetUpScript: []string{
			`set dne = "hello"`,
		},
		Query: "SELECT @@dne",
		Expected: []sql.Row{
			{"hello"},
		},
	},

	{
		Name: "set user var",
		SetUpScript: []string{
			`set @myvar = "hello"`,
		},
		Query: "SELECT @myvar",
		Expected: []sql.Row{
			{"hello"},
		},
	},
	{
		Name: "set user var, integer type",
		SetUpScript: []string{
			`set @myvar = 123`,
		},
		Query: "SELECT @myvar",
		Expected: []sql.Row{
			{123},
		},
	},
	{
		Name: "set user var, floating point",
		SetUpScript: []string{
			`set @myvar = 123.4`,
		},
		Query: "SELECT @myvar",
		Expected: []sql.Row{
			{123.4},
		},
	},
	{
		Name: "set user var and sys var in same statement",
		SetUpScript: []string{
			`set @myvar = 123.4, @@auto_increment_increment = 1234`,
		},
		Query: "SELECT @myvar, @@auto_increment_increment",
		Expected: []sql.Row{
			{123.4, 1234},
		},
	},
	{
		Name: "set sys var to user var",
		SetUpScript: []string{
			`set @myvar = 1234`,
			`set auto_increment_increment = @myvar`,
		},
		Query: "SELECT @myvar, @@auto_increment_increment",
		Expected: []sql.Row{
			{1234, 1234},
		},
	},
}

View Source

var VersionedQueries = []QueryTest{
	{
		Query: "SELECT *  FROM myhistorytable AS OF '2019-01-01' AS foo ORDER BY i",
		Expected: []sql.Row{
			{int64(1), "first row, 1"},
			{int64(2), "second row, 1"},
			{int64(3), "third row, 1"},
		},
	},
	{
		Query: "SELECT *  FROM myhistorytable AS OF '2019-01-02' foo ORDER BY i",
		Expected: []sql.Row{
			{int64(1), "first row, 2"},
			{int64(2), "second row, 2"},
			{int64(3), "third row, 2"},
		},
	},

	{
		Query: "SELECT *  FROM myhistorytable AS OF GREATEST('2019-01-02','2019-01-01','') foo ORDER BY i",
		Expected: []sql.Row{
			{int64(1), "first row, 2"},
			{int64(2), "second row, 2"},
			{int64(3), "third row, 2"},
		},
	},
	{
		Query: "SELECT *  FROM myhistorytable ORDER BY i",
		Expected: []sql.Row{
			{int64(1), "first row, 2"},
			{int64(2), "second row, 2"},
			{int64(3), "third row, 2"},
		},
	},
	{
		Query: "SHOW TABLES AS OF '2019-01-02' LIKE 'myhistorytable'",
		Expected: []sql.Row{
			{"myhistorytable"},
		},
	},
	{
		Query: "SHOW TABLES FROM mydb AS OF '2019-01-02' LIKE 'myhistorytable'",
		Expected: []sql.Row{
			{"myhistorytable"},
		},
	},
}

View Source

var VersionedViewTests = []QueryTest{
	{
		Query: "SELECT * FROM myview1 ORDER BY i",
		Expected: []sql.Row{
			sql.NewRow(int64(1), "first row, 2"),
			sql.NewRow(int64(2), "second row, 2"),
			sql.NewRow(int64(3), "third row, 2"),
		},
	},
	{
		Query: "SELECT t.* FROM myview1 AS t ORDER BY i",
		Expected: []sql.Row{
			sql.NewRow(int64(1), "first row, 2"),
			sql.NewRow(int64(2), "second row, 2"),
			sql.NewRow(int64(3), "third row, 2"),
		},
	},
	{
		Query: "SELECT t.i FROM myview1 AS t ORDER BY i",
		Expected: []sql.Row{
			sql.NewRow(int64(1)),
			sql.NewRow(int64(2)),
			sql.NewRow(int64(3)),
		},
	},
	{
		Query: "SELECT * FROM myview1 AS OF '2019-01-01' ORDER BY i",
		Expected: []sql.Row{
			sql.NewRow(int64(1), "first row, 1"),
			sql.NewRow(int64(2), "second row, 1"),
			sql.NewRow(int64(3), "third row, 1"),
		},
	},
	{
		Query: "SELECT * FROM myview2",
		Expected: []sql.Row{
			sql.NewRow(int64(1), "first row, 2"),
		},
	},
	{
		Query: "SELECT i FROM myview2",
		Expected: []sql.Row{
			sql.NewRow(int64(1)),
		},
	},
	{
		Query: "SELECT myview2.i FROM myview2",
		Expected: []sql.Row{
			sql.NewRow(int64(1)),
		},
	},
	{
		Query: "SELECT myview2.* FROM myview2",
		Expected: []sql.Row{
			sql.NewRow(int64(1), "first row, 2"),
		},
	},
	{
		Query: "SELECT t.* FROM myview2 as t",
		Expected: []sql.Row{
			sql.NewRow(int64(1), "first row, 2"),
		},
	},
	{
		Query: "SELECT t.i FROM myview2 as t",
		Expected: []sql.Row{
			sql.NewRow(int64(1)),
		},
	},
	{
		Query: "SELECT * FROM myview2 AS OF '2019-01-01'",
		Expected: []sql.Row{
			sql.NewRow(int64(1), "first row, 1"),
		},
	},

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

View Source

var ViewTests = []QueryTest{
	{
		Query: "SELECT * FROM myview ORDER BY i",
		Expected: []sql.Row{
			sql.NewRow(int64(1), "first row"),
			sql.NewRow(int64(2), "second row"),
			sql.NewRow(int64(3), "third row"),
		},
	},
	{
		Query: "SELECT myview.* FROM myview ORDER BY i",
		Expected: []sql.Row{
			sql.NewRow(int64(1), "first row"),
			sql.NewRow(int64(2), "second row"),
			sql.NewRow(int64(3), "third row"),
		},
	},
	{
		Query: "SELECT i FROM myview ORDER BY i",
		Expected: []sql.Row{
			sql.NewRow(int64(1)),
			sql.NewRow(int64(2)),
			sql.NewRow(int64(3)),
		},
	},
	{
		Query: "SELECT t.* FROM myview AS t ORDER BY i",
		Expected: []sql.Row{
			sql.NewRow(int64(1), "first row"),
			sql.NewRow(int64(2), "second row"),
			sql.NewRow(int64(3), "third row"),
		},
	},
	{
		Query: "SELECT t.i FROM myview AS t ORDER BY i",
		Expected: []sql.Row{
			sql.NewRow(int64(1)),
			sql.NewRow(int64(2)),
			sql.NewRow(int64(3)),
		},
	},
	{
		Query: "SELECT * FROM myview2",
		Expected: []sql.Row{
			sql.NewRow(int64(1), "first row"),
		},
	},
	{
		Query: "SELECT i FROM myview2",
		Expected: []sql.Row{
			sql.NewRow(int64(1)),
		},
	},
	{
		Query: "SELECT myview2.i FROM myview2",
		Expected: []sql.Row{
			sql.NewRow(int64(1)),
		},
	},
	{
		Query: "SELECT myview2.* FROM myview2",
		Expected: []sql.Row{
			sql.NewRow(int64(1), "first row"),
		},
	},
	{
		Query: "SELECT t.* FROM myview2 as t",
		Expected: []sql.Row{
			sql.NewRow(int64(1), "first row"),
		},
	},
	{
		Query: "SELECT t.i FROM myview2 as t",
		Expected: []sql.Row{
			sql.NewRow(int64(1)),
		},
	},

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

Functions ¶

func AssertErr ¶

func AssertErr(t *testing.T, e *sqle.Engine, harness Harness, query string, expectedErrKind *errors.Kind)

AssertErr asserts that the given query returns an error during its execution, optionally specifying a type of error.

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 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, harness Harness, 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 RunQuery ¶

func RunQuery(t *testing.T, e *sqle.Engine, harness Harness, query string)

RunQuery runs the query given and asserts that it doesn't result in an error.

func RunQueryTests ¶

func RunQueryTests(t *testing.T, harness Harness, queries []QueryTest)

Runs the query tests given after setting up the engine. Useful for testing out a smaller subset of queries during debugging.

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 TestColumnDefaults ¶

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

func TestCreateForeignKeys ¶

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

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 TestDropForeignKeys ¶

func TestDropForeignKeys(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, bindings map[string]sql.Expression)

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, bindings map[string]sql.Expression)

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 TestScript ¶

func TestScript(t *testing.T, harness Harness, script ScriptTest) bool

TestScript runs the test script given, making any assertions given

func TestScriptWithEngine ¶

func TestScriptWithEngine(t *testing.T, e *sqle.Engine, harness Harness, script ScriptTest)

TestScriptWithEngine runs the test script given with the engine provided.

func TestScripts ¶

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

func TestSessionSelectLimit ¶

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

func TestTracing ¶

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

func TestTriggerErrors ¶

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

func TestTriggers ¶

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

func TestTruncate ¶

func TestTruncate(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 TestVariableErrors ¶

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

func TestVariables ¶

func TestVariables(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)

func WidenRow ¶

func WidenRow(row sql.Row) sql.Row

See WidenRows

func WidenRows ¶

func WidenRows(rows []sql.Row) []sql.Row

For a variety of reasons, the widths of various primitive types can vary when passed through different SQL queries (and different database implementations). We may eventually decide that this undefined behavior is a problem, but for now it's mostly just an issue when comparing results in tests. To get around this, we widen every type to its widest value in actual and expected results.

Types ¶

type ForeignKeyHarness ¶

type ForeignKeyHarness interface {
	Harness
	// SupportsForeignKeys returns whether this harness should accept CREATE FOREIGN KEY statements as part of test
	// setup.
	SupportsForeignKeys() bool
}

ForeignKeyHarness is an extension to Harness that lets an integrator test their implementation with foreign keys. Integrator tables must implement sql.ForeignKeyAlterableTable and sql.ForeignKeyTable.

type GenericErrorQueryTest ¶

type GenericErrorQueryTest struct {
	Name     string
	Query    string
	Bindings map[string]sql.Expression
}

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 IndexDriverInitalizer ¶

type IndexDriverInitalizer func([]sql.Database) sql.IndexDriver

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 KeylessTableHarness ¶

type KeylessTableHarness interface {
	Harness
	// SupportsKeylessTables indicates integrator support for keyless tables.
	SupportsKeylessTables() bool
}

KeylessTableHarness is an extension to Harness that lets an integrator test their implementation with keyless tables.

type MemoryHarness ¶

type MemoryHarness struct {
	// contains filtered or unexported fields
}

func NewDefaultMemoryHarness ¶

func NewDefaultMemoryHarness() *MemoryHarness

func NewMemoryHarness ¶

func NewMemoryHarness(name string, parallelism int, numTablePartitions int, useNativeIndexes bool, indexDriverInitalizer IndexDriverInitalizer) *MemoryHarness

func (*MemoryHarness) IndexDriver ¶

func (m *MemoryHarness) IndexDriver(dbs []sql.Database) sql.IndexDriver

func (*MemoryHarness) NewContext ¶

func (m *MemoryHarness) NewContext() *sql.Context

func (*MemoryHarness) NewDatabase ¶

func (m *MemoryHarness) NewDatabase(name string) sql.Database

func (*MemoryHarness) NewTable ¶

func (m *MemoryHarness) NewTable(db sql.Database, name string, schema sql.Schema) (sql.Table, error)

func (*MemoryHarness) NewTableAsOf ¶

func (m *MemoryHarness) NewTableAsOf(db sql.VersionedDatabase, name string, schema sql.Schema, asOf interface{}) sql.Table

func (*MemoryHarness) Parallelism ¶

func (m *MemoryHarness) Parallelism() int

func (*MemoryHarness) SnapshotTable ¶

func (m *MemoryHarness) SnapshotTable(db sql.VersionedDatabase, name string, asOf interface{}) error

func (*MemoryHarness) SupportsForeignKeys ¶

func (m *MemoryHarness) SupportsForeignKeys() bool

func (*MemoryHarness) SupportsKeylessTables ¶

func (m *MemoryHarness) SupportsKeylessTables() bool

func (*MemoryHarness) SupportsNativeIndexCreation ¶

func (m *MemoryHarness) SupportsNativeIndexCreation() bool

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
	Bindings map[string]sql.Expression
}

type ScriptTest ¶

type ScriptTest struct {
	// Name of the script test
	Name string
	// The sql statements to execute as setup, in order. Results are not checked, but statements must not error.
	SetUpScript []string
	// The set of assertions to make after setup, in order
	Assertions []ScriptTestAssertion
	// For tests that make a single assertion, Query can be set for the single assertion
	Query string
	// For tests that make a single assertion, Expected can be set for the single assertion
	Expected []sql.Row
	// For tests that make a single assertion, ExpectedErr can be set for the expected error
	ExpectedErr *errors.Kind
}

type ScriptTestAssertion ¶

type ScriptTestAssertion struct {
	Query       string
	Expected    []sql.Row
	ExpectedErr *errors.Kind
}

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 SkippingMemoryHarness ¶

type SkippingMemoryHarness struct {
	MemoryHarness
}

func NewSkippingMemoryHarness ¶

func NewSkippingMemoryHarness() *SkippingMemoryHarness

func (SkippingMemoryHarness) SkipQueryTest ¶

func (s SkippingMemoryHarness) SkipQueryTest(query string) bool

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
	Bindings            map[string]sql.Expression
}

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

Directories ¶

Path	Synopsis
sqllogictest
harness

?	: This menu
/	: Search site
f or F	: Jump to
y or Y	: Canonical URL