Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 10770 invoked from network); 23 Mar 2005 09:45:33 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 23 Mar 2005 09:45:33 -0000 Received: (qmail 69306 invoked by uid 500); 23 Mar 2005 09:45:32 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 69268 invoked by uid 500); 23 Mar 2005 09:45:32 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Development" Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 69254 invoked by uid 99); 23 Mar 2005 09:45:31 -0000 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests= X-Spam-Check-By: apache.org Received-SPF: pass (hermes.apache.org: local policy) Received: from basil.ocn.ne.jp (HELO smtp.basil.ocn.ne.jp) (222.146.51.83) by apache.org (qpsmtpd/0.28) with ESMTP; Wed, 23 Mar 2005 01:45:30 -0800 Received: from Arkat (p1210-adsah09honb5-acca.tokyo.ocn.ne.jp [218.224.7.210]) by smtp.basil.ocn.ne.jp (Postfix) with ESMTP id 33EEF4B03 for ; Wed, 23 Mar 2005 18:45:22 +0900 (JST) Received: from 127.0.0.1 (AVG SMTP 7.0.308 [266.8.0]); Wed, 23 Mar 2005 18:35:40 +0900 Message-ID: <000301c52f8b$ad86f630$2000a8c0@Arkat> From: "TomohitoNakayama" To: "Derby Development" References: <000201c50e79$f5c07670$2000a8c0@Arkat> <000301c50e82$081ec9e0$2000a8c0@Arkat> <003101c50e9a$4e419340$2000a8c0@Arkat> <420D0335.8080603@Sourcery.Org> <000901c51197$3e454c70$2000a8c0@Arkat> <000d01c5119a$f3564440$2000a8c0@Arkat> <001401c51253$f96c0a40$2000a8c0@Arkat> <4217CDCB.10102@sbcglobal.net> <001c01c516fe$4bbeb1e0$2000a8c0@Arkat> <423214B8.1070503@sourcery.org> <000c01c528ac$9505abf0$2000a8c0@Arkat> <42363FBF.30504@sbcglobal.net> <003d01c52bc7$71557780$2000a8c0@Arkat> <423B6CA6.2030508@sbcglobal.net> <000f01c52c4e$c6f20c30$2000a8c0@Arkat> <000201c52e28$abf2e900$2000a8c0@Arkat> <423F4BBF.9060604@sbcglobal.net> <001e01c52ecf$d5ab2bc0$2000a8c0@Arkat> <42411399.2010200@sbcglobal.net> Subject: Re: About improvement of DERBY-134 Date: Wed, 23 Mar 2005 18:35:35 +0900 X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Mime-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; format=flowed; charset=iso-8859-1; reply-type=response X-Virus-Checked: Checked X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N Thanks Jack. I have just begun to suspect logical conflict too. Difference between numberes of test suggest it. I will try svn update command. And test again. Best regards. /* Tomohito Nakayama tomoihto@rose.zero.ad.jp tomonaka@basil.ocn.ne.jp Naka http://www5.ocn.ne.jp/~tomohito/TopPage.html */ ----- Original Message ----- From: "Jack Klebanoff" To: "Derby Development" Sent: Wednesday, March 23, 2005 3:58 PM Subject: Re: About improvement of DERBY-134 > You may have to do an svn update to bring in the lastest version of the > source. I think that there has been some sort of change in the way the > Derby handles binding when there are correlation names. Perhaps it is the > combination of your changes and these other changes that cause the failure > in wisconsin.sql. > > It must have been made about a week ago. It has affected some other stuff > I am working on. I do not know who made the change, why, or exactly when. > > (Hopefully you will not have to do any merging). > > Perhaps the lang/orderby.sql tests need to be improved with some cases > that use table correlation names in the order by clause. e.g. > > select * from (values (2),(1)) as t(x) orderby t.x > select t1.id,t2.c3 from ta as t1 join tb as t2 on t1.id = t2.id order by > t2.c2,t1.id,t2.c3 > > This is a test of functionality that existed before your changes. Test > cases like these probably should have been in lang/orderby.sql before you > started. > > Jack Klebanoff > > TomohitoNakayama wrote: > >> I have tried your small.sql and result was as next. >> >> --These are evidence for improvement of 134 >> ij> select * from test_number order by abs(value); >> VALUE >> ----------- >> 1 >> 2 >> 3 >> >> 3 rows selected >> ij> select * from test_number order by value * -1; >> VALUE >> ----------- >> 3 >> 2 >> 1 >> >> 3 rows selected >> >> --This is what was written in small.sql >> ij> create table TENKTUP1 ( >> unique1 int not null, >> unique2 int not null, >> two int, >> four int, >> ten int, >> twenty int, >> onePercent int, >> tenPercent int, >> twentyPercent int, >> fiftyPercent int, >> unique3 int, >> evenOnePercent int, >> oddOnePercent int, >> stringu1 char(52) not null, >> stringu2 char(52) not null, >> string4 char(52) >> ); >> 0 rows inserted/updated/deleted >> ij> get cursor c as >> 'select * from TENKTUP1, (values 1) as t(x) >> where TENKTUP1.unique1 = t.x >> order by TENKTUP1.unique1, t.x'; >> ij> >> >> Unfortunately, I could not found any ... >> >> And I attached derbylang_report.txt to this mail. >> Can you find any clue in it ? >> Are there any difference between yours ? >> >> If could. I want to yourr derbylang_report... >> >> Best regards. >> >> /* >> >> Tomohito Nakayama >> tomoihto@rose.zero.ad.jp >> tomonaka@basil.ocn.ne.jp >> >> Naka >> http://www5.ocn.ne.jp/~tomohito/TopPage.html >> >> */ >> ----- Original Message ----- From: "Jack Klebanoff" >> >> To: "Derby Development" >> Sent: Tuesday, March 22, 2005 7:33 AM >> Subject: Re: About improvement of DERBY-134 >> >> >>> java org.apache.derbyTesting.functionTests.harness.RunSuite suiteName >>> writes a test report in suiteName_report.txt. This describes the >>> environment, prints a counts of tests that passed and failed, and lists >>> all the differences from expected in the failed tests. You can also find >>> lists of passed and failed tests in suiteName_pass.txt and >>> suiteName_fail.txt. You can also find outputs, diffs, databases, and >>> derby.log files for the failed tests, but you have to dig deeper into >>> the directories. >>> >>> When I ran the lang/wisconsin.sql test with your patch it failed. The >>> query >>> get cursor c as >>> 'select * from TENKTUP1, (values 1) as t(x) >>> where TENKTUP1.unique1 = t.x >>> order by TENKTUP1.unique1, t.x'; >>> close c; >>> failed to compile, but the test expected it to run. It worked before >>> applying the patch, and I believe that it should work. >>> >>> I boiled the problem down to a small SQL file, which I have attached. >>> That file should run without error under ij as long as database "testdb" >>> does not exist when you start ij. >>> >>> I believe that the problem is with the updated bind method in >>> OrderByNode. It does not seem to be able to handle correlation names >>> from the FROM list. In the example that failed "t" is not the name of an >>> actual table, but a correlation name used to name the "(values 1)" >>> virtual table. >>> >>> I tried changing OrderByColumn.bindOrderByColumn to call >>> expression.bindExcpression and then eliminating most of the code in >>> resolveColumnReference. However this does not work either. Then the >>> statement >>> values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by "SQLCol1" >>> (from the lang/orderby.sql test) fails. >>> >>> I will work on this some more. Perhaps you can continue looking at it >>> also. >>> >>> Jack >>> >>> TomohitoNakayama wrote: >>> >>>> I have tried derbylang test suite , but could not found error which >>>> was reported . >>>> >>>> What I found was just difference around "lang/floattypes.sql". >>>> I 'm not sure this is error or not yet. >>>> >>>> Back to reported bug, the next is the test sql in my wisconsin.sql. >>>> ==================== >>>> -- Values clause is a single-row result set, so should not cause >>>> optimizer >>>> -- to require sort. >>>> >>>> get cursor c as >>>> 'select * from TENKTUP1, (values 1) as t(x) >>>> where TENKTUP1.unique1 = t.x >>>> order by TENKTUP1.unique1, t.x'; >>>> close c; >>>> >>>> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); >>>> >>>> commit; >>>> >>>> -- Try with a join on unique column and order on non-unique column >>>> =================== >>>> I couldn't find difference between what in your mail. >>>> >>>> >>>> >>>> Next is svn-status of my wisconsin.sql. >>>> =================== >>>> $ svn status -v wisconsin.sql >>>> 157254 122528 djd wisconsin.sql >>>> =================== >>>> >>>> Is this caused by versioning problem of wisconsin.sql ...? >>>> >>>> /* >>>> >>>> Tomohito Nakayama >>>> tomoihto@rose.zero.ad.jp >>>> tomonaka@basil.ocn.ne.jp >>>> >>>> Naka >>>> http://www5.ocn.ne.jp/~tomohito/TopPage.html >>>> >>>> */ >>>> ----- Original Message ----- From: "TomohitoNakayama" >>>> >>>> To: "Derby Development" >>>> Sent: Saturday, March 19, 2005 3:42 PM >>>> Subject: Re: About improvement of DERBY-134 >>>> >>>> >>>>> Thank you for your checking. >>>>> >>>>> I did'nt know way to test whole sqls. >>>>> Sorry for insufficient test. >>>>> >>>>> Now I will try whole test. >>>>> >>>>> Best regards. >>>>> >>>>> /* >>>>> >>>>> Tomohito Nakayama >>>>> tomoihto@rose.zero.ad.jp >>>>> tomonaka@basil.ocn.ne.jp >>>>> >>>>> Naka >>>>> http://www5.ocn.ne.jp/~tomohito/TopPage.html >>>>> >>>>> */ >>>>> ----- Original Message ----- From: "Jack Klebanoff" >>>>> >>>>> To: "Derby Development" >>>>> Sent: Saturday, March 19, 2005 9:04 AM >>>>> Subject: Re: About improvement of DERBY-134 >>>>> >>>>> >>>>>> The derbyall test suite found a problem. The lang/wisconsin.sql test >>>>>> failed. The problem output was: >>>>>> >>>>>> ij> -- Values clause is a single-row result set, so should not cause >>>>>> optimizer >>>>>> -- to require sort. >>>>>> get cursor c as >>>>>> 'select * from TENKTUP1, (values 1) as t(x) >>>>>> where TENKTUP1.unique1 = t.x >>>>>> order by TENKTUP1.unique1, t.x'; >>>>>> ERROR 42X10: 'T' is not an exposed table name in the scope in which >>>>>> it >>>>>> appears. >>>>>> >>>>>> This error is incorrect. >>>>>> >>>>>> There must be a problem in the way that the patch binds the ORDER BY >>>>>> expressions. I don't have time to look more deeply into it now. >>>>>> >>>>>> You should probably run at least the derbylang test suite before >>>>>> submitting a patch for ORDER BY. >>>>>> >>>>>> To do this, change into an empty directory and run >>>>>> java org.apache.derbyTesting.functionTests.harness.RunSuite derbylang >>>>>> The derbylang suite takes about 90 minutes on my laptop. The derbyall >>>>>> suite takes 5 or 6 hours. >>>>>> >>>>>> In order to run just the wisconsin.sql test change into an empty >>>>>> directory and run >>>>>> java org.apache.derbyTesting.functionTests.harness.RunTest >>>>>> lang/wisconsin.sql >>>>>> >>>>>> Jack Klebanoff >>>>>> >>>>>> TomohitoNakayama wrote: >>>>>> >>>>>>> Hello. >>>>>>> >>>>>>> Thank for your checking. >>>>>>> I have solved the 2 problems. >>>>>>> Attached file is new patch. >>>>>>> >>>>>>> Best regards. >>>>>>> >>>>>>> /* >>>>>>> >>>>>>> Tomohito Nakayama >>>>>>> tomoihto@rose.zero.ad.jp >>>>>>> tomonaka@basil.ocn.ne.jp >>>>>>> >>>>>>> Naka >>>>>>> http://www5.ocn.ne.jp/~tomohito/TopPage.html >>>>>>> >>>>>>> */ >>>>>>> ----- Original Message ----- From: "Jack Klebanoff" >>>>>>> >>>>>>> To: "Derby Development" >>>>>>> Sent: Tuesday, March 15, 2005 10:51 AM >>>>>>> Subject: Re: About improvement of DERBY-134 >>>>>>> >>>>>>> >>>>>>>> The new patch looks much better. However, I found two problems, one >>>>>>>> serious and the other minor. >>>>>>>> >>>>>>>> The serious problem is that INTERSECT no longer works. The >>>>>>>> lang/intersect.sql test (part of the derbylang suite) fails. The >>>>>>>> problem >>>>>>>> is in the >>>>>>>> org.apache.derby.impl.sql.compile.IntersectOrExceptNode.pushOrderingDown >>>>>>>> >>>>>>>> method. It attempts to create OrderByColumns by calling >>>>>>>> nf.getNode( C_NodeTypes.ORDER_BY_COLUMN, >>>>>>>> ReuseFactory.getInteger( intermediateOrderByColumns[i] + 1), >>>>>>>> cm) >>>>>>>> This used to work. Now OrderByColumn.init throws a >>>>>>>> ClassCastException >>>>>>>> because it expects to be passed a ValueNode, not an Integer. >>>>>>>> >>>>>>>> IntersectOrExceptNode.pushOrderingDown has to be changed to pass a >>>>>>>> ValueNode. I think that >>>>>>>> nf.getNode( C_NodeTypes.ORDER_BY_COLUMN, >>>>>>>> nf.getNode( C_NodeTypes.INT_CONSTANT_NODE, >>>>>>>> ReuseFactory.getInteger( intermediateOrderByColumns[i] + 1), >>>>>>>> cm), >>>>>>>> cm) >>>>>>>> works. >>>>>>>> >>>>>>>> The minor problem is that the javadoc for OrderByColumn.init( >>>>>>>> Object >>>>>>>> expression) documents a "dummy" parameter that no longer exists. >>>>>>>> >>>>>>>> Jack Klebanoff >>>>>>>> >>>>>>>> TomohitoNakayama wrote: >>>>>>>> >>>>>>>>> Hello. >>>>>>>>> >>>>>>>>> I have finished coding and testing in orderby.sql. >>>>>>>>> I'm not sure test is enough. >>>>>>>>> >>>>>>>>> Would you please review it ? >>>>>>>>> >>>>>>>>> Best regards. >>>>>>>>> >>>>>>>>> /* >>>>>>>>> >>>>>>>>> Tomohito Nakayama >>>>>>>>> tomoihto@rose.zero.ad.jp >>>>>>>>> tomonaka@basil.ocn.ne.jp >>>>>>>>> >>>>>>>>> Naka >>>>>>>>> http://www5.ocn.ne.jp/~tomohito/TopPage.html >>>>>>>>> >>>>>>>>> */ >>>>>>>>> ----- Original Message ----- From: "Satheesh Bandaram" >>>>>>>>> >>>>>>>>> To: "Derby Development" >>>>>>>>> Sent: Saturday, March 12, 2005 6:59 AM >>>>>>>>> Subject: Re: About improvement of DERBY-134 >>>>>>>>> >>>>>>>>> >>>>>>>>>> Hi Tomohito Nakayama, >>>>>>>>>> >>>>>>>>>> Just wanted to check how you are progressing on the patch update, >>>>>>>>>> following comments by myself and Jack. I do think you are working >>>>>>>>>> on an >>>>>>>>>> important enhancement that not only yourself but other >>>>>>>>>> developpers >>>>>>>>>> have >>>>>>>>>> expressed interest in. I strongly encourage you to continue >>>>>>>>>> working on >>>>>>>>>> this and post any questions or comments you might have. You are >>>>>>>>>> pretty >>>>>>>>>> close to addressing all issues. >>>>>>>>>> >>>>>>>>>> I am willing to help, if you need any, to continue taking this >>>>>>>>>> further. >>>>>>>>>> >>>>>>>>>> Satheesh >>>>>>>>>> >>>>>>>>>> TomohitoNakayama wrote: >>>>>>>>>> >>>>>>>>>>> Hello. >>>>>>>>>>> Thanks for your reviewing. >>>>>>>>>>> >>>>>>>>>>> About 1: >>>>>>>>>>> Handling any sortKey as expression is better structure. >>>>>>>>>>> A little challenging but worth for it. >>>>>>>>>>> I will try. >>>>>>>>>>> >>>>>>>>>>> About 2: >>>>>>>>>>> Uh oh. >>>>>>>>>>> Bug about starting value of element indexing in >>>>>>>>>>> ResultColumnList .... >>>>>>>>>>> Test of comma separated lists of ORDER BY expressions in >>>>>>>>>>> orderby.sql >>>>>>>>>>> was needed..... >>>>>>>>>>> >>>>>>>>>>> About 3: >>>>>>>>>>> I see. >>>>>>>>>>> It seems that it is certainly needed to add test case . >>>>>>>>>>> >>>>>>>>>>> I will continue this issue. >>>>>>>>>>> Best regards. >>>>>>>>>>> >>>>>>>>>>> /* >>>>>>>>>>> >>>>>>>>>>> Tomohito Nakayama >>>>>>>>>>> tomoihto@rose.zero.ad.jp >>>>>>>>>>> tomonaka@basil.ocn.ne.jp >>>>>>>>>>> >>>>>>>>>>> Naka >>>>>>>>>>> http://www5.ocn.ne.jp/~tomohito/TopPage.html >>>>>>>>>>> >>>>>>>>>>> */ >>>>>>>>>>> ----- Original Message ----- From: "Jack Klebanoff" >>>>>>>>>>> >>>>>>>>>>> To: "Derby Development" >>>>>>>>>>> Sent: Sunday, February 20, 2005 8:37 AM >>>>>>>>>>> Subject: Re: About improvement of DERBY-134 >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>>> TomohitoNakayama wrote: >>>>>>>>>>>> >>>>>>>>>>>>> Hello. >>>>>>>>>>>>> >>>>>>>>>>>>> I have put some LOOKAHEAD to sqlgrammer.jj and >>>>>>>>>>>>> add some test pattern to orderby.sql. >>>>>>>>>>>>> >>>>>>>>>>>>> Would someone review patch please ? >>>>>>>>>>>>> >>>>>>>>>>>>> Best regards. >>>>>>>>>>>>> >>>>>>>>>>>>> /* >>>>>>>>>>>>> >>>>>>>>>>>>> Tomohito Nakayama >>>>>>>>>>>>> tomoihto@rose.zero.ad.jp >>>>>>>>>>>>> tomonaka@basil.ocn.ne.jp >>>>>>>>>>>>> >>>>>>>>>>>>> Naka >>>>>>>>>>>>> http://www5.ocn.ne.jp/~tomohito/TopPage.html >>>>>>>>>>>>> >>>>>>>>>>>>> */ >>>>>>>>>>>>> ----- Original Message ----- From: "TomohitoNakayama" >>>>>>>>>>>>> >>>>>>>>>>>>> To: "Derby Development" >>>>>>>>>>>>> Sent: Sunday, February 13, 2005 4:09 PM >>>>>>>>>>>>> Subject: Re: About improvement of DERBY-134 >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>>> Sorry. >>>>>>>>>>>>>> Mistaken. >>>>>>>>>>>>>> >>>>>>>>>>>>>> LOOKAHEAD().... >>>>>>>>>>>>>> >>>>>>>>>>>>>> /* >>>>>>>>>>>>>> >>>>>>>>>>>>>> Tomohito Nakayama >>>>>>>>>>>>>> tomoihto@rose.zero.ad.jp >>>>>>>>>>>>>> tomonaka@basil.ocn.ne.jp >>>>>>>>>>>>>> >>>>>>>>>>>>>> Naka >>>>>>>>>>>>>> http://www5.ocn.ne.jp/~tomohito/TopPage.html >>>>>>>>>>>>>> >>>>>>>>>>>>>> */ >>>>>>>>>>>>>> ----- Original Message ----- From: "TomohitoNakayama" >>>>>>>>>>>>>> >>>>>>>>>>>>>> To: "Derby Development" >>>>>>>>>>>>>> Sent: Sunday, February 13, 2005 3:42 PM >>>>>>>>>>>>>> Subject: Re: About improvement of DERBY-134 >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>>> Hello. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Thank's for your reviewing. >>>>>>>>>>>>>>> Grammer ambiguity is very critical problem .... >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> I will try to put LOOKUP() and consider about testing.. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> #World is not simple as I wish to be..... >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Best regards. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> /* >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Tomohito Nakayama >>>>>>>>>>>>>>> tomoihto@rose.zero.ad.jp >>>>>>>>>>>>>>> tomonaka@basil.ocn.ne.jp >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Naka >>>>>>>>>>>>>>> http://www5.ocn.ne.jp/~tomohito/TopPage.html >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> */ >>>>>>>>>>>>>>> ----- Original Message ----- From: Satheesh Bandaram >>>>>>>>>>>>>>> To: Derby Development >>>>>>>>>>>>>>> Sent: Saturday, February 12, 2005 4:10 AM >>>>>>>>>>>>>>> Subject: Re: About improvement of DERBY-134 >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> I think the patch is a good start. But more work needs to be >>>>>>>>>>>>>>> done. >>>>>>>>>>>>>>> Based on a quick review, some of the items to be completed >>>>>>>>>>>>>>> are: >>>>>>>>>>>>>>> (there may be more) >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Grammar ambiguity. SortKey() has grammar ambiguity the way >>>>>>>>>>>>>>> the >>>>>>>>>>>>>>> patch >>>>>>>>>>>>>>> is written. Since orderby expression and orderby column can >>>>>>>>>>>>>>> both >>>>>>>>>>>>>>> start with an identifier, this causes ambiguity. Need to >>>>>>>>>>>>>>> rewrite or >>>>>>>>>>>>>>> add lookup to avoid this. >>>>>>>>>>>>>>> Current patch doesn't seem to support all expressions, Ex: >>>>>>>>>>>>>>> select i >>>>>>>>>>>>>>> from t1 order by i/2. So, needs more work. >>>>>>>>>>>>>>> Add more test cases and test outputs to show changed >>>>>>>>>>>>>>> behavior. >>>>>>>>>>>>>>> You >>>>>>>>>>>>>>> could add test cases to orderby.sql test that is already >>>>>>>>>>>>>>> part of >>>>>>>>>>>>>>> functionTests/tests/lang. >>>>>>>>>>>>>>> I do encourage you to continue work on this ... >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Satheesh >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> TomohitoNakayama wrote: >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> I tried to solve DERBY-134. >>>>>>>>>>>>>>> Patch is attached to this mail. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> /* >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Tomohito Nakayama >>>>>>>>>>>>>>> tomoihto@rose.zero.ad.jp >>>>>>>>>>>>>>> tomonaka@basil.ocn.ne.jp >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Naka >>>>>>>>>>>>>>> http://www5.ocn.ne.jp/~tomohito/TopPage.html >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> */ >>>>>>>>>>>>>>> ----- Original Message ----- From: "TomohitoNakayama" >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> To: "Derby Development" >>>>>>>>>>>>>>> Sent: Wednesday, February 09, 2005 5:33 PM >>>>>>>>>>>>>>> Subject: Re: About improvement of DERBY-134 >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Woops. >>>>>>>>>>>>>>> Mistaken. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> That's "DERBY-124 Sorted string columns are sorted in a case >>>>>>>>>>>>>>> sensitive way" >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> That's "DERBY-134 Sorted string columns are sorted in a case >>>>>>>>>>>>>>> sensitive way" >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> /* >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Tomohito Nakayama >>>>>>>>>>>>>>> tomoihto@rose.zero.ad.jp >>>>>>>>>>>>>>> tomonaka@basil.ocn.ne.jp >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Naka >>>>>>>>>>>>>>> http://www5.ocn.ne.jp/~tomohito/TopPage.html >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> */ >>>>>>>>>>>>>>> ----- Original Message ----- From: "TomohitoNakayama" >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> To: >>>>>>>>>>>>>>> Sent: Wednesday, February 09, 2005 4:35 PM >>>>>>>>>>>>>>> Subject: About improvement of DERBY-134 >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Hello. >>>>>>>>>>>>>>> My name is Naka. >>>>>>>>>>>>>>> I'm very newbie in derby community. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> I'm now seeing report for derby in ASF Jira. >>>>>>>>>>>>>>> And found a interesting issue. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> That's "DERBY-124 Sorted string columns are sorted in a case >>>>>>>>>>>>>>> sensitive way" >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> This issue seems to mean that we can't use complex item in >>>>>>>>>>>>>>> order >>>>>>>>>>>>>>> clause. >>>>>>>>>>>>>>> #That title was difficult to understand a bit .... >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Solving this isn't useful? >>>>>>>>>>>>>>> Especially when we manipulate DBMS by hand. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> What I think we need to do is as next: >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> 1) Allow additiveExpression() in sortKey() in >>>>>>>>>>>>>>> "sqlgrammer.jj". 2) >>>>>>>>>>>>>>> Make OrderByColumn class to support additiveExpression. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Best regards. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> /* >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Tomohito Nakayama >>>>>>>>>>>>>>> tomoihto@rose.zero.ad.jp >>>>>>>>>>>>>>> tomonaka@basil.ocn.ne.jp >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Naka >>>>>>>>>>>>>>> http://www5.ocn.ne.jp/~tomohito/TopPage.html >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> */ >>>>>>>>>>>>>>> >>>>>>>>>>>> I have worked on Derby/Cloudscape for a few years and have even >>>>>>>>>>>> fixed >>>>>>>>>>>> one or two ORDER BY bugs in the past. I have reviewed your >>>>>>>>>>>> patch. >>>>>>>>>>>> It is >>>>>>>>>>>> close, but I have some problems with it. >>>>>>>>>>>> >>>>>>>>>>>> 1. sqlgrammar.jj. I think that creating a new method, >>>>>>>>>>>> isNonReservedKeyword() to determine whether a token is a >>>>>>>>>>>> non-reserved >>>>>>>>>>>> keyword or not, is a maintenance problem. Whenever we add a new >>>>>>>>>>>> non-reserved keyword we must add it to the list of tokens, to >>>>>>>>>>>> nonReservedKeyword(), and now to isNonReservedKeyword(). Having >>>>>>>>>>>> to add >>>>>>>>>>>> it in two places is difficult enough to discover or remember. >>>>>>>>>>>> If we >>>>>>>>>>>> need >>>>>>>>>>>> isNonReservedKeyword then we should find a way of combining >>>>>>>>>>>> nonReservedKeyword and isNonReservedKeyword so that only one >>>>>>>>>>>> of them >>>>>>>>>>>> keeps the list of non-reserved key words. >>>>>>>>>>>> >>>>>>>>>>>> It is not necessary for the parser to recognize 3 cases of >>>>>>>>>>>> ORDER BY >>>>>>>>>>>> sort >>>>>>>>>>>> key type. A column name is just one kind of . If >>>>>>>>>>>> the >>>>>>>>>>>> parser >>>>>>>>>>>> treats it as an expression we should still get the right >>>>>>>>>>>> ordering. I >>>>>>>>>>>> think that it would better if the parser did so. The >>>>>>>>>>>> OrderByColumn >>>>>>>>>>>> class >>>>>>>>>>>> can special case a simple column reference expression, as an >>>>>>>>>>>> optimization. This considerably simplifies parsing sort keys. >>>>>>>>>>>> >>>>>>>>>>>> The only sort key type that has to be handled specially is that >>>>>>>>>>>> of an >>>>>>>>>>>> integer constant. That specifies one of the select list columns >>>>>>>>>>>> as the >>>>>>>>>>>> sort key. This case can be recognized in the parser, as is done >>>>>>>>>>>> in the >>>>>>>>>>>> patch, or it can be recognized in OrderByColumn. In this >>>>>>>>>>>> alternative the >>>>>>>>>>>> parser always creates OrderByColumn nodes with the sort key >>>>>>>>>>>> given >>>>>>>>>>>> by an >>>>>>>>>>>> expression (a ValueNode). At bind time OrderByColumn can >>>>>>>>>>>> determine >>>>>>>>>>>> whether the sort key expression is an integer constant, and if >>>>>>>>>>>> so >>>>>>>>>>>> treat >>>>>>>>>>>> it as a column position. >>>>>>>>>>>> >>>>>>>>>>>> The two alternatives differ in the way that they treat constant >>>>>>>>>>>> integer >>>>>>>>>>>> expressions like "ORDER BY 2-1". The patch orders the rows by >>>>>>>>>>>> the >>>>>>>>>>>> constant 1, which is not usefull. With the patch "ORDER BY 2-1 >>>>>>>>>>>> ASC" >>>>>>>>>>>> and >>>>>>>>>>>> "ORDER BY 2-1 DESC" produce the same ordering. If OrderByColumn >>>>>>>>>>>> treated >>>>>>>>>>>> an integer constant sort key expression as a result column >>>>>>>>>>>> position >>>>>>>>>>>> then >>>>>>>>>>>> "ORDER BY 2-1" would cause the rows to be ordered on the first >>>>>>>>>>>> result >>>>>>>>>>>> column, which I think is more usefull. >>>>>>>>>>>> >>>>>>>>>>>> 2. OrderByColumn. I think that there is a mistake in the patch >>>>>>>>>>>> to >>>>>>>>>>>> the >>>>>>>>>>>> bindOrderByColumn method of class OrderByColumn. >>>>>>>>>>>> >>>>>>>>>>>> The new code is >>>>>>>>>>>> }else if(expression != null){ >>>>>>>>>>>> >>>>>>>>>>>> ResultColumn col = null; >>>>>>>>>>>> int i = 0; >>>>>>>>>>>> >>>>>>>>>>>> for(i = 0; >>>>>>>>>>>> i < targetCols.size(); >>>>>>>>>>>> i ++){ >>>>>>>>>>>> col = targetCols.getOrderByColumn(i); >>>>>>>>>>>> if(col != null && >>>>>>>>>>>> col.getExpression() == expression){ >>>>>>>>>>>> break; >>>>>>>>>>>> } >>>>>>>>>>>> } >>>>>>>>>>>> >>>>>>>>>>>> Method ResultColumnList.getOrderByColumn( int) uses 1 >>>>>>>>>>>> indexing. The >>>>>>>>>>>> patch assumes 0 indexing. So the loop really should be "for( i >>>>>>>>>>>> = 1; >>>>>>>>>>>> i <= >>>>>>>>>>>> targetCols.size(); i++)". >>>>>>>>>>>> >>>>>>>>>>>> (Java likes 0 indexing while SQL likes 1 indexing. So some >>>>>>>>>>>> parts of >>>>>>>>>>>> the >>>>>>>>>>>> Derby code use 0 indexing while others use 1 indexing. The >>>>>>>>>>>> resulting >>>>>>>>>>>> confusion has caught most of us at one time or another). >>>>>>>>>>>> >>>>>>>>>>>> The result is that when the sort key is an expression >>>>>>>>>>>> OrderByColumn.pullUpOrderByColumn adds it to the end of the >>>>>>>>>>>> target >>>>>>>>>>>> list, >>>>>>>>>>>> but OrderByColumn.bindOrderByColumn doesn't find it. >>>>>>>>>>>> OrderByColumn.bindOrderByColumn tests whether the second last >>>>>>>>>>>> column in >>>>>>>>>>>> the target list is orderable. This is usually not right. >>>>>>>>>>>> Consider >>>>>>>>>>>> the >>>>>>>>>>>> following SQL: >>>>>>>>>>>> >>>>>>>>>>>> create table tblob( id int, b blob(1000)); >>>>>>>>>>>> select id,b from tblob order by abs(id); >>>>>>>>>>>> select b,id from tblob order by abs(id); >>>>>>>>>>>> >>>>>>>>>>>> The first SELECT raises the error "ERROR X0X67: Columns of type >>>>>>>>>>>> 'BLOB' >>>>>>>>>>>> may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, >>>>>>>>>>>> INTERSECT, >>>>>>>>>>>> EXCEPT or DISTINCT, because comparisons are not supported for >>>>>>>>>>>> that >>>>>>>>>>>> type". The second SELECT executes properly. >>>>>>>>>>>> >>>>>>>>>>>> 3. Testing. I would like to see some additional tests: the >>>>>>>>>>>> failing >>>>>>>>>>>> case >>>>>>>>>>>> above; ORDER BY expressions combined with ASC and DESC, to >>>>>>>>>>>> ensure >>>>>>>>>>>> that >>>>>>>>>>>> the compiler handles ASC and DESC after a sort key, and comma >>>>>>>>>>>> separated >>>>>>>>>>>> lists of ORDER BY expressions. >>>>>>>>>>>> >>>>>>>>>>>> Jack >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>> >>> >> >> >> -------------------------------------------------------------------------------- >> >> >> >>> connect 'jdbc:derby:testdb;create=true'; >>> create table TENKTUP1 ( >>> unique1 int not null, >>> unique2 int not null, >>> two int, >>> four int, >>> ten int, >>> twenty int, >>> onePercent int, >>> tenPercent int, >>> twentyPercent int, >>> fiftyPercent int, >>> unique3 int, >>> evenOnePercent int, >>> oddOnePercent int, >>> stringu1 char(52) not null, >>> stringu2 char(52) not null, >>> string4 char(52) >>> ); >>> >>> get cursor c as >>> 'select * from TENKTUP1, (values 1) as t(x) >>> where TENKTUP1.unique1 = t.x >>> order by TENKTUP1.unique1, t.x'; >>> >>> >> >> > > > > > -- > No virus found in this incoming message. > Checked by AVG Anti-Virus. > Version: 7.0.308 / Virus Database: 266.8.0 - Release Date: 2005/03/21 > > -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.8.0 - Release Date: 2005/03/21