[jira] Commented: (DERBY-2706) fix like clauses with trailing %

["Knut Anders Hatlen (JIRA)" <ji...@apache.org>]

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

Knut Anders Hatlen commented on DERBY-2706:
-------------------------------------------

> Results in collated databases from like clauses with trailing % seem to be matching upper and lower of specified character.

I'll just add that they also ignore preceding spaces and hyphenation characters. Could this have something to do with the dynamic like optimization? If I remember correctly, "WHERE X LIKE 'a%'" will be translated into "x >= 'a' AND x < 'a\uFFFF'". Since the Unicode collation algorithm says that certain characters (like spaces and hyphens) should be ignored when comparing strings, I think this optimization might not work on databases with territory based collation.

> fix like clauses with trailing % 
> ---------------------------------
>
>                 Key: DERBY-2706
>                 URL: https://issues.apache.org/jira/browse/DERBY-2706
>             Project: Derby
>          Issue Type: Sub-task
>          Components: SQL
>    Affects Versions: 10.3.0.0
>            Reporter: Mike Matrigali
>         Attachments: derby2706.sql
>
>
> Results in collated databases from like clauses with trailing % seem to be matching upper and lower of specified character.
> For instance:
> ij(CONNECTION1)> connect 'jdbc:derby:nodb;create=true;territory=no_NO;collation=
> TERRITORY_BASED';
> WARNING 01J01: Database 'nodb' not created, connection made to existing database
>  instead.
> ij(CONNECTION2)> drop table t;
> 0 rows inserted/updated/deleted
> ij(CONNECTION2)> create table t (x varchar(20));
> 0 rows inserted/updated/deleted
> ij(CONNECTION2)> insert into t values 'Waagan', 'Wσhan', 'Wanvik', 'Wσgan', 'eks
> trabetaling', 'ekstraarbeid', 'ekstra¡arbeid', '¡a', 'a', '¡b', 'b', '-a', '-b',
>  ' a', ' b', 'A', 'B', 'C';
> ij(CONNECTION2)> select * from t where x like 'a%';
> X
> --------------------
> ¡a
> a
> -a
>  a
> A
> 5 rows selected
> ij(CONNECTION2)> select * from t where x like 'b%';
> X
> --------------------
> ¡b
> b
> -b
>  b
> B
> 5 rows selected
> ij(CONNECTION2)> select * from t where x like 'c%';
> X
> --------------------
> C

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