Regression in 1.3.0.0 Current transaction is aborted (please ROLLBACK) when running faulty JDBC batch statements even if auto commit is set to true

[lukaseder]

Try running this program:

connection.setAutoCommit(true);
try {
    try (Statement s = connection.createStatement()) {
        s.executeUpdate("drop table if exists t");
        s.executeUpdate("create table t (i int not null)");
    }

    try (Statement s = connection.createStatement()) {
        s.addBatch("insert into t values (null)");
        s.executeBatch();
    }
}
finally {
    try (Statement s = connection.createStatement()) {
        s.executeUpdate("drop table t");
    }
}

It produces this error on the drop table t statement:

java.sql.SQLException: TransactionContext Error: Current transaction is aborted (please ROLLBACK)
	at org.duckdb.DuckDBNative.duckdb_jdbc_execute(Native Method)
	at org.duckdb.DuckDBPreparedStatement.execute(DuckDBPreparedStatement.java:178)
	at org.duckdb.DuckDBPreparedStatement.execute(DuckDBPreparedStatement.java:155)
	at org.duckdb.DuckDBPreparedStatement.executeLargeUpdate(DuckDBPreparedStatement.java:229)
	at org.duckdb.DuckDBPreparedStatement.executeLargeUpdate(DuckDBPreparedStatement.java:259)
	at org.duckdb.DuckDBPreparedStatement.executeUpdate(DuckDBPreparedStatement.java:252)
	at org.jooq.testscripts.JDBC.main(JDBC.java:73)

The problem is that the s.executeBatch() call fails with a constraint violation exception, which seems to abort an internal transaction. But I set the auto commit flag to true explicitly, so I don't think the user should have to recover from this problem, it should be handled internally inside of executeBatch().

Note:

• This doesn't happen with ordinary statement execution, only with batches.
• This is a regression in version 1.3.0.0 of the driver. The problem didn't happen yet in 1.1.3 or 1.2.2.0 yet

[lukaseder]

It seems obvious from the source code:

• https://github.com/duckdb/duckdb-java/blob/v1.3.0.0/src/main/java/org/duckdb/DuckDBPreparedStatement.java#L636

Only the happy path is covered. This change was introduced with: f647a9f

[lukaseder]

Looking into 1.2.2.0 source code, the transaction was also started, but it seemingly didn't have this effect on subsequent statements, so two things are interacting here.

[staticlibs]

@lukaseder

Thanks for the report! You are absolutely correct, with auto-commit enabled rollback should be handled inside executeBatch. This should be fixed by #261.

And in 1.2.x there were effectively no batching with auto-commit enabled, so the problem did not happen.

BTW, we are releasing 1.3.1 update next week, would it be acceptable if I create PRs to jOOQ bumping the version when DuckDB updates are out?

[lukaseder]

would it be acceptable if I create PRs to jOOQ bumping the version when DuckDB updates are out?

Thanks for the offer, no need. I'll upgrade to 1.3.x at some later stage.