Re: [eclipselink-users] Querying on null parameter values

[James Sutherland <jamesssss@xxxxxxxxx>]

One solution is to set the query to not prepare itself using the query hint,

"eclipselink.prepare"="false"

This should cause IS NULL to be used.


Phillip Ross-4 wrote:
> 
> Thanks Bernard.
> 
> I did some cursory searching for the issue on google before sending my
> message to the mailing list and I was super surprised that I didnt get
> any quick hits with people running into the same thing.  I do know
> that databases not supporting =NULL and making you do IS NULL is
> literally a decades old thing that devs run into.  And I thought for
> sure EclipseLink handled the automatic creation of =NULL versus IS
> NULL predicates depending on the parameter values, but now seeing the
> bug you filed and Tom's response, I guess I just have false memories
> :)
> 
> Your solution for writing the query to check for null then use an OR
> operator is a neat workaround.  I'll play with it but I usually avoid
> relying on inherent behaviors such as query expression evaluation
> short circuiting.  I've since just rewritten code to use the criteria
> API and evaluate the parameters as I'm building the expression using
> the equals or isNull methods depending on the parameter value.  I'm
> already suspecting there are going to be performance problems due to
> using these null checks for locating records due to a lot of databases
> not being able to utilize indexes to find null values :(
> 
> I voted for the issue in bugzilla.  Thanks again for the response!
> - Phillip
> 
> 
> On Fri, Nov 4, 2011 at 2:10 AM,  <bht@xxxxxxxxxxxxx> wrote:
>> Phillip,
>>
>> You are one of probably thousands of developers who encounter this
>> type of issue. Your chances to get the someColumnName = null test to
>> work are fairly slim, because SQL dictates that that "IS NULL" must be
>> coded instead for null values.
>>
>> There are varying outcomes with "=" even with the same database but
>> different drivers such as in Sybase native versus Sypacce over ODBC.
>> Alternatively, you can code in your named query:
>>
>> select object(p) from Purchase p where :detail1 is null or p.detail1 =
>> :detail1
>>
>> This is efficient, elegant, compact and correct if one accepts that
>> the database is smart enough to avoid the second test if the first
>> (null is null) is true.
>>
>> Unfortunately, even this fails with EclipseLink 2.3.0 on postgresql or
>> derby.
>>
>> This issue has just been discussed under the subject "Cannot pass null
>> parameter"
>>
>> Please refer to
>>
>> http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples
>>
>> and
>>
>> https://bugs.eclipse.org/bugs/show_bug.cgi?id=362414
>>
>> I would suggest to:
>>
>> 1) study the testcase and file a bug with the providers of these
>> stupid SQL database engines
>>
>> 2) vote for the EclipseLink issue
>>
>> Kind Regards,
>>
>> Bernard
>>
>>
>> On Thu, 3 Nov 2011 16:33:31 -0400, you wrote:
>>
>>>Hi all... I'm looking for a little assistance on using named queries
>>>using parameters that may have null values.  I'm positive this worked
>>>before but it's not working for me now and I'm wondering if I'm
>>>running into incorrect usage scenario or a bug.
>>>
>>>Basically I have an entity with some fields that are nullable and a
>>>named query that uses them in the query.  Example code:
>>>
>>>query annotation:
>>>@NamedQuery(name="Purchase.findByDetails", query="select p from
>>>Purchase p where p.detail1 = :detail1 and p.detail2 = :detail2 and
>>>p.detail3 = :detail3")
>>>
>>>The property declarations in the Purchase entity:
>>>private String detail1;
>>>private String detail2;
>>>private String detail3;
>>>
>>>Example query code:
>>>query = entityManager.createNamedQuery("Purchase.findByDetails");
>>>query.setParameter("detail1", detail1);
>>>query.setParameter("detail2", detail2);
>>>query.setParameter("detail3", detail3);
>>>purchases = query.getResultList();
>>>
>>>
>>>So when the content of detail1, 2, 3 parameters are null, the query
>>>fails to return records where the values of detail1/2/3 are null.
>>>This is surprising as I was positive that the provider would
>>>automatically handle the translation to database platform specific
>>>clauses that would use "is null" predicates if necessary.  This is NOT
>>>what happens with eclipselink 2.3.0 on postgresql or derby.  It simply
>>>doesnt return the results.
>>>
>>>Am I just doing this wrong and really have to dynamically build the
>>>JPQL query to use JPQL "is null" with null checks on the parameter
>>>values and all of that?  Or am I running into a bug?
>>>
>>>Thanks
>>>- Phillip
>>>_______________________________________________
>>>eclipselink-users mailing list
>>>eclipselink-users@xxxxxxxxxxx
>>>https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>>
>> _______________________________________________
>> eclipselink-users mailing list
>> eclipselink-users@xxxxxxxxxxx
>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
> 
> 


-----
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland 
http://www.eclipse.org/eclipselink/
 EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
TopLink 
Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink , 
http://wiki.oracle.com/page/TopLink TopLink 
Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink , 
http://www.nabble.com/EclipseLink-f26430.html EclipseLink 
Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence 
Blog:  http://java-persistence-performance.blogspot.com/ Java Persistence
Performance 
-- 
View this message in context: http://old.nabble.com/Querying-on-null-parameter-values-tp32776036p32811897.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.