Derby
  1. Derby
  2. DERBY-4240

An index cause SQL ORDER BY can't return correct result

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Duplicate
    • Affects Version/s: 10.4.2.0
    • Fix Version/s: None
    • Component/s: SQL
    • Labels:
      None
    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Wrong query result

      Description

      Following snippet is a SQL example program. It can reproduce a database issue.

      DROP TABLE test1;
      DROP TABLE test2;
      CREATE TABLE test1 (id BIGINT NOT NULL, name VARCHAR(255), PRIMARY KEY (id));
      CREATE TABLE test2 (entity_id BIGINT, rel_id BIGINT);
      CREATE INDEX idx_test2 ON test2 (entity_id);
      INSERT INTO test1 (id, name) VALUES (102, 'Tom');
      INSERT INTO test1 (id, name) VALUES (1, null);
      INSERT INTO test1 (id, name) VALUES (103, 'Jerry');
      INSERT INTO test1 (id, name) VALUES (101, 'Pupy');
      INSERT INTO test2 (entity_id, rel_id) VALUES (1, 102);
      INSERT INTO test2 (entity_id, rel_id) VALUES (1, 101);
      INSERT INTO test2 (entity_id, rel_id) VALUES (1, 103);
      SELECT t1.id, t1.name FROM test2 t2 INNER JOIN test1 t1 ON t2.rel_id = t1.id WHERE t2.entity_id = 1 ORDER BY t1.id ASC;

      The expected result should be
      ID NAME
      --------------------------
      101 Pupy
      102 Tom
      103 Jerry

      When running the program, I got below result.
      ID NAME
      --------------------------
      102 Tom
      101 Pupy
      103 Jerry

      The result is obviously wrong. Using ORDER BY ASC does not get expected result. I found ORDER BY DESC works fine.
      Note: there is an index (idx_test2). This index affects the SQL query. If the index is dropped, ORDER BY ASC can return correct result..

        Issue Links

          Activity

            People

            • Assignee:
              Mamta A. Satoor
              Reporter:
              Simon Meng
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development