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

        Issue Links

          Activity

            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