PreparedStatement setNull in SELECT query

user_2310954 0
Viewed 1344

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.

jdbc postgresql
3 Answers

It is meant behaviour that comparison x = null is equal to null (no matter what x is equal to). Basically for SQL NULL is unknown, not the actual value... To bypass it you can set transform_null_equals to on or true. Please checkout docs:

https://www.postgresql.org/docs/current/static/functions-comparison.html

Some applications might expect that expression = NULL returns true if expression evaluates to the null value. It is highly recommended that these applications be modified to comply with the SQL standard. However, if that cannot be done the transform_null_equals configuration variable is available. If it is enabled, PostgreSQL will convert x = NULL clauses to x IS NULL.

user_53159740

I have just found a solution, which works the same for "values" and "NULLs" by using IS NOT DISTINCT FROM instead of =.

More on postgresql wiki

user_23109540

It is important to recognize that null is not a value with SQL. It is encoding the logical notion of "unknown". This is why null = var results in false always, even for cases where var has a value of null. So even if if you are replacing the value of your variable (aka ? in your case) with a value of null, the result be definition must not be what you do expect as long as SQL standard is complied with.

Now there are some databases around that try to outsmart SQL standard by assuming a column value of null should be taken as a programming language null (nil, undef or whatever is used for that purpose). This creates some convenience for the unwary programmer, but in the long run causes grieve as soon as you need a true distinction between a SQL null and a programming language null.

Nevertheless, for ease of porting from such databases to PostgresQL (or simple for ease of lazy programming) you may resort to setting transform_null_equals.

BUT, you are using prepared statements. As such, prepared statements are converted to query plan once and such query plan needs to be valid for all potential values of the variables used in the prepared statement query. Now, a VAR is null is fundamentally different from a VAR = ?. So there is no chance for the query parser, query optimizer or even query execution engine to dynamically rewrite the (already prepared) query based on the actual parameter values passed in.

From this, you should take the recommendation serious that is given with the documentation of transform_null_equals and change your code to use VAR is null when a null value is to be searched for and a VAR = ? for other cases.

user_59821100
Related