[
https://issues.apache.org/jira/browse/DERBY-5728?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16985402#comment-16985402
]
Bernard commented on DERBY-5728:
--------------------------------
Richard I appreciate your comments where you highlight the dilemma.
Non-native JPQL does not have a CAST because it aims to be platform agnostic.
Consequently, in pure JPQL, without using the criteria API, a programmer would
need to write different queries depending on the value of a parameter. This
leads to a high number of combinations of queries to write and maintain,
depending on the number of parameters. To maintain such an incredibly costly
approach clearly defeats the purpose of programming. Therefore, it is not
practical to do.
Because static JPQL as described works with other databases, e.g. with SQL
Server, a competitor, I would think that it is worth our consideration to
implement a solution, even while it could be in conflict with the pure SQL
grammar definitions as you describe. I would not want Derby to lag behind.
The solution would allow us to write a single static JPQL query that serves an
extremely common use case. Consider a database search entry form with multiple
optional text fields. I cannot remember many applications that do not have at
least one of these.
So if you have the opportunity to resolve this as requested, I am sure that you
would do the programmer community, and Derby a great service.
> Add Support for NULL IS NULL
> ----------------------------
>
> Key: DERBY-5728
> URL: https://issues.apache.org/jira/browse/DERBY-5728
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.8.2.2
> Environment: Windows XP
> java version "1.6.0_31"
> Java(TM) SE Runtime Environment (build 1.6.0_31-b05)
> Java HotSpot(TM) Client VM (build 20.6-b01, mixed mode, sharing)
> Reporter: bernard
> Priority: Critical
> Labels: derby_triage10_10
> Attachments: Bug5728.java, NullParameterEclipseLinkDerbyMaven.zip,
> NullParameterHibernateDerbyMaven.zip
>
>
> The following query fails:
> SELECT ID FROM CUSTOMER WHERE ((NULL IS NULL) OR (NAME = NULL))
> Why this is an issue?
> At least two major Java ORMs, Hibernate JPA and EclipseLink JPA have isues
> with generating SQL for trivial JPQL queries such as:
> select object(c) from Customer c where ((name: is null) or (c.name = name:))
> where name: is a parameter
> For why this is a fundamental issue, please see a minimalistic JPQL query at
> http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples
> Part of this has already been resolved by issue "Add support for
> setObject(<arg>, null)"
> https://issues.apache.org/jira/browse/DERBY-1938
> Please see EclipseLink and Hibernate test cases for verification.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
