stringtype=unspecified is null check problem

From: Martin Handsteiner <martin(dot)handsteiner(at)sibvisions(dot)com>
To: "pgsql-jdbc(at)lists(dot)postgresql(dot)org" <pgsql-jdbc(at)lists(dot)postgresql(dot)org>
Subject: stringtype=unspecified is null check problem
Date: 2023-01-10 17:28:13
Message-ID: VI1PR1001MB1423CDC3E737B4763436B730E8FF9@VI1PR1001MB1423.EURPRD10.PROD.OUTLOOK.COM
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

If a bind parameter is checked for null, ERROR: could not determine data type of parameter $1 occurs.
Simple testcase:
Connection conn = DriverManager.getConnection("jdbc:postgresql://192.168.1.201:5432/testdb?stringtype=unspecified", "test", "test");
PreparedStatement stat = conn.prepareStatement("select 1 where ? is null");
stat.setNull(1, Types.VARCHAR);
stat.executeQuery();
-> Exception

For checking the parameter for null, the data type should not be relevant. At least TEXT or VARCHAR could be implicitly used.

There is a mailing post, that says, that stat.setNull(1, Types.VARCHAR); would strict set the value as VARCHAR.
https://www.postgresql.org/message-id/Pine.BSO.4.64.0702141439480.24142%40leary.csoft.net
But this seams not to happen in my case.

I have also testet to bind the value with:
stat.setNull(1, Types.NULL); or stat.setNull(1, Types.OTHER); or stat.setString(1, null);
Nothing works in this case.

As far as I understand, the jdbc driver sends the value without type information to the database. The database throws the exception.
So maybe this is a database problem.
The database can assume, that in case of ? is null it doesn't matter, which type the sent null is. So even a UNKOWN null is null or not null.

Strange is, that the following statement will work. So in this case null is unknown, but mapped.
conn.prepareStatement("select 1 where coalesce(?, null) is null");

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message David G. Johnston 2023-01-10 17:44:25 stringtype=unspecified is null check problem
Previous Message Dave Cramer 2023-01-09 15:17:01 [pgjdbc/pgjdbc] ae2afe: add redirects (#2718)