0

Met strange behavior using query with json_table in cte at oracle - connection reset from server, without details.

Illustrating artificial query is:

with data as (
    select 5 as some_value
    from dual
),
ps as (
    select
       dt.some_value,
       json_cnt
    from data dt
    cross join json_table(
            to_clob('{"positions":[{"name":"aaa","cnt":5}]}'),
            '$'
            columns (
                nested path '$.positions[*]'
                    columns (
                        json_cnt varchar2 path '$.cnt'
                        )
                )
        )
)
-- select * -- its ok 
-- select count(json_cnt) -- also ok
select count(*) -- fails with connection reset
from ps

It behaves such way:

  • count(*) - leads to "No more data read from socket" and 17410 error
  • * - leads to correct fields representation
  • count(json_cnt) - leads to correct count (1)

In real scenario query is more complicated and uses data from real tables, but for illustration purpose cut it to be reproducible(at least in my environment) in simplified format.

More I can say, that adding some kind of additional use of intermediate cte, "corrects" expected behavior. following query works without a problem :

with data as (
    select 5 as some_value
    from dual
),
ps as (
    select
       dt.some_value,
       json_cnt
    from data dt
    cross join json_table(
            to_clob('{"positions":[{"name":"aaa","cnt":5}]}'),
            '$'
            columns (
                nested path '$.positions[*]'
                    columns (
                        json_cnt varchar2 path '$.cnt'
                        )
                )
        )
),
int_cte as (
    select 5 as json_cnt from dual
)
select count(*)
from ps
left join int_cte on ps.json_cnt = int_cte.json_cnt;

I can guess, that there is some strange combo of cross join leading to this, but don't understand the reason. And it took a time to figure out the source of a problem in real query.

Can somebody give a hint what is wrong with first query and what leads to error without explicit check but connection reset.

Version: 12.2.0.1.0

3
  • Heve you checked the server for internal server errors corrresponding to your selection? Mar 13 at 13:56
  • Unfortunately, I have no direct access to any kind of oracle logs no conntact with dba team. I thought mb this situation is some general behavior and well known. I'll try to communicate dba team, but it will take some time, hope they'll answer. Mar 14 at 11:15
  • Until then, you may be facing a bug. Stick to the workaround, since the Oracle solution will probably be an upgrade of the database. Mar 14 at 12:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.