Bug 362414

Comment 1 Bernard Missing name 2011-10-29 22:42:11 EDT

Created attachment 206173 [details]
NetBeans Project Testcase in zip file

Comment 2 Tom Ware 2011-11-01 14:16:40 EDT

From discussion on the mailing list:

This issue appears on databases that do not support SQL that includes "null = null"

e.g. 
select * from auction_bid where (null is null)

Two such databases are JavaDB and PostGreSQL

The suggestion is that in the case like the one in the bug:

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

If the parameter passed in as :lastName is null, we should optimize out SQL for the ":lastName IS NULL" because it will always return true.

Comment 3 Tom Ware 2011-11-01 14:18:41 EDT

The only workaround I can think of is to run two queries:

SELECT a FROM Author a WHERE :lastName IS NULL

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

Comment 4 Tom Ware 2011-11-09 13:45:27 EST

Setting target and priority.  See the following page for the meanings of these fields:

http://wiki.eclipse.org/EclipseLink/Development/Bugs/Guidelines

Community: Please vote for this bug if it is important to you.  Votes are one of the main criteria we use to determine which bugs to fix next.

Comment 5 Bernard Missing name 2011-11-22 19:44:55 EST

I tried to verify comment #2 but failed to do so with Postgresql:

A native query run directly (without JDBC) works 100% fine:

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

The following Java code works fine as well:

TypedQuery<CustomerOrder> query = em.createNamedQuery(CustomerOrder.GET_BY_NAME, CustomerOrder.class);
	Parameter<String> nameParameter = new Parameter<String>(){
		public String getName() {
			return "name";
		}
		public Integer getPosition() {
			return null;
		}
		public Class<String> getParameterType() {
			return String.class;
		}
	};
	query.setParameter(nameParameter, (String) null);
	List <CustomerOrder> resultList = query.getResultList();

but this one fails:

	TypedQuery<CustomerOrder> query = em.createNamedQuery(CustomerOrder.GET_EQUAL_ID, CustomerOrder.class);
	Parameter<Integer> nameParameter = new Parameter<Integer>(){
		public String getName() {
			return "regionId";
		}
		public Integer getPosition() {
			return null;
		}
		public Class<Integer> getParameterType() {
			return Integer.class;
		}
	};
	query.setParameter(nameParameter, (Integer) null);
	List <CustomerOrder> resultList = query.getResultList();
	
Internal Exception: org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = character varying

This is even more illogical because the type is provided with getParameterType().

It appears that this is an EclipseLink / JDBC / JDBC driver issue where low level Java code cannot figure out what data type to use for null (which does not have any benefit).

In this case, this is counterproductive nonsense that an application developer cannot resolve.

In the JPQL, the parameter is guarded correctly with "IS NULL", and native SQL does NOT have a problem with "NULL IS NULL" in exactly the same spot.

Some may argue that this use of "IS NULL" in SQL is short-circuiting and should therefore be avoided but that certainly does not apply for a host variable (parameter).

Any preference for not doing this is probably a conclusion from other bad practice or the consequence of experiences with this bug or similar bugs (circular arguments).

Comment 6 A Lazarus 2011-11-22 19:55:35 EST

(In reply to comment #2)
> From discussion on the mailing list:
> 
> This issue appears on databases that do not support SQL that includes "null =
> null"
> 
> e.g. 
> select * from auction_bid where (null is null)

This is not correct. Postgresql has no problem with NULL=NULL except that, as per the SQL standard, the result of the comparison is NULL, not TRUE. The results, therefore, will not be what some users (those who use MS Access which does evaluate this as boolean TRUE) expect. There is a configuration flag that allows Postgres users to mimic Access's standard-violating behavior.

Comment 7 Tom Ware 2011-11-23 11:24:08 EST

It looks like there may be separate issues for Derby and for PostGreSQL.

Since this issue was initially filed with a Derby Test case, I am converting it to a Derby Issue.

Please file an bug for the PostGreSQL issue including the exception you see on PostGres - which I expect will be different for the derby exception.

Comment 8 Bernard Missing name 2012-02-17 03:57:43 EST

Even if I wanted to, I could not file a bug against the derby database driver based on the information I have.

This may not even be a driver issue because the following JDBC code succeeds unexpectedly with derby version 10.8.2.2:

String sql = "SELECT ID, NAME, region_id FROM CUSTOMERORDER WHERE ((? IS NULL) OR (region_id = ?))";
PreparedStatement pStmt = conn.prepareStatement(sql);
Integer regionId = null;
pStmt.setObject(1, regionId);
pStmt.setObject(2, regionId);
ResultSet result = pStmt.executeQuery();

Success!

This should fail in the case where the jdbc driver would insist on type information with pStmt.setNull(1, Types.INTEGER);

At least it fails with a slightly older derby version 10.6.2.1

Could someone please explain this discrepancy?

It there anything that I could do to help resolve this issue? Why is the EclipseLink JPA testcase not succeeding while raw JDBC succeeds?

Comment 9 Bernard Missing name 2012-02-18 18:23:07 EST

JDBC succeeeds now because Derby has addressed the null parameter issue since db-derby-10.7.1.1. See

https://issues.apache.org/jira/browse/DERBY-1938
"Add support for setObject(<arg>, null)"

Why is the test still failing in EclipseLink with this derby version? Where is the next bottleneck?

Comment 10 Tom Ware 2012-02-21 09:13:05 EST

What error are you getting now?

Comment 11 Bernard Missing name 2012-02-21 12:03:24 EST

The error is:

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "NULL" at line 1, column 55.
Error Code: -1
Call: SELECT ID, NAME, region_id FROM CUSTOMERORDER WHERE ((NULL IS NULL) OR (region_id = NULL))
Query: ReadAllQuery(name="CustomerOrder.getEqualId" referenceClass=CustomerOrder sql="SELECT ID, NAME, region_id FROM CUSTOMERORDER WHERE ((? IS NULL) OR (region_id = ?))")

This is perplexing because the equivalent JDBC statement succeeds:

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

but at the same time if I execute SELECT ID, NAME, region_id FROM CUSTOMERORDER WHERE ((NULL IS NULL) OR (region_id = NULL)) in the NetBeans database explorer then I get the same error.

Perhaps EclipseLink is not using PreparedStatement in this case.

Comment 12 Tom Ware 2012-02-22 09:19:56 EST

Workaround:  User our parameter binding query hint.  Here's an example from our tests that works:


        em.createQuery("select a from Address a where :city IS NULL or a.city = :city").setHint(QueryHints.BIND_PARAMETERS, true).setParameter("city", null).getResultList();

Comment 13 Bernard Missing name 2012-04-08 21:27:52 EDT

Tom,

Thanks for the workaround. Meanwhile there is a verified issue for Derby which aims to address this:

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

It doesn't seem to be moving, perhaps some votes will help?

I have tried Postgresql JDBC where this was acknowledged with some ideas to fix it:

http://archives.postgresql.org/pgsql-jdbc/2011-11/msg00110.php

and

http://archives.postgresql.org/pgsql-jdbc/2011-12/msg00017.php

All this is a kind of a pain because the JDBC spec keeps things fairly open for bad drivers and databases while the majority of databases and drivers have had the root cause fixed long ago.

Comment 14 Marko Kotar 2012-04-25 16:05:28 EDT

I had same problem with NULL IS NULL expression.
I found out that workaround 

setHint(QueryHints.BIND_PARAMETERS, true) 

works only with simple data types objects which are not references.

This query works:
"SELECT material FROM Material material WHERE (:qParam1 IS NULL or material.materialGroup.id=:qParam1)"

setParameter("qParam1",<id of materialGroup>);

This one fails:
"SELECT material FROM Material material WHERE (:qParam1 IS NULL or material.materialGroup=:qParam1)"

setParameter("qParam1",<materialGroup object>);



I hope I saved a day for someone who finds this post. :)

Comment 15 Bernard Missing name 2012-04-25 17:03:11 EDT

Thanks Marko. Please see

"Add Support for NULL IS NULL"
https://issues.apache.org/jira/browse/DERBY-5728

Comment 16 Eclipse Webmaster 2022-06-09 10:31:33 EDT

The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink