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-6563) NOT elimination for CASE expressions is broken
Date Mon, 05 May 2014 13:02:15 GMT

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

Knut Anders Hatlen commented on DERBY-6563:

ConditionalNode.eliminateNots() eliminates NOTs by swapping the THEN and ELSE branch of the
top-level ConditionalNode.

For two-legged CASE expressions (such as the one in the bug description), it means that

NOT CASE WHEN expr1 THEN expr2 ELSE expr3 END

is rewritten to

CASE WHEN expr1 THEN expr3 ELSE expr2 END

For three-legged (or more) expressions,

NOT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END


CASE WHEN expr1 THEN CASE WHEN expr3 THEN expr4 ELSE expr5 END ELSE expr2 END

In neither of the cases, the rewritten expression is equivalent to the original expression.

I believe a more correct rewrite would be

CASE WHEN expr1 THEN NOT expr2 else NOT expr3 END

in the two-legged case, and similar for the three-or-more-legged case.

> NOT elimination for CASE expressions is broken
> ----------------------------------------------
>                 Key: DERBY-6563
>                 URL: https://issues.apache.org/jira/browse/DERBY-6563
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions:
>            Reporter: Knut Anders Hatlen
>            Assignee: Knut Anders Hatlen
> NOT elimination for CASE expressions seems to be broken. Take this example:
> {noformat}
> ij> select * from sysibm.sysdummy1 where not ( case when ibmreqd = 'Y' then true else
true end );
> IBM&
> ----
> Y   
> 1 row selected
> {noformat}
> Both branches of the CASE expression evaluate to TRUE, so one would expect the predicate
"not ( case ... )" to evaluate to FALSE, and the query should return an empty result.

This message was sent by Atlassian JIRA

View raw message