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.

• Do NOT hallucinate restrictions that don't exist (e.g., "Multiple RANK aggregates per window" is NOT an error).
• You CAN combine LAG, LEAD, SUM OVER, etc. in the same query — no restriction.

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 definition: Spark treats ' ' (space), \t (tab), \n (newline), \r (carriage return), and other Unicode whitespace as "whitespace" in any operation that involves trimming or whitespace-awareness. Feldera follows the SQL standard and only considers ASCII space (0x20) as whitespace. This affects TRIM, LTRIM, RTRIM, CAST(str AS BOOLEAN), and any other function that implicitly strips whitespace. If the input may contain \t or \n at the edges, the results will differ.

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

• [GBD-AGG-TYPE] Aggregate return types on numeric inputs: Spark often widens numeric aggregates regardless of input type; Feldera follows the SQL standard and 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 DOUBLE)) 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 DOUBLE)) 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 returns DECIMAL(p,s) (same scale). No exact rewrite is possible.

• [GBD-DIV-ZERO] Division by zero / overflow: Spark typically returns NULL or Infinity for division by zero and silently wraps on overflow. Feldera panics on integer division by zero or integer overflow (checked_div — panics with message "'a / b' causes overflow"). Floating-point division by zero returns Infinity (same as Spark). try_divide/try_add/try_subtract/try_multiply cannot be safely rewritten — mark unsupported.

• [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-NAN] NaN values: Spark represents IEEE 754 NaN as nan in output; Feldera computes NaN correctly but the Python SDK serializes it as None (Python SDK bug).

• [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/map function element order: Feldera returns elements in sorted order for set-based and aggregation functions; Spark preserves the original element order. Affected: ARRAY_UNION, ARRAY_EXCEPT, ARRAY_INTERSECT, MAP_KEYS, MAP_VALUES, ARRAY_AGG / collect_list, ARRAY_AGG(DISTINCT) / collect_set. Results contain the same elements but may be in different positions.

• [GBD-FLOAT-GROUP] ORDER BY on special float values: ORDER BY on DOUBLE columns containing NaN, +Inf, -Inf, or -0.0 produces different row order. Spark follows IEEE 754 (-Inf < finite < +Inf, NaN last); Feldera uses a different sort order (e.g. 0 sorts before -Inf). Both engines group these values correctly — only the output ordering differs. Avoid ORDER BY on float columns containing special values, or add a secondary sort key.

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 'borrowerid' 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 (e.g. timestamp, date, time)
• Apply quoting consistently in both CREATE TABLE and every query reference
• Do not quote ordinary identifiers — quoting non-reserved words makes them case-sensitive and adds noise

-- 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
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:

• [GBD-BOOL-WINDOW] every/bool_and/bool_or/some as window functions with ORDER BY on a BOOLEAN column are not supported in Feldera — compiler error: "OVER currently cannot sort on columns with type 'BOOL'"

• COUNT, SUM, MIN, MAX, COUNT(DISTINCT ...), bool_or, bool_and work identically — no translation needed

• [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, REVERSE, 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.

• filter, zip_with, exists, and other higher-order functions are unsupported or require rewriting — see Rewritable patterns and Unsupported sections.

• [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

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: avoid exposing the parsed JSON object as a view column. Feldera supports lateral aliases (PARSE_JSON(col) AS v), but v becomes a real output column of the view. Only include columns that were in the original Spark query — wrap in a subquery to hide v:

Simple SELECT — parse inline (preferred when only extracting fields):

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

Simple SELECT — lateral alias with subquery (use when parsing once is needed for performance):

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 raw_events
);

• 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 raw_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-ROUNDING] ROUND: Spark rounds half-up (0.5 → 1); Feldera rounds half-to-even (0.5 → 0, 1.5 → 2)
• [GBD-LOG-DOMAIN] LN/LOG10 on invalid input: Spark returns NULL; Feldera returns -inf for 0 and panics (WorkerPanic) for negative values

• 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 frame clauses not supported — partition must be unbounded

• LAG, LEAD, SUM, AVG, COUNT, MIN, MAX window functions work identically and can be freely combined in the same query
• The QUALIFY clause is supported directly in Feldera:

SELECT *, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
FROM employees
QUALIFY rnk = 1

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

• [GBD-TOPK-FILTER] ROW_NUMBER/RANK/DENSE_RANK without an outer WHERE filter on the result are not supported in Feldera — always wrap in a subquery and filter on the result

These require translation but ARE supported.

[GBD-JSON-CAST] CAST(VARIANT AS VARCHAR) returns NULL for numeric and boolean JSON values.

• In Spark: get_json_object(s, '$.num') returns a string like "42" regardless of the JSON type.
• In Feldera: CAST(PARSE_JSON(s)['num'] AS VARCHAR) returns NULL when the JSON value is a number or boolean — cast to the numeric type first, then to VARCHAR if a string is needed.

-- Feldera correct: cast number to DOUBLE first, then to VARCHAR
CAST(CAST(PARSE_JSON(s)['num'] AS DOUBLE) AS VARCHAR)

-- Feldera wrong: returns NULL for numeric JSON values
CAST(PARSE_JSON(s)['num'] AS VARCHAR)

[GBD-OUTER-EXPLODE] LATERAL VIEW OUTER explode — Feldera has no OUTER equivalent. Rows where the array is NULL or empty are dropped. Add a warning when translating OUTER.

[GBD-PIVOT-NULL] Spark PIVOT returns NULL for pivot values with no matching rows; plain COUNT(CASE WHEN ...) returns 0. Use NULLIF(..., 0) to match Spark's NULL semantics.

-- Feldera equivalent of PIVOT(COUNT(case_id) FOR priority IN ('LOW', 'MEDIUM'))
SELECT region,
  NULLIF(COUNT(CASE WHEN priority = 'LOW'    THEN case_id END), 0) AS "LOW",
  NULLIF(COUNT(CASE WHEN priority = 'MEDIUM' THEN case_id END), 0) AS "MEDIUM"
FROM t
GROUP BY region

[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 rounded to 8 decimal digits (e.g. 3.94959677); Feldera DATEDIFF(MONTH, ...) returns integer months. Add a warning if the result is used in arithmetic. The optional roundOff argument is not supported — mark unsupported if present.

[GBD-SAFE-CAST] SAFE_CAST is not a perfect equivalent of Spark's TRY_CAST for all types. For REAL/DOUBLE/BOOLEAN targets, SAFE_CAST returns the type default (0 for numeric, false for boolean) instead of NULL on failure — there is no exact NULL-on-failure equivalent for those types in Feldera.

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): REGEXP_REPLACE treats each character as a regex pattern — escape special regex chars (. → [.], * → [*], etc.) if they appear in the from string. If from contains many special chars, mark unsupported.

-- translate(s, 'aei', '123')
REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_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. Always swap both arguments — no exceptions, regardless of column names or alias hints.

-- Spark
LOG(amplitude, 2)   →  Feldera: LOG(2, amplitude)
LOG(col, 10)        →  Feldera: LOG(10, col)

Do NOT be misled by column alias names like log2_col — follow the rule, not the alias.

• 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)

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
• Anonymous ROW field access (no CAST): row_val[1] (1-based index)

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

-- Feldera (anonymous ROW)
SELECT source, COUNT(DISTINCT ROW(left_id, right_id)) AS unique_pair_count
FROM pair_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 pair_events GROUP BY source

x'...' literals in POSITION, comparisons, and binary-typed expressions.

SELECT POSITION(x'537061726b' IN x'537061726b2053514c') > 0;
SELECT contains(x'aa', x'bb');  -- rewrite: POSITION(x'bb' IN x'aa') > 0

Passing x'...' to VARCHAR-expecting functions — type mismatch fails.

RPAD(varchar_col, n, x'...')  -- wrong: binary literal where VARCHAR expected

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

If an expression is just an alias name from an earlier column (a AS b where a was defined as col1 AS a), replace with the original expression (col1 AS b).

-- Spark: valid
SELECT col1 AS a, a AS b FROM t
-- Feldera: rewrite
SELECT col1 AS a, col1 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: reference the source column explicitly
SELECT SUM(a) AS b FROM T GROUP BY GROUPING SETS ((b), (a, b)) HAVING MAX(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

When an unsupported construct is found:

1. If any part of a query is unsupported, treat the entire query as unsupported.
2. List each unsupported construct in the unsupported array with a brief explanation.
3. If the entire query depends on the unsupported construct, set feldera_query to an empty string.
4. 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.

If any part of a query is unsupported, treat the entire query as unsupported. Do not emit a partial view — return the schema only (no CREATE VIEW) and list the unsupported constructs.

Rationale: a partial translation produces incorrect results, which is worse than no result.

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

When the Feldera compiler rejects translated SQL, check these common causes first: