You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Mike Matrigali (JIRA)" <ji...@apache.org> on 2009/05/21 17:20:45 UTC
[jira] Updated: (DERBY-4240) An index cause SQL ORDER BY can't
return correct result
[ https://issues.apache.org/jira/browse/DERBY-4240?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-4240:
----------------------------------
This reproduces for me in trunk using the included script against both ibm16 and ibm15 jvms.
Here is the query plan:
2009-05-21 15:09:30.671 GMT Thread[main,5,main] (XID = 288), (SESSIONID = 1), SELECT t1.id, t1.name FROM test2 t2 INNER JOIN test1 t1 ON t2.rel_id = t
1.id WHERE t2.entity_id = 1 ORDER BY t1.id ASC ******* Project-Restrict ResultSet (6):
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: 3.00
optimizer estimated cost: 62.07
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: 3.00
optimizer estimated cost: 62.07
Left result set:
Index Row to Base Row ResultSet for TEST2:
Number of opens = 1
Rows seen = 3
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: 3.00
optimizer estimated cost: 40.57
Index Scan ResultSet for TEST2 using index IDX_TEST2 at read committed isolation level using share row locking chosen by the optimizer
Number of opens = 1
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=2
Number of deleted rows visited=0
Number of pages visited=1
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:
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
optimizer estimated row count: 3.00
optimizer estimated cost: 40.57
Right result set:
Index Row to Base Row ResultSet for TEST1:
Number of opens = 3
Rows seen = 3
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: 3.00
optimizer estimated cost: 21.50
Index Scan ResultSet for TEST1 using constraint SQL090521080928600 at read committed isolation level using share row locking cho
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=2
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:
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
optimizer estimated row count: 3.00
optimizer estimated cost: 21.50
> An index cause SQL ORDER BY can't return correct result
> -------------------------------------------------------
>
> Key: DERBY-4240
> URL: https://issues.apache.org/jira/browse/DERBY-4240
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.2.0
> Reporter: Simon Meng
>
> Following snippet is a SQL example program. It can reproduce a database issue.
> DROP TABLE test1;
> DROP TABLE test2;
> CREATE TABLE test1 (id BIGINT NOT NULL, name VARCHAR(255), PRIMARY KEY (id));
> CREATE TABLE test2 (entity_id BIGINT, rel_id BIGINT);
> CREATE INDEX idx_test2 ON test2 (entity_id);
> INSERT INTO test1 (id, name) VALUES (102, 'Tom');
> INSERT INTO test1 (id, name) VALUES (1, null);
> INSERT INTO test1 (id, name) VALUES (103, 'Jerry');
> INSERT INTO test1 (id, name) VALUES (101, 'Pupy');
> INSERT INTO test2 (entity_id, rel_id) VALUES (1, 102);
> INSERT INTO test2 (entity_id, rel_id) VALUES (1, 101);
> INSERT INTO test2 (entity_id, rel_id) VALUES (1, 103);
> SELECT t1.id, t1.name FROM test2 t2 INNER JOIN test1 t1 ON t2.rel_id = t1.id WHERE t2.entity_id = 1 ORDER BY t1.id ASC;
> The expected result should be
> ID NAME
> --------------------------
> 101 Pupy
> 102 Tom
> 103 Jerry
> When running the program, I got below result.
> ID NAME
> --------------------------
> 102 Tom
> 101 Pupy
> 103 Jerry
> The result is obviously wrong. Using ORDER BY ASC does not get expected result. I found ORDER BY DESC works fine.
> Note: there is an index (idx_test2). This index affects the SQL query. If the index is dropped, ORDER BY ASC can return correct result..
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.