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 12:33:31 GMT
Hello.

Now , updating my local working directory, I could found what was happend at 
Jack's site.

derbylang_report.txt at my site is uploaded next url.
http://www5.ocn.ne.jp/~tomohito/20050323/derbylang_report.txt


If anyone know, could you please give me some more information about next ?

>I think that there has been some sort of change in the way the Derby 
>handles binding when there are correlation names.

> 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.


The way I bind column in "resolveColumnReference" method was
based on original "bindOrderByColumn" method,
just replacing variable "correlationName" and "schemaName"  to
return value of ColumnReference.getSourceTableName() method and
ColumnReference.getSchemaName() method.

Considering there were no problem before my patch,
I think there exist some changes in ColumnReference ...


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