|
[
Permlink
| « Hide
]
Donatas Ciuksys added a comment - 08/Jan/08 01:26 PM
Actual database with data
SQL that fails:
SELECT MAX(Preke.pavadinimas) AS PAVADINIMAS, MAX(Preke.barkodas) AS BARKODAS, MAX(Preke.kiekis) AS KIEKIS, SUM(Irasas.faktinis_kiekis) AS FAKTINIS_KIEKIS, SUM(Irasas.faktinis_kiekis)-MAX(Preke.kiekis) AS TRUKUMAS FROM Preke LEFT JOIN Irasas ON Preke.unikalus_kodas = Irasas.unikalus_kodas JOIN Irasu_Blokas ON Irasas.irasu_blokas = Irasu_Blokas.id WHERE Irasu_Blokas.inventorizacija = 1 GROUP BY Irasas.unikalus_kodas ORDER BY ABS(SUM(Irasas.faktinis_kiekis) - MAX(Preke.kiekis)) DESC DDL script to create database (without data)
Thank you *very* much for the reproduction script, Donatas.
I ran your script against trunk and the bug reproduces there, as well. Using a sane build the query fails with the following assertion error: org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED column ordering error at org.apache.derby.shared.common.sanity.SanityManager.ASSERT(SanityManager.java:120) at org.apache.derby.impl.store.access.sort.MergeSort.initialize(MergeSort.java:535) at org.apache.derby.impl.store.access.sort.ExternalSortFactory.createSort(ExternalSortFactory.java:216) at org.apache.derby.impl.store.access.RAMTransaction.createSort(RAMTransaction.java:1711) at org.apache.derby.impl.sql.execute.SortResultSet.loadSorter(SortResultSet.java:301) at org.apache.derby.impl.sql.execute.SortResultSet.openCore(SortResultSet.java:268) The query runs without error at svn # 516453, but fails with # 516454. So it looks like this is related to A simpler way to reproduce the problem on trunk:
create table t1 (i int, j int, k int); insert into t1 values (1, 1, 2), (1, 3, 3), (2, 3, 1), (2, 2, 4); -- Works. select sum(k) as s from t1 group by i order by 1; -- Works. select sum(k) as s from t1 group by i order by s; -- Fails (ArrayIndexOutOfBounds / ASSERT); should fail with ERROR 42X77... select sum(k) as s from t1 group by i order by 2; -- Fails (ArrayIndexOutOfBounds / ASSERT) select sum(k) as s from t1 group by i order by abs(1); -- Fails (ArrayIndexOutOfBounds / ASSERT) select sum(k) as s from t1 group by i order by sum(k); Confirmed that all of these run without error before From what I could gather the failures mentioned in my previous comment were caused by two different problems, but both come down to the mistreatment (esp. ignoring) of columns in a ResultColumnList that have been "pulled up" for GROUP BY processing.
I'm attaching a patch, d3303_v1.patch, which makes two very small changes to OrderByColumn.java to account for "pulled" GROUP BY columns. With this patch applied all of the queries posted in my previous comment run as expected. I also ran derbyall and suites.All with ibm142 and they ran cleanly. I _think_ this constitutes a complete fix for the issue, but I am not all that familiar with the GROUP BY and ORDER BY column "pulling" logic that exists in Derby. I've skimmed through the relevant code for reviews but have never actually changed it myself. So if anyone out there can double-check the change, that would be great. It's only two lines of code change plus some additional comments, so it should in theory be quick... Hi Army, thanks for working on this problem. I'll try to have a look at your patch over the weekend (little busy today).
Hi Army, things lightened up a bit this afternoon so I had a look at the patch.
The comments are excellent! Thank you very much; they make it very clear. Your change seems correct to me. The code is also considerably cleaner by using visibleSize(), and reads better. Here are a few suggestions; none are worth holding up the patch for. - It might be nice to format your detailed comment for resolveAddedColumns() as a true javadoc comment for that method (which currently has no javadoc), rather than as an inline comment. - I recalled from my investigations with gets exercised by various combinations of including "*" in the select list, and using expressions in the order by list. So I tried a couple more test cases. They didn't find any additional problems, but here they are anyway, in case you think that they might add value to the regression suite: select d3303.i as old_i, sum(d3303.k), d3303.* from d3303 group by k, i, j order by j; select d3303.i as old_i, sum(d3303.k), d3303.* from d3303 group by k, i, j order by 4; select d3303.i as old_i, sum(d3303.k), d3303.* from d3303 group by k, i, j order by k+2; +1 to commit. Thank you very much for taking the time to review, Bryan. I appreciate it!
Your suggestions are great ones so I'm attaching a _v2 patch which incorporates them. I did modify the comments a bit to try to be more accurate about what "resolveAddedColumn()" really does--esp. for the example query, it doesn't really look for a column corresponding to "SUM(J)", rather it looks for a column corresponding to "this.addedColumnOffset", which (in the old example) ends up being SUM(J). I also changed the example to order by SUM(K) instead of SUM(J) in hopes of making it clearer (since there are two "SUM(J)"s in the old example). Hopefully the new comments are not worse than the old ones... Thanks again for the quick feedback! Patch 2 looks great, and changing that extra SUM(J) in the comments
does indeed make it clearer. I have no additional suggestions to offer. Many thanks for the timely and useful feedback, Bryan!
I committed the _v2 patch with svn # 628823: URL: http://svn.apache.org/viewvc?rev=628823&view=rev If tinderbox runs are clean over the next day or two, I plan to port this back to 10.3. The "svn merge" command from trunk to 10.3 had a conflict in orderby.sql. From what I can tell it was perhaps a line-ending issue, though I'm not real sure. In any event, I resolved the conflict and am attaching d3303_10_3_merge.patch, which is what I plan to commit.
Ported the fix for this issue back to 10.3 by commiting d3303_10_3_merge.patch with svn # 629535:
URL: http://svn.apache.org/viewvc?rev=629535&view=rev Donatas, if you have a moment can you check to see if your issue is fixed with this commit, and report back? Sorry, I have migrated to DB2 already (have changed jdbc driver, connection url, and so on), so I don't have development environment suitable for Derby any more. And I don't know, how to build version 10.3.2.2. I have attached db scripts, it shouldn't be difficult to run them and check whether this issue still holds.
Good luck. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
|
Bug
Closed
Blocker
ArrayIndexOutOfBoundsException at MergeSort.compare
