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

Derby 10.3.X ignores ORDER BY DESC when target column has an index and is used in an OR clause or an IN list.

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.3.1.4, 10.3.2.1
    • Fix Version/s: 10.3.3.0, 10.4.1.3
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Rational Application Developer 7.0.0.2 (Eclipse 3.2.2), J2RE 1.5.0 IBM J9 2.3 Windows XP
    • Urgency:
      Urgent
    • Bug behavior facts:
      Regression

      Description

      Running the following produces the error seen in Derby 10.3.X but not in 10.2.X nor in 10.1.X.
      Don't know if this related to DERBY-3231.

      First query is incorrectly sorted whereas the second one is okay when there is an index on the table.
      If the table is not indexed, the sort works correctly in DESC order.
      ------
      create table CHEESE (
      CHEESE_CODE VARCHAR(5),
      CHEESE_NAME VARCHAR(20),
      CHEESE_COST DECIMAL(7,4)
      );

      create index cheese_index on CHEESE (CHEESE_CODE DESC, CHEESE_NAME DESC, CHEESE_COST DESC);

      INSERT INTO CHEESE (
      CHEESE_CODE,
      CHEESE_NAME,
      CHEESE_COST)
      VALUES ('00000', 'GOUDA', 001.1234),
      ('00000', 'EDAM', 002.1111),
      ('54321', 'EDAM', 008.5646),
      ('12345', 'GORGONZOLA', 888.2309),
      ('AAAAA', 'EDAM', 999.8888),
      ('54321', 'MUENSTER', 077.9545);

      SELECT * FROM CHEESE
      WHERE (CHEESE_CODE='00000' OR CHEESE_CODE='54321') AND CHEESE_NAME='EDAM'
      ORDER BY CHEESE_CODE DESC, CHEESE_NAME DESC, CHEESE_COST DESC;

      SELECT * FROM CHEESE
      WHERE (CHEESE_CODE='AAAAA' OR CHEESE_CODE='54321') AND CHEESE_NAME='EDAM'
      ORDER BY CHEESE_CODE DESC, CHEESE_NAME DESC, CHEESE_COST DESC;

        Attachments

        1. cheese2.sql
          2 kB
          Ajay Bhala
        2. d3279_10_3_merge.patch
          44 kB
          A B
        3. d3279_ix2brnode_v1.patch
          18 kB
          A B
        4. d3279_v1.patch
          29 kB
          A B
        5. releaseNote.html
          7 kB
          A B

          Issue Links

            Activity

              People

              • Assignee:
                army A B
                Reporter:
                abhala Ajay Bhala
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: