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] [Updated] (DERBY-6363) Incorrect evaluation of logical expressions in CASE
Date Thu, 03 Oct 2013 11:25:42 GMT

     [ https://issues.apache.org/jira/browse/DERBY-6363?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel

Knut Anders Hatlen updated DERBY-6363:

    Attachment: d6363-1a.diff

The attached patch, d6363-1a.diff, disables the IN list transformation if the OR node is not
on conjunctive normal form. All the expressions that could be used as qualifiers in a multi-probe
scan will be on CNF at this point in the code, and those expressions are the only ones that
would benefit from such a transformation, as far as I know, so skipping the transformation
in those cases should not hurt performance.

The current code, without the the patch, partially checks that the OR chain is on CNF by checking
that each left operand is either an equals operator or an IN operator. (This is a stricter
condition than CNF, which only requires that the left operand is either an AND node on CNF
or some node that is not an OR node.) It does however not check that the right operand satisfies
the CNF requirements. Instead, it silently assumes that the right operand is either an OR
node or a BooleanConstantNode whose value is false.

The patch fixes this logic by verifying that the terminating right operand in the chain indeed
is Boolean false. If it is not, the conversion to an IN list is skipped. In the examples above
where the incorrect conversion happens, the terminating right operand is a node representing
{{(b = 'b')}}. Since the original logic assumed that the chain would be terminated by a dummy
{{FALSE}} value, which could safely be dropped, it would ignore the {{(b = 'b')}} part of
the expression when it created the IN list. Now it will check explicitly that the terminating
node is safe to drop, and only do the rewrite if it is safe.

The patch also fixes a small bug in OrNode.verifyChangeToCNF() which prevented it from detecting
that the right side of the tree was not well-formed. This code is only invoked in debug builds.
The check in OrNode.verifyChangeToCNF() now matches the check in AndNode.verifyChangeToCNF().

All regression tests passed with the patch.

> 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:,,
>            Reporter: Grzegorz ┼╗ur
>            Assignee: Knut Anders Hatlen
>         Attachments: ac4d3680a5x0141x78ccx0cb1x000006c347101.java, d6363-1a.diff
> 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

View raw message