[
https://issues.apache.org/jira/browse/DERBY-5728?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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(<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)
