wasabii opened a new pull request, #5276:
URL: https://github.com/apache/calcite/pull/5276
`JSON_VALUE` never converted the value it pulled out of the document to the
type in the `RETURNING` clause. Its return type is always nullable, so code
generation asks `EnumUtils.convert` for `Object` to a *boxed* primitive, and
that case falls through to a plain `(Double) x`. The cast only worked when the
JSON value already happened to be that class:
```sql
JSON_VALUE('{"c":0}', '$.c' RETURNING DOUBLE) -- Integer cannot be cast
to Double
JSON_VALUE('{"c":0.5}', '$.c' RETURNING INTEGER) -- Double cannot be cast
to Integer
```
`INTEGER`, `VARCHAR` and `DECIMAL` happened to work; everything else threw.
And because the exception escaped the runtime function, `ON ERROR` never saw
it. `JSON_QUERY` has the same problem with `RETURNING ... ARRAY`, where the
elements are never converted at all, so I fixed both.
The conversion has to happen inside the runtime function, since that is
where the `ON EMPTY`/`ON ERROR` logic lives. It can't simply call `CAST`: that
is generated code, chosen from the statically known source and target types,
and here the source is only known at run time — the scalar may arrive as an
`Integer`, `Double`, `BigInteger`, `String` or `Boolean`. None of that is
specific to JSON, so I put it in `SqlFunctions`, next to the `toInt(Object)`
family it delegates to:
```java
SqlFunctions.cast(value, typeName, precision, scale, roundingMode)
SqlFunctions.castArray(value, elementType, precision, scale, roundingMode)
```
These dispatch on the target type and call the same methods cast's generated
code would have: `Primitive.integerCast` for the exact numerics,
`Primitive.charToDecimalCast` and friends for `DECIMAL`,
`truncate`/`truncateOrPad` for character types, the `DateTimeUtils` parsers for
datetimes. The type, its precision and scale, and the type system's rounding
mode come down from the two implementors as constants. `castArray` converts a
nested array at every level, so `RETURNING DOUBLE ARRAY ARRAY` works where it
used to throw, and it checks the value against the declared type rather than
following whatever shape the value happens to have.
Precision and scale were being dropped entirely, and now aren't:
```sql
JSON_VALUE('{"c":100}', '$.c' RETURNING DECIMAL(5,2)) -- was 100,
now 100.00
JSON_VALUE('{"c":"abcdef"}', '$.c' RETURNING VARCHAR(3)) -- was abcdef,
now abc
JSON_VALUE('{"c":"2020-01-01 10:20:30.987"}', '$.c' RETURNING TIMESTAMP(0))
-- was .987, now truncated
```
Datetimes parse from the JSON string, including both `WITH LOCAL TIME ZONE`
types — no time zone needs supplying, because cast passes one only when
converting a `TIME` or `TIMESTAMP`, which is a local reading of a clock, and a
JSON datetime is always a string. A JSON number is not a datetime, so
converting one is an error; Oracle and MySQL agree, PostgreSQL doesn't.
`cast` raises for any target type it doesn't handle, so a conversion failure
can no longer escape `ON ERROR`. Cases that used to throw an uncatchable
`ClassCastException` now go through it: `RETURNING VARBINARY(n)`; `RETURNING
<type> ARRAY` on `JSON_VALUE`, which cannot match anything as Calcite
implements it, since `JSON_VALUE` returns a scalar; and a value whose shape
does not fit an array `RETURNING` type, such as a JSON object, or an array
nested more or less deeply than the type says.
`RETURNING DECIMAL` that yields NULL still doesn't work, here and on main
alike, because linq4j's constant-hoisting lifts the conversion out of its null
guard. That can be fixed separately.
--
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]
