eclipse-vertx / eclipse-vertx/vertx-sql-client

Allow specifying types of bind parameters when preparing query

Open
#363 0 comments 1 reaction 1 assignee Claimed by @tsegismont View on GitHub
Dominant language
Java
Stars
912
Forks
212
Avg merge
17h 30m
Merged PRs (30d)
3

Description

(We're still using `reactive-pg-client 0.11.4` but this appears to apply to this library as well and I assume no new development will take place for `reactive-pg-client` anyway so I'm opening this here.)

I see this code was commented out: https://github.com/eclipse-vertx/vertx-sql-client/blob/3.8/vertx-pg-client/src/main/java/io/vertx/pgclient/impl/codec/PgEncoder.java#L285-L294

We've run into a need for this as binding parameters to a Postgres statement with binds inside a `VALUES()` expression is error-prone because Postgres cannot infer the types of the bind parameters and falls back to `TEXT`. This can be fixed by writing explicit `cast()`s around the bind parameters but we are generating SQL with JOOQ and it's difficult to get JOOQ to emit explicit casts for values which it knows don't need to be cast:

```java
// create a synthetic table with IDs and their original indices in the list, where externalIds is a `List`
final var rows = new ArrayList>(externalIds.size());

for (int i = 0; i < externalIds.size(); i++) {
rows.add(DSL.row(
DSL.value(i),
// explicit type needed because many values can be `null`
DSL.value(externalIds.get(i), UUID.class)));
}

@SuppressWarnings("unchecked")
// FIXME: https://github.com/jOOQ/jOOQ/issues/7098
final var extIdsTable = DSL.values(rows.toArray(new Row2[0]));

... //, a `Query` is built as `query`

final var dsl = DSL.using(SQLDialect.POSTGRES_10);

// fixed in JOOQ 3.12, not released yet
// https://github.com/jOOQ/jOOQ/issues/7797
final var sql = dsl.renderNamedParams(query).replaceAll("(? { ... });
```
The generated SQL looks something like this (being used in a bigger query):

```sql
values ($1, cast($2 as uuid)), ...) as "v"("c1", "c2")
```

Preparing the query with binds like so fails with `Values [0, null, ...] cannot be coerced to [String, UUID, ...]`; Postgres was unable to infer the types of the bind parameters and so fell back to `TEXT`.

To get JOOQ to emit explicit casts for the integer types it has to be told to cast to a type which needs explicit conversion:
```java
rows.add(DSL.row(
// neither `Integer.class` nor `Long.class` alone emits a cast, I already tried it
// this probably has an additional runtime cost but this isn't a high-volume query so we're not worried about it
DSL.value(i).cast(BigInteger.class).cast(Integer.class),
// explicit type needed because many values can be `null`
DSL.value(externalIds.get(i), UUID.class)));
```

It would be nice if `PgConnection.preparedQuery()` had an overload which took a list of class types or something which added explicit typing for bind parameters to the `Parse` command.

This isn't really an issue with JOOQ because if we were using JOOQ to query as well this probably wouldn't be an issue as JOOQ would tell Postgres the types of the bind parameters when preparing the query. However, if it turns out this is fixed in JOOQ 3.12 I'll close this myself.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.