[ https://issues.apache.org/jira/browse/CALCITE-6208?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Sergey Nuyanzin closed CALCITE-6208. ------------------------------------ Resolved in release 1.37.0 (2024-05-06) > 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 > Fix For: 1.37.0 > > > 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)