Re: stringtype=unspecified is null check problem

Dave Cramer
www.postgres.rocks

On Wed, 11 Jan 2023 at 04:48, 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 …)
>
>
>

One thing you should be aware of is that by default jdbc uses Extended
Query whereas psql uses simple query.

see PostgreSQL: Documentation: 15: 55.1. Overview
<https://www.postgresql.org/docs/current/protocol-overview.html#PROTOCOL-QUERY-CONCEPTS>
for
details on extended query.

Simple query allows the server to infer quite a bit whereas Extended Query
is very opinionated.

Dave

> *Von:* David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
> *Gesendet:* Dienstag, 10. Jänner 2023 18:44
> *An:* Martin Handsteiner <martin(dot)handsteiner(at)sibvisions(dot)com>
> *Cc:* pgsql-jdbc(at)lists(dot)postgresql(dot)org
> *Betreff:* stringtype=unspecified is null check problem
>
>
>
> On Tuesday, January 10, 2023, Martin Handsteiner <
> martin(dot)handsteiner(at)sibvisions(dot)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.
>
>
>