Db2 cannot handle scalar subqueries in JSON_OBJECT in the presence of a MULTISET ad-hoc converter

[lukaseder]

This doesn't work in Db2:

select 
  json_object(
    key 'v0' value 4,
    key 'v1' value (
      select 'a'
      from SYSIBM.DUAL
    )
  )
from SYSIBM.DUAL;

The above produces:

SQL Error [42601]: An unexpected token ")" was found following "SYSIBM.DUAL ) ". Expected tokens may include: "<interval_qualifier>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.28.11

Casting the value to a VARCHAR "fixes" it

select 
  json_object(
    key 'v0' value 4,
    key 'v1' value cast((
      select 'a'
      from SYSIBM.DUAL
    ) as varchar(32672))
  )
from SYSIBM.DUAL;

[lukaseder]

I seem to have already fixed this, but the fix didn't work in this case:
https://stackoverflow.com/a/64350132/521799

[lukaseder]

The fix doesn't work when there's an ad-hoc converter!

[lukaseder]

The problem is that we do an field instanceof ScalarSubquery check, rather than calling Tools.uncoerce(field), first. It's probably always wrong to just check for something being an instanceof ScalarSubquery

[lukaseder]

There we go.

[lukaseder]

Fixed in jOOQ 3.16.0 and 3.15.1 (#12190)