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

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

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

P2: remaining expressions and collection/JSON support

• #90 - Native collection access: support column-dependent ARRAY indexes and MAP keys
• #91 - Native SQL/JSON: remaining selectors beyond the verified definite-path grammar; empty quoted members shipped in PR #131; negative literal indexes shipped in PR #150; standard JSON/Unicode and printable ASCII member-name escapes shipped in PR #156, literal Unicode/control escapes in PR #160, trailing ASCII index controls in PR #163, and literal dot-member punctuation/control/Unicode names in PR #172 (on main through PR #170), wildcard/member/index combinations in PR #173, and array-index unions in PR #175; batch JVM coverage for remaining scalar Calc forms shipped in PR #176 (00b8a172); nested ARRAY/ROW boundaries shipped in PR #199 (9dc1d9e7) after all 26 CI checks passed; non-Calc, MAP/MULTISET and unsupported nested leaves remain
• #92 - Native Calc: support RAND and RAND_INTEGER under the nondeterministic-function policy

P2: aggregates and relational plan shapes

• #95 - Native GROUP BY: add FIRST_VALUE, LAST_VALUE and SINGLE_VALUE aggregates
• #97 - Native window aggregation: support COUNT(DISTINCT) with window-scoped state
• #98 - Native window aggregation: support AVG mixed with other aggregates and two-phase merging
• #99 - Integer SUM and numeric COUNT/COUNT(*) retracting event-time TUMBLE/HOP/CUMULATE shipped in PR #150; grouping-only and integer AVG shipped in PR #154, FLOAT/DOUBLE AVG in PR #155, FLOAT/DOUBLE SUM in PR #157 DECIMAL SUM in PR #158 and DECIMAL AVG in PR #161, and filtered aligned event-time COUNT/SUM/AVG in PR #174; append-only filtered numeric MIN/MAX shipped in PR #177 (81b5f00c); filtered append-only COUNT(DISTINCT) shipped in PR #178 (13edaa7c), with all 25 CI checks passing; other aggregate and window forms remain
• #100 - Native SEMI/ANTI join: support null-safe equality keys
• #101 - Native regular join: add a verified no-equi-key CROSS JOIN path
• #102 - Retracting OFFSET: upstream stateful mini-batch ordering remains; retained row kinds/counts and recovery shipped in PR #132 (40290ec9)
• #103 - Native time-ordered rank: support processing-time first-N beyond deduplication
• #104 - Non-null partition-derived bounds run natively for all three rank strategies; computed-key proofs shipped in #179 (ebcb50f4). Independently changing append-only bounds shipped in #180 (c744ce0c), and general retracting first-bound state, independent TTL, recovery, rescaling and mismatch metrics shipped in #181 (ec923f0d). Both passed all 25 CI checks before merging. Changing update-fast bounds with TTL disabled shipped in #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.
• #105 - Native OVER: support COUNT(*) together with ROWS UNBOUNDED PRECEDING

P3: floating-point work

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

Test setup and investigations

• #106 - SQL parity harness: register portable scalar/table/aggregate functions and changelog sources
• #107 - SQL parity harness: preserve parameter variants, dialect semantics and execution modes
• #108 - Independent failure comparison and typed JSON RETURNING conversion exceptions shipped in 3a3ae31d / 084d3d1c
• #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 #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.

• #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 #133 (0a9d7849). #116 is complete: shared mutable binary results across call sites preserve complete-row Flink evaluation through the Arrow batch bridge (PR #162), including lifecycle, exception and changelog parity.

• #82 and #83 are shipped UDF identity/lifecycle fixes. #81 is complete: intermediate JSON STRING identity is preserved through fused scalar consumers, with explicit fallback at boundaries that cannot preserve it (PR #150).

• #84 is complete: the host cast warmup and forced delta-join guard are shipped, and interval parity is verified on the current temporal implementation.

• #126 is complete: folded typed NULL expressions retain their declared Arrow type and compose with dynamic collection access.

• #124 is complete: integer/string CAST and TRY_CAST use native kernels without JVM callbacks, with released-Flink parity and release benchmarks. #123 is complete: verified constant numeric FROM_UNIXTIME formats use native evaluation, with the JVM bridge retained for other patterns and zones (PR #150).

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

• #105 is the focused COUNT(*)/unbounded-ROWS follow-up to #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.

Start by reading the remaining child tickets, especially #91, #99, #102, and #104, alongside the parity-harness work in #106 and #107. Use the released Apache Flink 2.2.1 oracle and preserve the distinctions between host failures, fallback, scan-only queries, and native parity. The work is done when a scoped child item has implementation links, native execution and parity evidence, updated documentation, and is closed.