[
https://issues.apache.org/jira/browse/DERBY-6408?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13912933#comment-13912933
]
Knut Anders Hatlen commented on DERBY-6408:
-------------------------------------------
I think there are at least two problems that need to be fixed:
1) SubqueryNode.preprocess() wraps some subqueries in an IsNullNode to avoid
NULLs. It does that with NOT EXISTS subqueries, but not with plain EXISTS
subqueries. It should probably do that with EXISTS too.
2) SubqueryList.preprocess() calls preprocess() on all its SubqueryNodes, but
it does not update the list with the values returned by
SubqueryNode.preprocess(), so the IsNullNode added in (1) will be lost. This
part of the problem probably only affects VALUES statements, since
SubqueryList.preprocess() is only called from RowResultSetNode.preprocess().
> 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
>
> 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)
