db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kathey Marsden (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-3926) Incorrect ORDER BY caused by index
Date Sun, 02 Aug 2009 15:15:14 GMT

     [ https://issues.apache.org/jira/browse/DERBY-3926?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Kathey Marsden updated DERBY-3926:
----------------------------------

    Fix Version/s: 10.2.2.1
                   10.1.3.3

> 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.1.3.3, 10.2.3.0, 10.3.3.1, 10.4.2.0
>         Environment: Checked into 10.3. This will still go to 10.2 and 10.1
>            Reporter: Tars Joris
>            Assignee: Mamta A. Satoor
>             Fix For: 10.1.3.3, 10.2.2.1, 10.3.3.1, 10.4.2.1, 10.5.2.0, 10.6.0.0
>
>         Attachments: d3926_repro.sql, derby-3926_10.3_mergeattempt.txt, derby-reproduce.zip,
DERBY3926_notforcheckin_patch1_051109_diff.txt, DERBY3926_notforcheckin_patch1_051109_stat.txt,
DERBY3926_notforcheckin_patch2_051109_diff.txt, DERBY3926_patch3_051509_diff.txt, DERBY3926_patch3_051509_stat.txt,
DERBY3926_patch4_051519_diff.txt, DERBY3926_patch4_051519_stat.txt, DERBY3926_patch5_052709_diff.txt,
DERBY3926_patch5_052709_stat.txt, DERBY3926_patch6_060309_diff.txt, DERBY3926_patch6_060309_stat.txt,
script3.sql, script3WithUserFriendlyIndexNames.sql, test-script.zip, wisconsin_10.1_result.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.


Mime
View raw message