db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kahat...@apache.org
Subject svn commit: r989048 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/OrderByColumn.java testing/org/apache/derbyTesting/functionTests/master/orderby.out testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
Date Wed, 25 Aug 2010 12:09:44 GMT
Author: kahatlen
Date: Wed Aug 25 12:09:43 2010
New Revision: 989048

URL: http://svn.apache.org/viewvc?rev=989048&view=rev
Log:
DERBY-4371: Non-selected columns for SELECT DISTINCT allowed in ORDER BY clause if ordered
by expression

Patch contributed by Nirmal Fernando <nirmal070125@gmail.com>.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java?rev=989048&r1=989047&r2=989048&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java Wed
Aug 25 12:09:43 2010
@@ -21,18 +21,12 @@
 
 package	org.apache.derby.impl.sql.compile;
 
-import org.apache.derby.iapi.types.TypeId;
+import java.util.Iterator;
 
 import org.apache.derby.iapi.error.StandardException;
 import org.apache.derby.iapi.reference.SQLState;
-
 import org.apache.derby.iapi.services.sanity.SanityManager;
-
-import org.apache.derby.iapi.sql.compile.NodeFactory;
 import org.apache.derby.iapi.sql.compile.C_NodeTypes;
-
-import org.apache.derby.iapi.util.ReuseFactory;
-import org.apache.derby.iapi.sql.compile.Visitable;
 import org.apache.derby.iapi.sql.compile.Visitor;
 
 /**
@@ -239,18 +233,92 @@ public class OrderByColumn extends Order
 			}
 
 		}else{
-            if( SanityManager.DEBUG)
-                SanityManager.ASSERT( addedColumnOffset >= 0,
-                                      "Order by expression was not pulled into the result
column list");
-            resolveAddedColumn(target);
-		if (resultCol == null)
-			throw StandardException.newException(SQLState.LANG_UNION_ORDER_BY);
+			/*checks for the conditions when using distinct*/
+			if (addedColumnOffset >= 0 &&
+					target instanceof SelectNode &&
+					((SelectNode)target).hasDistinct() &&
+					!expressionMatch(target))
+			{
+				String col=null;
+				boolean match=false;
+
+				CollectNodesVisitor collectNodesVisitor =
+					new CollectNodesVisitor(ColumnReference.class);
+				expression.accept(collectNodesVisitor);
+
+				for (Iterator it = collectNodesVisitor.getList().iterator();
+				it.hasNext(); )
+				{//visits through the columns in this OrderByColumn
+					ColumnReference cr1=(ColumnReference)it.next();
+					col=cr1.getColumnName();
+					match = columnMatchFound(target,cr1);
+					/* breaks if a match not found, this is needed
+					 * because all column references in this
+					 * OrderByColumn should be there in the select
+					 * clause.*/
+					if(!match)
+						throw StandardException.newException(
+								SQLState.LANG_DISTINCT_ORDER_BY,
+								col);
+				}
+			}
+
+			if( SanityManager.DEBUG)
+				SanityManager.ASSERT( addedColumnOffset >= 0,
+				"Order by expression was not pulled into the result column list");
+			resolveAddedColumn(target);
+			if (resultCol == null)
+				throw StandardException.newException(SQLState.LANG_UNION_ORDER_BY);
 		}
 
 		// Verify that the column is orderable
 		resultCol.verifyOrderable();
 	}
 
