[DERBY-6408] EXISTS returns NULL instead of FALSE

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.7.1.1, 10.8.3.3, 10.9.2.2, 10.10.1.1
• Fix Version/s: 10.10.2.0, 10.11.1.1
• Component/s: SQL
• Urgency: Normal
• Issue & fix info: Repro attached
• Bug behavior facts: Deviation from standard, Wrong query result
• Assignee: Knut Anders Hatlen
• Reporter: Knut Anders Hatlen
• Created: 08/Nov/13 09:27
• Updated: 21/Jan/15 00:23
• Resolved: 03/Mar/14 12:50

Description

The reference manual topic on Boolean expressions - http://db.apache.org/derby/docs/10.10/ref/rrefsqlj23075.html - says that EXISTS should return FALSE if the subquery returns no rows. In reality, it returns NULL:

ij> create table t(x int);
0 rows inserted/updated/deleted
ij> values exists(select * from t);
1

---

NULL

1 row selected

SQL:2011, part 2, 8.10 <exists predicate> also says that FALSE is the correct result if the cardinality is 0.

Issue Links

• is duplicated by: DERBY-6409 Wrong result from quantified comparison
• is related to: DERBY-6577 Quantified comparison returns wrong result in CASE, COALESCE, IN and BETWEEN
• is related to: DERBY-6481 Derby 10.10 backport issue (winter/spring 2014)
• relates to: DERBY-6409 Wrong result from quantified comparison