[
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.
