PG Completion Scenarios

Goal: Comprehensive test coverage for PostgreSQL autocompletion across all SQL contexts Verification: go test -v -count=1 github.com/bytebase/bytebase/backend/plugin/parser/pg -run ^TestCompletion$ Reference sources: PostgreSQL documentation, omni parser grammar rules, completion.go source code

Status: [ ] pending, [x] passing, [~] partial (needs upstream change)

---

Phase 1: Foundation Gaps

Fill missing coverage for features already partially tested.

1.1 Join Variants

• SELECT | FROM t1 RIGHT JOIN t2 ON t1.c1 = t2.c1 — columns from both tables visible in RIGHT JOIN
• SELECT | FROM t1 FULL OUTER JOIN t2 ON t1.c1 = t2.c1 — columns from both tables visible in FULL OUTER JOIN
• SELECT | FROM t1 CROSS JOIN t2 — columns from both tables visible in CROSS JOIN (no ON clause)
• SELECT | FROM t1 NATURAL JOIN t2 — columns from both tables visible in NATURAL JOIN
• SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 = t2.| — qualified column in RIGHT JOIN ON condition
• SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.c1 = t2.| — qualified column in FULL OUTER JOIN ON condition
• SELECT * FROM t1 CROSS JOIN t2 WHERE t1.| — qualified column in WHERE after CROSS JOIN
• SELECT * FROM t1 JOIN t2 USING (|) — columns common to both tables in USING clause
• SELECT | FROM t1 JOIN t2 ON t1.c1 = t2.c1 LEFT JOIN test.auto ON t2.c1 = test.auto.id — mixed join types with cross-schema
• SELECT a.| FROM t1 a NATURAL JOIN t2 b — alias-qualified columns after NATURAL JOIN

1.2 Advanced WHERE Expressions

• SELECT * FROM t2 WHERE c1 BETWEEN | AND c2 — completion after BETWEEN
• SELECT * FROM t2 WHERE c1 BETWEEN c2 AND | — completion after AND in BETWEEN
• SELECT * FROM test.auto WHERE name LIKE | — completion after LIKE
• SELECT * FROM t2 WHERE c1 IS NOT NULL AND | — completion after IS NOT NULL condition
• SELECT * FROM t2 WHERE c1 IS NULL OR | — completion after IS NULL condition
• SELECT * FROM t2 WHERE NOT | — completion after NOT
• SELECT * FROM t2 WHERE c1 > | — completion after comparison operator
• SELECT * FROM t2 WHERE c1 + | > 0 — completion in arithmetic expression
• SELECT * FROM t2 WHERE c1 IN (1, 2) AND | — completion after IN list
• SELECT * FROM t1 WHERE c1 = (SELECT MAX(|) FROM t2) — completion inside aggregate in scalar subquery
• SELECT * FROM t1 x WHERE x.c1 IN (SELECT c1 FROM t2 WHERE |) — completion in correlated subquery WHERE
• SELECT * FROM t2 WHERE CASE WHEN c1 > 0 THEN | END = 1 — completion in CASE WHEN THEN

1.3 Advanced SELECT Features

• SELECT DISTINCT | FROM t2 — columns after DISTINCT
• SELECT DISTINCT ON (|) c1, c2 FROM t2 — columns in DISTINCT ON
• SELECT | FROM t2 LIMIT 10 — columns in SELECT with LIMIT present
• SELECT * FROM t2 LIMIT | — completion in LIMIT expression
• SELECT * FROM t2 OFFSET | — completion in OFFSET expression
• SELECT * FROM t2 ORDER BY c1 LIMIT | — completion in LIMIT after ORDER BY
• SELECT c1, | FROM t2 — completion for second column in select list
• DELETE FROM t2 WHERE c1 > 0 RETURNING | — completion in RETURNING clause (DML)
• UPDATE t2 SET c1 = 1 RETURNING | — completion in UPDATE RETURNING clause
• SELECT c1, SUM(c2) OVER (PARTITION BY |) FROM t2 — column in window PARTITION BY
• SELECT c1, SUM(c2) OVER (ORDER BY |) FROM t2 — column in window ORDER BY
• SELECT c1, ROW_NUMBER() OVER (PARTITION BY | ORDER BY c1) FROM t2 — column in window function
• SELECT COALESCE(|, 0) FROM t2 — completion in COALESCE first argument
• SELECT COALESCE(c1, |) FROM t2 — completion in COALESCE second argument
• SELECT NULLIF(|, 0) FROM t2 — completion in NULLIF
• SELECT CAST(| AS int) FROM t2 — completion in CAST expression
• SELECT | FROM t2 FOR UPDATE — columns with FOR UPDATE locking clause

1.4 Object Type Coverage

