db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r565184 - in /db/derby/code/branches/10.3/java: engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/loc/ shared/org/apache/derby/shared/common/reference/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache...
Date Sun, 12 Aug 2007 22:24:45 GMT
Author: bpendleton
Date: Sun Aug 12 15:24:44 2007
New Revision: 565184

URL: http://svn.apache.org/viewvc?view=rev&rev=565184
Log:
DERBY-2351: Some ambiguous ORDER BY queries are not rejected as invalid.

Merged the change from the trunk via svn merge -r 555095:555096


Modified:
    db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java
    db/derby/code/branches/10.3/java/engine/org/apache/derby/loc/messages.xml
    db/derby/code/branches/10.3/java/shared/org/apache/derby/shared/common/reference/SQLState.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?view=diff&rev=565184&r1=565183&r2=565184
==============================================================================
--- 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
Sun Aug 12 15:24:44 2007
@@ -169,6 +169,10 @@
 			
 			columnPosition = resultCol.getColumnPosition();
 
+			if (addedColumnOffset >= 0 &&
+					target instanceof SelectNode &&
+					( (SelectNode)target ).hasDistinct())
+				throw StandardException.newException(SQLState.LANG_DISTINCT_ORDER_BY, cr.columnName);
 		}else if(isReferedColByNum(expression)){
 			
 			ResultColumnList targetCols = target.getResultColumns();
@@ -187,6 +191,10 @@
             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/engine/org/apache/derby/loc/messages.xml
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/loc/messages.xml?view=diff&rev=565184&r1=565183&r2=565184
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/loc/messages.xml (original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/loc/messages.xml Sun Aug 12 15:24:44
2007
@@ -1185,6 +1185,17 @@
             </msg>
 
             <msg>
+                <name>42879</name>
+                <text>The ORDER BY clause may not contain column '{0}', since the query
specifies DISTINCT and that column does not appear in the query result.</text>
+                <arg>columnName</arg>
+            </msg>
+            <msg>
+                <name>4287A</name>
+                <text>The ORDER BY clause may not specify an expression, since the
query specifies DISTINCT.</text>
+            </msg>
+
+
+            <msg>
                 <name>42884</name>
                 <text>No authorized routine named '{0}' of type '{1}' having compatible
arguments was found.</text>
                 <arg>routineName</arg>

Modified: db/derby/code/branches/10.3/java/shared/org/apache/derby/shared/common/reference/SQLState.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/shared/org/apache/derby/shared/common/reference/SQLState.java?view=diff&rev=565184&r1=565183&r2=565184
==============================================================================
--- db/derby/code/branches/10.3/java/shared/org/apache/derby/shared/common/reference/SQLState.java
(original)
+++ db/derby/code/branches/10.3/java/shared/org/apache/derby/shared/common/reference/SQLState.java
Sun Aug 12 15:24:44 2007
@@ -857,6 +857,8 @@
 	String LANG_DUPLICATE_COLUMN_FOR_ORDER_BY                          = "42X79";
 	String LANG_QUALIFIED_COLUMN_NAME_NOT_ALLOWED                      = "42877";
         String LANG_UNION_ORDER_BY                                         = "42878";
+	String LANG_DISTINCT_ORDER_BY                                      = "42879";
+	String LANG_DISTINCT_ORDER_BY_EXPRESSION                           = "4287A";
 	String LANG_EMPTY_VALUES_CLAUSE                                    = "42X80";
 	String LANG_USING_CARDINALITY_VIOLATION                            = "42X82";
 	String LANG_ADDING_COLUMN_WITH_NULL_AND_NOT_NULL_CONSTRAINT        = "42X83";

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?view=diff&rev=565184&r1=565183&r2=565184
==============================================================================
--- 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
Sun Aug 12 15:24:44 2007
@@ -1674,4 +1674,91 @@
 b   |NULL
 g   |c   
 NULL|d   
+ij> -- Some test cases for DERBY-2351. The issue in DERBY-2351 involves whether
+-- pulled-up ORDER BY columns appear in the result set or not, and how
+-- DISCTINCT interacts with that decision. The point is that DISTINCT should
+-- apply only to the columns specified by the user in the result column list,
+-- not to the extra columns pulled up into the result by the ORDER BY. This
+-- means that some queries should throw an error, but due to DERBY-2351
+-- the queries instead display erroneous results.
+
+create table t1 (c1 int, c2 varchar(10));
+0 rows inserted/updated/deleted
+ij> create table t2 (t2c1 int);
+0 rows inserted/updated/deleted
+ij> insert into t1 values (3, 'a'), (4, 'c'), (2, 'b'), (1, 'c');
+4 rows inserted/updated/deleted
+ij> insert into t2 values (4), (3);
+2 rows inserted/updated/deleted
+ij> -- This query should return 4 distinct rows, ordered by column c1:
+select distinct c1, c2 from t1 order by c1;
+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:
+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.
+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;
+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> -- This query should return 3 distinct rows, ordered by column c2
+select distinct c2 from t1 order by c2;
+C2        
+----------
+a         
+b         
+c         
+ij> -- This query should work because * will be expanded to include c2:
+select distinct * from t1 order by c2;
+C1         |C2        
+----------------------
+3          |a         
+2          |b         
+1          |c         
+4          |c         
+ij> -- This query should not work because the expanded * does not include c1+1:
+select distinct * from t1 order by c1+1;
+ERROR 4287A: The ORDER BY clause may not specify an expression, since the query specifies
DISTINCT.
+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.
+ij> -- But without the distinct it should be fine:
+select t1.* from t1, t2 where t1.c1=t2.t2c1 order by t2c1;
+C1         |C2        
+----------------------
+3          |a         
+4          |c         
+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);
+1 row inserted/updated/deleted
+ij> insert into person values ('John', 30);
+1 row inserted/updated/deleted
+ij> insert into person values ('Mary', 20);
+1 row inserted/updated/deleted
+ij> -- DERBY-2351 causes this statement to display 3 rows, when it should
+-- instead show an error. Again, note that the rows returned are not distinct.
+SELECT DISTINCT name FROM person ORDER BY age;
+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> -- This query should return two rows, ordered by name.
+SELECT DISTINCT name FROM person ORDER BY name;
+NAME      
+----------
+John      
+Mary      
+ij> -- This query should return two rows, ordered by name descending:
+SELECT DISTINCT name FROM person ORDER BY name desc;
+NAME      
+----------
+Mary      
+John      
+ij> drop table person;
+0 rows inserted/updated/deleted
 ij> 

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?view=diff&rev=565184&r1=565183&r2=565184
==============================================================================
--- 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
Sun Aug 12 15:24:44 2007
@@ -636,3 +636,51 @@
 select id, id i from D2459_A1 union select id j, id from D2459_A2 order by 2;
 select id, ref from D2459_A1 union select ref, id from D2459_A2;
 select id i, ref j from D2459_A1 union select ref i, id j from D2459_A2;
+
+-- Some test cases for DERBY-2351. The issue in DERBY-2351 involves whether
+-- pulled-up ORDER BY columns appear in the result set or not, and how
+-- DISCTINCT interacts with that decision. The point is that DISTINCT should
+-- apply only to the columns specified by the user in the result column list,
+-- not to the extra columns pulled up into the result by the ORDER BY. This
+-- means that some queries should throw an error, but due to DERBY-2351
+-- the queries instead display erroneous results.
+
+create table t1 (c1 int, c2 varchar(10));
+create table t2 (t2c1 int);
+insert into t1 values (3, 'a'), (4, 'c'), (2, 'b'), (1, 'c');
+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:
+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!
+select distinct c2 from t1 order by c1;
+-- This query should return 3 distinct rows, ordered by column c2
+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:
+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;
+-- But without the distinct it should be fine:
+select t1.* from t1, t2 where t1.c1=t2.t2c1 order by t2c1;
+drop table t1;
+
+create table person (name varchar(10), age int);
+insert into person values ('John', 10);
+insert into person values ('John', 30);
+insert into person values ('Mary', 20);
+-- DERBY-2351 causes this statement to display 3 rows, when it should
+-- instead show an error. Again, note that the rows returned are not distinct.
+SELECT DISTINCT name FROM person ORDER BY age;
+-- This query should return two rows, ordered by name.
+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;
+drop table person;
+
+
+



Mime
View raw message