Call JDBC3PreparedStatement.executeQuery can return null

[AndSDev]

I noticed an error after update org.xerial:sqlite-jdbc to latest version.

Describe the bug

The call of org.sqlite.jdbc3.JDBC3PreparedStatement.executeQuery can return null, which is against the specifications.

This can happen when using org.apache.commons.dbcp2.BasicDataSource (from org.apache.commons:commons-dbcp2) with enabled prepared statements pool: dataSource.setPoolPreparedStatements(true).

As I understand it, the problem is in the org.sqlite.jdbc3.JDBC3Statement.exhaustedResults flag, which is enabled when the prepared statement is reused.

To Reproduce

Example for reproducing:

pom.xml

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.example</groupId>
    <artifactId>sandbox</artifactId>
    <version>0.0-SNAPSHOT</version>

    <name>sandbox</name>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
    </properties>

    <dependencies>

        <dependency>
            <groupId>com.j256.ormlite</groupId>
            <artifactId>ormlite-jdbc</artifactId>
            <version>5.1</version>
        </dependency>

        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>2.9.0</version>
        </dependency>

        <dependency>
            <groupId>org.xerial</groupId>
            <artifactId>sqlite-jdbc</artifactId>
            <version>3.42.0.0</version>
        </dependency>

    </dependencies>

</project>

src/main/java/com/example/App.java

package com.example;

import java.sql.Connection;
import java.sql.Statement;

import org.apache.commons.dbcp2.BasicDataSource;

import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.dao.DaoManager;
import com.j256.ormlite.field.DatabaseField;
import com.j256.ormlite.field.SqlType;
import com.j256.ormlite.jdbc.DataSourceConnectionSource;
import com.j256.ormlite.stmt.QueryBuilder;
import com.j256.ormlite.stmt.ThreadLocalSelectArg;
import com.j256.ormlite.support.ConnectionSource;
import com.j256.ormlite.table.DatabaseTable;

public class App {

    public static String CREATE_SQL = """
              CREATE TABLE \"storage\" (
              \"row_id\" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
              \"key\" text NOT NULL,
              \"value\" text NOT NULL
            );
              """;

    public static void main(String[] args) throws Exception {

        String connectURI = "jdbc:sqlite:file:memDb?mode=memory&cache=shared";
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setUrl(connectURI);

        // Disable this
        dataSource.setPoolPreparedStatements(true);

        dataSource.setDriverClassName("org.sqlite.JDBC");

        try (Connection connection = dataSource.getConnection()) {
            try (Statement statement = connection.createStatement()) {
                statement.executeUpdate(CREATE_SQL);
            }
        }

        try (ConnectionSource connectionSource = new DataSourceConnectionSource(dataSource,
                dataSource.getUrl())) {

            Dao<DatabaseProperty, Long> daoSettings = DaoManager.createDao(connectionSource, DatabaseProperty.class);

            QueryBuilder<DatabaseProperty, Long> queryBuilder = daoSettings.queryBuilder();
            queryBuilder.selectColumns("value");
            queryBuilder.where().eq("key", new ThreadLocalSelectArg(SqlType.STRING, "Setting"));

            DatabaseProperty record = queryBuilder.queryForFirst();

            System.out.printf("Step 1: value must be 'null': %s\n", record);

            daoSettings.createOrUpdate(new DatabaseProperty() {
                {
                    key = "Setting";
                    value = "OK";
                }
            });

            DatabaseProperty record2 = queryBuilder.queryForFirst();

            System.out.printf("Step 2: value must be 'OK': %s\n", record2.value);
        }
    }

    @DatabaseTable(tableName = "storage")
    private static class DatabaseProperty {
        @DatabaseField(id = true, columnName = "key", canBeNull = false)
        public String key;
        @DatabaseField(columnName = "value")
        public String value;
    }
}

Expected behavior

Show "OK" in output:

Step 1: value must be null: null
Step 2: value must be 'OK': OK

Logs

Execution will cause an error associated with the described bug (resultSet cannot be null by specs):

