[DERBY-5728] Add Support for NULL IS NULL

• Type: Improvement
• Status: Open
• Priority: Critical
• Resolution: Unresolved
• Affects Version/s: 10.8.2.2
• Component/s: SQL
• Labels: derby_triage10_10
• 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)
• Urgency: Urgent
• Assignee: Unassigned
• Reporter: bernard
• Created: 25/Apr/12 20:38
• Updated: 30/Nov/19 17:07

Description

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.

Issue Links

• is related to: DERBY-2507 Query compiler assumes bad parameter type for ? is null expression
• is related to: DERBY-5629 Queries with guarded null Parameter fail