Skip to content

Database governance built for humans and agents — controlling changes and access across every major database.

41.2 KB
Raw Blame
package plsql

import (
	"context"
	"io"
	"os"
	"slices"
	"strings"
	"testing"

	metadatapb "github.com/bytebase/omni/metadata"
	"github.com/stretchr/testify/require"
	"gopkg.in/yaml.v3"

	"github.com/bytebase/bytebase/backend/common/yamltest"
	storepb "github.com/bytebase/bytebase/backend/generated-go/store"
	"github.com/bytebase/bytebase/backend/plugin/parser/base"
	"github.com/bytebase/bytebase/backend/store/model"
)

type candidatesTest struct {
	Input string
	Want  []base.Candidate
}

func TestCompletion(t *testing.T) {
	tests := []candidatesTest{}

	const (
		record = false
	)
	var (
		filepath = "test-data/test_completion.yaml"
	)

	a := require.New(t)
	yamlFile, err := os.Open(filepath)
	a.NoError(err)

	byteValue, err := io.ReadAll(yamlFile)
	a.NoError(yamlFile.Close())
	a.NoError(err)
	a.NoError(yaml.Unmarshal(byteValue, &tests))

	for i, t := range tests {
		text, caretLine, caretOffset := catchCaret(t.Input)
		result, err := base.Completion(context.Background(), storepb.Engine_ORACLE, base.CompletionContext{
			Scene:             base.SceneTypeAll,
			DefaultDatabase:   "SCHEMA1",
			Metadata:          getMetadataForTest,
			ListDatabaseNames: listDatabaseNamesForTest,
		}, text, caretLine, caretOffset)
		a.NoError(err)
		var filteredResult []base.Candidate
		for _, r := range result {
			switch r.Type {
			case base.CandidateTypeKeyword, base.CandidateTypeFunction:
				continue
			default:
				filteredResult = append(filteredResult, r)
			}
		}
		slices.SortFunc(filteredResult, func(a, b base.Candidate) int {
			if a.Type != b.Type {
				if a.Type < b.Type {
					return -1
				}
				return 1
			}
			if a.Text != b.Text {
				if a.Text < b.Text {
					return -1
				}
				return 1
			}
			if a.Definition < b.Definition {
				return -1
			}
			if a.Definition > b.Definition {
				return 1
			}
			return 0
		})

		if record {
			tests[i].Want = filteredResult
		} else {
			a.Equal(t.Want, filteredResult, t.Input)
		}
	}

	if record {
		yamltest.Record(t, filepath, tests)
	}
}

func TestCompletionDoesNotDependOnANTLR(t *testing.T) {
	content, err := os.ReadFile("completion.go")
	require.NoError(t, err)
	source := string(content)
	require.NotContains(t, source, "github.com/antlr4-go/antlr/v4")
	require.NotContains(t, source, "github.com/bytebase/parser/plsql")
	require.NotContains(t, source, "CodeCompletionCore")
}

func TestCompletionByteOffsetUsesUTF16Columns(t *testing.T) {
	input := "SELECT '😀' FROM t1 WHERE | AND c1 = 1"
	statement, caretLine, caretOffset := catchCaretUTF16(input)

	_, byteOffset := computeSQLAndByteOffset(statement, caretLine, caretOffset, false /* tricky */)

	require.Equal(t, strings.Index(input, "|"), byteOffset)
}

func TestCompletionLongTailTableSources(t *testing.T) {
	tests := []struct {
		name        string
		input       string
		wantColumns []string
	}{
		{
			name:        "json table explicit columns",
			input:       "SELECT jt.| FROM t1, JSON_TABLE(t1.c1, '$.rows[*]' COLUMNS (id NUMBER PATH '$.id', name VARCHAR2(100) PATH '$.name')) jt",
			wantColumns: []string{"ID", "NAME"},
		},
		{
			name:        "xmltable explicit columns",
			input:       "SELECT xt.| FROM t1, XMLTABLE('/root/row' PASSING t1.c1 COLUMNS id NUMBER PATH 'id', name VARCHAR2(100) PATH 'name') xt",
			wantColumns: []string{"ID", "NAME"},
		},
		{
			name:        "inline external declared columns",
			input:       "SELECT ext.| FROM EXTERNAL ((a NUMBER, b VARCHAR2(10)) TYPE ORACLE_LOADER DEFAULT DIRECTORY data_dir LOCATION ('x.csv')) ext",
			wantColumns: []string{"A", "B"},
		},
		{
			name:        "lateral inline view columns",
			input:       "SELECT l.| FROM t1, LATERAL (SELECT c1 FROM t2 WHERE t2.c1 = t1.c1) l",
			wantColumns: []string{"C1"},
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			text, caretLine, caretOffset := catchCaret(tt.input)
			result, err := base.Completion(context.Background(), storepb.Engine_ORACLE, base.CompletionContext{
				Scene:             base.SceneTypeAll,
				DefaultDatabase:   "SCHEMA1",
				Metadata:          getMetadataForTest,
				ListDatabaseNames: listDatabaseNamesForTest,
			}, text, caretLine, caretOffset)
			require.NoError(t, err)
			require.Equal(t, tt.wantColumns, completionColumnTexts(result))
		})
	}
}

func TestCompletionCoverageMatrix(t *testing.T) {
	tests := []struct {
		name    string
		input   string
		want    []completionCandidateSpec
		notWant []completionCandidateSpec
	}{
		{
			name:  "select list uses table columns",
			input: "SELECT | FROM t1",
			want:  columns("C1"),
		},
		{
			name:  "select list uses alias columns",
			input: "SELECT a.| FROM t1 a",
			want:  columns("C1"),
		},
		{
			name:  "select list uses schema qualified table columns",
			input: "SELECT | FROM schema1.t2",
			want:  columns("C1", "C2"),
		},
		{
			name:  "column prefix keeps matching column available",
			input: "SELECT C2| FROM t2",
			want:  columns("C2"),
		},
		{
			name:  "qualified column prefix keeps matching column available",
			input: "SELECT t2.C2| FROM t2",
			want:  columns("C2"),
		},
		{
			name:    "unknown qualifier offers no columns",
			input:   "SELECT missing.| FROM t1",
			notWant: columns("C1", "C2"),
		},
		{
			name:  "join on left alias columns",
			input: "SELECT * FROM t1 a JOIN t2 b ON a.|",
			want:  columns("C1"),
		},
		{
			name:  "join on right alias columns",
			input: "SELECT * FROM t1 a JOIN t2 b ON b.|",
			want:  columns("C1", "C2"),
		},
		{
			name:  "join where sees both table columns",
			input: "SELECT * FROM t1 a JOIN t2 b ON a.c1 = b.c1 WHERE |",
			want:  columns("C1", "C2"),
		},
		{
			name:    "left alias excludes right only column",
			input:   "SELECT a.| FROM t1 a JOIN t2 b ON a.c1 = b.c1",
			want:    columns("C1"),
			notWant: columns("C2"),
		},
		{
			name:  "join table reference offers tables",
			input: "SELECT * FROM t1 JOIN |",
			want:  tables("T1", "T2"),
		},
		{
			name:  "using clause offers joined columns",
			input: "SELECT * FROM t1 JOIN t2 USING (|)",
			want:  columns("C1", "C2"),
		},
		{
			name:  "cte explicit columns are available",
			input: "WITH x(x1, x2) AS (SELECT * FROM t2) SELECT x.| FROM x",
			want:  columns("X1", "X2"),
		},
		{
			name:  "cte star body expands through query span",
			input: "WITH x AS (SELECT * FROM t2) SELECT x.| FROM x",
			want:  columns("C1", "C2"),
		},
		{
			name:  "cte table candidate is available without schema qualifier",
			input: "WITH x AS (SELECT * FROM t2) SELECT * FROM |",
			want:  tables("X", "T1", "T2"),
		},
		{
			name:    "cte table candidate is excluded from schema qualified table reference",
			input:   "WITH x AS (SELECT * FROM t2) SELECT * FROM schema1.|",
			want:    tables("T1", "T2"),
			notWant: tables("X"),
		},
		{
			name:  "multiple ctes expose selected cte columns",
			input: "WITH a AS (SELECT c1 FROM t1), b AS (SELECT c1, c2 FROM t2) SELECT b.| FROM a JOIN b ON a.c1 = b.c1",
			want:  columns("C1", "C2"),
		},
		{
			name:  "derived table explicit projection columns",
			input: "SELECT x.| FROM (SELECT c1 FROM t1) x",
			want:  columns("C1"),
		},
		{
			name:  "derived table star body expands through query span",
			input: "SELECT x.| FROM (SELECT * FROM t2) x",
			want:  columns("C1", "C2"),
		},
		{
			name:    "unqualified select from derived table exposes derived columns",
			input:   "SELECT | FROM (SELECT c1 FROM t2) x",
			want:    columns("C1"),
			notWant: columns("C2"),
		},
		{
			name:  "lateral alias columns",
			input: "SELECT l.| FROM t1, LATERAL (SELECT c1 FROM t2 WHERE t2.c1 = t1.c1) l",
			want:  columns("C1"),
		},
		{
			name:    "lateral body sees left outer reference",
			input:   "SELECT * FROM t1, LATERAL (SELECT t1.| FROM t2) l",
			want:    columns("C1"),
			notWant: columns("C2"),
		},
		{
			name:  "json table qualified columns",
			input: "SELECT jt.| FROM t1, JSON_TABLE(t1.c1, '$.rows[*]' COLUMNS (id NUMBER PATH '$.id', name VARCHAR2(100) PATH '$.name')) jt",
			want:  columns("ID", "NAME"),
		},
		{
			name:  "json table unqualified columns",
			input: "SELECT | FROM t1, JSON_TABLE(t1.c1, '$.rows[*]' COLUMNS (id NUMBER PATH '$.id', name VARCHAR2(100) PATH '$.name')) jt",
			want:  columns("C1", "ID", "NAME"),
		},
		{
			name:  "xmltable qualified columns",
			input: "SELECT xt.| FROM t1, XMLTABLE('/root/row' PASSING t1.c1 COLUMNS id NUMBER PATH 'id', name VARCHAR2(100) PATH 'name') xt",
			want:  columns("ID", "NAME"),
		},
		{
			name:  "inline external qualified columns",
			input: "SELECT ext.| FROM EXTERNAL ((a NUMBER, b VARCHAR2(10)) TYPE ORACLE_LOADER DEFAULT DIRECTORY data_dir LOCATION ('x.csv')) ext",
			want:  columns("A", "B"),
		},
		{
			name:  "containers alias resolves base object columns",
			input: "SELECT c.| FROM CONTAINERS(schema1.t2) c",
			want:  columns("C1", "C2"),
		},
		{
			name:    "table function alias does not invent columns",
			input:   "SELECT x.| FROM TABLE(pkg.values()) x",
			notWant: columns("C1", "C2"),
		},
		{
			name:  "insert target table reference",
			input: "INSERT INTO |",
			want:  tables("T1", "T2"),
		},
		{
			name:  "insert column list uses target table columns",
			input: "INSERT INTO t2 (|) VALUES (1)",
			want:  columns("C1", "C2"),
		},
		{
			name:  "insert select uses source table columns",
			input: "INSERT INTO t1 SELECT | FROM t2",
			want:  columns("C1", "C2"),
		},
		{
			name:  "update target table reference",
			input: "UPDATE | SET c1 = 1",
			want:  tables("T1", "T2"),
		},
		{
			name:  "update set uses target table columns",
			input: "UPDATE t2 SET |",
			want:  columns("C1", "C2"),
		},
		{
			name:  "update where uses target table columns",
			input: "UPDATE t2 SET c1 = 1 WHERE |",
			want:  columns("C1", "C2"),
		},
		{
			name:  "delete target table reference",
			input: "DELETE FROM |",
			want:  tables("T1", "T2"),
		},
		{
			name:  "delete where uses target table columns",
			input: "DELETE FROM t2 WHERE |",
			want:  columns("C1", "C2"),
		},
		{
			name:  "merge target table reference",
			input: "MERGE INTO |",
			want:  tables("T1", "T2"),
		},
		{
			name:    "merge source table reference offers real tables",
			input:   "MERGE INTO t1 target USING |",
			want:    tables("T1", "T2"),
			notWant: tables("TARGET"),
		},
		{
			name:  "merge on uses target and source columns",
			input: "MERGE INTO t1 target USING t2 source ON |",
			want:  columns("C1", "C2"),
		},
		{
			name:  "schema qualified table reference offers schema tables",
			input: "SELECT * FROM schema1.|",
			want:  tables("T1", "T2"),
		},
		{
			name:  "table prefix keeps matching table available",
			input: "SELECT * FROM t|",
			want:  tables("T1", "T2"),
		},
		{
			name:  "quoted identifier context preserves candidate text",
			input: `SELECT "t2".| FROM t2`,
			want:  columns("C1", "C2"),
		},
		{
			name:  "select list uses t2 columns",
			input: "SELECT | FROM t2",
			want:  columns("C1", "C2"),
		},
		{
			name:  "where clause uses table columns",
			input: "SELECT * FROM t2 WHERE |",
			want:  columns("C1", "C2"),
		},
		{
			name:  "order by clause uses table columns",
			input: "SELECT * FROM t2 ORDER BY |",
			want:  columns("C1", "C2"),
		},
		{
			name:  "group by clause uses table columns",
			input: "SELECT * FROM t2 GROUP BY |",
			want:  columns("C1", "C2"),
		},
		{
			name:  "having clause uses grouped table columns",
			input: "SELECT c1, COUNT(*) FROM t2 GROUP BY c1 HAVING |",
			want:  columns("C1", "C2"),
		},
		{
			name:  "distinct select list uses table columns",
			input: "SELECT DISTINCT | FROM t2",
			want:  columns("C1", "C2"),
		},
		{
			name:  "table name qualifier uses table columns",
			input: "SELECT t2.| FROM t2",
			want:  columns("C1", "C2"),
		},
		{
			name:  "comparison expression uses table columns",
			input: "SELECT * FROM t2 WHERE c1 = |",
			want:  columns("C1", "C2"),
		},
		{
			name:  "arithmetic expression uses table columns",
			input: "SELECT * FROM t2 WHERE c1 + |",
			want:  columns("C1", "C2"),
		},
		{
			name:  "case expression uses table columns",
			input: "SELECT CASE WHEN | THEN c1 ELSE c2 END FROM t2",
			want:  columns("C1", "C2"),
		},
		{
			name:  "function argument uses table columns",
			input: "SELECT NVL(|, 0) FROM t2",
			want:  columns("C1", "C2"),
		},
		{
			name:  "between lower bound uses table columns",
			input: "SELECT * FROM t2 WHERE c1 BETWEEN | AND 10",
			want:  columns("C1", "C2"),
		},
		{
			name:  "between upper bound uses table columns",
			input: "SELECT * FROM t2 WHERE c1 BETWEEN 1 AND |",
			want:  columns("C1", "C2"),
		},
		{
			name:  "like expression uses table columns",
			input: "SELECT * FROM t2 WHERE c1 LIKE |",
			want:  columns("C1", "C2"),
		},
		{
			name:  "is null predicate continuation uses table columns",
			input: "SELECT * FROM t2 WHERE c1 IS NULL OR |",
			want:  columns("C1", "C2"),
		},
		{
			name:  "select alias is available in order by",
			input: "SELECT c1 AS c_alias FROM t1 ORDER BY |",
			want:  columns("C1", "C_ALIAS"),
		},
		{
			name:  "select alias survives nested select in order by",
			input: "SELECT c1 AS c_alias FROM t1 WHERE EXISTS (SELECT c1 FROM t2) ORDER BY |",
			want:  columns("C1", "C_ALIAS"),
		},
		{
			name:    "select alias is not available in where",
			input:   "SELECT c1 AS c_alias FROM t1 WHERE |",
			want:    columns("C1"),
			notWant: columns("C_ALIAS"),
		},
		{
			name:  "select alias is available in group by",
			input: "SELECT c1 AS c_alias FROM t1 GROUP BY |",
			want:  columns("C1", "C_ALIAS"),
		},
		{
			name:  "comma join select list sees both tables",
			input: "SELECT | FROM t1 a, t2 b",
			want:  columns("C1", "C2"),
		},
		{
			name:  "comma join left alias columns",
			input: "SELECT a.| FROM t1 a, t2 b",
			want:  columns("C1"),
		},
		{
			name:  "comma join right alias columns",
			input: "SELECT b.| FROM t1 a, t2 b",
			want:  columns("C1", "C2"),
		},
		{
			name:  "left join select list sees both sides",
			input: "SELECT | FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1",
			want:  columns("C1", "C2"),
		},
		{
			name:  "right join select list sees both sides",
			input: "SELECT | FROM t1 RIGHT JOIN t2 ON t1.c1 = t2.c1",
			want:  columns("C1", "C2"),
		},
		{
			name:  "full outer join select list sees both sides",
			input: "SELECT | FROM t1 FULL OUTER JOIN t2 ON t1.c1 = t2.c1",
			want:  columns("C1", "C2"),
		},
		{
			name:  "cross join select list sees both sides",
			input: "SELECT | FROM t1 CROSS JOIN t2",
			want:  columns("C1", "C2"),
		},
		{
			name:  "natural join select list sees both sides",
			input: "SELECT | FROM t1 NATURAL JOIN t2",
			want:  columns("C1", "C2"),
		},
		{
			name:  "multi join final alias columns",
			input: "SELECT c.| FROM t1 a JOIN t2 b ON a.c1 = b.c1 JOIN t2 c ON b.c1 = c.c1",
			want:  columns("C1", "C2"),
		},
		{
			name:  "multi join where sees all aliases",
			input: "SELECT * FROM t1 a JOIN t2 b ON a.c1 = b.c1 JOIN t2 c ON b.c1 = c.c1 WHERE |",
			want:  columns("C1", "C2"),
		},
		{
			name:  "schema qualified join alias columns",
			input: "SELECT s.| FROM t1 a JOIN schema2.t2 s ON a.c1 = s.c1",
			want:  columns("C1", "C2"),
		},
		{
			name:    "schema qualified alias excludes same table from other schema",
			input:   "SELECT a.| FROM schema1.t1 a JOIN schema4.t1 b ON a.c1 = b.c1",
			want:    columns("C1"),
			notWant: columns("SCHEMA4_ONLY"),
		},
		{
			name:  "join table prefix keeps matching tables",
			input: "SELECT * FROM t1 JOIN t|",
			want:  tables("T1", "T2"),
		},
		{
			name:  "left join table reference offers tables",
			input: "SELECT * FROM t1 LEFT JOIN |",
			want:  tables("T1", "T2"),
		},
		{
			name:  "right join table reference offers tables",
			input: "SELECT * FROM t1 RIGHT JOIN |",
			want:  tables("T1", "T2"),
		},
		{
			name:  "cross join table reference offers tables",
			input: "SELECT * FROM t1 CROSS JOIN |",
			want:  tables("T1", "T2"),
		},
		{
			name:  "subquery select list uses inner table columns",
			input: "SELECT * FROM t1 WHERE EXISTS (SELECT | FROM t2)",
			want:  columns("C1", "C2"),
		},
		{
			name:  "subquery where uses inner table columns",
			input: "SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE |)",
			want:  columns("C1", "C2"),
		},
		{
			name:  "correlated subquery can qualify outer table",
			input: "SELECT * FROM t1 outer_t WHERE EXISTS (SELECT * FROM t2 WHERE outer_t.|)",
			want:  columns("C1"),
		},
		{
			name:  "correlated subquery can qualify inner table",
			input: "SELECT * FROM t1 outer_t WHERE EXISTS (SELECT * FROM t2 inner_t WHERE inner_t.|)",
			want:  columns("C1", "C2"),
		},
		{
			name:  "scalar subquery select list uses inner table columns",
			input: "SELECT (SELECT | FROM t2) FROM t1",
			want:  columns("C1", "C2"),
		},
		{
			name:  "in subquery select list uses inner table columns",
			input: "SELECT * FROM t1 WHERE c1 IN (SELECT | FROM t2)",
			want:  columns("C1", "C2"),
		},
		{
			name:  "nested derived table alias columns",
			input: "SELECT y.| FROM (SELECT x.c1 FROM (SELECT c1 FROM t1) x) y",
			want:  columns("C1"),
		},
		{
			name:  "derived table with column alias",
			input: "SELECT x.| FROM (SELECT c1 AS renamed FROM t1) x",
			want:  columns("RENAMED"),
		},
		{
			name:  "derived table join exposes derived alias columns",
			input: "SELECT x.| FROM (SELECT c1 FROM t1) x JOIN t2 ON x.c1 = t2.c1",
			want:  columns("C1"),
		},
		{
			name:  "derived table join where sees derived and physical columns",
			input: "SELECT * FROM (SELECT c1 FROM t1) x JOIN t2 ON x.c1 = t2.c1 WHERE |",
			want:  columns("C1", "C2"),
		},
		{
			name:  "cte selected through derived table",
			input: "WITH x AS (SELECT c1 FROM t1) SELECT | FROM (SELECT * FROM x) sub",
			want:  columns("C1"),
		},
		{
			name:  "cte joined with physical table",
			input: "WITH x AS (SELECT c1 FROM t1) SELECT | FROM x JOIN t2 ON x.c1 = t2.c1",
			want:  columns("C1", "C2"),
		},
		{
			name:  "cte alias joined with physical table",
			input: "WITH x AS (SELECT c1 FROM t1) SELECT xa.| FROM x xa JOIN t2 ON xa.c1 = t2.c1",
			want:  columns("C1"),
		},
		{
			name:  "cte in insert select",
			input: "WITH x AS (SELECT * FROM t2) INSERT INTO t1 SELECT | FROM x",
			want:  columns("C1", "C2"),
		},
		{
			name:  "cte in update scalar subquery",
			input: "WITH x AS (SELECT c1, c2 FROM t2) UPDATE t1 SET c1 = (SELECT | FROM x)",
			want:  columns("C1", "C2"),
		},
		{
			name:  "cte in delete subquery",
			input: "WITH x AS (SELECT * FROM t2) DELETE FROM t1 WHERE c1 IN (SELECT | FROM x)",
			want:  columns("C1", "C2"),
		},
		{
			name:  "cte set operation exposes positional columns",
			input: "WITH x AS (SELECT c1 FROM t1 UNION SELECT c1 FROM t2) SELECT x.| FROM x",
			want:  columns("C1"),
		},
		{
			name:  "lateral body uses inner alias columns",
			input: "SELECT * FROM t1, LATERAL (SELECT inner_t.| FROM t2 inner_t WHERE inner_t.c1 = t1.c1) l",
			want:  columns("C1", "C2"),
		},
		{
			name:  "left join lateral alias columns",
			input: "SELECT l.| FROM t1 LEFT JOIN LATERAL (SELECT c1 FROM t2 WHERE t2.c1 = t1.c1) l ON 1 = 1",
			want:  columns("C1"),
		},
		{
			name:  "lateral unqualified select list sees inner columns",
			input: "SELECT * FROM t1, LATERAL (SELECT | FROM t2 WHERE t2.c1 = t1.c1) l",
			want:  columns("C1", "C2"),
		},
		{
			name:  "json table table alias is offered as table candidate",
			input: "SELECT | FROM t1, JSON_TABLE(t1.c1, '$.rows[*]' COLUMNS (id NUMBER PATH '$.id')) jt",
			want:  tables("JT"),
		},
		{
			name:  "xmltable unqualified columns",
			input: "SELECT | FROM t1, XMLTABLE('/root/row' PASSING t1.c1 COLUMNS id NUMBER PATH 'id', name VARCHAR2(100) PATH 'name') xt",
			want:  columns("C1", "ID", "NAME"),
		},
		{
			name:  "inline external unqualified columns",
			input: "SELECT | FROM EXTERNAL ((a NUMBER, b VARCHAR2(10)) TYPE ORACLE_LOADER DEFAULT DIRECTORY data_dir LOCATION ('x.csv')) ext",
			want:  columns("A", "B"),
		},
		{
			name:  "containers without alias resolves base object columns",
			input: "SELECT t2.| FROM CONTAINERS(schema1.t2)",
			want:  columns("C1", "C2"),
		},
		{
			name:    "pivot does not invent transformed columns",
			input:   "SELECT | FROM t2 PIVOT (COUNT(*) FOR c1 IN (1 AS one))",
			notWant: columns("ONE"),
		},
		{
			name:    "unpivot does not invent transformed columns",
			input:   "SELECT | FROM t2 UNPIVOT (val FOR col IN (c1 AS 'C1', c2 AS 'C2'))",
			notWant: columns("VAL", "COL"),
		},
		{
			name:    "model does not invent transformed columns",
			input:   "SELECT | FROM t2 MODEL DIMENSION BY (c1) MEASURES (c2) RULES (c2[1] = 1)",
			notWant: columns("VAL", "COL"),
		},
		{
			name:  "insert schema qualified table reference",
			input: "INSERT INTO schema2.|",
			want:  tables("T1", "T2"),
		},
		{
			name:  "insert schema qualified target columns",
			input: "INSERT INTO schema2.t2 (|) VALUES (1)",
			want:  columns("C1", "C2"),
		},
		{
			name:  "insert values expression uses target columns",
			input: "INSERT INTO t2 VALUES (|)",
			want:  columns("C1", "C2"),
		},
		{
			name:  "insert second column list position uses target columns",
			input: "INSERT INTO t2 (c1, |) VALUES (1, 2)",
			want:  columns("C1", "C2"),
		},
		{
			name:  "insert select with schema qualified source",
			input: "INSERT INTO t1 SELECT | FROM schema2.t2",
			want:  columns("C1", "C2"),
		},
		{
			name:  "update schema qualified target reference",
			input: "UPDATE schema2.|",
			want:  tables("T1", "T2"),
		},
		{
			name:  "update schema qualified target set columns",
			input: "UPDATE schema2.t2 SET |",
			want:  columns("C1", "C2"),
		},
		{
			name:  "update set expression uses target columns",
			input: "UPDATE t2 SET c1 = |",
			want:  columns("C1", "C2"),
		},
		{
			name:  "update where continuation uses target columns",
			input: "UPDATE t2 SET c1 = 1 WHERE c1 > 0 AND |",
			want:  columns("C1", "C2"),
		},
		{
			name:  "delete schema qualified table reference",
			input: "DELETE FROM schema2.|",
			want:  tables("T1", "T2"),
		},
		{
			name:  "delete where continuation uses target columns",
			input: "DELETE FROM t2 WHERE c1 > 0 AND |",
			want:  columns("C1", "C2"),
		},
		{
			name:  "merge target schema qualified table reference",
			input: "MERGE INTO schema2.|",
			want:  tables("T1", "T2"),
		},
		{
			name:  "merge source schema qualified table reference",
			input: "MERGE INTO t1 target USING schema2.|",
			want:  tables("T1", "T2"),
		},
		{
			name:  "merge qualified target columns",
			input: "MERGE INTO t1 target USING t2 source ON target.|",
			want:  columns("C1"),
		},
		{
			name:  "merge qualified source columns",
			input: "MERGE INTO t1 target USING t2 source ON source.|",
			want:  columns("C1", "C2"),
		},
		{
			name:  "merge update set uses target and source columns",
			input: "MERGE INTO t1 target USING t2 source ON target.c1 = source.c1 WHEN MATCHED THEN UPDATE SET target.c1 = |",
			want:  columns("C1", "C2"),
		},
		{
			name:  "merge insert values uses target and source columns",
			input: "MERGE INTO t1 target USING t2 source ON target.c1 = source.c1 WHEN NOT MATCHED THEN INSERT (c1) VALUES (|)",
			want:  columns("C1", "C2"),
		},
		{
			name:  "select from root offers schemas",
			input: "SELECT * FROM |",
			want:  schemas("SCHEMA1", "SCHEMA2", "SCHEMA3"),
		},
		{
			name:  "select from root offers views",
			input: "SELECT * FROM |",
			want:  views("V1"),
		},
		{
			name:  "select from root offers sequences",
			input: "SELECT * FROM |",
			want:  sequences("SEQ1", "USER_ID_SEQ"),
		},
		{
			name:  "schema2 table reference offers schema2 tables",
			input: "SELECT * FROM schema2.|",
			want:  tables("T1", "T2"),
		},
		{
			name:  "schema3 table reference offers schema3 tables",
			input: "SELECT * FROM schema3.|",
			want:  tables("T1", "T2"),
		},
		{
			name:  "schema prefix keeps matching schema available",
			input: "SELECT * FROM schema|",
			want:  schemas("SCHEMA1", "SCHEMA2", "SCHEMA3"),
		},
		{
			name:  "view reference offers view candidate",
			input: "SELECT * FROM v|",
			want:  views("V1"),
		},
		{
			name:  "sequence reference offers sequence candidates",
			input: "DROP SEQUENCE |",
			want:  sequences("SEQ1", "USER_ID_SEQ"),
		},
		{
			name:  "sequence prefix keeps matching sequence",
			input: "DROP SEQUENCE user|",
			want:  sequences("USER_ID_SEQ"),
		},
		{
			name:  "alter table reference offers tables",
			input: "ALTER TABLE |",
			want:  tables("T1", "T2"),
		},
		{
			name:  "alter schema qualified table reference offers tables",
			input: "ALTER TABLE schema1.|",
			want:  tables("T1", "T2"),
		},
		{
			name:  "alter drop column offers table columns",
			input: "ALTER TABLE t2 DROP COLUMN |",
			want:  columns("C1", "C2"),
		},
		{
			name:  "drop table reference offers tables",
			input: "DROP TABLE |",
			want:  tables("T1", "T2"),
		},
		{
			name:  "drop schema qualified table reference offers tables",
			input: "DROP TABLE schema1.|",
			want:  tables("T1", "T2"),
		},
		{
			name:  "drop view reference offers views",
			input: "DROP VIEW |",
			want:  views("V1"),
		},
		{
			name:  "truncate table reference offers tables",
			input: "TRUNCATE TABLE |",
			want:  tables("T1", "T2"),
		},
		{
			name:  "comment on table reference offers tables",
			input: "COMMENT ON TABLE |",
			want:  tables("T1", "T2"),
		},
		{
			name:  "comment on column offers table columns",
			input: "COMMENT ON COLUMN t2.|",
			want:  columns("C1", "C2"),
		},
		{
			name:  "grant on object offers tables",
			input: "GRANT SELECT ON |",
			want:  tables("T1", "T2"),
		},
		{
			name:  "revoke on object offers tables",
			input: "REVOKE SELECT ON |",
			want:  tables("T1", "T2"),
		},
		{
			name:  "create table datatype offers NUMBER",
			input: "CREATE TABLE t (c |)",
			want:  keywords("NUMBER"),
		},
		{
			name:  "create table references offers tables",
			input: "CREATE TABLE child (parent_id NUMBER REFERENCES |)",
			want:  tables("T1", "T2"),
		},
		{
			name:  "multiple statements limits to statement at caret insert",
			input: "SELECT * FROM t1; INSERT INTO |",
			want:  tables("T1", "T2"),
		},
		{
			name:  "multiple statements limits to statement at caret update",
			input: "SELECT * FROM t1; UPDATE | SET c1 = 1",
			want:  tables("T1", "T2"),
		},
		{
			name:  "multiple statements limits to statement at caret delete",
			input: "SELECT * FROM t1; DELETE FROM |",
			want:  tables("T1", "T2"),
		},
		{
			name:  "multiple statements select columns after invalid first statement",
			input: "SELECT FROM broken; SELECT | FROM t2",
			want:  columns("C1", "C2"),
		},
		{
			name:  "table prefix after multiple statements",
			input: "SELECT * FROM t1; SELECT * FROM t|",
			want:  tables("T1", "T2"),
		},
		{
			name:  "quoted table alias columns",
			input: `SELECT "Alias".| FROM t2 "Alias"`,
			want:  columns("C1", "C2"),
		},
		{
			name:  "quoted cte alias columns",
			input: `WITH "X"(x1, x2) AS (SELECT * FROM t2) SELECT "X".| FROM "X"`,
			want:  columns("X1", "X2"),
		},
		{
			name:  "quoted derived alias columns",
			input: `SELECT "Sub".| FROM (SELECT c1 FROM t1) "Sub"`,
			want:  columns("C1"),
		},
		{
			name:  "quoted schema qualified table reference",
			input: `SELECT * FROM "SCHEMA1".|`,
			want:  tables("T1", "T2"),
		},
		{
			name:  "quoted schema qualified columns",
			input: `SELECT | FROM "SCHEMA1"."T2"`,
			want:  columns("C1", "C2"),
		},
		{
			name:  "reserved-like quoted alias columns",
			input: `SELECT "SELECT".| FROM t2 "SELECT"`,
			want:  columns("C1", "C2"),
		},
		{
			name:    "database link table keeps local table columns unavailable when metadata missing",
			input:   "SELECT remote_t.| FROM t2@remote remote_t",
			notWant: columns("C1", "C2"),
		},
		{
			name:    "schema qualified unknown table offers no known columns",
			input:   "SELECT | FROM schema1.missing_table",
			notWant: columns("C1", "C2"),
		},
		{
			name:    "unknown schema table reference offers no local tables",
			input:   "SELECT * FROM missing_schema.|",
			notWant: tables("T1", "T2"),
		},
		{
			name:    "unknown cte qualifier offers no columns",
			input:   "WITH x AS (SELECT c1 FROM t1) SELECT y.| FROM x",
			notWant: columns("C1", "C2"),
		},
		{
			name:    "unknown physical alias offers no columns",
			input:   "SELECT z.| FROM t1 a JOIN t2 b ON a.c1 = b.c1",
			notWant: columns("C1", "C2"),
		},
		{
			name:    "unknown lateral alias offers no columns",
			input:   "SELECT missing.| FROM t1, LATERAL (SELECT c1 FROM t2) l",
			notWant: columns("C1", "C2"),
		},
		{
			name:  "hierarchical start with uses from scope",
			input: "SELECT * FROM t2 START WITH | CONNECT BY PRIOR c1 = c2",
			want:  columns("C1", "C2"),
		},
		{
			name:  "hierarchical connect by uses from scope",
			input: "SELECT * FROM t2 START WITH c1 IS NULL CONNECT BY |",
			want:  columns("C1", "C2"),
		},
		{
			name:  "analytic partition by uses table columns",
			input: "SELECT COUNT(*) OVER (PARTITION BY |) FROM t2",
			want:  columns("C1", "C2"),
		},
		{
			name:  "analytic order by uses table columns",
			input: "SELECT COUNT(*) OVER (ORDER BY |) FROM t2",
			want:  columns("C1", "C2"),
		},
		{
			name:  "for update select list keeps table columns",
			input: "SELECT | FROM t2 FOR UPDATE",
			want:  columns("C1", "C2"),
		},
		{
			name:  "subquery alias table candidate is offered",
			input: "SELECT | FROM (SELECT c1 FROM t1) sub1",
			want:  tables("SUB1"),
		},
		{
			name:  "physical alias table candidate is offered",
			input: "SELECT | FROM t1 x",
			want:  tables("X"),
		},
		{
			name:  "schema2 table prefix keeps matching tables",
			input: "SELECT * FROM schema2.t|",
			want:  tables("T1", "T2"),
		},
		{
			name:  "quoted schema2 table prefix keeps matching tables",
			input: `SELECT * FROM "SCHEMA2".t|`,
			want:  tables("T1", "T2"),
		},
		{
			name:  "schema3 t1 select list uses columns",
			input: "SELECT | FROM schema3.t1",
			want:  columns("C1"),
		},
		{
			name:  "schema3 alias select list uses columns",
			input: "SELECT s.| FROM schema3.t2 s",
			want:  columns("C1", "C2"),
		},
		{
			name:  "schema3 alias where uses columns",
			input: "SELECT * FROM schema3.t2 s WHERE s.|",
			want:  columns("C1", "C2"),
		},
		{
			name:  "schema3 alias order by uses columns",
			input: "SELECT * FROM schema3.t2 s ORDER BY s.|",
			want:  columns("C1", "C2"),
		},
		{
			name:  "join schema3 table alias columns",
			input: "SELECT s.| FROM t1 a JOIN schema3.t2 s ON a.c1 = s.c1",
			want:  columns("C1", "C2"),
		},
		{
			name:  "insert schema3 qualified target columns",
			input: "INSERT INTO schema3.t2 (|) VALUES (1)",
			want:  columns("C1", "C2"),
		},
		{
			name:  "update schema3 qualified target set columns",
			input: "UPDATE schema3.t2 SET |",
			want:  columns("C1", "C2"),
		},
		{
			name:  "delete schema3 qualified target where columns",
			input: "DELETE FROM schema3.t2 WHERE |",
			want:  columns("C1", "C2"),
		},
		{
			name:  "merge schema3 target qualified columns",
			input: "MERGE INTO schema3.t1 target USING schema2.t2 source ON source.|",
			want:  columns("C1", "C2"),
		},
		{
			name:  "cte table prefix keeps matching cte",
			input: "WITH x AS (SELECT * FROM t2) SELECT * FROM x|",
			want:  tables("X"),
		},
		{
			name:  "cte explicit column prefix keeps matching column",
			input: "WITH x(x1, x2) AS (SELECT * FROM t2) SELECT x.X1| FROM x",
			want:  columns("X1"),
		},
		{
			name:  "derived table column prefix keeps matching column",
			input: "SELECT x.C1| FROM (SELECT c1 FROM t1) x",
			want:  columns("C1"),
		},
		{
			name:  "json table column prefix keeps matching column",
			input: "SELECT jt.I| FROM t1, JSON_TABLE(t1.c1, '$.rows[*]' COLUMNS (id NUMBER PATH '$.id', name VARCHAR2(100) PATH '$.name')) jt",
			want:  columns("ID"),
		},
		{
			name:  "xmltable column prefix keeps matching column",
			input: "SELECT xt.N| FROM t1, XMLTABLE('/root/row' PASSING t1.c1 COLUMNS id NUMBER PATH 'id', name VARCHAR2(100) PATH 'name') xt",
			want:  columns("NAME"),
		},
		{
			name:  "inline external column prefix keeps matching column",
			input: "SELECT ext.A| FROM EXTERNAL ((a NUMBER, b VARCHAR2(10)) TYPE ORACLE_LOADER DEFAULT DIRECTORY data_dir LOCATION ('x.csv')) ext",
			want:  columns("A"),
		},
		{
			name:  "comment on schema qualified column offers columns",
			input: "COMMENT ON COLUMN schema1.t2.|",
			want:  columns("C1", "C2"),
		},
		{
			name:  "grant on schema qualified object offers tables",
			input: "GRANT SELECT ON schema1.|",
			want:  tables("T1", "T2"),
		},
		{
			name:  "revoke on schema qualified object offers tables",
			input: "REVOKE SELECT ON schema1.|",
			want:  tables("T1", "T2"),
		},
		{
			name:  "truncate schema qualified table reference offers tables",
			input: "TRUNCATE TABLE schema1.|",
			want:  tables("T1", "T2"),
		},
		{
			name:  "comment on schema qualified table reference offers tables",
			input: "COMMENT ON TABLE schema1.|",
			want:  tables("T1", "T2"),
		},
		{
			name:    "drop schema qualified view reference offers only views",
			input:   "DROP VIEW schema1.|",
			want:    views("V1"),
			notWant: append(tables("T1", "T2"), sequences("SEQ1", "USER_ID_SEQ")...),
		},
		{
			name:  "view schema qualified table reference offers views",
			input: "SELECT * FROM schema1.v|",
			want:  views("V1"),
		},
		{
			name:  "sequence schema qualified reference offers sequences",
			input: "DROP SEQUENCE schema1.|",
			want:  sequences("SEQ1", "USER_ID_SEQ"),
		},
		{
			name:    "sequence schema qualified reference uses non default schema",
			input:   "DROP SEQUENCE schema2.|",
			want:    sequences("SCHEMA2_SEQ"),
			notWant: sequences("SEQ1", "USER_ID_SEQ"),
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			result := completeOracleForTest(t, tt.input)
			for _, want := range tt.want {
				require.Truef(t, hasCompletionCandidate(result, want), "missing %s candidate %q in %v", want.typ, want.text, result)
			}
			for _, notWant := range tt.notWant {
				require.Falsef(t, hasCompletionCandidate(result, notWant), "unexpected %s candidate %q in %v", notWant.typ, notWant.text, result)
			}
		})
	}
}

func TestCompletionQuerySceneRestrictsTopLevelWrites(t *testing.T) {
	tests := []struct {
		name    string
		input   string
		want    []completionCandidateSpec
		notWant []completionCandidateSpec
	}{
		{
			name:    "statement start offers query keywords only",
			input:   "|",
			want:    keywords("SELECT", "WITH", "EXPLAIN"),
			notWant: keywords("INSERT", "UPDATE", "DELETE", "MERGE", "CREATE", "ALTER", "DROP", "TRUNCATE"),
		},
		{
			name:    "statement start prefix keeps matching query keyword",
			input:   "SEL|",
			want:    keywords("SELECT"),
			notWant: keywords("INSERT", "UPDATE", "CREATE", "DROP"),
		},
		{
			name:    "write statement does not offer object candidates",
			input:   "DROP TABLE |",
			notWant: tables("T1", "T2"),
		},
		{
			name:    "with prefixed write statement does not offer object candidates",
			input:   "WITH x AS (SELECT c1 FROM t1) INSERT INTO t1 SELECT * FROM |",
			notWant: tables("T1", "T2", "X"),
		},
		{
			name:  "select table reference still offers tables",
			input: "SELECT * FROM |",
			want:  tables("T1", "T2"),
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			result := completeOracleForTestWithScene(t, tt.input, base.SceneTypeQuery)
			for _, want := range tt.want {
				require.Truef(t, hasCompletionCandidate(result, want), "missing %s candidate %q in %v", want.typ, want.text, result)
			}
			for _, notWant := range tt.notWant {
				require.Falsef(t, hasCompletionCandidate(result, notWant), "unexpected %s candidate %q in %v", notWant.typ, notWant.text, result)
			}
		})
	}
}

