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)
