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

Join returns results in wrong order

    XMLWordPrintableJSON

Details

    • High Value Fix, Repro attached
    • 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

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

              Dates

                Created:
                Updated:
                Resolved: