public inbox for [email protected]  

help / color / mirror / Atom feed

From: Martin Handsteiner <[email protected]>
To: [email protected] <[email protected]>
Subject: Re: stringtype=unspecified is null check problem
Date: Tue, 10 Jan 2023 18:53:15 +0000
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKFQuwZA+9+FNovGXA6moj+sDzR6ShyzzP3H77m-05n3dXDv-w@mail.gmail.com>
References: <VI1PR1001MB1423CDC3E737B4763436B730E8FF9@VI1PR1001MB1423.EURPRD10.PROD.OUTLOOK.COM>
	<CAKFQuwZA+9+FNovGXA6moj+sDzR6ShyzzP3H77m-05n3dXDv-w@mail.gmail.com>

The problem is about generic queries, where the user writes something like:


… where (:SEARCH is null or table.id<http://or table.id; = :SEARCH)





The parameters are replaced by ? and the value is bound to the statement.





The stringtype=unspecified solves the problem for table.id<http://for table.id; = :SEARCH, but causes the problem for :SEARCH is null.

So with stringtype=VARCHAR :SEARCH is null is working, but table.id<http://but table.id; = :SEARCH fails.


A developer is of course able to write a cast(? as varchar), normal users maybe not, and it breaks database independency.


The database parses the statement and knowns therefore the use case. It would be the central place. Maybe there is also a solution in the jdbc driver?





Statement parsing, is complex, so it would be fine, to find any solution without parsing the statement.





Am 10. Jan. 2023, um 18:44, "David G. Johnston" <[email protected]<mailto:[email protected]>> schrieb:
On Tuesday, January 10, 2023, Martin Handsteiner <[email protected]<mailto:[email protected]>> 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<http://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.

view thread (14+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: stringtype=unspecified is null check problem
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox