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

[Phillip Ross <phillip.w.g.ross@xxxxxxxxx>]

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
>