Skip to content

SCENARIOS-redshift-omni.md

Latest commit

 

History

History
237 lines (185 loc) · 12.2 KB

SCENARIOS-redshift-omni.md

File metadata and controls

237 lines (185 loc) · 12.2 KB
Raw

Redshift Omni Scenarios

Goal: replace Bytebase's ANTLR-backed Redshift parser with a hand-written Omni Redshift dialect while preserving the runtime surfaces Bytebase already enables: SQL review, syntax diagnostics, SQL editor validation, masking query span, autocomplete, statement report, and changed-resource extraction.

Reference sources:

  • Legacy parser grammar and examples: github.com/bytebase/parser/redshift at v0.0.0-20260417075056-57b6ef7a2640.
  • Official syntax: Amazon Redshift SQL command documentation.
  • Existing Bytebase consumers: backend/plugin/parser/redshift, backend/common/engine.go, SQL Editor, export resources, statement report, and LSP completion.

Progress rules:

  • A scenario passes only when a focused automated test proves the behavior.
  • Legacy examples that parse today must not regress.
  • Unsupported cases must fail explicitly; they must not produce misleading AST, query span, or completion results.

Phase 0: Scaffold And Corpus

Package Shape

  • redshift exposes Parse(sql string) ([]Statement, error).
  • redshift/parser exposes a hand-written recursive-descent parser forked from Omni PostgreSQL.
  • redshift/ast contains Redshift AST nodes and walker support.
  • redshift/catalog can analyze PostgreSQL-compatible Redshift SELECT queries.
  • redshift/completion exposes completion context for Redshift SQL.
  • go test ./redshift/... runs without depending on Bytebase.

Legacy Corpus

  • All 115 legacy Redshift example files are available to tests or test fixtures.
  • The corpus is classified into P0 parse, P1 parse, and unsupported/deferred groups.
  • PostgreSQL-compatible examples from the legacy corpus parse successfully.
  • Redshift-specific examples are tracked as expected failures until implemented.
  • Corpus failures report the source file and first syntax error.

Phase 1: Parser Foundation

Statement Boundaries

  • Split separates top-level semicolon-delimited statements.
  • Split ignores semicolons inside single-quoted strings.
  • Split ignores semicolons inside double-quoted identifiers.
  • Split ignores semicolons inside dollar-quoted strings.
  • Split ignores semicolons inside block comments.
  • Split handles Redshift procedure/function blocks without splitting internal statements.

Core Statements

  • SELECT 1 parses as a select statement.
  • WITH c AS (SELECT 1) SELECT * FROM c parses with a CTE.
  • INSERT INTO t SELECT 1 parses as DML.
  • UPDATE t SET a = 1 WHERE id = 2 parses as DML.
  • DELETE FROM t WHERE id = 1 parses as DML.
  • MERGE INTO target USING source ON target.id = source.id WHEN MATCHED THEN UPDATE SET v = source.v parses as DML.
  • MERGE ... REMOVE DUPLICATES parses as Redshift-specific MERGE.
  • EXPLAIN SELECT 1 parses as utility SQL.
  • EXPLAIN ANALYZE SELECT 1 parses and is classified distinctly from plain explain.

Phase 2: Redshift DDL And Utility Syntax

CREATE TABLE

  • CREATE TABLE t (id INT) parses.
  • CREATE TABLE t (id INT IDENTITY(1,1)) parses and records identity options.
  • CREATE TABLE t (id INT GENERATED BY DEFAULT AS IDENTITY) parses.
  • CREATE TABLE t (name VARCHAR(32) ENCODE lzo) parses and records column encoding.
  • CREATE TABLE t (id INT DISTKEY) parses and records column distkey.
  • CREATE TABLE t (id INT SORTKEY) parses and records column sortkey.
  • CREATE TABLE t (id INT) DISTSTYLE KEY DISTKEY(id) parses and records table distribution options.
  • CREATE TABLE t (id INT) SORTKEY(id) parses and records compound sort key.
  • CREATE TABLE t (id INT) INTERLEAVED SORTKEY(id) parses and records interleaved sort key.
  • CREATE TABLE t (id INT) ENCODE AUTO parses and records automatic encoding.
  • CREATE TABLE t (id INT) BACKUP NO parses and records backup behavior.

Data Movement

  • COPY t FROM 's3://bucket/file' IAM_ROLE DEFAULT parses.
  • COPY t FROM 's3://bucket/file' IAM_ROLE 'arn:aws:iam::1:role/r' FORMAT AS CSV parses.
  • COPY t FROM 's3://bucket/file' CREDENTIALS 'aws_access_key_id=...' parses.
  • UNLOAD ('SELECT * FROM t') TO 's3://bucket/out' IAM_ROLE DEFAULT parses.
  • UNLOAD options parse for FORMAT, MANIFEST, HEADER, DELIMITER, ENCRYPTED, and compression.

SHOW And DESC

  • SHOW DATABASES parses.
  • SHOW DATABASES LIKE 'dev%' LIMIT 10 parses.
  • SHOW SCHEMAS FROM DATABASE dev LIMIT 10 parses.
  • SHOW TABLES FROM SCHEMA dev.public LIKE 't%' LIMIT 10 parses.
  • SHOW COLUMNS FROM TABLE public.t parses.
  • SHOW GRANTS ON TABLE public.t parses.
  • SHOW DATASHARES parses.
  • DESC DATASHARE share_name parses.

Redshift Objects

  • CREATE DATASHARE s parses.
  • ALTER DATASHARE s ADD TABLE public.t parses.
  • DROP DATASHARE IF EXISTS s parses.
  • CREATE EXTERNAL SCHEMA s FROM DATA CATALOG DATABASE 'db' REGION 'us-east-1' IAM_ROLE DEFAULT parses.
  • CREATE EXTERNAL TABLE s.t (id INT) STORED AS PARQUET LOCATION 's3://bucket/' parses.
  • CREATE EXTERNAL VIEW v AS SELECT 1 parses.
  • CREATE MASKING POLICY p WITH (...) USING (...) parses or fails with explicit unsupported detail.
  • ATTACH MASKING POLICY p ON t(c) TO ROLE r parses or fails with explicit unsupported detail.
  • CREATE RLS POLICY p WITH (...) USING (...) parses or fails with explicit unsupported detail.
  • CREATE MODEL m FROM (...) TARGET y FUNCTION f IAM_ROLE DEFAULT parses or fails with explicit unsupported detail.

Phase 3: Bytebase Runtime Surfaces

Diagnostics And Ranges

  • Diagnose returns no diagnostics for valid P0 Redshift SQL.
  • Diagnose returns a syntax diagnostic with line and column for invalid SQL.
  • StatementRanges returns UTF-16 LSP ranges for single-line SQL.
  • StatementRanges returns UTF-16 LSP ranges for multi-line SQL.
  • StatementRanges handles non-BMP characters before statement starts.

Statement Types And Validation

  • GetStatementTypes classifies CREATE TABLE.
  • GetStatementTypes classifies ALTER TABLE.
  • GetStatementTypes classifies DROP TABLE.
  • GetStatementTypes classifies INSERT, UPDATE, DELETE, and MERGE as DML.
  • ValidateSQLForEditor allows SELECT.
  • ValidateSQLForEditor allows plain EXPLAIN.
  • ValidateSQLForEditor allows SHOW statements.
  • ValidateSQLForEditor rejects DML for read-only execution.
  • ValidateSQLForEditor rejects DDL for read-only execution.
  • ValidateSQLForEditor treats EXPLAIN ANALYZE non-SELECT conservatively.

Changed Resources

  • ExtractChangedResources reports created tables.
  • ExtractChangedResources reports dropped tables as affected resources.
  • ExtractChangedResources reports altered tables as affected resources.
  • ExtractChangedResources counts INSERT statements.
  • ExtractChangedResources counts UPDATE statements.
  • ExtractChangedResources counts DELETE statements.
  • ExtractChangedResources respects SET search_path.

Phase 4: Query Span

SELECT Lineage

  • Query span reports source columns for simple table columns.
  • Query span expands SELECT * from table metadata.
  • Query span expands table-qualified star.
  • Query span tracks aliases in SELECT list.
  • Query span tracks source columns inside expressions.
  • Query span tracks predicate columns from WHERE.
  • Query span tracks predicate columns from JOIN ON.
  • Query span handles JOIN USING.
  • Query span handles CTEs with explicit column aliases.
  • Query span handles CTEs without explicit column aliases.
  • Query span handles subquery table aliases.
  • Query span handles set operations.
  • Query span handles view definitions from metadata.

