Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (herse.apache.org: local policy)
Date: Tue, 03 Apr 2007 09:04:59 -0700
From: Rick Hillegas <Richard.Hillegas@Sun.COM>
Subject: Re: Feedback on wiki page
 http://wiki.apache.org/db-derby/BuiltInLanguageBasedOrderingDERBY-1478
In-reply-to: <d9619e4a0704022305l66bfc330n7c9377cbf3da2fa7@mail.gmail.com>
Sender: Richard.Hillegas@Sun.COM
To: derby-dev@db.apache.org
Message-id: <46127B2B.7080204@sun.com>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
References: <d9619e4a0704022305l66bfc330n7c9377cbf3da2fa7@mail.gmail.com>
User-Agent: Thunderbird 1.5.0.5 (X11/20060828)

Hi Mamta,

Thanks for describing this behavior on a tidy wiki page. Having all of 
this material collected in one place is great. I have a comment:

6)CHAR, VARCHAR functions do not look like they are defined in the SQL 
spec. But based on 5) above, the result character string type's 
collation can be considered same as the first argument's collation type 
if the first argument to CHAR/VARCHAR function is a character string 
type. If the first argument is not character string type, then the 
result character string of CHAR/VARCHAR will have the same collation as 
current schema's character set. The collation derivation will be implicit.

I think the behavior would be easier to understand if it were uniform, 
that is, if the CHAR and VARCHAR operators always returned strings which 
had the collation of the current schema. I suspect you will find that 
this is easier to implement. I also think that this is the intention of 
the SQL Standard. Here is my reasoning:

It seems to me that there is a default (implementation-defined) 
character set and collation for the whole database. That default can be 
overridden at the session, schema, and client-module levels. That is, 
once you know what database, session, schema, and client-module you are 
in, you know the default character set and collation for string 
datatypes mentioned by your SQL statements. This default can be 
explicitly overridden with a CAST or COLLATE clause. There are also 
explicit exceptions to this behavior for certain operators (e.g., TRIM, 
UPPER, LOWER, SUBSTR). The default character set and collation apply 
unless the SQL Standard explicitly defines an exception or your 
statement explicitly overrides the default. The default character set 
and collation apply to the return types of the CHAR and VARCHAR 
operators because the SQL Standard does not carve out an explicit 
exception for these operators.

Is there some problem that would be solved by adopting the non-uniform 
behavior proposed on the wiki page?

Thanks,
-Rick

Mamta Satoor wrote:
> Hi,
>  
> I have created a wiki page for DERBY-1478 : Add built in language 
> based ordering and like processing to Derby
>  
> The wiki page is located at 
> http://wiki.apache.org/db-derby/BuiltInLanguageBasedOrderingDERBY-1478 and 
> it includes the current design proposal along with line items. If 
> anyone has any comments, please let me know.
>  
> thanks,
> Mamta