[CALCITE-6478] JSON functions should return NULL when input is NULL

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.38.0
• Labels: pull-request-available
• Assignee: xiong duan
• Reporter: xiong duan
• Created: 17/Jul/24 02:26
• Updated: 17/Oct/24 22:41
• Resolved: 17/Oct/24 22:41

Description

Here is the test in H2:

SELECT js,
       js IS JSON        "json?",
       js IS JSON value  "json?",
       js IS JSON SCALAR "scalar?",
       js IS JSON OBJECT "object?",
       js IS JSON ARRAY  "array?"
FROM (VALUES ('123'),
             ('"abc"'),
             ('{"a": "b"}'),
             ('[1,2]'),
             ('abc'), NULL) foo(js);

+----------+-----+-----+-------+-------+------+
|JS        |json?|json?|scalar?|object?|array?|
+----------+-----+-----+-------+-------+------+
|123       |true |true |true   |false  |false |
|"abc"     |true |true |true   |false  |false |
|{"a": "b"}|true |true |false  |true   |false |
|[1,2]     |true |true |false  |false  |true  |
|abc       |false|false|false  |false  |false |
|null      |null |null |null   |null   |null  |
+----------+-----+-----+-------+-------+------+

Same SQL in Calcite:

+------------+-------+-------+---------+---------+--------+
| JS         | json? | json? | scalar? | object? | array? |
+------------+-------+-------+---------+---------+--------+
| "abc"      | true  | true  | true    | false   | false  |
| 123        | true  | true  | true    | false   | false  |
| [1,2]      | true  | true  | false   | false   | true   |
| abc        | false | false | false   | false   | false  |
| {"a": "b"} | true  | true  | false   | true    | false  |
|            | false | false | false   | false   | false  |
+------------+-------+-------+---------+---------+--------+

Issue Links

• is related to: CALCITE-2266 Implement SQL 2016 JSON functions
• links to: GitHub Pull Request #3892