[DERBY-6363] Incorrect evaluation of logical expressions in CASE

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.4.1.3, 10.8.3.0, 10.10.1.1
• Fix Version/s: 10.8.3.3, 10.9.2.2, 10.10.2.0, 10.11.1.1
• Component/s: SQL
• Bug behavior facts: Wrong query result
• Assignee: Knut Anders Hatlen
• Reporter: Grzegorz Żur
• Created: 02/Oct/13 10:27
• Updated: 21/Jan/15 00:23
• Resolved: 07/Oct/13 10:48

Description

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 - -

Issue Links

• is duplicated by: DERBY-6364 Incorrect evaluation of logical expressions in CASE
• is duplicated by: DERBY-6365 Incorrect evaluation of logical expressions in CASE
• is duplicated by: DERBY-6366 Incorrect evaluation of logical expressions in CASE