JSON query returns NULLvalue after upgrade to 19.10 (Doc ID 2771491.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

On : 19.10.0.0.0 version, XML Database

ACTUAL BEHAVIOR
---------------
SQL JSON problem after installing on 19.10


Customer patched databases on Monday on version 19.10.0.0.210119 (RU Jan 2021).

Simply test case:

Oracle EE 19.6.0.0.200114(RU Jan 2020):

SELECT JSON_QUERY(json_object_table.json_object, '$.size()' WITH CONDITIONAL WRAPPER)
  FROM (SELECT JSON_OBJECT('ID' VALUE 1, 'NAME' VALUE 'NAME' ABSENT ON NULL RETURNING CLOB) JSON_OBJECT
  FROM dual) json_object_table

Result: 2

SQL> SELECT JSON_QUERY(json_object_table.json_object, '$.size()' WITH CONDITIONAL WRAPPER)
  FROM (SELECT JSON_OBJECT('ID' VALUE 1, 'NAME' VALUE 'NAME' ABSENT ON NULL RETURNING CLOB) JSON_OBJECT
  FROM dual) json_object_table
  2 3 4 /

JSON_QUERY(JSON_OBJECT_TABLE.JSON_OBJECT,'$.SIZE()'WITHCONDITIONALWRAPPER)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2


Oracle EE 19.10.0.0.210119 (RU Jan 2021)

SELECT JSON_QUERY(json_object_table.json_object, '$.size()' WITH CONDITIONAL WRAPPER)
  FROM (SELECT JSON_OBJECT('ID' VALUE 1, 'NAME' VALUE 'NAME' ABSENT ON NULL RETURNING CLOB) JSON_OBJECT
  FROM dual) json_object_table

Result: Null

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.