[
https://issues.apache.org/jira/browse/DERBY-3926?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12683992#action_12683992
]
Mamta A. Satoor commented on DERBY-3926:
----------------------------------------
The query plan when the correct results are returned is as follows
Statement Name:
null
Statement Text:
SELECT table1.id, m0.value, m1.value FROM table1, table2 m0, table2 m1 W
HERE table1.id=m0.id AND
m0.name='PageSequenceId' AND table1.id=m1.id AND m1.name='PostComponentId' AND m
1.value='21857' ORDER BY
m0.value
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 = 3
Rows returned = 3
Eliminate duplicates = false
In sorted order = false
Sort information:
Number of rows input=3
Number of rows output=3
Sort type=internal
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: 8.26
Source result set:
Project-Restrict ResultSet (9):
Number of opens = 1
Rows seen = 3
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.20
optimizer estimated cost: 8.26
Source result set:
Nested Loop Exists Join ResultSet:
Number of opens = 1
Rows seen from the left = 3
Rows seen from the right = 3
Rows filtered = 0
Rows returned = 3
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: 8.26
Left result set:
Nested Loop Exists Join ResultSet:
Number of opens = 1
Rows seen from the left = 3
Rows seen from the right = 3
Rows filtered = 0
Rows returned = 3
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.29
Left result set:
Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 3
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.20
optimizer estimated cost: 6.9
7
Source result set:
Index Row to Base Row ResultSet for TABL
E2:
Number of opens = 1
Rows seen = 3
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.20
optimizer estimated cost:
6.97
Index Scan ResultSet for
TABLE2using index KEY3 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 (millis
econds) = 0
open time (milliseconds)
= 0
next time (milliseconds)
= 0
close time (milliseconds
) = 0
next time in millisecond
s/row = 0
scan information:
Bit set of columns fetch
ed=All
Number of columns fetche
d=2
Number of deleted rows v
isited=0
Number of pages visited=
2
Number of rows qualified
=3
Number of rows visited=4
Scan type=btree
Tree height=2
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
optimizer estimated row
count: 0.20
optimizer estimated cost
: 6.97
Right result set:
Index Scan ResultSet for TABLE1 using
constraint SQL090320113016460 at read committed isolation level using share row
locking chosen by the optimizer
Number of opens = 3
Rows seen = 3
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=3
Number of rows qualified=3
Number of rows visited=3
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.20
optimizer estimated cost:
0.31
Right result set:
Index Row to Base Row ResultSet for TABLE2:
Number of opens = 3
Rows seen = 3
Columns accessed from heap = {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: 0.97
Index Scan ResultSet for TABLE2 using
constraint SQL090320113016670 at read committed isolation level using share row
locking chosen by the optimizer
Number of opens = 3
Rows seen = 3
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=6
Number of rows qualified=3
Number of rows visited=3
Scan type=btree
Tree height=2
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.20
optimizer estimated cost:
0.97
> Incorrect ORDER BY caused by index
> ----------------------------------
>
> Key: DERBY-3926
> URL: https://issues.apache.org/jira/browse/DERBY-3926
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.2.0
> Reporter: Tars Joris
> Attachments: derby-reproduce.zip
>
>
> I think I found a bug in Derby that is triggered by an index on a large
> column: VARCHAR(1024). I know it is generally not a good idea to have an
> index on such a large column.
> I have a table (table2) with a column "value", my query orders on this column
> but the result is not sorted. It is sorted if I remove the index on that
> column.
> The output of the attached script is as follows (results should be ordered on
> the middle column):
> ID |VALUE |VALUE
> ----------------------------------------------
> 2147483653 |000002 |21857
> 2147483654 |000003 |21857
> 4294967297 |000001 |21857
> While I would expect:
> ID |VALUE |VALUE
> ----------------------------------------------
> 4294967297 |000001 |21857
> 2147483653 |000002 |21857
> 2147483654 |000003 |21857
> This is the definition:
> CREATE TABLE table1 (id BIGINT NOT NULL, PRIMARY KEY(id));
> CREATE INDEX key1 ON table1(id);
> CREATE TABLE table2 (id BIGINT NOT NULL, name VARCHAR(40) NOT NULL, value
> VARCHAR(1024), PRIMARY KEY(id, name));
> CREATE UNIQUE INDEX key2 ON table2(id, name);
> CREATE INDEX key3 ON table2(value);
> This is the query:
> SELECT table1.id, m0.value, m1.value
> FROM table1, table2 m0, table2 m1
> WHERE table1.id=m0.id
> AND m0.name='PageSequenceId'
> AND table1.id=m1.id
> AND m1.name='PostComponentId'
> AND m1.value='21857'
> ORDER BY m0.value;
> The bug can be reproduced by just executing the attached script with the
> ij-tool.
> Note that the result of the query becomes correct when enough data is
> changed. This prevented me from creating a smaller example.
> See the attached file "derby-reproduce.zip" for sysinfo, derby.log and
> script.sql.
> Michael Segel pointed out:
> "It looks like its hitting the index ordering on id,name from table 2 and is
> ignoring the order by clause."
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
