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 Fri, 25 Mar 2005 16:23:27 GMT
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" <tomonaka@basil.ocn.ne.jp>
To: "Derby Development" <derby-dev@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" <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
>
>
>
>
> -- 
> 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