Exposed name matching has bugs when the column name is qualified with a schema name.

• Key: DERBY-18
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Minor
• Assignee: Shreyas Kaushik
• Reporter: Tulika Agrawal
• Component/s: SQL
• Affects Version/s: 10.0.2.0
• Fix Version/s: 10.1.1.0

Description

Reporting for Kathey Marsden.

create table t1(c1 int); -- goes into app
select sys.t1.c1 from t1; -- should fail
select sys.b.c1 from t1 b; -- should fail
select * from t1, app.t1; -- fails, should succeed
select t1.c1 from t1, app.t1; -- fails, should succeed

- According to SQL92, the <table
name> in a <table reference> exposes its name when it is not
qualified (See 6.3 <table reference>, Syntax Rule 1). Also,
an unqualified <table name> is equivalent to one qualified
with the current default schema name (See 5.4 Names and
identifiers, Syntax Rule 8). So, in the above queries,

select * from t1, app.t1 -- is the same as select * from
app.t1, app.t1, and this is not SQL92 --- you have duplicate
exposed <table name>s in the same scope (see 6.3 <table
reference>, Syntax Rule 3). Derby can support it, but it's an
extension.

select t1.c1 from t1, app.t1 -- is the same as select app.t1.c1
from app.t1, app.t1, and again supporting this would be an
extension to SQL92.

Note that if you say
select * from t1, t2 t1 -- this is also a duplication, as the
exposed <table name> of the first table is app.t1 and the
exposed <correlation name> of the second table is t1. these
are different names, but 6.3 <table reference> Syntax Rule 4
rules out this case explicitly -- a <correlation name>
cannot be the same as the unqualified part of any exposed
<table name>.

One possibility is to go to a 2 pass method of column resolution:
1st pass looks for an exact match on the qualifier (app.t1
matches app.t1 but not t1, ...)
if no match, then do 2nd pass where it looks for a match on
table id.
(Look at OrderByColumn.bindOrderByColumn()

Comments

[Shreyas Kaushik]

set current schema app;

create table t1(int c1 varchar(10));

create schema s1;
create table s1.t1(id1 int, d2 varchar(10));

select * from t1, app.t1; ---> This fails, should succeed.

Internally table names are handled correctly but the above problem occurs because of the equals() method in Tablename.java.

Here the follwoing piece of code

----------------------------------------------------------------------------------
else if ((schemaName == null) ||
(otherTableName.getSchemaName() == null))
{
return tableName.equals(otherTableName.getTableName());
}
----------------------------------------------------------------------------------

causes the above error to occur. In this if either of the schema name is null, only the table names are compared and hence the error for the above statement.

Should the equals method implementation be changed to take of this?
  or
Should we set the tables that have null schemas to the current schema or something along these lines?

[Daniel John Debrunner]

Applied patch from Shreyas.

Sending java/engine/org/apache/derby/impl/sql/compile/CurrentOfNode.java
Sending java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
Sending java/testing/org/apache/derbyTesting/functionTests/master/dml162.out
Sending java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
Sending java/testing/org/apache/derbyTesting/functionTests/master/select.out
Sending java/testing/org/apache/derbyTesting/functionTests/tests/lang/select.sql
Sending java/testing/org/apache/derbyTesting/functionTests/tests/nist/dml162.sql
Transmitting file data .......
Committed revision 157591.

[Shreyas Kaushik]

Fixed , as the earlier comment says

[Shreyas Kaushik]

Patch submitted