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.
