Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Subject: svn commit: r637529 - 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: Sun, 16 Mar 2008 04:00:44 -0000
To: derby-commits@db.apache.org
From: bpendleton@apache.org
Message-Id: <20080316040044.D39371A9832@eris.apache.org>

Author: bpendleton
Date: Sat Mar 15 21:00:43 2008
New Revision: 637529

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

Derby was issuing the error message "The ORDER BY clause may not specify
an expression, since the query specifies DISTINCT" in situations where
the message was not appropriate. An example of a query that should have
been accepted, but was being rejected, is:

   select name from person order by lower(name)

This patch backs out part of the changes that went in with DERBY-2351;
specifically, this patch reverts the "if" test which was rejecting any
DISTINCT query which had a pulled-up ORDER BY expression. Not all such
expressions are invalid, as the above example shows. Since the "if"
statement cannot distinguish between valid expressions and invalid ones,
it is better to revert to the pre-DERBY-2351 behavior.

A few new tests are also added to demonstrate the behavior.

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=637529&r1=637528&r2=637529&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 Sat Mar 15 21:00:43 2008
@@ -215,10 +215,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/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=637529&r1=637528&r2=637529&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 Sat Mar 15 21:00:43 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/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=637529&r1=637528&r2=637529&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 Sat Mar 15 21:00:43 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;