Re: Strange NullPointerException in result set checkColumnIndex

Sorry for the very late reply. I didn't see this in gmail. It appears gmail
is "fixing" sorting.

I am unable to recreate the problem. I presume the no_such_col does not
exist in the table?

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Fri, Jul 19, 2013 at 3:39 PM, hack bear <hackingbear(at)hotmail(dot)com> wrote:

> 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();
> }
> }
>
>