Table.batchInsert throws java.lang.ArrayIndexOutOfBoundsException: 10

[Petitt88]

I have a fairly simple Users table, it looks like this:

object Users : Table("users") {
    val id = long("id").autoIncrement("users_seq").primaryKey()
    val userName = nvarchar2("user_name", 256).uniqueIndex()
    val displayName = nvarchar2("display_name", 256).nullable()
}

The schema has to 2 more tables in relation to the Users table: Roles and UserRoles:

object UserRoles : Table("account_user_roles") {
    val userId = (long("user_id") references Users.id).primaryKey()
    val roleId = (long("role_id") references Roles.id).primaryKey()
}
object Roles : Table("account_roles") {
    val id = long("id").autoIncrement("roles_seq").primaryKey()
    val displayName = nvarchar2("display_name", 256)
}

When I try to batchinsert (in my case 156 user records into the users table) I get this exception:

Users.batchInsert(newUsers) {
    this[Users.userName] = it.userName
    this[Users.displayName] = it.displayName
}

java.lang.ArrayIndexOutOfBoundsException: 10
    at oracle.jdbc.driver.Accessor.setOffset(Accessor.java:651) ~[ojdbc7.jar:12.1.0.1.0]
    at oracle.jdbc.driver.T4CRowidAccessor.unmarshalBytes(T4CRowidAccessor.java:205) ~[ojdbc7.jar:12.1.0.1.0]
    at oracle.jdbc.driver.T4CRowidAccessor.unmarshalOneRow(T4CRowidAccessor.java:146) ~[ojdbc7.jar:12.1.0.1.0]
    at oracle.jdbc.driver.T4C8Oall.readRXD(T4C8Oall.java:809) ~[ojdbc7.jar:12.1.0.1.0]
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:441) ~[ojdbc7.jar:12.1.0.1.0]
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249) ~[ojdbc7.jar:12.1.0.1.0]
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566) ~[ojdbc7.jar:12.1.0.1.0]
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215) ~[ojdbc7.jar:12.1.0.1.0]
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58) ~[ojdbc7.jar:12.1.0.1.0]
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:943) ~[ojdbc7.jar:12.1.0.1.0]
    at oracle.jdbc.driver.OraclePreparedStatement.executeForRowsWithTimeout(OraclePreparedStatement.java:10932) ~[ojdbc7.jar:12.1.0.1.0]
    at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:11043) ~[ojdbc7.jar:12.1.0.1.0]
    at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:244) ~[ojdbc7.jar:12.1.0.1.0]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_152]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_152]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_152]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_152]
    at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) ~[tomcat-jdbc-8.5.23.jar:na]
    at com.sun.proxy.$Proxy122.executeBatch(Unknown Source) ~[na:na]
    at org.jetbrains.exposed.sql.statements.InsertStatement.execInsertFunction(InsertStatement.kt:82) ~[exposed-0.9.2-SNAPSHOT.jar:na]
    at org.jetbrains.exposed.sql.statements.InsertStatement.executeInternal(InsertStatement.kt:91) ~[exposed-0.9.2-SNAPSHOT.jar:na]
    at org.jetbrains.exposed.sql.statements.InsertStatement.executeInternal(InsertStatement.kt:11) ~[exposed-0.9.2-SNAPSHOT.jar:na]
    at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed(Statement.kt:55) ~[exposed-0.9.2-SNAPSHOT.jar:na]
    at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:103) ~[exposed-0.9.2-SNAPSHOT.jar:na]
    at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:97) ~[exposed-0.9.2-SNAPSHOT.jar:na]
    at org.jetbrains.exposed.sql.statements.Statement.execute(Statement.kt:27) ~[exposed-0.9.2-SNAPSHOT.jar:na]
    at org.jetbrains.exposed.sql.QueriesKt.batchInsert(Queries.kt:89) ~[exposed-0.9.2-SNAPSHOT.jar:na]
    at org.jetbrains.exposed.sql.QueriesKt.batchInsert$default(Queries.kt:59) ~[exposed-0.9.2-SNAPSHOT.jar:na]
    at com.test.UserRepository$updateUsers$$inlined$transactionWithLogging$1.invoke(Transactions.kt:71) ~[classes/:na]
    at com.test.UserRepository$updateUsers$$inlined$transactionWithLogging$1.invoke(Transactions.kt) ~[classes/:na]
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:92) ~[exposed-0.9.2-SNAPSHOT.jar:na]
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:64) ~[exposed-0.9.2-SNAPSHOT.jar:na]
    ...

If I do a normal insert in a for loop Users.insert {...} - it works.

[Tapac]

Is this reproducible for any size of newUsers ? There is batchInsert test for every DBMS and no problem with Oracle. Could it happen when you try to insert userName which already exists in DB?

[HirumaStu]

Hello @Tapac, I faced with the same issue and investigated this bug a little bit. Oracle driver doesn't like executeBatch with PreparedStatement.RETURN_GENERATED_KEYS. ojdbc6 (11.2.0.3 and 11.2.0.4 versions) throw OOB exception on any batch size > 1, ojdbc7 (12.1.0.2) doesn't work on batches with size > 10 (It depends on CONNECTION_PROPERTY_DEFAULT_ROW_PREFETCH_DEFAULT). ojdbc8 (12.2.0.1) works well, but returned list of columns contains only empty hashmaps and can't be used as list of generated keys.

8th version should solve problem with batchInsert in case where generated keys are not required. At least it supports all latest version of oracle db.

All versions was tested on windows against oracle 11g db.

[Tapac]

@HirumaStu , thank you for your detailed investigation. So if I understand properly batchInsert implementation for Oracle should depend on jdbc version and CONNECTION_PROPERTY_DEFAULT_ROW_PREFETCH_DEFAULT property value? And if it doesn't support returning generated keys then it should be replaced with a simple insert?

[joc-a]

@HirumaStu , thank you for your detailed investigation. So if I understand properly batchInsert implementation for Oracle should depend on jdbc version and CONNECTION_PROPERTY_DEFAULT_ROW_PREFETCH_DEFAULT property value? And if it doesn't support returning generated keys then it should be replaced with a simple insert?

Hey @Petitt88 @HirumaStu , any thoughts on the above?