db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r555096 - in /db/derby/code/trunk/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/derbyTe...
Date Tue, 10 Jul 2007 22:06:03 GMT
Author: bpendleton
Date: Tue Jul 10 15:06:02 2007
New Revision: 555096

URL: http://svn.apache.org/viewvc?view=rev&rev=555096
Log:
DERBY-2351: Certain ORDER BY clauses should be rejected as invalid

This change modifies the ORDER BY clause so that it rejects certain
queries as invalid: specifically, queries which:
a) specify the set quantifier DISTINCT,
b) and also contain an ORDER BY clause which refers to a column
or expression which is not in the query result.

The problem with such queries is that we are told to return only
a single instance of the DISTINCT columns, but since the ORDER BY
clause refers to columns which are not in the DISTINCT set, if there
should be multiple candidate rows from which we choose the DISTINCT
result, we don't know which of those rows to use for the ORDER BY
processing.

When the DISTINCT and ORDER BY clauses are in conflict, Derby should
reject the query. This change modifies Derby to do so.


Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java
    db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
    db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.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?view=diff&rev=555096&r1=555095&r2=555096
==============================================================================
--- 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 Tue
Jul 10 15:06:02 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/trunk/java/engine/org/apache/derby/loc/messages.xml
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml?view=diff&rev=555096&r1=555095&r2=555096
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml Tue Jul 10 15:06:02
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/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java?view=diff&rev=555096&r1=555095&r2=555096
==============================================================================
--- db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
(original)
+++ db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
Tue Jul 10 15:06:02 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/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?view=diff&rev=555096&r1=555095&r2=555096
==============================================================================
--- 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
Tue Jul 10 15:06:02 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/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?view=diff&rev=555096&r1=555095&r2=555096
==============================================================================
--- 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
Tue Jul 10 15:06:02 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