[
https://issues.apache.org/jira/browse/DERBY-6408?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Knut Anders Hatlen updated DERBY-6408:
--------------------------------------
Attachment: d6408-1a.diff
Attaching d6408-1a.diff with a fix for this bug. It makes the following changes:
RowResultSetNode.java:
Make the preprocess() method invoke preprocess() on the result column list
instead of the subquery list. It will still end up preprocessing the
subqueries, but now the IsNullNode added by SubqueryNode.preprocess() will not
be lost.
SubqueryList.java:
Remove the broken (and now unused) preprocess() method so that no one gets
tempted to use it.
SubqueryNode.java:
Make sure that EXISTS subqueries are wrapped in an IsNullNode, not only NOT
EXISTS subqueries.
ValueNode.java:
Enhance the genIsNullTree() method so that it can produce both IS NULL and IS
NOT NULL nodes.
master/schemas.out:
Update canon which expected EXISTS to return NULL.
lang/ExistsWithSubqueriesTest.java:
Add test case that verifies the fix.
lang/GrantRevokeDDLTest.java:
Update assert that expects NULL instead of FALSE. (It was when writing this
test case the bug was originally discovered.)
> EXISTS returns NULL instead of FALSE
> ------------------------------------
>
> Key: DERBY-6408
> URL: https://issues.apache.org/jira/browse/DERBY-6408
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.7.1.1, 10.8.3.3, 10.9.2.2, 10.10.1.1
> Reporter: Knut Anders Hatlen
> Assignee: Knut Anders Hatlen
> Attachments: d6408-1a.diff
>
>
> 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.
--
This message was sent by Atlassian JIRA
(v6.1.5#6160)
