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 mi...@apache.org on 2006/10/03 01:57:19 UTC
svn commit: r452269 - in /db/derby/code/branches/10.1/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
Author: mikem
Date: Mon Oct 2 16:57:18 2006
New Revision: 452269
URL: http://svn.apache.org/viewvc?view=rev&rev=452269
Log:
DERBY-1894
contributed by Yip Ng
merging from trunk to 10.1 branch.
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/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/synonym.out
db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/synonym.sql
Modified: db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java?view=diff&rev=452269&r1=452268&r2=452269
==============================================================================
--- db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java (original)
+++ db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java Mon Oct 2 16:57:18 2006
@@ -2248,7 +2248,7 @@
if (derivedRCL != null)
{
resultColumns.propagateDCLInfo(derivedRCL,
- tableName.getFullTableName());
+ origTableName.getFullTableName());
}
/* Assign the tableNumber */
@@ -2275,7 +2275,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:
@@ -2322,10 +2323,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;
}
@@ -2337,7 +2342,7 @@
}
// Make sure exposed name is not a correlation name
- if (! getExposedName().equals(tableName.getTableName()))
+ if (! getExposedName().equals(getOrigTableName().getTableName()))
{
return null;
}
@@ -2348,7 +2353,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;
}
@@ -2360,7 +2365,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()
@@ -2370,11 +2377,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/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/synonym.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/synonym.out?view=diff&rev=452269&r1=452268&r2=452269
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/synonym.out (original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/synonym.out Mon Oct 2 16:57:18 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/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/synonym.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/synonym.sql?view=diff&rev=452269&r1=452268&r2=452269
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/synonym.sql (original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/synonym.sql Mon Oct 2 16:57:18 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;