Derby
  1. Derby
  2. DERBY-3926

Incorrect ORDER BY caused by index

    Details

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

      Description

      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):
      ID |VALUE |VALUE
      ----------------------------------------------
      2147483653 |000002 |21857
      2147483654 |000003 |21857
      4294967297 |000001 |21857

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

      This is the definition:
      CREATE TABLE table1 (id BIGINT NOT NULL, PRIMARY KEY(id));
      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. d3926_repro.sql
        0.6 kB
        A B
      2. derby-3926_10.3_mergeattempt.txt
        53 kB
        Kathey Marsden
      3. DERBY3926_notforcheckin_patch1_051109_diff.txt
        8 kB
        Mamta A. Satoor
      4. DERBY3926_notforcheckin_patch1_051109_stat.txt
        0.7 kB
        Mamta A. Satoor
      5. DERBY3926_notforcheckin_patch2_051109_diff.txt
        9 kB
        Mamta A. Satoor
      6. DERBY3926_patch3_051509_diff.txt
        8 kB
        Mamta A. Satoor
      7. DERBY3926_patch3_051509_stat.txt
        0.3 kB
        Mamta A. Satoor
      8. DERBY3926_patch4_051519_diff.txt
        12 kB
        Mamta A. Satoor
      9. DERBY3926_patch4_051519_stat.txt
        0.3 kB
        Mamta A. Satoor
      10. DERBY3926_patch5_052709_diff.txt
        15 kB
        Mamta A. Satoor
      11. DERBY3926_patch5_052709_stat.txt
        0.5 kB
        Mamta A. Satoor
      12. DERBY3926_patch6_060309_diff.txt
        54 kB
        Mamta A. Satoor
      13. DERBY3926_patch6_060309_stat.txt
        0.9 kB
        Mamta A. Satoor
      14. derby-reproduce.zip
        15 kB
        Tars Joris
      15. script3.sql
        392 kB
        Mamta A. Satoor
      16. script3WithUserFriendlyIndexNames.sql
        392 kB
        Mamta A. Satoor
      17. test-script.zip
        13 kB
        Tars Joris
      18. wisconsin_10.1_result.zip
        149 kB
        Kathey Marsden

        Issue Links

          Activity

          Gavin made changes -
          Workflow jira [ 12445195 ] Default workflow, editable Closed status [ 12802600 ]
          Dag H. Wanvik made changes -
          Link This issue relates to DERBY-6148 [ DERBY-6148 ]
          Dag H. Wanvik made changes -
          Affects Version/s 10.2.2.1 [ 12312251 ]
          Affects Version/s 10.2.3.0 [ 12312215 ]
          Mike Matrigali made changes -
          Status Reopened [ 4 ] Closed [ 6 ]
          Resolution Fixed [ 1 ]
          Rick Hillegas made changes -
          Resolution Fixed [ 1 ]
          Status Resolved [ 5 ] Reopened [ 4 ]
          Kathey Marsden made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Resolution Fixed [ 1 ]
          Kathey Marsden made changes -
          Fix Version/s 10.1.3.3 [ 12313478 ]
          Fix Version/s 10.2.2.1 [ 12312251 ]
          Knut Anders Hatlen made changes -
          Link This issue relates to DERBY-4331 [ DERBY-4331 ]
          Kathey Marsden made changes -
          Fix Version/s 10.5.2.0 [ 12314116 ]
          Fix Version/s 10.5.1.2 [ 12313870 ]
          Kristian Waagan made changes -
          Fix Version/s 10.6.0.0 [ 12313727 ]
          Fix Version/s 11.0.0.0 [ 12312083 ]
          Urgency Normal
          Dag H. Wanvik made changes -
          Issue & fix info [High Value Fix]
          Kathey Marsden made changes -
          Attachment wisconsin_10.1_result.zip [ 12411542 ]
          Kathey Marsden made changes -
          Fix Version/s 10.3.3.1 [ 12313143 ]
          Environment Checked into 10.3. This will still go to 10.2 and 10.1
          Kathey Marsden made changes -
          Link This issue depends upon DERBY-3997 [ DERBY-3997 ]
          Kathey Marsden made changes -
          Attachment derby-3926_10.3_mergeattempt.txt [ 12411115 ]
          Mamta A. Satoor made changes -
          Fix Version/s 10.4.2.1 [ 12313401 ]
          Fix Version/s 10.5.1.2 [ 12313870 ]
          Fix Version/s 11.0.0.0 [ 12312083 ]
          Mamta A. Satoor made changes -
          Attachment DERBY3926_patch6_060309_diff.txt [ 12409853 ]
          Attachment DERBY3926_patch6_060309_stat.txt [ 12409854 ]
          Mamta A. Satoor made changes -
          Attachment DERBY3926_patch5_052709_diff.txt [ 12409186 ]
          Attachment DERBY3926_patch5_052709_stat.txt [ 12409187 ]
          Mike Matrigali made changes -
          Link This issue relates to DERBY-4240 [ DERBY-4240 ]
          Mamta A. Satoor made changes -
          Attachment DERBY3926_patch4_051519_diff.txt [ 12408558 ]
          Attachment DERBY3926_patch4_051519_stat.txt [ 12408559 ]
          Mamta A. Satoor made changes -
          Attachment DERBY3926_patch3_051509_diff.txt [ 12408262 ]
          Attachment DERBY3926_patch3_051509_stat.txt [ 12408263 ]
          Mamta A. Satoor made changes -
          Mamta A. Satoor made changes -
          A B made changes -
          Attachment d3926_repro.sql [ 12407375 ]
          Mamta A. Satoor made changes -
          Attachment script3WithUserFriendlyIndexNames.sql [ 12406808 ]
          Mamta A. Satoor made changes -
          Attachment script3.sql [ 12406701 ]
          Tars Joris made changes -
          Attachment test-script.zip [ 12406494 ]
          Mamta A. Satoor made changes -
          Assignee Mamta A. Satoor [ mamtas ]
          Kathey Marsden made changes -
          Affects Version/s 10.1.3.3 [ 12313478 ]
          Affects Version/s 10.2.3.0 [ 12312215 ]
          Affects Version/s 10.3.3.1 [ 12313143 ]
          Mamta A. Satoor made changes -
          Link This issue is related to DERBY-4105 [ DERBY-4105 ]
          Kathey Marsden made changes -
          Derby Categories [Wrong query result] [High Value Fix, Wrong query result]
          Tars Joris made changes -
          Field Original Value New Value
          Attachment derby-reproduce.zip [ 12392976 ]
          Tars Joris created issue -

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development