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-6363) Incorrect evaluation of logical expressions in CASE
Date Wed, 02 Oct 2013 12:33:24 GMT

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

Knut Anders Hatlen commented on DERBY-6363:
-------------------------------------------

It looks like something goes wrong during conversion of the OR expression to an IN list in
OrNode.preprocess(), and {{( b = 'a' or b = 'b' )}} gets incorrectly converted to {{( b IN
('a') )}}.

I think the problem may be that OrNode.preprocess() expects the OrNode to be on conjunctive
normal form. Currently, only WHERE, HAVING and ON clauses are converted to CNF, as far as
I can see. (And the expression indeed evaluates to the correct value if it is moved from the
SELECT list to a WHERE clause.)

> Incorrect evaluation of logical expressions in CASE
> ---------------------------------------------------
>
>                 Key: DERBY-6363
>                 URL: https://issues.apache.org/jira/browse/DERBY-6363
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.4.1.3, 10.8.3.0, 10.10.1.1
>            Reporter: Grzegorz ┼╗ur
>         Attachments: ac4d3680a5x0141x78ccx0cb1x000006c347101.java
>
>
> Logical expressions with AND and OR operators inside WHEN inside CASE expression nested
in SELECT part of statement are wrongly evaluated.
> Evaluation results depends on position of OR subexpression. If OR is placed on left side
of AND it is evaluated incorrectly.
> Following code shows the error.
> create table t ( a int, b char );
> insert into t values (1, 'a');
> insert into t values (2, 'b');
> insert into t values (3, 'a');
> insert into t values (4, 'b');
> insert into t values (5, 'a');
> insert into t values (6, 'b');
> select
> a,
> b,
> case
>     when (( b = 'a' or b = 'b' ) and a < 4) then 'x'
>     else '-'
> end,
> case
>     when (a < 4 and ( b = 'a' or b = 'b' )) then 'y'
>     else '-'
> end
> from t;
> Actual result:
> 1 a x y
> 2 b - y
> 3 a x y
> 4 b - -
> 5 a - -
> 6 b - -
> Expected result
> 1 a x y
> 2 b x y
> 3 a x y
> 4 b - -
> 5 a - -
> 6 b - -



--
This message was sent by Atlassian JIRA
(v6.1#6144)

Mime
View raw message