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.
