pizzasql/pkg/executor/arrays_test.go

package executor

import (
	"errors"
	"strings"
	"testing"
	"time"

	"github.com/danfragoso/pizzasql-next/pkg/analyzer"
	"github.com/danfragoso/pizzasql-next/pkg/lexer"
	"github.com/danfragoso/pizzasql-next/pkg/parser"
	"github.com/danfragoso/pizzasql-next/pkg/pgerror"
	"github.com/danfragoso/pizzasql-next/pkg/storage"
)

func TestArrayColumnDDLAndRoundTrip(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)
	execMust(t, e, `CREATE TABLE items (
		id INTEGER PRIMARY KEY,
		tags TEXT[] NOT NULL,
		scores INTEGER[],
		flags BOOLEAN[],
		ratios DOUBLE PRECISION[]
	)`)
	execMust(t, e, `INSERT INTO items (id, tags, scores, flags, ratios)
		VALUES (1, '{a,b}', '{1,2}', '{t,f}', '{1.5,2.25}')`)
	execMust(t, e, `INSERT INTO items (id, tags, scores)
		VALUES (2, '{}', NULL)`)

	res := execMust(t, e, `SELECT id, tags, scores, flags, ratios FROM items ORDER BY id`)
	if got := res.Rows[0][1]; got != "{a,b}" {
		t.Fatalf("tags = %v, want {a,b}", got)
	}
	// Elements are canonicalized per declared type.
	if got := res.Rows[0][2]; got != "{1,2}" {
		t.Fatalf("scores = %v, want {1,2}", got)
	}
	if got := res.Rows[0][3]; got != "{t,f}" {
		t.Fatalf("flags = %v, want {t,f}", got)
	}
	if got := res.Rows[0][4]; got != "{1.5,2.25}" {
		t.Fatalf("ratios = %v, want {1.5,2.25}", got)
	}
	// NULL and the empty array stay distinguishable.
	if got := res.Rows[1][1]; got != "{}" {
		t.Fatalf("empty tags = %v, want {}", got)
	}
	if got := res.Rows[1][2]; got != nil {
		t.Fatalf("NULL scores = %v, want nil", got)
	}

	// Result metadata reports the declared array type.
	if got := res.ColumnTypes[1]; got != "TEXT[]" {
		t.Fatalf("tags column type = %q, want TEXT[]", got)
	}

	// The durable catalog keeps the array spelling, so sqlite_master and
	// PRAGMA table_info still render it.
	master := execMust(t, e, `SELECT sql FROM sqlite_master WHERE name = 'items'`)
	if sql, _ := master.Rows[0][0].(string); !strings.Contains(sql, "TEXT[]") {
		t.Fatalf("sqlite_master SQL = %q, want it to contain TEXT[]", sql)
	}
	info := execMust(t, e, `PRAGMA table_info(items)`)
	found := false
	for _, row := range info.Rows {
		if row[1] == "scores" {
			found = true
			if row[2] != "INTEGER[]" {
				t.Fatalf("PRAGMA table_info type = %v, want INTEGER[]", row[2])
			}
		}
	}
	if !found {
		t.Fatal("PRAGMA table_info did not report the scores column")
	}

	// A fresh executor over the same store sees the same bytes: no reload
	// conversion and no storage-format change.
	reloaded := newExec(schema, table)
	res = execMust(t, reloaded, `SELECT tags, scores, flags FROM items WHERE id = 1`)
	if res.Rows[0][0] != "{a,b}" || res.Rows[0][1] != "{1,2}" || res.Rows[0][2] != "{t,f}" {
		t.Fatalf("reloaded row = %v", res.Rows[0])
	}
}

func TestArrayCanonicalEncoding(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)
	execMust(t, e, `CREATE TABLE t (id INTEGER PRIMARY KEY, v TEXT[])`)

	for _, tc := range []struct{ sql, want string }{
		{`INSERT INTO t VALUES (1, '{ a , b }') RETURNING v`, "{a,b}"},
		{`INSERT INTO t VALUES (2, '{"a b","c,d"}') RETURNING v`, `{"a b","c,d"}`},
		{`INSERT INTO t VALUES (3, ARRAY['x', NULL, 'NULL']) RETURNING v`, `{x,NULL,"NULL"}`},
		{`INSERT INTO t VALUES (4, ARRAY[]::TEXT[]) RETURNING v`, "{}"},
		{`INSERT INTO t VALUES (5, '{"quote\"d", "back\\slash"}') RETURNING v`, `{"quote\"d","back\\slash"}`},
	} {
		res := execMust(t, e, tc.sql)
		if got := res.Rows[0][0]; got != tc.want {
			t.Errorf("%s = %v, want %s", tc.sql, got, tc.want)
		}
	}
}

func TestArrayConstructorAndCast(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)

	cases := []struct {
		sql  string
		want interface{}
	}{
		{`SELECT ARRAY[1, 2, 3]`, "{1,2,3}"},
		{`SELECT ARRAY['a', NULL]`, "{a,NULL}"},
		{`SELECT ARRAY[TRUE, FALSE]`, "{t,f}"},
		{`SELECT '{1,2,3}'::INTEGER[]`, "{1,2,3}"},
		{`SELECT CAST('{1,2}' AS INTEGER[])`, "{1,2}"},
		{`SELECT ARRAY['1','2']::INTEGER[]`, "{1,2}"},
		{`SELECT '{1.50,2}'::NUMERIC[]`, "{1.50,2}"},
		{`SELECT NULL::INTEGER[]`, nil},
	}
	for _, tc := range cases {
		res := execMust(t, e, tc.sql)
		if got := res.Rows[0][0]; got != tc.want {
			t.Errorf("%s = %#v, want %#v", tc.sql, got, tc.want)
		}
	}

	res := execMust(t, e, `SELECT ARRAY[1,2] AS a, '{x}'::TEXT[] AS b`)
	if res.ColumnTypes[0] != "INTEGER[]" || res.ColumnTypes[1] != "TEXT[]" {
		t.Fatalf("constructor types = %v, want [INTEGER[] TEXT[]]", res.ColumnTypes)
	}
}

func TestArraySubscriptAndLength(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)
	execMust(t, e, `CREATE TABLE t (id INTEGER PRIMARY KEY, nums INTEGER[], tags TEXT[], flags BOOLEAN[])`)
	execMust(t, e, `INSERT INTO t VALUES (1, '{10,20,30}', '{"x", NULL, "z"}', '{t,f}')`)
	execMust(t, e, `INSERT INTO t VALUES (2, '{}', '{}', '{}')`)

	cases := []struct {
		sql  string
		want interface{}
	}{
		{`SELECT nums[1] FROM t WHERE id = 1`, int64(10)},
		{`SELECT nums[3] FROM t WHERE id = 1`, int64(30)},
		{`SELECT nums[4] FROM t WHERE id = 1`, nil},
		{`SELECT nums[0] FROM t WHERE id = 1`, nil},
		{`SELECT tags[2] FROM t WHERE id = 1`, nil},
		{`SELECT tags[1] FROM t WHERE id = 1`, "x"},
		{`SELECT flags[1] FROM t WHERE id = 1`, true},
		{`SELECT flags[2] FROM t WHERE id = 1`, false},
		{`SELECT nums[1] FROM t WHERE id = 2`, nil},
		{`SELECT array_length(nums, 1) FROM t WHERE id = 1`, int64(3)},
		{`SELECT array_length(nums, 1) FROM t WHERE id = 2`, nil},
		{`SELECT array_length(nums, 2) FROM t WHERE id = 1`, nil},
	}
	for _, tc := range cases {
		res := execMust(t, e, tc.sql)
		if got := res.Rows[0][0]; got != tc.want {
			t.Errorf("%s = %#v, want %#v", tc.sql, got, tc.want)
		}
	}

	res := execMust(t, e, `SELECT nums[1] AS n FROM t WHERE id = 1`)
	if res.ColumnTypes[0] != "INTEGER" {
		t.Fatalf("subscript type = %q, want INTEGER", res.ColumnTypes[0])
	}
}

func TestAnyAllThreeValuedLogic(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)
	execMust(t, e, `CREATE TABLE t (id INTEGER PRIMARY KEY, n INTEGER, b BOOLEAN)`)
	execMust(t, e, `INSERT INTO t VALUES (1, 2, TRUE)`)

	cases := []struct {
		sql  string
		want interface{}
	}{
		// ANY: true if any comparison is true.
		{`SELECT 2 = ANY('{1,2,3}')`, true},
		{`SELECT 5 = ANY('{1,2,3}')`, false},
		// NULL elements: no true and a NULL comparison yields NULL.
		{`SELECT 5 = ANY('{1,NULL,3}')`, nil},
		{`SELECT 2 = ANY('{NULL,2}')`, true},
		// Empty array: ANY is false, ALL is true, even for a NULL left side.
		{`SELECT 5 = ANY('{}')`, false},
		{`SELECT NULL = ANY('{}')`, false},
		{`SELECT 5 <> ALL('{}')`, true},
		{`SELECT NULL <> ALL('{}')`, true},
		// ALL: false if any comparison is false, NULL if only NULLs remain.
		{`SELECT 2 <> ALL('{1,3,4}')`, true},
		{`SELECT 2 <> ALL('{1,2,4}')`, false},
		{`SELECT 2 <> ALL('{1,NULL}')`, nil},
		{`SELECT 3 > ALL('{1,2}')`, true},
		{`SELECT 1 >= ALL('{1,NULL}')`, nil},
		// A NULL left operand yields NULL for a non-empty array.
		{`SELECT NULL = ANY('{1,2}')`, nil},
		{`SELECT NULL <> ALL('{1,2}')`, nil},
		// Boolean elements compare as booleans, not as text.
		{`SELECT TRUE = ANY('{f,t}')`, true},
		{`SELECT FALSE = ANY('{f,t}')`, true},
		{`SELECT TRUE <> ALL('{f,t}')`, false},
		// Column-driven forms, including a NULL column.
		{`SELECT n = ANY('{1,2}') FROM t`, true},
		{`SELECT n <> ALL('{1,2}') FROM t`, false},
		{`SELECT b = ANY('{t}') FROM t`, true},
		{`SELECT id = ANY('{9,8}') FROM t`, false},
	}
	for _, tc := range cases {
		res := execMust(t, e, tc.sql)
		if got := res.Rows[0][0]; got != tc.want {
			t.Errorf("%s = %#v, want %#v", tc.sql, got, tc.want)
		}
	}

	if res := execMust(t, e, `SELECT n = ANY('{1,2}') AS hit FROM t`); res.ColumnTypes[0] != "BOOLEAN" {
		t.Fatalf("ANY result type = %q, want BOOLEAN", res.ColumnTypes[0])
	}
}

func TestAnyAllOverArrayColumn(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)
	execMust(t, e, `CREATE TABLE t (id INTEGER PRIMARY KEY, allowed TEXT[])`)
	execMust(t, e, `INSERT INTO t VALUES (1, '{red,green}'), (2, '{blue}'), (3, NULL)`)

	for _, tc := range []struct {
		sql  string
		want int
	}{
		{`SELECT count(*) FROM t WHERE 'red' = ANY(allowed)`, 1},
		{`SELECT count(*) FROM t WHERE 'purple' = ANY(allowed)`, 0},
		{`SELECT count(*) FROM t WHERE 'purple' <> ALL(allowed)`, 2},
	} {
		res := execMust(t, e, tc.sql)
		if got := res.Rows[0][0]; got != int64(tc.want) {
			t.Errorf("%s = %v, want %d", tc.sql, got, tc.want)
		}
	}
}

func TestUnnestAsTableFunction(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)

	res := execMust(t, e, `SELECT * FROM unnest('{10,20,30}'::INTEGER[])`)
	if res.RowCount != 3 {
		t.Fatalf("unnest rows = %d, want 3", res.RowCount)
	}
	if res.Columns[0] != "unnest" {
		t.Fatalf("unnest column = %q, want unnest", res.Columns[0])
	}
	if res.Rows[0][0] != int64(10) || res.Rows[2][0] != int64(30) {
		t.Fatalf("unnest values = %v", res.Rows)
	}
	if res.ColumnTypes[0] != "INTEGER" {
		t.Fatalf("unnest column type = %q, want INTEGER", res.ColumnTypes[0])
	}

	res = execMust(t, e, `SELECT u.unnest FROM unnest(ARRAY['a', 'b']) AS u WHERE u.unnest <> 'a'`)
	if res.RowCount != 1 || res.Rows[0][0] != "b" {
		t.Fatalf("unnest alias rows = %v", res.Rows)
	}

	res = execMust(t, e, `SELECT * FROM unnest(NULL::TEXT[])`)
	if res.RowCount != 0 {
		t.Fatalf("unnest(NULL) rows = %d, want 0", res.RowCount)
	}

	if _, err := execSQL(e, `SELECT unnest('{1,2}'::INTEGER[])`); err == nil {
		t.Fatal("scalar unnest should be rejected")
	}
}

func TestArrayDefaultsAndAlterTable(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)
	execMust(t, e, `CREATE TABLE t (id INTEGER PRIMARY KEY, tags TEXT[] DEFAULT ARRAY['a'])`)
	execMust(t, e, `INSERT INTO t (id) VALUES (1)`)
	res := execMust(t, e, `SELECT tags FROM t WHERE id = 1`)
	if res.Rows[0][0] != "{a}" {
		t.Fatalf("default tags = %v, want {a}", res.Rows[0][0])
	}

	execMust(t, e, `ALTER TABLE t ADD COLUMN nums INTEGER[] DEFAULT '{1, 2}'`)
	res = execMust(t, e, `SELECT nums FROM t WHERE id = 1`)
	if res.Rows[0][0] != "{1,2}" {
		t.Fatalf("added column default = %v, want {1,2}", res.Rows[0][0])
	}
	execMust(t, e, `INSERT INTO t (id, nums) VALUES (2, '{3}')`)
	res = execMust(t, e, `SELECT nums FROM t WHERE id = 2`)
	if res.Rows[0][0] != "{3}" {
		t.Fatalf("explicit array = %v, want {3}", res.Rows[0][0])
	}
}

func TestArrayInCompositePrimaryKey(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)
	execMust(t, e, `CREATE TABLE t (id INTEGER, tags TEXT[], PRIMARY KEY (id, tags))`)
	execMust(t, e, `INSERT INTO t VALUES (1, '{a,b}')`)
	execMust(t, e, `INSERT INTO t VALUES (1, '{c}')`)
	res := execMust(t, e, `SELECT count(*) FROM t`)
	if res.Rows[0][0] != int64(2) {
		t.Fatalf("rows = %v, want 2", res.Rows[0][0])
	}
	if _, err := execSQL(e, `INSERT INTO t VALUES (1, '{a, b}')`); err == nil {
		t.Fatal("duplicate composite key with an equivalent array should conflict")
	}

	reloaded := newExec(schema, table)
	res = execMust(t, reloaded, `SELECT tags FROM t WHERE id = 1 AND tags = '{a,b}'`)
	if res.RowCount != 1 || res.Rows[0][0] != "{a,b}" {
		t.Fatalf("reloaded composite-key lookup = %v", res.Rows)
	}
}

func TestArrayRejectedShapes(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)
	execMust(t, e, `CREATE TABLE t (id INTEGER PRIMARY KEY, v TEXT[])`)

	cases := []struct {
		sql         string
		unsupported bool
	}{
		{`INSERT INTO t VALUES (1, '{{1,2},{3,4}}')`, true},
		{`SELECT '{{1,2},{3,4}}'::INTEGER[]`, true},
		{`SELECT 1 = ANY('not an array')`, false},
		{`SELECT 1 = ANY(1)`, false},
		{`INSERT INTO t VALUES (2, '{1,2')`, false},
		{`INSERT INTO t VALUES (3, 5)`, false},
	}
	for _, tc := range cases {
		_, err := execSQL(e, tc.sql)
		if err == nil {
			t.Errorf("%s succeeded, want an error", tc.sql)
			continue
		}
		if tc.unsupported && !errors.Is(err, pgerror.ErrFeatureNotSupported) {
			t.Errorf("%s error = %v, want ErrFeatureNotSupported", tc.sql, err)
		}
		if !tc.unsupported && !errors.Is(err, pgerror.ErrInvalidText) {
			t.Errorf("%s error = %v, want ErrInvalidText", tc.sql, err)
		}
	}
}

func TestArrayUpdateAndReturning(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)
	execMust(t, e, `CREATE TABLE t (id INTEGER PRIMARY KEY, tags TEXT[])`)
	execMust(t, e, `INSERT INTO t VALUES (1, '{a}')`)
	res := execMust(t, e, `UPDATE t SET tags = '{b, c}' WHERE id = 1 RETURNING tags`)
	if res.Rows[0][0] != "{b,c}" {
		t.Fatalf("UPDATE RETURNING tags = %v, want {b,c}", res.Rows[0][0])
	}
	res = execMust(t, e, `SELECT tags FROM t WHERE id = 1`)
	if res.Rows[0][0] != "{b,c}" {
		t.Fatalf("stored tags = %v, want {b,c}", res.Rows[0][0])
	}
}

// The array value is a plain string in the row codec, so a second decoder sees
// exactly the bytes the first one wrote. A freshly constructed schema and table
// manager pair over the same store stands in for a process restart: no
// converter runs, and the durable catalog plus row bytes are read as written.
func TestArrayStorageReloadIsStable(t *testing.T) {
	pool, schema, table := newTestDB(t)
	e := newExec(schema, table)
	execMust(t, e, `CREATE TABLE t (id INTEGER PRIMARY KEY, v TEXT[], n INTEGER[])`)
	execMust(t, e, `INSERT INTO t VALUES (1, ARRAY['x','y'], '{1,2}')`)

	session := storage.NewSession(schema, table)
	row, err := session.GetByPK("t", "1")
	if err != nil {
		t.Fatalf("GetByPK: %v", err)
	}
	if row["v"] != "{x,y}" {
		t.Fatalf("stored value = %#v, want {x,y}", row["v"])
	}

	reloadedSchema := storage.NewSchemaManager(pool, "testdb")
	reloadedTable := storage.NewTableManager(pool, reloadedSchema, "testdb")
	reloaded := newExec(reloadedSchema, reloadedTable)
	res := execMust(t, reloaded, `SELECT v, n FROM t WHERE id = 1`)
	if res.Rows[0][0] != "{x,y}" || res.Rows[0][1] != "{1,2}" {
		t.Fatalf("reloaded row = %v, want [{x,y} {1,2}]", res.Rows[0])
	}
	if res.ColumnTypes[0] != "TEXT[]" || res.ColumnTypes[1] != "INTEGER[]" {
		t.Fatalf("reloaded types = %v, want [TEXT[] INTEGER[]]", res.ColumnTypes)
	}
	// A second write through the reloaded session must encode identically.
	execMust(t, reloaded, `INSERT INTO t (id, v) VALUES (2, '{x, y}')`)
	res = execMust(t, reloaded, `SELECT v = '{x,y}' FROM t WHERE id = 2`)
	if res.Rows[0][0] != true {
		t.Fatalf("canonical reloaded write did not compare equal: %v", res.Rows[0][0])
	}
}

// A boolean element must be a Go bool in every value context. Left as the
// canonical "t"/"f" text, toBool("f") is true and an equality against TRUE is
// false, so WHERE/NOT/=/ANY are all checked.
func TestBooleanSubscriptPredicates(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)
	execMust(t, e, `CREATE TABLE t (id INTEGER PRIMARY KEY, flags BOOLEAN[], nums INTEGER[], ratios DOUBLE PRECISION[], amounts NUMERIC[])`)
	execMust(t, e, `INSERT INTO t VALUES
		(1, '{t,f}', '{10,20}', '{1.5,2.5}', '{12345678901234567890,1.10}'),
		(2, '{f,t}', '{30,40}', '{3.5}', '{2.50}'),
		(3, NULL, NULL, NULL, NULL)`)

	cases := []struct {
		sql  string
		want interface{}
	}{
		{`SELECT count(*) FROM t WHERE flags[1]`, int64(1)},
		{`SELECT count(*) FROM t WHERE NOT flags[1]`, int64(1)},
		{`SELECT count(*) FROM t WHERE flags[1] = TRUE`, int64(1)},
		{`SELECT count(*) FROM t WHERE flags[1] = FALSE`, int64(1)},
		{`SELECT count(*) FROM t WHERE flags[1] = ANY('{true}')`, int64(1)},
		{`SELECT count(*) FROM t WHERE flags[1] = ANY('{false}')`, int64(1)},
		{`SELECT flags[1] = TRUE FROM t WHERE id = 2`, false},
		{`SELECT flags[1] FROM t WHERE id = 2`, false},
		// Integer and real elements keep their Go carrier, so the HTTP layer
		// emits a JSON number and the wire metadata matches.
		{`SELECT nums[2] FROM t WHERE id = 1`, int64(20)},
		{`SELECT ratios[1] FROM t WHERE id = 1`, 1.5},
		// NUMERIC keeps its exact text instead of a float round-trip.
		{`SELECT amounts[1] FROM t WHERE id = 1`, "12345678901234567890"},
		{`SELECT amounts[2] FROM t WHERE id = 1`, "1.10"},
		{`SELECT count(*) FROM t WHERE amounts[1] = '12345678901234567890'`, int64(1)},
		{`SELECT count(*) FROM t WHERE amounts[2] > 1`, int64(1)},
	}
	for _, tc := range cases {
		res := execMust(t, e, tc.sql)
		if got := res.Rows[0][0]; got != tc.want {
			t.Errorf("%s = %#v, want %#v", tc.sql, got, tc.want)
		}
	}

	res := execMust(t, e, `SELECT nums[1] AS n, ratios[1] AS r, flags[1] AS f, amounts[1] AS a FROM t WHERE id = 1`)
	wantTypes := []string{"INTEGER", "DOUBLE", "BOOLEAN", "NUMERIC"}
	for i, want := range wantTypes {
		if res.ColumnTypes[i] != want {
			t.Errorf("column %d type = %q, want %q", i, res.ColumnTypes[i], want)
		}
	}
}

// A text literal is canonicalized against the array-typed side, so equivalent
// spellings compare equal to the stored canonical text.
func TestArrayLiteralComparisonCanonicalization(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)
	execMust(t, e, `CREATE TABLE t (id INTEGER PRIMARY KEY, tags TEXT[], nums INTEGER[], amounts NUMERIC[])`)
	execMust(t, e, `INSERT INTO t VALUES (1, '{a,b}', '{3,4}', '{1.10,2}')`)

	cases := []struct {
		sql  string
		want interface{}
	}{
		{`SELECT count(*) FROM t WHERE tags = '{a, b}'`, int64(1)},
		{`SELECT count(*) FROM t WHERE tags = '{"a","b"}'`, int64(1)},
		{`SELECT count(*) FROM t WHERE '{a, b}' = tags`, int64(1)},
		{`SELECT count(*) FROM t WHERE tags <> '{a, c}'`, int64(1)},
		{`SELECT count(*) FROM t WHERE tags IN ('{a, b}', '{x}')`, int64(1)},
		{`SELECT count(*) FROM t WHERE tags IS NOT DISTINCT FROM '{a, b}'`, int64(1)},
		{`SELECT count(*) FROM t WHERE nums = '{3, 4}'`, int64(1)},
		{`SELECT count(*) FROM t WHERE nums <> '{3,5}'`, int64(1)},
		{`SELECT count(*) FROM t WHERE amounts = '{1.10,2}'`, int64(1)},
		{`SELECT tags = '{a, b}' FROM t`, true},
		{`SELECT '{a, b}' = tags FROM t`, true},
	}
	for _, tc := range cases {
		res := execMust(t, e, tc.sql)
		if got := res.Rows[0][0]; got != tc.want {
			t.Errorf("%s = %#v, want %#v", tc.sql, got, tc.want)
		}
	}

	// A text value that is not array text is an invalid array literal, not a
	// silently false comparison.
	if _, err := execSQL(e, `SELECT count(*) FROM t WHERE tags = 'not-an-array'`); !errors.Is(err, pgerror.ErrInvalidText) {
		t.Fatalf("malformed comparison literal error = %v, want ErrInvalidText", err)
	}
}

// NUMERIC array elements are validated but never rewritten through float64, so
// big values and trailing zeros survive storage, reload, and casts.
func TestNumericArrayPrecision(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)
	execMust(t, e, `CREATE TABLE t (id INTEGER PRIMARY KEY, amounts NUMERIC[])`)
	execMust(t, e, `INSERT INTO t VALUES (1, '{12345678901234567890,1.10,1e+3}')`)

	res := execMust(t, e, `SELECT amounts FROM t WHERE id = 1`)
	if got := res.Rows[0][0]; got != "{12345678901234567890,1.10,1e+3}" {
		t.Fatalf("numeric array = %v, want exact text", got)
	}
	if res.ColumnTypes[0] != "NUMERIC[]" {
		t.Fatalf("numeric array type = %q, want NUMERIC[]", res.ColumnTypes[0])
	}

	reloaded := newExec(schema, table)
	res = execMust(t, reloaded, `SELECT amounts[1], amounts[2] FROM t WHERE id = 1`)
	if res.Rows[0][0] != "12345678901234567890" || res.Rows[0][1] != "1.10" {
		t.Fatalf("reloaded numeric elements = %v", res.Rows[0])
	}

	res = execMust(t, e, `SELECT '{1.10}'::NUMERIC[] AS v`)
	if res.Rows[0][0] != "{1.10}" {
		t.Fatalf("numeric cast = %v, want {1.10}", res.Rows[0][0])
	}
	if res.ColumnTypes[0] != "NUMERIC[]" {
		t.Fatalf("numeric cast type = %q, want NUMERIC[]", res.ColumnTypes[0])
	}

	// Whole-array equality is numeric-aware for NUMERIC[]: spelling differences
	// (1.10 vs 1.1, 1e+3 vs 1000) compare equal, while a changed digit does not.
	cases := []struct {
		sql  string
		want interface{}
	}{
		{`SELECT count(*) FROM t WHERE amounts = '{12345678901234567890,1.10,1e+3}'`, int64(1)},
		{`SELECT count(*) FROM t WHERE amounts = '{12345678901234567890,1.1,1000}'`, int64(1)},
		{`SELECT count(*) FROM t WHERE amounts = '{12345678901234567891,1.10,1e+3}'`, int64(0)},
		{`SELECT count(*) FROM t WHERE amounts <> '{12345678901234567890,1.1,1000}'`, int64(0)},
		{`SELECT amounts = '{12345678901234567890,1.1,1000}' FROM t`, true},
		{`SELECT amounts IS DISTINCT FROM '{12345678901234567890,1.1,1000}' FROM t`, false},
		{`SELECT '{1.10}'::NUMERIC[] = '{1.1}'::NUMERIC[]`, true},
		{`SELECT '{1.10}'::NUMERIC[] <> '{1.1}'::NUMERIC[]`, false},
		{`SELECT '{1.10,2.50}'::NUMERIC[] = '{1.1,2.5}'::NUMERIC[]`, true},
		// Element comparison is exact too, so a float64 rounding cannot make two
		// adjacent 20-digit values equal.
		{`SELECT count(*) FROM t WHERE amounts[1] = '12345678901234567890'`, int64(1)},
		{`SELECT count(*) FROM t WHERE amounts[1] = '12345678901234567891'`, int64(0)},
		{`SELECT count(*) FROM t WHERE amounts[2] = '1.1'`, int64(1)},
		{`SELECT count(*) FROM t WHERE amounts[2] = '1.10'`, int64(1)},
		{`SELECT count(*) FROM t WHERE amounts[2] = ANY('{1.1}')`, int64(1)},
		{`SELECT count(*) FROM t WHERE amounts[2] = ANY('{1.11}')`, int64(0)},
		{`SELECT count(*) FROM t WHERE amounts IN ('{12345678901234567890,1.1,1000}')`, int64(1)},
	}
	for _, tc := range cases {
		res := execMust(t, e, tc.sql)
		if got := res.Rows[0][0]; got != tc.want {
			t.Errorf("%s = %#v, want %#v", tc.sql, got, tc.want)
		}
	}
}

// NUMERIC element text is trimmed and a leading + is normalized; non-decimal
// spellings ParseFloat would accept (hex, NaN, Inf) are rejected.
func TestNumericArrayValidation(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)

	for _, tc := range []struct{ sql, want string }{
		{`SELECT '{" 1.10 "}'::NUMERIC[]`, "{1.10}"},
		{`SELECT '{+1.10}'::NUMERIC[]`, "{1.10}"},
		{`SELECT '{-1.10,+.5}'::NUMERIC[]`, "{-1.10,.5}"},
		{`SELECT '{1e+3,2E-2}'::NUMERIC[]`, "{1e+3,2E-2}"},
	} {
		res := execMust(t, e, tc.sql)
		if got := res.Rows[0][0]; got != tc.want {
			t.Errorf("%s = %v, want %s", tc.sql, got, tc.want)
		}
	}

	for _, sql := range []string{
		`SELECT '{0x1p4}'::NUMERIC[]`,
		`SELECT '{NaN}'::NUMERIC[]`,
		`SELECT '{Inf}'::NUMERIC[]`,
		`SELECT '{Infinity}'::NUMERIC[]`,
		`SELECT '{1.2.3}'::NUMERIC[]`,
		`SELECT '{1e}'::NUMERIC[]`,
		`SELECT '{+}'::NUMERIC[]`,
	} {
		if _, err := execSQL(e, sql); !errors.Is(err, pgerror.ErrInvalidText) {
			t.Errorf("%s error = %v, want ErrInvalidText", sql, err)
		}
	}
}

func TestArrayIndexTypeValidation(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)
	execMust(t, e, `CREATE TABLE t (id INTEGER PRIMARY KEY, nums INTEGER[])`)
	execMust(t, e, `INSERT INTO t VALUES (1, '{10,20}')`)

	for _, sql := range []string{
		`SELECT nums[1.5] FROM t`,
		`SELECT nums[TRUE] FROM t`,
		`SELECT nums['x'] FROM t`,
		`SELECT array_length(nums, 1.5) FROM t`,
		`SELECT array_length(nums, TRUE) FROM t`,
		`SELECT array_length(nums, 'x') FROM t`,
	} {
		_, err := execSQL(e, sql)
		var analysisErr *analyzer.AnalysisError
		if !errors.Is(err, pgerror.ErrDatatypeMismatch) && !(errors.As(err, &analysisErr) && analysisErr.Type == analyzer.ErrTypeMismatch) {
			t.Errorf("%s error = %v, want a datatype mismatch", sql, err)
		}
	}

	// NULL subscripts and dimensions still yield NULL.
	for _, sql := range []string{
		`SELECT nums[NULL] FROM t`,
		`SELECT array_length(nums, NULL) FROM t`,
	} {
		res := execMust(t, e, sql)
		if res.Rows[0][0] != nil {
			t.Errorf("%s = %#v, want nil", sql, res.Rows[0][0])
		}
	}
}

func TestMalformedStoredBooleanArrayPropagates(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)
	execMust(t, e, `CREATE TABLE bad (id INTEGER PRIMARY KEY, b BOOLEAN[])`)
	// Write around the executor's normalization to model a corrupted or
	// hand-authored catalog value.
	session := storage.NewSession(schema, table)
	if _, err := session.InsertWithRowID("bad", storage.Row{"id": int64(1), "b": "{t,falsy}"}); err != nil {
		t.Fatalf("seed malformed row: %v", err)
	}

	if _, err := execSQL(e, `SELECT b[2] FROM bad`); !errors.Is(err, pgerror.ErrInvalidText) {
		t.Fatalf("subscript error = %v, want ErrInvalidText", err)
	}
	// unnest() decodes through the same helper, so it must not coerce the
	// element to false.
	element := "falsy"
	if value, err := arrayElementValue(&element, "BOOLEAN"); !errors.Is(err, pgerror.ErrInvalidText) || value != nil {
		t.Fatalf("arrayElementValue = %#v, %v; want ErrInvalidText", value, err)
	}
}

func TestArrayEmptyElementRejected(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)
	for _, sql := range []string{
		`SELECT '{,a}'::TEXT[]`,
		`SELECT '{a,,b}'::TEXT[]`,
		`SELECT '{a,}'::TEXT[]`,
	} {
		if _, err := execSQL(e, sql); !errors.Is(err, pgerror.ErrInvalidText) {
			t.Errorf("%s error = %v, want ErrInvalidText", sql, err)
		}
	}
	// A quoted empty element is a valid empty string.
	res := execMust(t, e, `SELECT '{""}'::TEXT[]`)
	if res.Rows[0][0] != `{""}` {
		t.Fatalf("quoted empty element = %v", res.Rows[0][0])
	}
}

func TestMultidimensionalConstructorRejected(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)
	for _, sql := range []string{
		`SELECT ARRAY[[1,2],[3,4]]`,
		`SELECT ARRAY[[1,2]]`,
	} {
		if _, err := execSQL(e, sql); !errors.Is(err, pgerror.ErrFeatureNotSupported) {
			t.Errorf("%s error = %v, want ErrFeatureNotSupported", sql, err)
		}
	}
}

// The parsed-array cache is keyed by AST node and checked against the evaluated
// text, so a row-dependent array is re-parsed when its value changes.
func TestArrayParseCacheTracksRowValues(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)
	execMust(t, e, `CREATE TABLE t (id INTEGER PRIMARY KEY, nums INTEGER[])`)
	execMust(t, e, `INSERT INTO t VALUES (1, '{1}'), (2, '{2}')`)

	res := execMust(t, e, `SELECT count(*) FROM t WHERE 1 = ANY(nums)`)
	if res.Rows[0][0] != int64(1) {
		t.Fatalf("1 = ANY(nums) count = %v, want 1", res.Rows[0][0])
	}
	res = execMust(t, e, `SELECT count(*) FROM t WHERE 2 = ANY(nums)`)
	if res.Rows[0][0] != int64(1) {
		t.Fatalf("2 = ANY(nums) count = %v, want 1", res.Rows[0][0])
	}
}

// The lexer reads a bracket after an expression as a subscript, so the SQLite
// bracket-alias shapes fail instead of silently aliasing. This pins the chosen
// reading at execution time.
func TestBracketAliasShapesFailAtExecution(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)
	execMust(t, e, `CREATE TABLE t (id INTEGER PRIMARY KEY)`)

	if _, err := execSQL(e, `SELECT count(*) [cnt] FROM t`); err == nil {
		t.Fatal("count(*) [cnt] was accepted as an alias")
	}
	if _, err := execSQL(e, `SELECT 1 [x]`); err == nil {
		t.Fatal("1 [x] was accepted as an alias")
	}
	if _, err := execSQL(e, `SELECT * FROM t [a]`); err == nil {
		t.Fatal("FROM t [a] was accepted as an alias")
	}
}

// The array cache is keyed by AST node and validated by text: a repeated
// evaluation with the same value reuses the parsed slice, while a changed value
// is parsed again.
func TestParsedArrayMemoizesPerExpression(t *testing.T) {
	e := &Executor{arrayCache: make(map[parser.Expr]*arrayParseEntry)}
	expr := &parser.LiteralExpr{Type: lexer.TokenString, Value: "{1,2}"}

	first, err := e.parsedArray(expr, "{1,2}")
	if err != nil || len(first) != 2 {
		t.Fatalf("first parse = %v, %v", first, err)
	}
	second, err := e.parsedArray(expr, "{1,2}")
	if err != nil || len(second) != 2 {
		t.Fatalf("second parse = %v, %v", second, err)
	}
	if &first[0] != &second[0] {
		t.Fatal("identical array text was parsed twice")
	}

	third, err := e.parsedArray(expr, "{3}")
	if err != nil || len(third) != 1 || *third[0] != "3" {
		t.Fatalf("changed value = %v, %v", third, err)
	}

	// A nil cache (evaluation outside Execute) still parses.
	bare := &Executor{}
	if values, err := bare.parsedArray(expr, "{4}"); err != nil || len(values) != 1 {
		t.Fatalf("uncached parse = %v, %v", values, err)
	}
}

// A constant array operand is evaluated once per statement; a column-dependent
// operand is evaluated for every row so changed values are never reused.
func TestArrayOperandMemoizesConstantExpressions(t *testing.T) {
	e := &Executor{arrayValueCache: make(map[parser.Expr]interface{})}
	constant := &parser.CastExpr{
		Expr: &parser.LiteralExpr{Type: lexer.TokenString, Value: "{1,2}"},
		Type: parser.DataType{Name: "INTEGER", Array: true},
	}
	calls := 0
	evaluate := func() (interface{}, error) {
		calls++
		return "{1,2}", nil
	}
	for i := 0; i < 5; i++ {
		if _, err := e.arrayOperand(constant, evaluate); err != nil {
			t.Fatal(err)
		}
	}
	if calls != 1 {
		t.Fatalf("constant operand evaluated %d times, want 1", calls)
	}

	calls = 0
	column := &parser.ColumnRef{Column: "nums"}
	for i := 0; i < 3; i++ {
		if _, err := e.arrayOperand(column, evaluate); err != nil {
			t.Fatal(err)
		}
	}
	if calls != 3 {
		t.Fatalf("column operand evaluated %d times, want 3", calls)
	}
}

// Column typing survives a derived table: the outer statement must decode
// boolean subscripts instead of treating "t"/"f" as text.
func TestDerivedTableArrayTyping(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)
	execMust(t, e, `CREATE TABLE t (id INTEGER PRIMARY KEY, flags BOOLEAN[], tags TEXT[])`)
	execMust(t, e, `INSERT INTO t VALUES (1, '{t,f}', '{a,b}'), (2, '{f,t}', '{c}')`)

	cases := []struct {
		sql  string
		want interface{}
	}{
		{`SELECT count(*) FROM (SELECT flags FROM t) x WHERE flags[1]`, int64(1)},
		{`SELECT count(*) FROM (SELECT flags FROM t) x WHERE NOT flags[1]`, int64(1)},
		{`SELECT count(*) FROM (SELECT flags FROM t) x WHERE flags[1] = TRUE`, int64(1)},
		{`SELECT count(*) FROM (SELECT flags FROM t) x WHERE flags[1] = ANY('{true}')`, int64(1)},
		{`SELECT count(*) FROM (SELECT tags FROM t) x WHERE tags = '{a, b}'`, int64(1)},
		{`SELECT count(*) FROM (SELECT tags FROM t) x WHERE tags = '{"a","b"}'`, int64(1)},
	}
	for _, tc := range cases {
		res := execMust(t, e, tc.sql)
		if got := res.Rows[0][0]; got != tc.want {
			t.Errorf("%s = %#v, want %#v", tc.sql, got, tc.want)
		}
	}
}

// Column typing survives a CTE, including NOT over a boolean element.
func TestCTEArrayTyping(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)
	execMust(t, e, `CREATE TABLE t (id INTEGER PRIMARY KEY, flags BOOLEAN[], nums INTEGER[])`)
	execMust(t, e, `INSERT INTO t VALUES (1, '{t,f}', '{10,20}'), (2, '{f,t}', '{30,40}')`)

	cases := []struct {
		sql  string
		want interface{}
	}{
		{`WITH c AS (SELECT flags FROM t) SELECT count(*) FROM c WHERE flags[1]`, int64(1)},
		{`WITH c AS (SELECT flags FROM t) SELECT count(*) FROM c WHERE NOT flags[1]`, int64(1)},
		{`WITH c AS (SELECT flags FROM t) SELECT count(*) FROM c WHERE flags[1] = ANY('{true}')`, int64(1)},
		{`WITH c AS (SELECT nums FROM t) SELECT count(*) FROM c WHERE nums[1] = 10`, int64(1)},
		{`WITH c AS (SELECT nums FROM t) SELECT count(*) FROM c WHERE nums = '{10,20}'`, int64(1)},
		{`WITH c AS (SELECT nums FROM t) SELECT count(*) FROM c WHERE nums = '{10, 20}'`, int64(1)},
	}
	for _, tc := range cases {
		res := execMust(t, e, tc.sql)
		if got := res.Rows[0][0]; got != tc.want {
			t.Errorf("%s = %#v, want %#v", tc.sql, got, tc.want)
		}
	}
}

// A materialized CTE shadows a real table with the same name. Its own column
// types must win; reading the shadowed table's schema would decode INTEGER[]
// elements as text or report a spurious invalid-array error.
func TestCTEShadowingRealTableUsesCTETypes(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)
	execMust(t, e, `CREATE TABLE shadow (tags INTEGER[], flags TEXT[])`)
	execMust(t, e, `INSERT INTO shadow VALUES ('{1}', '{"not a bool"}')`)
	execMust(t, e, `CREATE TABLE docs (id INTEGER PRIMARY KEY, tags TEXT[], flags BOOLEAN[])`)
	execMust(t, e, `INSERT INTO docs VALUES (1, '{a,b}', '{f}')`)

	// Boolean NOT only flips when the CTE's BOOLEAN[] type is used.
	res := execMust(t, e, `WITH shadow AS (SELECT flags FROM docs) SELECT count(*) FROM shadow WHERE NOT flags[1]`)
	if res.Rows[0][0] != int64(1) {
		t.Fatalf("CTE boolean shadow count = %v, want 1", res.Rows[0][0])
	}
	// TEXT[] element access accepts 'a'; the shadowed INTEGER[] type would fail.
	res = execMust(t, e, `WITH shadow AS (SELECT tags FROM docs) SELECT count(*) FROM shadow WHERE tags[1] = 'a'`)
	if res.Rows[0][0] != int64(1) {
		t.Fatalf("CTE text shadow count = %v, want 1", res.Rows[0][0])
	}
	// Comparison canonicalization also uses the CTE's type.
	res = execMust(t, e, `WITH shadow AS (SELECT tags FROM docs) SELECT count(*) FROM shadow WHERE tags = '{a, b}'`)
	if res.Rows[0][0] != int64(1) {
		t.Fatalf("CTE comparison shadow count = %v, want 1", res.Rows[0][0])
	}
}

// The deadlock fix must hold for a locked in-transaction scan that evaluates an
// array subscript: a SELECT over the table followed by an UPDATE must not
// re-enter the session lock.
func TestArraySubscriptInTransactionScan(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)
	execMust(t, e, `CREATE TABLE t (id INTEGER PRIMARY KEY, flags BOOLEAN[], n INTEGER)`)
	execMust(t, e, `INSERT INTO t VALUES (1, '{t,f}', 0), (2, '{f,t}', 0)`)

	execMust(t, e, `BEGIN`)
	runWithTimeout(t, 5*time.Second, func() {
		res := execMust(t, e, `SELECT count(*) FROM t WHERE flags[1]`)
		if res.Rows[0][0] != int64(1) {
			t.Errorf("in-transaction count = %v, want 1", res.Rows[0][0])
		}
	})
	runWithTimeout(t, 5*time.Second, func() {
		execMust(t, e, `UPDATE t SET n = n + 1 WHERE flags[1]`)
	})
	execMust(t, e, `COMMIT`)

	res := execMust(t, e, `SELECT n FROM t ORDER BY id`)
	if res.Rows[0][0] != int64(1) || res.Rows[1][0] != int64(0) {
		t.Fatalf("post-commit values = %v", res.Rows)
	}
}

// canonicalArrayValue memoizes a constant comparison literal per statement and
// still re-canonicalizes changed text or a different target type.
func TestCanonicalArrayValueMemoizes(t *testing.T) {
	e := &Executor{arrayCanonicalCache: make(map[parser.Expr]*arrayCanonicalEntry)}
	e.pushTypeScope(func(_, column string) (string, bool) {
		if strings.EqualFold(column, "tags") {
			return "TEXT[]", true
		}
		return "", false
	})
	expr := &parser.ColumnRef{Column: "tags"}

	first, err := e.canonicalArrayValue(expr, "{a, b}")
	if err != nil || first != "{a,b}" {
		t.Fatalf("canonical = %v, %v", first, err)
	}
	if _, ok := e.arrayCanonicalCache[expr]; !ok {
		t.Fatal("canonical literal was not cached")
	}
	second, err := e.canonicalArrayValue(expr, "{a, b}")
	if err != nil || second != "{a,b}" {
		t.Fatalf("cached canonical = %v, %v", second, err)
	}
	changed, err := e.canonicalArrayValue(expr, "{c}")
	if err != nil || changed != "{c}" {
		t.Fatalf("changed canonical = %v, %v", changed, err)
	}

	if _, err := e.canonicalArrayValue(expr, "not-an-array"); !errors.Is(err, pgerror.ErrInvalidText) {
		t.Fatalf("malformed literal error = %v, want ErrInvalidText", err)
	}
	if _, err := e.canonicalArrayValue(expr, "not-an-array"); !errors.Is(err, pgerror.ErrInvalidText) {
		t.Fatalf("cached malformed literal error = %v, want ErrInvalidText", err)
	}
}

// A derived table or CTE materialized on the right side of a JOIN also keeps
// its column types for the outer WHERE.
func TestJoinedDerivedAndCTEArrayTyping(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)
	execMust(t, e, `CREATE TABLE t (id INTEGER PRIMARY KEY, flags BOOLEAN[])`)
	execMust(t, e, `INSERT INTO t VALUES (1, '{t,f}'), (2, '{f,t}')`)
	execMust(t, e, `CREATE TABLE u (id INTEGER PRIMARY KEY, flags BOOLEAN[], tags TEXT[])`)
	execMust(t, e, `INSERT INTO u VALUES (1, '{f,t}', '{a,b}'), (2, '{t,f}', '{c}')`)

	cases := []struct {
		sql  string
		want interface{}
	}{
		{`SELECT count(*) FROM t JOIN (SELECT id, flags FROM u) x ON x.id = t.id WHERE NOT x.flags[1]`, int64(1)},
		{`SELECT count(*) FROM t JOIN (SELECT id, flags FROM u) x ON x.id = t.id WHERE x.flags[1] = ANY('{true}')`, int64(1)},
		{`SELECT count(*) FROM t JOIN (SELECT id, tags FROM u) x ON x.id = t.id WHERE x.tags = '{a, b}'`, int64(1)},
		{`WITH x AS (SELECT id, flags FROM u) SELECT count(*) FROM t JOIN x ON x.id = t.id WHERE NOT x.flags[1]`, int64(1)},
		{`WITH x AS (SELECT id, tags FROM u) SELECT count(*) FROM t JOIN x ON x.id = t.id WHERE x.tags = '{a, b}'`, int64(1)},
	}
	for _, tc := range cases {
		res := execMust(t, e, tc.sql)
		if got := res.Rows[0][0]; got != tc.want {
			t.Errorf("%s = %#v, want %#v", tc.sql, got, tc.want)
		}
	}
}

