[jira] [Updated] (FLINK-40580) Cast from VARIANT to a character string should render an object or array as JSON

["Ramin Gharib (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/FLINK-40580?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Ramin Gharib updated FLINK-40580:
---------------------------------
    Description: 
Casting a VARIANT to CHAR/VARCHAR/STRING renders a stored scalar the way a regular SQL cast of that kind would (a boolean becomes TRUE, a timestamp uses the SQL format, a binary value is read as UTF-8). A VARIANT that holds an object or an array has no scalar form, so today the cast fails:
{code:java}
Cannot cast a VARIANT OBJECT value to a character string. Use the JSON_STRING function to obtain its JSON representation. {code}
Redirecting users to JSON_STRING for the container case is surprising: the value already has a well-defined textual form, its JSON representation, which both JSON_STRING and the result-print path already produce.

 

Proposal: make CAST(v AS STRING) render an object or array as its JSON representation (via Variant#toJson()) instead of failing. Scalar rendering is unchanged, so a top-level scalar string still casts unquoted while a string nested inside a rendered container stays quoted, matching JSON_STRING and the print path.
{code:java}
CAST(PARSE_JSON('["a","b"]') AS STRING)  -- ["a","b"]   (was: runtime error)
CAST(PARSE_JSON('\{"a":1}')   AS STRING)  -- \{"a":1}     (was: runtime error)
CAST(PARSE_JSON('"foo"')     AS STRING)  -- foo         (unchanged)
{code}
A bounded CHAR/VARCHAR target trims the rendered JSON like any other over-length value. TRY_CAST returns the same JSON rather than NULL for a container. A VARIANT storing a JSON null still casts to SQL NULL.

Scope: only OBJECT and ARRAY change here. Scalar kinds whose string rendering is still unsupported (TIME and nanosecond timestamps, tracked in FLINK-40492) keep failing.

  was:
Casting a VARIANT to CHAR/VARCHAR/STRING renders a stored scalar the way a regular SQL cast of that kind would (a boolean becomes TRUE, a timestamp uses the SQL format, a binary value is read as UTF-8). A VARIANT that holds an object or an array has no scalar form, so today the cast fails:

{code}Cannot cast a VARIANT OBJECT value to a character string. Use the JSON_STRING function to obtain its JSON representation.\{code}

Redirecting users to JSON_STRING for the container case is surprising: the value already has a well-defined textual form, its JSON representation, which both JSON_STRING and the result-print path already produce.

Proposal: make CAST(v AS STRING) render an object or array as its JSON representation (via Variant#toJson()) instead of failing. Scalar rendering is unchanged, so a top-level scalar string still casts unquoted while a string nested inside a rendered container stays quoted, matching JSON_STRING and the print path.
{code:sql}
 CAST(PARSE_JSON('["a","b"]') AS STRING)  -- ["a","b"]   (was: runtime error)
 CAST(PARSE_JSON('\{"a":1}')   AS STRING)  -- \{"a":1}     (was: runtime error)
 CAST(PARSE_JSON('"foo"')     AS STRING)  -- foo         (unchanged)
{code}
A bounded CHAR(n)/VARCHAR(n) target trims the rendered JSON like any other over-length value. TRY_CAST returns the same JSON rather than NULL for a container. A VARIANT storing a JSON null still casts to SQL NULL.

Scope: only OBJECT and ARRAY change here. Scalar kinds whose string rendering is still unsupported (TIME and nanosecond timestamps, tracked in FLINK-40492) keep failing.


> Cast from VARIANT to a character string should render an object or array as JSON
> --------------------------------------------------------------------------------
>
>                 Key: FLINK-40580
>                 URL: https://issues.apache.org/jira/browse/FLINK-40580
>             Project: Flink
>          Issue Type: Improvement
>            Reporter: Ramin Gharib
>            Assignee: Ramin Gharib
>            Priority: Major
>
> Casting a VARIANT to CHAR/VARCHAR/STRING renders a stored scalar the way a regular SQL cast of that kind would (a boolean becomes TRUE, a timestamp uses the SQL format, a binary value is read as UTF-8). A VARIANT that holds an object or an array has no scalar form, so today the cast fails:
> {code:java}
> Cannot cast a VARIANT OBJECT value to a character string. Use the JSON_STRING function to obtain its JSON representation. {code}
> Redirecting users to JSON_STRING for the container case is surprising: the value already has a well-defined textual form, its JSON representation, which both JSON_STRING and the result-print path already produce.
>  
> Proposal: make CAST(v AS STRING) render an object or array as its JSON representation (via Variant#toJson()) instead of failing. Scalar rendering is unchanged, so a top-level scalar string still casts unquoted while a string nested inside a rendered container stays quoted, matching JSON_STRING and the print path.
> {code:java}
> CAST(PARSE_JSON('["a","b"]') AS STRING)  -- ["a","b"]   (was: runtime error)
> CAST(PARSE_JSON('\{"a":1}')   AS STRING)  -- \{"a":1}     (was: runtime error)
> CAST(PARSE_JSON('"foo"')     AS STRING)  -- foo         (unchanged)
> {code}
> A bounded CHAR/VARCHAR target trims the rendered JSON like any other over-length value. TRY_CAST returns the same JSON rather than NULL for a container. A VARIANT storing a JSON null still casts to SQL NULL.
> Scope: only OBJECT and ARRAY change here. Scalar kinds whose string rendering is still unsupported (TIME and nanosecond timestamps, tracked in FLINK-40492) keep failing.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)