[jira] [Commented] (DERBY-6408) EXISTS returns NULL instead of FALSE

["ASF subversion and git services (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-6408?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13918022#comment-13918022 ] 

ASF subversion and git services commented on DERBY-6408:
--------------------------------------------------------

Commit 1573548 from [~knutanders] in branch 'code/trunk'
[ https://svn.apache.org/r1573548 ]

DERBY-6408: EXISTS returns NULL instead of FALSE

Make sure EXISTS subqueries are wrapped in an IsNullNode to convert
NULL to a BOOLEAN. Before, only NOT EXISTS subqueries were wrapped.

Make RowResultSetNode.preprocess() invoke preprocess() on the result
columns instead of directly on the subquery nodes. Calling it directly
on the subquery nodes prevented the result columns from seeing the
added IsNullNode.

> 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.2#6252)