jOOQ generates invalid SQL when upgrading from 3.19.6 to 3.19.7

[alf]

Expected behavior

Tests should pass.

Actual behavior

24 errors during test execution: ORA-00932: inconsistent datatypes: expected CHAR got BINARY

Steps to reproduce the problem

We have the following condition in one of our tests:

coalesce(jsonbValue((Field)DB_HENDELSE.HENDELSESDATA,"$.OLD.STATUS_REG_OK"),inline("N")).eq(inline("N"))

DB_HENDELSE.HENDELSESDATA is generated by jOOQ as:

    public final TableField<DbHendelseRecord, JSONB> HENDELSESDATA = createField(DSL.name("HENDELSESDATA"), SQLDataType.JSONB, this, "");

Using 3.19.6 jOOQ generates the following SQL for the condition:

coalesce(
        json_value("KJERNEAPI"."DB_HENDELSE"."HENDELSESDATA", '$.OLD.STATUS_REG_OK'),
        'N'
      ) = 'N'

Using 3.19.7 we get:

coalesce(
        json_value("KJERNEAPI"."DB_HENDELSE"."HENDELSESDATA", '$.OLD.STATUS_REG_OK'),
        hextoraw('224E22')
      ) = 'N'

jOOQ Version

3.19.7

Database product and version

Oracle 18 XE

Java Version

Java 11

JDBC / R2DBC driver name and version (include name if unofficial driver)

No response

[lukaseder]

Thanks a lot for your report. I'll investigate this regression soon. I suspect this is due to:

• Fix other cases where org.jooq.impl.Val uses "inferred data type," but cannot actually lazily infer the type #16452

The data types passed to COALESCE() are expected to match. They've been made to match when you pass bind values directly, wrapping them in DSL.val() implicitly, in the past. But in the past, explicit DSL.inline() calls occasionally didn't receive the associated data type (including converters, if applicable).

What's the reason why you're using the JSONB functions, rather than the JSON ones, in Oracle? jOOQ's implementation of JSONB functions in Oracle tries to apply RETURNING BLOB semantics, which would explain the binary expression.

[alf]

The column we store the data in is CLOB. No particular reason other than the 4k character limit of VARCHAR which we haven’t bumped to 32k as far as I’m aware.

I suspect we can get away with 4k as well. My experience is mostly with Postgres where JSONB is preferred. This might have leaked through here TBH.

[lukaseder]

The column we store the data in is CLOB. No particular reason other than the 4k character limit of VARCHAR which we haven’t bumped to 32k as far as I’m aware.

Note that jOOQ supports the standard SQL RETURNING CLOB clause on most JSON functions.

I suspect we can get away with 4k as well. My experience is mostly with Postgres where JSONB is preferred. This might have leaked through here TBH.

I see. I have wondered a few times how to best map PostgreSQL's JSONB to other dialects. I think Oracle's current mapping to BLOB may not be too useful as it isn't strictly the same thing. JSONB is mostly about storage representation of JSON data, whereas RETURNING BLOB is about serialisation of JSON data (to the client).

This can't be changed easily now, but I wonder what user expectations really are here. Yours is simply to enable larger JSON data, so that doesn't require BLOB really (which complicates a few other things).

Also, Oracle's native JSON data type really corresponds more to PG's JSONB, whereas VARCHAR2 or CLOB encodings correspond to PG JSON:

• https://docs.oracle.com/en/database/oracle/oracle-database/21/adjsn/json-in-oracle-database.html

In any case, getting back to your original issue, I think you shouldn't use JSONB in Oracle, but if you can't change this easily, then perhaps do explicit casts in cases like the above, where COALESCE() and similar is used.