Step 1: value must be null: null
Exception in thread "main" java.lang.NullPointerException: Cannot invoke "java.sql.ResultSet.getMetaData()" because "resultSet" is null
        at com.j256.ormlite.jdbc.JdbcDatabaseResults.<init>(JdbcDatabaseResults.java:35)
        at com.j256.ormlite.jdbc.JdbcCompiledStatement.runQuery(JdbcCompiledStatement.java:63)
        at com.j256.ormlite.stmt.StatementExecutor.queryForFirst(StatementExecutor.java:106)
        at com.j256.ormlite.dao.BaseDaoImpl.queryForFirst(BaseDaoImpl.java:240)
        at com.j256.ormlite.stmt.QueryBuilder.queryForFirst(QueryBuilder.java:380)
        at com.example.App.main(App.java:65)

Environment:

• OS: Ubuntu 22.04
• CPU architecture: x86_64
• sqlite-jdbc version: 3.39.4.0+ (works on 3.39.3.0)
• Java version: 17.0.5

openjdk 17.0.5 2022-10-18
OpenJDK Runtime Environment Temurin-17.0.5+8 (build 17.0.5+8)
OpenJDK 64-Bit Server VM Temurin-17.0.5+8 (build 17.0.5+8, mixed mode, sharing)

Additional context

Stacktrace before returning null (with org.xerial:sqlite-jdbc:3.42.0.0):

org.sqlite.jdbc3.JDBC3Statement.getResultSet(JDBC3Statement.java:149)
org.sqlite.jdbc3.JDBC3PreparedStatement.lambda$executeQuery$1(JDBC3PreparedStatement.java:94)
org.sqlite.jdbc3.JDBC3PreparedStatement$$Lambda$94/0x0000000800cb7578.call(Unknown Source)
org.sqlite.jdbc3.JDBC3Statement.withConnectionTimeout(JDBC3Statement.java:454)
org.sqlite.jdbc3.JDBC3PreparedStatement.executeQuery(JDBC3PreparedStatement.java:82)
org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
com.j256.ormlite.jdbc.JdbcCompiledStatement.runQuery(JdbcCompiledStatement.java:63)
com.j256.ormlite.stmt.StatementExecutor.queryForFirst(StatementExecutor.java:106)
com.j256.ormlite.dao.BaseDaoImpl.queryForFirst(BaseDaoImpl.java:240)
com.j256.ormlite.stmt.QueryBuilder.queryForFirst(QueryBuilder.java:380)
com.example.App.main(App.java:65)

[gotson]

This is difficult to investigate as it apparently relies on a 3rd party project. Are you able to provide a repro that is not using CP2 ?

[AndSDev]

Using only org.xerial:sqlite-jdbc in deps:

package com.example;

import java.sql.DriverManager;

public class App {

    public static void main(String[] args) throws Exception {

        try (var connection = DriverManager.getConnection("jdbc:sqlite:file:memDb?mode=memory&cache=shared")) {
            try (var statement = connection.prepareStatement("SELECT 1")) {

                var process = statement.execute();
                while (process) {
                    var result = statement.getResultSet();
                    while (result.next()) {
                        System.out.printf("Step 1: value must be '1': %s\n", result.getString(1));
                    }
                    process = statement.getMoreResults();
                }

                var result = statement.executeQuery();
                while (result.next()) {
                    System.out.printf("Step 2: value must be '1': %s\n", result.getString(1));
                }
            }
        }
    }
}

In log:

java.lang.IllegalArgumentException: Self-suppression not permitted
    at java.lang.Throwable.addSuppressed (Throwable.java:1072)
    at com.example.App.main (App.java:26)
    at org.codehaus.mojo.exec.ExecJavaMojo$1.run (ExecJavaMojo.java:279)
    at java.lang.Thread.run (Thread.java:833)
Caused by: java.lang.IllegalArgumentException: Self-suppression not permitted
    at java.lang.Throwable.addSuppressed (Throwable.java:1072)
    at com.example.App.main (App.java:25)
    at org.codehaus.mojo.exec.ExecJavaMojo$1.run (ExecJavaMojo.java:279)
    at java.lang.Thread.run (Thread.java:833)
Caused by: java.lang.NullPointerException: Cannot invoke "java.sql.ResultSet.next()" because "result" is null
    at com.example.App.main (App.java:22)
    at org.codehaus.mojo.exec.ExecJavaMojo$1.run (ExecJavaMojo.java:279)
    at java.lang.Thread.run (Thread.java:833)

[AndSDev]

I updated the example:
statement.getResultSet() can be null (from the specs), but statement.executeQuery() cannot be null.

[gotson]

Thanks, i can reproduce in a unit test.

[github-actions]

🎉 This issue has been resolved in 3.42.0.1 (Release Notes)