db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jack Klebanoff <klebanoff-de...@sbcglobal.net>
Subject Re: About improvement of DERBY-134
Date Tue, 22 Mar 2005 23:46:05 GMT
I tried re-applying your patch, rebuilding, and re-running the derbylang
suite. The result was the same: the lang/wisconsin.sql test failed.
Interestingly my derbylang suite ran 1 more test than was reported in
your derbylang_report.txt at
http://www5.ocn.ne.jp/~tomohito/derbylang_report.txt. I do not know why
it worked for you. The stack trace from the derby.log file was:

Statement is: 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.
at
org.apache.derby.iapi.error.StandardException.newException(StandardException.java:311)
at
org.apache.derby.impl.sql.compile.OrderByColumn.resolveColumnReference(OrderByColumn.java:312)
at
org.apache.derby.impl.sql.compile.OrderByColumn.bindOrderByColumn(OrderByColumn.java:147)
at
org.apache.derby.impl.sql.compile.OrderByList.bindOrderByColumns(OrderByList.java:153)
at org.apache.derby.impl.sql.compile.CursorNode.bind(CursorNode.java:255)
at
org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:332)
at
org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:107)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:688)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:501)
at
org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(EmbedStatement.java:130)
at org.apache.derby.impl.tools.ij.ij.GetCursorStatement(ij.java:1102)
at org.apache.derby.impl.tools.ij.ij.ijStatement(ij.java:550)
at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:289)
at org.apache.derby.impl.tools.ij.Main.go(Main.java:209)
at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:175)
at org.apache.derby.impl.tools.ij.Main14.main(Main14.java:55)
at org.apache.derby.tools.ij.main(ij.java:60)

I have attached my derbylang_report.txt file.

Jack

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 uploaded derbylang_report.txt to next url.
>
> http://www5.ocn.ne.jp/~tomohito/derbylang_report.txt
>
> 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';
>>
>>


Mime
View raw message