db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6408) EXISTS returns NULL instead of FALSE
Date Wed, 26 Feb 2014 14:05:20 GMT

    [ 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)

Mime
View raw message