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

The following bug has been logged on the website:

Bug reference:      14923
Logged by:          Jaroslav Urik
Email address:      jarda.urik@gmail.com
PostgreSQL version: 10.1
Operating system:   Windows 10 / Ubuntu server 16.04
Description:

I have already described it on StackOverflow (
https://stackoverflow.com/questions/47340176/preparedstatement-setnull-in-select-query
) so, the following will be copy/paste from there:


I am using Postgresql together with HikariCP and my query is something
like

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
try (ResultSet rs = ps.executeQuery()) {
... // get result from resultset
}

Although I have rows matching the conditions ( NULL in column 'int_val'), I
have not received any records..

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

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

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?

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..

To summarize:

try (ResultSet rs = st.executeQuery(SELECT * FROM my_table WHERE int_val =
NULL)){ 
  // query is replaced to '.. int_val IS NULL ..' and gets correct result
}

ps.setNull(1, Types.INTEGER);
try (ResultSet rs = ps.executeQuery()) {
  // Does not get replaced and does not get any result
}
I am using JVM version 1.8.0_121, the latest postgres driver (42.1.4), but I
have also tried older driver (9.4.1212). Database version -- PostgreSQL
9.6.2, compiled by Visual C++ build 1800, 64-bit.

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.