Consult this reference BEFORE translating any Spark SQL. It covers DDL rewrites, type mappings, function mappings, and unsupported features.

All Spark function signatures in this file are from the Apache Spark SQL reference (spark.apache.org/docs/latest/api/sql/index.html). When in doubt about a function signature or argument order, the Apache SQL reference is the authority.

These are systematic differences between Spark and Feldera to be aware of during translation. If any of these apply to the query being translated, add a -- NOTE: comment in the output SQL explaining the difference to the user. This is important so the user understands where results may differ between Spark and Feldera.

• [GBD-WHITESPACE] Whitespace in string values: Spark treats ' ' (space), \t (tab), \n (newline), \r (carriage return), and other Unicode whitespace characters as "whitespace" when operating on CHAR/VARCHAR/STRING values. Feldera follows the SQL standard and only considers ASCII space (0x20) as whitespace. This affects TRIM, LTRIM, RTRIM, and any other function that implicitly strips whitespace from string values. If the input may contain \t or \n at the edges, the results will differ. Note: CAST(str AS BOOLEAN) is NOT affected — Feldera correctly handles non-space whitespace in boolean strings.

• [GBD-CAST-TRIM] CAST from string auto-trims in Spark: When casting a VARCHAR/STRING expression to a numeric type (TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL) or a date/time type (DATE, TIMESTAMP), Spark automatically strips surrounding whitespace (spaces, tabs, newlines) before parsing. Feldera does not — leading/trailing whitespace causes a runtime error. Always wrap the string argument with TRIM before casting: CAST(TRIM(str) AS TINYINT), CAST(TRIM(str) AS DATE), etc. Apply whenever the source expression is VARCHAR or STRING, including column references and string literals.

• [GBD-INT-DIV] Integer division: When both operands are integers, Spark always performs floating point division and returns DOUBLE (e.g. 95/100 = 0.95); Feldera performs integer division (e.g. 95/100 = 0). Cast at least one operand to DOUBLE when fractional results are needed: CAST(col AS DOUBLE) / divisor.

• [GBD-DIV-OPERATOR] Spark div operator (floor division): a div b in Spark is floor integer division — it truncates toward negative infinity and always returns an integer. Feldera does not support the div keyword. Translate a div b as FLOOR(CAST(a AS DECIMAL(20,0)) / b) — always cast at least one operand to DECIMAL to ensure true division before flooring (e.g. FLOOR(CAST(51 AS DECIMAL(20,0)) / 2) = 25, FLOOR(CAST(-7 AS DECIMAL(20,0)) / 2) = -4). Do NOT use FLOOR(a / b) with integer operands — Feldera integer division truncates toward zero, so FLOOR(-7 / 2) = -3, not -4.

• [GBD-AGG-TYPE] Aggregate return types on numeric inputs: Spark often widens numeric aggregates regardless of input type; Feldera preserves the input type. Key cases:

  • AVG(integer_col) — Spark returns DOUBLE (AVG(1,2) = 1.5); Feldera returns INT (AVG(1,2) = 1). Rewrite: AVG(CAST(col AS DECIMAL(38,6))) only when the input type is confirmed integer (INT, BIGINT, SMALLINT, TINYINT) — derive from schema or column definition. If the type cannot be determined, leave as-is and flag [GBD-AGG-TYPE].
  • STDDEV_SAMP/STDDEV_POP(col) — Spark always returns DOUBLE; Feldera preserves the input type. Rewrite: STDDEV_SAMP(CAST(col AS DECIMAL(38,6))) only when the input type is confirmed non-DOUBLE (INT, BIGINT, SMALLINT, TINYINT, DECIMAL).
  • AVG(decimal_col) — Spark returns DECIMAL(p+4, s+4); Feldera preserves the input type and returns DECIMAL(p,s). Rewrite: AVG(CAST(col AS DECIMAL(p+4, s+4))) — Feldera's AVG preserves the input type, so pre-casting to the target precision/scale matches Spark's output. Only valid if p+4 ≤ 38 (Feldera's maximum DECIMAL precision); otherwise leave as-is and flag [GBD-AGG-TYPE].
  • NOTE (precision): Spark actually returns DOUBLE (~15–17 significant digits) for AVG(integer) and for STDDEV_*. The DECIMAL(38,6) rewrite above keeps a stable, exact decimal type but only carries 6 fractional digits, so results can differ from Spark in the trailing digits (e.g. AVG(1,2,4) → 2.333333 vs Spark 2.3333333333333335). This is an accepted trade-off; expect minor precision mismatches on repeating/irrational results. (Casting to DOUBLE instead would match Spark exactly but yields a floating type.)

• [GBD-DIV-ZERO] Division by zero / overflow: Spark returns NULL for integer division by zero and silently wraps on overflow. Feldera panics on integer division by zero or overflow. Floating-point division by zero returns Infinity in both engines.

  • try_divide(a, b) → div_null(a, b) — Feldera's div_null returns NULL on divide-by-zero, matching Spark's try_divide semantics. Always use this rewrite.
  • try_add / try_subtract / try_multiply → mark unsupported; no safe equivalent in Feldera.

• [GBD-FLOAT-FMT] FLOAT display format: Spark and Feldera may produce different string representations of floating-point values because the decimal representation of a binary float can be periodic (infinite). Both store the same bit pattern; they simply apply different rounding when converting to string.

• [GBD-FP-PREC] Floating point precision: Spark (JVM) and Feldera (Rust) use different algorithms for rounding when converting floating-point values to strings, so results can differ. Results may also differ between CPU architectures (Intel vs ARM).

• [GBD-ARRAY-ORDER] Array element order not guaranteed: Feldera does not preserve insertion order for ARRAY_AGG, collect_list, ARRAY_UNION, ARRAY_EXCEPT, ARRAY_INTERSECT. Spark preserves input order; Feldera may return elements in any order.

  • For ARRAY_AGG and collect_list: if a deterministic order is required, use ARRAY_AGG(col ORDER BY col) — Feldera supports ORDER BY inside ARRAY_AGG. Otherwise translate normally and add a NOTE comment: -- NOTE: [GBD-ARRAY-ORDER] element order in this array may differ from Spark — Feldera does not guarantee insertion order.
  • For ARRAY_UNION, ARRAY_EXCEPT, ARRAY_INTERSECT: no order-preserving rewrite is possible — translate normally and always add a NOTE comment: -- NOTE: [GBD-ARRAY-ORDER] element order in this array may differ from Spark — Feldera does not guarantee insertion order.

• [GBD-FLOAT-GROUP] ORDER BY on NULL values: When a REAL or DOUBLE column may contain NULL, sorting that column with ORDER BY yields a different row order in Spark and Feldera. Feldera treats NULL as the smallest value (sorts first in ASC, last in DESC); Spark treats NULL as the largest (sorts last in ASC, first in DESC). The sort order for special float values (-Inf, +Inf, NaN) matches between the two engines: -Inf < finite values < +Inf < NaN. GROUP BY and aggregation are unaffected; only output row order diverges. Translate the query normally, but add a NOTE comment above the ORDER BY clause: -- NOTE: [GBD-FLOAT-GROUP] NULL sort order differs from Spark — Feldera sorts NULLs first (smallest), Spark sorts NULLs last.

Spark SQL uses Python-style boolean literals (True, False). Feldera uses standard SQL keywords (TRUE, FALSE). Never quote boolean literals as strings.

❌ Wrong: col = 'True', col = 'False', col = 'true', col = 'false' ✅ Correct: col = TRUE, col = FALSE

⚠️ Exception: if the Spark query already has quotes around the literal (e.g. col = 'true'), the column is VARCHAR — keep the string literal as-is. Do NOT convert to TRUE/FALSE. Only convert unquoted Python-style True/False.

IMPORTANT: When a Spark query uses col is true or col is false, translate to col IS TRUE / col IS FALSE, NOT col = TRUE / col = FALSE. NULL handling differs: NULL IS TRUE = FALSE, but NULL = TRUE = NULL.

Feldera uses suffix syntax — fix DDL before addressing query-level issues:

• Never emit ARRAY<...> in Feldera DDL — always use suffix T ARRAY form
• Array literals: use ARRAY[...] or ARRAY(...)

• Array indexes are 1-based in both engines
• Compiler error Encountered "<" ... ARRAY<VARCHAR>: rewrite ALL ARRAY<...> to suffix form first, then re-validate

• Spark accepts CONSTRAINT name PRIMARY KEY (col) — Feldera rejects the named wrapper
• Spark allows nullable PK columns — Feldera requires all PK columns to be NOT NULL

• Never invent a PRIMARY KEY — only carry over a PK that is explicitly declared in the Spark schema (CONSTRAINT pk PRIMARY KEY (col) or bare PRIMARY KEY (col)). Do NOT add a PK just because a column is named id or transaction_id. Adding a spurious PK causes Feldera to deduplicate rows, silently corrupting results.
• Drop the CONSTRAINT name wrapper — use bare PRIMARY KEY (col)
• Add NOT NULL to all PK columns

-- Spark (both issues)
CREATE TABLE orders (
  order_id STRING,
  item_id STRING,
  CONSTRAINT orders_pk PRIMARY KEY (order_id, item_id)
);

-- Feldera (fixed)
CREATE TABLE orders (
  order_id VARCHAR NOT NULL,
  item_id VARCHAR NOT NULL,
  PRIMARY KEY (order_id, item_id)
);

• Compiler error when PK column is nullable: PRIMARY KEY cannot be nullable: PRIMARY KEY column 'user_id' has type VARCHAR, which is nullable

• Spark is permissive with reserved words as column names; Feldera requires them to be quoted

• Quote column names that clash with SQL reserved words — known reserved words that appear as column names in this codebase: timestamp, date, time, variant
• Apply quoting consistently in both CREATE TABLE/CREATE VIEW column list and every query reference
• Do not quote ordinary identifiers — quoting non-reserved words makes them case-sensitive and adds noise
• Quote identifiers with SQL-standard double quotes ("name"), never Spark-style backticks — Feldera/Calcite rejects backticks with a lexical error. This covers view, table, and column names that contain hyphens, dots, or reserved words (a hyphenated view name such as string-functions_142 must be written "string-functions_142", not in backticks).

-- Schema: only "TimeStamp" is quoted — it clashes with the TIMESTAMP type keyword
CREATE TABLE events (
  id BIGINT NOT NULL,
  source VARCHAR,
  "TimeStamp" TIMESTAMP,
  PRIMARY KEY (id)
);

-- Query: quote "TimeStamp" everywhere it appears, leave other columns unquoted
SELECT e.source, e."TimeStamp" as ts,
       MAX(e."TimeStamp") OVER (PARTITION BY e.id) as max_ts
FROM events e
WHERE e."TimeStamp" >= TIMESTAMP '2024-01-01 00:00:00'

• Spark supports 3-part qualified names: `catalog`.`schema`.`table` and 2-part: `schema`.`table`
• Feldera SQL does not support catalog/schema qualification — only the bare table name is valid

• Always strip catalog and schema prefixes; keep only the last segment (the table name)
• Apply to all table references in FROM, JOIN, and subquery clauses
• Apply to all view references in CREATE VIEW ... AS SELECT ... FROM
• Apply to the view name in CREATE VIEW schema.view_name — use bare view_name (the last segment after the dot, NOT the schema). Example: CREATE VIEW analytics.sales_summary → CREATE VIEW sales_summary.
• Name collision: If two tables from different schemas share the same unqualified name (e.g. public.status and audit.status), disambiguate by prefixing with the immediate schema. Give the bare name to the schema that appears most frequently across the query set (or the first encountered if tied); prefix the other: public.status → status, audit.status → audit_status. Use schema_table form only for the colliding name, not universally. Apply the rename consistently — update both the CREATE TABLE declaration and every query reference to the renamed table.

-- Spark (3-part qualified)
SELECT o.order_id, o.amount
FROM `prod_warehouse`.`public`.`orders` o
JOIN `prod_warehouse`.`public`.`customers` c ON o.customer_id = c.id;

-- Feldera (unqualified)
SELECT o.order_id, o.amount
FROM orders o
JOIN customers c ON o.customer_id = c.id;

-- Spark (2-part qualified)
SELECT * FROM `analytics`.`events`;

-- Feldera
SELECT * FROM events;

-- Spark
CREATE TABLE t (id BIGINT, name STRING, tags ARRAY<STRING>) USING parquet;
-- Feldera
CREATE TABLE t (id BIGINT, name VARCHAR, tags VARCHAR ARRAY);

-- Spark
CREATE OR REPLACE TEMP VIEW v AS SELECT * FROM t ORDER BY id LIMIT 10;
-- Feldera
CREATE VIEW v AS SELECT * FROM t ORDER BY id LIMIT 10;

These Spark functions exist in Feldera — translate directly:

• COUNT, MIN, MAX, COUNT(DISTINCT ...), bool_or, bool_and work identically — no translation needed
• SUM: works identically in most cases. Exception: when the input column is a narrow integer type (TINYINT, SMALLINT, INT), Spark widens the result to avoid overflow; Feldera preserves the input type. If overflow is possible, rewrite as SUM(CAST(col AS BIGINT)) or SUM(CAST(col AS DECIMAL(38,0))).

• [GBD-REGEX-ESCAPE] RLIKE / REGEXP_REPLACE regex pattern escaping: Spark SQL string literals apply Java-style \\ backslash escaping, so '\\.' passes the regex \. (escaped dot) to the engine. Feldera SQL follows the SQL standard and does not interpret \\ as an escape, so '\\.' is the two-character sequence \. in the regex — which may match differently. Use POSIX character classes instead: [.] for literal dot, [+] for literal plus, [*] for literal star, etc.

• UPPER, LOWER, LENGTH, SUBSTRING, CONCAT, CONCAT_WS, REPLACE, REGEXP_REPLACE, INITCAP, REPEAT, LEFT, RIGHT, MD5, ASCII, CHR work identically — no translation needed

• [GBD-SIZE-NULL] size(arr) returns -1 for NULL input in Spark; Feldera CARDINALITY returns NULL. Rewrite as COALESCE(CARDINALITY(arr), -1). If the column is NOT NULL, CARDINALITY(arr) alone is sufficient.

• TRANSFORM(arr, x -> expr) and ARRAY_EXISTS(arr, x -> expr) are supported — pass through (with name change for exists). Do NOT mark them unsupported.
• filter, zip_with, aggregate, forall, map_filter, transform_keys, transform_values are unsupported — see Unsupported section.

• [GBD-NONDETERMINISTIC] CURRENT_TIMESTAMP / NOW() — value is captured at execution time and will differ between Spark and Feldera runs

• YEAR, MONTH, HOUR, MINUTE, SECOND work identically — no translation needed
• DATE vs TIMESTAMP in comparisons / BETWEEN: When comparing DATE and TIMESTAMP columns (e.g. date_col BETWEEN timestamp_col AND timestamp_col), cast the DATE side to TIMESTAMP: CAST(date_col AS TIMESTAMP) BETWEEN ts1 AND ts2. Never cast to VARCHAR — VARCHAR comparison of dates produces wrong ordering.

Feldera uses a VARIANT type for JSON. Core functions:

VARIANT access patterns:

• JSON field access (e.g. v['age']) always returns VARIANT — always cast to the target type before use: CAST(v['age'] AS INT), CAST(v['name'] AS VARCHAR), etc.
• When translating JSON extraction: never expose PARSE_JSON(col) AS v as a top-level view column — v becomes a real output column of the view and does not appear in the original Spark query. Two safe patterns:

Simple SELECT — parse inline (preferred when only extracting fields; avoids the subquery entirely):

SELECT
  id,
  CAST(PARSE_JSON(payload)['user_id']  AS VARCHAR) AS user_id,
  CAST(PARSE_JSON(payload)['amount']   AS DOUBLE)  AS amount
FROM events;

Simple SELECT — lateral alias with subquery (mandatory subquery when using v; wrap the inner SELECT so v is hidden from the view's output columns):

SELECT id, user_id, amount FROM (
  SELECT
    id,
    PARSE_JSON(payload)                    AS v,
    CAST(v['user_id']  AS VARCHAR)         AS user_id,
    CAST(v['amount']   AS DOUBLE)          AS amount
  FROM events
);

CRITICAL: SELECT PARSE_JSON(payload) AS v, CAST(v['x'] AS T) AS x FROM t without the outer SELECT x FROM (...) wrapper is WRONG — it leaks v into the view output.

• With GROUP BY: use a CTE to pre-parse. The CTE goes inside CREATE VIEW ... AS, not before it:

CREATE VIEW summary AS
WITH parsed AS (
  SELECT *, PARSE_JSON(payload) AS v FROM events
)
SELECT
  CAST(v['user_id'] AS VARCHAR) AS user_id,
  COUNT(*) AS cnt
FROM parsed
GROUP BY CAST(v['user_id'] AS VARCHAR);

• [GBD-CEIL-FLOOR] CEIL/FLOOR on float/double input: Spark returns BIGINT; Feldera returns DOUBLE
• [GBD-LOG-DOMAIN] LN/LOG10 on invalid input: Spark returns NULL; Feldera returns -inf for 0 and panics (WorkerPanic) for negative values. Always wrap the argument: LN(GREATEST(x, 0)) / LOG10(GREATEST(x, 0)) to prevent the panic.

• ABS, POWER, SQRT work identically — no translation needed

• COALESCE works identically — no translation needed

• [GBD-WINDOW-FIRST-LAST] FIRST_VALUE/LAST_VALUE: IGNORE NULLS not supported; ROWS BETWEEN UNBOUNDED PRECEDING AND ... is supported; bounded ROWS BETWEEN N PRECEDING AND ... is not supported

• LAG, LEAD, SUM, AVG, COUNT, MIN, MAX window functions work identically and can be freely combined in the same query
• The QUALIFY clause is fully supported directly in Feldera — do NOT rewrite it into a subquery. When Spark uses a subquery-then-WHERE to filter on a window result, rewrite to QUALIFY.

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2 ASC, id ASC) AS rn
  FROM t
) sub
WHERE rn <= 10

These require translation but ARE supported.

[GBD-OUTER-EXPLODE] LATERAL VIEW OUTER explode — Feldera has no OUTER equivalent. Rows where the array is NULL or empty are excluded from the join result (Feldera uses INNER join semantics for UNNEST; there is no LEFT JOIN / OUTER option). Add a warning when translating OUTER.

• UNNEST on NULL returns no rows (empty).
• For arrays of ROW values, UNNEST expands struct fields as output columns automatically.
• Always provide explicit column aliases.
• WITH ORDINALITY: ordinal column comes after the value column — opposite of Spark's posexplode which puts position first; reorder in SELECT.
• EXPLODE(sequence(...)) for date range generation — sequence() is unsupported; mark the entire pattern unsupported.
• LATERAL VIEW OUTER — see [GBD-OUTER-EXPLODE].

-- Spark: inline(arr_of_structs)
SELECT o.id, item.product_id, item.qty
FROM orders o LATERAL VIEW inline(o.line_items) item AS product_id, qty

-- Feldera
SELECT o.id, item.product_id, item.qty
FROM orders o, UNNEST(o.line_items) AS item(product_id, qty)

-- Array
FROM table, UNNEST(array_col) AS alias(col_name)
-- Map (two columns)
FROM table CROSS JOIN UNNEST(map_col) AS alias(key_col, val_col)
-- With position index (1-based; subtract 1 to match Spark's 0-based posexplode)
FROM table, UNNEST(array_col) WITH ORDINALITY AS alias(val_col, pos_col)

[GBD-PIVOT-COUNT] Feldera PIVOT(COUNT(...)) returns 0 for pivot cells with no matching rows; Spark returns NULL. Add a warning. If exact NULL semantics are required, the PIVOT cannot be faithfully translated — mark unsupported and add a warning.

[GBD-TIMEZONE] Feldera treats TIMESTAMP as UTC; Spark uses the session timezone. Epoch↔timestamp conversions (unix_timestamp, from_unixtime, etc.) are only equivalent when spark.sql.session.timeZone=UTC.

[GBD-MONTHS-BETWEEN] Spark months_between returns fractional months; Feldera has no fractional-month equivalent. Use DATEDIFF(MONTH, start, end) for integer months or DATEDIFF(DAY, start, end) for exact day-precision — choose the unit that fits the downstream computation. Add a warning. The optional roundOff argument is not supported — mark unsupported if present.

Spark allows type names as functions for casting: bigint(x), string(x), int(x), double(x), boolean(x), date(x), timestamp(x), etc. Feldera does not support this functional form — rewrite as CAST(x AS type). Spark also allows PostgreSQL-style ::type cast syntax (e.g. expr::VARCHAR, expr::DECIMAL(38,5)) — Feldera supports ::type too, so pass through as-is.

[GBD-SAFE-CAST] ⚠️ Note: for REAL/DOUBLE/BOOLEAN targets, both CAST and SAFE_CAST return the type default (0.0 for numeric, false for boolean) instead of NULL or an error on invalid input. For example, SAFE_CAST('not_a_number' AS DOUBLE) returns 0.0 instead of NULL. This affects TRY_CAST translations too. Add a warning when translating to DOUBLE or BOOLEAN targets.

[GBD-SAFE-CAST-TIMESTAMP-ALIAS] ⚠️ Spark's TIMESTAMP_NTZ / TIMESTAMP_LTZ map to Feldera TIMESTAMP. When the source is a string cast to one of these aliases, apply the CAST(string AS TIMESTAMP) → SAFE_CAST rule above — e.g. CAST('a' AS TIMESTAMP_NTZ) → SAFE_CAST('a' AS TIMESTAMP), which yields NULL on an invalid string instead of panicking at runtime, matching Spark.

[GBD-SAFE-CAST-NUMERIC-TIMESTAMP] ⚠️ For the CAST(numeric AS TIMESTAMP) chain above, use SAFE_CAST on the cast steps when the numeric source can be non-finite or out of range (e.g. CAST('inf' AS DOUBLE)): SAFE_CAST(SAFE_CAST(numeric AS BIGINT) * 1000 AS TIMESTAMP). A plain CAST of ±Infinity/NaN/overflow to BIGINT or TIMESTAMP panics at runtime, whereas Spark returns NULL — SAFE_CAST matches that. (DOUBLE/BOOLEAN targets still follow [GBD-SAFE-CAST].)

LOCATE(substr, str, pos) — nested LOCATE: When pos is itself a LOCATE(...) expression, keep substr distinct from the inner expression. Never substitute the wrong character for substr.

-- Spark
LOCATE('.', email, LOCATE('@', email))

-- Feldera
CASE WHEN POSITION('.' IN SUBSTRING(email, POSITION('@' IN email))) = 0
     THEN 0
     ELSE POSITION('.' IN SUBSTRING(email, POSITION('@' IN email))) + POSITION('@' IN email) - 1
END

translate(s, from, to): Replace each character in from with the corresponding character in to using chained REPLACE calls. If from and to differ in length, mark unsupported (deletion semantics not supported).

-- translate(s, 'aei', '123')
REPLACE(REPLACE(REPLACE(s, 'a', '1'), 'e', '2'), 'i', '3')

log(base, x) — arg order is reversed: Spark: first arg = base, second arg = value. Feldera: first arg = value, second arg = base. The output SQL MUST have the arguments in the opposite order from the input. A comment saying "args swapped" without actually swapping them in the SQL is wrong.

-- Spark input         →  Feldera output (args physically swapped)
LOG(val, 2)    →  LOG(2, val)
LOG(col, 10)   →  LOG(10, col)
LOG(b, x)      →  LOG(x, b)

Do NOT be misled by column alias names like log2_col — follow the rule, not the alias. If LOG(A, B) appears in Spark, the Feldera output must be LOG(B, A) — always.

date_add and date_sub take exactly 2 args: (date_expr, integer_days) → expr ± INTERVAL 'n' DAY. For unit-first 3-arg form use TIMESTAMPADD(DAY, 30, d) — it is a different function.

Do NOT emit these wrong forms:

DATE_ADD(d, 30, 'DAY')                  -- 3-arg hybrid: wrong
DATE_ADD(d, INTERVAL '30' DAY, 'DAY')   -- 3-arg hybrid: wrong
DATE_ADD(DAY, 30, d)                    -- TIMESTAMPADD signature: wrong

named_struct('a', v1, 'b', v2) → CAST(ROW(v1, v2) AS ROW(a T, b S)) when field names matter, or plain ROW(v1, v2) when they don't.

• Named ROW field access: (row_val).field_name — always wrap the column in parentheses. Bare row_val.field_name is a syntax error (not a compiler bug): standard SQL parses x.y as table.column, so the compiler reports "Table 'row_val' not found".
• Anonymous ROW field access (no CAST): row_val[1] (1-based index)
• Struct field access inside lambdas: transform(arr, x -> x.field_name) where elements are anonymous ROW(...) — use 1-based index instead: transform(arr, x -> x[n]). Example: transform(arr, x -> x.name) where arr is ARRAY_AGG(ROW(ordinality, name)) → transform(arr, x -> x[2]) (name is the 2nd field).
• STRUCT column in DDL becomes ROW column — all field accesses in SELECT and WHERE must use (col).field syntax:

  -- Spark: col.field
  SELECT details.department, details.salary FROM t WHERE details.salary > 0
  -- Feldera: (col).field
  SELECT (details).department, (details).salary FROM t WHERE (details).salary > 0

-- Spark
SELECT source, COUNT(DISTINCT named_struct('l', left_id, 'r', right_id)) AS unique_pair_count
FROM events GROUP BY source

-- Feldera (anonymous ROW)
SELECT source, COUNT(DISTINCT ROW(left_id, right_id)) AS unique_pair_count
FROM events GROUP BY source

-- Feldera (named fields via CAST)
SELECT source, COUNT(DISTINCT CAST(ROW(left_id, right_id) AS ROW(l BIGINT, r BIGINT))) AS unique_pair_count
FROM events GROUP BY source

• POSITION(x'...' IN x'...') — binary search, pass through unchanged
• OCTET_LENGTH(x'...') — correct byte count (use instead of LENGTH, which counts hex chars not bytes)
• SUBSTRING(x'...' FROM n FOR m) — binary slicing
• REPLACE(x'...', x'...', x'...') — binary replace
• Comparisons with x'...' literals

-- Spark
contains(x'aa', x'bb')
-- Feldera
POSITION(x'bb' IN x'aa') > 0

-- Spark (POSITION with binary literals passes through unchanged)
SELECT POSITION(x'537061726b' IN x'537061726b2053514c') > 0
-- Feldera
SELECT POSITION(x'537061726b' IN x'537061726b2053514c') > 0

• CONCAT(x'...', x'...') — fails: BINARY not supported in CONCAT
• TRIM(x'...') — fails: TRIM does not accept BINARY
• RPAD(varchar_col, n, x'...') — fails: binary literal where VARCHAR expected
• LENGTH(x'...') — compiles but returns hex char count, not byte count; use OCTET_LENGTH instead
• UPPER(x'...') / LOWER(x'...') — compile but are no-ops on binary; do not use
• CAST(x'...' AS VARCHAR) — returns the hex string (e.g. 48656c6c6f), not the decoded string (Hello)

Always wrap VALUES (...) in parentheses and add an alias: FROM (VALUES ...) AS alias(cols).

Preserve implicit column names: When Spark uses FROM VALUES (...) without explicit column aliases, it assigns implicit names like col1, col2. If the query references those names, the alias in AS t(...) must use the same names.

-- Spark: VALUES column is implicitly named 'col1'
SELECT 1 AS col1, col1 FROM VALUES (10) ORDER BY col1;

-- Feldera WRONG — renamed to x, but Spark query references 'col1'
SELECT 1 AS col1, x FROM (VALUES (10)) AS t(x) ORDER BY col1;

-- Feldera CORRECT — preserve 'col1' so all references still work
SELECT 1 AS col1, col1 FROM (VALUES (10)) AS t(col1) ORDER BY col1;

When GROUP BY or HAVING is present but there is no FROM clause, add FROM (VALUES (1)) AS t(x).

-- Spark (valid)
SELECT 1 GROUP BY COALESCE(1, 1) HAVING COALESCE(1, 1) = 1;

-- Feldera (add dummy FROM)
SELECT 1 AS col1 FROM (VALUES (1)) AS t(x) GROUP BY COALESCE(1, 1) HAVING COALESCE(1, 1) = 1;

• If (SELECT expr) has no FROM, rewrite as just expr.
• If the subquery has a FROM clause, leave it as-is — it is a regular or correlated subquery, not this shorthand.
• (SELECT 1 WHERE cond) returns 1 if cond is true, NULL otherwise — so a = (SELECT 1 WHERE cond) simplifies to a = 1 AND cond.

-- Spark
HAVING (SELECT col1.a = 1)   →  HAVING col1.a = 1
WHERE  (SELECT x > 0)        →  WHERE  x > 0

-- Spark
HAVING MAX(col2) == (SELECT 1 WHERE MAX(col2) = 1)
-- Feldera
HAVING MAX(col2) = 1

Feldera supports lateral aliases in SELECT — no rewrite needed. SELECT col1 AS a, a AS b FROM t passes through unchanged.

-- Spark: valid
SELECT col1 AS a, a AS b FROM t
-- Feldera: also valid — pass through unchanged
SELECT col1 AS a, a AS b FROM t

No rewrite needed in general — Feldera resolves names in HAVING/GROUP BY to the source column, same as Spark.

When the SELECT alias reuses a GROUP BY source column name with GROUPING SETS/CUBE/ROLLUP, Feldera resolves HAVING to the aggregate alias; Spark resolves it to the source column. Workaround: reference the source column explicitly in HAVING.

-- Data: VALUES (1, 10), (2, 20) AS T(a, b)
-- 'b' is both a source column (values 10, 20) and a SELECT alias for SUM(a) (values 1, 2)

-- Spark: HAVING b → column b (10 or 20) → 2 rows returned (b=20 > 10, appears twice in GROUPING SETS)
-- Feldera: HAVING b → alias SUM(a) (1 or 2) → 0 rows returned
SELECT SUM(a) AS b FROM T GROUP BY GROUPING SETS ((b), (a, b)) HAVING b > 10;

-- Workaround: rename the SELECT alias to avoid shadowing the source column
SELECT SUM(a) AS sum_a FROM T GROUP BY GROUPING SETS ((b), (a, b)) HAVING b > 10;

• In ORDER BY or HAVING, always prefix struct column access with the table alias to avoid misparsing.
• Non-aggregate HAVING with struct field → move to WHERE.

ORDER BY t.col.field   -- correct
ORDER BY col.field     -- may fail (Feldera misparses as table.column)

-- Spark
SELECT col1 FROM t GROUP BY col1 HAVING col1.a = 1
-- Feldera
SELECT col1 FROM t WHERE t.col1.a = 1 GROUP BY col1

Feldera supports both EXCEPT and EXCLUDE for column exclusion — pass through unchanged, no rewrite needed.

-- Spark
SELECT SEC.* EXCEPT(Status, conameorcik), ...

-- Feldera: same — pass through unchanged
SELECT SEC.* EXCEPT(Status, conameorcik), ...

When the Feldera compiler rejects a query, distinguish the cause before deciding how to respond:

These are not compiler bugs — the SQL you emitted is invalid. Fix the translation and retry.

Key rule: if the error points to a specific line and column with a clear parse or type message, it is a syntax/type error — fix the SQL. Do not mark the construct as unsupported.

These are cases where the syntax is valid SQL but Feldera has no implementation:

• No match found for function signature F(...) for a function that does not exist in Feldera at all (e.g. REVERSE, uuid, substring_index)
• Feature not yet implemented messages

For these, mark as unsupported per the rules below. Do not retry with different syntax — the feature is absent, not broken.

When an unsupported construct is found:

1. List each unsupported construct in the unsupported array with a brief explanation.
2. Still produce a CREATE VIEW: replace each unsupported expression with CAST(NULL AS <type>), translate all other parts normally. An empty feldera_query is never acceptable.
3. Do NOT enter the repair loop for known-unsupported functions.

Do NOT:

• Approximate with a different function that changes semantics.
• Keep retrying after a compiler "No match found" error for a known-unsupported function.
• Silently change semantics to make the SQL compile.
• Flag a table as unsupported just because it is referenced inside a dependency view (e.g. a staging view provided in the schema) but not directly used by the main query being translated. Missing tables in dependency views are a deployment concern, not a translation error — do not list them in unsupported.

All scalar bitwise operators are unsupported in Feldera — no scalar equivalent exists for any of them.

Spark's from_json(col, schema) parses a JSON string. In Feldera, use CAST(PARSE_JSON(col) AS type) where type is the Feldera equivalent of the Spark schema string (e.g., MAP<VARCHAR,VARCHAR>, VARCHAR ARRAY, ROW(field1 TYPE1, field2 TYPE2)). Specific patterns below.

| from_json(col, schema) IS NOT NULL | col IS NOT NULL — from_json never returns NULL for non-null input (bad JSON → struct with NULL fields), so the check reduces to the column itself | | from_json(col, 'field TYPE')['field'] | CAST(PARSE_JSON(col)['field'] AS type) | | COALESCE(from_json(col, schema)['field'], default) | COALESCE(CAST(PARSE_JSON(col)['field'] AS type), default) — never drop the COALESCE or its default value |

Databricks/Spark SQL allows col:field as shorthand for extracting a JSON sub-field from a VARCHAR/STRING column containing JSON. Translate it as PARSE_JSON bracket access:

[GBD-INLINE-STRUCT-ARRAY] When translating inline(from_json(json_col, 'Array<struct<f1 T1, f2 T2>>')) from a LATERAL VIEW or SELECT clause:

• json_col may itself be a colon-syntax expression — translate it first (e.g. col:steps → PARSE_JSON(col)['steps'])
• Move any SELECT-clause inline(...) into a FROM-clause comma join
• Full translation pattern:

-- Spark (LATERAL VIEW form)
SELECT t.id, steps.step_name, steps.step_uuid
FROM t
LATERAL VIEW inline(from_json(t.payload:steps, 'Array<struct<name STRING, uuid STRING>>')) steps AS step_name, step_uuid

-- Feldera
SELECT t.id, steps.step_name, steps.step_uuid
FROM t
, UNNEST(CAST(PARSE_JSON(t.payload)['steps'] AS ROW(name VARCHAR, uuid VARCHAR) ARRAY)) AS steps(step_name, step_uuid)

• Map struct field types using the Type Mappings section.
• Add a warning. Do NOT mark unsupported.