• SELECT * FROM | with foreign tables in metadata — foreign tables appear in FROM completion
• SELECT * FROM | with materialized views in metadata — materialized views appear in FROM completion
• SELECT * FROM public.| with foreign tables — foreign tables in schema-qualified FROM
• SELECT * FROM public.| with materialized views — materialized views in schema-qualified FROM
• SELECT mv1.| FROM mv1 — columns from materialized view (qualified)
• SELECT | FROM mv1 — columns from materialized view (unqualified)
• SELECT ft1.| FROM ft1 — columns from foreign table (qualified)
• SELECT | FROM ft1 — columns from foreign table (unqualified)
• INSERT INTO | with foreign tables — foreign tables in INSERT target
• [~] UPDATE | with materialized views — materialized views should NOT appear in UPDATE target (they are read-only; completion engine uses same relation_expr rule for SELECT/UPDATE/DELETE and doesn't distinguish context)

---

Phase 2: DDL Contexts

Completion in DDL statements. Independent of Phase 1 — can run in parallel. Many scenarios likely need completion.go changes or omni parser grammar support; mark [~] if blocked.

2.1 CREATE Statements

• CREATE TABLE test_tbl (id int, FOREIGN KEY (id) REFERENCES |) — table completion in FK reference
• CREATE TABLE test_tbl (id int, FOREIGN KEY (id) REFERENCES public.|) — schema-qualified FK reference
• CREATE TABLE test_tbl (id int REFERENCES |) — inline FK reference
• CREATE TABLE test_tbl (id int REFERENCES t1(|)) — column completion in FK reference
• CREATE INDEX idx ON | — table completion in CREATE INDEX
• CREATE INDEX idx ON public.| — schema-qualified table in CREATE INDEX
• CREATE INDEX idx ON t1 (|) — column completion in index expression
• CREATE INDEX idx ON t2 (c1, |) — second column in composite index
• CREATE VIEW v2 AS SELECT | FROM t1 — completion in CREATE VIEW body
• CREATE VIEW v2 AS SELECT * FROM | — FROM completion in CREATE VIEW body
• CREATE MATERIALIZED VIEW mv AS SELECT | FROM t1 — completion in CREATE MATERIALIZED VIEW body
• CREATE TABLE test_tbl AS SELECT | FROM t1 — completion in CREATE TABLE AS SELECT
• CREATE TABLE test_tbl (LIKE |) — table completion in LIKE clause
• CREATE TRIGGER trig AFTER INSERT ON | — table completion in CREATE TRIGGER
• CREATE TRIGGER trig AFTER INSERT ON public.| — schema-qualified table in CREATE TRIGGER

2.2 ALTER Statements

• ALTER TABLE | — table completion in ALTER TABLE
• ALTER TABLE public.| — schema-qualified table in ALTER TABLE
• ALTER TABLE t1 ADD COLUMN c2 int REFERENCES | — FK reference in ALTER ADD COLUMN
• ALTER TABLE t1 DROP COLUMN | — column completion for existing columns
• ALTER TABLE t1 RENAME COLUMN | TO new_name — column completion in RENAME
• ALTER TABLE t1 ALTER COLUMN | — column completion in ALTER COLUMN
• ALTER TABLE t1 ALTER COLUMN | SET NOT NULL — column completion with SET NOT NULL
• ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (|) REFERENCES t2 — column in FK constraint
• ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (c1) REFERENCES t2(|) — referenced column
• ALTER INDEX | RENAME TO new_name — index completion in ALTER INDEX
• ALTER VIEW | — view completion in ALTER VIEW
• ALTER VIEW public.| — schema-qualified view in ALTER VIEW
• ALTER SEQUENCE | — sequence completion in ALTER SEQUENCE
• ALTER SEQUENCE public.| — schema-qualified sequence
• ALTER MATERIALIZED VIEW | — materialized view completion

2.3 DROP and TRUNCATE Statements

• DROP TABLE | — table completion in DROP TABLE
• DROP TABLE public.| — schema-qualified table in DROP
• DROP TABLE IF EXISTS | — table completion after IF EXISTS
• DROP VIEW | — view completion in DROP VIEW
• DROP VIEW public.| — schema-qualified view in DROP VIEW
• DROP INDEX | — index completion in DROP INDEX
• DROP SEQUENCE | — sequence completion in DROP SEQUENCE
• DROP MATERIALIZED VIEW | — materialized view in DROP
• TRUNCATE | — table completion in TRUNCATE
• TRUNCATE public.| — schema-qualified table in TRUNCATE

2.4 COMMENT and GRANT Statements

• COMMENT ON TABLE | — table completion in COMMENT ON
• COMMENT ON TABLE public.| — schema-qualified table in COMMENT ON
• COMMENT ON COLUMN t1.| — column completion in COMMENT ON COLUMN
• COMMENT ON COLUMN public.t1.| — schema-qualified column in COMMENT ON COLUMN
• GRANT SELECT ON | — table completion in GRANT
• GRANT SELECT ON public.| — schema-qualified table in GRANT
• GRANT ALL ON ALL TABLES IN SCHEMA | — schema completion in GRANT
• REVOKE SELECT ON | — table completion in REVOKE
• REVOKE SELECT ON public.| — schema-qualified table in REVOKE
• GRANT USAGE ON SEQUENCE | — sequence completion in GRANT

---

Phase 3: Advanced Query Patterns

Complex query structures and nesting. Independent of Phase 2. Sections 3.1 and 3.2 may need completion.go scope-tracking changes.

3.1 Recursive CTEs and CTEs in DML

• [~] WITH RECURSIVE x AS (SELECT c1 FROM t1 UNION ALL SELECT | FROM x JOIN t1 ON x.c1 = t1.c1) SELECT * FROM x — completion in recursive branch (only t1 columns resolve; recursive self-reference x does not contribute its own columns)
• [~] WITH RECURSIVE x AS (SELECT c1 FROM t1 UNION ALL SELECT x.| FROM x JOIN t1 ON x.c1 = t1.c1) SELECT * FROM x — qualified columns from recursive CTE reference (no candidates returned; recursive self-reference columns not resolvable)
• WITH x AS (SELECT * FROM t2) INSERT INTO t1 SELECT | FROM x — CTE used in INSERT...SELECT
• WITH x AS (SELECT c1, c2 FROM t2) UPDATE t1 SET c1 = (SELECT | FROM x) — CTE in UPDATE scalar subquery
• WITH x AS (SELECT * FROM t2) DELETE FROM t1 WHERE c1 IN (SELECT | FROM x) — CTE in DELETE subquery
• [~] WITH a AS (SELECT c1 FROM t1), b AS (SELECT * FROM a) SELECT | FROM b — chained CTEs (CTE referencing another CTE) (b's columns not resolved; query span cannot resolve SELECT * FROM a where a is a CTE)
• WITH a AS (SELECT c1 FROM t1), b AS (SELECT a.| FROM a) SELECT * FROM b — qualified column in chained CTE
• WITH x AS (SELECT c1 FROM t1) SELECT | FROM x, t2 — CTE mixed with regular table in FROM
• WITH x AS (SELECT c1 FROM t1) SELECT x.| FROM x JOIN t2 ON x.c1 = t2.c1 — CTE qualified columns in JOIN
• WITH x(a, b) AS (SELECT c1, c2 FROM t2) SELECT | FROM x JOIN t1 ON x.a = t1.c1 — named CTE columns in JOIN

3.2 LATERAL Joins and Table Functions

• SELECT * FROM t1, LATERAL (SELECT | FROM t2 WHERE t2.c1 = t1.c1) sub — LATERAL subquery referencing outer table
• SELECT sub.| FROM t1, LATERAL (SELECT c1, c2 FROM t2 WHERE t2.c1 = t1.c1) sub — columns from LATERAL subquery alias
• SELECT | FROM t1 LEFT JOIN LATERAL (SELECT * FROM t2 WHERE t2.c1 = t1.c1) sub ON true — LATERAL with LEFT JOIN
• SELECT * FROM t1, LATERAL (SELECT t1.| FROM t2) sub — outer table column access in LATERAL
• SELECT * FROM t2 x, LATERAL (SELECT x.| FROM t1) sub — alias-qualified outer reference in LATERAL
• SELECT | FROM generate_series(1, 10) g — table function in FROM clause
• [~] SELECT g.| FROM generate_series(1, 10) g — qualified column from table function alias (no candidates returned; table functions have no column metadata in the completion engine)
• SELECT | FROM t1, generate_series(1, 10) g — table function with regular table
• SELECT | FROM t2 x, LATERAL (SELECT * FROM t1 WHERE t1.c1 = x.c1) sub — complex LATERAL with alias

3.3 Nested Subqueries and Expressions

• SELECT (SELECT | FROM t1) FROM t2 — scalar subquery in SELECT list
• SELECT (SELECT t1.| FROM t1) FROM t2 — qualified column in scalar subquery
• SELECT * FROM t1 WHERE c1 = ANY(SELECT | FROM t2) — ANY subquery
• SELECT * FROM t1 WHERE c1 = ALL(SELECT | FROM t2) — ALL subquery
• SELECT * FROM t1 WHERE c1 > (SELECT MAX(|) FROM t2) — aggregate in scalar subquery
• SELECT * FROM (SELECT * FROM (SELECT | FROM t1) a) b — triple-nested subquery
• SELECT b.| FROM (SELECT * FROM (SELECT c1 FROM t1) a) b — qualified column from triple-nested
• SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.c1 = t1.|) — correlated subquery referencing outer column
• SELECT | FROM (SELECT c1, c2 FROM t2 UNION SELECT c1, c2 FROM t2) sub — subquery with UNION
• SELECT sub.| FROM (SELECT c1, c2 FROM t2 UNION SELECT c1, c2 FROM t2) sub — qualified columns from UNION subquery

3.4 Set Operations

• SELECT c1 FROM t1 INTERSECT SELECT | FROM t2 — SELECT list in INTERSECT
• SELECT c1 FROM t1 EXCEPT SELECT | FROM t2 — SELECT list in EXCEPT
• SELECT c1 FROM t1 UNION SELECT c1 FROM t1 UNION SELECT | FROM t2 — triple UNION
• (SELECT c1 FROM t1) UNION (SELECT | FROM t2) — parenthesized set operation
• SELECT c1 FROM t1 UNION ALL SELECT c1 FROM t2 ORDER BY | — ORDER BY on UNION result
• SELECT * FROM (SELECT c1 FROM t1 UNION SELECT c1 FROM t2) x WHERE x.| — WHERE on UNION subquery
• WITH x AS (SELECT c1 FROM t1 UNION SELECT c1 FROM t2) SELECT x.| FROM x — CTE with UNION body
• SELECT c1 FROM t1 EXCEPT ALL SELECT | FROM t2 — EXCEPT ALL variant
• SELECT c1 FROM t1 INTERSECT ALL SELECT | FROM t2 — INTERSECT ALL variant
• SELECT | FROM t2 UNION SELECT * FROM t1 — first branch of UNION

---

Phase 4: Edge Cases & Robustness

Identifier handling, error recovery, and special scenarios.

4.1 Quoted Identifiers and Reserved Keywords

• SELECT | FROM "t1" — quoted table name in FROM (should resolve t1 columns)
• SELECT "t1".| FROM t1 — quoted table qualifier
• SELECT * FROM "public".| — quoted schema name
• SELECT * FROM | with table named using reserved keyword (metadata has table named "order") — reserved keyword table appears quoted
• SELECT | FROM "order" — columns from reserved-keyword-named table
• SELECT "order".| FROM "order" — qualified columns from reserved-keyword table
• SELECT * FROM | with mixed-case table name (metadata has "MyTable") — mixed-case name appears quoted
• SELECT | FROM "MyTable" — columns from mixed-case table
• SELECT * FROM | with table containing special chars (metadata has "my-table") — special char name appears quoted
• SELECT * FROM public."t1" WHERE | — columns after quoted schema-qualified table

4.2 Partial Prefix Completion

• SELECT * FROM t| — partial table name prefix "t" matches t1, t2
• SELECT * FROM public.t| — partial schema-qualified prefix
• SELECT t1.c| — partial column name prefix
• SELECT * FROM test.a| — partial prefix in non-default schema
• SELECT * FROM tes| — partial schema name prefix
• SELECT * FROM t1 WHERE t1.c| — partial column in WHERE
• INSERT INTO t| — partial table name in INSERT
• UPDATE t| — partial table name in UPDATE
• DELETE FROM t| — partial table name in DELETE
• SELECT * FROM t1 JOIN t| — partial table name in JOIN

4.3 Multi-Statement and Error Recovery

• SELECT 1; INSERT INTO | — completion in second statement (INSERT)
• SELECT 1; UPDATE | — completion in second statement (UPDATE)
• SELECT 1; DELETE FROM | — completion in second statement (DELETE)
• INVALID SQL; SELECT * FROM | — recovery after invalid first statement
• SELECT * FROM t1; SELECT * FROM t2 WHERE | — columns from correct table in second statement
• SELECT; SELECT | FROM t1 — recovery after incomplete SELECT
• SELECT * FROM t1 WHERE; SELECT | FROM t2 — recovery after incomplete WHERE
• CREATE TABLE x (id int); SELECT | FROM t1 — DDL then DML multi-statement

4.4 Whitespace and Formatting Variations

• Multi-line SELECT: SELECT\n |\nFROM t1 — completion works across line breaks
• Multi-line FROM: SELECT *\nFROM\n | — FROM on separate line
• Tab-indented: SELECT\t|\tFROM t1 — tab characters in SQL
• Extra whitespace: SELECT * FROM | — multiple spaces between tokens
• Trailing whitespace: SELECT * FROM t1 WHERE | — space after cursor
• Comment before cursor: SELECT * FROM /* comment */ | — completion after block comment
• Line comment: SELECT * FROM t1 -- comment\nWHERE | — completion after line comment