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