Uploaded image for project: '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

        Attachments

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

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: