Add support for setObject(<arg>, null)

[bernard01]

This has been implemented by drivers and/or engines of almost all other databases I am aware of, and a description and discussion can be found at:

https://issues.apache.org/jira/browse/DERBY-1938

From the practical perspective, JPA does not provide us with any choice because it does not support type casting.

JPQL like the following from http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples should just work:

SELECT a FROM Author a WHERE :lastName IS NULL OR LOWER(a.lastName) = :lastName

On JDBC level:

SELECT ID, NAME, region_id FROM CUSTOMERORDER WHERE ((? IS NULL) OR (region_id = ?))

[davecramer]

At this point since this is not how the spec is written I don't have a good solution for this

[bernard01]

The spec contains language that is a legacy fallback not forward looking.

Are we waiting for the JDBC standard to catch up and remove this? Most likely nobody will ever change this part.

Meanwhile the industry has moved on with data binding technologies and with newer standards such as JPA with JPQL that do not function with such legacy constraints.

For these technologies to work for most databases, JDBC drivers have been upgraded accordingly.

With Postgresql, we are forced to emit query language dynamically with all possible permutations due to potential null values.

This is error-prone, it hurts and it is no longer state of the art.

Perhaps this is addressed more easily in combination with a back-end change.

[ringerc]

Bernard: While it feels quite opaque, the JDBC working group is open to contributions and outside opinion. I strongly recommend that you contact them about this and propose it as an improvement for JDBC 4.2, which is due in the Java SE 8 time-frame. It's on JCP 2.7 so there's no public -users mailing list, but you can and should contact the spec lead Lance Andersen, who's said he wants to hear from people with concerns about the API.

If writing to the working group you should show specific test cases against specific other drivers that you know implement this functionality, and include a strong argument showing why it won't break any existing working or spec-compliant code. Convince them it's a good idea. Feel free to cc the PgJDBC mailing list.

If the change is accepted by the JDBC working group and it can be implemented against older versions without breaking anything it'd be good to support it.

Re Pg specifically, remember that Pg has user-defined functions with type-based overloading and user-defined operators. A non-type-qualified NULL can sometimes mean Pg can't choose between two different functions or operators. You'll see errors like:

ERROR: function some_func(unknown) is not unique
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

That said, most of the time it'll just work, and it'd be nice to support it if the spec doesn't explicitly prohibit it. There are some usability challenges introduced though, like the fact that creating an overload of a function will result in calls with NULL arguments that used to work suddenly failing.

I suspect the bigger problem would be that the server expects to plan statements at PREPARE time, planning depends on being able to resolve functions and operators, and to do that it needs to know types. At least at the SQL level Pg can't currently prepare parameterised statements with unknown data types:

regress=# PREPARE justatest(unknown) AS SELECT $1;
ERROR:  could not determine data type of parameter $1

... so PgJDBC would have to do its own type inference. Yikes.

Tom Lane made some changes in 9.2 that might be useful here, allowing prepared statements to be re-planned for every execution in some situations. Maybe that'd permit prepared statements to be bound with unknown types that'd be inferred at execution time with some more work. It's certainly something the backend needs to support, though, not something that can easily be hacked into PgJDBC.

All in all, this needs a lot more thinking through, and almost certainly requires backend changes. I don't see any way I could usefully push this forward right now.

[abstratt]

Isn't this an issue with the JPA implementation and the JPA spec?

The JPA implementation should be calling setNull(index, type) or setObject(int parameterIndex, Object x, int sqlType), instead of setObject(index, object).

However, the JPA spec does not currently provide a mechanism for users to provide a type hint in the case of a null value. And that seems the crux of the issue here.

[davecramer]

will not fix