Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 62187 invoked from network); 3 Apr 2007 16:25:29 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 3 Apr 2007 16:25:29 -0000 Received: (qmail 36958 invoked by uid 500); 3 Apr 2007 16:25:36 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 36910 invoked by uid 500); 3 Apr 2007 16:25:35 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 36875 invoked by uid 99); 3 Apr 2007 16:25:35 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 03 Apr 2007 09:25:35 -0700 X-ASF-Spam-Status: No, hits=2.0 required=10.0 tests=HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (herse.apache.org: domain of msatoor@gmail.com designates 209.85.132.246 as permitted sender) Received: from [209.85.132.246] (HELO an-out-0708.google.com) (209.85.132.246) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 03 Apr 2007 09:25:26 -0700 Received: by an-out-0708.google.com with SMTP id c25so1885886ana for ; Tue, 03 Apr 2007 09:25:05 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=hTW+DFpPFBo4iTAySjNHzpbFDqZhpmEbC+VCNaglqk3XCxsYvhfdlhiiSs9bvCgmc37V9k88d9dRd/NGqePQs0/Zbif0I0TRz9oFdS+EDcry+668lBqKWfq7CMa1QN+ultop88c/tC1gEdLpyTkpJlMTT/9P6q3krLNL6N/wZMk= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=NUK9044VQyAGXqXdIE1IVJRzTS4hFsVbFQ3PfVsM9p7x7gzV6k9+5O0TXyq4FrGdiyfPHPkAQ2FtHni81lPYqaPrjQBtp0+hzQfE+xzmV5JmJGKtJowyTg2kOKUsJOTpaiKsjDhwusRDatIBXDJarQPHNd0V62wTjDrKBYf58GQ= Received: by 10.100.10.20 with SMTP id 20mr4556073anj.1175617504903; Tue, 03 Apr 2007 09:25:04 -0700 (PDT) Received: by 10.100.136.4 with HTTP; Tue, 3 Apr 2007 09:25:04 -0700 (PDT) Message-ID: Date: Tue, 3 Apr 2007 09:25:04 -0700 From: "Mamta Satoor" To: derby-dev@db.apache.org Subject: Re: Feedback on wiki page http://wiki.apache.org/db-derby/BuiltInLanguageBasedOrderingDERBY-1478 In-Reply-To: <46127B2B.7080204@sun.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_12993_11777023.1175617504855" References: <46127B2B.7080204@sun.com> X-Virus-Checked: Checked by ClamAV on apache.org ------=_Part_12993_11777023.1175617504855 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Rick, Dan had also brought up this point. Dan's comment from * http://www.nabble.com/Collation-feature-discussion-p9526316.html* "This approach means that CHAR(varchar_col, 20) behaves differently to CAST (varchar_col AS CHAR(20)). Not sure if that's good or bad, but they might be implemented today using the same code path. " I think what you are proposing will be easier to implement and easier to explain to the users and fits in the SQL spec model. I wasn't trying to solve any paritcular scenario but was just trying to make CHAR work like TRIM when a character string type was it's first parameter. If no objections by the end of the day, then I will go ahead and change the wiki page for CHAR/VARCHAR functions to have the same collation as current schema's character set no matter what kind of parameter is passed to it. On 4/3/07, Rick Hillegas wrote: > > 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-1478and > > it includes the current design proposal along with line items. If > > anyone has any comments, please let me know. > > > > thanks, > > Mamta > > ------=_Part_12993_11777023.1175617504855 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Rick, Dan had also brought up this point. Dan's comment from http://www.nabble.com/Collation-feature-discussion-p9526316.html
"This approach means that CHAR(varchar_col, 20) behaves differently to CAST (varchar_col AS CHAR(20)). Not sure if that's good or bad, but they might be implemented today using the same code path. "
 
I think what you are proposing will be easier to implement and easier to explain to the users and fits in the SQL spec model. I wasn't trying to solve any paritcular scenario but was just trying to make CHAR work like TRIM when a character string type was it's first parameter. If no objections by the end of the day, then I will go ahead and change the wiki page for CHAR/VARCHAR functions to have the same collation as current schema's character set no matter what kind of parameter is passed to it.

 
On 4/3/07, Rick Hillegas <Richard.Hillegas@sun.com > wrote:
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


------=_Part_12993_11777023.1175617504855--