Derby
  1. Derby
  2. DERBY-4331

Join returns results in wrong order

    Details

    • Issue & fix info:
      High Value Fix, Repro attached
    • Bug behavior facts:
      Regression, Wrong query result

      Description

      In Derby 10.5.2.0, the query below started returning results in wrong order (note the ORDER BY clause). Derby 10.5.1.1 gives the expected ordering.

      ij> SELECT CS.ID
      FROM
      CHANGESETS CS, FILECHANGES FC, REPOSITORIES R, FILES F, AUTHORS A
      WHERE
      R.PATH = '/var/tmp/source5923202038296723704opengrok/mercurial' AND F.REPOSITORY = R.ID AND A.REPOSITORY = R.ID AND
      CS.REPOSITORY = R.ID AND CS.ID = FC.CHANGESET AND F.ID = FC.FILE AND
      A.ID = CS.AUTHOR AND
      EXISTS (
      SELECT 1
      FROM FILES F2
      WHERE
      F2.ID = FC.FILE AND F2.REPOSITORY = R.ID AND
      F2.PATH LIKE '/%' ESCAPE '#')
      ORDER BY CS.ID DESC;
      ID
      -----------
      1
      2
      3
      2
      2
      3

      6 rows selected

      1. derby_4331_patch_2.diff
        55 kB
        Mike Matrigali
      2. DERBY4331_additionalTests_diff.txt
        14 kB
        Mamta A. Satoor
      3. wisconsin.diff
        523 kB
        Mike Matrigali
      4. derby4331_do_not_commit.diff
        4 kB
        Mike Matrigali
      5. createDBsimplerVer2.txt
        1 kB
        Mamta A. Satoor
      6. createDBsimpler.txt
        2 kB
        Mamta A. Satoor
      7. orderby4331.zip
        3 kB
        Lily Wei
      8. notorderby4331.zip
        2 kB
        Lily Wei
      9. repro2_qryplan.txt
        13 kB
        Mike Matrigali
      10. repro2.sql
        3 kB
        Mike Matrigali
      11. repro.sql
        2 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Knut Anders Hatlen created issue -
          Knut Anders Hatlen made changes -
          Field Original Value New Value
          Attachment repro.sql [ 12415254 ]
          Kathey Marsden made changes -
          Issue & fix info [Repro attached] [High Value Fix, Repro attached]
          Knut Anders Hatlen made changes -
          Link This issue is related to DERBY-3926 [ DERBY-3926 ]
          Kathey Marsden made changes -
          Affects Version/s 10.1.3.3 [ 12313478 ]
          Affects Version/s 10.2.2.1 [ 12312251 ]
          Affects Version/s 10.3.3.1 [ 12313143 ]
          Affects Version/s 10.4.2.1 [ 12313401 ]
          Affects Version/s 10.6.0.0 [ 12313727 ]
          Mike Matrigali made changes -
          Attachment repro2.sql [ 12415395 ]
          Mike Matrigali made changes -
          Attachment repro2_qryplan.txt [ 12415398 ]
          Kathey Marsden made changes -
          Affects Version/s 10.2.2.1 [ 12312251 ]
          Affects Version/s 10.3.3.1 [ 12313143 ]
          Affects Version/s 10.4.2.1 [ 12313401 ]
          Affects Version/s 10.1.3.3 [ 12313478 ]
          Lily Wei made changes -
          Attachment notorderby4331.zip [ 12415443 ]
          Attachment orderby4331.zip [ 12415444 ]
          Mamta A. Satoor made changes -
          Attachment createDBsimpler.txt [ 12415459 ]
          Mamta A. Satoor made changes -
          Attachment createDBsimplerVer2.txt [ 12415462 ]
          Mike Matrigali made changes -
          Attachment derby4331_do_not_commit.diff [ 12415643 ]
          Mike Matrigali made changes -
          Assignee Mike Matrigali [ mikem ]
          Mike Matrigali made changes -
          Attachment wisconsin.diff [ 12415649 ]
          Mamta A. Satoor made changes -
          Attachment DERBY4331_additionalTests_diff.txt [ 12415667 ]
          Mike Matrigali made changes -
          Attachment derby_4331_patch_2.diff [ 12415676 ]
          Mike Matrigali made changes -
          Link This issue is related to DERBY-4339 [ DERBY-4339 ]
          Mike Matrigali made changes -
          Fix Version/s 10.6.0.0 [ 12313727 ]
          Affects Version/s 10.1.4.0 [ 12311950 ]
          Affects Version/s 10.2.3.0 [ 12312215 ]
          Affects Version/s 10.3.4.0 [ 12313653 ]
          Affects Version/s 10.4.3.0 [ 12313654 ]
          Mike Matrigali made changes -
          Fix Version/s 10.5.2.1 [ 12314117 ]
          Mike Matrigali made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Fix Version/s 10.1.4.0 [ 12311950 ]
          Fix Version/s 10.2.3.0 [ 12312215 ]
          Fix Version/s 10.3.4.0 [ 12313653 ]
          Fix Version/s 10.4.3.0 [ 12313654 ]
          Resolution Fixed [ 1 ]
          Knut Anders Hatlen made changes -
          Fix Version/s 10.1.3.3 [ 12313478 ]
          Fix Version/s 10.2.2.1 [ 12312251 ]
          Fix Version/s 10.3.3.1 [ 12313143 ]
          Fix Version/s 10.4.2.1 [ 12313401 ]
          Fix Version/s 10.1.4.0 [ 12311950 ]
          Fix Version/s 10.2.3.0 [ 12312215 ]
          Fix Version/s 10.3.4.0 [ 12313653 ]
          Fix Version/s 10.4.3.0 [ 12313654 ]
          Knut Anders Hatlen made changes -
          Affects Version/s 10.1.3.3 [ 12313478 ]
          Affects Version/s 10.2.2.1 [ 12312251 ]
          Affects Version/s 10.3.3.1 [ 12313143 ]
          Affects Version/s 10.4.2.1 [ 12313401 ]
          Affects Version/s 10.1.4.0 [ 12311950 ]
          Affects Version/s 10.2.3.0 [ 12312215 ]
          Affects Version/s 10.3.4.0 [ 12313653 ]
          Affects Version/s 10.4.3.0 [ 12313654 ]
          Knut Anders Hatlen made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Dag H. Wanvik made changes -
          Link This issue relates to DERBY-6148 [ DERBY-6148 ]
          Gavin made changes -
          Workflow jira [ 12472112 ] Default workflow, editable Closed status [ 12796664 ]

            People

            • Assignee:
              Mike Matrigali
              Reporter:
              Knut Anders Hatlen
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development