Обсуждение: Possible regression: setNull() usage changed from 7.4 to 8.0 and up

In the process of upgrading an app, I came across a change in behavior
of PreparedStatement.setNull(). The behavior of the driver for 7.3 and
7.4 is consistent, a call to:
  stmt.setNull(1,java.sql.Types.NULL);
succeeds. However, in 8.0 and up (including the 8.2 dev driver), this
call fails with a "Could not determine data type" error.

The following calls also fail:
  stmt.setNull(1,java.sql.Types.OTHER);
  stmt.setObject(1,null);
  stmt.setObject(1,null,java.sql.Types.NULL);
  stmt.setObject(1,null,java.sql.Types.OTHER);

Changelog for Version dev401 (2005-07-24):
 * Improve null handling. Allow setNull(1, Types.XXX) for ARRAY, NULL,
DISTINCT, and STRUCT by mapping them to the unknown oid.

Changelog for Version 8.0-310 (2005-02-02):
 * Implement the Describe Statement protocol message. Reallow untyped
nulls and get the backend to resolve the type for us (if necessary).

Changelog for Version 8.0beta1-308 (2004-11-09):
 * Disallow these cases as we have insufficient type information:
setNull(i,Types.OTHER), setObject(i,null),
setObject(i,null,Types.OTHER).

Similar thread on the mailing list archives:
http://archives.postgresql.org/pgsql-jdbc/2005-02/msg00144.php

JDBC Drivers tested:
  pg73jdbc3.jar
  pg74.216.jdbc3.jar
  postgresql-8.1-315.jdbc3.jar
  postgresql-8.2dev-501.jdbc3.jar

Server Version:
  PostgreSQL 8.1.3

Stack Trace (using postgresql-8.1-315.jdbc3.jar):
Exception in thread "main" org.postgresql.util.PSQLException: ERROR:
could not determine data type of parameter $1
        at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(
QueryExecutorImpl.java:1512)
        at
org.postgresql.core.v3.QueryExecutorImpl.processResults(
QueryExecutorImpl.java:1297)
        at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:
188)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(
AbstractJdbc2Statement.java:437)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(
AbstractJdbc2Statement.java:353)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(
AbstractJdbc2Statement.java:257)
        at JDBCTest.main(JDBCTest.java:20)

Test Case:
createTestTable.sql
===================
create table test_null (test_null integer);

JDBCTest.java
=============
import java.sql.*;

