cjohnson-confluent opened a new pull request, #29063: URL: https://github.com/apache/flink/pull/29063
## What is the purpose of the change `JSON_VALUE` and `JSON_QUERY` both accept a `RETURNING` clause, but only a narrow set of types is supported. This PR widens both: - `JSON_VALUE`: adds TINYINT, SMALLINT, BIGINT, FLOAT, and DECIMAL. Fixes the existing INTEGER and DOUBLE casts, which fail at runtime on certain JSON values (INTEGER throws on large numbers because Jayway returns Long instead of Integer; DOUBLE throws on integers because Jayway returns Integer instead of BigDecimal). All numeric casts now go through range-checked conversions that route to ON ERROR behavior on overflow. FLOAT and DOUBLE reject infinity/NaN as overflow. This matches MySQL, Oracle, and PostgreSQL, which all treat numeric overflow as a conversion error handled by ON ERROR. - `JSON_QUERY`: adds ARRAY with numeric and boolean element types (INT, TINYINT, SMALLINT, BIGINT, FLOAT, DOUBLE, DECIMAL, BOOLEAN). Previously only ARRAY\<VARCHAR\> was accepted. Array element conversions use the same range-checked methods. - **String coercion**: JSON string values (e.g. `"42"`) are parsed as numbers when the RETURNING clause requests a numeric type, matching MySQL and PostgreSQL semantics. Boolean string coercion follows PostgreSQL's bool input function (`true/t/yes/1`, `false/f/no/0`, case-insensitive). Non-parseable strings route to ON ERROR. ### Architectural approach Type conversion and error handling live in testable Java methods in `SqlJsonUtils` (`convertJsonScalar`, `convertJsonArray`) rather than in Scala codegen string templates. The codegen files emit a single method call each instead of inline type-dispatch + try/catch + error-fallback blocks. This eliminates a class of string-template bugs where generated variable names can be misreferenced. A custom `JsonConversionException` replaces `ClassCastException` as the internal control-flow mechanism, preventing unrelated ClassCastExceptions (from actual codegen bugs) from being silently swallowed by ON ERROR handling. A shared `isSupportedJsonReturningType()` method serves as the single source of truth for accepted types, preventing validation and codegen type lists from diverging. Also fixes: - DECIMAL precision overflow (e.g. `123456789.99 RETURNING DECIMAL(5,2)`) silently returning null instead of triggering ON ERROR - BigInteger overflow in BIGINT conversion silently truncating (Jayway returns BigInteger for numbers exceeding Long.MAX_VALUE) - NULL ON ERROR in JSON_QUERY typed arrays returning a partially-filled array instead of null (codegen bug: wrong variable nulled in catch block) - String-literal DEFAULT values with DECIMAL RETURNING causing a `CompileException` - A copy-paste error in `JsonQueryCallGen` where the unsupported-type error message said "JSON_VALUE" instead of "JSON_QUERY" ## Brief change log - Add `convertJsonScalar` and `convertJsonArray` methods to `SqlJsonUtils` with full type conversion, string coercion, and ON ERROR handling - Add range-checked conversion methods (`toCheckedInt`, `toCheckedFloat`, `toCheckedDouble`, `parseStringAsBoolean`, etc.) to `SqlJsonUtils` - Add `JsonConversionException` (package-private) for conversion control flow - Add `isSupportedJsonReturningType` as a shared type allowlist - Simplify `JsonValueCallGen` codegen to a single `convertJsonScalar` call for non-VARCHAR types - Simplify `JsonQueryCallGen` codegen to a single `convertJsonArray` call for typed arrays - Add `RAW_ARRAY` variant to `JsonQueryReturnType` for raw Jayway object arrays - Update validation in `SqlJsonQueryFunctionWrapper` to use the shared type allowlist - Add unit tests (`SqlJsonUtilsConversionTest`) for all conversion methods: 60 tests covering overflow, string coercion, error behaviors, boundary values - Add integration tests for all new type combinations, overflow, ON ERROR, string coercion, boundary values: 933 total cases ## Verifying this change This change added tests and can be verified as follows: **Unit tests (SqlJsonUtilsConversionTest, 60 tests):** - Parameterized valid conversions across all numeric types (TINYINT through DOUBLE) - Parameterized overflow cases including negative overflow - FLOAT/DOUBLE overflow (infinity/NaN rejection) - BigInteger and BigDecimal overflow for BIGINT - BigDecimal fractional truncation for BIGINT - DECIMAL precision overflow and scientific notation - String coercion: numeric strings, overflow, non-numeric strings, empty strings, NaN/Infinity strings - Boolean string coercion: true/false/t/f/yes/no/1/0 and invalid strings - Error behavior: NULL/DEFAULT/ERROR ON ERROR for scalar and array conversion - Array atomicity: partial element failure fails the entire array **Integration tests (JsonFunctionsITCase, 933 tests):** - JSON_VALUE RETURNING for all new types with various JSON values - JSON_QUERY RETURNING ARRAY\<T\> for all element types including DECIMAL - Overflow with NULL/DEFAULT/ERROR ON ERROR - ARRAY\<BIGINT\> near Long.MAX_VALUE/MIN_VALUE boundaries and overflow - Quoted number strings parsed as integers - Nested arrays routed to ON ERROR - Negative values within TINYINT range ## Does this pull request potentially affect one of the following parts: - Dependencies (does it add or upgrade a dependency): no - The public API, i.e., is any changed class annotated with `@Public(Evolving)`: no - The serializers: no - The runtime per-record code paths (performance sensitive): no - Anything that affects deployment or recovery: JobManager (and its components), Checkpointing, Kubernetes/Yarn, ZooKeeper: no - The S3 file system connector: no ## Documentation - Does this pull request introduce a new feature? yes - If yes, how is the feature documented? not documented (widens accepted types in existing SQL functions; could be added to the JSON functions docs page as a follow-up) --- ##### Was generative AI tooling used to co-author this PR? - [X] Yes (please specify the tool below) Generated-by: Claude Code -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
