[CALCITE-6208] JSON_VALUE 'RETURNING' syntax support for arrays with nullable elements

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Fix Version/s: 1.37.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: Clint Wylie
• Created: 17/Jan/24 08:28
• Updated: 06/May/24 22:19
• Resolved: 30/Jan/24 18:31

Description

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

 

select c
from table, unnest(json_value(nested, '$.c' returning bigint array)) as u(c)
where c is not null

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.

 

Issue Links

• links to: GitHub Pull Request #3633