Skip to Main Content

Oracle Database Free

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Another bug not fixed yet: optimizer operation resulting in invalid chaining of JSON_TABLE

mathguyMar 2 2024 — edited Mar 2 2024

Piling on here - seeing that there is meaningful interest in, perhaps, fixing not just new bugs (new as of version 23) but also old ones.

Ever since the introduction of JSON support in Oracle 12, and in particular JSON_TABLE, the chaining of JSON_TABLE calls in the same FROM clause has not been supported. (At some point perhaps it should be; there are legitimate uses, where the NESTED clause doesn't work - for example when a value in the “outer” JSON is an escaped JSON itself, and one needs to get to data in the embedded JSON. See a recent thread in the SQL & PL/SQL forum: https://forums.oracle.com/ords/apexds/post/parse-json-with-json-table-no-results-4525 )

The obvious workaround is to use JSON_TABLE in a subquery, and then in an outer query to use JSON_TABLE again. As illustrated in the thread in the SQL & PL/SQL forum. But here we run into a bug.

The optimizer tries to merge the subquery into the main query. Fine - the optimizer can do whatever it pleases. But then the parser throws an error when it finds chained calls to JSON_TABLE. This is the bug I am asking about.

There may be some (unjustified in my opinion) debate as to whether this should be considered a bug. It should be: if the optimizer attempts an operation (such as merging a subquery) that results in an unsupported feature, the optimizer should backtrack. The query is perfectly valid; it only becomes invalid (unsupported) due to an operation performed by the optimizer, not by the programmer.

This may not be easy to fix; I don't know exactly how the optimizer and the parser work together, but I imagine the optimizer merges and then only the parser catches the unsupported feature, and logical flow doesn't then go back from the parser to the optimizer. If so, the optimizer needs some checks of its own when it considers merging, to make sure it doesn't cause errors down the line. Perhaps not something that's easy to do, but I am sure there are many similar checks for other things, otherwise we would be seeing many more bugs like this. (Indeed we do see some, but not that many.) Anyway, this is something I know nothing about.

This can be easily worked around - use the NO_MERGE hint; but that's a crutch, not a proper solution.

Comments
Post Details
Added on Mar 2 2024
3 comments
166 views