Null elements of an array bind parameter are broken since 5.0 (numeric[] throws / hangs, jsonb[] stores json `null`)

[Meemaw]

Version

Reproduced on 5.0.12 and 5.1.6 (io.vertx:vertx-pg-client). Works correctly on 4.5.32.

Context

Passing an array bind parameter whose elements may be null (a plain nullable column filled through
UNNEST($1::int[], $2::numeric[])-style bulk inserts) is broken since 5.0:

• numeric[]: ParamExtractor.prepareNumeric does assert value instanceof Number; return value.toString();,
  so a null element throws AssertionError when assertions are enabled, or
  VertxException: Cannot invoke "Object.toString()" because "value" is null otherwise.
  With -ea the AssertionError escapes on the event loop as SEVERE: Unhandled exception and the
  query future is never completed, so the caller hangs until its own timeout.
• jsonb[]/json[]: ParamExtractor.prepareJson encodes a Java null element as the JSON value
  null, so the row is stored with attributes = 'null'::jsonb instead of SQL NULL
  (attributes IS NULL is false). This contradicts the documented tuple contract where Java null
  means SQL NULL and Tuple.JSON_NULL means JSON null. Reading such a row back with
  Row#getJsonObject then throws ClassCastException: class io.vertx.sqlclient.Tuple$1 cannot be cast to class io.vertx.core.json.JsonObject.
• unknown[] looks affected the same way: prepareUnknown is String.valueOf(value), turning a
  null element into the string "null".

The cause looks like the per-element loop added in PgParamDesc#prepare (5.x): the scalar path is
guarded by if (val != null), but the array path applies the preEncoder to every element without
a null check:

if (paramDataType.array) {
  Object[] array = (Object[]) val;
  Object[] tmp = new Object[array.length];
  for (int j = 0; j < array.length; j++) {
    tmp[j] = preparator.apply(array[j]);   // array[j] may be null
  }
  val = tmp;
}

A one-line fix would be to keep null elements as-is:

tmp[j] = array[j] == null ? null : preparator.apply(array[j]);

Separately, it seems wrong that an error thrown from PgPreparedStatement#prepare on the event loop
leaves the query future uncompleted — that turns a bind error into a hang.

Do you have a reproducer?

Yes — single-file, no build tooling needed (inlined below).

docker run --rm -d -p 5432:5432 -e POSTGRES_PASSWORD=password --name pg postgres:17
cs fetch --classpath io.vertx:vertx-pg-client:5.1.6 > cp.txt   # or 4.5.32 for comparison
javac -cp $(cat cp.txt) -d out Repro.java
java -ea -cp out:$(cat cp.txt) Repro

It creates repro (id int primary key, amount numeric, attributes jsonb) and inserts two rows via
UNNEST, where the second row's amount / attributes element is null.

Repro.java

import io.vertx.core.Vertx;
import io.vertx.core.json.JsonObject;
import io.vertx.pgclient.PgBuilder;
import io.vertx.pgclient.PgConnectOptions;
import io.vertx.sqlclient.Pool;
import io.vertx.sqlclient.Row;
import io.vertx.sqlclient.RowSet;
import io.vertx.sqlclient.Tuple;

import java.math.BigDecimal;
import java.util.concurrent.TimeUnit;

/**
 * Inserts two rows through UNNEST, where the nullable numeric / jsonb column is null for the second
 * row.
 *
 * Expected (the behaviour of 4.5.x): both rows are inserted, and the second row's numeric / jsonb
 * column is SQL NULL.
 */
public class Repro {

  private static final String INSERT_NUMERIC =
    "INSERT INTO repro (id, amount) SELECT * FROM UNNEST($1::int[], $2::numeric[])";
  private static final String INSERT_JSONB =
    "INSERT INTO repro (id, attributes) SELECT * FROM UNNEST($1::int[], $2::jsonb[])";

