Reply.fill() - insufficient data when fetching JSON_ARRAY() data from Db2 Luw table

Question

I'm using Db2 LUW v11.5.5.1 as available from docker hub with this schema:

create table t (i int, j int);
insert into t values (1, 2);

When I run this statement from DBeaver:

select json_array(i, j)
from t;

Then I'm getting this error:

SQL Error [08001]: [jcc][t4][2030][11211][4.29.24] A communication error occurred during operations on the connection's underlying socket, socket input stream, or socket output stream. Error location: Reply.fill() - insufficient data (-1). Message: Insufficient data. ERRORCODE=-4499, SQLSTATE=08001

This works:

select json_array(1, 2)
from t;

Is this a known bug on the server / in the JDBC driver (I've tried all driver versions between 11.5.0.0, 11.5.4.0, 11.5.5.0, 11.5.6.0 from maven central)? Are there workarounds?

Answer 1

Workaround for single value arrays

A workaround that works for single value arrays is this:

select json_array((select i from sysibm.dual))
from t;

Unfortunately, that workaround doesn't work for more than one such value. Both of these fail with the same error (as documented, only a single subquery is allowed):

select json_array((select i from sysibm.dual), j)
from t;

select json_array((select i from sysibm.dual), (select j from sysibm.dual))
from t;

SQL Error [42601]: An unexpected token "," was found following "i from sysibm.dual)". Expected tokens may include: "=".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.29.24

Workaround for multi value arrays without joins

But incredibly, this works!

select (select json_array(i, j) from sysibm.dual)
from t;

Workaround for queries with joins

But the above workaround fails again as soon as there's a join:

create table t (i int, j int);
create table u (i int, j int);
insert into t values (1, 2);
insert into u values (1, 2);

-- And then, this fails again:
select (select json_array(t.i, u.j) from sysibm.dual)
from t join u on t.i = u.i

But I shall not give up. It is possible to completely re-invent the seemingly trivial JSON_ARRAY() function using JSON_QUERY() and JSON_OBJECT().

Not saying you should, but you definitely could.

select json_query(
  json_object(key 'k' value t.i, key 'k' value u.j), 
  '$.*' with unconditional array wrapper
)
from t join u on t.i = u.i

Answer 2

I was getting the same error, and found out that JSON_ARRAY expects a single column to be returned when using SELECT statement inside of it.

If you need to build json array out of SELECT result, you need to ensure that it returns multiple rows with single column each. If desired values are in two columns of a single result row, you can use union all to get them in two separate result rows.

E.g instead of:

select json_array((select i from sysibm.dual), (select j from sysibm.dual))
from t;

You should try

select json_array(select i from sysibm.dual union all select j from sysibm.dual) 
from t;

This works also for joined tables, e.g.:

select json_array(select t.i from sysibm.dual union all select u.j from sysibm.dual) 
from t join u on t.i = u.i

or by creating a single column TABLE:

select json_array(select * from TABLE(VALUES(t.i),(u.j)))
from t join u on t.i = u.i

Answer 3

Possibly a defect in Db2-LUW server at v11.5.5.1 (i.e not related to jdbc driver).

Seems to be fixed in Db2-LUW v11.5.6.0.