[
https://issues.apache.org/jira/browse/CALCITE-6208?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17807642#comment-17807642
]
Clint Wylie commented on CALCITE-6208:
--------------------------------------
opened [https://github.com/apache/calcite/pull/3633] with potential fix
> JSON_VALUE 'RETURNING' syntax support for arrays with nullable elements
> -----------------------------------------------------------------------
>
> Key: CALCITE-6208
> URL: https://issues.apache.org/jira/browse/CALCITE-6208
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Clint Wylie
> Priority: Minor
> Labels: pull-request-available
>
> The JSON_VALUE function RETURNING syntax is not currently super array
> friendly because it creates an array type with non-null elements. This is
> primarily due to the interaction between SqlDataTypeSpec which creates a not
> null array with not null elements, and the
> SqlJsonValueFunction which forces nullable of the type, but that in turn does
> not force nullable for the element type, so it results in a nullable array
> but still with not null elements.
>
> In a lot of cases this isn't a huge problem, but coupled with unnest and
> filtering, it results in filters being incorrectly eliminated after being
> incorrectly classified as 'all true' or 'all false'.
> For example, given a query such as
>
> {code:java}
> select c
> from table, unnest(json_value(nested, '$.c' returning bigint array)) as u(c)
> where c is not null{code}
> c is inferred to be 'BIGINT NOT NULL', which results in 'c is not null' being
> dropped.
> I believe the solution to this is to modify the return type inference to
> include special handling for when the SqlDataTypeSpec is an array type to
> force both the array and its elements to be nullable.
> I tested this out and it appears to work as expected.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
