Postgres Pro
Downloads
Home   >   mailing lists

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

From David G. Johnston
Subject Re: stringtype=unspecified is null check problem
Date
Msg-id CAKFQuwZrn0F9RyM=WL9Z=pioGX5pwH1+mbBe-jGNnjy_J2E+Jw@mail.gmail.com
Whole thread Raw
In response to AW: stringtype=unspecified is null check problem  (Martin Handsteiner <martin.handsteiner@sibvisions.com>)
Responses Re: stringtype=unspecified is null check problem
List pgsql-jdbc
Tree view
On Wed, Jan 11, 2023 at 2:48 AM Martin Handsteiner <martin.handsteiner@sibvisions.com> wrote:

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 …)


The equality operator forces both sides of it to be of the same type.  Since the unquoted number 1 is a typed integer that fixes the null to be an integer.  Likewise, since both the single-quoted A and null both end up being interpreted as text that is what you get.  The "is null" test, unlike the equals operator, does not force any particular concrete data type, hence the error.  You've asked the driver to not specify a concrete type when sending text-like content and the server, respecting that, realizes it cannot infer one either, and bam!

SELECT 1 where 1=?; and then using Types.VARCHAR is wrong on its face.  The fact is stringtype lets you write bad code and get away with it in some limited cases.  But as you've seen sometimes it doesn't and you have to just go and write good code.  Good, in this case, meaning being explicit and accurate about the data types the query involves.

I'll agree this maybe isn't the best UX, but the few complainers are also not stepping up and proposing a comprehensive fix.  Call it a bug if it makes you feel better but sometimes non-critical bugs don't get fixed; and given the long history of this behavior, and complaints about it, I'm not hopeful a change is going to happen.

I will say that a nearby thread on the server lists is discussing why null defaults to be considered text; and to maybe change that.  I suppose the question here, for the server, is why it cannot just choose text instead of producing "could not determine data type of parameter".  Turning an error into minimally functioning behavior seems like a reasonable consideration.

David J.

pgsql-jdbc by date:

Previous
From: Dave Cramer
Date:
Subject: Re: stringtype=unspecified is null check problem
Next
From: Tom Lane
Date:
Subject: Re: stringtype=unspecified is null check problem