Track native SQL support gaps and parity-harness follow-ups from the September audit

[liuyongvs]

Objective

Track the native support and test-infrastructure gaps identified by the SQL parity audit at main ebe550c69122909cd08fca8ee89380478a92cc5c, using released Apache Flink 2.2.1 as the oracle. Prioritize STRING/VARCHAR, DECIMAL, INT/BIGINT, TIMESTAMP and TIMESTAMP_LTZ; floating-point-only work is lower priority.

Evidence and scope

• 51 query records contain explicit planner fallback reasons. A query can have several blockers, so one completed feature does not necessarily unlock a whole query.
• 79 further zero-substitution records optimize to TableSourceScan alone after fixture/sink adaptation. They are not 79 missing compute operators and do not prove original connector IO or recovery contracts.
• 53 queries fail on the host before native comparison: 32 missing functions, 3 missing tables, 6 FIRST_VALUE signature differences, 4 temporal signatures, 2 implicit conversions, 2 streaming shape/mode restrictions, 2 error-producing inputs and 2 JSON RETURNING runtime exceptions.
• One of the 51 planner-rejected records also has an unexpanded JSON-path test parameter. Track its intended variants through the harness ticket rather than counting it as complete path coverage.
• SQL examples in the child tickets are shapes for minimized regressions, not claims that every example has independently been executed. Preserve full-query evidence and assert native substitutions in new tests.

The 25 work items below comprise 21 feature-support tickets and 4 test/investigation tickets. They do not create native semantics for SQL that the released host cannot execute.

P1: common strings, exact numerics and temporal types

• Native Calc: support IFNULL without forcing surrounding operators to fall back #85 - Native Calc: support IFNULL without forcing surrounding operators to fall back
• Native CAST: support STRING/VARCHAR to BOOLEAN with Flink parsing semantics #86 - Native CAST: support STRING/VARCHAR to BOOLEAN with Flink parsing semantics
• Native INSTR: support start position and occurrence arguments #87 - Native INSTR: support start position and occurrence arguments
• Native DECIMAL: close ROUND, literal rounding and integral-cast admission gaps #88 - Native DECIMAL: close ROUND, literal rounding and integral-cast admission gaps
• Native scalar UDF bridge: admit DECIMAL and VARBINARY return values #89 - Native scalar UDF bridge: admit DECIMAL and VARBINARY return values
• Native GROUP BY: support MIN/MAX over TIMESTAMP values #94 - Native GROUP BY: support MIN/MAX over TIMESTAMP values
• Native window TVF: support standalone plain TIMESTAMP rowtime #96 - Native window TVF: support standalone plain TIMESTAMP rowtime

P2: remaining expressions and collection/JSON support

• Native collection access: support column-dependent ARRAY indexes and MAP keys #90 - Native collection access: support column-dependent ARRAY indexes and MAP keys
• Native SQL/JSON: extend selectors beyond the verified definite-path grammar #91 - Native SQL/JSON: remaining selectors beyond the verified definite-path grammar; empty quoted members shipped in PR Accelerate SQL/JSON paths selecting empty object keys #131; negative literal indexes shipped in PR Integrate SQL audit coverage and native retracting windows #150; standard JSON/Unicode and printable ASCII member-name escapes shipped in PR Support verified escaped JSON member paths #156, literal Unicode/control escapes in PR Support escaped Unicode and control characters in JSON paths #160, trailing ASCII index controls in PR Accelerate verified whitespace after SQL/JSON array indexes #163, and literal dot-member punctuation/control/Unicode names in PR Support literal punctuation and controls in JSON dot-member paths #172 (on main through PR Support partition-derived bounds for update-fast Top-N #170), wildcard/member/index combinations in PR Run SQL/JSON wildcard paths natively #173, and array-index unions in PR Accelerate SQL/JSON array-index unions #175; batch JVM coverage for remaining scalar Calc forms shipped in PR Extend SQL/JSON coverage through the batch JVM bridge #176 (00b8a17); nested ARRAY/ROW boundaries shipped in PR Support nested ARRAY and ROW values in the JSON JVM batch bridge #199 (9dc1d9e) after all 26 CI checks passed; non-Calc, MAP/MULTISET and unsupported nested leaves remain
• Native Calc: support RAND and RAND_INTEGER under the nondeterministic-function policy #92 - Native Calc: support RAND and RAND_INTEGER under the nondeterministic-function policy

P2: aggregates and relational plan shapes

• Native GROUP BY: add FIRST_VALUE, LAST_VALUE and SINGLE_VALUE aggregates #95 - Native GROUP BY: add FIRST_VALUE, LAST_VALUE and SINGLE_VALUE aggregates
• Native window aggregation: support COUNT(DISTINCT) with window-scoped state #97 - Native window aggregation: support COUNT(DISTINCT) with window-scoped state
• Native window aggregation: support AVG mixed with other aggregates and two-phase merging #98 - Native window aggregation: support AVG mixed with other aggregates and two-phase merging
• Native window aggregation: support retracting input from upstream Top-N #99 - Integer SUM and numeric COUNT/COUNT(*) retracting event-time TUMBLE/HOP/CUMULATE shipped in PR Integrate SQL audit coverage and native retracting windows #150; grouping-only and integer AVG shipped in PR Support retracting grouping-only and integer AVG windows #154, FLOAT/DOUBLE AVG in PR Support floating AVG over updating windows #155, FLOAT/DOUBLE SUM in PR Support FLOAT and DOUBLE SUM in retracting windows #157 DECIMAL SUM in PR Support DECIMAL SUM in retracting windows #158 and DECIMAL AVG in PR Support retracting DECIMAL AVG windows #161, and filtered aligned event-time COUNT/SUM/AVG in PR Support filtered numeric event-time window aggregates #174; append-only filtered numeric MIN/MAX shipped in PR Support filtered numeric MIN/MAX in event-time windows #177 (81b5f00); filtered append-only COUNT(DISTINCT) shipped in PR Support filtered COUNT(DISTINCT) in event-time windows #178 (13edaa7), with all 25 CI checks passing; other aggregate and window forms remain
• Native SEMI/ANTI join: support null-safe equality keys #100 - Native SEMI/ANTI join: support null-safe equality keys
• Native regular join: add a verified no-equi-key CROSS JOIN path #101 - Native regular join: add a verified no-equi-key CROSS JOIN path
• Native retracting OFFSET: preserve upstream stateful mini-batch changelog order #102 - Retracting OFFSET: upstream stateful mini-batch ordering remains; retained row kinds/counts and recovery shipped in PR Preserve Flink retained row kinds for retracting OFFSET #132 (40290ec)
• Native time-ordered rank: support processing-time first-N beyond deduplication #103 - Native time-ordered rank: support processing-time first-N beyond deduplication
• Native Top-N: support nullable and changing variable rank bounds #104 - Non-null partition-derived bounds run natively for all three rank strategies; computed-key proofs shipped in Recognize computed partition keys for variable Top-N bounds #179 (ebcb50f). Independently changing append-only bounds shipped in Accelerate append-only Top-N with changing bounds #180 (c744ce0), and general retracting first-bound state, independent TTL, recovery, rescaling and mismatch metrics shipped in Accelerate retracting Top-N with changing bounds #181 (ec923f0). Both passed all 25 CI checks before merging. Changing update-fast bounds with TTL disabled shipped in Retain changing update-fast Top-N bounds with TTL disabled #201 after all required CI checks passed (4.814× in the recorded release workload, with recovery/rescaling and upstream native-execution evidence). Remaining: nullable bounds, update-fast changing bounds with positive TTL, and first-proposal ordering through upstream stateful mini-batches.
• Native OVER: support COUNT(*) together with ROWS UNBOUNDED PRECEDING #105 - Native OVER: support COUNT(*) together with ROWS UNBOUNDED PRECEDING

P3: floating-point work

• Native expressions: track remaining DECIMAL-to-FLOAT, ARRAY<FLOAT> and POWER gates #93 - Native expressions: track remaining DECIMAL-to-FLOAT, ARRAY and POWER gates

Test setup and investigations

• SQL parity harness: register portable scalar/table/aggregate functions and changelog sources #106 - SQL parity harness: register portable scalar/table/aggregate functions and changelog sources
• SQL parity harness: preserve parameter variants, dialect semantics and execution modes #107 - SQL parity harness: preserve parameter variants, dialect semantics and execution modes
• SQL parity harness: compare expected failures instead of stopping at the host exception #108 - Independent failure comparison and typed JSON RETURNING conversion exceptions shipped in 3a3ae31 / 084d3d1
• Report confirmed Flink JSON_VALUE RETURNING conversion failures upstream #109 - Link an upstream report for independently reproduced JSON_VALUE RETURNING host failures

Existing related work

• Additional verified gap: Table API scalar projections retaining an AS wrapper fall back with Calc: unsupported function/operator: AS. Upstream constructor-state, inline and non-static object UDF fixtures each reproduce this on both backend variants. PR Audit native and fallback routes in unchanged upstream UDF tests #206 shipped exact route contracts and coverage documentation in b94064cc after all 26 checks passed; alias admission itself remains unimplemented. The same run proves native work for rich-function job parameters, multiple functions and code-generation splitting.

• Preserve pre-conversion nullness when consuming a DECIMAL scalar UDF result #115 is complete: DECIMAL scalar-UDF consumers preserve external nullness and values in fused Flink-generated evaluators inside native pipelines; lifecycle and exception parity shipped in PR Preserve Flink nullness and external values in DECIMAL UDF consumers #133 (0a9d784). Preserve shared mutable binary UDF results across multiple call sites #116 is complete: shared mutable binary results across call sites preserve complete-row Flink evaluation through the Arrow batch bridge (PR Preserve shared binary UDF results across complete Calc rows #162), including lifecycle, exception and changelog parity.

• Native Calc dispatches user-defined functions by builtin name and silently runs the wrong function #82 and Native Calc opens a shared scalar UDF once per call site, corrupting lifecycle-dependent INT results #83 are shipped UDF identity/lifecycle fixes. Preserve surrogate identity in intermediate JSON STRING expressions #81 is complete: intermediate JSON STRING identity is preserved through fused scalar consumers, with explicit fallback at boundaries that cannot preserve it (PR Integrate SQL audit coverage and native retracting windows #150).

• Track main correctness fixes for CAST initialization, day-time intervals, and FORCE join validation (PR #30) #84 is complete: the host cast warmup and forced delta-join guard are shipped, and interval parity is verified on the current temporal implementation.

• Typed NULL projections produced by constant folding cause native Calc fallback #126 is complete: folded typed NULL expressions retain their declared Arrow type and compose with dynamic collection access.

• Native CAST: support STRING/VARCHAR to integer and integer to STRING without JVM upcalls #124 is complete: integer/string CAST and TRY_CAST use native kernels without JVM callbacks, with released-Flink parity and release benchmarks. Support fully native FROM_UNIXTIME evaluation for constant format strings #123 is complete: verified constant numeric FROM_UNIXTIME formats use native evaluation, with the JVM bridge retained for other patterns and zones (PR Integrate SQL audit coverage and native retracting windows #150).

• Report confirmed Flink JSON_VALUE RETURNING conversion failures upstream #109 is complete: five independent host-contract/native-control tests and documented audit classification now link upstream FLINK-40463 and Apache Flink PR #29063.

• Native OVER: support COUNT(*) together with ROWS UNBOUNDED PRECEDING #105 is the focused COUNT(*)/unbounded-ROWS follow-up to Native OVER aggregation: support AVG, COUNT(*), and decimal value columns #8, which was closed as not planned. It does not claim that the entire older AVG/DECIMAL OVER backlog is complete.

The implementations for #81, #93, #95, #97, #106, #107 and #123 shipped with the integrated stack in PR #150. The harness for #106/#107 uses a portable corpus derived from the public issues; the original independently produced September audit corpus was unavailable, so this is not a claim to have replayed it. The integrated tree passed all required CI and unchanged upstream suites before merging.

Completion criteria

Each child issue should link its implementation and parity evidence, identify actual native execution versus fallback, update the relevant documentation and close when its scoped work ships. The harness must account separately for host setup errors, expected exceptions, scan-only queries, explicit fallback and native result/changelog parity. Reassess remaining blockers after each implementation instead of assuming the original query counts are additive.