Jerome Isaac Haltom created CALCITE-7801:
--------------------------------------------
Summary: 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.42.0, 1.41.0
Reporter: Jerome Isaac Haltom
{{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)
