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
[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: 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:
[EMAIL PROTECTED] ~/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 [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 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 [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, 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 [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, 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 [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: 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 [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';
-- 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
DERBY-134.20050328_2.patch
Description: Binary data
Java Version: 1.5.0_02
Java Vendor: Sun Microsystems Inc.
Java home: C:\Program Files\Java\jre1.5.0_02
Java classpath: c:\PROGRA~2\derby\trunk\tools\java\jakarta-oro-2.0.8.jar;c:\PROGRA~2\derby\trunk\jars\insane\derbynet.jar;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;
OS name: Windows XP
OS architecture: x86
OS version: 5.1
Java user name: ���R�q�m
Java user home: C:\Documents and Settings\���R�q�m
Java user dir: c:\ProgramDev\derbyUser\derbylang.20050328
java.specification.name: Java Platform API Specification
java.specification.version: 1.5
--------- Derby Information --------
JRE - JDBC: J2SE 5.0 - JDBC 3.0
[C:\ProgramDev\derby\trunk\jars\insane\derbynet.jar] 10.1.0.0 alpha - (158750M)
[C:\ProgramDev\derby\trunk\jars\insane\derby.jar] 10.1.0.0 alpha - (158750M)
[C:\ProgramDev\derby\trunk\jars\insane\derbytools.jar] 10.1.0.0 alpha - (158750M)
------------------------------------------------------
----------------- Locale Information -----------------
------------------------------------------------------
Test environment information:
COMMAND LINE STYLE: jdk13
TEST CANONS: master
------------------------------------------------------
------------------------------------------------------
Summary results:
Test Run Started: 2005-03-28 02:19:17.0 Test Run Duration: 00:56:04
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/floattypes.diff
*** Start: floattypes jdk1.5.0_02 derbylang:derbylang 2005-03-28 02:38:20 ***
152,165d151
< ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(31,0).
< ij> select * from tmp;
< D < --------------------------------
< 100000000000000000000000000000 < ij> drop table tmp;
< 0 rows inserted/updated/deleted
< ij> -- try inserting various types into decimal.
< -- we expect silent truncation of the fraction
< drop table tmp;
< ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TMP' because it does not exist.
< ij> create table tmp (d decimal(5,2));
< 0 rows inserted/updated/deleted
< ij> insert into tmp values (100);
166a153,167
ij> select * from tmp;
D --------------------------------
100000000000000000000000000000 1797690000000000000000000000000&
ij> drop table tmp;
0 rows inserted/updated/deleted
ij> -- try inserting various types into decimal.
-- we expect silent truncation of the fraction
drop table tmp;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TMP' because it does not exist.
ij> create table tmp (d decimal(5,2));
0 rows inserted/updated/deleted
ij> insert into tmp values (100);
1 row inserted/updated/deleted
Test Failed. *** End: floattypes jdk1.5.0_02 derbylang:derbylang 2005-03-28 02:38:45 *** ------------------------------------------------------
No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.8.3 - Release Date: 2005/03/25
