I tried re-applying your patch, rebuilding, and re-running the derbylang
suite. The result was the same: the lang/wisconsin.sql test failed.
Interestingly my derbylang suite ran 1 more test than was reported in
your derbylang_report.txt at
http://www5.ocn.ne.jp/~tomohito/derbylang_report.txt. I do not know why
it worked for you. The stack trace from the derby.log file was:
Statement is: select * from TENKTUP1, (values 1) as t(x)
where TENKTUP1.unique1 = t.x
order by TENKTUP1.unique1, t.x
ERROR 42X10: 'T' is not an exposed table name in the scope in which it
appears.
at
org.apache.derby.iapi.error.StandardException.newException(StandardException.java:311)
at
org.apache.derby.impl.sql.compile.OrderByColumn.resolveColumnReference(OrderByColumn.java:312)
at
org.apache.derby.impl.sql.compile.OrderByColumn.bindOrderByColumn(OrderByColumn.java:147)
at
org.apache.derby.impl.sql.compile.OrderByList.bindOrderByColumns(OrderByList.java:153)
at org.apache.derby.impl.sql.compile.CursorNode.bind(CursorNode.java:255)
at
org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:332)
at
org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:107)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:688)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:501)
at
org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(EmbedStatement.java:130)
at org.apache.derby.impl.tools.ij.ij.GetCursorStatement(ij.java:1102)
at org.apache.derby.impl.tools.ij.ij.ijStatement(ij.java:550)
at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:289)
at org.apache.derby.impl.tools.ij.Main.go(Main.java:209)
at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:175)
at org.apache.derby.impl.tools.ij.Main14.main(Main14.java:55)
at org.apache.derby.tools.ij.main(ij.java:60)
I have attached my derbylang_report.txt file.
Jack
TomohitoNakayama wrote:
> I have tried your small.sql and result was as next.
>
> --These are evidence for improvement of 134
> ij> select * from test_number order by abs(value);
> VALUE
> -----------
> 1
> 2
> 3
>
> 3 rows selected
> ij> select * from test_number order by value * -1;
> VALUE
> -----------
> 3
> 2
> 1
>
> 3 rows selected
>
> --This is what was written in small.sql
> ij> create table TENKTUP1 (
> unique1 int not null,
> unique2 int not null,
> two int,
> four int,
> ten int,
> twenty int,
> onePercent int,
> tenPercent int,
> twentyPercent int,
> fiftyPercent int,
> unique3 int,
> evenOnePercent int,
> oddOnePercent int,
> stringu1 char(52) not null,
> stringu2 char(52) not null,
> string4 char(52)
> );
> 0 rows inserted/updated/deleted
> ij> get cursor c as
> 'select * from TENKTUP1, (values 1) as t(x)
> where TENKTUP1.unique1 = t.x
> order by TENKTUP1.unique1, t.x';
> ij>
>
> Unfortunately, I could not found any ...
>
> And I uploaded derbylang_report.txt to next url.
>
> http://www5.ocn.ne.jp/~tomohito/derbylang_report.txt
>
> Can you find any clue in it ?
> Are there any difference between yours ?
>
> If could. I want to yourr derbylang_report...
>
> Best regards.
>
> /*
>
> Tomohito Nakayama
> [EMAIL PROTECTED]
> [EMAIL PROTECTED]
>
> Naka
> http://www5.ocn.ne.jp/~tomohito/TopPage.html
>
> */
> ----- Original Message ----- From: "Jack Klebanoff"
> <[EMAIL PROTECTED]>
> To: "Derby Development" <[email protected]>
> 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
>>> [EMAIL PROTECTED]
>>> [EMAIL PROTECTED]
>>>
>>> Naka
>>> http://www5.ocn.ne.jp/~tomohito/TopPage.html
>>>
>>> */
>>> ----- Original Message ----- From: "TomohitoNakayama"
>>> <[EMAIL PROTECTED]>
>>> To: "Derby Development" <[email protected]>
>>> 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
>>>> [EMAIL PROTECTED]
>>>> [EMAIL PROTECTED]
>>>>
>>>> Naka
>>>> http://www5.ocn.ne.jp/~tomohito/TopPage.html
>>>>
>>>> */
>>>> ----- Original Message ----- From: "Jack Klebanoff"
>>>> <[EMAIL PROTECTED]>
>>>> To: "Derby Development" <[email protected]>
>>>> 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
>>>>>> [EMAIL PROTECTED]
>>>>>> [EMAIL PROTECTED]
>>>>>>
>>>>>> Naka
>>>>>> http://www5.ocn.ne.jp/~tomohito/TopPage.html
>>>>>>
>>>>>> */
>>>>>> ----- Original Message ----- From: "Jack Klebanoff"
>>>>>> <[EMAIL PROTECTED]>
>>>>>> To: "Derby Development" <[email protected]>
>>>>>> 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
>>>>>>>> [EMAIL PROTECTED]
>>>>>>>> [EMAIL PROTECTED]
>>>>>>>>
>>>>>>>> Naka
>>>>>>>> http://www5.ocn.ne.jp/~tomohito/TopPage.html
>>>>>>>>
>>>>>>>> */
>>>>>>>> ----- Original Message ----- From: "Satheesh Bandaram"
>>>>>>>> <[EMAIL PROTECTED]>
>>>>>>>> To: "Derby Development" <[email protected]>
>>>>>>>> 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
>>>>>>>>>> [EMAIL PROTECTED]
>>>>>>>>>> [EMAIL PROTECTED]
>>>>>>>>>>
>>>>>>>>>> Naka
>>>>>>>>>> http://www5.ocn.ne.jp/~tomohito/TopPage.html
>>>>>>>>>>
>>>>>>>>>> */
>>>>>>>>>> ----- Original Message ----- From: "Jack Klebanoff"
>>>>>>>>>> <[EMAIL PROTECTED]>
>>>>>>>>>> To: "Derby Development" <[email protected]>
>>>>>>>>>> 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
>>>>>>>>>>>> [EMAIL PROTECTED]
>>>>>>>>>>>> [EMAIL PROTECTED]
>>>>>>>>>>>>
>>>>>>>>>>>> Naka
>>>>>>>>>>>> http://www5.ocn.ne.jp/~tomohito/TopPage.html
>>>>>>>>>>>>
>>>>>>>>>>>> */
>>>>>>>>>>>> ----- Original Message ----- From: "TomohitoNakayama"
>>>>>>>>>>>> <[EMAIL PROTECTED]>
>>>>>>>>>>>> To: "Derby Development" <[email protected]>
>>>>>>>>>>>> Sent: Sunday, February 13, 2005 4:09 PM
>>>>>>>>>>>> Subject: Re: About improvement of DERBY-134
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>> Sorry.
>>>>>>>>>>>>> Mistaken.
>>>>>>>>>>>>>
>>>>>>>>>>>>> LOOKAHEAD()....
>>>>>>>>>>>>>
>>>>>>>>>>>>> /*
>>>>>>>>>>>>>
>>>>>>>>>>>>> Tomohito Nakayama
>>>>>>>>>>>>> [EMAIL PROTECTED]
>>>>>>>>>>>>> [EMAIL PROTECTED]
>>>>>>>>>>>>>
>>>>>>>>>>>>> Naka
>>>>>>>>>>>>> http://www5.ocn.ne.jp/~tomohito/TopPage.html
>>>>>>>>>>>>>
>>>>>>>>>>>>> */
>>>>>>>>>>>>> ----- Original Message ----- From: "TomohitoNakayama"
>>>>>>>>>>>>> <[EMAIL PROTECTED]>
>>>>>>>>>>>>> To: "Derby Development" <[email protected]>
>>>>>>>>>>>>> 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
>>>>>>>>>>>>>> [EMAIL PROTECTED]
>>>>>>>>>>>>>> [EMAIL PROTECTED]
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> 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
>>>>>>>>>>>>>> [EMAIL PROTECTED]
>>>>>>>>>>>>>> [EMAIL PROTECTED]
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Naka
>>>>>>>>>>>>>> http://www5.ocn.ne.jp/~tomohito/TopPage.html
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> */
>>>>>>>>>>>>>> ----- Original Message ----- From: "TomohitoNakayama"
>>>>>>>>>>>>>> <[EMAIL PROTECTED]>
>>>>>>>>>>>>>> To: "Derby Development" <[email protected]>
>>>>>>>>>>>>>> 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
>>>>>>>>>>>>>> [EMAIL PROTECTED]
>>>>>>>>>>>>>> [EMAIL PROTECTED]
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Naka
>>>>>>>>>>>>>> http://www5.ocn.ne.jp/~tomohito/TopPage.html
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> */
>>>>>>>>>>>>>> ----- Original Message ----- From: "TomohitoNakayama"
>>>>>>>>>>>>>> <[EMAIL PROTECTED]>
>>>>>>>>>>>>>> To: <[email protected]>
>>>>>>>>>>>>>> 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
>>>>>>>>>>>>>> [EMAIL PROTECTED]
>>>>>>>>>>>>>> [EMAIL PROTECTED]
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> 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';
>>
>>Generating report for RunSuite derbylang java null null true
------------------ Java Information ------------------
Java Version: 1.4.2_03
Java Vendor: Sun Microsystems Inc.
Java home: c:\p4clients\jack_main3\jdk142\jre
Java classpath:
.;c:/p4clients/jack_main3/classes;c:/p4clients/jack_main3/tools/java/jdbctests.zip;c:/p4clients/jack_main3/tools/java/jakarta-oro-2.0.8.jar;c:/p4clients/jack_main3/tools/java/jgl3.1.0.jar;c:/p4clients/jack_main3/tools/java/jndi/providerutil.jar;c:/p4clients/jack_main3/tools/java/jndi/ldap.jar;c:/p4clients/jack_main3/tools/java/jndi/jndi.jar;c:/p4clients/jack_main3/tools/java/RmiJdbc.jar;c:/p4clients/jack_main3/drda/jcc/2.4/db2jcc.jar;c:/p4clients/jack_main3/drda/jcc/2.4/db2jcc_license_c.jar;c:/p4clients/jack_main3/tools/java/jnl1.0F.zip;c:/p4clients/jack_main3/tools/java/servlet-2_2b.jar;c:/p4clients/jack_main3/tools/java/xslt4j-2_5_0/xercesImpl.jar;c:/p4clients/jack_main3/tools/java/xslt4j-2_5_0/xml-apis.jar;c:/p4clients/jack_main3/tools/java/xslt4j-2_5_0/xalan.jar
OS name: Windows 2000
OS architecture: x86
OS version: 5.0
Java user name: jack
Java user home: C:\Documents and Settings\Administrator
Java user dir: C:\p4clients\jack_main3\test
java.specification.name: Java Platform API Specification
java.specification.version: 1.4
--------- Derby Information --------
JRE - JDBC: J2SE 1.4.2 - JDBC 3.0
[C:\p4clients\jack_main3\classes] 10.1.0.0 alpha - (1)
[C:\p4clients\jack_main3\drda\jcc\2.4\db2jcc.jar] 2.4 - (17)
[C:\p4clients\jack_main3\drda\jcc\2.4\db2jcc_license_c.jar] 2.4 - (17)
------------------------------------------------------
----------------- Locale Information -----------------
Current Locale : [English/United States [en_US]]
Found support for locale: [de_DE]
version: 10.1.0.0 alpha - (1)
Found support for locale: [es]
version: 10.1.0.0 alpha - (1)
Found support for locale: [fr]
version: 10.1.0.0 alpha - (1)
Found support for locale: [it]
version: 10.1.0.0 alpha - (1)
Found support for locale: [ja_JP]
version: 10.1.0.0 alpha - (1)
Found support for locale: [ko_KR]
version: 10.1.0.0 alpha - (1)
Found support for locale: [pt_BR]
version: 10.1.0.0 alpha - (1)
Found support for locale: [zh_CN]
version: 10.1.0.0 alpha - (1)
Found support for locale: [zh_TW]
version: 10.1.0.0 alpha - (1)
------------------------------------------------------
Test environment information:
COMMAND LINE STYLE: jdk13
TEST CANONS: master
------------------------------------------------------
------------------------------------------------------
Summary results:
Test Run Started: 2005-03-22 13:43:01.0
Test Run Duration: 01:44:37
139 Tests Run
100% Pass (138 tests passed)
0% Fail (1 tests failed)
0 Suites skipped
------------------------------------------------------
Failed tests in: derbylang_fail.txt
------------------------------------------------------
Passed tests in: derbylang_pass.txt
------------------------------------------------------
System properties in: derbylang_prop.txt
------------------------------------------------------
------------------------------------------------------
Failure Details:
********* Diff file derbylang/derbylang/wisconsin.diff
*** Start: wisconsin jdk1.4.2_03 derbylang:derbylang 2005-03-22 15:25:33 ***
28081a28082
> ERROR 42X10: 'T' is not an exposed table name in the scope in which it
> appears.
28082a28084
> IJ ERROR: Unable to establish cursor
28087 del
< C
28087a28089
> null
28089 del
< select * from TENKTUP1, (values 1) as t(x)
28090 del
< where TENKTUP1.unique1 = t.x
28091 del
< order by TENKTUP1.unique1, t.x
28091a28091
> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()
28103 del
< Project-Restrict ResultSet (3):
28103a28103
> Row ResultSet:
28105 del
< Rows seen = 0
28106 del
< Rows filtered = 0
28107 del
< restriction = false
28108 del
< projection = true
28108a28105
> Rows returned = 1
28113 del
< restriction time (milliseconds) = 0
28114 del
< projection time (milliseconds) = 0
28115 del
< Source result set:
28116 del
< Index Row to Base Row ResultSet for TENKTUP1:
28117 del
< Number of opens = 1
28118 del
< Rows seen = 0
28119 del
< Columns accessed from heap = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
13, 14, 15}
28120 del
< constructor time (milliseconds) = 0
28121 del
< open time (milliseconds) = 0
28122 del
< next time (milliseconds) = 0
28123 del
< close time (milliseconds) = 0
28124 del
< Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at
serializable isolation level using share row locking chosen by the optimizer
28125 del
< Number of opens = 1
28126 del
< Rows seen = 0
28127 del
< Rows filtered = 0
28128 del
< Fetch Size = 1
28129 del
< constructor time (milliseconds) = 0
28130 del
< open time (milliseconds) = 0
28131 del
< next time (milliseconds) = 0
28132 del
< close time (milliseconds) = 0
28133,28151d28109
< scan information:
< Bit set of columns fetched=All
< Number of columns fetched=2
< Number of deleted rows visited=0
< Number of pages visited=0
< Number of rows qualified=0
< Number of rows visited=0
< Scan type=btree
< Tree height=2
< start position:
< >= on first 1 column(s).
< Ordered null semantics on the following columns:
< 0
< stop position:
< > on first 1 column(s).
< Ordered null semantics on the following columns:
< 0
< qualifiers:
< None
28157a28116
> ERROR 42X10: 'T' is not an exposed table name in the scope in which it
> appears.
28158a28118
> IJ ERROR: Unable to establish cursor
28163 del
< C
28163a28123
> null
28165 del
< select * from TENKTUP1, (values 1) as t(x)
28166 del
< where TENKTUP1.unique1 = t.x
28167 del
< order by TENKTUP1.two, t.x
28167a28125
> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()
28179 del
< Project-Restrict ResultSet (3):
28179a28137
> Row ResultSet:
28181 del
< Rows seen = 0
28182 del
< Rows filtered = 0
28183 del
< restriction = false
28184 del
< projection = true
28184a28139
> Rows returned = 1
28189 del
< restriction time (milliseconds) = 0
28190 del
< projection time (milliseconds) = 0
28191 del
< Source result set:
28192 del
< Index Row to Base Row ResultSet for TENKTUP1:
28193 del
< Number of opens = 1
28194 del
< Rows seen = 0
28195 del
< Columns accessed from heap = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
13, 14, 15}
28196 del
< constructor time (milliseconds) = 0
28197 del
< open time (milliseconds) = 0
28198 del
< next time (milliseconds) = 0
28199 del
< close time (milliseconds) = 0
28200 del
< Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at
serializable isolation level using share row locking chosen by the optimizer
28201 del
< Number of opens = 1
28202 del
< Rows seen = 0
28203 del
< Rows filtered = 0
28204 del
< Fetch Size = 1
28205 del
< constructor time (milliseconds) = 0
28206 del
< open time (milliseconds) = 0
28207 del
< next time (milliseconds) = 0
28208 del
< close time (milliseconds) = 0
28209,28227d28143
< scan information:
< Bit set of columns fetched=All
< Number of columns fetched=2
< Number of deleted rows visited=0
< Number of pages visited=0
< Number of rows qualified=0
< Number of rows visited=0
< Scan type=btree
< Tree height=2
< start position:
< >= on first 1 column(s).
< Ordered null semantics on the following columns:
< 0
< stop position:
< > on first 1 column(s).
< Ordered null semantics on the following columns:
< 0
< qualifiers:
< None
28232a28149
> ERROR 42X10: 'T' is not an exposed table name in the scope in which it
> appears.
28233a28151
> IJ ERROR: Unable to establish cursor
28238 del
< C
28238a28156
> null
28240 del
< select * from TENKTUP1, (values 1) as t(x)
28241 del
< where TENKTUP1.two = t.x
28242 del
< order by TENKTUP1.two, t.x
28242a28158
> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()
28254 del
< Project-Restrict ResultSet (2):
28254a28170
> Row ResultSet:
28256 del
< Rows seen = 0
28257 del
< Rows filtered = 0
28258 del
< restriction = false
28259 del
< projection = true
28259a28172
> Rows returned = 1
28264 del
< restriction time (milliseconds) = 0
28265 del
< projection time (milliseconds) = 0
28266 del
< Source result set:
28267 del
< Table Scan ResultSet for TENKTUP1 at serializable isolation level using
share table locking chosen by the optimizer
28268 del
< Number of opens = 1
28269 del
< Rows seen = 0
28270 del
< Rows filtered = 0
28271 del
< Fetch Size = 16
28272,28290d28176
< constructor time (milliseconds) = 0
< open time (milliseconds) = 0
< next time (milliseconds) = 0
< close time (milliseconds) = 0
< scan information:
< Bit set of columns fetched=All
< Number of columns fetched=16
< Number of pages visited=0
< Number of rows qualified=0
< Number of rows visited=0
< Scan type=heap
< start position:
< null stop position:
< null qualifiers:
< Column[0][0] Id: 2
< Operator: =
< Ordered nulls: false
< Unknown return value: false
< Negate comparison result: false
28295a28182
> ERROR 42X10: 'T' is not an exposed table name in the scope in which it
> appears.
28296a28184
> IJ ERROR: Unable to establish cursor
28301 del
< C
28301a28189
> null
28303 del
< select * from TENKTUP1, (values 1) as t(x)
28304 del
< where TENKTUP1.four = t.x
28305 del
< order by TENKTUP1.four, t.x
28305a28191
> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()
28317 del
< Project-Restrict ResultSet (3):
28317a28203
> Row ResultSet:
28319 del
< Rows seen = 0
28320 del
< Rows filtered = 0
28321 del
< restriction = false
28322 del
< projection = true
28322a28205
> Rows returned = 1
28327 del
< restriction time (milliseconds) = 0
28328 del
< projection time (milliseconds) = 0
28329 del
< Source result set:
28330 del
< Index Row to Base Row ResultSet for TENKTUP1:
28331 del
< Number of opens = 1
28332 del
< Rows seen = 0
28333 del
< Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
13, 14, 15}
28334 del
< constructor time (milliseconds) = 0
28335 del
< open time (milliseconds) = 0
28336 del
< next time (milliseconds) = 0
28337 del
< close time (milliseconds) = 0
28338 del
< Index Scan ResultSet for TENKTUP1 using index TK1FOUR at
serializable isolation level using share row locking chosen by the optimizer
28339 del
< Number of opens = 1
28340 del
< Rows seen = 0
28341 del
< Rows filtered = 0
28342 del
< Fetch Size = 1
28343 del
< constructor time (milliseconds) = 0
28344 del
< open time (milliseconds) = 0
28345,28363d28209
< next time (milliseconds) = 0
< close time (milliseconds) = 0
< scan information:
< Bit set of columns fetched=All
< Number of columns fetched=2
< Number of deleted rows visited=0
< Number of pages visited=0
< Number of rows qualified=0
< Number of rows visited=0
< Scan type=btree
< Tree height=2
< start position:
< >= on first 1 column(s).
< Ordered null semantics on the following columns:
< stop position:
< > on first 1 column(s).
< Ordered null semantics on the following columns:
< qualifiers:
< None
28368a28215
> ERROR 42X10: 'T' is not an exposed table name in the scope in which it
> appears.
28369a28217
> IJ ERROR: Unable to establish cursor
28374 del
< C
28374a28222
> null
28376 del
< select * from TENKTUP1, (values 1) as t(x)
28377 del
< where TENKTUP1.ten = t.x
28378 del
< order by TENKTUP1.ten, t.x
28378a28224
> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()
28390 del
< Project-Restrict ResultSet (3):
28390a28236
> Row ResultSet:
28392 del
< Rows seen = 0
28393 del
< Rows filtered = 0
28394 del
< restriction = false
28395 del
< projection = true
28395a28238
> Rows returned = 1
28400 del
< restriction time (milliseconds) = 0
28401 del
< projection time (milliseconds) = 0
28402 del
< Source result set:
28403 del
< Index Row to Base Row ResultSet for TENKTUP1:
28404 del
< Number of opens = 1
28405 del
< Rows seen = 0
28406 del
< Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
13, 14, 15}
28407 del
< constructor time (milliseconds) = 0
28408 del
< open time (milliseconds) = 0
28409 del
< next time (milliseconds) = 0
28410 del
< close time (milliseconds) = 0
28411 del
< Index Scan ResultSet for TENKTUP1 using index TK1TEN at
serializable isolation level using share row locking chosen by the optimizer
28412 del
< Number of opens = 1
28413 del
< Rows seen = 0
28414 del
< Rows filtered = 0
28415 del
< Fetch Size = 1
28416 del
< constructor time (milliseconds) = 0
28417 del
< open time (milliseconds) = 0
28418,28436d28242
< next time (milliseconds) = 0
< close time (milliseconds) = 0
< scan information:
< Bit set of columns fetched=All
< Number of columns fetched=2
< Number of deleted rows visited=0
< Number of pages visited=0
< Number of rows qualified=0
< Number of rows visited=0
< Scan type=btree
< Tree height=2
< start position:
< >= on first 1 column(s).
< Ordered null semantics on the following columns:
< stop position:
< > on first 1 column(s).
< Ordered null semantics on the following columns:
< qualifiers:
< None
28441a28248
> ERROR 42X10: 'T' is not an exposed table name in the scope in which it
> appears.
28442a28250
> IJ ERROR: Unable to establish cursor
28447 del
< C
28447a28255
> null
28449 del
< select * from TENKTUP1, (values 1) as t(x)
28450 del
< where TENKTUP1.twenty = t.x
28451 del
< order by TENKTUP1.twenty, t.x
28451a28257
> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()
28463 del
< Project-Restrict ResultSet (3):
28463a28269
> Row ResultSet:
28465 del
< Rows seen = 0
28466 del
< Rows filtered = 0
28467 del
< restriction = false
28468 del
< projection = true
28468a28271
> Rows returned = 1
28473 del
< restriction time (milliseconds) = 0
28474 del
< projection time (milliseconds) = 0
28475 del
< Source result set:
28476 del
< Index Row to Base Row ResultSet for TENKTUP1:
28477 del
< Number of opens = 1
28478 del
< Rows seen = 0
28479 del
< Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
13, 14, 15}
28480 del
< constructor time (milliseconds) = 0
28481 del
< open time (milliseconds) = 0
28482 del
< next time (milliseconds) = 0
28483 del
< close time (milliseconds) = 0
28484 del
< Index Scan ResultSet for TENKTUP1 using index TK1TWENTY at
serializable isolation level using share row locking chosen by the optimizer
28485 del
< Number of opens = 1
28486 del
< Rows seen = 0
28487 del
< Rows filtered = 0
28488 del
< Fetch Size = 1
28489 del
< constructor time (milliseconds) = 0
28490 del
< open time (milliseconds) = 0
28491,28509d28275
< next time (milliseconds) = 0
< close time (milliseconds) = 0
< scan information:
< Bit set of columns fetched=All
< Number of columns fetched=2
< Number of deleted rows visited=0
< Number of pages visited=0
< Number of rows qualified=0
< Number of rows visited=0
< Scan type=btree
< Tree height=2
< start position:
< >= on first 1 column(s).
< Ordered null semantics on the following columns:
< stop position:
< > on first 1 column(s).
< Ordered null semantics on the following columns:
< qualifiers:
< None
28514a28281
> ERROR 42X10: 'T' is not an exposed table name in the scope in which it
> appears.
28515a28283
> IJ ERROR: Unable to establish cursor
28520 del
< C
28520a28288
> null
28522 del
< select * from TENKTUP1, (values 1) as t(x)
28523 del
< where TENKTUP1.onePercent = t.x
28524 del
< order by TENKTUP1.onePercent, t.x
28524a28290
> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()
28536 del
< Project-Restrict ResultSet (3):
28536a28302
> Row ResultSet:
28538 del
< Rows seen = 0
28539 del
< Rows filtered = 0
28540 del
< restriction = false
28541 del
< projection = true
28541a28304
> Rows returned = 1
28546 del
< restriction time (milliseconds) = 0
28547 del
< projection time (milliseconds) = 0
28548 del
< Source result set:
28549 del
< Index Row to Base Row ResultSet for TENKTUP1:
28550 del
< Number of opens = 1
28551 del
< Rows seen = 0
28552 del
< Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
13, 14, 15}
28553 del
< constructor time (milliseconds) = 0
28554 del
< open time (milliseconds) = 0
28555 del
< next time (milliseconds) = 0
28556 del
< close time (milliseconds) = 0
28557 del
< Index Scan ResultSet for TENKTUP1 using index TK1ONEPERCENT at
serializable isolation level using share row locking chosen by the optimizer
28558 del
< Number of opens = 1
28559 del
< Rows seen = 0
28560 del
< Rows filtered = 0
28561 del
< Fetch Size = 1
28562 del
< constructor time (milliseconds) = 0
28563 del
< open time (milliseconds) = 0
28564,28582d28308
< next time (milliseconds) = 0
< close time (milliseconds) = 0
< scan information:
< Bit set of columns fetched=All
< Number of columns fetched=2
< Number of deleted rows visited=0
< Number of pages visited=0
< Number of rows qualified=0
< Number of rows visited=0
< Scan type=btree
< Tree height=2
< start position:
< >= on first 1 column(s).
< Ordered null semantics on the following columns:
< stop position:
< > on first 1 column(s).
< Ordered null semantics on the following columns:
< qualifiers:
< None
28587a28314
> ERROR 42X10: 'T' is not an exposed table name in the scope in which it
> appears.
28588a28316
> IJ ERROR: Unable to establish cursor
28593 del
< C
28593a28321
> null
28595 del
< select * from TENKTUP1, (values 1) as t(x)
28596 del
< where TENKTUP1.tenPercent = t.x
28597 del
< order by TENKTUP1.tenPercent, t.x
28597a28323
> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()
28609 del
< Project-Restrict ResultSet (2):
28609a28335
> Row ResultSet:
28611 del
< Rows seen = 0
28612 del
< Rows filtered = 0
28613 del
< restriction = false
28614 del
< projection = true
28614a28337
> Rows returned = 1
28619 del
< restriction time (milliseconds) = 0
28620 del
< projection time (milliseconds) = 0
28621 del
< Source result set:
28622 del
< Table Scan ResultSet for TENKTUP1 at serializable isolation level using
share table locking chosen by the optimizer
28623 del
< Number of opens = 1
28624 del
< Rows seen = 0
28625 del
< Rows filtered = 0
28626 del
< Fetch Size = 16
28627,28645d28341
< constructor time (milliseconds) = 0
< open time (milliseconds) = 0
< next time (milliseconds) = 0
< close time (milliseconds) = 0
< scan information:
< Bit set of columns fetched=All
< Number of columns fetched=16
< Number of pages visited=0
< Number of rows qualified=0
< Number of rows visited=0
< Scan type=heap
< start position:
< null stop position:
< null qualifiers:
< Column[0][0] Id: 7
< Operator: =
< Ordered nulls: false
< Unknown return value: false
< Negate comparison result: false
28650a28347
> ERROR 42X10: 'T' is not an exposed table name in the scope in which it
> appears.
28651a28349
> IJ ERROR: Unable to establish cursor
28656 del
< C
28656a28354
> null
28658 del
< select * from TENKTUP1, (values 1) as t(x)
28659 del
< where TENKTUP1.twentyPercent = t.x
28660 del
< order by TENKTUP1.twentyPercent, t.x
28660a28356
> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()
28672 del
< Project-Restrict ResultSet (3):
28672a28368
> Row ResultSet:
28674 del
< Rows seen = 0
28675 del
< Rows filtered = 0
28676 del
< restriction = false
28677 del
< projection = true
28677a28370
> Rows returned = 1
28682 del
< restriction time (milliseconds) = 0
28683 del
< projection time (milliseconds) = 0
28684 del
< Source result set:
28685 del
< Index Row to Base Row ResultSet for TENKTUP1:
28686 del
< Number of opens = 1
28687 del
< Rows seen = 0
28688 del
< Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
13, 14, 15}
28689 del
< constructor time (milliseconds) = 0
28690 del
< open time (milliseconds) = 0
28691 del
< next time (milliseconds) = 0
28692 del
< close time (milliseconds) = 0
28693 del
< Index Scan ResultSet for TENKTUP1 using index TK1TWENTYPERCENT
at serializable isolation level using share row locking chosen by the optimizer
28694 del
< Number of opens = 1
28695 del
< Rows seen = 0
28696 del
< Rows filtered = 0
28697 del
< Fetch Size = 1
28698 del
< constructor time (milliseconds) = 0
28699 del
< open time (milliseconds) = 0
28700,28718d28374
< next time (milliseconds) = 0
< close time (milliseconds) = 0
< scan information:
< Bit set of columns fetched=All
< Number of columns fetched=2
< Number of deleted rows visited=0
< Number of pages visited=0
< Number of rows qualified=0
< Number of rows visited=0
< Scan type=btree
< Tree height=2
< start position:
< >= on first 1 column(s).
< Ordered null semantics on the following columns:
< stop position:
< > on first 1 column(s).
< Ordered null semantics on the following columns:
< qualifiers:
< None
28723a28380
> ERROR 42X10: 'T' is not an exposed table name in the scope in which it
> appears.
28724a28382
> IJ ERROR: Unable to establish cursor
28729 del
< C
28729a28387
> null
28731 del
< select * from TENKTUP1, (values 1) as t(x)
28732 del
< where TENKTUP1.fiftyPercent = t.x
28733 del
< order by TENKTUP1.fiftyPercent, t.x
28733a28389
> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()
28745 del
< Project-Restrict ResultSet (2):
28745a28401
> Row ResultSet:
28747 del
< Rows seen = 0
28748 del
< Rows filtered = 0
28749 del
< restriction = false
28750 del
< projection = true
28750a28403
> Rows returned = 1
28755 del
< restriction time (milliseconds) = 0
28756 del
< projection time (milliseconds) = 0
28757 del
< Source result set:
28758 del
< Table Scan ResultSet for TENKTUP1 at serializable isolation level using
share table locking chosen by the optimizer
28759 del
< Number of opens = 1
28760 del
< Rows seen = 0
28761 del
< Rows filtered = 0
28762 del
< Fetch Size = 16
28763,28781d28407
< constructor time (milliseconds) = 0
< open time (milliseconds) = 0
< next time (milliseconds) = 0
< close time (milliseconds) = 0
< scan information:
< Bit set of columns fetched=All
< Number of columns fetched=16
< Number of pages visited=0
< Number of rows qualified=0
< Number of rows visited=0
< Scan type=heap
< start position:
< null stop position:
< null qualifiers:
< Column[0][0] Id: 9
< Operator: =
< Ordered nulls: false
< Unknown return value: false
< Negate comparison result: false
Test Failed.
*** End: wisconsin jdk1.4.2_03 derbylang:derbylang 2005-03-22 15:27:38 ***
------------------------------------------------------