func completionColumnTexts(candidates []base.Candidate) []string {
	var columns []string
	for _, candidate := range candidates {
		if candidate.Type == base.CandidateTypeColumn {
			columns = append(columns, candidate.Text)
		}
	}
	slices.Sort(columns)
	return columns
}

type completionCandidateSpec struct {
	text string
	typ  base.CandidateType
}

func completeOracleForTest(t *testing.T, input string) []base.Candidate {
	return completeOracleForTestWithScene(t, input, base.SceneTypeAll)
}

func completeOracleForTestWithScene(t *testing.T, input string, scene base.SceneType) []base.Candidate {
	t.Helper()
	text, caretLine, caretOffset := catchCaret(input)
	result, err := base.Completion(context.Background(), storepb.Engine_ORACLE, base.CompletionContext{
		Scene:             scene,
		DefaultDatabase:   "SCHEMA1",
		Metadata:          getMetadataForTest,
		ListDatabaseNames: listDatabaseNamesForTest,
	}, text, caretLine, caretOffset)
	require.NoError(t, err)
	return result
}

func hasCompletionCandidate(candidates []base.Candidate, want completionCandidateSpec) bool {
	for _, candidate := range candidates {
		if candidate.Type == want.typ && candidate.Text == want.text {
			return true
		}
	}
	return false
}

func columns(texts ...string) []completionCandidateSpec {
	return completionSpecs(base.CandidateTypeColumn, texts...)
}

func tables(texts ...string) []completionCandidateSpec {
	return completionSpecs(base.CandidateTypeTable, texts...)
}

func views(texts ...string) []completionCandidateSpec {
	return completionSpecs(base.CandidateTypeView, texts...)
}

func schemas(texts ...string) []completionCandidateSpec {
	return completionSpecs(base.CandidateTypeSchema, texts...)
}

func sequences(texts ...string) []completionCandidateSpec {
	return completionSpecs(base.CandidateTypeSequence, texts...)
}

func keywords(texts ...string) []completionCandidateSpec {
	return completionSpecs(base.CandidateTypeKeyword, texts...)
}

func completionSpecs(typ base.CandidateType, texts ...string) []completionCandidateSpec {
	specs := make([]completionCandidateSpec, 0, len(texts))
	for _, text := range texts {
		specs = append(specs, completionCandidateSpec{text: text, typ: typ})
	}
	return specs
}

func listDatabaseNamesForTest(_ context.Context, _ string) ([]string, error) {
	return []string{"SCHEMA1", "SCHEMA2", "SCHEMA3"}, nil
}

func getMetadataForTest(_ context.Context, _, databaseName string) (string, *model.DatabaseMetadata, error) {
	switch databaseName {
	case "SCHEMA1":
		return "SCHEMA1", model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
			Name: databaseName,
			Schemas: []*metadatapb.SchemaMetadata{
				{
					Name: "",
					Tables: []*metadatapb.TableMetadata{
						{
							Name: "T1",
							Columns: []*metadatapb.ColumnMetadata{
								{
									Name: "C1",
									Type: "int",
								},
							},
						},
						{
							Name: "T2",
							Columns: []*metadatapb.ColumnMetadata{
								{
									Name: "C1",
									Type: "int",
								},
								{
									Name: "C2",
									Type: "int",
								},
							},
						},
					},
					Views: []*metadatapb.ViewMetadata{
						{
							Name: "V1",
							Definition: `CREATE VIEW v1 AS
											SELECT *
											FROM t1
							`,
						},
					},
					Sequences: []*metadatapb.SequenceMetadata{
						{
							Name: "SEQ1",
						},
						{
							Name: "USER_ID_SEQ",
						},
					},
				},
			},
		}, nil, nil, storepb.Engine_ORACLE, true /* isObjectCaseSensitive */), nil
	case "SCHEMA2":
		return "SCHEMA2", model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
			Name: databaseName,
			Schemas: []*metadatapb.SchemaMetadata{
				{
					Name: "",
					Tables: []*metadatapb.TableMetadata{
						{
							Name: "T1",
							Columns: []*metadatapb.ColumnMetadata{
								{
									Name: "C1",
									Type: "int",
								},
							},
						},
						{
							Name: "T2",
							Columns: []*metadatapb.ColumnMetadata{
								{
									Name: "C1",
									Type: "int",
								},
								{
									Name: "C2",
									Type: "int",
								},
							},
						},
					},
					Views: []*metadatapb.ViewMetadata{
						{
							Name: "V1",
							Definition: `CREATE VIEW v1 AS
											SELECT *
											FROM t1
							`,
						},
					},
					Sequences: []*metadatapb.SequenceMetadata{
						{
							Name: "SCHEMA2_SEQ",
						},
					},
				},
			},
		}, nil, nil, storepb.Engine_ORACLE, true /* isObjectCaseSensitive */), nil
	case "SCHEMA3":
		return "SCHEMA3", model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
			Name: databaseName,
			Schemas: []*metadatapb.SchemaMetadata{
				{
					Name: "",
					Tables: []*metadatapb.TableMetadata{
						{
							Name: "T1",
							Columns: []*metadatapb.ColumnMetadata{
								{
									Name: "C1",
									Type: "int",
								},
							},
						},
						{
							Name: "T2",
							Columns: []*metadatapb.ColumnMetadata{
								{
									Name: "C1",
									Type: "int",
								},
								{
									Name: "C2",
									Type: "int",
								},
							},
						},
					},
					Views: []*metadatapb.ViewMetadata{
						{
							Name: "V1",
							Definition: `CREATE VIEW v1 AS
											SELECT *
											FROM t1
							`,
						},
					},
				},
			},
		}, nil, nil, storepb.Engine_ORACLE, true /* isObjectCaseSensitive */), nil
	case "SCHEMA4":
		return "SCHEMA4", model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
			Name: databaseName,
			Schemas: []*metadatapb.SchemaMetadata{
				{
					Name: "",
					Tables: []*metadatapb.TableMetadata{
						{
							Name: "T1",
							Columns: []*metadatapb.ColumnMetadata{
								{
									Name: "C1",
									Type: "int",
								},
								{
									Name: "SCHEMA4_ONLY",
									Type: "int",
								},
							},
						},
					},
				},
			},
		}, nil, nil, storepb.Engine_ORACLE, true /* isObjectCaseSensitive */), nil
	default:
		return "", nil, nil
	}
}

func catchCaret(s string) (string, int, int) {
	line := 1
	column := 0
	for i, c := range s {
		switch c {
		case '|':
			return s[:i] + s[i+1:], line, column
		case '\n':
			line++
			column = 0
		default:
			column++
		}
	}
	return s, -1, -1
}

func catchCaretUTF16(s string) (string, int, int) {
	line := 1
	column := 0
	for i, c := range s {
		switch c {
		case '|':
			return s[:i] + s[i+1:], line, column
		case '\n':
			line++
			column = 0
		default:
			if c > 0xFFFF {
				column += 2
			} else {
				column++
			}
		}
	}
	return s, -1, -1
}