Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <3356456.1190835470582.JavaMail.jira@brutus>
Date: Wed, 26 Sep 2007 12:37:50 -0700 (PDT)
From: "Mamta A. Satoor (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-3080) With TERRITORY_BASED collation, when
 multiple characters map to a single collation element '_'  matches a single
 collation element rather than a single character
In-Reply-To: <3385875.1190131963651.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ https://issues.apache.org/jira/browse/DERBY-3080?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12530536 ] 

Mamta A. Satoor commented on DERBY-3080:
----------------------------------------

The collation of locale applies only for collation related methods and hence 'aa' in Norway is treated as single character when used in a LIKE operation with _. But the collation is not taken into consideration when the LENGTH function is used because it is not one of the collation related methods. 

I did a quick glance through the SQL spec and didn't find LENGTH function in there but they do have CHAR_LENGTH Section 6.27 <numeric value expression> General Rules 5B)"the result is the number of explicit or implicit <char length units>". It appears that <char length units> is what would be used to specify the length of the character string literal when that character string is say defined using CHAR(length). 

So in Norway, the character column (say c1) definition for 'aa' will be CHAR(2) and looking at SQL spec, CHAR_LENGTH for that character column will return 2. But when column c1 is used in a collation related operation, for instance, c1 LIKE '_', we will use the collation of the Norway locale and 'aa' will be a match to '_'. 

I hope this is useful.

> With TERRITORY_BASED collation, when multiple characters map to a single collation element '_'  matches a single collation element rather than a single character
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-3080
>                 URL: https://issues.apache.org/jira/browse/DERBY-3080
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.3.1.4, 10.4.0.0
>            Reporter: Kathey Marsden
>
> '_' matches a single collation element rather than a single character if multiple characters have a single collation element.  Below is an example with norwegian aa  This occurs also with the patch for DERBY-2967 which addresses the case of matching of a  single character having more than one collation element.
> ij> connect 'jdbc:derby:wombat;create=true;territory=no_NO;collation=TERRITORY_BASED';
> ij> drop table t;
> 0 rows inserted/updated/deleted
> ij> create table t (vc varchar(30));
> 0 rows inserted/updated/deleted
> ij> insert into t values('middleaavalue');
> 1 row inserted/updated/deleted
> -- The query below should match
> ij> select * from t where vc like 'middle__value';
> VC
> ------------------------------
> 0 rows selected
> -- The query below should not match.
> ij>  select * from t where vc like 'middle_value';
> VC
> ------------------------------
> middleaavalue

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.