Author: bandaram
Date: Fri Apr 1 16:12:48 2005
New Revision: 159746
URL: http://svn.apache.org/viewcvs?view=rev&rev=159746
Log:
Derby-134: Improvement to allow ordering by expressions, instead of correlation
names or column positions only.
Submitted by Tomohito Nakayama. ([EMAIL PROTECTED])
Modified:
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableName.java
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
Modified:
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java
URL:
http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java?view=diff&r1=159745&r2=159746
==============================================================================
---
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java
(original)
+++
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java
Fri Apr 1 16:12:48 2005
@@ -202,7 +202,9 @@
{
OrderByColumn orderByColumn = (OrderByColumn)
nf.getNode( C_NodeTypes.ORDER_BY_COLUMN,
- ReuseFactory.getInteger(
intermediateOrderByColumns[i] + 1),
+ nf.getNode(C_NodeTypes.INT_CONSTANT_NODE,
+ ReuseFactory.getInteger(
intermediateOrderByColumns[i] + 1),
+ cm),
cm);
if( intermediateOrderByDirection[i] < 0)
orderByColumn.setDescending();
Modified:
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java
URL:
http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java?view=diff&r1=159745&r2=159746
==============================================================================
---
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java
(original)
+++
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java
Fri Apr 1 16:12:48 2005
@@ -42,36 +42,20 @@
public class OrderByColumn extends OrderedColumn {
private ResultColumn resultCol;
- private String columnName;
- private String correlationName;
- private String schemaName;
private boolean ascending = true;
+ private ValueNode expression;
- /**
- * Initializer.
- *
- * @param columnName The name of the column being referenced
- * @param correlationName The correlation name, if any
- */
- public void init(
- Object columnName,
- Object correlationName,
- Object schemaName)
- {
- this.columnName = (String) columnName;
- this.correlationName = (String) correlationName;
- this.schemaName = (String) schemaName;
- }
- /**
+ /**
* Initializer.
*
- * @param columnPosition The position of the column being
referenced
+ * @param expression Expression of this column
*/
- public void init(Object columnPosition) {
- this.columnPosition = ((Integer) columnPosition).intValue();
+ public void init(Object expression)
+ {
+ this.expression = (ValueNode)expression;
}
-
+
/**
* Convert this object to a String. See comments in QueryTreeNode.java
* for how this should be done for tree printing.
@@ -80,25 +64,13 @@
*/
public String toString() {
if (SanityManager.DEBUG) {
- return "columnName: " + columnName + "\n" +
- "correlationName: " + correlationName + "\n" +
- "schemaName: " + schemaName + "\n" +
- super.toString();
+ return expression.toString();
} else {
return "";
}
}
/**
- * Get the name of this column
- *
- * @return The name of this column
- */
- public String getColumnName() {
- return columnName;
- }
-
- /**
* Mark the column as descending order
*/
public void setDescending() {
@@ -168,80 +140,46 @@
public void bindOrderByColumn(ResultSetNode target)
throws StandardException
{
- int sourceTableNumber = -1;
- ResultColumnList targetCols = target.getResultColumns();
-
- //bug 5716 - for db2 compatibility - no qualified names allowed
in order by clause when union/union all operator is used
- if (target instanceof SetOperatorNode && correlationName !=
null)
- {
- String fullName = (schemaName != null) ?
- (schemaName + "." + correlationName + "." +
columnName) :
- (correlationName + "." + columnName);
- throw
StandardException.newException(SQLState.LANG_QUALIFIED_COLUMN_NAME_NOT_ALLOWED,
fullName);
- }
- /* If the correlation name is non-null then we need to verify
that it
- * is a valid exposed name.
- */
- if (correlationName != null)
- {
- /* Find the matching FromTable visible in the current
scope.
- * We first try a full match on both schema and table
name. If no
- * match, then we go for same table id.
- */
- FromTable fromTable =
target.getFromTableByName(correlationName, schemaName, true);
- if (fromTable == null)
- {
- fromTable =
target.getFromTableByName(correlationName, schemaName, false);
- if (fromTable == null)
- {
- String fullName = (schemaName != null) ?
-
(schemaName + "." + correlationName) :
-
correlationName;
- // correlation name is not an exposed
name in the current scope
- throw
StandardException.newException(SQLState.LANG_EXPOSED_NAME_NOT_FOUND, fullName);
- }
- }
+ if(expression instanceof ColumnReference){
+
+ ColumnReference cr = (ColumnReference) expression;
+
+ resultCol = resolveColumnReference(target,
+ cr);
- /* HACK - if the target is a UnionNode, then we have to
- * have special code to get the sourceTableNumber.
This is
- * because of the gyrations we go to with building the
RCLs
- * for a UnionNode.
- */
- if (target instanceof SetOperatorNode)
- {
- sourceTableNumber = ((FromTable)
target).getTableNumber();
- }
- else
- {
- sourceTableNumber = fromTable.getTableNumber();
- }
- }
-
- if (columnName != null)
- {
- /* If correlation name is not null, then we look an RC
whose expression is a
- * ColumnReference with the same table number as the
FromTable with
- * correlationName as its exposed name.
- * If correlation name is null, then we simply look for
an RC whose name matches
- * columnName.
- */
- resultCol = targetCols.getOrderByColumn(columnName,
correlationName, sourceTableNumber);
-
- /* DB2 doesn't allow ordering using generated column
name */
- if ((resultCol == null) || resultCol.isNameGenerated())
- {
- String errString = (correlationName == null) ?
-
columnName :
-
correlationName + "." + columnName;
- throw
StandardException.newException(SQLState.LANG_ORDER_BY_COLUMN_NOT_FOUND,
errString);
- }
columnPosition = resultCol.getColumnPosition();
- }
- else {
+
+ }else if(isReferedColByNum(expression)){
+
+ ResultColumnList targetCols = target.getResultColumns();
+ columnPosition =
((Integer)expression.getConstantValueAsObject()).intValue();
resultCol = targetCols.getOrderByColumn(columnPosition);
+
if (resultCol == null) {
- throw
StandardException.newException(SQLState.LANG_COLUMN_OUT_OF_RANGE,
String.valueOf(columnPosition));
+ throw
StandardException.newException(SQLState.LANG_COLUMN_OUT_OF_RANGE,
+
String.valueOf(columnPosition));
}
+
+ }else{
+ ResultColumnList targetCols = target.getResultColumns();
+ ResultColumn col = null;
+ int i = 1;
+
+ for(i = 1;
+ i <= targetCols.size();
+ i ++){
+
+ col = targetCols.getOrderByColumn(i);
+ if(col != null &&
+ col.getExpression() == expression){
+
+ break;
+ }
+ }
+
+ resultCol = col;
+ columnPosition = i;
+
}
// Verify that the column is orderable
@@ -257,44 +195,33 @@
public void pullUpOrderByColumn(ResultSetNode target)
throws StandardException
{
- if (columnName != null)
- {
- /* If correlation name is not null, then we look an RC
whose expression is a
- * ColumnReference with the same table number as the
FromTable with
- * correlationName as its exposed name.
- * If correlation name is null, then we simply look for
an RC whose name matches
- * columnName.
- */
- ResultColumnList targetCols =
target.getResultColumns();
- resultCol = targetCols.getOrderByColumn(columnName,
correlationName);
- if (resultCol == null)
- {// add this order by column to the result set
+ if(expression instanceof ColumnReference){
- TableName tabName = null;
- if (schemaName != null || correlationName !=
null)
- {
- tabName = (TableName)
getNodeFactory().getNode(
-
C_NodeTypes.TABLE_NAME,
-
schemaName,
-
correlationName,
-
getContextManager());
- }
-
- ColumnReference cr = (ColumnReference)
getNodeFactory().getNode(
-
C_NodeTypes.COLUMN_REFERENCE,
-
columnName,
-
tabName,
-
getContextManager());
-
- resultCol = (ResultColumn)
getNodeFactory().getNode(
-
C_NodeTypes.RESULT_COLUMN,
-
columnName,
-
cr, // column reference
-
getContextManager());
+ ColumnReference cr = (ColumnReference) expression;
+ ResultColumnList targetCols = target.getResultColumns();
+ resultCol =
targetCols.getOrderByColumn(cr.getColumnName(),
+ cr.tableName !=
null ?
+
cr.tableName.getFullTableName():
+ null);
+
+ if(resultCol == null){
+ resultCol = (ResultColumn)
getNodeFactory().getNode(C_NodeTypes.RESULT_COLUMN,
+
cr.getColumnName(),
+
cr,
+
getContextManager());
targetCols.addResultColumn(resultCol);
targetCols.incOrderBySelect();
}
+
+ }else if(!isReferedColByNum(expression)){
+ ResultColumnList targetCols =
target.getResultColumns();
+ resultCol = (ResultColumn)
getNodeFactory().getNode(C_NodeTypes.RESULT_COLUMN,
+
null,
+
expression,
+
getContextManager());
+ targetCols.addResultColumn(resultCol);
+ targetCols.incOrderBySelect();
}
}
@@ -345,4 +272,80 @@
resultCol.setExpression(
resultCol.getExpression().remapColumnReferencesToExpressions());
}
+
+ private static boolean isReferedColByNum(ValueNode expression)
+ throws StandardException{
+
+ if(!expression.isConstantExpression()){
+ return false;
+ }
+
+ return expression.getConstantValueAsObject() instanceof Integer;
+ }
+
+
+ private static ResultColumn resolveColumnReference(ResultSetNode target,
+ ColumnReference cr)
+ throws StandardException{
+
+ ResultColumn resultCol = null;
+
+ int sourceTableNumber = -1;
+
+ //bug 5716 - for db2 compatibility - no qualified names allowed
in order by clause when union/union all operator is used
+
+ if (target instanceof SetOperatorNode && cr.getTableName() !=
null){
+ String fullName = cr.getSQLColumnName();
+ throw
StandardException.newException(SQLState.LANG_QUALIFIED_COLUMN_NAME_NOT_ALLOWED,
fullName);
+ }
+
+ if(cr.getTableNameNode() != null){
+ TableName tableNameNode = cr.getTableNameNode();
+
+ FromTable fromTable =
target.getFromTableByName(tableNameNode.getTableName(),
+
(tableNameNode.hasSchema() ?
+
tableNameNode.getSchemaName():null),
+ true);
+ if(fromTable == null){
+ fromTable =
target.getFromTableByName(tableNameNode.getTableName(),
+
(tableNameNode.hasSchema() ?
+
tableNameNode.getSchemaName():null),
+ false);
+ if(fromTable == null){
+ String fullName =
cr.getTableNameNode().toString();
+ throw
StandardException.newException(SQLState.LANG_EXPOSED_NAME_NOT_FOUND, fullName);
+ }
+ }
+
+ /* HACK - if the target is a UnionNode, then we have to
+ * have special code to get the sourceTableNumber.
This is
+ * because of the gyrations we go to with building the
RCLs
+ * for a UnionNode.
+ */
+ if (target instanceof SetOperatorNode)
+ {
+ sourceTableNumber = ((FromTable)
target).getTableNumber();
+ }
+ else
+ {
+ sourceTableNumber = fromTable.getTableNumber();
+ }
+
+ }
+
+ ResultColumnList targetCols = target.getResultColumns();
+
+ resultCol = targetCols.getOrderByColumn(cr.getColumnName(),
+ cr.getTableName(),
+ sourceTableNumber);
+
+ if (resultCol == null || resultCol.isNameGenerated()){
+ String errString = cr.columnName;
+ throw
StandardException.newException(SQLState.LANG_ORDER_BY_COLUMN_NOT_FOUND,
errString);
+ }
+
+ return resultCol;
+
+ }
+
}
Modified:
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableName.java
URL:
http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableName.java?view=diff&r1=159745&r2=159746
==============================================================================
---
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableName.java
(original)
+++
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableName.java
Fri Apr 1 16:12:48 2005
@@ -107,6 +107,16 @@
}
/**
+ * Return true if this instance was initialized with not null
schemaName.
+ *
+ * @return true if this instance was initialized with not null
schemaName
+ */
+
+ public boolean hasSchema(){
+ return hasSchema;
+ }
+
+ /**
* Get the schema name.
*
* @return Schema name as a String
Modified:
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL:
http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?view=diff&r1=159745&r2=159746
==============================================================================
---
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
(original)
+++
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
Fri Apr 1 16:12:48 2005
@@ -6584,71 +6584,20 @@
orderCol = sortKey() /* [ collateClause() ] */ [
orderingSpecification(orderCol) ]
{
orderCols.addOrderByColumn(orderCol);
- }
+ }
}
OrderByColumn
sortKey() throws StandardException :
{
- String firstName;
- String secondName = null;
- String thirdName = null;
- String columnName = null;
- String correlationName = null;
- String schemaName = null;
- int columnPosition;
+ ValueNode columnExpression;
}
{
- firstName = identifier(DB2Limit.DB2_MAX_IDENTIFIER_LENGTH128, false)
- [
- <PERIOD> secondName =
identifier(DB2Limit.DB2_MAX_IDENTIFIER_LENGTH128, false)
- [
- <PERIOD> thirdName =
identifier(DB2Limit.DB2_MAX_IDENTIFIER_LENGTH128, false)
- ]
- ]
- {
- // Figure out what each name stands for
- if (thirdName == null)
- {
- if (secondName == null)
- {
- // only one name, so must be column name
- columnName = firstName;
- }
- else
- {
- // Two names: correlation.column
- correlationName = firstName;
- columnName = secondName;
- }
- }
- else
- {
- // Three names: schema.correlation.column
- schemaName = firstName;
- correlationName = secondName;
- columnName = thirdName;
- }
-
- //column name and schema name can not be longer than 30
characters and correlation name can not be longer than 128 characters
- checkIdentifierLengthLimit(columnName,
DB2Limit.DB2_MAX_IDENTIFIER_LENGTH30);
- if (schemaName != null)
- checkIdentifierLengthLimit(schemaName,
DB2Limit.DB2_MAX_IDENTIFIER_LENGTH30);
- if (correlationName != null)
- checkIdentifierLengthLimit(correlationName,
DB2Limit.DB2_MAX_IDENTIFIER_LENGTH128);
- return (OrderByColumn) nodeFactory.getNode(
-
C_NodeTypes.ORDER_BY_COLUMN,
- columnName,
- correlationName,
- schemaName,
-
getContextManager());
- }
-|
- columnPosition = uint_value()
+ columnExpression = additiveExpression(null,0,true)
{
return (OrderByColumn) nodeFactory.getNode(
C_NodeTypes.ORDER_BY_COLUMN,
-
ReuseFactory.getInteger(columnPosition),
+
columnExpression,
getContextManager());
}
}
Modified:
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
URL:
http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out?view=diff&r1=159745&r2=159746
==============================================================================
---
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
(original)
+++
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
Fri Apr 1 16:12:48 2005
@@ -345,9 +345,15 @@
2
1
NULL
-ij> -- . order by on expression (not allowed)
+ij> -- . order by on expression (allowed)
select i from obt order by i+1;
-ERROR 42X01: Syntax error: Encountered "+" at line 2, column 29.
+I
+-----------
+1
+1
+2
+3
+NULL
ij> -- . order by on qualified column name, incorrect correlation name (not
allowed)
select i from obt t order by obt.i;
ERROR 42X04: Column 'OBT.I' is not in any table in the FROM list or it appears
within a join specification and is outside the scope of the join specification
or it appears in a HAVING clause and is not in the GROUP BY list. If this is a
CREATE or ALTER TABLE statement then 'OBT.I' is not a column in the target
table.
@@ -863,6 +869,352 @@
2 |NULL
3 |NULL
0 |NULL
+ij> --Test addtive expression in order clause
+create table test_word(value varchar(32));
+0 rows inserted/updated/deleted
+ij> insert into test_word(value) values('anaconda');
+1 row inserted/updated/deleted
+ij> insert into test_word(value) values('America');
+1 row inserted/updated/deleted
+ij> insert into test_word(value) values('camel');
+1 row inserted/updated/deleted
+ij> insert into test_word(value) values('Canada');
+1 row inserted/updated/deleted
+ij> select * from test_word order by value;
+VALUE
+--------------------------------
+America
+Canada
+anaconda
+camel
+ij> select * from test_word order by upper(value);
+VALUE
+--------------------------------
+America
+anaconda
+camel
+Canada
+ij> drop table test_word;
+0 rows inserted/updated/deleted
+ij> create table test_number(value integer);
+0 rows inserted/updated/deleted
+ij> insert into test_number(value) values(-1);
+1 row inserted/updated/deleted
+ij> insert into test_number(value) values(0);
+1 row inserted/updated/deleted
+ij> insert into test_number(value) values(1);
+1 row inserted/updated/deleted
+ij> insert into test_number(value) values(2);
+1 row inserted/updated/deleted
+ij> insert into test_number(value) values(3);
+1 row inserted/updated/deleted
+ij> insert into test_number(value) values(100);
+1 row inserted/updated/deleted
+ij> insert into test_number(value) values(1000);
+1 row inserted/updated/deleted
+ij> select * from test_number order by value;
+VALUE
+-----------
+-1
+0
+1
+2
+3
+100
+1000
+ij> select * from test_number order by value + 1;
+VALUE
+-----------
+-1
+0
+1
+2
+3
+100
+1000
+ij> select * from test_number order by value - 1;
+VALUE
+-----------
+-1
+0
+1
+2
+3
+100
+1000
+ij> select * from test_number order by value * 1;
+VALUE
+-----------
+-1
+0
+1
+2
+3
+100
+1000
+ij> select * from test_number order by value / 1;
+VALUE
+-----------
+-1
+0
+1
+2
+3
+100
+1000
+ij> select * from test_number order by 1 + value;
+VALUE
+-----------
+-1
+0
+1
+2
+3
+100
+1000
+ij> select * from test_number order by 1 - value;
+VALUE
+-----------
+1000
+100
+3
+2
+1
+0
+-1
+ij> select * from test_number order by 1 * value;
+VALUE
+-----------
+-1
+0
+1
+2
+3
+100
+1000
+ij> select * from test_number where value <> 0 order by 6000 / value;
+VALUE
+-----------
+-1
+1000
+100
+3
+2
+1
+ij> select * from test_number order by -1 + value;
+VALUE
+-----------
+-1
+0
+1
+2
+3
+100
+1000
+ij> select * from test_number order by -1 - value;
+VALUE
+-----------
+1000
+100
+3
+2
+1
+0
+-1
+ij> select * from test_number order by - 1 * value;
+VALUE
+-----------
+1000
+100
+3
+2
+1
+0
+-1
+ij> select * from test_number where value <> 0 order by - 6000 / value;
+VALUE
+-----------
+1
+2
+3
+100
+1000
+-1
+ij> select * from test_number order by abs(value);
+VALUE
+-----------
+0
+1
+-1
+2
+3
+100
+1000
+ij> select * from test_number order by value desc;
+VALUE
+-----------
+1000
+100
+3
+2
+1
+0
+-1
+ij> select * from test_number order by value + 1 desc;
+VALUE
+-----------
+1000
+100
+3
+2
+1
+0
+-1
+ij> select * from test_number order by value - 1 desc;
+VALUE
+-----------
+1000
+100
+3
+2
+1
+0
+-1
+ij> select * from test_number order by value * 1 desc;
+VALUE
+-----------
+1000
+100
+3
+2
+1
+0
+-1
+ij> select * from test_number order by value / 1 desc;
+VALUE
+-----------
+1000
+100
+3
+2
+1
+0
+-1
+ij> select * from test_number order by 1 + value desc;
+VALUE
+-----------
+1000
+100
+3
+2
+1
+0
+-1
+ij> select * from test_number order by 1 - value desc;
+VALUE
+-----------
+-1
+0
+1
+2
+3
+100
+1000
+ij> select * from test_number order by 1 * value desc;
+VALUE
+-----------
+1000
+100
+3
+2
+1
+0
+-1
+ij> select * from test_number where value <> 0 order by 6000 / value desc;
+VALUE
+-----------
+1
+2
+3
+100
+1000
+-1
+ij> select * from test_number order by -1 + value desc;
+VALUE
+-----------
+1000
+100
+3
+2
+1
+0
+-1
+ij> select * from test_number order by -1 - value desc;
+VALUE
+-----------
+-1
+0
+1
+2
+3
+100
+1000
+ij> select * from test_number order by - 1 * value desc;
+VALUE
+-----------
+-1
+0
+1
+2
+3
+100
+1000
+ij> select * from test_number where value <> 0 order by - 6000 / value desc;
+VALUE
+-----------
+-1
+1000
+100
+3
+2
+1
+ij> select * from test_number order by abs(value) desc;
+VALUE
+-----------
+1000
+100
+3
+2
+1
+-1
+0
+ij> drop table test_number;
+0 rows inserted/updated/deleted
+ij> create table test_number2(value1 integer,value2 integer);
+0 rows inserted/updated/deleted
+ij> insert into test_number2(value1,value2) values(-2,2);
+1 row inserted/updated/deleted
+ij> insert into test_number2(value1,value2) values(-1,2);
+1 row inserted/updated/deleted
+ij> insert into test_number2(value1,value2) values(0,1);
+1 row inserted/updated/deleted
+ij> insert into test_number2(value1,value2) values(0,2);
+1 row inserted/updated/deleted
+ij> insert into test_number2(value1,value2) values(1,1);
+1 row inserted/updated/deleted
+ij> insert into test_number2(value1,value2) values(2,1);
+1 row inserted/updated/deleted
+ij> select * from test_number2 order by abs(value1),mod(value2,2);
+VALUE1 |VALUE2
+-----------------------
+0 |2
+0 |1
+-1 |2
+1 |1
+-2 |2
+2 |1
+ij> drop table test_number2;
+0 rows inserted/updated/deleted
ij> -- error case
select * from t order by d;
ERROR 42X04: Column 'D' is not in any table in the FROM list or it appears
within a join specification and is outside the scope of the join specification
or it appears in a HAVING clause and is not in the GROUP BY list. If this is a
CREATE or ALTER TABLE statement then 'D' is not a column in the target table.
@@ -895,5 +1247,47 @@
ij> select s.a from t s order by s.d;
ERROR 42X04: Column 'S.D' is not in any table in the FROM list or it appears
within a join specification and is outside the scope of the join specification
or it appears in a HAVING clause and is not in the GROUP BY list. If this is a
CREATE or ALTER TABLE statement then 'S.D' is not a column in the target table.
ij> drop table t;
+0 rows inserted/updated/deleted
+ij> -- test fof using table correlation names
+select * from (values (2),(1)) as t(x) order by t.x;
+X
+-----------
+1
+2
+ij> create table ta(id int);
+0 rows inserted/updated/deleted
+ij> create table tb(id int,c1 int,c2 int);
+0 rows inserted/updated/deleted
+ij> insert into ta(id) values(1);
+1 row inserted/updated/deleted
+ij> insert into ta(id) values(2);
+1 row inserted/updated/deleted
+ij> insert into ta(id) values(3);
+1 row inserted/updated/deleted
+ij> insert into ta(id) values(4);
+1 row inserted/updated/deleted
+ij> insert into ta(id) values(5);
+1 row inserted/updated/deleted
+ij> insert into tb(id,c1,c2) values(1,5,3);
+1 row inserted/updated/deleted
+ij> insert into tb(id,c1,c2) values(2,4,3);
+1 row inserted/updated/deleted
+ij> insert into tb(id,c1,c2) values(3,4,2);
+1 row inserted/updated/deleted
+ij> insert into tb(id,c1,c2) values(4,4,1);
+1 row inserted/updated/deleted
+ij> insert into tb(id,c1,c2) values(5,4,2);
+1 row inserted/updated/deleted
+ij> select t1.id,t2.c1 from ta as t1 join tb as t2 on t1.id = t2.id order by
t2.c1,t2.c2,t1.id;
+ID |C1
+-----------------------
+4 |4
+3 |4
+5 |4
+2 |4
+1 |5
+ij> drop table ta;
+0 rows inserted/updated/deleted
+ij> drop table tb;
0 rows inserted/updated/deleted
ij>
Modified:
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
URL:
http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql?view=diff&r1=159745&r2=159746
==============================================================================
---
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
(original)
+++
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
Fri Apr 1 16:12:48 2005
@@ -133,7 +133,7 @@
-- . order by on column not in select, in table (error)
select i from obt order by v;
--- . order by on expression (not allowed)
+-- . order by on expression (allowed)
select i from obt order by i+1;
-- . order by on qualified column name, incorrect correlation name (not
allowed)
@@ -350,6 +350,66 @@
select a, b, c from t order by b, c;
select b, c from t order by app.t.a;
+
+--Test addtive expression in order clause
+
+create table test_word(value varchar(32));
+insert into test_word(value) values('anaconda');
+insert into test_word(value) values('America');
+insert into test_word(value) values('camel');
+insert into test_word(value) values('Canada');
+
+select * from test_word order by value;
+select * from test_word order by upper(value);
+
+drop table test_word;
+
+create table test_number(value integer);
+insert into test_number(value) values(-1);
+insert into test_number(value) values(0);
+insert into test_number(value) values(1);
+insert into test_number(value) values(2);
+insert into test_number(value) values(3);
+insert into test_number(value) values(100);
+insert into test_number(value) values(1000);
+select * from test_number order by value;
+select * from test_number order by value + 1;
+select * from test_number order by value - 1;
+select * from test_number order by value * 1;
+select * from test_number order by value / 1;
+select * from test_number order by 1 + value;
+select * from test_number order by 1 - value;
+select * from test_number order by 1 * value;
+select * from test_number where value <> 0 order by 6000 / value;
+select * from test_number order by -1 + value;
+select * from test_number order by -1 - value;
+select * from test_number order by - 1 * value;
+select * from test_number where value <> 0 order by - 6000 / value;
+select * from test_number order by abs(value);
+select * from test_number order by value desc;
+select * from test_number order by value + 1 desc;
+select * from test_number order by value - 1 desc;
+select * from test_number order by value * 1 desc;
+select * from test_number order by value / 1 desc;
+select * from test_number order by 1 + value desc;
+select * from test_number order by 1 - value desc;
+select * from test_number order by 1 * value desc;
+select * from test_number where value <> 0 order by 6000 / value desc;
+select * from test_number order by -1 + value desc;
+select * from test_number order by -1 - value desc;
+select * from test_number order by - 1 * value desc;
+select * from test_number where value <> 0 order by - 6000 / value desc;
+select * from test_number order by abs(value) desc;
+drop table test_number;
+create table test_number2(value1 integer,value2 integer);
+insert into test_number2(value1,value2) values(-2,2);
+insert into test_number2(value1,value2) values(-1,2);
+insert into test_number2(value1,value2) values(0,1);
+insert into test_number2(value1,value2) values(0,2);
+insert into test_number2(value1,value2) values(1,1);
+insert into test_number2(value1,value2) values(2,1);
+select * from test_number2 order by abs(value1),mod(value2,2);
+drop table test_number2;
-- error case
select * from t order by d;
select t.* from t order by d;
@@ -371,3 +431,23 @@
select s.a from t s order by s.d;
drop table t;
+
+-- test fof using table correlation names
+select * from (values (2),(1)) as t(x) order by t.x;
+
+create table ta(id int);
+create table tb(id int,c1 int,c2 int);
+insert into ta(id) values(1);
+insert into ta(id) values(2);
+insert into ta(id) values(3);
+insert into ta(id) values(4);
+insert into ta(id) values(5);
+insert into tb(id,c1,c2) values(1,5,3);
+insert into tb(id,c1,c2) values(2,4,3);
+insert into tb(id,c1,c2) values(3,4,2);
+insert into tb(id,c1,c2) values(4,4,1);
+insert into tb(id,c1,c2) values(5,4,2);
+select t1.id,t2.c1 from ta as t1 join tb as t2 on t1.id = t2.id order by
t2.c1,t2.c2,t1.id;
+
+drop table ta;
+drop table tb;
