Quarkus sql client PreparedQuery.executeBatch number of rows affected always equal to 1

[lucafuji]

Describe the bug

We are using io.quarkus:quarkus-reactive-pg-client library and quarkus 1.11.1.Final.
We are using the preparedQuery.executeBatch as below, but the rowCount always returns 1 no matter how many rows it updated

import io.vertx.mutiny.sqlclient.RowIterator;
import io.vertx.mutiny.sqlclient.RowSet;
import io.vertx.mutiny.sqlclient.SqlClient;
import io.vertx.mutiny.sqlclient.SqlResult;
import io.vertx.mutiny.sqlclient.Tuple;

  default Uni<Integer> batchUpdate(SqlClient client, String sql, List<List<Object>> paramList) {
    return client.preparedQuery(sql).
        executeBatch(prepareBatchParams(paramList)).
        map(SqlResult::rowCount);
  }

Expected behavior

The row count should return actual number of rows updated

Actual behavior

Always returns 1

[geoand]

cc @tsegismont

[tsegismont]

Thanks for the heads-up @geoand . I'll look into it

[tsegismont]

@lucafuji this might not be a bug.

The rowCount method returns the affected rows for each RowSet. If the query executed affects one row per batch, then calling rowCount on each RowSet will return 1.

Can you try to iterate using RowSet.next until it returns null, and verify RowSet.rowCount returns 1 every time?

[lucafuji]

@tsegismont It seems the returned row set is empty, I made the following changes:

default Uni<Integer> batchUpdate(SqlClient client, String sql, List<List<Object>> paramList) {
    return client.preparedQuery(sql).
        executeBatch(prepareBatchParams(paramList)).
        map(rows -> rows.iterator().next().getInteger(0)));
  }

and immediately got a NoSuchElementException

"java.util.NoSuchElementException: null\n\tat java.util.ArrayList$Itr.next(ArrayList.java:862)\n\tat io.vertx.sqlclient.impl.RowSetImpl$1.next(RowSetImpl.java:74)\n\tat io.vertx.mutiny.sqlclient.RowIterator.next(RowIterator.java:71)\n

[tsegismont]

@lucafuji it is expected that the rowset is null if the query does not return results (e.g. INSERT or UPDATE without RETURNING clause). I guess this is why you get a NoSuchElementException by calling next() on the rowset iterator.

My suggestion was to invoke next() on the result directly:

client.preparedQuery(sql)
        .executeBatch(prepareBatchParams(paramList))
        .map(res -> {
                int total = 0;
                do {
                  total += res.rowCount();
                } while ((res = res.next()) != null);
                return total;
        });

[geoand]

@tsegismont is your suggestion something we should document somewhere?

[tsegismont]

@geoand we could do that. First let's see if this is a misuse of the API or actually a bug.

[lucafuji]

seems working now @tsegismont thanks a lot

[zaneschepke]

For anyone wanting to have all of the actual inserted results from their RETURNING * query instead of just rowCount, this seems to work.
client.preparedQuery(sql).executeBatch(batch).map(res -> { Set<Row> rows = new HashSet<>(); do { res.forEach(rows::add); } while ((res = res.next()) != null); return rows; }) .onItem().transformToMulti(set -> Multi.createFrom().iterable(set)) .onItem().transform(MyObject::from);

[tsegismont]

@zaneschepke this would work too:

        client.preparedQuery(sql).executeBatch(batch)
                .onItem().<RowSet<Row>>transformToMulti(res ->
                    // Generate a Multi of RowSet items
                    Multi.createFrom().generator(() -> res, (rs, emitter) -> {
                        RowSet<Row> next = null;
                        if (rs != null) {
                            emitter.emit(rs);
                            next = rs.next();
                        }
                        if (next == null) {
                            emitter.complete();
                        }
                        return next;
                    })
                )
                // Transform each RowSet into Multi and Concatenate 
                .onItem().transformToMultiAndConcatenate(Multi.createFrom()::iterable)
                // Transform each Row into MyObject
                .onItem().transform(MyObject::from);

[cescoffier]

@tsegismont should this be documented?

[tsegismont]

@cescoffier where would be document this?

[cescoffier]

@tsegismont I would say here: https://github.com/quarkusio/quarkus/blob/main/docs/src/main/asciidoc/reactive-sql-clients.adoc