[jira] Commented: (DERBY-4331) Join returns results in wrong order
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12740149#action_12740149 ] Mamta A. Satoor commented on DERBY-4331: 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. > Join returns results in wrong order > --- > > Key: DERBY-4331 > URL: https://issues.apache.org/jira/browse/DERBY-4331 > Project: Derby > Issue Type: Bug > Components: SQL >Affects Versions: 10.5.2.0, 10.6.0.0 >Reporter: Knut Anders Hatlen >Assignee: Mike Matrigali > Attachments: createDBsimpler.txt, createDBsimplerVer2.txt, > DERBY4331_additionalTests_diff.txt, derby4331_do_not_commit.diff, > derby_4331_patch_2.diff, notorderby4331.zip, orderby4331.zip, repro.sql, > repro2.sql, repro2_qryplan.txt, wisconsin.diff > > > 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 -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4331) Join returns results in wrong order
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12739858#action_12739858 ] Lily Wei commented on DERBY-4331: - I only run tests again repro2.sql and DERBY-3926 and both result are correct. Nothing feels better when correct result returns. Thanks!!! > Join returns results in wrong order > --- > > Key: DERBY-4331 > URL: https://issues.apache.org/jira/browse/DERBY-4331 > Project: Derby > Issue Type: Bug > Components: SQL >Affects Versions: 10.5.2.0, 10.6.0.0 >Reporter: Knut Anders Hatlen >Assignee: Mike Matrigali > Attachments: createDBsimpler.txt, createDBsimplerVer2.txt, > DERBY4331_additionalTests_diff.txt, derby4331_do_not_commit.diff, > derby_4331_patch_2.diff, notorderby4331.zip, orderby4331.zip, repro.sql, > repro2.sql, repro2_qryplan.txt, wisconsin.diff > > > 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 -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4331) Join returns results in wrong order
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12739718#action_12739718 ] Kathey Marsden commented on DERBY-4331: --- 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. > Join returns results in wrong order > --- > > Key: DERBY-4331 > URL: https://issues.apache.org/jira/browse/DERBY-4331 > Project: Derby > Issue Type: Bug > Components: SQL >Affects Versions: 10.5.2.0, 10.6.0.0 >Reporter: Knut Anders Hatlen >Assignee: Mike Matrigali > Attachments: createDBsimpler.txt, createDBsimplerVer2.txt, > derby4331_do_not_commit.diff, notorderby4331.zip, orderby4331.zip, repro.sql, > repro2.sql, repro2_qryplan.txt, wisconsin.diff > > > 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 -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4331) Join returns results in wrong order
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12739698#action_12739698 ] Mamta A. Satoor commented on DERBY-4331: 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. > Join returns results in wrong order > --- > > Key: DERBY-4331 > URL: https://issues.apache.org/jira/browse/DERBY-4331 > Project: Derby > Issue Type: Bug > Components: SQL >Affects Versions: 10.5.2.0, 10.6.0.0 >Reporter: Knut Anders Hatlen >Assignee: Mike Matrigali > Attachments: createDBsimpler.txt, createDBsimplerVer2.txt, > derby4331_do_not_commit.diff, notorderby4331.zip, orderby4331.zip, repro.sql, > repro2.sql, repro2_qryplan.txt > > > 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 -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4331) Join returns results in wrong order
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12739682#action_12739682 ] Rick Hillegas commented on DERBY-4331: -- 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 > Join returns results in wrong order > --- > > Key: DERBY-4331 > URL: https://issues.apache.org/jira/browse/DERBY-4331 > Project: Derby > Issue Type: Bug > Components: SQL >Affects Versions: 10.5.2.0, 10.6.0.0 >Reporter: Knut Anders Hatlen >Assignee: Mike Matrigali > Attachments: createDBsimpler.txt, createDBsimplerVer2.txt, > derby4331_do_not_commit.diff, notorderby4331.zip, orderby4331.zip, repro.sql, > repro2.sql, repro2_qryplan.txt > > > 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 -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4331) Join returns results in wrong order
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12739329#action_12739329 ] Mamta A. Satoor commented on DERBY-4331: 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; > Join returns results in wrong order > --- > > Key: DERBY-4331 > URL: https://issues.apache.org/jira/browse/DERBY-4331 > Project: Derby > Issue Type: Bug > Components: SQL >Affects Versions: 10.5.2.0, 10.6.0.0 >Reporter: Knut Anders Hatlen > Attachments: createDBsimpler.txt, createDBsimplerVer2.txt, > notorderby4331.zip, orderby4331.zip, repro.sql, repro2.sql, repro2_qryplan.txt > > > 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 -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4331) Join returns results in wrong order
[
https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12738862#action_12738862
]
Mamta A. Satoor commented on DERBY-4331:
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 }
[jira] Commented: (DERBY-4331) Join returns results in wrong order
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12738826#action_12738826 ] Mamta A. Satoor commented on DERBY-4331: 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 > Join returns results in wrong order > --- > > Key: DERBY-4331 > URL: https://issues.apache.org/jira/browse/DERBY-4331 > Project: Derby > Issue Type: Bug > Components: SQL >Affects Versions: 10.5.2.0, 10.6.0.0 >Reporter: Knut Anders Hatlen > Attachments: notorderby4331.zip, orderby4331.zip, repro.sql, > repro2.sql, repro2_qryplan.txt > > > 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 -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4331) Join returns results in wrong order
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12738814#action_12738814 ] Mamta A. Satoor commented on DERBY-4331: 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 > Join returns results in wrong order > --- > > Key: DERBY-4331 > URL: https://issues.apache.org/jira/browse/DERBY-4331 > Project: Derby > Issue Type: Bug > Components: SQL >Affects Versions: 10.5.2.0, 10.6.0.0 >Reporter: Knut Anders Hatlen > Attachments: notorderby4331.zip, orderby4331.zip, repro.sql, > repro2.sql, repro2_qryplan.txt > > > 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 -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4331) Join returns results in wrong order
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12738717#action_12738717 ] Lily Wei commented on DERBY-4331: - 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 > Join returns results in wrong order > --- > > Key: DERBY-4331 > URL: https://issues.apache.org/jira/browse/DERBY-4331 > Project: Derby > Issue Type: Bug > Components: SQL >Affects Versions: 10.5.2.0, 10.6.0.0 >Reporter: Knut Anders Hatlen > Attachments: repro.sql, repro2.sql, repro2_qryplan.txt > > > 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 -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4331) Join returns results in wrong order
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12738631#action_12738631 ] Mamta A. Satoor commented on DERBY-4331: 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.IDAND A.REPOSITORY = R.IDAND CS.REPOSITORY = R.IDAND CS.ID = FC.CHANGESETAND 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; > Join returns results in wrong order > --- > > Key: DERBY-4331 > URL: https://issues.apache.org/jira/browse/DERBY-4331 > Project: Derby > Issue Type: Bug > Components: SQL >Affects Versions: 10.5.2.0, 10.6.0.0 >Reporter: Knut Anders Hatlen > Attachments: repro.sql, repro2.sql, repro2_qryplan.txt > > > 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 -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4331) Join returns results in wrong order
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12738603#action_12738603 ] Mamta A. Satoor commented on DERBY-4331: BTW, just an FYI, that I am doing my research on this bug in 10.5 codeline. > Join returns results in wrong order > --- > > Key: DERBY-4331 > URL: https://issues.apache.org/jira/browse/DERBY-4331 > Project: Derby > Issue Type: Bug > Components: SQL >Affects Versions: 10.1.3.3, 10.2.2.1, 10.3.3.1, 10.4.2.1, 10.5.2.0, > 10.6.0.0 >Reporter: Knut Anders Hatlen > Attachments: repro.sql, repro2.sql, repro2_qryplan.txt > > > 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 -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4331) Join returns results in wrong order
[
https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12738591#action_12738591
]
Mamta A. Satoor commented on DERBY-4331:
Wanted to share that once the db is created, re-running 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.IDAND
A.REPOSITORY = R.IDAND
CS.REPOSITORY = R.IDAND
CS.ID = FC.CHANGESETAND
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
[jira] Commented: (DERBY-4331) Join returns results in wrong order
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12738483#action_12738483 ] Mamta A. Satoor commented on DERBY-4331: 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. > Join returns results in wrong order > --- > > Key: DERBY-4331 > URL: https://issues.apache.org/jira/browse/DERBY-4331 > Project: Derby > Issue Type: Bug > Components: SQL >Affects Versions: 10.1.3.3, 10.2.2.1, 10.3.3.1, 10.4.2.1, 10.5.2.0, > 10.6.0.0 >Reporter: Knut Anders Hatlen > Attachments: repro.sql > > > 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 -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4331) Join returns results in wrong order
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12738424#action_12738424 ] Kathey Marsden commented on DERBY-4331: --- 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? > Join returns results in wrong order > --- > > Key: DERBY-4331 > URL: https://issues.apache.org/jira/browse/DERBY-4331 > Project: Derby > Issue Type: Bug > Components: SQL >Affects Versions: 10.1.3.3, 10.2.2.1, 10.3.3.1, 10.4.2.1, 10.5.2.0, > 10.6.0.0 >Reporter: Knut Anders Hatlen > Attachments: repro.sql > > > 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 -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4331) Join returns results in wrong order
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12738084#action_12738084 ] Knut Anders Hatlen commented on DERBY-4331: --- 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. > Join returns results in wrong order > --- > > Key: DERBY-4331 > URL: https://issues.apache.org/jira/browse/DERBY-4331 > Project: Derby > Issue Type: Bug > Components: SQL >Affects Versions: 10.5.2.0 >Reporter: Knut Anders Hatlen > Attachments: repro.sql > > > 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 -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4331) Join returns results in wrong order
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12738078#action_12738078 ] Lily Wei commented on DERBY-4331: - 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. > Join returns results in wrong order > --- > > Key: DERBY-4331 > URL: https://issues.apache.org/jira/browse/DERBY-4331 > Project: Derby > Issue Type: Bug > Components: SQL >Affects Versions: 10.5.2.0 >Reporter: Knut Anders Hatlen > Attachments: repro.sql > > > 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 -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
