You are viewing a plain text version of this content. The canonical link for it is here.

Posted to derby-dev@db.apache.org by TomohitoNakayama <to...@basil.ocn.ne.jp> on 2005/03/27 20:26:09 UTC

[Patch] Re: About improvement of DERBY-134

Hello.

I have added some test to orderby.sql (and correspond result to orderby.out) 
,
which was suggested by Jack Klebanoff.

And I have executed derbylang test and coulud not found error exept for 
lang/floattypes.sql.
I think this error is not caused by my patch.

derbylang_report.txt is attached to this mail.

Please check this patch from a point of other person's view.

best regards.

/*

         Tomohito Nakayama
         tomoihto@rose.zero.ad.jp
         tomonaka@basil.ocn.ne.jp

         Naka
         http://www5.ocn.ne.jp/~tomohito/TopPage.html

*/
----- Original Message ----- 
From: "TomohitoNakayama" <to...@basil.ocn.ne.jp>
To: "Derby Development" <de...@db.apache.org>
Sent: Monday, March 28, 2005 1:14 AM
Subject: Re: About improvement of DERBY-134


> Hello.
>
> I have modified patch for DERBY-134 and solved error at wisconsin.sql .
>
> What I have done was as next.
>
> change-1:
> I made it possible to get value of  TableName.hasSchema via
> TableName.hasSchema() method.
> This value seems to indicate whether schema was specified in sql , seeing
> code of sqlgrammer.jj and TableName.java.
>
> change-2:
> Calling method TableName.hasSchema() added by change-1 in
> OrderByColumn.resolveColumnReference(ResultSetNode,ColumnReference).
> If True, pass schemaName to getFromTableByName as schemaName.
> If false, pass null to getFromTableByName as schemaName
>
> This was to make processing as same as original implementation, passing
> schemaName only when it was specified in sql.
>
>
> Test result for winsoncin.sql is as next:
>
> 中山智仁@Arkat ~/derbyUser/wisconsin.20050328
> $ testSql.bat lang/wisconsin.sql
>
> c:\ProgramDev\derbyUser\wisconsin.20050328>set
> DERBY_INSTALL=c:\ProgramDev\derby
> \trunk
>
> c:\ProgramDev\derbyUser\wisconsin.20050328>FOR %X in
> ("c:\ProgramDev\derby\trunk
> ") DO SET DERBY_INSTALL=%~sX
>
> c:\ProgramDev\derbyUser\wisconsin.20050328>SET
> DERBY_INSTALL=c:\PROGRA~2\derby\t
> runk
>
> c:\ProgramDev\derbyUser\wisconsin.20050328>set OLD_CLASSPATH=
>
> c:\ProgramDev\derbyUser\wisconsin.20050328>set
> CLASSPATH=c:\PROGRA~2\derby\trunk
> \tools\java\jakarta-oro-2.0.8.jar;c:\PROGRA~2\derby\trunk\jars\insane\derbynet.j
> ar;c:\PROGRA~2\derby\trunk\jars\insane\derbyTesting.jar;c:\PROGRA~2\derby\trunk\
> jars\insane\derby.jar;c:\PROGRA~2\derby\trunk\jars\insane\derbytools.jar;
>
> c:\ProgramDev\derbyUser\wisconsin.20050328>java
> org.apache.derbyTesting.function
> Tests.harness.RunTest lang/wisconsin.sql
> -- listing properties --
> derby.debug.true=
> derby.storage.checkpointInterval=100000
> derby.language.preloadClasses=true
> *** Start: wisconsin jdk1.5.0_02 2005-03-28 01:00:23 ***
> *** End:   wisconsin jdk1.5.0_02 2005-03-28 01:01:47 ***
>
> Best regards.
>
> /*
>
>         Tomohito Nakayama
>         tomoihto@rose.zero.ad.jp
>         tomonaka@basil.ocn.ne.jp
>
>         Naka
>         http://www5.ocn.ne.jp/~tomohito/TopPage.html
>
> */
> ----- Original Message ----- 
> From: "TomohitoNakayama" <to...@basil.ocn.ne.jp>
> To: "Derby Development" <de...@db.apache.org>
> Sent: Saturday, March 26, 2005 1:23 AM
> Subject: Re: About improvement of DERBY-134
>
>
>> Hello.
>>
>> I come to think that this problem seems to happen because of unwannted
>> default schema name.
>>
>> Original before patch code to bind order column uses only schema name ,
>> tablename and column name , contained sql order by clause.
>> If there was no schema name in order clause of processing sql , no schema
>> name , which is null in program , was used to bind.
>>
>> But now, binding process uses value returned via method of 
>> ColumnReference
>> .
>> These method returns defalut value ,which is not null , even if user
>> specify no schema name.
>>
>> That does not always cause bug. That is just well-seen default value
>> processing.
>>
>> But some processing of derby seems to run on assumpt that if user specify
>> no schema name, null is passed to bind process.
>>
>> select * from TENKTUP1, (values 1) as t(x)
>> where TENKTUP1.unique1 = t.x
>> order by TENKTUP1.unique1, t.x
>>
>> Sql above is one example.
>>
>> Because "t(x)" is not TABLE in DB , t(x) is out of schema.
>> On the other hand "t.x" is handled as "APP.T.x" in ColumnReference.
>> This was turned out by tracing running process by jdb as next
>>
>> main[1] where
>>  [1]
>> org.apache.derby.impl.sql.compile.OrderByColumn.resolveColumnReference
>> (OrderByColumn.java:303)
>>
>> <ommited.>
>> main[1] list
>> 299                     throw
>> StandardException.newException(SQLState.LANG_QUALIFIED_COLUMN_NAME_NOT_ALLOWED,
>> fullName);
>> 300             }
>> 301
>> 302             if(cr.getTableNameNode() != null){
>> 303 =>                  FromTable fromTable =
>> target.getFromTableByName(cr.getSourceTableName(),
>> 304 cr.getSchemaName(),
>> 305 true);
>> 306                     if(fromTable == null){
>> 307                             fromTable =
>> target.getFromTableByName(cr.getSourceTableName(),
>> 308 cr.getSchemaName(),
>> main[1] eval cr.getSchemaName()
>> cr.getSchemaName() = "APP"
>> main[1] eval cr.getSourceTableName()
>> cr.getSourceTableName() = "T"
>>
>> Because "t(x)" is out of schema , it is failed to bind "t(x)" with schema
>> name not null.
>>
>> This "APP" was the unwannted default schema name and which cause the
>> problem.
>>
>>
>> /*
>>
>>         Tomohito Nakayama
>>         tomoihto@rose.zero.ad.jp
>>         tomonaka@basil.ocn.ne.jp
>>
>>         Naka
>>         http://www5.ocn.ne.jp/~tomohito/TopPage.html
>>
>> */
>> ----- Original Message ----- 
>> From: "TomohitoNakayama" <to...@basil.ocn.ne.jp>
>> To: "Derby Development" <de...@db.apache.org>
>> Sent: Wednesday, March 23, 2005 10:30 PM
>> Subject: Re: About improvement of DERBY-134
>>
>>
>>>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" <to...@basil.ocn.ne.jp>
>>> To: "Derby Development" <de...@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" <kl...@sbcglobal.net>
>>>> To: "Derby Development" <de...@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"
>>>>>> <kl...@sbcglobal.net>
>>>>>> To: "Derby Development" <de...@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"
>>>>>>>> <to...@basil.ocn.ne.jp>
>>>>>>>> To: "Derby Development" <de...@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"
>>>>>>>>> <kl...@sbcglobal.net>
>>>>>>>>> To: "Derby Development" <de...@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"
>>>>>>>>>>> <kl...@sbcglobal.net>
>>>>>>>>>>> To: "Derby Development" <de...@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"
>>>>>>>>>>>>> <sa...@sourcery.org>
>>>>>>>>>>>>> To: "Derby Development" <de...@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"
>>>>>>>>>>>>>>> <kl...@sbcglobal.net>
>>>>>>>>>>>>>>> To: "Derby Development" <de...@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"
>>>>>>>>>>>>>>>>> <to...@basil.ocn.ne.jp>
>>>>>>>>>>>>>>>>> To: "Derby Development" <de...@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"
>>>>>>>>>>>>>>>>>> <to...@basil.ocn.ne.jp>
>>>>>>>>>>>>>>>>>> To: "Derby Development" <de...@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"
>>>>>>>>>>>>>>>>>>> <to...@basil.ocn.ne.jp>
>>>>>>>>>>>>>>>>>>> To: "Derby Development" <de...@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"
>>>>>>>>>>>>>>>>>>> <to...@basil.ocn.ne.jp>
>>>>>>>>>>>>>>>>>>> To: <de...@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
>>>
>>>
>>>
>>>
>>> -- 
>>> 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.3 - Release Date: 2005/03/25
>>
>


--------------------------------------------------------------------------------


No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.8.3 - Release Date: 2005/03/25

Re: (Derby 167) was Re: [Patch] Re: About improvement of DERBY-134

Posted by Kathey Marsden <km...@sbcglobal.net>.

Danesh Irani wrote:

>Hello Kathey,
>
>Christian when reporting Derby-167 had a few possible solutions, is
>Satheesh's proposed SQL Standard way included in one of those? If not
>could you perhaps elaborate a little on what Satheesh proposed.
>Personally I think all 3 of Christians solutions would add value in
>terms of flexibility to Derby.
>
>Thanks
>~Danesh
>  
>
>
I couldn't find Satheesh's mail but yes it was regarding option 1,
generated by default.  I  looked up the SQL syntax  in the sql standard
and put a comment in the JIRA  entry. 

Kathey

Re: (Derby 167) was Re: [Patch] Re: About improvement of DERBY-134

Posted by Danesh Irani <da...@gmail.com>.

Hello Kathey,

Christian when reporting Derby-167 had a few possible solutions, is
Satheesh's proposed SQL Standard way included in one of those? If not
could you perhaps elaborate a little on what Satheesh proposed.
Personally I think all 3 of Christians solutions would add value in
terms of flexibility to Derby.

Thanks
~Danesh

On Apr 2, 2005 10:39 AM, Kathey Marsden <km...@sbcglobal.net> wrote:
> TomohitoNakayama wrote:
> 
> > Hello.
> >
> > Thank for all who advise and help me !
> >
> > Now I'm reading code of derby here and there , and found this program
> > is very attractive.
> > I want to find next material for hacking :D
> >
> 
> Hello Tomohito,
> 
> Since you are looking for a next project ,  I wanted to put a plug in
> for my personal crusade for
> 
>  Derby-167.  Inserting values in an identity column.
> http://issues.apache.org/jira/browse/DERBY-167
> 
> I think would be tremendous value you in implementing this feature as
> currently it is not possible to import  data to Derby  if you have an
> autoincrement column and want to keep your primary keys intact.   I know
> there are others who have expressed interest and would be willing to
> help you in this project.     I don't want to get into too many details
> on this thread outside the Jira comments but just to give you an idea,
> Satheesh I think proposed a specific SQL Standard way to do this and
> Mamta promised support with reviews or whatever.
> 
> I'd suggest  first submit a proposed solution to the community and get
> consensus before starting to implement a solution.
> I think it would be a way for you to add huge value to Derby.
> 
> Thanks
> 
> Kathey
> 
>