[ https://issues.apache.org/jira/browse/DERBY-6563?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13989500#comment-13989500 ]
Knut Anders Hatlen commented on DERBY-6563: ------------------------------------------- It also affects NULLIF, since ConditionalNode is used by both CASE and NULLIF. For example, these two queries are equivalent, but they return different results: {noformat} ij> select * from (values false) v(b) where not(nullif(b, true)); B ----- 0 rows selected ij> select * from (values false) v(b) where nullif(b, true) = false; B ----- false 1 row selected {noformat} It is the second one that's correct. > 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: 10.10.2.0 > 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 (v6.2#6252)