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

Incorrect ORDER BY caused by index


    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s:,,,
    • Component/s: SQL
    • Labels:
    • Environment:
      Checked into 10.3. This will still go to 10.2 and 10.1
    • Urgency:
    • Issue & fix info:
      High Value Fix
    • Bug behavior facts:
      Wrong query result


      I think I found a bug in Derby that is triggered by an index on a large column: VARCHAR(1024). I know it is generally not a good idea to have an index on such a large column.

      I have a table (table2) with a column "value", my query orders on this column but the result is not sorted. It is sorted if I remove the index on that column.

      The output of the attached script is as follows (results should be ordered on the middle column):
      2147483653 |000002 |21857
      2147483654 |000003 |21857
      4294967297 |000001 |21857

      While I would expect:
      4294967297 |000001 |21857
      2147483653 |000002 |21857
      2147483654 |000003 |21857

      This is the definition:
      CREATE INDEX key1 ON table1(id);
      CREATE TABLE table2 (id BIGINT NOT NULL, name VARCHAR(40) NOT NULL, value VARCHAR(1024), PRIMARY KEY(id, name));
      CREATE UNIQUE INDEX key2 ON table2(id, name);
      CREATE INDEX key3 ON table2(value);

      This is the query:
      SELECT table1.id, m0.value, m1.value
      FROM table1, table2 m0, table2 m1
      WHERE table1.id=m0.id
      AND m0.name='PageSequenceId'
      AND table1.id=m1.id
      AND m1.name='PostComponentId'
      AND m1.value='21857'
      ORDER BY m0.value;

      The bug can be reproduced by just executing the attached script with the ij-tool.
      Note that the result of the query becomes correct when enough data is changed. This prevented me from creating a smaller example.

      See the attached file "derby-reproduce.zip" for sysinfo, derby.log and script.sql.

      Michael Segel pointed out:
      "It looks like its hitting the index ordering on id,name from table 2 and is ignoring the order by clause."


        1. derby-reproduce.zip
          15 kB
          Tars Joris
        2. test-script.zip
          13 kB
          Tars Joris
        3. script3.sql
          392 kB
          Mamta A. Satoor
        4. script3WithUserFriendlyIndexNames.sql
          392 kB
          Mamta A. Satoor
        5. d3926_repro.sql
          0.6 kB
          A B
        6. DERBY3926_notforcheckin_patch1_051109_diff.txt
          8 kB
          Mamta A. Satoor
        7. DERBY3926_notforcheckin_patch1_051109_stat.txt
          0.7 kB
          Mamta A. Satoor
        8. DERBY3926_notforcheckin_patch2_051109_diff.txt
          9 kB
          Mamta A. Satoor
        9. DERBY3926_patch3_051509_diff.txt
          8 kB
          Mamta A. Satoor
        10. DERBY3926_patch3_051509_stat.txt
          0.3 kB
          Mamta A. Satoor
        11. DERBY3926_patch4_051519_diff.txt
          12 kB
          Mamta A. Satoor
        12. DERBY3926_patch4_051519_stat.txt
          0.3 kB
          Mamta A. Satoor
        13. DERBY3926_patch5_052709_diff.txt
          15 kB
          Mamta A. Satoor
        14. DERBY3926_patch5_052709_stat.txt
          0.5 kB
          Mamta A. Satoor
        15. DERBY3926_patch6_060309_diff.txt
          54 kB
          Mamta A. Satoor
        16. DERBY3926_patch6_060309_stat.txt
          0.9 kB
          Mamta A. Satoor
        17. derby-3926_10.3_mergeattempt.txt
          53 kB
          Kathey Marsden
        18. wisconsin_10.1_result.zip
          149 kB
          Kathey Marsden

          Issue Links



              • Assignee:
                mamtas Mamta A. Satoor
                tjoris Tars Joris
              • Votes:
                0 Vote for this issue
                0 Start watching this issue


                • Created: