[FEATURE] Unified SQL language across OpenSearch, Flint and Spark SQL

[dai-chen]

Is your feature request related to a problem?

OpenSearch SQL is currently fragmented across two repositories and four engines. This fragmentation creates the same class of problems identified in the PPL unification effort (opensearch-project/opensearch-spark#1136): semantic divergence, engineering duplication, feature lag, and missed optimization opportunities.

Repository	Engine	Scope
opensearch-project/sql	Legacy (NLPchina)	JOINs, UNIONs, subqueries
opensearch-project/sql	V2	Simple queries, improved correctness
opensearch-project/sql	Calcite-based ANSI SQL (new)	Analytics engine integration, ANSI SQL compliance
opensearch-project/opensearch-spark	Spark SQL + Flint SQL	Spark queries, secondary index DDL

A gap analysis that runs the full V2 + Legacy integration test suite (807 queries) against the new Calcite engine shows a 67.9% pass rate, with 259 failures across 13 categories. This quantifies the distance between the current engines and a unified SQL baseline. See #5346 (comment) below for the full breakdown.

What solution would you like?

Define a Unified SQL Language specification — an ANSI SQL core with internal language extensions that model capabilities such as custom identifier/parsing rules, relevance search and Flint DDL — and implement it once in the Calcite-based engine for reuse across both the OpenSearch and Spark.

Unified SQL Language
├── ANSI SQL Core
├── Search Extension (full-text search, vector search, identifier rules)
└── Secondary Index Extension (Flint DDL)

Phase 1: Analytics engine integration

• Core DQL — SELECT, subqueries, CTEs, window functions
• Explain support
• Full-text search functions

Phase 2: Full language unification

• V2/Legacy backward compatibility (identifier rules, type mappings, syntax gaps)
• Vector search ([Feature] Add vector search execution pipeline for vectorSearch() table function #5320)
• Flint DDL (skipping index, covering index, materialized view)
• Spark SQL alignment (via ANSI mode)
• Others: prepared statement, cursor/pagination

See #5346 (comment) below for the full design and roadmap.

What alternatives have you considered?

• Keep all engines permanently: Maintenance burden of 4 code paths, inconsistent behavior, no convergence.
• Strict ANSI SQL only: Breaks existing users who depend on full-text search functions, backtick quoting, and OpenSearch identifier conventions
• Enable V2 SQL: Since SQL V2 and PPL V3 share the same AST abstraction, it could be routed through the existing CalciteRelNodeVisitor as PPL V3. However, this bypasses Calcite built-in logical optimizations, ANSI SQL features like CTEs etc.

Do you have any additional context?

• Related issue: [FEATURE] SQL query support for Analytics engine integration #5248 (SQL query support for Analytics engine integration)

[dai-chen]

Unified Query API — SQL Gap Analysis Report (Consolidated)

Date: 2026-03-30
Branch: https://github.com/dai-chen/sql-1/tree/poc/gap-analysis-on-formal
Scope: All SQL IT tests — V2 (org.opensearch.sql.sql.*IT, 50 classes) + Legacy (org.opensearch.sql.legacy.*IT, 39 classes) — 807 queries
Pipeline: UnifiedQueryPlanner (Calcite native SQL parser) → UnifiedQueryCompiler → PreparedStatement.executeQuery()

Consolidates the Mar 24 baseline (exception-based failures) and Mar 25 update (added result-mismatch detection).

---

Summary

Metric	Count	%
Total Queries	807	100%
✅ Success	548	67.9%
❌ Failed (Exception)	232	28.7%
❌ Failed (Result Mismatch)	27	3.3%
Total Failed	259	32.1%

All Failure Categories

#	Category	Count	Unified SQL?	Potential Solution
0	Table Identifier Compatibility	~750*	✅	Query pre-processor: auto-quote special chars. Or enable ALLOW_HYPHEN_IN_IDENTIFIER + custom lexer rules
1	Missing Calcite Functions	86	Mixed	Register all missing wildcard_query, query operators
2	EXPR_TIMESTAMP/TIME Type Conversion	22	✅	Implement EXPR_TIMESTAMP/EXPR_TIME converters in result set reader
3	Comma-Join / Nested Table Syntax (PartiQL)	26	❌	No ANSI SQL equivalent — would require custom PartiQL-to-Calcite translation layer
4	Relevance Function Named Parameters	13	✅	Extend Calcite SQL parser to support key=value named parameter syntax in function calls
5	String Literal vs Identifier	2	❌	N/A — Calcite follows ANSI correctly
6	Backtick Identifiers	8	⚠️	Set Quoting.BACK_TICK in SqlParser.Config. Or enable MySQL conformance
7	GROUP BY Expression Issues	8	❌	N/A — Calcite enforces ANSI correctly
8	Wildcard * in nested()	6	✅	Implement * wildcard expansion inside function arguments during schema resolution
9	Square Bracket [field] Syntax	17	✅	Extend Calcite parser to support [field1, field2] array literal syntax in function args
10	match() Reserved Word	16	✅	De-reserve MATCH keyword in parser config. Or enable MySQL conformance
11	Other	28	Mixed	See sub-category breakdown
12	Result Mismatch	27	Mixed	See sub-category breakdown
	Total	259

*Category 0 is not counted in the 259 total — it is silently fixed by the gap analyzer's pre-processing (quoteHyphenatedTableNames). Without this pre-processing, ~750 of 807 queries would fail at PLAN phase.

---

Category 0: Table Identifier Compatibility (~750 queries, masked by pre-processing)

V2/Legacy allows special characters in table identifiers without quoting. Calcite follows ANSI SQL where these require delimited identifiers (double quotes).

Feature	V2 Engine	Calcite (ANSI SQL)	Impact
Hyphens (opensearch-sql_test)	✅ Allowed unquoted	❌ Parsed as minus operator	~750 queries
Dot-prefixed (.kibana)	✅ Allowed unquoted	❌ Parsed as qualifier separator	Low (admin indices)
@ prefix (@timestamp)	✅ Allowed unquoted	❌ Not valid identifier start	Low (field names)
Star wildcards (index*)	✅ Allowed in table names	❌ Not valid in identifiers	Low (PPL only)

The gap analyzer applies a regex-based workaround (quoteHyphenatedTableNames) that wraps hyphenated names after FROM/JOIN in double quotes. This is fragile — it does not cover subqueries, CTEs, or other table reference positions. In production, the unified engine needs a proper identifier
compatibility layer (either auto-quoting in the query pre-processor or extending the Calcite parser's identifier rules).

Note: See Identifier documentation for V2's full identifier rules including backtick and double-quote delimited identifiers.

Category 1: Missing Calcite Functions (86 queries)

Error: No match found for function signature <name>(...)

Function Group	Count	Examples
Relevance: wildcard_query() / wildcardquery()	22	WHERE wildcard_query(KeywordBody, 'test*')
Relevance: match_query() / matchquery()	10	WHERE match_query(lastname, 'Bates')
Relevance: match_phrase() / matchphrase()	10	WHERE match_phrase(phrase, 'quick fox')
Relevance: match_phrase_prefix()	8	WHERE match_phrase_prefix(Title, 'champagne be')
Relevance: query()	8	WHERE query('Tags:taste')
Relevance: multi_match() / multimatch()	3	WHERE multi_match([Title, Body], 'IPA')
Relevance: matchphrasequery()	2	WHERE matchphrasequery(phrase, 'quick fox')
Datetime: convert_tz()	17	SELECT convert_tz('2021-05-12','+10:00','+11:00')
Datetime: DATETIME()	15	SELECT DATETIME('2008-01-01 02:00:00+10:00', '-10:00')
nested()	1	SELECT nested(message.info)
percentiles()	2	SELECT percentiles(age, 25.0, 50.0, 75.0, 99.9)
ISNULL()	1	SELECT ISNULL(lastname)
Log() (case-sensitive)	1	SELECT Log(MAX(age) + MIN(age))

Note: Core relevance functions (match, match_phrase, match_bool_prefix, match_phrase_prefix, multi_match, simple_query_string, query_string) support are in progress (#5279)

Category 2: EXPR_TIMESTAMP/TIME Type Conversion (22 queries)

Error: class java.lang.String: need to implement EXPR_TIMESTAMP / EXPR_TIME
OpenSearch returns EXPR_TIMESTAMP/EXPR_TIME typed values that the Calcite pipeline doesn't have a converter for. Affects datetime columns and functions like DATE_FORMAT(). Includes 1 EXPR_TIME query (same root cause).

Category 3: Comma-Join / Nested Table Syntax (26 queries)

Error: Table '<name>' not found / no such index [<alias>]
Legacy syntax FROM t1 e, e.message m treats e.message as a nested field path. Calcite parses it as a table reference. This is an OpenSearch-specific semantic with no ANSI SQL equivalent. Includes 5 queries (previously "Index Not Found") where the table alias (e, tab) passes through to OpenSearch as a literal index name.

Category 4: Relevance Function Named Parameters (13 queries)

Error: Column 'slop' not found / Column 'boost' not found / Column 'analyzer' not found / Column 'query' not found
Relevance functions use key=value named parameter syntax (e.g., match_phrase(phrase, 'quick fox', slop=2)). Calcite doesn't understand this convention and treats slop, boost, analyzer, query, max_expansions as column references.

Category 5: String Literal vs Identifier (2 queries)

Error: Column 'Hello' not found in any table
Calcite follows ANSI SQL: double-quoted strings are identifiers. V2 treats them as string literals (SELECT "Hello").
Fix: Configure SqlParser.Config quoting behavior, or accept ANSI behavior and document the migration.

Category 6: Backtick Identifiers (8 queries)

Error: Lexical error: Encountered "\" (96)**Fix:** ConfigureQuoting.BACK_TICKinSqlParser.Config`.

Category 7: GROUP BY Expression Issues (8 queries)

Error: Expression 'lastname' is not being grouped
Calcite enforces strict GROUP BY validation per ANSI SQL. V2 is more lenient. Includes 1 query where a SELECT alias used in GROUP BY is not resolved.

Category 8: Wildcard * in nested() (6 queries)

Error: Unknown field '*'
nested(message.*) wildcard expansion fails during schema resolution. Distinct from Cat 3 — the nested() function parses fine, but * expansion inside function arguments is unsupported.

Category 9: Square Bracket [field] Syntax (17 queries)

Error: Encountered "[" at line 1, column N
Calcite's parser does not recognize [field1, field2] array literal syntax used by multi_match, query_string, and highlight functions.

Category 10: match() Reserved Word (16 queries)

Error: Encountered "match" at line 1, column N
MATCH is a SQL reserved keyword in Calcite (used for MATCH_RECOGNIZE). OpenSearch uses match() as a full-text search function in WHERE/HAVING clauses.

Category 11: Other (28 queries)

Sub	Name	Count	Unified SQL?	Potential Solution
11a	SHOW/DESCRIBE Syntax	11	✅	Fallback to V2/Spark. Or add custom DDL parsing rules
11b	Trailing Semicolons	6	✅	Pre-processor: strip trailing ; before Calcite parse
11c	Reserved Word as Alias	3	⚠️	MySQL conformance un-reserves most keywords
11d	CAST BOOLEAN→INT	1	⚠️	Custom BOOLEAN→INTEGER type coercion rule
11e	Slash in Index Name	1	❌	N/A — deprecated ES type syntax
11f	Nested Function + AND	1	❌	No ANSI SQL equivalent — PartiQL nested syntax
11g	Nested Type Access on Array	1	⚠️	Array-aware field resolution in type system
11h	Quote Escaping (backslash)	1	⚠️	MySQL conformance enables backslash escaping
11i	TYPEOF Type Restriction	1	✅	Broaden TYPEOF function signature to accept any type
11j	Runtime Type Cast Error	1	✅	Handle array fields in result set reader

Category 12: Result Mismatch (27 queries)

Both pipelines execute successfully but return different data.

Sub	Name	Count	Unified SQL?	Potential Solution
12a	Extra metadata columns	9	⚠️	Exclude _id, _index, etc. from SELECT * projection
12b	JOIN row explosion	3	❌	N/A — V2's implicit LIMIT 200 is non-standard
12c	Column truncation	6	❌	N/A — V2 stripping ORDER BY columns is non-standard
12d	Numeric precision	1	❌	N/A — V2 truncating decimals is non-standard
12e	Geo-point format	2	⚠️	Configure geo-point output format in serializer
12f	LIKE escape	6	⚠️	MySQL conformance enables implicit \ escape

[dai-chen]

Unified SQL — Design & Roadmap [WIP]

1. Gap Analysis Interpretation

#	Category	Count	Unified SQL [TBD]	Potential Solution
0	Table Identifier Compatibility	~750*	✅	Query pre-processor: auto-quote special chars. Or enable Calcite HYPHENATED_IDENTIFIER support
1	Missing Calcite Functions	86	Mixed	Wire PPLBuiltinOperators into SQL FrameworkConfig.operatorTable(). Add missing wildcard_query, query operators
2	EXPR_TIMESTAMP/TIME Type Conversion	22	✅	Implement EXPR_TIMESTAMP/EXPR_TIME converters in result set reader
3	Comma-Join / Nested Table Syntax (PartiQL)	26	❌	No ANSI SQL equivalent — would require custom PartiQL-to-Calcite translation layer
4	Relevance Function Named Parameters	13	✅	Extend Calcite SQL parser to support key=value named parameter syntax in function calls
5	String Literal vs Identifier	2	❌	N/A — Calcite follows ANSI correctly
6	Backtick Identifiers	8	⚠️	Set Quoting.BACK_TICK in SqlParser.Config. Or enable MySQL conformance
7	GROUP BY Expression Issues	8	❌	N/A — Calcite enforces ANSI correctly
8	Wildcard * in nested()	6	✅	Implement * wildcard expansion inside function arguments during schema resolution
9	Square Bracket [field] Syntax	17	✅	Extend Calcite parser to support [field1, field2] array literal syntax in function args
10	match() Reserved Word	16	✅	De-reserve MATCH keyword via Babel parser
12	Result Mismatch	27	Mixed	See gap analysis report for sub-category breakdown
	Total	259

* Category 0 is not counted in the 259 total — masked by gap analyzer pre-processing.
✅ Must support · ⚠️ Debatable · ❌ Not carrying forward (ANSI SQL is correct)

2. Roadmap & Full Scope

Phase 1 covers what's required for the Analytics-engine integration — ANSI SQL with full-text search functions. Phase 2 items need explicit design decisions on how far to diverge from strict ANSI SQL for OpenSearch usability.

Statement Type	SQL V2/Legacy (Plugin)	Spark SQL + Flint SQL	Unified SQL (Calcite)
Phase 1
DQL: SELECT	✅ V2 (simple) + Legacy (JOINs, UNION)	✅ Full Spark SQL	🔄 In progress (this issue)
Admin: EXPLAIN	✅ REST endpoint (/_explain)	✅ EXPLAIN statement	🔄 In progress (this issue)
DQL: Subqueries, CTEs, Window	✅ Partial (subquery in FROM/WHERE)	✅ Full (CTEs, window functions)	🔄 In progress (this issue)
Admin: SHOW/DESCRIBE	✅ SHOW TABLES, DESCRIBE TABLES	✅ SHOW TABLES/DATABASES/COLUMNS	SQL V2/Spark fallback
Phase 2
DQL: Vector Search	❌	❌	📋 Planned (vectorSearch() table function, #5320)
PreparedStatement	✅ ? params via REST + JDBC driver	❌	📋 Planned
Cursor/Pagination	✅ fetch_size + cursor ID (no agg/JOIN)	❌	📋 Planned
DDL: Flint Index	❌	✅ CREATE/DROP/ALTER/REFRESH/VACUUM SKIPPING INDEX, INDEX, MATERIALIZED VIEW	📋 Planned
DDL: Table	❌	✅ CREATE/DROP/ALTER TABLE (non-OpenSearch catalogs)	Spark fallback
DML: INSERT/UPDATE/DELETE	❌	✅ INSERT, UPDATE, DELETE, MERGE	Spark fallback

3. Unified SQL Language [TBD]

The unified query library defines a single SQL language specification built on ANSI SQL with capability extensions:

Unified SQL Language
├── ANSI SQL Core
├── Search Extension
│   ├── Full-text search (relevance functions, named parameters, multi-field syntax)
│   ├── Vector search (vectorSearch table function)
│   └── Data model (identifier rules, nested types, metadata fields, type mappings)
└── Secondary Index Extension
    └── Flint DDL (skipping index, covering index, materialized view)

The "extension" concept is an internal organizational model — it tracks what the Unified SQL Language adds beyond ANSI SQL. Externally, these are all first-class features of one coherent language specification. Extensions are organized by capability, not by engine, making them portable across Calcite, Spark, DataFusion, or any future execution engine.

Search Extension

Category	What it adds	Examples
Relevance functions	Full-text search as first-class SQL functions	match(), match_phrase(), wildcard_query(), query_string(), multi_match(), highlight(), score()
Named function parameters	key=value syntax in function calls	match_phrase(field, text, slop=2, boost=1.0)
Array literal in function args	Multi-field specification	multi_match([field1, field2], text)
Vector search	Table function for knn queries	vectorSearch(table, field, vector, option)
Identifier rules	Index naming conventions	Hyphenated names (logs-2024), dot-prefixed (.kibana), backtick quoting

Secondary Index Extension (Flint DDL)

Category	What it adds	Examples
Skipping index	Secondary index for data skipping	CREATE/DROP/ALTER/REFRESH/VACUUM/ANALYZE SKIPPING INDEX
Covering index	Secondary index for covering queries	CREATE/DROP/ALTER/REFRESH/VACUUM INDEX
Materialized view	Precomputed query results	CREATE/DROP/ALTER/REFRESH/VACUUM MATERIALIZED VIEW
Index management	Cross-index operations	SHOW FLINT INDEXES, RECOVER INDEX JOB

4. Implementation Architecture (in Calcite)

The capability extensions map to Calcite's distinct extension seams. Each seam has a specific role — they should not be conflated:

Calcite Seam	What it controls	Extension concerns mapped here
Custom Parser Grammar (config.fmpp, parserImpls.ftl)	New syntax rules	Named parameters (key=value), array literals ([field1, field2]), Flint DDL statements, SHOW/DESCRIBE
Parser Config (SqlParser.Config, Lex)	Lexical acceptance	Backtick quoting, identifier casing, hyphenated identifier support
Conformance (SqlConformance)	Semantic SQL behavior	GROUP BY alias, lenient type coercion, LIMIT syntax
Operator Table (SqlOperatorTable)	Function/operator registration	Relevance functions, datetime functions, vectorSearch() table function
Query Pre-processor (before Calcite)	Input normalization	Auto-quote special-char identifiers, strip trailing semicolons

How extensions compose

Pre-processor (auto-quote identifiers, strip semicolons)
  → Custom Parser Grammar
      (ANSI SQL base + Search syntax + Flint DDL syntax)
  → Parser Config
      (backtick quoting, hyphenated identifiers, casing rules)
  → Conformance
      (ANSI base + semantic accommodations)
  → Chained Operator Table
      (SqlStdOperatorTable + Search functions + datetime)