This post is loosely related to another one I started: https://forums.oracle.com/ords/apexds/post/another-bug-not-fixed-yet-optimizer-operation-resulting-in-9815
However, the issue in that thread is somewhat different from what I am discussing here.
Oracle 23c introduced the NESTED clause as shorthand for lateral outer join to JSON_TABLE. The documentation, here:
https://docs.oracle.com/en/database/oracle/oracle-database/23/adjsn/function-JSON_TABLE.html#GUID-D870AAFF-58B0-4162-AC11-4DDC74B608A5
in section 21.1, SQL NESTED Clause Instead of JSON_TABLE, says:
The NESTED clause is a shortcut for using json_table with an ANSI left outer join. That is, these two queries are equivalent:
SELECT ...
FROM mytable NESTED jcol COLUMNS (...);
SELECT ...
FROM mytable t1 LEFT OUTER JOIN
json_table(t1.jcol COLUMNS (...)
ON 1=1;
The statement that the two SELECT queries are equivalent is false, as I will demonstrate below. I wonder: is this an error in the documentation, by someone who didn't verify that the statements are equivalent, or is it an error in the implementation of NESTED, where the two queries were in fact intended to be equivalent, but they simply aren't?
In the “loosely related” thread I linked to at the top of this post, I discuss the issue of chained calls to JSON_TABLE. They aren't supported in any form in Oracle versions prior to 23, but they are allowed for OUTER APPLY only (no other syntax) in 23.2 at least.
It turns out that NESTED clauses can't be chained either in version 23 (at least in 23.2); trying to do so results in the same “unsupported chaining” error. However, chaining LEFT OUTER JOIN JSON_TABLE … is supported. I am not showing that test here, since it's out of scope for this thread, but it is a real-life example where the distinction between NESTED and LEFT OUTER JOIN JSON_TABLE… is clear and relevant.
OK - so what's the difference? The NESTED clause is rewritten as a lateral join to JSON_TABLE; see the “Final query after transformations” from the 10053 trace, below. So, as long as chained JSON_TABLE is unsupported, so will be the chaining of NESTED clauses. On the other hand, the LEFT OUTER JOIN JSON_TABLE … is rewritten as a lateral outer join to an un-merged subquery selecting from JSON_TABLE. This is exactly the essence of the bug I discussed in my other thread, where the optimizer's unilateral choice to merge a view into the main query results in unsupported chaining of JSON_TABLE calls.
So - here is the short test I built. Table and data creation:
create table nested_test (id number generated as identity primary key, json_col json);
insert into nested_test (json_col) values (json('{"name":"foo","price":"2500"}'));
commit;
The two queries (which should be equivalent according to the documentation):
select id, name, price
from nested_test
NESTED
json_col columns (name, price)
;
select id, name, price
from nested_test
LEFT OUTER JOIN json_table(json_col, '$' columns (name, price))
on 1 = 1
;
And the “Final query after transformations” from the 10053 trace for each, showing that one merges the JSON_TABLE view into the main query while the other doesn't. Copied and pasted from the trace file, I only altered white space for readability:
SELECT "NESTED_TEST"."ID" "ID","P"."NAME" "NAME","P"."PRICE" "PRICE"
FROM "MATHGUY"."NESTED_TEST" "NESTED_TEST",
JSON_TABLE( "NESTED_TEST"."JSON_COL" , '$'
COLUMNS( "NAME" VARCHAR2(4000) PATH '$.name' NULL ON ERROR ,
"PRICE" VARCHAR2(4000) PATH '$.price' NULL ON ERROR )
) "P" WHERE 1=1
SELECT "NESTED_TEST"."ID" "ID","VW_LAT_93ADBB55"."ITEM_1" "NAME",
"VW_LAT_93ADBB55"."ITEM_2" "PRICE"
FROM "MATHGUY"."NESTED_TEST" "NESTED_TEST",
LATERAL( (SELECT "P"."NAME" "ITEM_1","P"."PRICE" "ITEM_2"
FROM JSON_TABLE( "NESTED_TEST"."JSON_COL" , '$'
COLUMNS( "NAME" VARCHAR2(4000) PATH '$.name' NULL ON ERROR ,
"PRICE" VARCHAR2(4000) PATH '$.price' NULL ON ERROR )
) "P"
WHERE 1 = 1)
)(+) "VW_LAT_93ADBB55"