db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2706) fix like clauses with trailing %
Date Sun, 27 May 2007 19:19:16 GMT

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


Mime
View raw message