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

Incorrect evaluation of logical expressions in CASE

    XMLWordPrintableJSON

    Details

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

        1. d6363-1a.diff
          6 kB
          Knut Anders Hatlen
        2. ac4d3680a5x0141x78ccx0cb1x000006c347101.java
          3 kB
          Knut Anders Hatlen

          Issue Links

            Activity

              People

              • Assignee:
                knutanders Knut Anders Hatlen
                Reporter:
                grzegorz.zur Grzegorz ┼╗ur
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: