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