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