Bug: Cannot pass null in Parameter in Query for ISNULL - Mailing list pgsql-jdbc

Hi,

Native PostgreSQL has no problem with queries like:

select id from author a where null is null or a.name = null

However the JDBC driver fails to process such a query with a
parameter:

ERROR: could not determine data type of parameter $1

The failure reproduces with a very simple and common JPQL query shown
at

http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples

SELECT a FROM Author a WHERE :lastName IS NULL OR LOWER(a.lastName) =
:lastName

While the final pass criterion is a JPA testcase, the error can also
be reproduced more directly with raw JDBC.

It appears to be unproductive to test for the "type" of null of a host
variable in "WHERE ? IS NULL" or "WHERE :lastName IS NULL"

Other drivers for databases e.g. MS SQL Server, Oracle, Sybase, mySQL
achieve the expected results.

To solve this defect would be quite rewarding because while the defect
appears to be perplexingly simple, the typical use cases are quite
prominient, useful and powerful.

Testcase JDBC (I can provide a zip file with both JPA and JBC cases if
required):

DROP TABLE REGION
CREATE TABLE REGION (ID INTEGER NOT NULL, PRIMARY KEY (ID))
DROP TABLE CUSTOMERORDER
CREATE TABLE CUSTOMERORDER (ID INTEGER NOT NULL, NAME VARCHAR(255),
region_id INTEGER, PRIMARY KEY (ID))
ALTER TABLE CUSTOMERORDER ADD CONSTRAINT FK_CUSTOMERORDER_region_id
FOREIGN KEY (region_id) REFERENCES REGION (ID)

package main;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

public class Jdbc {

    private static final boolean NULL_WORKAROUND = false;

    public static void main(String[] args){
        try {
            DriverManager.registerDriver(new org.postgresql.Driver());
            String url = "jdbc:postgresql://localhost:5432/test";
            String user ="postgres";
            String password="passme";
            Connection conn = DriverManager.getConnection(url, user,
password);
            String sql = "SELECT ID, NAME, region_id FROM
CUSTOMERORDER WHERE ((? IS NULL) OR (region_id = ?))";
            PreparedStatement pStmt = conn.prepareStatement(sql);
            Integer regionId = null;
            if(regionId == null && NULL_WORKAROUND){
                pStmt.setNull(1, Types.INTEGER);
                pStmt.setNull(2, Types.INTEGER);
            }else{
                pStmt.setObject(1, regionId);
                pStmt.setObject(2, regionId);
            }
            ResultSet result = pStmt.executeQuery();
            while(result.next()){
                int id = result.getInt(1);
                String name = result.getString(2);
                Integer regionIdResult = (Integer)result.getObject(3);

            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }


    }

}