You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by "Clint Wylie (Jira)" <ji...@apache.org> on 2024/01/17 08:29:00 UTC
[jira] [Created] (CALCITE-6208) JSON_VALUE 'RETURNING' syntax support for arrays with nullable elements
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)