Clint Wylie created CALCITE-6208: ------------------------------------ Summary: 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
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)