#1739 NPE in Session.executeCompiledBatchStatement with "insert on duplicate key update" statements

[Andy Carroll]

A bug was introduced in version 2.5.1, such that line 1609 of Session can blow up with an NPE, when an insert on duplicate key update statement. The issue is that starting with 2.5.1 due to a change in StatementInsert which cs.hasGeneratedColumns() to return true, but in.getChainedResult() to return null
This causes the following to throw a NullPointerException.

            if (cs.hasGeneratedColumns()) {
                RowSetNavigator navgen = in.getChainedResult() // this can be null in the case "insert / on duplicate key update" synax
                                           .getNavigator(); // <--- this can throw an NPE

 A simple null check works. 
 ie. this code works
                if (cs.hasGeneratedColumns()) {
                // @LQBK - FIXED NPE, check in.getChainedResult() for null before de-referecning
                Result chained = in.getChainedResult();
                if (chained != null) {
                    RowSetNavigator navgen = chained.getNavigator();

                    while (navgen != null && navgen.next()) {

Im not familiar with your unit test infrastructure or how to submit a patch request.
What do we need to do to get this fixed agsinst the 2.7.4 code base?

[Andy Carroll]

Just to clarify: the bug was introduced in 2.5.1 but still exists in 2.7.4 <-- this bug has prevented us from upgrading. We are only looking for a fix to the latest hsqldb code base. We do not expect a back port to older versions. We're unfamiliar with the hsql bug review process. @fredt would you be able to guide as as to next steps as we would love to see this fixed so we can upgrade to the latest version.

[Andy Carroll]

I'd like to clarify this ticket. Firstly the milestone should be for the next 2.7.x point release - NOT 2.5.x . We only want this fixed in the latest version of HSQLDB.

@fredt would you mind taking a look? What we are asking for is a Null check in a code flow that throws an NPE - no other logic change is needed.

If you look at the latest version (2.7.4) of Session.java the method Session.executeCompiledBatchStatement can blow up on line 1609 as it assumes Result.getChainedResult() can not return null in this circumstance.

Specifically for "INSERT / ON DUPLICATE KEY UPDATE" statement this snippet line 1609 of Session.java can throw a throw a NullPointerException

           RowSetNavigator navgen = in.getChainedResult()
                                       .getNavigator();

All we need is to change the code thus

        Result chained = in.getChainedResult();
        if (chained != null) {
            RowSetNavigator navgen = chained.getNavigator();

ie. Check if the chained result is null before calling getNavigator.

Fwiw. the root cause is a refactoring of InsertStatement back in in v 2.5.1 - a side effect of that refactoring is 'INSERT / ON DUPLICATE KEY' statements can return a Result for which Result.hasGeneratedColumns() returns true, while Result.getChainedResult return null, hence the NPE without a check for that.

Can we get this NPE fix scheduled for the next point release - its low risk as it literally is just preventing an NPE in a code flow for which a NullPointerException is not handled.

But we cant move beyond 2.5.0 until this is fixed.

Side comment: Were are using "INSERT / ON DUPLICATE KEY" in a batch. ie. create the prepared statement, add batches of data to it. I didnt fully internalize the refactoring of InsertStatement made back in v 2.5.1 - but "INSERT / ON DUPLICATE KEY UPDATE" is unique in so far as until its executed theres no way of knowing whether a given batch of data is an insert or update. (first row might be an update, second row an insert and vice versa.) Thats a run time consideration thats only known after it executes. Just mentioning - in case the fact insert vs update is heterogenous, is why post the 2.5.1 refactoring of InsertStatements its now legal for getChainedResults to sometimes be null.

[Fred Toussi]

Thanks for reporting. I think this occurs only with ON DUPLICATE KEY UPDATE. It will be fixed in the next point release. I will post here when it is committed and you can then compile the jar and test with you app.

[Alex Rosenfeld]

Hi @fredt, hope you don't mind me checking in, just wondering if / when you might have a fix for this released. I understand it's an open source project you're authoring and maintaining, so not trying to be rude and rush you. Thanks in advance

[Fred Toussi]

The next point release, date not yet determined.

[Andy Carroll]

will do.

On Sun, Mar 29, 2026 at 8:03 PM Fred Toussi fredt@users.sourceforge.net
wrote:

• status: open --> open-works-for-me
• assigned_to: Fred Toussi
• Comment:

I cannot reproduce this. Please post a modified version of the working
code below to cause the NPE.

    Connection connection = newConnection();        Statement statement = connection.createStatement();        statement.execute("SET DATABASE SQL SYNTAX MYS TRUE");        statement.execute("DROP TABLE test3 IF EXISTS");        statement.execute("CREATE TABLE test3 (id INT GENERATED BY DEFAULT AS IDENTITY, data BIGINT)");
    PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO test3 (id, data) VALUES (?, ?) ON DUPLICATE KEY UPDATE id = 1", Statement.RETURN_GENERATED_KEYS);        preparedStatement.setInt(1, 1);        preparedStatement.setLong(2, 42L);        preparedStatement.addBatch();        preparedStatement.setInt(1, 3);        preparedStatement.setNull(2, java.sql.Types.BIGINT);        preparedStatement.addBatch();
    preparedStatement.executeBatch();
    ResultSet resultSet = preparedStatement.getGeneratedKeys();        resultSet.next();

---

[bugs:#1739] https://sourceforge.net/p/hsqldb/bugs/1739/ NPE in
Session.executeCompiledBatchStatement with "insert on duplicate key update"
statements

Status: open-works-for-me
Group: version 2.5.x
Created: Wed Oct 01, 2025 11:33 PM UTC by Andy Carroll
Last Updated: Wed Jan 28, 2026 10:04 AM UTC
Owner: Fred Toussi

A bug was introduced in version 2.5.1, such that line 1609 of Session can
blow up with an NPE, when an insert on duplicate key update statement. The
issue is that starting with 2.5.1 due to a change in StatementInsert which
cs.hasGeneratedColumns() to return true, but in.getChainedResult() to
return null
This causes the following to throw a NullPointerException.

        if (cs.hasGeneratedColumns()) {                RowSetNavigator navgen = in.getChainedResult() // this can be null in the case "insert / on duplicate key update" synax                                           .getNavigator(); // <--- this can throw an NPE

A simple null check works. ie. this code works if (cs.hasGeneratedColumns()) { // @LQBK - FIXED NPE, check in.getChainedResult() for null before de-referecning Result chained = in.getChainedResult(); if (chained != null) { RowSetNavigator navgen = chained.getNavigator();
while (navgen != null && navgen.next()) {

Im not familiar with your unit test infrastructure or how to submit a
patch request.
What do we need to do to get this fixed agsinst the 2.7.4 code base?

---

Sent from sourceforge.net because you indicated interest in
https://sourceforge.net/p/hsqldb/bugs/1739/

To unsubscribe from further messages, please visit
https://sourceforge.net/auth/subscriptions/

[Fred Toussi]

I cannot reproduce this. Please post a modified version of the working code below to cause the NPE.

        Connection connection = newConnection();
        Statement statement = connection.createStatement();
        statement.execute("SET DATABASE SQL SYNTAX MYS TRUE");
        statement.execute("DROP TABLE test3 IF EXISTS");
        statement.execute("CREATE TABLE test3 (id INT GENERATED BY DEFAULT AS IDENTITY, data BIGINT)");

        PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO test3 (id, data) VALUES (?, ?) ON DUPLICATE KEY UPDATE id = 1", Statement.RETURN_GENERATED_KEYS);
        preparedStatement.setInt(1, 1);
        preparedStatement.setLong(2, 42L);
        preparedStatement.addBatch();
        preparedStatement.setInt(1, 3);
        preparedStatement.setNull(2, java.sql.Types.BIGINT);
        preparedStatement.addBatch();

        preparedStatement.executeBatch();

        ResultSet resultSet = preparedStatement.getGeneratedKeys();
        resultSet.next();