Permit bulk implementation to return ALL individual results

The bulk command COM_STMT_BULK_STMT actually returns one OK_Packet that contains the first insert ID and the total of affected rows.

Using java there is 2 behaviors that are not compatible :

• When all auto increment id's needs to be retrieved.
• when using optimistic batch.

Explaination of those use cases :

insert ids needed

Prepared statement can be created with explicit indicator that auto increment ids are needed : https://docs.oracle.com/en/java/javase/18/docs/api/java.sql/java/sql/Connection.html#prepareStatement(java.lang.String,int)

example :

    try (PreparedStatement ps =

        sharedConn.prepareStatement(

            "INSERT INTO prepare2(t2) VALUES (?)", java.sql.Statement.RETURN_GENERATED_KEYS)) {

      ps.setInt(1, 10);

      ps.addBatch();

      ps.setInt(1, 20);

      ps.addBatch();

      ps.executeBatch();

      ResultSet rs = ps.getGeneratedKeys();

      assertTrue(rs.next());

      assertEquals(1, rs.getInt(1));

      assertTrue(rs.next());

      assertEquals(2, rs.getInt(1));

      assertFalse(rs.next());

    }

Actual java connector implementation is to just loop with COM_STMT_EXECUTE, since driver is aware that generated id's are needed.

Second case is more problematic.

optimistic locking.

In java, when using multiple client servers, a stragegy named "Optimistic locking" to have faster result is to cache some data client side. "Optimistic locking" doesn't shared cache information between client servers: This is more complex than that, but to simplify, all tables have an additional 'version' column that is incremented at any changes. All operations on data will check primary AND that version equality, then increment version.
example :

Table basket_item DDL :

create table people (

    name varchar(32) not null,

    age int,

    version int not null,

    PRIMARY KEY (name));

When updating some Entity, update command will use primary key + version like, incrementing version :

 UPDATE people SET age=26, version=11 WHERE name='john Doe' AND version = 10;

Application check that affected rows is exactly 1 to ensure value has not changed by another client during the time this data has been in client cache. If Affected row is 0, then application knows changes has occurs, then will handle that.

the problem is when using BULK, actual server implementation only returns the total number of changes, not unitary change. Java connector then returns an array of Statement.SUCCESS_NO_INFO, indicating that command has succedded,not knowing exact affected rows. This isn't compatible with Optimistic locking.

Solution

There is a flag STMT_BULK_FLAG_SEND_UNIT_RESULTS for bulk that has not been implemented.
Client need to know that server permit that functionality (new capability MARIADB_CLIENT_BULK_UNIT_RESULTS) in order to know if can use that functionality.

Link is a proposed implementation (based on 11.0).
Proposition is to add an ending resulset containing all unitary changes, since BULK can be use with command using RETURNING.