db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "TomohitoNakayama" <tomon...@basil.ocn.ne.jp>
Subject Re: About improvement of DERBY-134
Date Wed, 23 Mar 2005 09:35:35 GMT
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" <klebanoff-derby@sbcglobal.net>
To: "Derby Development" <derby-dev@db.apache.org>
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" 
>> <klebanoff-derby@sbcglobal.net>
>> To: "Derby Development" <derby-dev@db.apache.org>
>> 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"
>>>> <tomonaka@basil.ocn.ne.jp>
>>>> To: "Derby Development" <derby-dev@db.apache.org>
>>>> 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"
>>>>> <klebanoff-derby@sbcglobal.net>
>>>>> To: "Derby Development" <derby-dev@db.apache.org>
>>>>> 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"
>>>>>>> <klebanoff-derby@sbcglobal.net>
>>>>>>> To: "Derby Development" <derby-dev@db.apache.org>
>>>>>>> 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"
>>>>>>>>> <satheesh@sourcery.org>
>>>>>>>>> To: "Derby Development" <derby-dev@db.apache.org>
>>>>>>>>> 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"
>>>>>>>>>>> <klebanoff-derby@sbcglobal.net>
>>>>>>>>>>> To: "Derby Development" <derby-dev@db.apache.org>
>>>>>>>>>>> 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"
>>>>>>>>>>>>> <tomonaka@basil.ocn.ne.jp>
>>>>>>>>>>>>> To: "Derby Development" <derby-dev@db.apache.org>
>>>>>>>>>>>>> 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"
>>>>>>>>>>>>>> <tomonaka@basil.ocn.ne.jp>
>>>>>>>>>>>>>> To: "Derby Development" <derby-dev@db.apache.org>
>>>>>>>>>>>>>> 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"
>>>>>>>>>>>>>>> <tomonaka@basil.ocn.ne.jp>
>>>>>>>>>>>>>>> To: "Derby Development" <derby-dev@db.apache.org>
>>>>>>>>>>>>>>> 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"
>>>>>>>>>>>>>>> <tomonaka@basil.ocn.ne.jp>
>>>>>>>>>>>>>>> To: <derby-dev@db.apache.org>
>>>>>>>>>>>>>>> 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 <expression>. 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


Mime
View raw message