Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Received-SPF: neutral (hermes.apache.org: local policy)
Message-ID: <41AF49C9.8090705@Mutagen.Net>
Date: Thu, 02 Dec 2004 08:58:49 -0800
From: Jack Klebanoff <klebanof@Mutagen.Net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
 rv:1.7.1) Gecko/20040707
MIME-Version: 1.0
To: Derby Development <derby-dev@db.apache.org>
Subject: Re: [jira] Commented: (DERBY-13) Quoted names with embedded period
 mishandled in from list
References: <1282959885.1101983902639.JavaMail.apache@nagoya>
In-Reply-To: <1282959885.1101983902639.JavaMail.apache@nagoya>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit

Jan Hlavat? (JIRA) wrote:

>     [ http://nagoya.apache.org/jira/browse/DERBY-13?page=comments#action_56090 ]
>     
>Jan Hlavat� commented on DERBY-13:
>----------------------------------
>
>I think you need to store table name as a pair <schma,name>, not as a single String.
>As delimited identifiers can contain arbitrary characters, you dont have a reliable delimiter character to separate, and any escaping mechanism would be costly.
>
>  
>
>>Quoted names with embedded period mishandled in from list
>>---------------------------------------------------------
>>
>>         Key: DERBY-13
>>         URL: http://nagoya.apache.org/jira/browse/DERBY-13
>>     Project: Derby
>>        Type: Bug
>>  Components: SQL
>>    Versions: 10.0.2.0
>>    Reporter: Ramandeep Kaur
>>    Priority: Minor
>> Attachments: Derby-13.patch
>>
>>Opening this bug on behalf of Satheesh Bandaram
>>---------------------------------------------------------
>>The compiler mishandles quoted names with embedded periods when 
>>checking uniqueness of table names in the from list of a 
>>SELECT. Consider the following SQL:
>>  create table "S1.T1" (id int not null primary key, d1 int);
>>  create schema s1;
>>  create table s1.t1 (id int not null primary key, d2 int);
>>  select * from s1.t1, "S1.T1" where s1.t1.id = "S1.T1".id;
>>Derby throws an error on the final SELECT statement:
>>"ERROR 42X09: The table or alias name 'S1.T1' is used more than 
>>once in the FROM list". However s1.t1 and "S1.T1" are different 
>>tables.
>>    
>>
>
>  
>
I agree with Jan. Representing table names as a (schema, table) pair is 
more straightforward, if we are to handle the corner cases correctly. It 
captures the fact that a table name is not a simple string and that a 
simple string comparison does not always correctly compute name equivalence.

Another consideration is the equivalence of delimited and non-delimited 
identifiers. For instance the undelimited identifier id, and the 
delimited identifier "ID" are supposed to be identical. The SQL standard 
says "A <regular identifier> and a <delimited identifier> are equivalent 
if the <identifier body> of the <regular identifier> (with every letter 
that is a lower-case letter replaced by the corresponding upper-case 
letter or letters) and the <delimited identifier body> of the <delimited 
identifier> (with all occurrences of <quote> replaced by <quote symbol> 
and all occurrences of <doublequote symbol> replaced by <double quote>), 
considered as the repetition of a <character string literal> that 
specifies a <character set specification> of SQL_IDENTIFIER and an 
implementationdefined collation that is sensitive to case, compare 
equally according to the comparison rules in Subclause 8.2, 
��<comparison predicate>��". (That is one long sentence).

If we store delimited identifiers with double quotes and undelimited 
identifiers without then a simple string comparison will miss this 
equivalence.

I think that it is important that we get this equivalence right. Some 
DML is generated programmatically from metadata and always uses 
delimited identifiers, even if the DDL did not.

Changing Derby to use a (schema, table) pair instead of a simple string 
for table names is a lot of work. A large number of files must be changed.

Jack