jOOQ 3.15 postgres complaining about jsonb types in generated sql

user_14953859 0
Viewed 161

The following runtime error popped up after upgrading my jOOQ version from 3.14 -> 3.15.3:

ERROR: column "old_values" is of type jsonb but expression is of type character varying

The code looks like:

final String jsonChanges = gson.toJson(map);
final InsertOnDuplicateStep<TicketLogRecord> insertLogs =
                insertInto(LOG,
                           LOG.ID,
                           LOG.OLD_VERSION,
                           LOG.OLD_VALUES,
                           LOG.NEW_VALUES)
                .select(
                        select(field("to_update.id", UUID.class),
                                field("to_update.version", Integer.class),
                                val("{}", JsonElement.class),
                                val(jsonChanges, JsonElement.class),
)
final var countUpdated =
                with("to_update", "id", "version", "state_id").as(ids)
                                                                      .with("update_states").as(updateWhere.returning())
                                                                      .with("insert_logs").as(insertLogs.returning())
                                                                      .select(count().as("updated_count"))
                                                                      .from("to_update");
            return using(configuration)
                .fetchOne(countUpdated)
                .getValue("updated_count", Integer.class);

It was resolved by casting cast(val("{}", JsonElement.class), LOG.OLD_VALUES) and likewise with NEW_VALUES on the line below it. But I'm interested in why this issue with SQL generation arose between these two minor versions?

Thanks

jooq
1 Answers

Why is this happening / "regressing"?

This usage here:

val(jsonChanges, JsonElement.class)

Is making use of an undocumented "feature", that happened to work by accident in some cases (not always). It seems to have worked for you in your case. However, only class literals supported by SQLDataType can be passed to methods like DSL.val(Object, Class), so this is valid:

field("to_update.version", Integer.class)

But passing JsonElement isn't. jOOQ can't possibly know how you intend to bind a JsonElement value to the JDBC driver. It might have worked because somewhere, you've registered a Binding<Object, JsonElement>, and because that binding is (historically) added to an internal static DataType registry, looking up a binding for JsonElement.class happened to work sometimes. Reasons why it might not work:

  • The class registering the Binding<Object, JsonElement> has not been loaded yet
  • An internal refactoring in jOOQ changes some of these undocumented lookup behaviours

This definitely deserves some attention in jOOQ:

  • #12470 Better Javadoc
  • #12471 Logging a warning when users try to lookup unsupported class literals

How to fix it?

In your particular case, you already have a reference to a DataType<JsonElement> in LOG.OLD_VALUES. Your cast can work, but even better, just remove the JsonElement.class literal entirely and write this instead:

// Assuming your JSON library is Gson
val(new JsonObject(), LOG.OLD_VALUES)

This will take the DataType from the OLD_VALUES column (including its binding), and use that to bind the JsonObject value.

user_5217990
Related