  public static void main(String[] args) throws Exception {
    PgConnectOptions connectOptions = new PgConnectOptions()
      .setHost("localhost")
      .setPort(5432)
      .setDatabase("postgres")
      .setUser("postgres")
      .setPassword("password");

    Vertx vertx = Vertx.vertx();
    Pool pool = PgBuilder.pool().using(vertx).connectingTo(connectOptions).build();

    await(pool.query("DROP TABLE IF EXISTS repro").execute());
    await(pool.query("CREATE TABLE repro (id int primary key, amount numeric, attributes jsonb)")
      .execute());

    Object[] ids = new Integer[]{1, 2};

    System.out.println("== numeric[] with a null element ==");
    Object[] amounts = new BigDecimal[]{new BigDecimal("1.5"), null};
    try {
      await(pool.preparedQuery(INSERT_NUMERIC)
        .execute(Tuple.tuple().addValue(ids).addValue(amounts)));
      System.out.println("inserted");
    } catch (Exception e) {
      System.out.println("FAILED: " + rootCause(e));
    }

    System.out.println("== jsonb[] with a null element ==");
    Object[] otherIds = new Integer[]{3, 4};
    Object[] attributes = new Object[]{new JsonObject().put("a", 1), null};
    try {
      await(pool.preparedQuery(INSERT_JSONB)
        .execute(Tuple.tuple().addValue(otherIds).addValue(attributes)));
      System.out.println("inserted");
    } catch (Exception e) {
      System.out.println("FAILED: " + rootCause(e));
    }

    RowSet<Row> rows = await(pool.query(
      "SELECT id, amount, attributes, attributes IS NULL AS attributes_is_null FROM repro ORDER BY id")
      .execute());
    for (Row row : rows) {
      System.out.println("id=" + row.getInteger("id")
        + " amount=" + row.getValue("amount")
        + " attributes=" + row.getValue("attributes")
        + " attributes IS NULL=" + row.getBoolean("attributes_is_null"));
    }

    await(pool.close());
    await(vertx.close());
  }

  private static <T> T await(io.vertx.core.Future<T> future) throws Exception {
    return future.toCompletionStage().toCompletableFuture().get(10, TimeUnit.SECONDS);
  }

  private static Throwable rootCause(Throwable t) {
    Throwable cause = t;
    while (cause.getCause() != null) {
      cause = cause.getCause();
    }
    return cause;
  }
}

Steps to reproduce / output

4.5.32 (expected):

== numeric[] with a null element ==
inserted
== jsonb[] with a null element ==
inserted
id=1 amount=1.5 attributes=null attributes IS NULL=true
id=2 amount=null attributes=null attributes IS NULL=true
id=3 amount=null attributes={"a":1} attributes IS NULL=false
id=4 amount=null attributes=null attributes IS NULL=true

5.0.12 and 5.1.6:

== numeric[] with a null element ==
SEVERE: Unhandled exception
java.lang.AssertionError
	at io.vertx.pgclient.impl.codec.ParamExtractor.prepareNumeric(ParamExtractor.java:51)
	at io.vertx.pgclient.impl.codec.PgParamDesc.prepare(PgParamDesc.java:73)
	at io.vertx.pgclient.impl.codec.PgPreparedStatement.prepare(PgPreparedStatement.java:57)
	at io.vertx.sqlclient.internal.command.ExtendedQueryCommand.prepare(ExtendedQueryCommand.java:121)
	at io.vertx.sqlclient.impl.SocketConnectionBase.lambda$prepareCommand$4(SocketConnectionBase.java:288)
	...
FAILED: java.util.concurrent.TimeoutException     <- future never completes
== jsonb[] with a null element ==
inserted
id=3 amount=null attributes={"a":1} attributes IS NULL=false
id=4 amount=null attributes=null attributes IS NULL=false   <- json 'null', not SQL NULL

Without -ea the numeric case fails with
VertxException: Cannot invoke "Object.toString()" because "value" is null instead of hanging.

Extra

• The only workaround we found is binding such arrays as text[] and casting in SQL
  ($2::text[] + amount::numeric), which is invasive for every bulk insert with a nullable
  numeric/json column.
• Postgres arrays allow NULL elements and the client decodes them as Java null, so round-tripping a
  decoded array back as a bind parameter is broken today.