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 13:30:48 GMT
I traced running-process using jdb,
and found that FromSubQuery.getFromTableByName(String,String 
schemaName,boolean)
seems to be failed ....


This method invokes FromTable.getFromTableByName(String,String 
schemaName,boolean) ,that is method of super class ,
with schemaName of not-null value.

org.apache.derby.impl.sql.compile.FromSubQuery L169:
 protected FromTable getFromTableByName(String name, String schemaName, 
boolean exactMatch)
  throws StandardException
 {
  if (generatedForGroupByClause || generatedForHavingClause)
  {
   return subquery.getFromTableByName(name, schemaName, exactMatch);
  }
  else
  {
   return super.getFromTableByName(name, schemaName, exactMatch); .
  }
 }


But FromTable.getFromTableByName(String,String,boolean)  returns null if 
schemaName was not null value.

org.apache.derby.impl.sql.compile.FromTable L1196:
protected FromTable getFromTableByName(String name, String schemaName, 
boolean exactMatch)
  throws StandardException
 {
  // Only FromBaseTables have schema names
  if (schemaName != null)
  {
   return null;
  }

  if (getExposedName().equals(name))
  {
   return this;
  }
  return null;
 }

As shown above , I found comment "Only FromBaseTables have schema names" in 
FromTable.getFromTableByName(String,String,boolean).
I think mystery exists around here ....


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: Wednesday, March 23, 2005 9:33 PM
Subject: Re: About improvement of DERBY-134


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