[
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: 10.4.1.3, 10.8.3.0, 10.10.1.1
> 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
(v6.1#6144)
