db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From banda...@apache.org
Subject svn commit: r159746 - in incubator/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Sat, 02 Apr 2005 00:12:50 GMT
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. (tomonaka@basil.ocn.ne.jp)

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;



Mime
View raw message