Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-6364

Incorrect evaluation of logical expressions in CASE

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 10.4.1.3, 10.8.3.0, 10.10.1.1
    • Fix Version/s: None
    • Component/s: SQL
    • Labels:
      None
    • Bug behavior facts:
      Wrong query result

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

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                grzegorz.zur Grzegorz ┼╗ur
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: