db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@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
Date Wed, 26 Sep 2007 19:37:50 GMT

    [ 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.


Mime
View raw message