Re: BUG #14923: Java driver - PreparedStatement setNull in SELECT query

Поиск
Список
Период
Сортировка
От Francisco Olarte
Тема Re: BUG #14923: Java driver - PreparedStatement setNull in SELECT query
Дата
Msg-id CA+bJJbxPqGkqKjyVN7+b=E583WkXrkjT=bnJzSQcgqQKMj5CLg@mail.gmail.com
обсуждение исходный текст
Ответ на BUG #14923: Java driver - PreparedStatement setNull in SELECT query  (jarda.urik@gmail.com)
Список pgsql-bugs
On Fri, Nov 24, 2017 at 11:24 AM,  <jarda.urik@gmail.com> wrote:
...
> SELECT * FROM my_table WHERE int_val = ? ...
> Now, I would like to set NULL value to my variables - I have tried
> ps.setNull(1, Types.INTEGER); // ps is instance of PreparedStatement
.. Does not work.
...
> Although I have rows matching the conditions ( NULL in column 'int_val'), I
> have not received any records..

This is not a bug, is aproblem you have, try the general lists for advice.

In SQL null does not equal null:

test=> select null=null, (null=null) is null, null is null;?column? | ?column? | ?column?
----------+----------+----------         | t        | t
(1 row)


You have answered yourself below,

> The problem is (I think) in query produced by the Statement, looks like:
>
> System.out.println(ps.toString());
> // --> SELECT * FROM my_table WHERE int_val = NULL ...
> But the query should look like:
>
> "SELECT * FROM my_table WHERE int_val IS NULL ..." - this query works

No, it does not. IF you were using an ORM or some fancy mapping layer
it MAY be. If you are using JDBC, it must send the queries as you give
it to it. It does not know if you are using the null=null is null
thing ( in fact null<> has the same problem:

test=> select null<>null, (null<>null) is null;?column? | ?column?
----------+----------         | t
(1 row)

) purposefully.

> I need to use dynamically create PreparedStatements which will contain NULL
> values, so I cannot somehow easily bypass this.

Yes you can, but is not easy.

> I have tried creating connection without the HikariCP with the same result,
> so I thing the problem is in the postgresql driver? Or am I doing something
> wrong?

You are doing something wrong.

>
> UPDATE:
> Based on answer from @Vao Tsun I have set transform_null_equals = on in
> postgresql.conf , which started changing val = null --> val is null in
> 'simple' Statements, but NOT in PreparedStatements..

And know your DB is not standars compliant. Read the end (19.13.2) of
https://www.postgresql.org/docs/10/static/runtime-config-compatible.html#RUNTIME-CONFIG-COMPATIBLE-CLIENTS

Note, you may be better served but a non-compliant database, nothing
wrong with it if you know what you are doing.

To summarize, not a bug.

Francisco Olarte.


В списке pgsql-bugs по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: BUG #14924: Subquery in VALUES inside recursive CTE
Следующее
От: Tom Lane
Дата:
Сообщение: Re: BUG #14924: Subquery in VALUES inside recursive CTE