[
https://issues.apache.org/jira/browse/CALCITE-7801?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-7801:
------------------------------------
Labels: pull-request-available (was: )
> JSON_VALUE(..., RETURNING DOUBLE) throws ClassCastException when the JSON
> number is an integer
> ----------------------------------------------------------------------------------------------
>
> Key: CALCITE-7801
> URL: https://issues.apache.org/jira/browse/CALCITE-7801
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.41.0, 1.42.0
> Reporter: Jerome Isaac Haltom
> Priority: Minor
> Labels: pull-request-available
>
> {{JSON_VALUE}} with a {{RETURNING}} type throws unless the value's Java class
> already is that type.
> {code:sql}
> SELECT JSON_VALUE('{"c":0}', '$.c' RETURNING DOUBLE)
> {code}
> {noformat}
> java.lang.ClassCastException: java.lang.Integer cannot be cast to
> java.lang.Double
> {noformat}
> The same query works if the number is written with a decimal point:
> {code:sql}
> SELECT JSON_VALUE('{"c":-83.489548}', '$.c' RETURNING DOUBLE) -- -83.489548
> {code}
> So it depends on the value, not on the query. Over a table it fails on some
> rows and not others:
> {code:sql}
> SELECT JSON_VALUE(v, '$.c' RETURNING DOUBLE) AS c
> FROM (VALUES ('{"c":-83.489548}'), ('{"c":0}')) AS t(v)
> {code}
> {{RETURNING INTEGER}} fails the same way on a number that has a decimal point:
> {code:sql}
> SELECT JSON_VALUE('{"c":0.5}', '$.c' RETURNING INTEGER)
> -- java.lang.ClassCastException: java.lang.Double cannot be cast to
> java.lang.Integer
> {code}
> {{ON ERROR}} does not catch it. {{NULL ON ERROR}} throws too:
> {code:sql}
> SELECT JSON_VALUE('{"c":0}', '$.c' RETURNING DOUBLE NULL ON ERROR)
> {code}
> A {{CAST}} works:
> {code:sql}
> SELECT CAST(JSON_VALUE('{"c":0}', '$.c') AS DOUBLE) -- 0.0
> {code}
> Expected: {{RETURNING DOUBLE}} should convert the value the way a {{CAST}}
> does, and a value it cannot convert should be an error the {{ON ERROR}}
> clause handles.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