Redshift SELECT Extensions

  • Query span handles QUALIFY as predicate context.
  • Query span handles EXCLUDE (...) result shaping.
  • Query span handles SELECT INTO as DML/DDL-like statement where appropriate.
  • Query span explicitly rejects unsupported CONNECT BY lineage instead of returning wrong lineage.

Phase 5: Completion

Core Completion

  • Completion suggests top-level SELECT/INSERT/UPDATE/DELETE/MERGE/CREATE/ALTER/DROP/COPY/UNLOAD/SHOW.
  • Completion suggests schema names from metadata.
  • Completion suggests table names from selected schema.
  • Completion suggests columns after SELECT with FROM context.
  • Completion suggests columns after table alias dot.
  • Completion suggests CTE names in FROM.
  • Completion suggests CTE columns when aliases are explicit.
  • Completion suggests subquery alias/output columns from syntax-derived target lists.

Redshift Slots

  • Completion suggests DISTKEY, SORTKEY, DISTSTYLE, and ENCODE in CREATE TABLE option context.
  • Completion suggests IAM_ROLE, CREDENTIALS, and format options in COPY context.
  • Completion suggests IAM_ROLE, MANIFEST, HEADER, and format options in UNLOAD context.
  • Completion suggests SHOW subcommands.
  • Completion does not invent metadata columns for unsupported shapes.

Phase 6: Bytebase Switch

  • Bytebase Redshift parser registration delegates parse/split/diagnose/ranges/types to Omni.
  • Bytebase Redshift query validator delegates to Omni.
  • Bytebase Redshift changed resources delegates to Omni.
  • Bytebase Redshift query span delegates to Omni.
  • Bytebase Redshift completion delegates to Omni.
  • Bytebase no longer imports github.com/bytebase/parser/redshift on the Redshift main path.
  • Bytebase focused Redshift parser tests pass.
  • Bytebase SQL Editor validation tests pass for Redshift.
  • Bytebase masking/query-span tests pass for Redshift.
  • Bytebase statement report tests pass for Redshift.

Phase 7: Omni Compatibility Verification

AWS Command Matrix

  • AWS Redshift SQL command reference is represented by an Omni-side manifest.
  • Every manifest command has an explicit status: runtime-supported, parse-supported, explicitly unsupported, or not relevant.
  • Runtime-supported command samples parse successfully.
  • Parse-supported command samples parse successfully.
  • Explicitly unsupported command samples fail instead of silently producing a misleading supported result.
  • The matrix includes Redshift-specific commands such as ALTER TABLE APPEND, ANALYZE COMPRESSION, COPY, UNLOAD, SHOW EXTERNAL TABLE, USE, and VACUUM.

Legacy Corpus Tracking

  • The 115-file legacy Redshift corpus is counted by a compatibility report.
  • Expected-failure legacy files are counted separately from passing files.
  • New legacy corpus failures fail the compatibility test.
  • Legacy expected failures that start parsing are reported as promoted files and must be reclassified.
  • Legacy corpus files are split into individual statements for old-parser-vs-Omni parity statistics.
  • Statement-level gaps are reported with examples so the next support tranche can be prioritized.

Runtime Semantics

  • Compatibility verification checks Redshift CREATE TABLE options through Parse.
  • Compatibility verification checks syntax diagnostics on invalid SQL.
  • Compatibility verification checks UTF-16 statement ranges on multi-statement SQL.
  • Compatibility verification checks Redshift command statement classification for COPY, UNLOAD, MERGE, and SHOW.
  • Compatibility verification checks SQL Editor read-only validation for EXPLAIN and DML rejection.
  • Compatibility verification checks changed-resource extraction for DDL and DML.
  • Compatibility verification checks parser-native completion scope for FROM relations.

Reference Redshift Harness

  • Compatibility verification has an optional Redshift reference execution harness.
  • The harness is disabled with an explicit skip reason when REDSHIFT_COMPAT_DSN is not set.
  • The report records whether reference Redshift validation ran or was skipped.

Compatibility Report

  • Omni can generate a Markdown Redshift compatibility report from the command manifest and legacy corpus.
  • The checked-in compatibility report is tested for freshness.
  • The report exposes command-level parse status, legacy file counts, legacy statement parity counts, runtime semantic counts, and reference Redshift status.