Oracle join with null values

CloverDX Platform
1

What does Clover pass to the database when using equality (=) with null values in the clover fields? I am trying to issue a join in which field1 and field2 can have a value or could be null. In my case, field1 is a decimal and field2 is a date. I am trying to issue the query with the following format:
select * from table1
where (field1 = ? or field1 is null)
and (field2 = ? or field2 is null);

This works fine in sqlplus but does not work in clover. I do not get an error, however, I do not return any joined rows. I know I could use nvl however that requires that I maintain duplicate fields with default values. I do not want to default the value of field1 and field2 because they will get inserted into the database in a different step and they are nullable fields. Is there a way to see the exact query clover executes against the database? I am running clover via the eclipse GUI.

2

The problem looks to be with whatever is being substituted for the ? character in the join key. If I issue the query:
select * from table where (field1 is null or field1 = 0), I will get the records where field 1 is null. However if I issue the query:
select * from table where (field1 is null or field1 = ?) and the join key field1 is null then I don’t get the records where field1 is null. So what is the value of the join key field1 when it is nullable and has not been set with a value?

3

Hello, problem seems to be in caching database reply - null can’t be set as key. Try to set “Cache size” to 0 on DBJoin component.