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

Incorrect evaluation of logical expressions in CASE

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 10.4.1.3, 10.8.3.0, 10.10.1.1
    • 10.8.3.3, 10.9.2.2, 10.10.2.0, 10.11.1.1
    • SQL
    • None
    • 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. ac4d3680a5x0141x78ccx0cb1x000006c347101.java
          3 kB
          Knut Anders Hatlen
        2. d6363-1a.diff
          6 kB
          Knut Anders Hatlen

        Issue Links

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved: