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

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          Verified that the problem is fixed on trunk and 10.5.3.0 RC. Closing.

          Thanks for the quick fix!

          Show
          Knut Anders Hatlen added a comment - Verified that the problem is fixed on trunk and 10.5.3.0 RC. Closing. Thanks for the quick fix!
          Hide
          Knut Anders Hatlen added a comment -

          Updated affects versions similarly.

          Show
          Knut Anders Hatlen added a comment - Updated affects versions similarly.
          Hide
          Knut Anders Hatlen added a comment -

          Updated fix versions to match the versions on the branches.

          Show
          Knut Anders Hatlen added a comment - Updated fix versions to match the versions on the branches.
          Hide
          Mike Matrigali added a comment -

          fix has been made in trunk and backported to 10.5, 10.4, 10.3, 10.2 and 10.1 branches.

          Show
          Mike Matrigali added a comment - fix has been made in trunk and backported to 10.5, 10.4, 10.3, 10.2 and 10.1 branches.
          Hide
          Mike Matrigali added a comment -

          I've backported fix to 10.5 branch with svn checkin #801817
          All tests passed for me on a 10.5 client, XP, ibm16 jvm.

          Show
          Mike Matrigali added a comment - I've backported fix to 10.5 branch with svn checkin #801817 All tests passed for me on a 10.5 client, XP, ibm16 jvm.
          Hide
          Mike Matrigali added a comment -

          updating affects version, it is affected by a fix that has been backported all the way to 10.1 branch.
          As tests pass I plan to backport all the way. Waiting now on the end of my 10.5 tests run.

          Show
          Mike Matrigali added a comment - updating affects version, it is affected by a fix that has been backported all the way to 10.1 branch. As tests pass I plan to backport all the way. Waiting now on the end of my 10.5 tests run.
          Hide
          Mamta A. Satoor added a comment -

          The bug behind the query below is as follows
          SELECT CS.ID
          FROM --DERBY-PROPERTIES joinOrder=FIXED
          FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
          , FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET
          , CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID
          WHERE
          CS.ID = FC.CHANGESET AND
          F.ID = FC.FILE
          ORDER BY CS.ID DESC;

          Optimizer while considering any join order collects the sorting provided by that join order. For instance, for the query above, optimizer will first start with [F, -1, -1] as the join order and for this join order, it knows that it can guarantee row ordering on FILES.REPOSITORY AND FILE.PATH. Next in the join order will be [F, FC, -1] and now available sorting is F.REPOSITORY, F.PATH, FC.FILE and FC.CHANGESET. The important thing to notice here is that the sorting available at any point is in the order collected by optimizer and this piece of information was not being considered by the code removed by Mike from FromBaseTable. This behavior can be seen when optimizer is considering the join order [F, FC, CS]. While considering this join order, the removed code from FromBaseTable kicks in for the predicate CS.ID=FC.CHANGESET. The removed code looks at equijoin CS.ID=FC.CHANGESET and it looks at the available ordering so far and sees FC.CHANGESET in there and incorrectly concludes that required sorting is available on CS.ID indirectly through FC.CHANGSET. It fails to take into account that FC.CHANGSET is in the 4th position in the ordering. The rows returned from [F, FC, -1] are ordered F.REPOSITORY, F.PATH, FC.FILE and FC.CHANGSET and not just FC.CHANGESET. Because the code incorrectly decides that required sorting is available, it decides to avoid the sorting for the given query.
          The result of the query above without Mike's changes are as follows
          ID
          -----------
          1
          2
          3
          2
          2
          3
          As we can see from above that FC.CHANGSET is sorted for every row selected from the outermost table F.
          a)The first row in the result above is for the first row qualified from F
          b)The next 2 rows in the result above are for the second qualified row from F
          c) the 4th row in the result above is for the 3rd qualified row from F
          d)and last 2 rows in the result above are for the 4th qualified row from F.

          If the outermost optimizable F was only one row resultset, we could safely assume that the required ordering is provided by indexes being considered for [F, FC, -1] but that is not the case.

          In future, if we ever decide to enhance sort avoidance code to qualify more queries for sort avoidance, we should consider the position of the sorted columns in available sorting list of columns and then decide if the query is pre-ordered on required ordering.

          Show
          Mamta A. Satoor added a comment - The bug behind the query below is as follows SELECT CS.ID FROM --DERBY-PROPERTIES joinOrder=FIXED FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH , FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET , CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID WHERE CS.ID = FC.CHANGESET AND F.ID = FC.FILE ORDER BY CS.ID DESC; Optimizer while considering any join order collects the sorting provided by that join order. For instance, for the query above, optimizer will first start with [F, -1, -1] as the join order and for this join order, it knows that it can guarantee row ordering on FILES.REPOSITORY AND FILE.PATH. Next in the join order will be [F, FC, -1] and now available sorting is F.REPOSITORY, F.PATH, FC.FILE and FC.CHANGESET. The important thing to notice here is that the sorting available at any point is in the order collected by optimizer and this piece of information was not being considered by the code removed by Mike from FromBaseTable. This behavior can be seen when optimizer is considering the join order [F, FC, CS] . While considering this join order, the removed code from FromBaseTable kicks in for the predicate CS.ID=FC.CHANGESET. The removed code looks at equijoin CS.ID=FC.CHANGESET and it looks at the available ordering so far and sees FC.CHANGESET in there and incorrectly concludes that required sorting is available on CS.ID indirectly through FC.CHANGSET. It fails to take into account that FC.CHANGSET is in the 4th position in the ordering. The rows returned from [F, FC, -1] are ordered F.REPOSITORY, F.PATH, FC.FILE and FC.CHANGSET and not just FC.CHANGESET. Because the code incorrectly decides that required sorting is available, it decides to avoid the sorting for the given query. The result of the query above without Mike's changes are as follows ID ----------- 1 2 3 2 2 3 As we can see from above that FC.CHANGSET is sorted for every row selected from the outermost table F. a)The first row in the result above is for the first row qualified from F b)The next 2 rows in the result above are for the second qualified row from F c) the 4th row in the result above is for the 3rd qualified row from F d)and last 2 rows in the result above are for the 4th qualified row from F. If the outermost optimizable F was only one row resultset, we could safely assume that the required ordering is provided by indexes being considered for [F, FC, -1] but that is not the case. In future, if we ever decide to enhance sort avoidance code to qualify more queries for sort avoidance, we should consider the position of the sorted columns in available sorting list of columns and then decide if the query is pre-ordered on required ordering.
          Hide
          Lily Wei added a comment -

          I only run tests again repro2.sql and DERBY-3926 and both result are correct. Nothing feels better when correct result returns. Thanks!!!

          Show
          Lily Wei added a comment - I only run tests again repro2.sql and DERBY-3926 and both result are correct. Nothing feels better when correct result returns. Thanks!!!
          Hide
          Mike Matrigali added a comment -

          proposed patch will stop sort avoidance on the types of queries described in
          DERBY-4339

          Show
          Mike Matrigali added a comment - proposed patch will stop sort avoidance on the types of queries described in DERBY-4339
          Hide
          Mike Matrigali added a comment - - edited

          Still have not finished running tests (all of the junit tests passed, waiting on old
          harness tests), derby_4331_patch_2.diff is the current state of the patch. It includes the new tests that mamta contributed (I had to make a few minor changes to get the queries to run correctly).

          It also includes an update to the wisconsin tests and master. I worked through the
          diffs and there were only 2 queries that previous to this patch that produced a
          sort avoidance plan and that now don't. I have filed DERBY-4339 to note these
          2 queries:
          select * from --DERBY-PROPERTIES joinOrder=FIXED
          TENKTUP2, TENKTUP1
          where TENKTUP1.unique1 = TENKTUP2.unique1
          and TENKTUP2.unique1 < 2500
          order by TENKTUP1.unique1';
          select * from TENKTUP1, TENKTUP2
          where TENKTUP1.unique1 = TENKTUP2.unique1
          order by TENKTUP1.unique1 = TENKTUP2.unique1

          All the other diffs were changes to the plans where the optimizer just choose
          different ways to provide a sort avoidance plan with the available indexes.

          Show
          Mike Matrigali added a comment - - edited Still have not finished running tests (all of the junit tests passed, waiting on old harness tests), derby_4331_patch_2.diff is the current state of the patch. It includes the new tests that mamta contributed (I had to make a few minor changes to get the queries to run correctly). It also includes an update to the wisconsin tests and master. I worked through the diffs and there were only 2 queries that previous to this patch that produced a sort avoidance plan and that now don't. I have filed DERBY-4339 to note these 2 queries: select * from --DERBY-PROPERTIES joinOrder=FIXED TENKTUP2, TENKTUP1 where TENKTUP1.unique1 = TENKTUP2.unique1 and TENKTUP2.unique1 < 2500 order by TENKTUP1.unique1'; select * from TENKTUP1, TENKTUP2 where TENKTUP1.unique1 = TENKTUP2.unique1 order by TENKTUP1.unique1 = TENKTUP2.unique1 All the other diffs were changes to the plans where the optimizer just choose different ways to provide a sort avoidance plan with the available indexes.
          Hide
          Mamta A. Satoor added a comment -

          i am attaching a patch for additional junits tests which were collected from this jira entry. Mike, can you please run the test on your codeline to see if works fine?

          Show
          Mamta A. Satoor added a comment - i am attaching a patch for additional junits tests which were collected from this jira entry. Mike, can you please run the test on your codeline to see if works fine?
          Hide
          Kathey Marsden added a comment -

          Put this in two unrelated issues. Not sure if there is a problem with my browser or the driver. I think probably the latter. Hopefully got it right this time.

          As a matter of bookkeeping, if all of the wisconsin diffs are deemed acceptable and other tests and reviews pass, I think we should do the partial backout as a resolution of this issue. Then both DERBY-3926 and DERBY-4331 can be resolved and a new issue opened for any follow up sort avoidance optimizations.

          Show
          Kathey Marsden added a comment - Put this in two unrelated issues. Not sure if there is a problem with my browser or the driver. I think probably the latter. Hopefully got it right this time. As a matter of bookkeeping, if all of the wisconsin diffs are deemed acceptable and other tests and reviews pass, I think we should do the partial backout as a resolution of this issue. Then both DERBY-3926 and DERBY-4331 can be resolved and a new issue opened for any follow up sort avoidance optimizations.
          Hide
          Mike Matrigali added a comment -

          my plan for this fix is going to be the proposed patch along with adding the new test cases for 4331 to the test suite. I am running full set of tests against trunk now and analysing the wisconsin diffs.
          I've posted the wisconsin diffs which as is the case for query plan diffs are quite extensive
          (15k lines) - but so far nothing surprising.

          If all goes well I would first check into trunk and then subsequently would like to backport to 10.5 and then hopefully a new 10.5 apache release could be made (along with whatever we are allowed to do to make it unlikely apache users will use the previous one).

          I plan on checking in even if there are a few cases where we use to do sort avoidance and now
          we don't. If I do this I would file a new JIRA issue outlining these cases, which could be addressed
          later. Note these would only represent performance enhancements, the queries would be returning
          correct results.

          I would appreciate it if anyone thinks this is a bad idea to let me know sooner than later.

          Show
          Mike Matrigali added a comment - my plan for this fix is going to be the proposed patch along with adding the new test cases for 4331 to the test suite. I am running full set of tests against trunk now and analysing the wisconsin diffs. I've posted the wisconsin diffs which as is the case for query plan diffs are quite extensive (15k lines) - but so far nothing surprising. If all goes well I would first check into trunk and then subsequently would like to backport to 10.5 and then hopefully a new 10.5 apache release could be made (along with whatever we are allowed to do to make it unlikely apache users will use the previous one). I plan on checking in even if there are a few cases where we use to do sort avoidance and now we don't. If I do this I would file a new JIRA issue outlining these cases, which could be addressed later. Note these would only represent performance enhancements, the queries would be returning correct results. I would appreciate it if anyone thinks this is a bad idea to let me know sooner than later.
          Hide
          Mike Matrigali added a comment -

          here is the diff file from the wisconsin diffs. I am working through it, but it is taking
          time. A lot of the diffs are that the new plan picks a different order which is also
          a valid sort avoidance plan, they are the of the form where we have TABLE1 with
          INDEX1(id) and TABLE2 with INDEX2(id) and query of form:
          select xxx
          where
          TABLE1.id = TABLE2.id
          order by TABLE1.id

          prior to backing out equijoin stuff we would often pick INDEX2 as left most using the equijoin logic, but now without it we instead find plan using INDEX1 as left most. No
          loss in performance as both provide a valid sort avoidance plan - just a lot of diffs.

          Show
          Mike Matrigali added a comment - here is the diff file from the wisconsin diffs. I am working through it, but it is taking time. A lot of the diffs are that the new plan picks a different order which is also a valid sort avoidance plan, they are the of the form where we have TABLE1 with INDEX1(id) and TABLE2 with INDEX2(id) and query of form: select xxx where TABLE1.id = TABLE2.id order by TABLE1.id prior to backing out equijoin stuff we would often pick INDEX2 as left most using the equijoin logic, but now without it we instead find plan using INDEX1 as left most. No loss in performance as both provide a valid sort avoidance plan - just a lot of diffs.
          Hide
          Mamta A. Satoor added a comment -

          Mike and I have been talking about different possibilities for DERBY-4331/DERBY-3926 given the time critical nature of it. We decided that we will investigate the fix for DERBY-3926 to see what can be backed out and at the same time debug the reason beind DERBY-4331 failure. I will write the information I have collected over last couple days on the reason behind the bug DERBY-4331 and apossible fix for it but first would like to comment on Mike's proposal about backing out some of DERBY-3926 changes

          I agree with the partial backout of DERBY-3926. The code proposed for backout was written to cover additional queries so that we can avoid sorting for them if possible but from what I recall, it was not needed for fixing DERBY-3926. So it should be safe to backout part of the change w/o impacting any queries negatively. It will be good to do full tests before we commit those changes though.

          Show
          Mamta A. Satoor added a comment - Mike and I have been talking about different possibilities for DERBY-4331 / DERBY-3926 given the time critical nature of it. We decided that we will investigate the fix for DERBY-3926 to see what can be backed out and at the same time debug the reason beind DERBY-4331 failure. I will write the information I have collected over last couple days on the reason behind the bug DERBY-4331 and apossible fix for it but first would like to comment on Mike's proposal about backing out some of DERBY-3926 changes I agree with the partial backout of DERBY-3926 . The code proposed for backout was written to cover additional queries so that we can avoid sorting for them if possible but from what I recall, it was not needed for fixing DERBY-3926 . So it should be safe to backout part of the change w/o impacting any queries negatively. It will be good to do full tests before we commit those changes though.
          Hide
          Rick Hillegas added a comment -

          Thanks, Mike. I can confirm that your derby4331_do_not_commit.diff patch produces correct results from the d3926_repro.sql script attached to DERBY-3926 repro and from the repro2.sql script attached to this JIRA

          Show
          Rick Hillegas added a comment - Thanks, Mike. I can confirm that your derby4331_do_not_commit.diff patch produces correct results from the d3926_repro.sql script attached to DERBY-3926 repro and from the repro2.sql script attached to this JIRA
          Hide
          Mike Matrigali added a comment - - edited

          This is a patch file based off of trunk.

          not fully tested patch file. This patch just gets rid of the FromBaseTable.java changes
          to DERBY-3926, it leaves every thing else.

          So far my testing shows:
          postives: fixes all run test cases of DERBY-3926 and DERBY-4331
          negatives: Causes some diffs in wisconsin, probably the known cases where we
          don't do sort avoidance anymore - but still get correct sorting.
          unknown: no full test run done yet.

          Show
          Mike Matrigali added a comment - - edited This is a patch file based off of trunk. not fully tested patch file. This patch just gets rid of the FromBaseTable.java changes to DERBY-3926 , it leaves every thing else. So far my testing shows: postives: fixes all run test cases of DERBY-3926 and DERBY-4331 negatives: Causes some diffs in wisconsin, probably the known cases where we don't do sort avoidance anymore - but still get correct sorting. unknown: no full test run done yet.
          Hide
          Mike Matrigali added a comment -

          I am still investigating, but as this seems time critical am posting some incomplete information.

          I have been debugging and talking to mamta about this issue. I now do believe part of
          DERBY-3926 should be backed out as the logic is incomplete, I don't believe the fix for this is
          simple and it would be better to get all the code lines into a safer state and put off the complete
          fix for later. But I believe we only need to backout part of the fix.

          The fix for DERBY-3926 addressed 2 issues:
          1) The code incorrectly assumed if a query plan resulted in for example "always sorted on a",
          "always sorted on b" where "always sorted" meant something like a=5, that then one could
          assume the query was also just sorted on b. This was only true if a was a single row result
          set. I believe this code is working fine.

          2) When the fix went in we noticed a few queries in wisconsin were not doing sort avoidance any
          more . See 23/Apr/09 01:13 PM DERBY-3926 comment by mamta. To this end code was
          added to do some tricky stuff with join equivalent analysis. This part of the fix is localized to
          a single file mamta and I have looked at it and it does not do the right work. It does not handle
          desc indexes or desc ordery by properly and can incorrectly assume that a query ordered
          on a, b, c satisfies and order by c. I have just tried just backing out this change and
          all the DERBY-4331 cases I tried worked, all the test cases specific for DERBY-3926 worked,
          and as expected I got wisconsin diffs that I am analysing. I have not had time to run full tests.

          I will post a patch with just this change if anyone else wants to experiment.

          Show
          Mike Matrigali added a comment - I am still investigating, but as this seems time critical am posting some incomplete information. I have been debugging and talking to mamta about this issue. I now do believe part of DERBY-3926 should be backed out as the logic is incomplete, I don't believe the fix for this is simple and it would be better to get all the code lines into a safer state and put off the complete fix for later. But I believe we only need to backout part of the fix. The fix for DERBY-3926 addressed 2 issues: 1) The code incorrectly assumed if a query plan resulted in for example "always sorted on a", "always sorted on b" where "always sorted" meant something like a=5, that then one could assume the query was also just sorted on b. This was only true if a was a single row result set. I believe this code is working fine. 2) When the fix went in we noticed a few queries in wisconsin were not doing sort avoidance any more . See 23/Apr/09 01:13 PM DERBY-3926 comment by mamta. To this end code was added to do some tricky stuff with join equivalent analysis. This part of the fix is localized to a single file mamta and I have looked at it and it does not do the right work. It does not handle desc indexes or desc ordery by properly and can incorrectly assume that a query ordered on a, b, c satisfies and order by c. I have just tried just backing out this change and all the DERBY-4331 cases I tried worked, all the test cases specific for DERBY-3926 worked, and as expected I got wisconsin diffs that I am analysing. I have not had time to run full tests. I will post a patch with just this change if anyone else wants to experiment.
          Hide
          Mamta A. Satoor added a comment -

          Following query gets rid of EXISTS clause and still runs into the incorrect order by result. Much easier to debug.

          SELECT CS.ID
          FROM --DERBY-PROPERTIES joinOrder=FIXED
          FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
          , FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET
          , CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID
          WHERE
          CS.ID = FC.CHANGESET AND
          F.ID = FC.FILE
          ORDER BY CS.ID DESC;

          Show
          Mamta A. Satoor added a comment - Following query gets rid of EXISTS clause and still runs into the incorrect order by result. Much easier to debug. SELECT CS.ID FROM --DERBY-PROPERTIES joinOrder=FIXED FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH , FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET , CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID WHERE CS.ID = FC.CHANGESET AND F.ID = FC.FILE ORDER BY CS.ID DESC;
          Hide
          Mike Matrigali added a comment -

          the bug is not specific to the desc order by, the following query also gives wrong ordered results:
          ij(C1)> SELECT
          CS.ID
          FROM --DERBY-PROPERTIES joinOrder=FIXED
          FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
          , FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET
          , CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID
          WHERE
          CS.ID = FC.CHANGESET AND
          F.ID = FC.FILE AND
          EXISTS (
          SELECT 1
          FROM FILES F2 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
          WHERE
          F2.ID = FC.FILE)
          ORDER BY CS.ID;
          ID
          -----------
          1
          2
          3
          2
          2
          3

          Show
          Mike Matrigali added a comment - the bug is not specific to the desc order by, the following query also gives wrong ordered results: ij(C1)> SELECT CS.ID FROM --DERBY-PROPERTIES joinOrder=FIXED FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH , FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET , CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID WHERE CS.ID = FC.CHANGESET AND F.ID = FC.FILE AND EXISTS ( SELECT 1 FROM FILES F2 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH WHERE F2.ID = FC.FILE) ORDER BY CS.ID; ID ----------- 1 2 3 2 2 3
          Hide
          Mike Matrigali added a comment -

          I am not sure if just running update statistics will fix this problem for any set of data. For this
          query it does look like changing the order by clause to include an extra column will work around
          this bug:

          ORDER BY CS.ID, CS.REPOSITORY;

          Show
          Mike Matrigali added a comment - I am not sure if just running update statistics will fix this problem for any set of data. For this query it does look like changing the order by clause to include an extra column will work around this bug: ORDER BY CS.ID, CS.REPOSITORY;
          Hide
          Mamta A. Satoor added a comment -

          The query plan for the query below is as follows
          SELECT CS.ID
          FROM --DERBY-PROPERTIES joinOrder=FIXED
          FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
          , FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET
          , CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID
          WHERE
          CS.ID = FC.CHANGESET AND
          F.ID = FC.FILE AND
          EXISTS (
          SELECT 1
          FROM FILES F2 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
          WHERE
          F2.ID = FC.FILE)
          ORDER BY CS.ID DESC;

          Statement Name:
          null
          Statement Text:
          SELECT CS.ID
          FROM --DERBY-PROPERTIES joinOrder=FIXED
          FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
          , FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET
          , CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID
          WHERE
          CS.ID = FC.CHANGESET AND
          F.ID = FC.FILE AND
          EXISTS (
          SELECT 1
          FROM FILES F2 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
          WHERE
          F2.ID = FC.FILE)
          ORDER BY CS.ID DESC
          Parse Time: 0
          Bind Time: 0
          Optimize Time: 0
          Generate Time: 0
          Compile Time: 0
          Execute Time: 0
          Begin Compilation Timestamp : null
          End Compilation Timestamp : null
          Begin Execution Timestamp : null
          End Execution Timestamp : null
          Statement Execution Plan Text:
          Project-Restrict ResultSet (11):
          Number of opens = 1
          Rows seen = 6
          Rows filtered = 0
          restriction = false
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 2.67
          optimizer estimated cost: 2330.17

          Source result set:
          User supplied optimizer overrides for join are

          { joinOrder=FIXED }
          Nested Loop Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 6
          Rows seen from the right = 6
          Rows filtered = 0
          Rows returned = 6
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 2.67
          optimizer estimated cost: 2330.17

          Left result set:
          User supplied optimizer overrides for join are { joinOrder=FIXED}
          Nested Loop Exists Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 6
          Rows seen from the right = 6
          Rows filtered = 0
          Rows returned = 6
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 6.00
          optimizer estimated cost: 357.23

          Left result set:
          User supplied optimizer overrides for join are { joinOrder=FIXED }

          Nested Loop Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 4
          Rows seen from the right = 6
          Rows filtered = 0
          Rows returned = 6
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 6.00
          optimizer estimated cost: 347.80

          Left result set:
          Index Row to Base Row ResultSet for FILES:
          Number of opens = 1
          Rows seen = 4
          Columns accessed from heap =

          {0}
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 9.00
          optimizer estimated cost: 328.82

          User supplied optimizer overrides on FILES are { index=SQL090803230333481 }
          Index Scan ResultSet for FILES using constraint FILES_REPOSITORY_PATH at read committed isolation level using share row locking chosen by the optimizer
          Number of opens = 1
          Rows seen = 4
          Rows filtered = 0
          Fetch Size = 1
          constructor time (milliseconds)= 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row =0

          scan information:
          Bit set of columns fetched={2}
          Number of columns fetched=1
          Number of deleted rows visited=0

          Number of pages visited=1
          Number of rows qualified=4
          Number of rows visited=4
          Scan type=btree
          Tree height=-1
          start position:
          None
          stop position:
          None
          qualifiers:
          None
          optimizer estimated row count: 9.00
          optimizer estimated cost: 328.82


          Right result set:
          User supplied optimizer overrides on FILECHANGES are { index=SQL090803230334101 }
          Index Scan ResultSet for FILECHANGES using constraint FILECHANGES_FILE_CHANGESET at read committed isolation level using instantaneous share row locking chosen by the optimizer
          Number of opens = 4
          Rows seen = 6
          Rows filtered = 0
          Fetch Size = 16
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched={0, 1}
          Number of columns fetched=2
          Number of deleted rows visited=0
          Number of pages visited=4
          Number of rows qualified=6
          Number of rows visited=9
          Scan type=btree
          Tree height=1
          start position:
          >= on first 1 column(s).
          Ordered null semantics on the following columns:0
          stop position:
          > on first 1 column(s).
          Ordered null semantics on the following columns:0
          qualifiers:None
          optimizer estimated row count: 6.00
          optimizer estimated cost: 18.97


          Right result set:
          User supplied optimizer overrides on CHANGESETS are { index=SQL090803230333820 }
          Index Scan ResultSet for CHANGESETS using constraint CHANGESETS_PRIMARY_ID at read committed isolation level using share row locking chosen by the optimizer
          Number of opens = 6
          Rows seen = 6
          Rows filtered = 0
          Fetch Size = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched={0}

          Number of columns fetched=1
          Number of deleted rows visited=0
          Number of pages visited=6
          Number of rows qualified=6
          Number of rows visited=6
          Scan type=btree
          Tree height=1
          start position:
          >= on first 1 column(s).
          Ordered null semantics on the following columns:0
          stop position:
          > on first 1 column(s).
          Ordered null semantics on the following columns:0
          qualifiers:None
          optimizer estimated row count: 6.00
          optimizer estimated cost: 9.43

          Right result set:
          Project-Restrict ResultSet (10):
          Number of opens = 6
          Rows seen = 24
          Rows filtered = 18
          restriction = true
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 2.67
          optimizer estimated cost: 1972.94

          Source result set:
          Index Row to Base Row ResultSet for FILES:
          Number of opens = 6
          Rows seen = 24
          Columns accessed from heap =

          {0}

          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 2.67
          optimizer estimated cost: 1972.94

          User supplied optimizer overrides on FILES are

          { index=SQL090803230333481 }

          Index Scan ResultSet for FILES using constraint FILES_REPOSITORY_PATH at read committed isolation level using share row locking chosen by the optimizer
          Number of opens = 6
          Rows seen = 24
          Rows filtered = 0
          Fetch Size = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=

          {2}

          Number of columns fetched=1
          Number of deleted rows visited=0
          Number of pages visited=6
          Number of rows qualified=24
          Number of rows visited=24
          Scan type=btree
          Tree height=1
          start position: None
          stop position: None
          qualifiers:None
          optimizer estimated row count: 2.67
          optimizer estimated cost: 1972.94

          Show
          Mamta A. Satoor added a comment - The query plan for the query below is as follows SELECT CS.ID FROM --DERBY-PROPERTIES joinOrder=FIXED FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH , FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET , CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID WHERE CS.ID = FC.CHANGESET AND F.ID = FC.FILE AND EXISTS ( SELECT 1 FROM FILES F2 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH WHERE F2.ID = FC.FILE) ORDER BY CS.ID DESC; Statement Name: null Statement Text: SELECT CS.ID FROM --DERBY-PROPERTIES joinOrder=FIXED FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH , FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET , CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID WHERE CS.ID = FC.CHANGESET AND F.ID = FC.FILE AND EXISTS ( SELECT 1 FROM FILES F2 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH WHERE F2.ID = FC.FILE) ORDER BY CS.ID DESC Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Project-Restrict ResultSet (11): Number of opens = 1 Rows seen = 6 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 2.67 optimizer estimated cost: 2330.17 Source result set: User supplied optimizer overrides for join are { joinOrder=FIXED } Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 6 Rows seen from the right = 6 Rows filtered = 0 Rows returned = 6 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 2.67 optimizer estimated cost: 2330.17 Left result set: User supplied optimizer overrides for join are { joinOrder=FIXED} Nested Loop Exists Join ResultSet: Number of opens = 1 Rows seen from the left = 6 Rows seen from the right = 6 Rows filtered = 0 Rows returned = 6 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 6.00 optimizer estimated cost: 357.23 Left result set: User supplied optimizer overrides for join are { joinOrder=FIXED } Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 4 Rows seen from the right = 6 Rows filtered = 0 Rows returned = 6 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 6.00 optimizer estimated cost: 347.80 Left result set: Index Row to Base Row ResultSet for FILES: Number of opens = 1 Rows seen = 4 Columns accessed from heap = {0} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 9.00 optimizer estimated cost: 328.82 User supplied optimizer overrides on FILES are { index=SQL090803230333481 } Index Scan ResultSet for FILES using constraint FILES_REPOSITORY_PATH at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 4 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds)= 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row =0 scan information: Bit set of columns fetched={2} Number of columns fetched=1 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=4 Number of rows visited=4 Scan type=btree Tree height=-1 start position: None stop position: None qualifiers: None optimizer estimated row count: 9.00 optimizer estimated cost: 328.82 Right result set: User supplied optimizer overrides on FILECHANGES are { index=SQL090803230334101 } Index Scan ResultSet for FILECHANGES using constraint FILECHANGES_FILE_CHANGESET at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 4 Rows seen = 6 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0, 1} Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=4 Number of rows qualified=6 Number of rows visited=9 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns:0 stop position: > on first 1 column(s). Ordered null semantics on the following columns:0 qualifiers:None optimizer estimated row count: 6.00 optimizer estimated cost: 18.97 Right result set: User supplied optimizer overrides on CHANGESETS are { index=SQL090803230333820 } Index Scan ResultSet for CHANGESETS using constraint CHANGESETS_PRIMARY_ID at read committed isolation level using share row locking chosen by the optimizer Number of opens = 6 Rows seen = 6 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0} Number of columns fetched=1 Number of deleted rows visited=0 Number of pages visited=6 Number of rows qualified=6 Number of rows visited=6 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns:0 stop position: > on first 1 column(s). Ordered null semantics on the following columns:0 qualifiers:None optimizer estimated row count: 6.00 optimizer estimated cost: 9.43 Right result set: Project-Restrict ResultSet (10): Number of opens = 6 Rows seen = 24 Rows filtered = 18 restriction = true projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 2.67 optimizer estimated cost: 1972.94 Source result set: Index Row to Base Row ResultSet for FILES: Number of opens = 6 Rows seen = 24 Columns accessed from heap = {0} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 2.67 optimizer estimated cost: 1972.94 User supplied optimizer overrides on FILES are { index=SQL090803230333481 } Index Scan ResultSet for FILES using constraint FILES_REPOSITORY_PATH at read committed isolation level using share row locking chosen by the optimizer Number of opens = 6 Rows seen = 24 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched= {2} Number of columns fetched=1 Number of deleted rows visited=0 Number of pages visited=6 Number of rows qualified=24 Number of rows visited=24 Scan type=btree Tree height=1 start position: None stop position: None qualifiers:None optimizer estimated row count: 2.67 optimizer estimated cost: 1972.94
          Hide
          Mamta A. Satoor added a comment -

          I have simplified the db creation just a little bit more removing the unnecessary columns from the tables. The new create db script is createDBsimplerVer2.txt and the query that goes along with that db is

          SELECT CS.ID
          FROM --DERBY-PROPERTIES joinOrder=FIXED
          FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
          , FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET
          , CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID
          WHERE
          CS.ID = FC.CHANGESET AND
          F.ID = FC.FILE AND
          EXISTS (
          SELECT 1
          FROM FILES F2 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
          WHERE
          F2.ID = FC.FILE)
          ORDER BY CS.ID DESC;

          Show
          Mamta A. Satoor added a comment - I have simplified the db creation just a little bit more removing the unnecessary columns from the tables. The new create db script is createDBsimplerVer2.txt and the query that goes along with that db is SELECT CS.ID FROM --DERBY-PROPERTIES joinOrder=FIXED FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH , FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET , CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID WHERE CS.ID = FC.CHANGESET AND F.ID = FC.FILE AND EXISTS ( SELECT 1 FROM FILES F2 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH WHERE F2.ID = FC.FILE) ORDER BY CS.ID DESC;
          Hide
          Mamta A. Satoor added a comment -

          Attaching a new simpler script, createDBsimpler.txt, to create the db which will have only the tables used by the query below

          SELECT CS.ID
          FROM --DERBY-PROPERTIES joinOrder=FIXED
          FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
          , FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET
          , CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID
          WHERE
          CS.ID = FC.CHANGESET AND
          F.ID = FC.FILE AND
          EXISTS (
          SELECT 1
          FROM FILES F2 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
          WHERE
          F2.ID = FC.FILE)
          ORDER BY CS.ID DESC;

          Show
          Mamta A. Satoor added a comment - Attaching a new simpler script, createDBsimpler.txt, to create the db which will have only the tables used by the query below SELECT CS.ID FROM --DERBY-PROPERTIES joinOrder=FIXED FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH , FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET , CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID WHERE CS.ID = FC.CHANGESET AND F.ID = FC.FILE AND EXISTS ( SELECT 1 FROM FILES F2 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH WHERE F2.ID = FC.FILE) ORDER BY CS.ID DESC;
          Hide
          Mamta A. Satoor added a comment -

          The above query works fine prior to DERBY-3926 change but fails with DERBY-3926 in the codeline. The results of the query above prior to DERBY-3926 changes are as follows
          ID
          -----------
          3
          3
          2
          2
          2
          1

          6 rows selected

          Show
          Mamta A. Satoor added a comment - The above query works fine prior to DERBY-3926 change but fails with DERBY-3926 in the codeline. The results of the query above prior to DERBY-3926 changes are as follows ID ----------- 3 3 2 2 2 1 6 rows selected
          Hide
          Mamta A. Satoor added a comment -

          The following query also seems to give wrong results on 10.5 codeline. It has tables REPOSITORIES and AUTHORS missing compared to the original query. I will try it without the changes for DERBY-3926 to see if it passes there.

          SELECT CS.ID
          FROM --DERBY-PROPERTIES joinOrder=FIXED
          FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
          , FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET
          , CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID
          WHERE
          CS.ID = FC.CHANGESET AND
          F.ID = FC.FILE AND
          EXISTS (
          SELECT 1
          FROM FILES F2 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
          WHERE
          F2.ID = FC.FILE)
          ORDER BY CS.ID DESC;
          ID
          -----------
          1
          2
          3
          2
          2
          3

          6 rows selected

          Show
          Mamta A. Satoor added a comment - The following query also seems to give wrong results on 10.5 codeline. It has tables REPOSITORIES and AUTHORS missing compared to the original query. I will try it without the changes for DERBY-3926 to see if it passes there. SELECT CS.ID FROM --DERBY-PROPERTIES joinOrder=FIXED FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH , FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET , CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID WHERE CS.ID = FC.CHANGESET AND F.ID = FC.FILE AND EXISTS ( SELECT 1 FROM FILES F2 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH WHERE F2.ID = FC.FILE) ORDER BY CS.ID DESC; ID ----------- 1 2 3 2 2 3 6 rows selected
          Hide
          Lily Wei added a comment -

          Taking out Author table from previous query still not ordering in trunk but works 10.5 (783167)
          ij> SELECT CS.ID
          FROM --DERBY-PROPERTIES joinOrder=FIXED
          REPOSITORIES R --DERBY-PROPERTIES constraint=REPOSITORIES_PATH
          , FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
          , FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET
          , CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID
          WHERE
          R.PATH = '/var/tmp/source5923202038296723704opengrok/mercurial' AND
          F.REPOSITORY = R.ID AND
          CS.REPOSITORY = R.ID AND
          CS.ID = FC.CHANGESET AND
          F.ID = FC.FILE AND
          EXISTS (
          SELECT 1
          FROM FILES F2 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
          WHERE
          F2.ID = FC.FILE)
          ORDER BY CS.ID DESC;
          ID
          -----------
          1
          2
          3
          2
          2
          3

          If take out of 'CS.ID = FC.CHANGESET' from where cause constraint, the query will perform order by on both trunk and 10.5(783167)
          ij> SELECT CS.ID
          FROM --DERBY-PROPERTIES joinOrder=FIXED
          REPOSITORIES R --DERBY-PROPERTIES constraint=REPOSITORIES_PATH
          , FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
          , FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET
          , CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID
          WHERE
          R.PATH = '/var/tmp/source5923202038296723704opengrok/mercurial' AND
          F.REPOSITORY = R.ID AND
          CS.REPOSITORY = R.ID AND
          F.ID = FC.FILE AND
          EXISTS (
          SELECT 1
          FROM FILES F2 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
          WHERE
          F2.ID = FC.FILE)
          ORDER BY CS.ID DESC;
          ID
          -----------
          3
          3
          3
          3
          3
          3
          2
          2
          2
          2
          2
          2
          1
          1
          1
          1
          1
          1

          18 rows selected

          I cannot really tell what is the difference between the two path. However, the value -1 for Tree height on the first query seems odd to me.
          The derby.log for the first query is on notorderby4331.zip and the second query is on orderby4331.zip.
          (The below capture from derby.log on notorderby4331.zip)
          User supplied optimizer overrides on REPOSITORIES are

          { index=SQL090803170751640 }
          Index Scan ResultSet for REPOSITORIES using constraint REPOSITORIES_PATH at read committed isolation level using share row locking chosen by the optimizer
          Number of opens = 1
          Rows seen = 1
          Rows filtered = 0
          Fetch Size = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=All
          Number of columns fetched=2
          Number of deleted rows visited=0
          Number of pages visited=1
          Number of rows qualified=1
          Number of rows visited=1
          Scan type=btree
          Tree height=-1 <<<==== -1 is an odd value
          start position:
          >= on first 1 column(s).

          (The below capture from derby.log on orderby4331.zip)
          User supplied optimizer overrides on REPOSITORIES are { index=SQL090803170751640 }

          Index Scan ResultSet for REPOSITORIES using constraint REPOSITORIES_PATH at read committed isolation level using share row locking chosen by the optimizer
          Number of opens = 1
          Rows seen = 1
          Rows filtered = 0
          Fetch Size = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=All
          Number of columns fetched=2
          Number of deleted rows visited=0
          Number of pages visited=1
          Number of rows qualified=1
          Number of rows visited=1
          Scan type=btree
          Tree height=1 <<<==== not -1 value
          start position:
          >= on first 1 column(s).

          Hope this info is helpful. Thanks, Lily

          Show
          Lily Wei added a comment - Taking out Author table from previous query still not ordering in trunk but works 10.5 (783167) ij> SELECT CS.ID FROM --DERBY-PROPERTIES joinOrder=FIXED REPOSITORIES R --DERBY-PROPERTIES constraint=REPOSITORIES_PATH , FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH , FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET , CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID WHERE R.PATH = '/var/tmp/source5923202038296723704opengrok/mercurial' AND F.REPOSITORY = R.ID AND CS.REPOSITORY = R.ID AND CS.ID = FC.CHANGESET AND F.ID = FC.FILE AND EXISTS ( SELECT 1 FROM FILES F2 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH WHERE F2.ID = FC.FILE) ORDER BY CS.ID DESC; ID ----------- 1 2 3 2 2 3 If take out of 'CS.ID = FC.CHANGESET' from where cause constraint, the query will perform order by on both trunk and 10.5(783167) ij> SELECT CS.ID FROM --DERBY-PROPERTIES joinOrder=FIXED REPOSITORIES R --DERBY-PROPERTIES constraint=REPOSITORIES_PATH , FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH , FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET , CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID WHERE R.PATH = '/var/tmp/source5923202038296723704opengrok/mercurial' AND F.REPOSITORY = R.ID AND CS.REPOSITORY = R.ID AND F.ID = FC.FILE AND EXISTS ( SELECT 1 FROM FILES F2 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH WHERE F2.ID = FC.FILE) ORDER BY CS.ID DESC; ID ----------- 3 3 3 3 3 3 2 2 2 2 2 2 1 1 1 1 1 1 18 rows selected I cannot really tell what is the difference between the two path. However, the value -1 for Tree height on the first query seems odd to me. The derby.log for the first query is on notorderby4331.zip and the second query is on orderby4331.zip. (The below capture from derby.log on notorderby4331.zip) User supplied optimizer overrides on REPOSITORIES are { index=SQL090803170751640 } Index Scan ResultSet for REPOSITORIES using constraint REPOSITORIES_PATH at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 Scan type=btree Tree height=-1 <<<==== -1 is an odd value start position: >= on first 1 column(s). (The below capture from derby.log on orderby4331.zip) User supplied optimizer overrides on REPOSITORIES are { index=SQL090803170751640 } Index Scan ResultSet for REPOSITORIES using constraint REPOSITORIES_PATH at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 Scan type=btree Tree height=1 <<<==== not -1 value start position: >= on first 1 column(s). Hope this info is helpful. Thanks, Lily
          Hide
          Lily Wei added a comment -

          This query is just an existence of a easier subquery that failed on trunk but gives correct order on svn 783167:
          ij> SELECT CS.ID
          FROM --DERBY-PROPERTIES joinOrder=FIXED
          REPOSITORIES R --DERBY-PROPERTIES constraint=REPOSITORIES_PATH
          , FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
          , FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET
          , AUTHORS A --DERBY-PROPERTIES constraint=AUTHORS_REPOSITORY_NAME
          , CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID
          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 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
          WHERE
          F2.ID = FC.FILE
          )
          ORDER BY CS.ID DESC;
          ID
          -----------
          1
          2
          3
          2
          2
          3

          6 rows selected

          Thanks, Lily

          Show
          Lily Wei added a comment - This query is just an existence of a easier subquery that failed on trunk but gives correct order on svn 783167: ij> SELECT CS.ID FROM --DERBY-PROPERTIES joinOrder=FIXED REPOSITORIES R --DERBY-PROPERTIES constraint=REPOSITORIES_PATH , FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH , FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET , AUTHORS A --DERBY-PROPERTIES constraint=AUTHORS_REPOSITORY_NAME , CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID 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 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH WHERE F2.ID = FC.FILE ) ORDER BY CS.ID DESC; ID ----------- 1 2 3 2 2 3 6 rows selected Thanks, Lily
          Hide
          Mike Matrigali added a comment -

          This query also gets out of order rows in trunk but not in 10.5 before 3629 change. It fixes the indexes used also:
          SELECT CS.ID
          FROM --DERBY-PROPERTIES joinOrder=FIXED
          REPOSITORIES R --DERBY-PROPERTIES constraint=REPOSITORIES_PATH
          , FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
          , FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET
          , AUTHORS A --DERBY-PROPERTIES constraint=AUTHORS_REPOSITORY_NAME
          , CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID
          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 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
          WHERE
          F2.ID = FC.FILE AND F2.REPOSITORY = R.ID AND
          F2.PATH LIKE '/%' ESCAPE '#')
          ORDER BY CS.ID DESC;

          Show
          Mike Matrigali added a comment - This query also gets out of order rows in trunk but not in 10.5 before 3629 change. It fixes the indexes used also: SELECT CS.ID FROM --DERBY-PROPERTIES joinOrder=FIXED REPOSITORIES R --DERBY-PROPERTIES constraint=REPOSITORIES_PATH , FILES F --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH , FILECHANGES FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET , AUTHORS A --DERBY-PROPERTIES constraint=AUTHORS_REPOSITORY_NAME , CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID 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 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH WHERE F2.ID = FC.FILE AND F2.REPOSITORY = R.ID AND F2.PATH LIKE '/%' ESCAPE '#') ORDER BY CS.ID DESC;
          Hide
          Mike Matrigali added a comment -

          Here is a fixed order join query that repro's on trunk but does not repro on 10.5 before the 3926
          changes. I shuffled the order of the tables in the from list to match order in the attached
          plan where the bug reproduced - nothing else is changed.:
          SELECT CS.ID
          FROM --DERBY-PROPERTIES joinOrder=FIXED
          REPOSITORIES R, FILES F, FILECHANGES FC, AUTHORS A, CHANGESETS CS
          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;

          Show
          Mike Matrigali added a comment - Here is a fixed order join query that repro's on trunk but does not repro on 10.5 before the 3926 changes. I shuffled the order of the tables in the from list to match order in the attached plan where the bug reproduced - nothing else is changed.: SELECT CS.ID FROM --DERBY-PROPERTIES joinOrder=FIXED REPOSITORIES R, FILES F, FILECHANGES FC, AUTHORS A, CHANGESETS CS 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;
          Hide
          Mike Matrigali added a comment -

          i have been looking at the query plan and have more questions than answers.

          One strange thing is that the bad query plan does not use the descending
          index at all. With DERBY-3926 the usual problem was that an index that
          would naturally satisfy a sort avoidance plan was used but in some part of the
          join order which actually didn't result in the row ordering being correct.

          The plan looks like it does some sort of query transformation, maybe flatten but not sure if that is right term. The EXISTS subquery I think is making it hard to
          control the plan with hints, but am not sure. If anyone can figure out a rewrite that reproduces the bug without the subquery that would help also.

          I tried the following query thinking just the existence of the subquery might be
          enough, but it got correct ordered results:
          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.REPOSITORY = 1)
          ORDER BY CS.ID DESC;

          Show
          Mike Matrigali added a comment - i have been looking at the query plan and have more questions than answers. One strange thing is that the bad query plan does not use the descending index at all. With DERBY-3926 the usual problem was that an index that would naturally satisfy a sort avoidance plan was used but in some part of the join order which actually didn't result in the row ordering being correct. The plan looks like it does some sort of query transformation, maybe flatten but not sure if that is right term. The EXISTS subquery I think is making it hard to control the plan with hints, but am not sure. If anyone can figure out a rewrite that reproduces the bug without the subquery that would help also. I tried the following query thinking just the existence of the subquery might be enough, but it got correct ordered results: 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.REPOSITORY = 1) ORDER BY CS.ID DESC;
          Hide
          Mamta A. Satoor added a comment -

          The following updated query is still pretty involved but it consistently reproduces the wrong results and will go through very limited iteration of optimizer code because of all the optimizer overrides. Ideal will be if the following query can be made simpler with fewer number of tables.
          SELECT CS.ID
          FROM --DERBY-PROPERTIES joinOrder=FIXED
          REPOSITORIES R – DERBY-PROPERTIES constraint=REPOSITORIES_PATH
          , FILES F – DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
          , FILECHANGES FC – DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET
          , AUTHORS A – DERBY-PROPERTIES constraint=AUTHORS_REPOSITORY_NAME
          , CHANGESETS CS – DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID
          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;

          Show
          Mamta A. Satoor added a comment - The following updated query is still pretty involved but it consistently reproduces the wrong results and will go through very limited iteration of optimizer code because of all the optimizer overrides. Ideal will be if the following query can be made simpler with fewer number of tables. SELECT CS.ID FROM --DERBY-PROPERTIES joinOrder=FIXED REPOSITORIES R – DERBY-PROPERTIES constraint=REPOSITORIES_PATH , FILES F – DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH , FILECHANGES FC – DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET , AUTHORS A – DERBY-PROPERTIES constraint=AUTHORS_REPOSITORY_NAME , CHANGESETS CS – DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID 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;
          Hide
          Kathey Marsden added a comment -

          Fix affect versions. updated wrong issue.

          Show
          Kathey Marsden added a comment - Fix affect versions. updated wrong issue.
          Hide
          Mamta A. Satoor added a comment -

          BTW, just an FYI, that I am doing my research on this bug in 10.5 codeline.

          Show
          Mamta A. Satoor added a comment - BTW, just an FYI, that I am doing my research on this bug in 10.5 codeline.
          Hide
          Mamta A. Satoor added a comment - - edited

          Wanted to share that if the same query is run with the optimizer ovreride of join order fixed, the query does not give incorrect results. The query plan at this time is as follows
          Statement Name:
          null
          Statement Text:
          SELECT CS.ID
          FROM --DERBY-PROPERTIES joinOrder=FIXED
          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
          Parse Time: 0
          Bind Time: 0
          Optimize Time: 0
          Generate Time: 0
          Compile Time: 0
          Execute Time: 0
          Begin Compilation Timestamp : null
          End Compilation Timestamp : null
          Begin Execution Timestamp : null
          End Execution Timestamp : null
          Statement Execution Plan Text:
          Sort ResultSet:
          Number of opens = 1
          Rows input = 6
          Rows returned = 6
          Eliminate duplicates = false
          In sorted order = false
          Sort information:
          Number of rows input=6
          Number of rows output=6
          Sort type=internal
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.08
          optimizer estimated cost: 553.68

          Source result set:
          Project-Restrict ResultSet (20):
          Number of opens = 1
          Rows seen = 6
          Rows filtered = 0
          restriction = false
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 0.08
          optimizer estimated cost: 553.68

          Source result set:
          User supplied optimizer overrides for join are

          { joinOrder=FIXED }
          Nested Loop Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 6
          Rows seen from the right = 6
          Rows filtered = 0
          Rows returned = 6
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.08
          optimizer estimated cost: 553.68

          Left result set:
          User supplied optimizer overrides for join are { joinOrder=FIXED }

          Nested Loop Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 6
          Rows seen from the right = 6
          Rows filtered = 0
          Rows returned = 6
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.36
          optimizer estimated cost: 546.94

          Left result set:
          User supplied optimizer overrides for join are

          { joinOrder=FIXED }
          Nested Loop Exists Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 6
          Rows seen from the right = 6
          Rows filtered = 0
          Rows returned = 6
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.60
          optimizer estimated cost: 475.57

          Left result set:
          User supplied optimizer overrides for join are { joinOrder=FIXED }

          Nested Loop Exists Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 6
          Rows seen from the right = 6
          Rows filtered = 0
          Rows returned = 6
          constructor time (milliseconds)= 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.60
          optimizer estimated cost: 454.07

          Left result set:
          User supplied optimizer overrides for join are

          { joinOrder=FIXED }

          Hash Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 3
          Rows seen from the right = 6
          Rows filtered = 0
          Rows returned = 6
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated rowcount: 0.60
          optimizer estimated cost: 372.68

          Left result set:
          Table Scan ResultSet for CHANGESETS at read committed isolation level using instantaneous share row locking chosen by the optimizer
          Number of opens = 1
          Rows seen = 3
          Rows filtered = 0
          Fetch Size = 16
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=

          {0, 1, 3}

          Number of columns fetched=3
          Number of pagesvisited=1
          Number of rows qualified=3
          Number of rows visited=3
          Scan type=heap
          start position:
          null stop position:
          null qualifiers:
          None
          optimizer estimated row count: 8.00
          optimizer estimated cost: 351.84

          Right result set:
          Hash Scan ResultSet for FILECHANGES using constraint FILECHANGES_FILE_CHANGESET at read committed isolation level using instantaneous share row locking:
          Number of opens = 3
          Hash table size = 3
          Hash key is column number 1
          Rows seen = 6
          Rows filtered = 0
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=

          {0, 1}
          Number of columns fetched=2
          Number of deleted rows visited=0
          Number of pagesvisited=1
          Number of rows qualified=6
          Number of rows visited=6
          Scan type=btree
          Tree height=1
          start position:

          None
          stop position:
          None
          scan qualifiers:

          None
          next qualifiers:

          Column[0][0] Id: 1
          Operator: =
          Ordered nulls: false
          Unknown return value: false
          Negate comparison result: false

          optimizer estimated row count: 0.60
          optimizer estimated cost: 20.84


          Right result set:
          Project-Restrict ResultSet (10):

          Number of opens = 6
          Rows seen = 6
          Rows filtered = 0
          restriction = true
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated rowcount: 0.60
          optimizer estimated cost: 81.39

          Source result set:
          Index Row to Base Row ResultSet for REPOSITORIES:
          Number of opens = 6
          Rows seen = 6
          Columns accessed from heap = {1}
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.60
          optimizer estimated cost: 81.39

          Index Scan ResultSet for REPOSITORIES using constraint REPOSITORIES_PRIMARY_ID at read committed isolation level using share row locking chosen by the optimizer
          Number of opens= 6
          Rows seen = 6
          Rows filtered =0
          Fetch Size = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=All
          Number of columns fetched=2
          Number of deleted rows visited=0
          Number of pages visited=6
          Number of rows qualified=6
          Number of rows visited=6
          Scan type=btree
          Tree height=1
          start position:
          >= on first 1 column(s).
          Ordered null semantics on the following columns:0
          stop position:
          > on first 1 column(s).
          Ordered null semantics on the following columns:0
          qualifiers:
          None
          optimizer estimated row count: 0.60
          optimizer estimated cost: 81.39



          Right result set:
          Project-Restrict ResultSet (13):
          Number of opens = 6
          Rows seen = 6
          Rows filtered = 0
          restriction = true
          projection = true
          constructor time (milliseconds)= 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds)= 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 0.60
          optimizer estimated cost: 21.50

          Source result set:
          Index Row to Base Row ResultSet for FILES:
          Number of opens = 6
          Rows seen = 6
          Columns accessed from heap = {2}

          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated rowcount: 0.60
          optimizer estimated cost: 21.50

          Index Scan ResultSet for FILES using constraint FILES_PRIMARY_ID at read committed isolation level using share row locking chosen by the optimizer
          Number of opens = 6
          Rows seen = 6
          Rows filtered = 0
          Fetch Size = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=All
          Number of columns fetched=2
          Number of deleted rows visited=0
          Number of pages visited=6
          Number of rows qualified=6
          Number of rows visited=6
          Scan type=btree
          Tree height=1
          start position:

          >= on first 1 column(s).
          Ordered null semantics on the following columns:0
          stop position: > on first 1 column(s).
          Ordered null semantics on the following columns:0
          qualifiers:None
          optimizer estimated row count: 0.60
          optimizer estimated cost: 21.50



          Right result set:
          Project-Restrict ResultSet (16):
          Number of opens = 6
          Rows seen = 6
          Rows filtered = 0
          restriction = true
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 0.36
          optimizer estimated cost: 71.37

          Source result set:
          Index Row to Base Row ResultSet for AUTHORS:
          Number of opens = 6
          Rows seen = 6
          Columns accessed from heap = {0, 1}

          constructor time (milliseconds)= 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.36
          optimizer estimated cost: 71.37

          Index Scan ResultSet for AUTHORS using constraint AUTHORS_REPOSITORY_NAME at read committed isolation level using instantaneous share row locking chosen by the optimizer
          Number of opens = 6
          Rows seen = 6
          Rows filtered = 0
          Fetch Size = 16
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=

          {0, 2}

          Number of columns fetched=2
          Number of deleted rows visited=0
          Number of pages visited=6
          Number of rows qualified=6
          Number of rows visited=6

          Scan type=btree
          Tree height=1
          start position:
          >= on first 1 column(s).
          Ordered null semantics on the following columns:0
          stop position:
          > on first 1 column(s).
          Ordered null semantics on the following columns:0
          qualifiers:
          Column[0][0] Id: 0
          Operator: =
          Ordered nulls: false
          Unknown return value: false
          Negate comparison result: false

          optimizer estimated rowcount: 0.36
          optimizer estimated cost: 71.37

          Right result set:
          Project-Restrict ResultSet (19):
          Number of opens = 6
          Rows seen = 24
          Rows filtered = 18
          restriction = true
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 0.08
          optimizer estimated cost: 6.73

          Source result set:
          Index Row to Base Row ResultSet for FILES:
          Number of opens = 6
          Rows seen = 24
          Columns accessed from heap =

          {0, 1, 2}

          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.08
          optimizer estimated cost: 6.73

          Index Scan ResultSet for FILES using constraint FILES_REPOSITORY_PATH at read committed isolation level using instantaneous share row locking chosen by the optimizer
          Number of opens = 6
          Rows seen = 24
          Rows filtered = 0
          Fetch Size = 16
          constructor time (milliseconds)= 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row =0

          scan information:
          Bit set of columns fetched=All
          Number of columns fetched=3
          Number of deleted rows visited=0

          Number of pages visited=6
          Number of rows qualified=24
          Number of rows visited=24
          Scan type=btree
          Tree height=1
          start position:
          >= on first 2 column(s).
          Ordered null semantics on the following columns:0 1
          stop position:
          >= on first 2 column(s).
          Ordered null semantics on the following columns:
          0 1
          qualifiers:
          Column[0][0] Id: 0
          Operator: =
          Ordered nulls: false
          Unknown return value: false
          Negate comparison result: false

          optimizer estimated row count: 0.08
          optimizer estimated cost: 6.73

          Show
          Mamta A. Satoor added a comment - - edited Wanted to share that if the same query is run with the optimizer ovreride of join order fixed, the query does not give incorrect results. The query plan at this time is as follows Statement Name: null Statement Text: SELECT CS.ID FROM --DERBY-PROPERTIES joinOrder=FIXED 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 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Sort ResultSet: Number of opens = 1 Rows input = 6 Rows returned = 6 Eliminate duplicates = false In sorted order = false Sort information: Number of rows input=6 Number of rows output=6 Sort type=internal constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.08 optimizer estimated cost: 553.68 Source result set: Project-Restrict ResultSet (20): Number of opens = 1 Rows seen = 6 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 0.08 optimizer estimated cost: 553.68 Source result set: User supplied optimizer overrides for join are { joinOrder=FIXED } Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 6 Rows seen from the right = 6 Rows filtered = 0 Rows returned = 6 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.08 optimizer estimated cost: 553.68 Left result set: User supplied optimizer overrides for join are { joinOrder=FIXED } Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 6 Rows seen from the right = 6 Rows filtered = 0 Rows returned = 6 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.36 optimizer estimated cost: 546.94 Left result set: User supplied optimizer overrides for join are { joinOrder=FIXED } Nested Loop Exists Join ResultSet: Number of opens = 1 Rows seen from the left = 6 Rows seen from the right = 6 Rows filtered = 0 Rows returned = 6 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.60 optimizer estimated cost: 475.57 Left result set: User supplied optimizer overrides for join are { joinOrder=FIXED } Nested Loop Exists Join ResultSet: Number of opens = 1 Rows seen from the left = 6 Rows seen from the right = 6 Rows filtered = 0 Rows returned = 6 constructor time (milliseconds)= 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.60 optimizer estimated cost: 454.07 Left result set: User supplied optimizer overrides for join are { joinOrder=FIXED } Hash Join ResultSet: Number of opens = 1 Rows seen from the left = 3 Rows seen from the right = 6 Rows filtered = 0 Rows returned = 6 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated rowcount: 0.60 optimizer estimated cost: 372.68 Left result set: Table Scan ResultSet for CHANGESETS at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 3 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched= {0, 1, 3} Number of columns fetched=3 Number of pagesvisited=1 Number of rows qualified=3 Number of rows visited=3 Scan type=heap start position: null stop position: null qualifiers: None optimizer estimated row count: 8.00 optimizer estimated cost: 351.84 Right result set: Hash Scan ResultSet for FILECHANGES using constraint FILECHANGES_FILE_CHANGESET at read committed isolation level using instantaneous share row locking: Number of opens = 3 Hash table size = 3 Hash key is column number 1 Rows seen = 6 Rows filtered = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched= {0, 1} Number of columns fetched=2 Number of deleted rows visited=0 Number of pagesvisited=1 Number of rows qualified=6 Number of rows visited=6 Scan type=btree Tree height=1 start position: None stop position: None scan qualifiers: None next qualifiers: Column [0] [0] Id: 1 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false optimizer estimated row count: 0.60 optimizer estimated cost: 20.84 Right result set: Project-Restrict ResultSet (10): Number of opens = 6 Rows seen = 6 Rows filtered = 0 restriction = true projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated rowcount: 0.60 optimizer estimated cost: 81.39 Source result set: Index Row to Base Row ResultSet for REPOSITORIES: Number of opens = 6 Rows seen = 6 Columns accessed from heap = {1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.60 optimizer estimated cost: 81.39 Index Scan ResultSet for REPOSITORIES using constraint REPOSITORIES_PRIMARY_ID at read committed isolation level using share row locking chosen by the optimizer Number of opens= 6 Rows seen = 6 Rows filtered =0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=6 Number of rows qualified=6 Number of rows visited=6 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns:0 stop position: > on first 1 column(s). Ordered null semantics on the following columns:0 qualifiers: None optimizer estimated row count: 0.60 optimizer estimated cost: 81.39 Right result set: Project-Restrict ResultSet (13): Number of opens = 6 Rows seen = 6 Rows filtered = 0 restriction = true projection = true constructor time (milliseconds)= 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds)= 0 projection time (milliseconds) = 0 optimizer estimated row count: 0.60 optimizer estimated cost: 21.50 Source result set: Index Row to Base Row ResultSet for FILES: Number of opens = 6 Rows seen = 6 Columns accessed from heap = {2} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated rowcount: 0.60 optimizer estimated cost: 21.50 Index Scan ResultSet for FILES using constraint FILES_PRIMARY_ID at read committed isolation level using share row locking chosen by the optimizer Number of opens = 6 Rows seen = 6 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=6 Number of rows qualified=6 Number of rows visited=6 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns:0 stop position: > on first 1 column(s). Ordered null semantics on the following columns:0 qualifiers:None optimizer estimated row count: 0.60 optimizer estimated cost: 21.50 Right result set: Project-Restrict ResultSet (16): Number of opens = 6 Rows seen = 6 Rows filtered = 0 restriction = true projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 0.36 optimizer estimated cost: 71.37 Source result set: Index Row to Base Row ResultSet for AUTHORS: Number of opens = 6 Rows seen = 6 Columns accessed from heap = {0, 1} constructor time (milliseconds)= 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.36 optimizer estimated cost: 71.37 Index Scan ResultSet for AUTHORS using constraint AUTHORS_REPOSITORY_NAME at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 6 Rows seen = 6 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched= {0, 2} Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=6 Number of rows qualified=6 Number of rows visited=6 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns:0 stop position: > on first 1 column(s). Ordered null semantics on the following columns:0 qualifiers: Column [0] [0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false optimizer estimated rowcount: 0.36 optimizer estimated cost: 71.37 Right result set: Project-Restrict ResultSet (19): Number of opens = 6 Rows seen = 24 Rows filtered = 18 restriction = true projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 0.08 optimizer estimated cost: 6.73 Source result set: Index Row to Base Row ResultSet for FILES: Number of opens = 6 Rows seen = 24 Columns accessed from heap = {0, 1, 2} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.08 optimizer estimated cost: 6.73 Index Scan ResultSet for FILES using constraint FILES_REPOSITORY_PATH at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 6 Rows seen = 24 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds)= 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row =0 scan information: Bit set of columns fetched=All Number of columns fetched=3 Number of deleted rows visited=0 Number of pages visited=6 Number of rows qualified=24 Number of rows visited=24 Scan type=btree Tree height=1 start position: >= on first 2 column(s). Ordered null semantics on the following columns:0 1 stop position: >= on first 2 column(s). Ordered null semantics on the following columns: 0 1 qualifiers: Column [0] [0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false optimizer estimated row count: 0.08 optimizer estimated cost: 6.73
          Hide
          Mike Matrigali added a comment -

          Here is query plan for bad sort result as generated by repro2.sql

          Show
          Mike Matrigali added a comment - Here is query plan for bad sort result as generated by repro2.sql
          Hide
          Mike Matrigali added a comment -

          I added names to all the constraints/primary keys to the original repro.sql script to make it easier to understand what is going on when looking at query plans.

          Show
          Mike Matrigali added a comment - I added names to all the constraints/primary keys to the original repro.sql script to make it easier to understand what is going on when looking at query plans.
          Hide
          Mamta A. Satoor added a comment -

          I will start looking at this bug. It will be great if the query can be made simpler and/or use optimizer overrides to make it always break so the debugger does not go through many iterations through the optimizer. I will post more info as I am progress.

          Show
          Mamta A. Satoor added a comment - I will start looking at this bug. It will be great if the query can be made simpler and/or use optimizer overrides to make it always break so the debugger does not go through many iterations through the optimizer. I will post more info as I am progress.
          Hide
          Mike Matrigali added a comment -

          here is the query plan from the bad sort results query, gotten by adding derby.language.logQueryPlan=true
          to derby.properties. Posting now, have not read through it yet - but first thing to notice is that there
          is not any sort node so as assumed the problem is not a sorter bug - but some sort of sort avoidance
          plan bug:
          2009-08-03 18:09:06.296 GMT Thread[main,5,main] (XID = 259), (SESSIONID = 3), SELECT CS.ID^M
          FROM^M
          CHANGESETS CS, FILECHANGES FC, REPOSITORIES R, FILES F, AUTHORS A^M
          WHERE^M
          R.PATH = '/var/tmp/source5923202038296723704opengrok/mercurial' AND ^M
          F.REPOSITORY = R.ID AND ^M
          A.REPOSITORY = R.ID AND^M
          CS.REPOSITORY = R.ID AND ^M
          CS.ID = FC.CHANGESET AND ^M
          F.ID = FC.FILE AND^M
          A.ID = CS.AUTHOR AND^M
          EXISTS (^M
          SELECT 1^M
          FROM FILES F2^M
          WHERE^M
          F2.ID = FC.FILE AND F2.REPOSITORY = R.ID AND^M
          F2.PATH LIKE '/%' ESCAPE '#')^M
          ORDER BY CS.ID DESC ******* Project-Restrict ResultSet (18):
          Number of opens = 1
          Rows seen = 6
          Rows filtered = 0
          restriction = false
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 0.01
          optimizer estimated cost: 157.72

          Source result set:
          Nested Loop Exists Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 6
          Rows seen from the right = 6
          Rows filtered = 0
          Rows returned = 6
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.01
          optimizer estimated cost: 157.72

          Left result set:
          Nested Loop Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 6
          Rows seen from the right = 6
          Rows filtered = 0
          Rows returned = 6
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.01
          optimizer estimated cost: 157.10

          Left result set:
          Nested Loop Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 16
          Rows seen from the right = 6
          Rows filtered = 0
          Rows returned = 6
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.02
          optimizer estimated cost: 155.23

          Left result set:
          Nested Loop Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 4
          Rows seen from the right = 16
          Rows filtered = 0
          Rows returned = 16
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.20
          optimizer estimated cost: 154.53

          Left result set:
          Nested Loop Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 1
          Rows seen from the right = 4
          Rows filtered = 0
          Rows returned = 4
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.22
          optimizer estimated cost: 146.81

          Left result set:
          Index Row to Base Row ResultSet for REPOSITORIES:
          Number of opens = 1
          Rows seen = 1
          Columns accessed from heap =

          {0}

          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 1.00
          optimizer estimated cost: 135.64

          Index Scan ResultSet for REPOSITORIES using constraint SQL090803110642671 at read committed isolation level using share ro
          w locking chosen by the optimizer
          Number of opens = 1
          Rows seen = 1
          Rows filtered = 0
          Fetch Size = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=All
          Number of columns fetched=2
          Number of deleted rows visited=0
          Number of pages visited=1
          Number of rows qualified=1
          Number of rows visited=1
          Scan type=btree
          Tree height=-1
          start position:
          >= on first 1 column(s).
          Ordered null semantics on the following columns:
          0
          stop position:
          > on first 1 column(s).
          Ordered null semantics on the following columns:
          0
          qualifiers:
          None
          optimizer estimated row count: 1.00
          optimizer estimated cost: 135.64

          Right result set:
          Index Row to Base Row ResultSet for FILES:
          Number of opens = 1
          Rows seen = 4
          Columns accessed from heap =

          {0, 1, 2}

          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.22
          optimizer estimated cost: 11.17

          Index Scan ResultSet for FILES using constraint SQL090803110643012 at read committed isolation level using share row locki
          ng chosen by the optimizer
          Number of opens = 1
          Rows seen = 4
          Rows filtered = 0
          Fetch Size = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=All
          Number of columns fetched=3
          Number of deleted rows visited=0
          Number of pages visited=1
          Number of rows qualified=4
          Number of rows visited=4
          Scan type=btree
          Tree height=1
          start position:
          >= on first 2 column(s).
          Ordered null semantics on the following columns:
          0 1
          stop position:
          >= on first 2 column(s).
          Ordered null semantics on the following columns:
          0 1
          qualifiers:
          None
          optimizer estimated row count: 0.22
          optimizer estimated cost: 11.17

          Right result set:
          Index Row to Base Row ResultSet for FILES:
          Number of opens = 4
          Rows seen = 16
          Columns accessed from heap =

          {0, 2}
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.20
          optimizer estimated cost: 7.72

          Index Scan ResultSet for FILES using constraint SQL090803110643012 at read committed isolation level using share row locking c
          hosen by the optimizer
          Number of opens = 4
          Rows seen = 16
          Rows filtered = 0
          Fetch Size = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched={0, 2}

          Number of columns fetched=2
          Number of deleted rows visited=0
          Number of pages visited=4
          Number of rows qualified=16
          Number of rows visited=16
          Scan type=btree
          Tree height=1
          start position:
          >= on first 1 column(s).
          Ordered null semantics on the following columns:
          0
          stop position:
          > on first 1 column(s).
          Ordered null semantics on the following columns:
          0
          qualifiers:
          Column[0][0] Id: 0
          Operator: =
          Ordered nulls: false
          Unknown return value: false
          Negate comparison result: false

          optimizer estimated row count: 0.20
          optimizer estimated cost: 7.72
          Right result set:
          Index Scan ResultSet for FILECHANGES using constraint SQL090803110644463 at read committed isolation level using instantaneous share r
          ow locking chosen by the optimizer
          Number of opens = 16
          Rows seen = 6
          Rows filtered = 0
          Fetch Size = 16
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=

          {0, 1}
          Number of columns fetched=2
          Number of deleted rows visited=0
          Number of pages visited=16
          Number of rows qualified=6
          Number of rows visited=36
          Scan type=btree
          Tree height=1
          start position:
          >= on first 1 column(s).
          Ordered null semantics on the following columns:
          0
          stop position:
          > on first 1 column(s).
          Ordered null semantics on the following columns:
          0
          qualifiers:
          Column[0][0] Id: 0
          Operator: =
          Ordered nulls: false
          Unknown return value: false
          Negate comparison result: false

          optimizer estimated row count: 0.02
          optimizer estimated cost: 0.70
          Right result set:
          Index Row to Base Row ResultSet for AUTHORS:
          Number of opens = 6
          Rows seen = 6
          Columns accessed from heap = {0, 1}

          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.01
          optimizer estimated cost: 1.87

          Index Scan ResultSet for AUTHORS using constraint SQL090803110643392 at read committed isolation level using share row locking chosen
          by the optimizer
          Number of opens = 6
          Rows seen = 6
          Rows filtered = 0
          Fetch Size = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=

          {0, 2}

          Number of columns fetched=2
          Number of deleted rows visited=0
          Number of pages visited=6
          Number of rows qualified=6
          Number of rows visited=6
          Scan type=btree
          Tree height=1
          start position:
          >= on first 1 column(s).
          Ordered null semantics on the following columns:
          0
          stop position:
          > on first 1 column(s).
          Ordered null semantics on the following columns:
          0
          qualifiers:
          Column[0][0] Id: 0
          Operator: =
          Ordered nulls: false
          Unknown return value: false
          Negate comparison result: false

          optimizer estimated row count: 0.01
          optimizer estimated cost: 1.87

          Right result set:
          Project-Restrict ResultSet (17):
          Number of opens = 6
          Rows seen = 6
          Rows filtered = 0
          restriction = true
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 0.01
          optimizer estimated cost: 0.62

          Source result set:
          Index Row to Base Row ResultSet for CHANGESETS:
          Number of opens = 6
          Rows seen = 6
          Columns accessed from heap =

          {1, 3}

          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.01
          optimizer estimated cost: 0.62

          Index Scan ResultSet for CHANGESETS using constraint SQL090803110643850 at read committed isolation level using share row locking chos
          en by the optimizer
          Number of opens = 6
          Rows seen = 6
          Rows filtered = 0
          Fetch Size = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=All
          Number of columns fetched=2
          Number of deleted rows visited=0
          Number of pages visited=6
          Number of rows qualified=6
          Number of rows visited=6
          Scan type=btree
          Tree height=1
          start position:
          >= on first 1 column(s).
          Ordered null semantics on the following columns:
          0
          stop position:
          > on first 1 column(s).
          Ordered null semantics on the following columns:
          0
          qualifiers:
          None
          optimizer estimated row count: 0.01
          optimizer estimated cost: 0.62

          Show
          Mike Matrigali added a comment - here is the query plan from the bad sort results query, gotten by adding derby.language.logQueryPlan=true to derby.properties. Posting now, have not read through it yet - but first thing to notice is that there is not any sort node so as assumed the problem is not a sorter bug - but some sort of sort avoidance plan bug: 2009-08-03 18:09:06.296 GMT Thread [main,5,main] (XID = 259), (SESSIONID = 3), SELECT CS.ID^M FROM^M CHANGESETS CS, FILECHANGES FC, REPOSITORIES R, FILES F, AUTHORS A^M WHERE^M R.PATH = '/var/tmp/source5923202038296723704opengrok/mercurial' AND ^M F.REPOSITORY = R.ID AND ^M A.REPOSITORY = R.ID AND^M CS.REPOSITORY = R.ID AND ^M CS.ID = FC.CHANGESET AND ^M F.ID = FC.FILE AND^M A.ID = CS.AUTHOR AND^M EXISTS (^M SELECT 1^M FROM FILES F2^M WHERE^M F2.ID = FC.FILE AND F2.REPOSITORY = R.ID AND^M F2.PATH LIKE '/%' ESCAPE '#')^M ORDER BY CS.ID DESC ******* Project-Restrict ResultSet (18): Number of opens = 1 Rows seen = 6 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 0.01 optimizer estimated cost: 157.72 Source result set: Nested Loop Exists Join ResultSet: Number of opens = 1 Rows seen from the left = 6 Rows seen from the right = 6 Rows filtered = 0 Rows returned = 6 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.01 optimizer estimated cost: 157.72 Left result set: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 6 Rows seen from the right = 6 Rows filtered = 0 Rows returned = 6 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.01 optimizer estimated cost: 157.10 Left result set: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 16 Rows seen from the right = 6 Rows filtered = 0 Rows returned = 6 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.02 optimizer estimated cost: 155.23 Left result set: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 4 Rows seen from the right = 16 Rows filtered = 0 Rows returned = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.20 optimizer estimated cost: 154.53 Left result set: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 1 Rows seen from the right = 4 Rows filtered = 0 Rows returned = 4 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.22 optimizer estimated cost: 146.81 Left result set: Index Row to Base Row ResultSet for REPOSITORIES: Number of opens = 1 Rows seen = 1 Columns accessed from heap = {0} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 1.00 optimizer estimated cost: 135.64 Index Scan ResultSet for REPOSITORIES using constraint SQL090803110642671 at read committed isolation level using share ro w locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 Scan type=btree Tree height=-1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: 0 stop position: > on first 1 column(s). Ordered null semantics on the following columns: 0 qualifiers: None optimizer estimated row count: 1.00 optimizer estimated cost: 135.64 Right result set: Index Row to Base Row ResultSet for FILES: Number of opens = 1 Rows seen = 4 Columns accessed from heap = {0, 1, 2} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.22 optimizer estimated cost: 11.17 Index Scan ResultSet for FILES using constraint SQL090803110643012 at read committed isolation level using share row locki ng chosen by the optimizer Number of opens = 1 Rows seen = 4 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=3 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=4 Number of rows visited=4 Scan type=btree Tree height=1 start position: >= on first 2 column(s). Ordered null semantics on the following columns: 0 1 stop position: >= on first 2 column(s). Ordered null semantics on the following columns: 0 1 qualifiers: None optimizer estimated row count: 0.22 optimizer estimated cost: 11.17 Right result set: Index Row to Base Row ResultSet for FILES: Number of opens = 4 Rows seen = 16 Columns accessed from heap = {0, 2} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.20 optimizer estimated cost: 7.72 Index Scan ResultSet for FILES using constraint SQL090803110643012 at read committed isolation level using share row locking c hosen by the optimizer Number of opens = 4 Rows seen = 16 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0, 2} Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=4 Number of rows qualified=16 Number of rows visited=16 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: 0 stop position: > on first 1 column(s). Ordered null semantics on the following columns: 0 qualifiers: Column [0] [0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false optimizer estimated row count: 0.20 optimizer estimated cost: 7.72 Right result set: Index Scan ResultSet for FILECHANGES using constraint SQL090803110644463 at read committed isolation level using instantaneous share r ow locking chosen by the optimizer Number of opens = 16 Rows seen = 6 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched= {0, 1} Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=16 Number of rows qualified=6 Number of rows visited=36 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: 0 stop position: > on first 1 column(s). Ordered null semantics on the following columns: 0 qualifiers: Column [0] [0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false optimizer estimated row count: 0.02 optimizer estimated cost: 0.70 Right result set: Index Row to Base Row ResultSet for AUTHORS: Number of opens = 6 Rows seen = 6 Columns accessed from heap = {0, 1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.01 optimizer estimated cost: 1.87 Index Scan ResultSet for AUTHORS using constraint SQL090803110643392 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 6 Rows seen = 6 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched= {0, 2} Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=6 Number of rows qualified=6 Number of rows visited=6 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: 0 stop position: > on first 1 column(s). Ordered null semantics on the following columns: 0 qualifiers: Column [0] [0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false optimizer estimated row count: 0.01 optimizer estimated cost: 1.87 Right result set: Project-Restrict ResultSet (17): Number of opens = 6 Rows seen = 6 Rows filtered = 0 restriction = true projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 0.01 optimizer estimated cost: 0.62 Source result set: Index Row to Base Row ResultSet for CHANGESETS: Number of opens = 6 Rows seen = 6 Columns accessed from heap = {1, 3} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.01 optimizer estimated cost: 0.62 Index Scan ResultSet for CHANGESETS using constraint SQL090803110643850 at read committed isolation level using share row locking chos en by the optimizer Number of opens = 6 Rows seen = 6 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=6 Number of rows qualified=6 Number of rows visited=6 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: 0 stop position: > on first 1 column(s). Ordered null semantics on the following columns: 0 qualifiers: None optimizer estimated row count: 0.01 optimizer estimated cost: 0.62
          Hide
          Mike Matrigali added a comment -

          If anyone has time, what would be useful is the smallest test case possible with a set of optimizer hints that forces the bad result. This will help in understanding exactly what is going on.

          If I remember correctly, DERBY-3926 basically eliminated sort avoidance plans when marching through
          the all the plans. It turned out that some of the plans it eliminated actually did return the correct order but
          just through luck. So the question with this bug, which is obviously affected by DERBY-3926 is did
          DERBY-3926 just happen to eliinate some "lucky" plans, and push it to an existing bug or did it somehow
          now mark a plan we previously skipped as valid. If the repro had a fixed join plan that showed bad sort
          now and good sort before we could more easily tell what the regression is.

          Show
          Mike Matrigali added a comment - If anyone has time, what would be useful is the smallest test case possible with a set of optimizer hints that forces the bad result. This will help in understanding exactly what is going on. If I remember correctly, DERBY-3926 basically eliminated sort avoidance plans when marching through the all the plans. It turned out that some of the plans it eliminated actually did return the correct order but just through luck. So the question with this bug, which is obviously affected by DERBY-3926 is did DERBY-3926 just happen to eliinate some "lucky" plans, and push it to an existing bug or did it somehow now mark a plan we previously skipped as valid. If the repro had a fixed join plan that showed bad sort now and good sort before we could more easily tell what the regression is.
          Hide
          Kathey Marsden added a comment -

          One thing I don't understand about this regression is the description of the fix for DERBY-3926 made it sound like it was making sort avoidance less likely.
          " The decision for avoiding sorting should also include relationship between the optimizables in a given join order "

          so how did that cause a new sort avoidance problem?

          The comments also say:
          "I think this patch is also improving the existing queries to include a better path than what it was picking up before."

          Is it a change in plan that is causing the regression, perhaps choosing a plan that had always ordered incorrectly?

          Show
          Kathey Marsden added a comment - One thing I don't understand about this regression is the description of the fix for DERBY-3926 made it sound like it was making sort avoidance less likely. " The decision for avoiding sorting should also include relationship between the optimizables in a given join order " so how did that cause a new sort avoidance problem? The comments also say: "I think this patch is also improving the existing queries to include a better path than what it was picking up before." Is it a change in plan that is causing the regression, perhaps choosing a plan that had always ordered incorrectly?
          Hide
          Kathey Marsden added a comment -

          DERBY-3926 was fixed with 10.5.2.0 but did not show up on the release notes because it is still open. The change was backported to the 10.1 branch, so updating the affects version for this issue.

          Show
          Kathey Marsden added a comment - DERBY-3926 was fixed with 10.5.2.0 but did not show up on the release notes because it is still open. The change was backported to the 10.1 branch, so updating the affects version for this issue.
          Hide
          Knut Anders Hatlen added a comment -

          Calling SYSCS_UTIL.SYSCS_UPDATE_STATISTICS on the tables involved also makes the result come out in the right order, so it seems like the bug only appears when a certain plan is chosen.

          The problem does not occur when I revert to revision 783167, but it does occur with revision 783168, which suggests that it was triggered by DERBY-3926.

          Show
          Knut Anders Hatlen added a comment - Calling SYSCS_UTIL.SYSCS_UPDATE_STATISTICS on the tables involved also makes the result come out in the right order, so it seems like the bug only appears when a certain plan is chosen. The problem does not occur when I revert to revision 783167, but it does occur with revision 783168, which suggests that it was triggered by DERBY-3926 .
          Hide
          Lily Wei added a comment -

          I try the same query without subquery and the result is order.
          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 ORDER BY CS.ID DESC;
          ID
          -----------
          3
          3
          2
          2
          2
          1

          6 rows selected

          Hopefully, this will narrow the join logic a little bit.

          Show
          Lily Wei added a comment - I try the same query without subquery and the result is order. 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 ORDER BY CS.ID DESC; ID ----------- 3 3 2 2 2 1 6 rows selected Hopefully, this will narrow the join logic a little bit.
          Hide
          Knut Anders Hatlen added a comment -

          Attached a script that creates a sample database and executes the problematic query.

          Show
          Knut Anders Hatlen added a comment - Attached a script that creates a sample database and executes the problematic query.

            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