Re: stringtype=unspecified is null check problem - Mailing list pgsql-jdbc

There are 3 use cases, where I would need one setting, that always ensures, that null can be bound…

(setNull(1, <setting>) and stringtype=<setting>)

 

select 1 where 1=?          -- setNull(1, Types.VARCHAR) and stringtype=unspecified

select 1 where 'A'=?       -- setNull(1, Types.VARCHAR) and stringtype doesn’t matter

select 1 where ? is null  -- setNull(1, Types.VARCHAR) and stringtype=VARCHAR

 

That there is no way to binding null in a simple way is a bug for me, because the following will work, and the database has also to decide, how to map null:

 

select 1 where 1=null     -- now the database converts null to a number

select 1 where 'A'=null  -- now the database converts null to a varchar

select 1 where null is null – now the database doesn’t care… so everthing works.

 

The question is, what is the difference between the two, and why should the caller parse the statement, if he uses jdbc. These examples are easy, but in real world, in 1=? the 1 could also be a sub select (select max(id) from …)

 

Von: David G. Johnston <david.g.johnston@gmail.com>
Gesendet: Dienstag, 10. Jänner 2023 18:44
An: Martin Handsteiner <martin.handsteiner@sibvisions.com>
Cc: pgsql-jdbc@lists.postgresql.org
Betreff: stringtype=unspecified is null check problem

 

On Tuesday, January 10, 2023, Martin Handsteiner <martin.handsteiner@sibvisions.com> wrote:

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");

 

This is indeed how all of this works in the current design.  I suggest you add a cast to the input parameter in the query.  Or choose a different value for stringtype…

 

David J.