[jira] [Commented] (DERBY-7164) Wrong result from EXISTS aggregate subquery that counts zero rows

[Richard N. Hillegas (Jira)]

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

Richard N. Hillegas commented on DERBY-7164:
--------------------------------------------

Agreed. That is my understanding of the EXISTS predicate. Other variations of 
this mistake are:

{noformat}
CONNECT 'jdbc:derby:memory:db;create=true';

-- correct
SELECT COUNT(*)
  FROM SYSIBM.SYSDUMMY1
  WHERE FALSE
;

-- incorrect. should be true since the query returns one row
VALUES
(
  EXISTS
  (
    SELECT COUNT(*)
    FROM SYSIBM.SYSDUMMY1
    WHERE FALSE
  )
)
;

-- incorrect for the same reason
SELECT EXISTS
(
  SELECT COUNT(*)
  FROM SYSIBM.SYSDUMMY1
  WHERE FALSE
)
FROM SYSIBM.SYSDUMMY1;
{noformat}

> Wrong result from EXISTS aggregate subquery that counts zero rows
> -----------------------------------------------------------------
>
>                 Key: DERBY-7164
>                 URL: https://issues.apache.org/jira/browse/DERBY-7164
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.17.1.0
>            Reporter: Lukas Eder
>            Priority: Major
>
> This query should return true as the aggregate subquery always produces 
> exactly 1 row, so the EXISTS predicate is trivially always true:
> {code:sql}
> select exists (
>   select count(*)
>   from SYSIBM.SYSDUMMY1
>   where false
> )
> from SYSIBM.SYSDUMMY1;
> {code}
> But it returns false, probably due to a wrong optimisation



--
This message was sent by Atlassian Jira
(v8.20.10#820010)