Re: stringtype=unspecified is null check problem

On Wed, Jan 11, 2023 at 2:48 AM Martin Handsteiner <
martin(dot)handsteiner(at)sibvisions(dot)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.