db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r636608 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Thu, 13 Mar 2008 03:01:17 GMT
Author: bpendleton
Date: Wed Mar 12 20:01:14 2008
New Revision: 636608

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

This is a follow-on patch for DERBY-2351, which addresses a number of
problems involving column aliasing that occurred with the initial
DERBY-2351 patch. After the initial DERBY-2351 patch, queries such as
the following one failed:

   select distinct c1 as a1 from t order by c1

This patch modifies the ORDER BY column matching algorithm to
match column references using multiple rules, depending on
whether or not the ORDER BY reference is qualified or not:
 - unqualified references are matched first against the alias name,
   then against the underlying column name from the source table
 - qualified references are matched only against the underlying
   specified table

A number of additional test cases are added as part of this patch to verify
that the above query, and other similar queries, now work as intended.

This patch rejects at least one query known to have worked before:

    select t1.id as idc1, t1.id as idc2 from t1 order by t1.idc1, t1.idc2; 

After discussion with Derby developers, this query was felt to
be invalid, as there is no such column "idc1" in table "t1". The query
should *not* qualify the ORDER BY column references with table "t1":

    select t1.id as idc1, t1.id as idc2 from t1 order by idc1, idc2; 


Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java
    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/master/synonym.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/ResultColumn.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java?rev=636608&r1=636607&r2=636608&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java Wed
Mar 12 20:01:14 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/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?rev=636608&r1=636607&r2=636608&view=diff
==============================================================================
--- 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
Wed Mar 12 20:01:14 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/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?rev=636608&r1=636607&r2=636608&view=diff
==============================================================================
--- 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
Wed Mar 12 20:01:14 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/trunk/java/testing/org/apache/derbyTesting/functionTests/master/synonym.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/synonym.out?rev=636608&r1=636607&r2=636608&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/synonym.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/synonym.out
Wed Mar 12 20:01:14 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/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?rev=636608&r1=636607&r2=636608&view=diff
==============================================================================
--- 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
Wed Mar 12 20:01:14 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