Re: Strange NullPointerException in result set checkColumnIndex

Hi,

I've been battling with a strange NullPointerException throwing out of PostgreSQL JDBC. Hope some genius can give me some help here. Thanks in advance!

 This is roughly how the bug occurs

1. the code gets a connection from the apache-commons.dbcp BasicDataSource
2. it creates a PrepareStatement and executes a SELECT against some tables with UUID type column (the DB has implicit uuid to string cast via CREATE CAST(TEXT AS UUID) WITH INOUT AS IMPLICIT and CREATE CAST(VARCHAR AS UUID) WITH INOUT AS IMPLICIT)
   
3. the user gives a string "123213131321213123112" whicj is not a valid UUID format. the code set it to the statement via setString(1, "123213131321213123112")
4. as expected, a SQLException is thrown: Caused by: org.postgresql.util.PSQLException: ERROR: invalid input syntax for uuid: "123213131321213123112"
5. the code then call connection.close() properly
6. repeat the above at least twice, serially or in parallel. same thing
7. now repeat the above with VALID UUID as input
8. correctly, the SQLException is NOT thrown
9. then code check result.next(), it returns true as expected
10. the code then calls result.getString(1)
11. BANG! a NullPointerException is thrown a shown below

Caused by: java.lang.NullPointerException: null
        at org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkColumnIndex(AbstractJdbc2ResultSet.java:2679) ~[postgresql-9.1-901.jdbc4.jar:na]
        at org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkResultSet(AbstractJdbc2ResultSet.java:2697) ~[postgresql-9.1-901.jdbc4.jar:na]
        at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getString(AbstractJdbc2ResultSet.java:1872) ~[postgresql-9.1-901.jdbc4.jar:na]
        at org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java:213) ~[commons-dbcp-1.4.jar:1.4]
        at org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java:213) ~[commons-dbcp-1.4.jar:1.4]
        at org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java:213) ~[commons-dbcp-1.4.jar:1.4]


Reproducibility and Environment

1. the error is reproducible readily every time on the PostgreSQL 9.2 / RHEL 6. I would tend to rule out multithread errors in the code or the dbcp pool.
2. but then I don't know why step 6 would be needed. (If I just make one request with invalid UUID and the another with valid one, the error does not occur)
3. the dbcp is configued to setTestOnBorrow(true) but forgot to setValidateQuery(). However, setting the validate query to "select 1" does not help.
4. try to force some other SQL error (like renaming the column) will NOT cause the subsequent NPE
   
5. the error never occurs in my local development environment on PostgreSQL 9.0.5 / MacOS X 10.7.5 not matter how many times I tried
6. the Postgres JDBC driver version is 9.1-901.jdbc4
7. I wrote a simple test program imitating the same steps but it causes no error in those environments (my version has the real longer query the one shown here.)
   

Attachment: my test codes attempting to reproduce the problem

import org.apache.commons.dbcp.BasicDataSource;
import org.testng.annotations.Test;

import java.sql.*;

@Test(groups = {"auto"})
public class PostgressJDBCUUIDTest {
    Connection dbc;
    static BasicDataSource dataSource;
    static {
        dataSource = new BasicDataSource();
        dataSource.setDefaultAutoCommit(false);
        dataSource.setDefaultReadOnly(false);
        dataSource.setDefaultTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        dataSource.setDriverClassName("org.postgresql.Driver");
        dataSource.setMaxActive(1);
        dataSource.setMaxIdle(1);
        dataSource.setMaxWait(10000); // 10 seconds max wait time
        dataSource.setMinEvictableIdleTimeMillis(1 * 60 * 1000);
        dataSource.setMinIdle(1);
        dataSource.setPassword("");
        dataSource.setPoolPreparedStatements(true);
        dataSource.setTestOnBorrow(true); // this to make the connection safer, bookserve does this too
        dataSource.setTestWhileIdle(false); // then no need to check while idle
//        dataSource.setValidationQuery("select 1"); // doesn't seem to make a difference
        dataSource.setUrl("jdbc:postgresql:blurb_development");
        dataSource.setUsername("postgres");
    }

    @Test(enabled = true)
    public void testOtherSQLError() throws Exception {
        final String uuid = "927b8a04-c319-4139-b985-79b1cbc43871";
        ResultSet result;
        PreparedStatement stmt;
        for (int i = 0; i < 10; i++) {
            dbc = dataSource.getConnection();
            try {
                stmt = dbc.prepareStatement("select * from table_with_uuid where no_such_col = ?");
                stmt.setString(1, "zzzzzzzzz");
                result = stmt.executeQuery();
                if (result.next()) {
                    String s = result.getString(1);
                    System.out.println("result: " + s);
                } else {
                    System.out.println("no result");
                }
                result.close();
                stmt.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
            dbc.close();
        }

        System.out.println("----------------------------------------------------");
        dbc = dataSource.getConnection();
        stmt = dbc.prepareStatement("select * from table_with_uuid where uuid_col = ?");
        stmt.setString(1, uuid);
        result = stmt.executeQuery();
        if (result.next()) {
            String s = result.getString(1);
            System.out.println("result: " + s);
        } else {
            System.out.println("no result");
        }
        result.close();
        stmt.close();
        dbc.close();
    }

    @Test
    public void testUUIDSQLError() throws Exception {
        System.out.println("***************************** ^^^^^^^^^^^^^^^^^^^^^^^^^^^^");
        ResultSet result;
        PreparedStatement stmt;
        for (int i = 0; i < 5; i++) {
            dbc = dataSource.getConnection();
            try {
                String uuid = "92f0edb5-d155-4cf7-8af3-ef3194393ca5";
                stmt = dbc.prepareStatement("select uuid_col from table_with_uuid where uuid_col = ?");
                stmt.setString(1, uuid);
                result = stmt.executeQuery();
                if (result.next()) {
                    String s = result.getString(1);
                    System.out.println("result: " + s);
                } else {
                    System.out.println("no result");
                }
                result.close();
                stmt.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
            dbc.close();
        }

        dbc = dataSource.getConnection();
        System.out.println("----------------------------------------------------");

        final String uuid = "927b8a04-c319-4139-b985-79b1cbc43871";
        stmt = dbc.prepareStatement("select uuid_col from id2user where uuid_col = ?");
        stmt.setString(1, uuid);
        result = stmt.executeQuery();
        if (result.next()) {
            String s = result.getString(1);
            System.out.println("result: " + s);
        } else {
            System.out.println("no result");
        }
        result.close();
        stmt.close();
        dbc.close();
    }
}