You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Knut Anders Hatlen (JIRA)" <ji...@apache.org> on 2009/08/02 01:14:14 UTC
[jira] Created: (DERBY-4331) Join returns results in wrong order
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
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
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ 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
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ 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.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
> 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
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ 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] Updated: (DERBY-4331) Join returns results in wrong order
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-4331:
----------------------------------
Fix Version/s: 10.5.2.1
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.
> 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.4.0, 10.2.3.0, 10.3.4.0, 10.4.3.0, 10.5.2.0, 10.6.0.0
> Reporter: Knut Anders Hatlen
> Assignee: Mike Matrigali
> Fix For: 10.5.2.1, 10.6.0.0
>
> 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] Closed: (DERBY-4331) Join returns results in wrong order
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen closed DERBY-4331.
-------------------------------------
Verified that the problem is fixed on trunk and 10.5.3.0 RC. Closing.
Thanks for the quick fix!
> 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
> Assignee: Mike Matrigali
> Fix For: 10.1.3.3, 10.2.2.1, 10.3.3.1, 10.4.2.1, 10.5.3.0, 10.6.0.0
>
> 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
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ 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] Updated: (DERBY-4331) Join returns results in wrong order
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-4331:
--------------------------------------
Affects Version/s: (was: 10.4.3.0)
(was: 10.3.4.0)
(was: 10.2.3.0)
(was: 10.1.4.0)
10.4.2.1
10.3.3.1
10.2.2.1
10.1.3.3
Updated affects versions similarly.
> 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
> Assignee: Mike Matrigali
> Fix For: 10.1.3.3, 10.2.2.1, 10.3.3.1, 10.4.2.1, 10.5.3.0, 10.6.0.0
>
> 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] Updated: (DERBY-4331) Join returns results in wrong order
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-4331:
----------------------------------
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.
> 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
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ 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
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ 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 }
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
> 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] Updated: (DERBY-4331) Join returns results in wrong order
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-4331:
----------------------------------
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.
> 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] Updated: (DERBY-4331) Join returns results in wrong order
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-4331:
----------------------------------
Attachment: derby_4331_patch_2.diff
Still have not finished running tests (all of the junit tests passed, waiting on old
harness tests), but here 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.
> 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] Updated: (DERBY-4331) Join returns results in wrong order
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-4331:
----------------------------------
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;
> 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
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ 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] Assigned: (DERBY-4331) Join returns results in wrong order
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali reassigned DERBY-4331:
-------------------------------------
Assignee: Mike Matrigali
> 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] Updated: (DERBY-4331) Join returns results in wrong order
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-4331:
----------------------------------
Attachment: repro2.sql
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.
> 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
>
>
> 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] Issue Comment Edited: (DERBY-4331) Join returns results in
wrong order
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12739810#action_12739810 ]
Mike Matrigali edited comment on DERBY-4331 at 8/5/09 4:15 PM:
---------------------------------------------------------------
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.
was (Author: mikem):
Still have not finished running tests (all of the junit tests passed, waiting on old
harness tests), but here 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.
> 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
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ 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
Posted by "Lily Wei (JIRA)" <ji...@apache.org>.
[ 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
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ 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] Updated: (DERBY-4331) Join returns results in wrong order
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-4331:
----------------------------------
Attachment: derby4331_do_not_commit.diff
not fully tested path 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.
> 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, 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] Updated: (DERBY-4331) Join returns results in wrong order
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor updated DERBY-4331:
-----------------------------------
Attachment: createDBsimplerVer2.txt
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;
> 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] Updated: (DERBY-4331) Join returns results in wrong order
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-4331:
----------------------------------
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;
> 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
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ 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.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;
> 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] Updated: (DERBY-4331) Join returns results in wrong order
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden updated DERBY-4331:
----------------------------------
Issue & fix info: [High Value Fix, Repro attached] (was: [Repro attached])
> 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] Updated: (DERBY-4331) Join returns results in wrong order
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-4331:
----------------------------------
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.
> 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
Posted by "Lily Wei (JIRA)" <ji...@apache.org>.
[ 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
Posted by "Lily Wei (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Updated: (DERBY-4331) Join returns results in wrong order
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor updated DERBY-4331:
-----------------------------------
Attachment: DERBY4331_additionalTests_diff.txt
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?
> 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, 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] Updated: (DERBY-4331) Join returns results in wrong order
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-4331:
----------------------------------
Attachment: wisconsin.diff
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.
> 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] Updated: (DERBY-4331) Join returns results in wrong order
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-4331:
----------------------------------
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
> 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] Resolved: (DERBY-4331) Join returns results in wrong order
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali resolved DERBY-4331.
-----------------------------------
Resolution: Fixed
Fix Version/s: 10.4.3.0
10.3.4.0
10.2.3.0
10.1.4.0
fix has been made in trunk and backported to 10.5, 10.4, 10.3, 10.2 and 10.1 branches.
> 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.4.0, 10.2.3.0, 10.3.4.0, 10.4.3.0, 10.5.2.0, 10.6.0.0
> Reporter: Knut Anders Hatlen
> Assignee: Mike Matrigali
> Fix For: 10.1.4.0, 10.2.3.0, 10.3.4.0, 10.4.3.0, 10.5.3.0, 10.6.0.0
>
> 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] Updated: (DERBY-4331) Join returns results in wrong order
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden updated DERBY-4331:
----------------------------------
Affects Version/s: (was: 10.1.3.3)
(was: 10.4.2.1)
(was: 10.3.3.1)
(was: 10.2.2.1)
Fix affect versions. updated wrong issue.
> 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
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ 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] Issue Comment Edited: (DERBY-4331) Join returns results in
wrong order
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12738591#action_12738591 ]
Mamta A. Satoor edited comment on DERBY-4331 at 8/3/09 2:19 PM:
----------------------------------------------------------------
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
was (Author: mamtas):
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.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
> 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] Updated: (DERBY-4331) Join returns results in wrong order
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-4331:
--------------------------------------
Fix Version/s: (was: 10.4.3.0)
(was: 10.3.4.0)
(was: 10.2.3.0)
(was: 10.1.4.0)
10.4.2.1
10.3.3.1
10.2.2.1
10.1.3.3
Updated fix versions to match the versions on the branches.
> 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.4.0, 10.2.3.0, 10.3.4.0, 10.4.3.0, 10.5.2.0, 10.6.0.0
> Reporter: Knut Anders Hatlen
> Assignee: Mike Matrigali
> Fix For: 10.1.3.3, 10.2.2.1, 10.3.3.1, 10.4.2.1, 10.5.3.0, 10.6.0.0
>
> 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] Updated: (DERBY-4331) Join returns results in wrong order
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden updated DERBY-4331:
----------------------------------
Affects Version/s: 10.6.0.0
10.4.2.1
10.3.3.1
10.2.2.1
10.1.3.3
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.
> 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] Updated: (DERBY-4331) Join returns results in wrong order
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-4331:
----------------------------------
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;
> 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] Updated: (DERBY-4331) Join returns results in wrong order
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor updated DERBY-4331:
-----------------------------------
Attachment: createDBsimpler.txt
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;
> 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, 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
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ 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] Updated: (DERBY-4331) Join returns results in wrong order
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-4331:
----------------------------------
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
> 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
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ 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] Updated: (DERBY-4331) Join returns results in wrong order
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-4331:
----------------------------------
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;
> 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] Updated: (DERBY-4331) Join returns results in wrong order
Posted by "Lily Wei (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Lily Wei updated DERBY-4331:
----------------------------
Attachment: orderby4331.zip
notorderby4331.zip
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
> 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] Updated: (DERBY-4331) Join returns results in wrong order
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-4331:
----------------------------------
Affects Version/s: 10.4.3.0
10.3.4.0
10.2.3.0
10.1.4.0
Fix Version/s: 10.6.0.0
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.
> 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.4.0, 10.2.3.0, 10.3.4.0, 10.4.3.0, 10.5.2.0, 10.6.0.0
> Reporter: Knut Anders Hatlen
> Assignee: Mike Matrigali
> Fix For: 10.6.0.0
>
> 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] Updated: (DERBY-4331) Join returns results in wrong order
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-4331:
--------------------------------------
Attachment: repro.sql
Attached a script that creates a sample database and executes the problematic query.
> 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] Updated: (DERBY-4331) Join returns results in wrong order
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-4331:
----------------------------------
Attachment: repro2_qryplan.txt
Here is query plan for bad sort result as generated by repro2.sql
> 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
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ 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] Issue Comment Edited: (DERBY-4331) Join returns results in
wrong order
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12739675#action_12739675 ]
Mike Matrigali edited comment on DERBY-4331 at 8/5/09 12:00 PM:
----------------------------------------------------------------
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.
was (Author: mikem):
not fully tested path 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.
> 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, 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.