[jira] [Commented] (DERBY-5728) Add Support for NULL IS NULL
[ https://issues.apache.org/jira/browse/DERBY-5728?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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(, 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)
[jira] [Commented] (DERBY-5728) Add Support for NULL IS NULL
[
https://issues.apache.org/jira/browse/DERBY-5728?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16985388#comment-16985388
]
Richard N. Hillegas commented on DERBY-5728:
I don't see the problem as a JBDC issue. The problem is that the ORM layer is
generating illegal SQL. In Standard SQL, there is no such thing as an untyped
NULL. The left side of an IS NULL predicate must have a known type at compile
time (JDBC prepareStatement() time). The phrase
{noformat}
NULL IS NULL
{noformat}
has no meaning in Standard SQL. The phrase
{noformat}
$something IS NULL
{noformat}
has meaning only if the compiler can determine the type of $something. The type
can be determined in the following circumstances:
1) $something is an identifier (e.g., a column reference). Column types are
known.
2) $something is a literal. Literals have types.
3) $something is an expression built up out of typed components.
But in the following phrase
{noformat}
? IS NULL
{noformat}
the IS NULL predicate is being applied to an untyped value. This is a syntax
error.
I understand that this explanation doesn't help you. At the end of the day
1) The Derby guy is telling you that the ORM layer is generating illegal SQL
and that JPQL needs to support the CAST statement. The Derby guy is telling you
that the problem is in JPQL.
2) The ORM guys are telling you that other databases handle untyped NULL on the
left side of an IS NULL predicate, in a way which makes this JPQL statement
compile and behave reasonably. The ORM guys are telling you that the problem is
in Derby.
> 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(, 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)
[jira] [Commented] (DERBY-5728) Add Support for NULL IS NULL
[ https://issues.apache.org/jira/browse/DERBY-5728?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16985289#comment-16985289 ] Bernard commented on DERBY-5728: Please let me add some context. It might be helpful, or even encouraging. I am not an expert on the JDBC spec. However, during research, I found that from the very start, the JDBC setNull with sql type parameter is a fallback for legacy JDBC drivers that do not pass the JDBC Driver Test Suite. This was many years ago, but it is still relevant for the very few databases that are still in this fallback situation. Meanwhile we have moved on and high popularity of abstractions like JPA make it difficult to figure out where the origin of some of these the problem is. This issue is only one manifestation of it. If this issue gets fixed, then automatically some other legacy / maintenance issues will go away. It is a hard pill to swallow that a type parameter must be passed alongside with null. JPQL does not even have a concept for that. This is such a good example where we should follow the trend as technology has clearly moved on, leaving the legacy behind. > 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(, 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)