// A view expands to a derived table, so it keeps the same typing guarantees.
func TestViewArrayTyping(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)
	execMust(t, e, `CREATE TABLE t (id INTEGER PRIMARY KEY, flags BOOLEAN[], tags TEXT[])`)
	execMust(t, e, `INSERT INTO t VALUES (1, '{t,f}', '{a,b}'), (2, '{f,t}', '{c}')`)
	execMust(t, e, `CREATE VIEW v AS SELECT flags, tags FROM t`)

	for _, tc := range []struct {
		sql  string
		want interface{}
	}{
		{`SELECT count(*) FROM v WHERE NOT flags[1]`, int64(1)},
		{`SELECT count(*) FROM v WHERE flags[1] = ANY('{true}')`, int64(1)},
		{`SELECT count(*) FROM v WHERE tags = '{a, b}'`, int64(1)},
	} {
		res := execMust(t, e, tc.sql)
		if got := res.Rows[0][0]; got != tc.want {
			t.Errorf("%s = %#v, want %#v", tc.sql, got, tc.want)
		}
	}
}

func TestTableFunctionColumnAliasList(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)

	res := execMust(t, e, `SELECT unnest FROM unnest(ARRAY[1,2,3]) AS t(unnest)`)
	if len(res.Columns) != 1 || res.Columns[0] != "unnest" {
		t.Fatalf("aliased unnest columns = %v, want [unnest]", res.Columns)
	}
	if res.RowCount != 3 || res.Rows[2][0] != int64(3) {
		t.Fatalf("aliased unnest rows = %v", res.Rows)
	}

	res = execMust(t, e, `SELECT t.n FROM unnest(ARRAY[10,20,30]) AS t(n) WHERE n > 10 ORDER BY n`)
	if res.RowCount != 2 || res.Rows[0][0] != int64(20) || res.Rows[1][0] != int64(30) {
		t.Fatalf("aliased unnest WHERE rows = %v", res.Rows)
	}
	if len(res.ColumnTypes) != 1 || res.ColumnTypes[0] != "INTEGER" {
		t.Fatalf("aliased unnest column type = %v, want INTEGER", res.ColumnTypes)
	}

	res = execMust(t, e, `SELECT n FROM unnest('{a,b}'::TEXT[]) AS t(n) WHERE n = 'b'`)
	if res.RowCount != 1 || res.Rows[0][0] != "b" {
		t.Fatalf("aliased text unnest rows = %v", res.Rows)
	}

	_, err := execSQL(e, `SELECT * FROM unnest(ARRAY[1]) AS t(a, b)`)
	if !errors.Is(err, pgerror.ErrInvalidColumnReference) {
		t.Fatalf("alias arity error = %v, want ErrInvalidColumnReference", err)
	}
}

func TestArrayContainment(t *testing.T) {
	_, schema, table := newTestDB(t)
	e := newExec(schema, table)
	execMust(t, e, `CREATE TABLE t (
		id INTEGER PRIMARY KEY,
		nums INTEGER[],
		tags TEXT[],
		flags BOOLEAN[],
		exact NUMERIC[]
	)`)
	execMust(t, e, `INSERT INTO t VALUES
		(1, '{1,2,3}', '{a,b}', '{t,f}', '{1.10,3}'),
		(2, '{1,NULL}', '{NULL,"x"}', NULL, NULL),
		(3, '{}', '{}', '{}', '{}'),
		(4, NULL, NULL, NULL, NULL)`)

	cases := []struct {
		sql  string
		want interface{}
	}{
		// Each contained element needs some containing element; duplicates are
		// irrelevant.
		{`SELECT nums @> ARRAY[1] FROM t WHERE id = 1`, true},
		{`SELECT nums @> ARRAY[3,1] FROM t WHERE id = 1`, true},
		{`SELECT nums @> ARRAY[2,2] FROM t WHERE id = 1`, true},
		{`SELECT nums @> ARRAY[4] FROM t WHERE id = 1`, false},
		{`SELECT nums <@ ARRAY[3,2,1,5] FROM t WHERE id = 1`, true},
		{`SELECT ARRAY[3,2,1,5] @> nums FROM t WHERE id = 1`, true},
		// Empty arrays: the empty array is contained by everything.
		{`SELECT nums @> ARRAY[]::INTEGER[] FROM t WHERE id = 1`, true},
		{`SELECT nums @> ARRAY[1] FROM t WHERE id = 3`, false},
		{`SELECT nums @> ARRAY[]::INTEGER[] FROM t WHERE id = 3`, true},
		{`SELECT nums <@ ARRAY[1] FROM t WHERE id = 3`, true},
		// NULL elements only match other NULL elements.
		{`SELECT nums @> ARRAY[NULL]::INTEGER[] FROM t WHERE id = 2`, true},
		{`SELECT nums @> ARRAY[1] FROM t WHERE id = 2`, true},
		{`SELECT nums @> ARRAY[2] FROM t WHERE id = 2`, false},
		{`SELECT nums @> ARRAY[1, NULL]::INTEGER[] FROM t WHERE id = 2`, true},
		{`SELECT nums @> ARRAY[1, NULL]::INTEGER[] FROM t WHERE id = 1`, false},
		// A NULL operand yields NULL.
		{`SELECT nums @> NULL FROM t WHERE id = 1`, nil},
		{`SELECT NULL::INTEGER[] @> nums FROM t WHERE id = 1`, nil},
		{`SELECT nums @> ARRAY[1] FROM t WHERE id = 4`, nil},
		// Other element types and a text array literal on the right.
		{`SELECT tags @> ARRAY['a'] FROM t WHERE id = 1`, true},
		{`SELECT tags @> ARRAY['c'] FROM t WHERE id = 1`, false},
		{`SELECT tags @> '{a,b}' FROM t WHERE id = 1`, true},
		{`SELECT tags @> '{a,b,c}' FROM t WHERE id = 1`, false},
		{`SELECT '{1,2}' <@ nums FROM t WHERE id = 1`, true},
		{`SELECT '{1,4}' <@ nums FROM t WHERE id = 1`, false},
		{`SELECT tags @> ARRAY[NULL]::TEXT[] FROM t WHERE id = 2`, true},
		{`SELECT flags @> ARRAY[TRUE] FROM t WHERE id = 1`, true},
		{`SELECT flags @> ARRAY[TRUE] FROM t WHERE id = 3`, false},
		// NUMERIC elements compare exactly, so 1.10 matches 1.1.
		{`SELECT exact @> ARRAY[1.1]::NUMERIC[] FROM t WHERE id = 1`, true},
	}
	for _, tc := range cases {
		res := execMust(t, e, tc.sql)
		if res.RowCount != 1 || res.Rows[0][0] != tc.want {
			t.Errorf("%s = %v, want %v", tc.sql, res.Rows, tc.want)
		}
	}

	if _, err := execSQL(e, `SELECT nums @> 5 FROM t`); jsonbErrorCode(t, err) != pgerror.CodeDatatypeMismatch {
		t.Fatalf("integer containment error = %v, want 42804", err)
	}
}