Support SQL table aliases consistently across SELECT queries

[tomatolog]

Proposal:

Create a separate ticket for general SQL table alias support.

The current fix/dbeaver-table-alias branch is acceptable as a narrow DBeaver compatibility fix: it handles the specific single-table SELECT-list shape DBeaver emits, for example:

SELECT p.id, p.name FROM products p ORDER BY id ASC;
SELECT `p`.id, `p`.name FROM products AS `p` ORDER BY id ASC;
SELECT p.* FROM Manticore.products AS p LIMIT 0, 200;

However, it should not be treated as general table alias support. The branch introduces opt_table_alias, table_alias_ident, and SetTableAlias(), but the implementation only rewrites already-collected m_pQuery->m_dItems. Aliases in WHERE, GROUP BY, HAVING, ORDER BY, JOIN table names, JOIN ON expressions, FACET, OPTION table qualifiers, and query logging are not generally resolved.

Why A Separate Ticket

General SQL table aliases are a broader parser and semantic-resolution feature than the current DBeaver fix.

The current branch is intentionally narrow and low-risk:

• It fixes the failing DBeaver SELECT-list query from PR 4695.
• It does not alter JOIN planning.
• It does not add alias maps to expression parsing.
• It avoids rewriting existing WHERE/ORDER/GROUP/HAVING behavior.

Expanding it inside PR 4695 would change the scope from "make DBeaver table browsing work" to "add SQL alias resolution across query clauses". That is likely too much for this branch and should get its own design, regression matrix, and review.

Current Evidence

DBeaver Branch Behavior

Current branch support is SELECT-list only:

SELECT p.id, p.name FROM products p ORDER BY id ASC;
SELECT `p`.id, `p`.name FROM products AS `p` ORDER BY id ASC;
SELECT `p`.* FROM products AS `p` ORDER BY id ASC;

JOIN Does Not Provide Reusable Alias Resolution

The existing JOIN feature supports table-name qualification, not SQL table aliases.

Code anchors:

• src/sphinxql.y:858-864: join_clause accepts single_tablename followed immediately by TOK_ON.
• src/searchdsql.cpp:2413-2420: SetJoin() stores the joined table name in m_pQuery->m_sJoinIdx.
• src/sphinxexpr.cpp:4714-4720: expression parsing recognizes a token as TOK_TABLE_NAME only when it equals the actual left or right table name.
• src/sphinxexpr.cpp:10589-10651: ParseJoinAttr() / AddNodeWithTable() map table.attr against schema attributes.

This works because products and categories are table names:

SELECT name, categories.title
FROM products
INNER JOIN categories ON categories.id = products.category_id
ORDER BY id ASC;

But these do not work as SQL aliases:

SELECT p.name, c.title
FROM products p
INNER JOIN categories c ON c.id = p.category_id
ORDER BY p.id ASC;

SELECT p.name, categories.title
FROM products p
INNER JOIN categories ON categories.id = p.category_id
ORDER BY p.id ASC;

Focused design probe:

• Repro: /mnt/c/dev/sphinx/manticore/tmp/gh4695/join_alias_design.rec
• Report: /mnt/c/dev/sphinx/manticore-labs/gh4695/clt_join_alias_design.md

Observed results:

• table-name-qualified JOIN passed
• JOIN categories c ON ... failed with unexpected identifier, expecting ON
• left-table alias plus ORDER BY p.id failed with sort-by attribute 'p.id' not found

Problem Statement

Manticore should either:

1. Clearly document that SQL table aliases are not generally supported, and keep PR 4695 scoped as a client-compatibility rewrite, or
2. Add first-class table alias support that consistently resolves aliases across all clauses where table-qualified names are accepted.

The current naming in PR 4695 can imply option 2 while implementing only option 1. That creates future maintenance risk: new code may assume table_alias_ident and SetTableAlias() are a general alias facility, but they only normalize SELECT-list output expressions.

Definition

Manticore currently supports table-name-qualified attributes in JOIN queries, e.g. categories.title, and PR 4695 adds a narrow DBeaver compatibility path for single-table SELECT-list aliases, e.g. SELECT p.id FROM products p.

That PR is intentionally limited: it strips alias prefixes only from SELECT-list items. It does not provide general SQL table alias resolution in WHERE, ORDER BY, GROUP BY, HAVING, JOIN ON, joined table aliases, FACET, OPTION table qualifiers, or query logging.

We should add first-class table alias support so queries such as these work consistently:

SELECT p.id FROM products p WHERE p.id = 1;
SELECT p.id FROM products p ORDER BY p.id;
SELECT p.category_id, COUNT(*) FROM products p GROUP BY p.category_id;
SELECT p.id, c.title FROM products p INNER JOIN categories c ON c.id = p.category_id;
SELECT p.id FROM products AS `p` WHERE `p`.id = 1;

The implementation should keep table-name qualification working, preserve explicit SELECT aliases, and avoid string-prefix rewrites as the primary semantic mechanism.

Desired Semantics

Aliases should be aliases for table identifiers, not string prefixes on raw expressions.

Required behavior:

• FROM products p and FROM products AS p define alias p for table products.
• Backticked aliases should work where aliases are accepted:

FROM products AS `p`

• Qualified references through the alias should resolve anywhere table-qualified references are valid.
• Existing table-name qualification should continue to work:

SELECT products.id FROM products;
SELECT products.id, categories.title
FROM products
INNER JOIN categories ON categories.id = products.category_id;

• Explicit SELECT aliases should be preserved:

SELECT p.id AS product_id FROM products p;

• Auto aliases should be stable and intuitive:

SELECT p.id FROM products p;
-- output column should be `id` or match the established current branch behavior

Open decision:

• Should a real table name remain usable after an alias is declared? MySQL allows alias-qualified references and generally expects the alias where one is declared. Manticore can choose compatibility behavior, but it should be explicit and tested.

Proposed Scope

Phase 1: parser and alias map

• Add table alias metadata to the parsed query state.
• Track aliases for the main FROM target and JOIN targets.
• Reject duplicate aliases or ambiguous aliases with clear errors.
• Decide case-folding rules for aliases and quoted aliases.

Phase 2: expression and attribute resolution

• Resolve alias.attr through a table-alias map before falling back to table-name qualification.
• Integrate alias lookup into the existing JOIN table-name path in ExprParser_t::ProcessRawToken().
• Resolve aliases in ParseJoinAttr() / AddNodeWithTable() without depending on raw table-name strings only.
• Preserve current table-name-qualified JOIN behavior.

Phase 3: clause coverage

Cover aliases in:

• SELECT list
• WHERE
• ORDER BY
• GROUP BY
• HAVING
• JOIN ON
• MATCH table argument where applicable
• FACET expressions
• OPTION table-scoped clauses, if table qualifiers are accepted there

Phase 4: tests and compatibility

• Add focused CLT tests for every clause above.
• Add negative tests for unsupported or ambiguous cases.
• Add backticked alias variants.
• Add query-log expectations if alias syntax is logged or normalized.

Risks

• Parser conflicts if optional aliases are added too broadly.
• Backwards compatibility if existing queries use identifiers after table names for another purpose.
• Ambiguity between alias names and column names.
• Ambiguity when an alias equals another table name.
• JOIN schema naming currently treats left-table columns as unprefixed and right-table columns as right.attr; alias resolution must respect that internal representation.
• Query-log output may preserve aliases while execution normalizes them, so logging expectations need explicit tests.

Suggested Acceptance Tests

Positive:

SELECT p.id, p.name FROM products p ORDER BY p.id ASC;
SELECT p.id FROM products AS p WHERE p.id = 1;
SELECT `p`.id FROM products AS `p` WHERE `p`.id = 1;
SELECT p.category_id, COUNT(*) FROM products p GROUP BY p.category_id;
SELECT p.id, c.title FROM products p INNER JOIN categories c ON c.id = p.category_id ORDER BY p.id ASC;
SELECT p.id FROM products p HAVING p.id > 0;

Backwards-compatible table-name qualification:

SELECT products.id FROM products ORDER BY products.id ASC;
SELECT products.id, categories.title
FROM products
INNER JOIN categories ON categories.id = products.category_id;

Negative / decision tests:

SELECT p.id FROM products p INNER JOIN categories p ON p.id = products.category_id;
SELECT p.id FROM products AS p WHERE products.id = 1;
SELECT x.id FROM products p;

The second negative case depends on the open decision about whether real table names remain valid after an alias is declared.

Checklist:

^{To be completed by the assignee. Check off tasks that have been completed or are not applicable.}

Details

• Implementation completed
• Tests developed
• Documentation updated
• Documentation reviewed
• OpenAPI YAML updated and issue created to rebuild clients