db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject svn commit: r448966 - in /db/derby/code/branches/10.2/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Fri, 22 Sep 2006 15:39:41 GMT
Author: rhillegas
Date: Fri Sep 22 08:39:40 2006
New Revision: 448966

URL: http://svn.apache.org/viewvc?view=rev&rev=448966
Log:
DERBY-1725: Merge following patch from the trunk to the 10.2 branch: DERBY-147 (447877).

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

Modified: db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java?view=diff&rev=448966&r1=448965&r2=448966
==============================================================================
--- db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
(original)
+++ db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
Fri Sep 22 08:39:40 2006
@@ -110,6 +110,25 @@
 
 	int			orderBySelect = 0; // the number of result columns pulled up
                                	   // from ORDERBY list
+    /*
+     * A comment on 'orderBySelect'. When we encounter a SELECT .. ORDER BY
+     * statement, the columns (or expressions) in the ORDER BY clause may
+     * or may not have been explicitly mentioned in the SELECT column list.
+     * If the columns were NOT explicitly mentioned in the SELECT column
+     * list, then the parsing of the ORDER BY clause implicitly generates
+     * them into the result column list, because we'll need to have those
+     * columns present at execution time in order to sort by them. Those
+     * generated columns are added to the *end* of the ResultColumnList, and
+     * we keep track of the *number* of those columns in 'orderBySelect',
+     * so we can tell whether we are looking at a generated column by seeing
+     * whether its position in the ResultColumnList is in the last
+     * 'orderBySelect' number of columns. If the SELECT .. ORDER BY
+     * statement uses the "*" token to select all the columns from a table,
+     * then during ORDER BY parsing we redundantly generate the columns
+     * mentioned in the ORDER BY clause into the ResultColumnlist, but then
+     * later in getOrderByColumn we determine that these are duplicates and
+     * we take them back out again.
+     */
 
 	/*
 	** Is this ResultColumnList for a FromBaseTable for an index
@@ -397,7 +416,7 @@
 
 	/**
 	 * For order by, get a ResultColumn that matches the specified 
-	 * columnName and ensure that there is only one match.
+	 * columnName.
 	 *
 	 * @param columnName	The ResultColumn to get from the list
 	 * @param tableName	The table name on the OrderByColumn, if any
@@ -405,7 +424,7 @@
 	 *						exposed name of tableName, if tableName != null.
 	 *
 	 * @return	the column that matches that name.
-	 * @exception StandardException thrown on duplicate
+	 * @exception StandardException thrown on ambiguity
 	 */
 	public ResultColumn getOrderByColumn(String columnName, TableName tableName, int tableNumber)
 		throws StandardException
