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