You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by bp...@apache.org on 2006/09/19 17:16:01 UTC
svn commit: r447877 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
testing/org/apache/derbyTesting/functionTests/master/orderby.out
testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
Author: bpendleton
Date: Tue Sep 19 08:16:00 2006
New Revision: 447877
URL: http://svn.apache.org/viewvc?view=rev&rev=447877
Log:
DERBY-147: ERROR 42x79 if specify same column twice and use ORDER BY
This patch contributed by Bernd Ruehlicke (apache@priwall.org)
This patch changes the getOrderByColumn method in ResultColumnList so that
it uses ResultColumn.isEquivalent to determine whether the column specified
in the ORDER BY clause is ambiguous or not. It is ok to select the ORDER BY
column multiple times, so long as all the occurrences are equivalent. If there
is an ambiguity, the statement is rejected and the user must reword it to
clarify which column is to be used for ordering the results.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.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/ResultColumnList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java?view=diff&rev=447877&r1=447876&r2=447877
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java Tue Sep 19 08:16:00 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/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=447877&r1=447876&r2=447877
==============================================================================
--- 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 Sep 19 08:16:00 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/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=447877&r1=447876&r2=447877
==============================================================================
--- 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 Sep 19 08:16:00 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;