You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ka...@apache.org on 2010/08/25 14:09:44 UTC
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
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 <ni...@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;