Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Message-ID: <1805048876.1096484672141.JavaMail.apache@nagoya>
Date: Wed, 29 Sep 2004 12:04:32 -0700 (PDT)
From: derby-dev@db.apache.org
To: derby-dev@db.apache.org
Subject: [jira] Created: (DERBY-18) Exposed name matching has bugs when the
 column name is qualified with a schema name.
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Message:

  A new issue has been created in JIRA.

---------------------------------------------------------------------
View the issue:
  http://issues.apache.org/jira/browse/DERBY-18

Here is an overview of the issue:
---------------------------------------------------------------------
        Key: DERBY-18
    Summary: Exposed name matching has bugs when the column name is qualified with a schema name.
       Type: Bug

     Status: Unassigned
   Priority: Minor

    Project: Derby
 Components: 
             SQL
   Versions:
             10.0.2.0

   Assignee: 
   Reporter: Tulika Agrawal

    Created: Wed, 29 Sep 2004 12:04 PM
    Updated: Wed, 29 Sep 2004 12:04 PM

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()


---------------------------------------------------------------------
JIRA INFORMATION:
This message is automatically generated by JIRA.

If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa

If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira