No records returned with useServerPrepStmts and large result size

There is a bug that is apparent in multiple MariaDB versions, where records are not being returned if useServerPrepStmts is true and the batch size is >= 1000. What happens is that only the last batch is returned and all the previous ones are returned empty.

MariaDB versions that trigger the bug: 10.3.34, 10.5.15, 10.6.7, 10.7.3, 10.8.2
MariaDB versions that do not trigger the bug: 10.3.32, 10.5.12.1, 10.5.13

You can find a Java class attached that can be used to demonstrate the bug on a MariaDB database. This basically populates a MariaDB table with seedCount ids. Then, it performs a query on that table in a sequence of operations, each covering a subset of the id values to be queried (called batchSize).

Below, there are different executions of the BugDemo.java class that showcase the bug. The MariaDB version in this case is 10.5.15 and the seedCount is 2500.

Case 1:

• useServerPrepStmts: true
• batchSize: 1000
  Output:

  DB version: 10.5.15-MariaDB

  Total records available: 2500

  Batch retrieved record count: 0

  Batch retrieved record count: 0

  Batch retrieved record count: 500

  Total records retrieved: 500

  Not all records retrieved: bug was triggered

Case 2:

• useServerPrepStmts: false
• batchSize: 1000
  Output:

  DB version: 10.5.15-MariaDB

  Total records available: 2500

  Batch retrieved record count: 1000

  Batch retrieved record count: 1000

  Batch retrieved record count: 500

  Total records retrieved: 2500

  All records retrieved: bug was not triggered

Case 3:

• useServerPrepStmts: true
• batchSize: 999
  Output:

  DB version: 10.5.15-MariaDB

  Total records available: 2500

  Batch retrieved record count: 999

  Batch retrieved record count: 999

  Batch retrieved record count: 502

  Total records retrieved: 2500

  All records retrieved: bug was not triggered