Oracle NULL ON NULL子句无效

Question

我试图让Oracle在SQL null数据上生成JSON NULL值，如下所示：

select 
  json_object(key 'a' value 1, key 'b' value null null on null)   c1, 
  json_object(key 'a' value 1, key 'b' value null absent on null) c2
from dual;

也可以：

select 
  json_object(key 'a' value a, key 'b' value b null on null)   c1, 
  json_object(key 'a' value a, key 'b' value b absent on null) c2
from (
  select 1 a, null b
  from dual
) t;

不幸的是，这两个查询的结果是：

|C1        |C2        |
|----------|----------|
|{"a":1}   |{"a":1}   |

相反，我本以为会这样：

|C1                 |C2        |
|-------------------|----------|
|{"a":1,"b":null}   |{"a":1}   |

我遗漏了什么？我正在使用Oracle XE18c

Answer 1

你发现了一个窃听器。我使用不同的空处理程序提交了Bug 31013529 -两个JSON_OBJECT，返回错误的结果。

我们将尽快修复它，并将其包含在包补丁中。如果你需要一次性补片，请告诉我们。

谢谢b

Answer 2

对我来说好像是个虫子：

select 
  json_object(key 'a' value 1, key 'b' value null null on null)   c1
  ,(SELECT json_object(key 'a' value 1, key 'b' value null absent on null) FROM dual) c2
from dual

输出：

+-------------------+---------+
|        C1         |   C2    |
+-------------------+---------+
| {"a":1,"b":null}  | {"a":1} |
+-------------------+---------+

db<>fiddle demo

当用(SELECT exp FROM dual)包装更改结果集(https://stackoverflow.com/a/52561721/5070879)时，就会出现这些奇怪的情况之一。

我想，是一些优化机制(“公共子表达式消除”)导致对exp进行一次第一次/最后一次发生的评估。如果您更改了键，它就会正常工作：

select 
  json_object(key 'a' value 1, key 'b' value null null on null)   c1, 
  json_object(key 'a' value 1, key 'c' value null absent on null) c2
from dual;

db<>fiddle demo

Answer 3

Lukasz的答案提供了一个可行的解决方法，使用相关的子查询。

为了记录在案，其他函数也会受到影响，例如JSON_ARRAYAGG

select
  json_arrayagg(a null on null) c1,
  json_arrayagg(a absent on null) c2
from (
  select 1 a from dual union all select null a from dual
) t;

产量：

|C1        |C2        |
|----------|----------|
|[1]       |[1]       |

而这个解决办法..。

select 
  cast(json_arrayagg((select a from dual) absent on null) as varchar2(10)) c2,
  cast(json_arrayagg((select a from dual) null on null) as varchar2(10)) c1
from (
  select 1 a from dual union all select null a from dual
) t;

产生正确的结果：

|C2        |C1        |
|----------|----------|
|[1]       |[1,null]  |