@@ -436,7 +455,21 @@
 			}
 
 			/* We finally got past the qualifiers, now see if the column
-			 * names are equal.
+			 * names are equal. If they are, then we appear to have found
+			* our order by column. If we find our order by column multiple
+			* times, make sure that they are truly duplicates, otherwise
+			* we have an ambiguous situation. For example, the query
+			*   SELECT b+c AS a, d+e AS a FROM t ORDER BY a
+			* is ambiguous because we don't know which "a" is meant. But
+			*   SELECT t.a, t.* FROM t ORDER BY a
+			* is not ambiguous, even though column "a" is selected twice.
+			* If we find our ORDER BY column at the end of the
+			* SELECT column list, in the last 'orderBySelect' number
+			* of columns, then this column was not explicitly mentioned
+			* by the user in their SELECT column list, but was implicitly 
+			* added by the parsing of the ORDER BY clause, and it
+			* should be removed from the ResultColumnList and returned
+			* to the caller.
 			 */
 			if (columnName.equals( resultColumn.getName()) )
 			{
@@ -444,11 +477,11 @@
 				{
 					retVal = resultColumn;
 				}
-				else if (index < size - orderBySelect)
+				else if (! retVal.isEquivalent(resultColumn))
 				{
 					throw StandardException.newException(SQLState.LANG_DUPLICATE_COLUMN_FOR_ORDER_BY, columnName);
 				}
-				else
+				else if (index >= size - orderBySelect)
 				{// remove the column due to pullup of orderby item
 					removeElement(resultColumn);
 					decOrderBySelect();
@@ -462,13 +495,13 @@
 
 	/**
 	 * For order by, get a ResultColumn that matches the specified 
-	 * columnName and ensure that there is only one match before the bind process.
+	 * columnName.
 	 *
 	 * @param columnName	The ResultColumn to get from the list
 	 * @param tableName	The table name on the OrderByColumn, if any
 	 *
 	 * @return	the column that matches that name.
-	 * @exception StandardException thrown on duplicate
+	 * @exception StandardException thrown on ambiguity
 	 */
 	public ResultColumn getOrderByColumn(String columnName, TableName tableName)
 		throws StandardException
@@ -505,11 +538,11 @@
 				{
 					retVal = resultColumn;
 				}
-				else if (index < size - orderBySelect)
+				else if (! retVal.isEquivalent(resultColumn))
 				{
 					throw StandardException.newException(SQLState.LANG_DUPLICATE_COLUMN_FOR_ORDER_BY, columnName);
 				}
-				else
+				else if (index >= size - orderBySelect)
 				{// remove the column due to pullup of orderby item
 					removeElement(resultColumn);
 					decOrderBySelect();

Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out?view=diff&rev=448966&r1=448965&r2=448966
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
(original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
Fri Sep 22 08:39:40 2006
@@ -1314,4 +1314,110 @@
 0 rows inserted/updated/deleted
 ij> drop table tb;
 0 rows inserted/updated/deleted
+ij> -- some investigation of the handling of non-unique columns in the result set
+-- related to DERBY-147. The idea with this tests is that it should be
+-- acceptable to mention a column in the SELECT statement multiple times and
+-- then order by it, so long as the multiple columns truly are equivalent.
+-- There are a few cases where there truly is an ambiguity, and in those
+-- cases we reject the ORDER BY clause.
+create table derby147 (a int, b int, c int, d int);
+0 rows inserted/updated/deleted
+ij> insert into derby147 values (1, 2, 3, 4);
+1 row inserted/updated/deleted
+ij> insert into derby147 values (6, 6, 6, 6);
+1 row inserted/updated/deleted
+ij> select t.* from derby147 t;
+A          |B          |C          |D          
+-----------------------------------------------
+1          |2          |3          |4          
+6          |6          |6          |6          
+ij> select t.a,t.b,t.* from derby147 t order by b;
+A          |B          |A          |B          |C          |D          
+-----------------------------------------------------------------------
+1          |2          |1          |2          |3          |4          
+6          |6          |6          |6          |6          |6          
+ij> select t.a,t.b,t.b,t.c from derby147 t;
+A          |B          |B          |C          
+-----------------------------------------------
+1          |2          |2          |3          
+6          |6          |6          |6          
+ij> select t.a,t.b,t.b,t.c from derby147 t order by t.b;
+A          |B          |B          |C          
+-----------------------------------------------
+1          |2          |2          |3          
+6          |6          |6          |6          
+ij> -- This one truly is ambiguous, because the two columns named "e" are
+-- NOT equivalent. So it should fail:
+select a+b as e, c+d as e from derby147 order by e;
+ERROR 42X79: Column name 'E' appears more than once in the result of the query expression.
+ij> create table derby147_a (a int, b int, c int, d int);
+0 rows inserted/updated/deleted
+ij> insert into derby147_a values (1,2,3,4), (40, 30, 20, 10), (1,50,3,50);
+3 rows inserted/updated/deleted
+ij> create table derby147_b (a int, b int);
+0 rows inserted/updated/deleted
+ij> insert into derby147_b values (4, 4), (10, 10), (2, 50);
+3 rows inserted/updated/deleted
+ij> -- The columns named "a" are NOT equivalent.
+select t1.a,t2.a from derby147_a t1, derby147_b t2 where t1.d=t2.b order by a;
+ERROR 42X79: Column name 'A' appears more than once in the result of the query expression.
+ij> select t1.a,t2.a from derby147_a t1, derby147_b t2 where t1.d=t2.b order by t2.a;
+A          |A          
+-----------------------
+1          |2          
+1          |4          
+40         |10         
+ij> select a,a,b,c,d,a from derby147_a order by a;
+A          |A          |B          |C          |D          |A          
+-----------------------------------------------------------------------
+1          |1          |50         |3          |50         |1          
+1          |1          |2          |3          |4          |1          
+40         |40         |30         |20         |10         |40         
+ij> select c+d as a, t1.a, t1.b+t1.c as a from derby147_a t1 order by 3, 2 desc;
+A          |A          |A          
+-----------------------------------
+7          |1          |5          
+30         |40         |50         
+53         |1          |53         
+ij> -- The columns named "a" are NOT equivalent.
+select c+d as a, t1.a, t1.b+t1.c as a from derby147_a t1 order by a, a desc;
+ERROR 42X79: Column name 'A' appears more than once in the result of the query expression.
+ij> select a, c+d as a from derby147_a;
+A          |A          
+-----------------------
+1          |7          
+40         |30         
+1          |53         
+ij> -- The columns named "a" are NOT equivalent.
+select a, c+d as a from derby147_a order by a;
+ERROR 42X79: Column name 'A' appears more than once in the result of the query expression.
+ij> select c+d as a, t1.a, t1.b+t1.c as b_plus_c from derby147_a t1 order by c+d;
+A          |A          |B_PLUS_C   
+-----------------------------------
+7          |1          |5          
+30         |40         |50         
+53         |1          |53         
+ij> -- The columns named "a" are NOT equivalent.
+select c+d as a, t1.a, t1.b+t1.c as a from derby147_a t1 order by d-4, a;
+ERROR 42X79: Column name 'A' appears more than once in the result of the query expression.
+ij> select * from derby147_a order by c+2 desc, b asc, a desc;
+A          |B          |C          |D          
+-----------------------------------------------
+40         |30         |20         |10         
+1          |2          |3          |4          
+1          |50         |3          |50         
+ij> -- If you introduce a coorelation name for a table, use the correlation
+-- name in the order by:
+select a, b from derby147_a t order by derby147_a.b;
+ERROR 42X04: Column 'DERBY147_A.B' is either not in any table in the FROM list or appears
within a join specification and is outside the scope of the join specification or appears
in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement
then 'DERBY147_A.B' is not a column in the target table.
+ij> -- pull expressions from the ORDER BY clause into the implicit area of
+-- the SELECT column list, and ensure they don't end up in the result. This
+-- statement causes a SanityManager assertion, filed as DERBY-1861
+-- select * from derby147_b order by b, a+2;
+-- Verify that correlation names match the table names properly:
+select t.a, sum(t.a) from derby147_a t group by t.a order by t.a;
+A          |2          
+-----------------------
+1          |2          
+40         |40         
 ij> 

Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql?view=diff&rev=448966&r1=448965&r2=448966
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
(original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
Fri Sep 22 08:39:40 2006
@@ -455,3 +455,49 @@
 
 drop table ta;
 drop table tb;
+
+-- some investigation of the handling of non-unique columns in the result set
+-- related to DERBY-147. The idea with this tests is that it should be
+-- acceptable to mention a column in the SELECT statement multiple times and
+-- then order by it, so long as the multiple columns truly are equivalent.
+-- There are a few cases where there truly is an ambiguity, and in those
+-- cases we reject the ORDER BY clause.
+
+create table derby147 (a int, b int, c int, d int);
+insert into derby147 values (1, 2, 3, 4);
+insert into derby147 values (6, 6, 6, 6);
+select t.* from derby147 t;
+select t.a,t.b,t.* from derby147 t order by b;
+select t.a,t.b,t.b,t.c from derby147 t;
+select t.a,t.b,t.b,t.c from derby147 t order by t.b;
+-- This one truly is ambiguous, because the two columns named "e" are
+-- NOT equivalent. So it should fail:
+select a+b as e, c+d as e from derby147 order by e;
+
+create table derby147_a (a int, b int, c int, d int);
+insert into derby147_a values (1,2,3,4), (40, 30, 20, 10), (1,50,3,50);
+create table derby147_b (a int, b int);
+insert into derby147_b values (4, 4), (10, 10), (2, 50);
+-- The columns named "a" are NOT equivalent.
+select t1.a,t2.a from derby147_a t1, derby147_b t2 where t1.d=t2.b order by a;
+select t1.a,t2.a from derby147_a t1, derby147_b t2 where t1.d=t2.b order by t2.a;
+select a,a,b,c,d,a from derby147_a order by a;
+select c+d as a, t1.a, t1.b+t1.c as a from derby147_a t1 order by 3, 2 desc;
+-- The columns named "a" are NOT equivalent.
+select c+d as a, t1.a, t1.b+t1.c as a from derby147_a t1 order by a, a desc;
+select a, c+d as a from derby147_a;
+-- The columns named "a" are NOT equivalent.
+select a, c+d as a from derby147_a order by a;
+select c+d as a, t1.a, t1.b+t1.c as b_plus_c from derby147_a t1 order by c+d;
+-- The columns named "a" are NOT equivalent.
+select c+d as a, t1.a, t1.b+t1.c as a from derby147_a t1 order by d-4, a;
+select * from derby147_a order by c+2 desc, b asc, a desc;
+-- If you introduce a coorelation name for a table, use the correlation
+-- name in the order by:
+select a, b from derby147_a t order by derby147_a.b;
+-- pull expressions from the ORDER BY clause into the implicit area of
+-- the SELECT column list, and ensure they don't end up in the result. This
+-- statement causes a SanityManager assertion, filed as DERBY-1861
+-- select * from derby147_b order by b, a+2;
+-- Verify that correlation names match the table names properly:
+select t.a, sum(t.a) from derby147_a t group by t.a order by t.a;



Mime
View raw message