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:23:05 UTC
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
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;