db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mi...@apache.org
Subject svn commit: r452259 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/FromBaseTable.java testing/org/apache/derbyTesting/functionTests/master/synonym.out testing/org/apache/derbyTesting/functionTests/tests/lang/synonym.sql
Date Mon, 02 Oct 2006 23:23:05 GMT
Author: mikem
Date: Mon Oct  2 16:23:04 2006
New Revision: 452259

URL: http://svn.apache.org/viewvc?view=rev&rev=452259
Log:
DERBY-1894
contributed by Yip Ng
patch: derby1894-trunk-diff02.txt

The fix is in FromBaseTable's getFromTableByName() method, where it is using 
the resolved synonym table name to do the binding for ORDER BY column. 
Patch includes additional tests.


Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
    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/synonym.sql

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java?view=diff&rev=452259&r1=452258&r2=452259
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java Mon
Oct  2 16:23:04 2006
@@ -2260,7 +2260,7 @@
 			if (derivedRCL != null)
 			{
 				 resultColumns.propagateDCLInfo(derivedRCL, 
-											    tableName.getFullTableName());
+											    origTableName.getFullTableName());
 			}
 
 			/* Assign the tableNumber */
@@ -2287,7 +2287,8 @@
 	protected FromTable getFromTableByName(String name, String schemaName, boolean exactMatch)
 		throws StandardException
 	{
-		String ourSchemaName = tableName.getSchemaName();
+		// ourSchemaName can be null if correlation name is specified.
+		String ourSchemaName = getOrigTableName().getSchemaName();
 		String fullName = (schemaName != null) ? (schemaName + '.' + name) : name;
 
 		/* If an exact string match is required then:
@@ -2334,10 +2335,14 @@
 		}
 
 		// Schema name only on column
+		// e.g.:  select w1.i from t1 w1 order by test2.w1.i;  (incorrect)
 		if (schemaName != null && ourSchemaName == null)
 		{
-			// Compare column's schema name with table descriptor's
-			if (! schemaName.equals(tableDescriptor.getSchemaDescriptor().getSchemaName()))
+			// Compare column's schema name with table descriptor's if it is
+			// not a synonym since a synonym can be declared in a different
+			// schema.
+			if (tableName.equals(origTableName) && 
+					! schemaName.equals(tableDescriptor.getSchemaDescriptor().getSchemaName()))
 			{
 				return null;
 			}
@@ -2349,7 +2354,7 @@
 			}
 
 			// Make sure exposed name is not a correlation name
-			if (! getExposedName().equals(tableName.getTableName()))
+			if (! getExposedName().equals(getOrigTableName().getTableName()))
 			{
 				return null;
 			}
@@ -2360,7 +2365,7 @@
 		/* Schema name only specified on table. Compare full exposed name
 		 * against table's schema name || "." || column's table name.
 		 */
-		if (! getExposedName().equals(tableName.getSchemaName() + "." + name))
+		if (! getExposedName().equals(getOrigTableName().getSchemaName() + "." + name))
 		{
 			return null;
 		}
@@ -2372,7 +2377,9 @@
 	/**
 	  *	Bind the table descriptor for this table.
 	  *
-	  *
+	  * If the tableName is a synonym, it will be resolved here.
+	  * The original table name is retained in origTableName.
+	  * 
 	  * @exception StandardException		Thrown on error
 	  */
 	private	TableDescriptor	bindTableDescriptor()
@@ -2382,11 +2389,7 @@
 		SchemaDescriptor sd = getSchemaDescriptor(schemaName);
 
 		tableDescriptor = getTableDescriptor(tableName.getTableName(), sd);
-		if (tableDescriptor != null)
-		{
-			this.tableDescriptor = tableDescriptor;
-		}
-		else
+		if (tableDescriptor == null)
 		{
 			// Check if the reference is for a synonym.
 			TableName synonymTab = resolveTableToSynonym(tableName);

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?view=diff&rev=452259&r1=452258&r2=452259
==============================================================================
--- 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
Mon Oct  2 16:23:04 2006
@@ -443,6 +443,33 @@
 ID                  
 --------------------
 1                   
+ij> -- DERBY-1894 
+-- ORDER BY clause with column qualifed by a synonym name where it is declared in 
+-- a different schema than the underlying table.
+select t1.id from t1 order by id;
+ID                  
+--------------------
+1                   
+ij> select t1.id from t1 order by t1.id;
+ID                  
+--------------------
+1                   
+ij> select t1.id as c1 from t1 order by c1;
+C1                  
+--------------------
+1                   
+ij> select t1.id from t1 where t1.id > 0 order by t1.id;
+ID                  
+--------------------
+1                   
+ij> select t1.id from t1 where t1.id > 0 group by t1.id;
+ID                  
+--------------------
+1                   
+ij> select t1.id from t1 where t1.id > 0 group by t1.id having t1.id > 0 order by
t1.id;
+ID                  
+--------------------
+1                   
 ij> select test2.t1.id from t1;
 ID                  
 --------------------
@@ -451,7 +478,104 @@
 ID                  
 --------------------
 1                   
+ij> select test2.t1.id from test2.t1 where t1.id > 0;
+ID                  
+--------------------
+1                   
+ij> select test2.t1.id from test2.t1 where t1.id > 0 order by t1.id;
+ID                  
+--------------------
+1                   
+ij> select test2.t1.id from test2.t1 order by id;
+ID                  
+--------------------
+1                   
+ij> select test2.t1.id from test2.t1 order by t1.id;
+ID                  
+--------------------
+1                   
+ij> select test2.t1.id from test2.t1 where t1.id > 0 order by test2.t1.id;
+ID                  
+--------------------
+1                   
+ij> select test2.t1.id from test2.t1 where t1.id > 0 group by test2.t1.id;
+ID                  
+--------------------
+1                   
+ij> select test2.t1.id from test2.t1 where t1.id > 0 group by test2.t1.id having test2.t1.id
> 0 order by test2.t1.id;
+ID                  
+--------------------
+1                   
+ij> select w1.id from t1 w1 order by id;
+ID                  
+--------------------
+1                   
+ij> select w1.id from t1 w1 order by w1.id;
+ID                  
+--------------------
+1                   
+ij> select t1.id as idcolumn1, t1.id as idcolumn2 from t1 order by idcolumn1, idcolumn2;
+IDCOLUMN1           |IDCOLUMN2           
+-----------------------------------------
+1                   |1                   
+ij> select t1.id as idcolumn1, t1.id as idcolumn2 from t1 order by t1.idcolumn1, t1.idcolumn2;
+IDCOLUMN1           |IDCOLUMN2           
+-----------------------------------------
+1                   |1                   
+ij> select t1.id from (select t1.id from t1) t1 order by t1.id;
+ID                  
+--------------------
+1                   
+ij> select t1.id from (select t1.id from t1 a, t1 b where a.id=b.id) t1 order by t1.id;
+ERROR 42X04: Column 'T1.ID' 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.ID' is not a column in the target table.
+ij> create table t2 (id bigint not null, name varchar(20));
+0 rows inserted/updated/deleted
+ij> create synonym s1 for test2.t1;
+0 rows inserted/updated/deleted
+ij> create synonym s2 for test2.t2;
+0 rows inserted/updated/deleted
+ij> insert into s2 values (1, 'John');
+1 row inserted/updated/deleted
+ij> insert into s2 values (2, 'Yip');
+1 row inserted/updated/deleted
+ij> insert into s2 values (3, 'Jane');
+1 row inserted/updated/deleted
+ij> select s1.id, s2.name from s1, s2 where s1.id=s2.id order by s1.id, s2.name;
+ID                  |NAME                
+-----------------------------------------
+1                   |John                
+ij> select s2.name from s2 where s2.id in (select s1.id from s1) order by s2.id;
+NAME                
+--------------------
+John                
+ij> select s2.name from s2 where exists (select s1.id from s1) order by s2.id;
+NAME                
+--------------------
+John                
+Yip                 
+Jane                
+ij> select s2.name from s2 where exists (select s1.id from s1 where s1.id=s2.id) order
by s2.id;
+NAME                
+--------------------
+John                
+ij> -- should fail
+select w1.id from t1 w1 order by test2.w1.id;
+ERROR 42X10: 'TEST2.W1' is not an exposed table name in the scope in which it appears.
+ij> select w1.id from t1 w1 order by test1.w1.id;
+ERROR 42X10: 'TEST1.W1' is not an exposed table name in the scope in which it appears.
+ij> select t1.id as idcolumn1, t1.id as idcolumn2 from t1 group by idcolumn1, idcolumn2
order by idcolumn1, idcolumn2;
+ERROR 42X04: Column '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
'IDCOLUMN1' is not a column in the target table.
+ij> select t1.id as idcolumn1, t1.id as idcolumn2 from t1 group by t1.idcolumn1, t1.idcolumn2
order by t1.idcolumn1, t1.idcolumn2;
+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 as c1 from t1 where c1 > 0 order by c1;
+ERROR 42X04: Column 'C1' 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
'C1' is not a column in the target table.
+ij> drop synonym s1;
+0 rows inserted/updated/deleted
+ij> drop synonym s2;
+0 rows inserted/updated/deleted
 ij> drop synonym t1;
+0 rows inserted/updated/deleted
+ij> drop table test2.t2;
 0 rows inserted/updated/deleted
 ij> drop table test1.t1;
 0 rows inserted/updated/deleted

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/synonym.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/synonym.sql?view=diff&rev=452259&r1=452258&r2=452259
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/synonym.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/synonym.sql
Mon Oct  2 16:23:04 2006
@@ -271,9 +271,53 @@
 select id from test2.t1;
 select t1.id from t1;
 select t1.id from test2.t1;
+-- DERBY-1894 
+-- ORDER BY clause with column qualifed by a synonym name where it is declared in 
+-- a different schema than the underlying table.
+select t1.id from t1 order by id;
+select t1.id from t1 order by t1.id;
+select t1.id as c1 from t1 order by c1;
+select t1.id from t1 where t1.id > 0 order by t1.id;
+select t1.id from t1 where t1.id > 0 group by t1.id;
+select t1.id from t1 where t1.id > 0 group by t1.id having t1.id > 0 order by t1.id;
 select test2.t1.id from t1;
 select test2.t1.id from test2.t1;
+select test2.t1.id from test2.t1 where t1.id > 0;
+select test2.t1.id from test2.t1 where t1.id > 0 order by t1.id;
+select test2.t1.id from test2.t1 order by id;
+select test2.t1.id from test2.t1 order by t1.id;
+select test2.t1.id from test2.t1 where t1.id > 0 order by test2.t1.id;
+select test2.t1.id from test2.t1 where t1.id > 0 group by test2.t1.id;
+select test2.t1.id from test2.t1 where t1.id > 0 group by test2.t1.id having test2.t1.id
> 0 order by test2.t1.id;
+select w1.id from t1 w1 order by id;
+select w1.id from t1 w1 order by w1.id;
+select t1.id as idcolumn1, t1.id as idcolumn2 from t1 order by idcolumn1, idcolumn2;
+select t1.id as idcolumn1, t1.id as idcolumn2 from t1 order by t1.idcolumn1, t1.idcolumn2;
+select t1.id from (select t1.id from t1) t1 order by t1.id;
+select t1.id from (select t1.id from t1 a, t1 b where a.id=b.id) t1 order by t1.id;
+
+create table t2 (id bigint not null, name varchar(20));
+create synonym s1 for test2.t1;
+create synonym s2 for test2.t2;
+insert into s2 values (1, 'John');
+insert into s2 values (2, 'Yip');
+insert into s2 values (3, 'Jane');
+select s1.id, s2.name from s1, s2 where s1.id=s2.id order by s1.id, s2.name;
+select s2.name from s2 where s2.id in (select s1.id from s1) order by s2.id;
+select s2.name from s2 where exists (select s1.id from s1) order by s2.id;
+select s2.name from s2 where exists (select s1.id from s1 where s1.id=s2.id) order by s2.id;
+
+-- should fail
+select w1.id from t1 w1 order by test2.w1.id;
+select w1.id from t1 w1 order by test1.w1.id;
+select t1.id as idcolumn1, t1.id as idcolumn2 from t1 group by idcolumn1, idcolumn2 order
by idcolumn1, idcolumn2;
+select t1.id as idcolumn1, t1.id as idcolumn2 from t1 group by t1.idcolumn1, t1.idcolumn2
order by t1.idcolumn1, t1.idcolumn2;
+select t1.id as c1 from t1 where c1 > 0 order by c1;
+
+drop synonym s1;
+drop synonym s2;
 drop synonym t1;
+drop table test2.t2;
 drop table test1.t1;
 
 set schema app;



Mime
View raw message