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.07021414
> 39480.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.