public class JDBCTest {
public static void main(String[] args) throws Exception {
String url = "jdbc:postgresql://***/***";
String uname = "***";
String pword = "***";

Class.forName("org.postgresql.Driver");
Connection conn = DriverManager.getConnection(url,uname,pword);

PreparedStatement st = conn.prepareStatement("select count(*) from
test_null where ? is null");
st.setNull(1,java.sql.Types.NULL);    // Fails
//st.setNull(1,java.sql.Types.OTHER);   // Fails
//st.setNull(1,java.sql.Types.INTEGER); // Works
//st.setObject(1,null);                 // Fails
//st.setObject(1,null,java.sql.Types.NULL);   // Fails
//st.setObject(1,null,java.sql.Types.OTHER);  // Fails
//st.setString(1,null);                 // Works
ResultSet rs = st.executeQuery();
while(rs.next()) {
System.out.print("Column 1 returned ");
System.out.println(rs.getString(1));
}
rs.close();
st.close();
conn.close();
}
}

On Thu, 13 Apr 2006, Jeff Hubbach wrote:

> In the process of upgrading an app, I came across a change in behavior
> of PreparedStatement.setNull(). The behavior of the driver for 7.3 and
> 7.4 is consistent, a call to:
>  stmt.setNull(1,java.sql.Types.NULL);
> succeeds. However, in 8.0 and up (including the 8.2 dev driver), this
> call fails with a "Could not determine data type" error.
>
> PreparedStatement st = conn.prepareStatement("select count(*) from
> test_null where ? is null");
> st.setNull(1,java.sql.Types.NULL);    // Fails
> //st.setNull(1,java.sql.Types.INTEGER); // Works
>

This is an expected change when the driver was modified to use server side
prepared statements instead of just interpolating text values into the
query string.  When given a null value, the driver cannot try to infer any
type information about it and must let the server determine what the type
is.  Your example is a situation where the server cannot possible do so.
If you had written "WHERE intcol = ?", then it could infer that the
parameter should be an integer.  For a situation like "? is null" you must
provide the server with the parameter type (integer is a real type,
Types.NULL, Types.OTHER are not).  The driver cannot pick an arbitrary
type because if the server infers a different type then an
appropriate cast must exist or the query will bail out.

The server generally does a reasonable job of inferring types, the example
you've shown is an awfully contrived one, why would you need the server to
tell you if a value was null?

Kris Jurka

On 4/13/06 at 7:07 PM, books@ejurka.com (Kris Jurka) wrote:

>On Thu, 13 Apr 2006, Jeff Hubbach wrote:
>
>> In the process of upgrading an app, I came across a change in
behavior
>> of PreparedStatement.setNull(). The behavior of the driver for 7.3
and
>> 7.4 is consistent, a call to:
>>  stmt.setNull(1,java.sql.Types.NULL);
>> succeeds. However, in 8.0 and up (including the 8.2 dev driver), this
>> call fails with a "Could not determine data type" error.
>>
>> PreparedStatement st = conn.prepareStatement("select count(*) from
>> test_null where ? is null");
>> st.setNull(1,java.sql.Types.NULL);    // Fails
>> //st.setNull(1,java.sql.Types.INTEGER); // Works
>>
>
>This is an expected change when the driver was modified to use server
side
>prepared statements instead of just interpolating text values into the
>query string.  When given a null value, the driver cannot try to infer
any
>type information about it and must let the server determine what the
type
>is.  Your example is a situation where the server cannot possible do
so.
>If you had written "WHERE intcol = ?", then it could infer that the
>parameter should be an integer.  For a situation like "? is null" you
must
>provide the server with the parameter type (integer is a real type,
>Types.NULL, Types.OTHER are not).  The driver cannot pick an arbitrary
>type because if the server infers a different type then an
>appropriate cast must exist or the query will bail out.
>
>The server generally does a reasonable job of inferring types, the
example
>you've shown is an awfully contrived one, why would you need the server
to
>tell you if a value was null?
>
>Kris Jurka

Kris,

Thanks for your reply. Yes, the example I posted is a simplified test
case to duplicate the error I was receiving in some other query. The
query is checking for duplicate usernames, and we use the same query
whether a user is being edited or not. We pass in a String username for
parameter 1 and an Integer userid (that could be null, in the case of an
Add instead of an Edit) for parameters 2 and 3.

select count(*) from users where usernam = ? and (? is null or userid !=
?)

If it's an Add, and we pass in 'blah' and null, the error message I
posted about is returned. If it's an edit, then the userid of the user
being edited is excluded from the check by virtue of the (? is null or
userid != ?) section.

It makes complete sense what you're saying. I'm assuming that it was the
8.0 JDBC driver that changed the behavior of PreparedStatement to use
server-side prepared statements instead of string replacement?

Something I successfully tried on the server was:
PREPARE test_null (anyelement) AS SELECT COUNT(*) FROM test_null WHERE
$1 IS NULL;

However I couldn't find, and don't think there is, a java.sql.Types
value that corresponds to the postgresql anyelement type.

The whole application was written around the idea that a query like this
works. If there is any way to work around this, I'd love to hear it. Is
there any way to force a PreparedStatement to use the old 7.4 string
replacement method instead of the server-side prepared statement?

Or, alternately, do you see anything wrong with the following:

  select count(*) from users where usernam = ? and
  (?::integer is null or userid != ?)

  stmt.setString(1,'blah');
  stmt.setNull(2,java.sql.Types.NULL);
  stmt.setNull(3,java.sql.Types.NULL);

This code gives the server the type information it needs by doing an
explicit cast. It seems to work, but was wondering if there are any
problems you see with it (or if you have a better/cleaner way to go
about it).

Thanks again,
-- Jeff Hubbach

Hi, Jeff,

Jeff Hubbach wrote:

>   select count(*) from users where usernam = ? and
>   (?::integer is null or userid != ?)
>
>   stmt.setString(1,'blah');
>   stmt.setNull(2,java.sql.Types.NULL);
>   stmt.setNull(3,java.sql.Types.NULL);
>
> This code gives the server the type information it needs by doing an
> explicit cast. It seems to work, but was wondering if there are any
> problems you see with it (or if you have a better/cleaner way to go
> about it).

The correct way, IMHO, would be to use two different prepared
statements, one with the userid, and another one without.

HTH,
Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org