db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r639935 - in /db/derby/code/branches/10.3/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 Sat, 22 Mar 2008 01:37:50 GMT
Author: bpendleton
Date: Fri Mar 21 18:37:46 2008
New Revision: 639935

URL: http://svn.apache.org/viewvc?rev=639935&view=rev
Log:
DERBY-3373: SQL distinct and order by needed together.

Merged change from the 10.4 branch via svn merge -r 639012:639013 ../10.4/


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

Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java?rev=639935&r1=639934&r2=639935&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java
(original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java
Fri Mar 21 18:37:46 2008
@@ -197,10 +197,6 @@
             resolveAddedColumn(target);
 		if (resultCol == null)
 			throw StandardException.newException(SQLState.LANG_UNION_ORDER_BY);
-			if (addedColumnOffset >= 0 &&
-					target instanceof SelectNode &&
-					( (SelectNode)target ).hasDistinct())
-				throw StandardException.newException(SQLState.LANG_DISTINCT_ORDER_BY_EXPRESSION);
 		}
 
 		// Verify that the column is orderable

Modified: db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out?rev=639935&r1=639934&r2=639935&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
(original)
+++ db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
Fri Mar 21 18:37:46 2008
@@ -1698,10 +1698,14 @@
 2          |b         
 3          |a         
 4          |c         
-ij> -- DERBY-2351 causes this statement to return 4 rows, which it should
--- instead show an error:
+ij> -- This statement is legitimate. Even though c1+1 is not distinct, c1 is:
 select distinct c1, c2 from t1 order by c1+1;
-ERROR 4287A: The ORDER BY clause may not specify an expression, since the query specifies
DISTINCT.
+C1         |C2        
+----------------------
+1          |c         
+2          |b         
+3          |a         
+4          |c         
 ij> -- DERBY-2351 causes this statement to return 4 rows, which it should
 -- instead show an error. Note that the rows returned are not distinct!
 select distinct c2 from t1 order by c1;
@@ -1721,9 +1725,14 @@
 2          |b         
 1          |c         
 4          |c         
-ij> -- This query should not work because the expanded * does not include c1+1:
+ij> -- After the * is expanded, the query contains c1, so this is legitimate:
 select distinct * from t1 order by c1+1;
-ERROR 4287A: The ORDER BY clause may not specify an expression, since the query specifies
DISTINCT.
+C1         |C2        
+----------------------
+1          |c         
+2          |b         
+3          |a         
+4          |c         
 ij> -- This query also should not work because the order by col is not in result:
 select distinct t1.* from t1, t2 where t1.c1=t2.t2c1 order by t2c1;
 ERROR 42879: The ORDER BY clause may not contain column 'T2C1', since the query specifies
DISTINCT and that column does not appear in the query result.
@@ -1757,6 +1766,25 @@
 SELECT DISTINCT name FROM person ORDER BY name desc;
 NAME      
 ----------
+Mary      
+John      
+ij> -- Ordering by an expression involving name is legitimate:
+select distinct name from person order by upper(name);
+NAME      
+----------
+John      
+Mary      
+ij> -- Ordering by an expression involving an unselected column is not. However,
+-- Derby does not currently enforce this restriction. Note that the answer
+-- that Derby returns is incorrect: Derby returns two rows with duplicate
+-- 'name' values. This is because Derby currently implicitly includes the
+-- '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.
+select distinct name from person order by age*2;
+NAME      
+----------
+John      
 Mary      
 John      
 ij> -- Some test cases involving column aliasing:

Modified: db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql?rev=639935&r1=639934&r2=639935&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
(original)
+++ db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
Fri Mar 21 18:37:46 2008
@@ -651,8 +651,7 @@
 insert into t2 values (4), (3);
 -- This query should return 4 distinct rows, ordered by column c1:
 select distinct c1, c2 from t1 order by c1;
--- DERBY-2351 causes this statement to return 4 rows, which it should
--- instead show an error:
+-- This statement is legitimate. Even though c1+1 is not distinct, c1 is:
 select distinct c1, c2 from t1 order by c1+1;
 -- DERBY-2351 causes this statement to return 4 rows, which it should
 -- instead show an error. Note that the rows returned are not distinct!
@@ -661,7 +660,7 @@
 select distinct c2 from t1 order by c2;
 -- This query should work because * will be expanded to include c2:
 select distinct * from t1 order by c2;
--- This query should not work because the expanded * does not include c1+1:
+-- After the * is expanded, the query contains c1, so this is legitimate:
 select distinct * from t1 order by c1+1;
 -- This query also should not work because the order by col is not in result:
 select distinct t1.* from t1, t2 where t1.c1=t2.t2c1 order by t2c1;
@@ -680,6 +679,16 @@
 SELECT DISTINCT name FROM person ORDER BY name;
 -- This query should return two rows, ordered by name descending:
 SELECT DISTINCT name FROM person ORDER BY name desc;
+-- Ordering by an expression involving name is legitimate:
+select distinct name from person order by upper(name);
+-- Ordering by an expression involving an unselected column is not. However,
+-- Derby does not currently enforce this restriction. Note that the answer
+-- that Derby returns is incorrect: Derby returns two rows with duplicate
+-- 'name' values. This is because Derby currently implicitly includes the
+-- '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.
+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;
 select distinct name as first_name from person order by first_name;



Mime
View raw message