[
https://issues.apache.org/jira/browse/DERBY-3926?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kathey Marsden updated DERBY-3926:
----------------------------------
Attachment: derby-3926_10.3_mergeattempt.txt
Attached is a patch for my first attempt at merging this to 10.3. This should
not be committed.
The new test is failing seeing extra columns in the query:
sql1 = "select c.col1, b.col1, a.col1 from a, b, c where a.col1=1 "+
"and b.col1 = 2 and c.col1=3 order by c.col1, b.col1, a.col1";
Probably one of those phantom column bugs needs to be backported too. I'll
take a look and see.
)
testAdditionalOrderByCases(org.apache.derbyTesting.functionTests.tests.lang.OrderByAndSortAvoidance)junit.framewo
sertionFailedError: Unexpected column count: expected:<3> but was:<5>
at org.apache.derbyTesting.junit.JDBC.assertFullResultSet(JDBC.java:750)
at org.apache.derbyTesting.junit.JDBC.assertFullResultSet(JDBC.java:700)
at org.apache.derbyTesting.junit.JDBC.assertFullResultSet(JDBC.java:658)
at
org.apache.derbyTesting.functionTests.tests.lang.OrderByAndSortAvoidance.testAdditionalOrderByCases(Order
SortAvoidance.java:10102)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:45)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
at
org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:89)
at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
at junit.extensions.TestSetup.run(TestSetup.java:23)
at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
)
testAdditionalOrderByCases(org.apache.derbyTesting.functionTests.tests.lang.OrderByAndSortAvoidance)junit.framewo
sertionFailedError: Unexpected column count: expected:<3> but was:<5>
at org.apache.derbyTesting.junit.JDBC.assertFullResultSet(JDBC.java:750)
at org.apache.derbyTesting.junit.JDBC.assertFullResultSet(JDBC.java:700)
at org.apache.derbyTesting.junit.JDBC.assertFullResultSet(JDBC.java:658)
at
org.apache.derbyTesting.functionTests.tests.lang.OrderByAndSortAvoidance.testAdditionalOrderByCases(Order
SortAvoidance.java:10102)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:45)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
at
org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:89)
at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
at junit.extensions.TestSetup.run(TestSetup.java:23)
at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
at junit.extensions.TestSetup.run(TestSetup.java:23)
at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
at junit.extensions.TestSetup.run(TestSetup.java:23)
at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
AILURES!!!
ests run: 10, Failures: 2, Errors: 0
> 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
> Reporter: Tars Joris
> Assignee: Mamta A. Satoor
> Fix For: 10.4.2.1, 10.5.1.2, 11.0.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
>
>
> 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.