+	/**
+	 * Checks whether the whole expression (OrderByColumn) itself
+	 * found in the select clause.
+	 * @param target Result set
+	 * @return boolean: whether any expression match found
+	 * @throws StandardException
+	 */
+	private boolean expressionMatch(ResultSetNode target)
+										throws StandardException{
+		ResultColumnList rcl=target.getResultColumns();
+		for (int i=1; i<=rcl.visibleSize();i++){
+			//since RCs are 1 based
+			if((rcl.getResultColumn(i)).isEquivalent(
+					resultCol))
+				return true;
+		}
+		return false;
+	}
+
+	/**
+	 * This method checks a ColumnReference of this OrderByColumn
+	 * against the ColumnReferences of the select clause of the query.
+	 * @param target result set
+	 * @param crOfExpression the CR to be checked
+	 * @return whether a match found or not
+	 * @throws StandardException
+	 */
+	private boolean columnMatchFound(ResultSetNode target,
+			ColumnReference crOfExpression) throws StandardException{
+		ResultColumnList rcl=target.getResultColumns();
+		for (int i=1; i<=rcl.visibleSize();
+		i++){//grab the RCs related to select clause
+			ValueNode exp=rcl.getResultColumn(i).getExpression();
+			if(exp instanceof ColumnReference)
+			{//visits through the columns in the select clause
+				ColumnReference cr2 =
+					(ColumnReference) (exp);
+				if(crOfExpression.isEquivalent(cr2))
+					return true;
+			}
+		}
+		return false;
+	}
+
     /**
      * Assuming this OrderByColumn was "pulled" into the received target's
      * ResultColumnList (because it wasn't there to begin with), use

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out?rev=989048&r1=989047&r2=989048&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
Wed Aug 25 12:09:43 2010
@@ -1777,6 +1777,84 @@ C1         |C2        
 4          |c         
 ij> drop table t1;
 0 rows inserted/updated/deleted
+ij> -- some test cases for DERBY-4371: This issue addresses the situations where
+-- the order by clause contains an expression such as j*2, i/j etc. when the distict
+-- is specified in the select clause. The rule is, a query will be a valid one if,
+-- 1) the exact expression can be found in the select clause OR
+-- 2) all the ColumnReferences of the expression can be found in the select clause
+create table t1 (c1 int, c2 int);
+0 rows inserted/updated/deleted
+ij> insert into t1 values (3, 1), (4, 2), (2, 1), (1, 3);
+4 rows inserted/updated/deleted
+ij> -- Following query should be failed since neither C1*2 nor C1 is there 
+-- in the select clause
+select distinct C2 from t1 order by C1*2;
+ERROR 42879: The ORDER BY clause may not contain column 'C1', since the query specifies DISTINCT
and that column does not appear in the query result.
+ij> -- Following query should be failed since neither C1*2 nor C1 is there 
+-- in the select clause
+select distinct C2,C1*3 from t1 order by C1*2;
+ERROR 42879: The ORDER BY clause may not contain column 'C1', since the query specifies DISTINCT
and that column does not appear in the query result.
+ij> -- Following query should be failed since neither C1*3 nor C1 is there 
+-- in the select clause
+select distinct C2 from t1 order by C2, C1*3;
+ERROR 42879: The ORDER BY clause may not contain column 'C1', since the query specifies DISTINCT
and that column does not appear in the query result.
+ij> -- Following query should be failed since C1/C2 or (C1 & C2) is not there 
+-- in the select clause
+select distinct C1*C2 from t1 order by C1/C2;
+ERROR 42879: The ORDER BY clause may not contain column 'C1', since the query specifies DISTINCT
and that column does not appear in the query result.
+ij> -- Following query should be failed since neither -C2 nor C2 is there 
+-- in the select clause
+select distinct C1 from t1 order by -C2;
+ERROR 42879: The ORDER BY clause may not contain column 'C2', since the query specifies DISTINCT
and that column does not appear in the query result.
+ij> -- Following query should be failed since neither C1/C2 nor C2 is there 
+-- in the select clause
+select distinct C1 from t1 order by C1/C2;
+ERROR 42879: The ORDER BY clause may not contain column 'C2', since the query specifies DISTINCT
and that column does not appear in the query result.
+ij> -- This query is valid since C2 is there in the select clause though
+-- C2*2 itself not.
+select distinct C1,C2 from t1 order by C2*2;
+C1         |C2         
+-----------------------
+2          |1          
+3          |1          
+4          |2          
+1          |3          
+ij> -- This query is valid since C2*2 is there in the select clause.
+select distinct C1,C2*2 from t1 order by C2*2;
+C1         |2          
+-----------------------
+2          |2          
+3          |2          
+4          |4          
+1          |6          
+ij> -- This query is valid since both C1 & C2 is there in the select clause though
+-- neither C1*2 nor C2*3 is there.
+select distinct C1,C2 from t1 order by C1*2,C2*3;
+C1         |C2         
+-----------------------
+1          |3          
+2          |1          
+3          |1          
+4          |2          
+ij> -- This query is valid since the select all is there though
+-- neither C2*2 nor C1*3 is there.
+select distinct * from t1 order by C2*2, C1*3;
+C1         |C2         
+-----------------------
+2          |1          
+3          |1          
+4          |2          
+1          |3          
+ij> -- This query is valid since order by column not involve any columns
+select distinct C1 from t1 order by 1+1;
+C1         
+-----------
+1          
+2          
+3          
+4          
+ij> drop table t1;
+0 rows inserted/updated/deleted
 ij> create table person (name varchar(10), age int);
 0 rows inserted/updated/deleted
 ij> insert into person values ('John', 10);
@@ -1814,12 +1892,9 @@ ij> -- Ordering by an expression involvi
 -- 'age' column into the 'distinct' processing due to its presence in the
 -- ORDER BY clause. DERBY-2351 and DERBY-3373 discuss this situation in
 -- more detail.
+-- The above issue is corrected under DERBY-4371
 select distinct name from person order by age*2;
-NAME      
-----------
-John      
-Mary      
-John      
+ERROR 42879: The ORDER BY clause may not contain column 'AGE', since the query specifies
DISTINCT and that column does not appear in the query result.
 ij> -- Some test cases involving column aliasing:
 select distinct name as first_name from person order by name;
 FIRST_NAME

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql?rev=989048&r1=989047&r2=989048&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
Wed Aug 25 12:09:43 2010
@@ -669,6 +669,46 @@ select distinct t1.* from t1, t2 where t
 select t1.* from t1, t2 where t1.c1=t2.t2c1 order by t2c1;
 drop table t1;
 
+-- some test cases for DERBY-4371: This issue addresses the situations where
+-- the order by clause contains an expression such as j*2, i/j etc. when the distict
+-- is specified in the select clause. The rule is, a query will be a valid one if,
+-- 1) the exact expression can be found in the select clause OR
+-- 2) all the ColumnReferences of the expression can be found in the select clause
+create table t1 (c1 int, c2 int);
+insert into t1 values (3, 1), (4, 2), (2, 1), (1, 3);
+-- Following query should be failed since neither C1*2 nor C1 is there 
+-- in the select clause
+select distinct C2 from t1 order by C1*2;
+-- Following query should be failed since neither C1*2 nor C1 is there 
+-- in the select clause
+select distinct C2,C1*3 from t1 order by C1*2;
+ -- Following query should be failed since neither C1*3 nor C1 is there 
+-- in the select clause
+select distinct C2 from t1 order by C2, C1*3;
+-- Following query should be failed since C1/C2 or (C1 & C2) is not there 
+-- in the select clause
+select distinct C1*C2 from t1 order by C1/C2;
+-- Following query should be failed since neither -C2 nor C2 is there 
+-- in the select clause
+select distinct C1 from t1 order by -C2;
+-- Following query should be failed since neither C1/C2 nor C2 is there 
+-- in the select clause
+select distinct C1 from t1 order by C1/C2;
+-- This query is valid since C2 is there in the select clause though
+-- C2*2 itself not.
+select distinct C1,C2 from t1 order by C2*2;
+-- This query is valid since C2*2 is there in the select clause.
+select distinct C1,C2*2 from t1 order by C2*2;
+-- This query is valid since both C1 & C2 is there in the select clause though
+-- neither C1*2 nor C2*3 is there.
+select distinct C1,C2 from t1 order by C1*2,C2*3;
+-- This query is valid since the select all is there though
+-- neither C2*2 nor C1*3 is there.
+select distinct * from t1 order by C2*2, C1*3;
+-- This query is valid since order by column not involve any columns
+select distinct C1 from t1 order by 1+1;
+drop table t1;
+
 create table person (name varchar(10), age int);
 insert into person values ('John', 10);
 insert into person values ('John', 30);
@@ -689,6 +729,7 @@ select distinct name from person order b
 -- 'age' column into the 'distinct' processing due to its presence in the
 -- ORDER BY clause. DERBY-2351 and DERBY-3373 discuss this situation in
 -- more detail.
+-- The above issue is corrected under DERBY-4371
 select distinct name from person order by age*2;
 -- Some test cases involving column aliasing:
 select distinct name as first_name from person order by name;



Mime
View raw message