db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r637526 - in /db/derby/code/branches/10.4/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Sun, 16 Mar 2008 03:50:32 GMT
Author: bpendleton
Date: Sat Mar 15 20:50:31 2008
New Revision: 637526

URL: http://svn.apache.org/viewvc?rev=637526&view=rev
Log:
DERBY-2351: ORDER BY with expression with distinct in SELECT list.

Merged by svn merge -r 636607:636608 ../trunk/


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

Modified: db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java?rev=637526&r1=637525&r2=637526&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java
(original)
+++ db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java
Sat Mar 15 20:50:31 2008
@@ -208,6 +208,35 @@
 	}
 
 	/**
+	 * Return TRUE if this result column matches the provided column name.
+	 *
+	 * This function is used by ORDER BY column resolution. For the
+	 * ORDER BY clause, Derby will prefer to match on the column's
+	 * alias (exposedName), but will also successfully match on the
+	 * underlying column name. Thus the following statements are
+	 * treated equally:
+	 *  select name from person order by name;
+	 *  select name as person_name from person order by name;
+	 *  select name as person_name from person order by person_name;
+	 * See DERBY-2351 for more discussion.
+	 */
+	boolean columnNameMatches(String columnName)
+	{
+		return columnName.equals(exposedName) ||
+			columnName.equals(name) ||
+			columnName.equals(getSourceColumnName());
+	}
+	/**
+	 * Returns the underlying source column name, if this ResultColumn
+	 * is a simple direct reference to a table column, or NULL otherwise.
+	 */
+	String getSourceColumnName()
+	{
+		if (expression instanceof ColumnReference)
+			return ((ColumnReference)expression).getColumnName();
+		return null;
+	}
+	/**
 	 * The following methods implement the ResultColumnDescriptor
 	 * interface.  See the Language Module Interface for details.
 	 */

Modified: db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java?rev=637526&r1=637525&r2=637526&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
(original)
+++ db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
Sat Mar 15 20:50:31 2008
@@ -441,6 +441,7 @@
 			 *	o  The RC is not qualified, but its expression is a ColumnReference
 			 *	   from the same table (as determined by the tableNumbers).
 			 */
+                        boolean columnNameMatches;
 			if (tableName != null)
 			{
                 ValueNode rcExpr = resultColumn.getExpression();
@@ -450,7 +451,13 @@
                 ColumnReference cr = (ColumnReference) rcExpr;
                 if( (! tableName.equals( cr.getTableNameNode())) && tableNumber !=
cr.getTableNumber())
                     continue;
+				columnNameMatches =
+					columnName.equals( resultColumn.getSourceColumnName() );
 			}
+			else
+				columnNameMatches =
+					resultColumn.columnNameMatches(columnName);
+
 
 			/* We finally got past the qualifiers, now see if the column
 			 * names are equal. If they are, then we appear to have found
@@ -469,7 +476,7 @@
 			* should be removed from the ResultColumnList and returned
 			* to the caller.
 			 */
-			if (columnName.equals( resultColumn.getName()) )
+			if (columnNameMatches)
 			{
 				if (retVal == null)
 				{
@@ -558,6 +565,7 @@
 			// exposedName will not be null and "*" will not have an expression
 			// or tablename.
 			// We may be checking on "ORDER BY T.A" against "SELECT T.B, T.A".
+                        boolean columnNameMatches;
 			if (tableName != null)
 			{
 				ValueNode rcExpr = resultColumn.getExpression();
@@ -568,12 +576,17 @@
 				ColumnReference cr = (ColumnReference) rcExpr;
                 if( ! tableName.equals( cr.getTableNameNode()))
                     continue;
+				columnNameMatches =
+					columnName.equals( resultColumn.getSourceColumnName() );
 			}
+			else
+				columnNameMatches =
+					resultColumn.columnNameMatches(columnName);
 
 			/* We finally got past the qualifiers, now see if the column
 			 * names are equal.
 			 */
-			if (columnName.equals( resultColumn.getName()) )
+			if (columnNameMatches)
 			{
 				if (retVal == null)
 				{

Modified: db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out?rev=637526&r1=637525&r2=637526&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
(original)
+++ db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
Sat Mar 15 20:50:31 2008
@@ -1759,8 +1759,141 @@
 ----------
 Mary      
 John      
+ij> -- Some test cases involving column aliasing:
+select distinct name as first_name from person order by name;
+FIRST_NAME
+----------
+John      
+Mary      
+ij> select distinct name as first_name from person order by first_name;
+FIRST_NAME
+----------
+John      
+Mary      
+ij> select distinct person.name from person order by name;
+NAME      
+----------
+John      
+Mary      
+ij> select distinct name as first_name from person order by person.name;
+FIRST_NAME
+----------
+John      
+Mary      
+ij> select distinct name as age from person order by age;
+AGE       
+----------
+John      
+Mary      
+ij> select distinct name as age from person order by person.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> select distinct name, name from person order by name;
+NAME      |NAME      
+---------------------
+John      |John      
+Mary      |Mary      
+ij> select distinct name, name as first_name from person order by name;
+NAME      |FIRST_NAME
+---------------------
+John      |John      
+Mary      |Mary      
+ij> select distinct name, name as first_name from person order by 2;
+NAME      |FIRST_NAME
+---------------------
+John      |John      
+Mary      |Mary      
+ij> -- Some test cases combining column aliasing with table aliasing:
+select distinct name nm from person p order by name;
+NM        
+----------
+John      
+Mary      
+ij> select distinct name nm from person p order by nm;
+NM        
+----------
+John      
+Mary      
+ij> select distinct name nm from person p order by p.name;
+NM        
+----------
+John      
+Mary      
+ij> select distinct name nm from person p order by person.name;
+ERROR 42X04: Column 'PERSON.NAME' 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 'PERSON.NAME' is not a column in the target table.
+ij> select distinct name nm from person p order by person.nm;
+ERROR 42X04: Column 'PERSON.NM' 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
'PERSON.NM' is not a column in the target table.
+ij> select distinct name nm from person p order by p.nm;
+ERROR 42X04: Column 'P.NM' 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
'P.NM' is not a column in the target table.
+ij> create table pets (name varchar(10), age int);
+0 rows inserted/updated/deleted
+ij> insert into pets values ('Rover', 3), ('Fido', 5), ('Buster', 1);
+3 rows inserted/updated/deleted
+ij> select distinct name from person union select distinct name from pets order by name;
+NAME      
+----------
+Buster    
+Fido      
+John      
+Mary      
+Rover     
+ij> select distinct name from person, pets order by name;
+ERROR 42X03: Column name 'NAME' is in more than one table in the FROM list.
+ij> select distinct person.name as person_name, pets.name as pet_name from person,pets
order by name;
+ERROR 42X79: Column name 'NAME' appears more than once in the result of the query expression.
+ij> select distinct person.name as person_name, pets.name from person,pets order by name;
+ERROR 42X79: Column name 'NAME' appears more than once in the result of the query expression.
+ij> select distinct person.name as person_name, pets.name from person,pets order by person.name;
+PERSON_NA&|NAME      
+---------------------
+John      |Buster    
+John      |Fido      
+John      |Rover     
+Mary      |Buster    
+Mary      |Fido      
+Mary      |Rover     
+ij> select distinct person.name as name, pets.name as pet_name from person,pets order
by name;
+ERROR 42X79: Column name 'NAME' appears more than once in the result of the query expression.
+ij> select distinct person.name as name, pets.name as pet_name from person,pets order
by pets.name;
+NAME      |PET_NAME  
+---------------------
+John      |Buster    
+Mary      |Buster    
+John      |Fido      
+Mary      |Fido      
+John      |Rover     
+Mary      |Rover     
+ij> -- Include some of the error cases from above without the DISTINCT
+-- specification to investigate how that affects the behavior:
+select name as age from person order by person.age;
+AGE       
+----------
+John      
+Mary      
+John      
+ij> select name from person, pets order by name;
+ERROR 42X03: Column name 'NAME' is in more than one table in the FROM list.
+ij> select person.name as person_name, pets.name as pet_name from person,pets order by
name;
+ERROR 42X79: Column name 'NAME' appears more than once in the result of the query expression.
+ij> select person.name as person_name, pets.name from person,pets order by person.name;
+PERSON_NA&|NAME      
+---------------------
+John      |Buster    
+John      |Fido      
+John      |Rover     
+John      |Buster    
+John      |Fido      
+John      |Rover     
+Mary      |Buster    
+Mary      |Fido      
+Mary      |Rover     
+ij> select person.name as person_name, pets.name from person,pets order by name;
+ERROR 42X79: Column name 'NAME' appears more than once in the result of the query expression.
+ij> select person.name as name, pets.name as pet_name from person,pets order by name;
+ERROR 42X79: Column name 'NAME' appears more than once in the result of the query expression.
 ij> drop table person;
 0 rows inserted/updated/deleted
+ij> drop table pets;
+0 rows inserted/updated/deleted
 ij> create table d2887_types(
    id             int,
    c1_smallint    smallint,
@@ -2049,4 +2182,4 @@
 ERROR 42X77: Column position '2' is out of range for the query expression.
 ij> drop table d3303;
 0 rows inserted/updated/deleted
-ij> 
+ij> 
\ No newline at end of file

Modified: db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/master/synonym.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/master/synonym.out?rev=637526&r1=637525&r2=637526&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/master/synonym.out
(original)
+++ db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/master/synonym.out
Sat Mar 15 20:50:31 2008
@@ -523,9 +523,7 @@
 -----------------------------------------
 1                   |1                   
 ij> select t1.id as idcolumn1, t1.id as idcolumn2 from t1 order by t1.idcolumn1, t1.idcolumn2;
-IDCOLUMN1           |IDCOLUMN2           
------------------------------------------
-1                   |1                   
+ERROR 42X04: Column 'T1.IDCOLUMN1' 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 'T1.IDCOLUMN1' is not a column in the target table.
 ij> select t1.id from (select t1.id from t1) t1 order by t1.id;
 ID                  
 --------------------

Modified: db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql?rev=637526&r1=637525&r2=637526&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
(original)
+++ db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
Sat Mar 15 20:50:31 2008
@@ -680,7 +680,42 @@
 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;
+-- 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;
+select distinct person.name from person order by name;
+select distinct name as first_name from person order by person.name;
+select distinct name as age from person order by age;
+select distinct name as age from person order by person.age;
+select distinct name, name from person order by name;
+select distinct name, name as first_name from person order by name;
+select distinct name, name as first_name from person order by 2;
+-- Some test cases combining column aliasing with table aliasing:
+select distinct name nm from person p order by name;
+select distinct name nm from person p order by nm;
+select distinct name nm from person p order by p.name;
+select distinct name nm from person p order by person.name;
+select distinct name nm from person p order by person.nm;
+select distinct name nm from person p order by p.nm;
+create table pets (name varchar(10), age int);
+insert into pets values ('Rover', 3), ('Fido', 5), ('Buster', 1);
+select distinct name from person union select distinct name from pets order by name;
+select distinct name from person, pets order by name;
+select distinct person.name as person_name, pets.name as pet_name from person,pets order
by name;
+select distinct person.name as person_name, pets.name from person,pets order by name;
+select distinct person.name as person_name, pets.name from person,pets order by person.name;
+select distinct person.name as name, pets.name as pet_name from person,pets order by name;
+select distinct person.name as name, pets.name as pet_name from person,pets order by pets.name;
+-- Include some of the error cases from above without the DISTINCT
+-- specification to investigate how that affects the behavior:
+select name as age from person order by person.age;
+select name from person, pets order by name;
+select person.name as person_name, pets.name as pet_name from person,pets order by name;
+select person.name as person_name, pets.name from person,pets order by person.name;
+select person.name as person_name, pets.name from person,pets order by name;
+select person.name as name, pets.name as pet_name from person,pets order by name;
 drop table person;
+drop table pets;
 
 
 create table d2887_types(



Mime
View raw message