[Feat]: SQL autocomplete — resolve CTEs and derived tables as column sources

Closed
  • Is something wrong?
#506 0 comments 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
55/100
Issue type
Feature
Clarity
Clearly specified
Activity status
Quiet
Tech stack
sql, typescript
Domain
database, developer-experience

Research direction

Start by reading src/utils/sqlContext.ts for the existing parenthesis-matching and string/comment-skipping helpers, then inspect src/utils/autocomplete.ts to understand completion context and column lookup. Implement and test parsing in src/utils/derivedTables.ts with tests/utils/derivedTables.test.ts, then add provider integration coverage in tests/utils/autocomplete.test.ts. Done means CTEs and derived-table columns resolve in scope, explicit column lists take precedence, and inner-table columns do not leak into the outer scope.

Written by the indexing model from the issue text.

Description

Problem

Follow-up to #505. The completion provider only knows about physical tables, so anything defined inside the query itself is invisible to it:

WITH recent AS (SELECT id, occurred_at AS ts FROM events)
SELECT recent.| FROM recent

Typing recent. completes nothing — the dot trigger resolves the alias through get_columns, which asks the database, and the database has never heard of recent. Same story for derived tables:

SELECT x.| FROM (SELECT a, b FROM t) x

Both should complete id/ts and a/b respectively. This is the biggest functional gap left after #505, and it bites hardest exactly in the queries where autocomplete would help most.

There's also a related scope leak this fixes for free (see Integration below): today SELECT | FROM (SELECT ... FROM inner_t) x offers inner_t columns at the outer level, because the outer parse still sees the raw subquery text.

Design sketch

New module src/utils/derivedTables.ts — pure functions, no Monaco/Tauri imports, so it can be unit-tested exhaustively like sqlContext.ts:

parseDerivedTables(statementText: string): DerivedTable[]

interface DerivedTable {
  name: string
  columns: DerivedColumn[]
  bodyStart: number  // offsets of the inner SELECT, for scope checks
  bodyEnd: number
}

interface DerivedColumn {
  name: string
  sourceExpression?: string
}

It needs to detect two shapes:

  • CTE headers: WITH [RECURSIVE] name [(col, ...)] AS ( ... ), including comma-separated lists of CTEs.
  • Derived tables in FROM/JOIN: FROM ( SELECT ... ) [AS] alias [(col, ...)].

Column names come from the inner SELECT list, split on top-level commas (commas inside parens/strings don't count):

  • expr AS name → name; bare alias expr name → name
  • plain reference t.col or col → col
  • expression with no alias (function call, arithmetic) → no derivable name, skip it
  • * → optionally fall back to get_columns on the underlying table; fine to punt on this in a first pass
  • an explicit column list on the alias (x(a, b)) always wins over anything derived from the body

sqlContext.ts already has the paren-matching and string/comment-skipping machinery — extract shared helpers rather than duplicating that logic (getting doubled-quote escapes right twice is asking for drift).

Integration in autocomplete.ts

  • Build a Map<string, DerivedTable> per completion request from the current statement.
  • On dot trigger, check that map before falling through to physical tables via get_columns. Detail label along the lines of CTE recent so it's obvious where the column comes from.
  • For context columns (non-dot suggestions), treat in-scope derived tables as column sources next to the physical FROM tables.
  • Nested case: a CTE body referencing an earlier CTE (WITH a AS (...), b AS (SELECT ... FROM a)) — resolve iteratively in declaration order, each CTE seeing only the ones declared before it.
  • The outer-scope leak: once the derived table replaces the raw subquery text in the outer parse, inner_t naturally disappears from the outer scope. Worth an explicit test so it doesn't regress.

Tests

tests/utils/derivedTables.test.ts should cover: single CTE, multiple comma-separated CTEs, RECURSIVE, explicit column lists, both alias forms (AS name / bare), unnamed expressions, *, a CTE referencing a previous CTE, and quoted identifiers in all three styles. Plus provider-level integration tests in tests/utils/autocomplete.test.ts: dot trigger on a CTE alias, context columns inside vs. outside the CTE body, and the outer-scope leak case above.

Effort

Noticeably bigger than the subquery-scoping commit in #505 — roughly 2–3×. The top-level SELECT-list splitter is the fiddly part; everything else is bookkeeping.

Dominant language
TypeScript
Stars
5.1k
Forks
335
Avg merge
1d 11h
Merged PRs (30d)
280

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from TabularisDB/tabularis

All issues in TabularisDB/tabularis

Similar issues

More TypeScript issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.