[
https://issues.apache.org/jira/browse/DERBY-5728?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16890625#comment-16890625
]
Bernard edited comment on DERBY-5728 at 7/23/19 2:48 AM:
---------------------------------------------------------
>From what I can remember, it goes like this:
Imagine we have a client form with multiple field entries, each tied to a JPQL
fragment for that field.
Let's start with one field:
SELECT a FROM Author a WHERE :lastName IS NULL OR a.lastName = :lastName
Then take two fields:
SELECT a FROM Author a WHERE
:lastName IS NULL OR a.lastName = :lastName
and
:firstName IS NULL OR a.firstName = :firstName
If we can do this, then effectively the database ensures that, if an entry is
missing, that the corresponding fragment can be ignored because it returns
true. This is how programmers want the computer to behave. If we can't do this,
and this issue prevents this from working, then things get really complicated.
It does not work because the DB cannot handle NULL IS NULL where the first NULL
is the missing parameter from the form.
was (Author: bht):
>From what I can remember, it goes like this:
Imagine we have a client form with multiple field entries, each tied to a JPQL
fragment for that field.
Let's start with one field:
SELECT a FROM Author a WHERE :lastName IS NULL OR a.lastName = :lastName
Then take two fields:
SELECT a FROM Author a WHERE
:lastName IS NULL OR a.lastName = :lastName
and
:firstName IS NULL OR a.firstName = :firstName
If we can do this, then effectively the database ensures that, if an entry is
missing, that the corresponding fragment can be ignored because it returns
true. This is how programmers want the computer to behave. If we can't do this,
and this issue prevents this from working, then things get really complicated.
> 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: 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
(v7.6.14#76016)
