fix(editor): suggest columns for derived-table and CTE aliases in autocomplete#1712
Conversation
![chatgpt-codex-connector[bot]](https://avatars.githubusercontent.com/in/1144995?s=60&v=4){kind=small}
There was a problem hiding this comment.
💡 Codex Review
Here are some automated review suggestions for this pull request.
Reviewed commit: 2cad9d004c
ℹ️ About Codex in GitHub
Your team has set up Codex to review pull requests in this repo. Reviews are triggered when you
- Open a pull request for review
- Mark a draft as ready
- Comment "@codex review".
If Codex has suggestions, it will comment; otherwise it will react with 👍.
Codex can also answer questions or update the PR. Try commenting "@codex address that feedback".
| let end = matchingQuote(in: ns, from: index, limit: limit, quote: c) | ||
| guard end > index + 1 else { return nil } | ||
| return ns.substring(with: NSRange(location: index + 1, length: end - index - 1)) |
There was a problem hiding this comment.
When the SELECT list or derived-table alias is backtick- or double-quoted, matchingQuote returns the index after the closing quote, but this substring length treats that value as the closing-quote index. For inputs like SELECT AVG(score) AS avg_score the derived column becomes `avg_score instead of avg_score, so autocomplete returns an invalid label; quoted derived aliases similarly fail to match the user's qualifier.
Useful? React with 👍 / 👎.
| if kind == "FROM" || kind == "JOIN" || preceding.text == "," { | ||
| return parseDerived(in: ns, subOpen: open, subClose: close, limit: limit) |
There was a problem hiding this comment.
Treating any (SELECT ...) preceded by a comma as a derived table also matches scalar subqueries in the SELECT list, e.g. SELECT name, (SELECT id FROM orders) AS order_id FROM users. In that case order_id is a column alias, not a table alias, but it gets added to tableReferences, so order_id. and unqualified column completion can suggest columns from the scalar subquery that are not valid in the outer scope.
Useful? React with 👍 / 👎.
There was a problem hiding this comment.
💡 Codex Review
Here are some automated review suggestions for this pull request.
Reviewed commit: 56b232514e
ℹ️ About Codex in GitHub
Your team has set up Codex to review pull requests in this repo. Reviews are triggered when you
- Open a pull request for review
- Mark a draft as ready
- Comment "@codex review".
If Codex has suggestions, it will comment; otherwise it will react with 👍.
Codex can also answer questions or update the PR. Try commenting "@codex address that feedback".
| } | ||
| guard cursor < limit, let alias = readAliasForward(in: ns, at: cursor, limit: limit) else { return nil } | ||
| guard !Self.nonAliasKeywords.contains(alias.uppercased()) else { return nil } | ||
| return DerivedTable(alias: alias, columns: columns) |
There was a problem hiding this comment.
When a derived table supplies a column alias list after the table alias, e.g. FROM (SELECT id, name FROM users) AS u(user_id, display_name), SQL exposes user_id and display_name, not the inner select-list names. This path always returns the parsed select-list columns and never looks for the optional alias column list after u, so u. completions suggest invalid column names for a valid query shape.
Useful? React with 👍 / 👎.
| for derived in derivedTables where present.insert(derived.alias.lowercased()).inserted { | ||
| references.append( | ||
| TableReference(tableName: derived.alias, alias: derived.alias, derivedColumns: derived.columns) |
There was a problem hiding this comment.
When a statement defines a CTE but does not reference it in the current FROM/JOIN, this loop still appends it as a TableReference with derivedColumns. For a query like WITH unused AS (SELECT secret FROM users) SELECT * FROM orders o WHERE |, column completion sees both orders and unused, so it can suggest unused.secret even though the CTE is only available as a table operand and is not in the query's range table.
Useful? React with 👍 / 👎.
|
Preview deployment for your docs. Learn more about Mintlify Previews.
💡 Tip: Enable Workflows to automatically generate PRs for you. |
There was a problem hiding this comment.
💡 Codex Review
Here are some automated review suggestions for this pull request.
Reviewed commit: e99664f251
ℹ️ About Codex in GitHub
Your team has set up Codex to review pull requests in this repo. Reviews are triggered when you
- Open a pull request for review
- Mark a draft as ready
- Comment "@codex review".
If Codex has suggestions, it will comment; otherwise it will react with 👍.
Codex can also answer questions or update the PR. Try commenting "@codex address that feedback".
| if let derived = context.tableReferences.first(where: { | ||
| $0.isDerived && $0.identifier.caseInsensitiveCompare(dotPrefix) == .orderedSame | ||
| }), let columns = derived.derivedColumns, !columns.isEmpty { |
There was a problem hiding this comment.
When a derived table has no parsed columns, such as FROM (SELECT * FROM users) d, derivedColumns is an empty array, so this condition skips the derived match and falls through to resolveAlias. Because derived references are stored with tableName == alias, d. can then fetch and show columns from a real base table named d, even though d is a derived table whose columns this parser intentionally skipped. Return immediately for a derived match even when the column list is empty.
Useful? React with 👍 / 👎.
| for key in [ref.tableName.lowercased(), ref.identifier.lowercased()] { | ||
| guard let columns = derivedColumnsByAlias[key] else { continue } | ||
| consumed.insert(key) | ||
| references[index] = TableReference( | ||
| tableName: ref.tableName, alias: ref.alias, schema: ref.schema, derivedColumns: columns |
There was a problem hiding this comment.
This matches every parsed CTE/derived name against both the table operand and the effective alias, so a query with WITH c AS (...) SELECT c.| FROM users c rewrites the in-scope users c reference as derived and c. suggests the CTE columns instead of users columns. That is separate from appending unused CTEs later: the actual table reference is mutated here before the append path. CTE columns should attach only when the referenced table name is the CTE, not when an unrelated table alias happens to share the CTE name.
Useful? React with 👍 / 👎.
1 participant
Fixes #1697.
Problem
Typing
alias.for a derived table (subquery with an alias) returned no column suggestions. Ordinary table aliases worked, butLEFT JOIN (SELECT ...) ahsdid not, soahs.suggested nothing. CTEs had the same gap.Root cause
Two layers, so a regex tweak alone would not fix it:
fromListRegexstops at the first(, and the JOIN regex requires an identifier right afterJOIN, soJOIN (SELECT ...) ahsmatched nothing.resolveAliasonly maps an alias to a real schema table; a derived table's columns live in the subquery's SELECT list, which the schema knows nothing about.The same class of bug already affected CTEs (
WITH cte AS (SELECT ...)).Fix
A small model extension, not a symptom patch:
DerivedTableParser(new): one paren/string/comment-aware scan that extracts each FROM/JOIN subquery and CTE with the column names its SELECT list produces. Resolves explicitASnames, bare and qualified columns; skipsSELECT *and unaliased expressions (no reliable name). Honors explicit CTE column lists (WITH c(a, b) AS ...). Uses O(1) NSString access.TableReferencegains an optionalderivedColumnssource (isDerived).SQLContextAnalyzer.analyzemerges parsed derived tables into the in-scope references; a derived alias wins over a plain reference of the same name (so a CTE used directly inFROMresolves).SQLCompletionProviderserves derived columns in thealias.path;SQLSchemaProvider.allColumnsInScopeincludes them in unqualified completion too.This matches DataGrip and DBeaver's semantic engine, which parse the subquery output columns rather than only real tables.
Tests
DerivedTableParserTests: the exact issue query, FROM/JOIN/comma-list derived tables, qualified columns,ASrenames,SELECT *(empty), nested subqueries, CTEs and explicit column lists, quoted aliases, and non-matches (IN (SELECT ...), function calls).SQLContextAnalyzerTests: derived-table and CTE references carry their columns.CompletionEngineTests: end-to-endahs.and CTEt.return the subquery's columns.Known limitation
A
SELECT *subquery and unaliased expressions likeAVG(score)have no name to suggest, so they are skipped (documented). This is where DataGrip and DBeaver also degrade.Docs / CHANGELOG
docs/features/autocomplete.mdxgains a "Derived Tables and CTEs" example; CHANGELOG updated under Fixed.