[
https://issues.apache.org/jira/browse/DERBY-2706?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mike Matrigali updated DERBY-2706:
----------------------------------
Attachment: derby2706.sql
attaching a script which shows a number of like clauses against 2 collation and
one default database. The
(a%, b%, c%) cases seem to be matching upper and lower in the collated db's,
but not in default db.
Here is output from my machine:
c:/p4/m3/classes;c:/p4/m3/tools/java/jdbctests.zip;c:/p4/m3/tools/java/jakarta-oro-2.0.8.jar;c:/p4/m3/tools/java/jgl3.1.0.jar;c:/p4/m3/tools/java/jndi/providerutil.jar;c:/p4/m3/tools/java/jndi/ldap.jar;c:/p4/m3/tools/java/jndi/jndi.jar;c:/p4/m3/tools/java/jnl1.0F.zip;c:/p4/m3/tools/java/junit.jar;c:/p4/m3/tools/java/servlet-2_2b.jar;c:/p4/m3/tools/java/xslt4j-2_5_0/xercesImpl.jar;c:/p4/m3/tools/java/xslt4j-2_5_0/xml-apis.jar;c:/p4/m3/tools/java/xslt4j-2_5_0/xalan.jar
ij version 10.3
CONNECTION0* - jdbc:derby:wombat
* = current connection
ij> 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(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',
'ekstrabetaling', 'ekstraarbeid', 'ekstraarbeid', 'a', 'a', 'b', 'b', '-a',
'-b', ' a', ' b', 'A', 'B', 'C';
18 rows inserted/updated/deleted
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
1 row selected
ij(CONNECTION2)> select * from t where x like '%a%';
X
--------------------
Waagan
Wåhan
Wanvik
Wågan
ekstrabetaling
ekstraarbeid
a
a
-a
a
10 rows selected
ij(CONNECTION2)> select * from t where x like '%b%';
X
--------------------
ekstrabetaling
ekstraarbeid
ekstraarbeid
b
b
-b
b
7 rows selected
ij(CONNECTION2)> select * from t where x like '%c%';
X
--------------------
0 rows selected
ij(CONNECTION2)> select * from t where x like '%a';
X
--------------------
a
a
-a
a
4 rows selected
ij(CONNECTION2)> select * from t where x like '%b';
X
--------------------
b
b
-b
b
4 rows selected
ij(CONNECTION2)> select * from t where x like '%c';
X
--------------------
0 rows selected
ij(CONNECTION2)> select * from t where x like '%';
X
--------------------
Waagan
Wåhan
Wanvik
Wågan
ekstrabetaling
ekstraarbeid
ekstraarbeid
a
a
b
b
-a
-b
a
b
A
B
C
18 rows selected
ij(CONNECTION2)> connect 'jdbc:derby:defaultdb;create=true;';
WARNING 01J01: Database 'defaultdb' not created, connection made to existing
database instead.
ij(CONNECTION3)> drop table t;
0 rows inserted/updated/deleted
ij(CONNECTION3)> create table t (x varchar(20));
0 rows inserted/updated/deleted
ij(CONNECTION3)> insert into t values 'Waagan', 'Wåhan', 'Wanvik', 'Wågan',
'ekstrabetaling', 'ekstraarbeid', 'ekstraarbeid', 'a', 'a', 'b', 'b', '-a',
'-b', ' a', ' b', 'A', 'B', 'C';
18 rows inserted/updated/deleted
ij(CONNECTION3)> select * from t where x like 'a%';
X
--------------------
a
1 row selected
ij(CONNECTION3)> select * from t where x like 'b%';
X
--------------------
b
1 row selected
ij(CONNECTION3)> select * from t where x like 'c%';
X
--------------------
0 rows selected
ij(CONNECTION3)> select * from t where x like '%a%';
X
--------------------
Waagan
Wåhan
Wanvik
Wågan
ekstrabetaling
ekstraarbeid
ekstraarbeid
a
a
-a
a
11 rows selected
ij(CONNECTION3)> select * from t where x like '%b%';
X
--------------------
ekstrabetaling
ekstraarbeid
ekstraarbeid
b
b
-b
b
7 rows selected
ij(CONNECTION3)> select * from t where x like '%c%';
X
--------------------
0 rows selected
ij(CONNECTION3)> select * from t where x like '%a';
X
--------------------
a
a
-a
a
4 rows selected
ij(CONNECTION3)> select * from t where x like '%b';
X
--------------------
b
b
-b
b
4 rows selected
ij(CONNECTION3)> select * from t where x like '%c';
X
--------------------
0 rows selected
ij(CONNECTION3)> select * from t where x like '%';
X
--------------------
Waagan
Wåhan
Wanvik
Wågan
ekstrabetaling
ekstraarbeid
ekstraarbeid
a
a
b
b
-a
-b
a
b
A
B
C
18 rows selected
ij(CONNECTION3)> connect
'jdbc:derby:endb;create=true;territory=en;collation=TERRITORY_BASED';
WARNING 01J01: Database 'endb' not created, connection made to existing
database instead.
ij(CONNECTION4)> drop table t;
0 rows inserted/updated/deleted
ij(CONNECTION4)> create table t (x varchar(20));
0 rows inserted/updated/deleted
ij(CONNECTION4)> insert into t values 'Waagan', 'Wåhan', 'Wanvik', 'Wågan',
'ekstrabetaling', 'ekstraarbeid', 'ekstraarbeid', 'a', 'a', 'b', 'b', '-a',
'-b', ' a', ' b', 'A', 'B', 'C';
18 rows inserted/updated/deleted
ij(CONNECTION4)> select * from t where x like 'a%';
X
--------------------
a
a
-a
a
A
5 rows selected
ij(CONNECTION4)> select * from t where x like 'b%';
X
--------------------
b
b
-b
b
B
5 rows selected
ij(CONNECTION4)> select * from t where x like 'c%';
X
--------------------
C
1 row selected
ij(CONNECTION4)> select * from t where x like '%a%';
X
--------------------
Waagan
Wåhan
Wanvik
Wågan
ekstrabetaling
ekstraarbeid
ekstraarbeid
a
a
-a
a
11 rows selected
ij(CONNECTION4)> select * from t where x like '%b%';
X
--------------------
ekstrabetaling
ekstraarbeid
ekstraarbeid
b
b
-b
b
7 rows selected
ij(CONNECTION4)> select * from t where x like '%c%';
X
--------------------
0 rows selected
ij(CONNECTION4)> select * from t where x like '%a';
X
--------------------
a
a
-a
a
4 rows selected
ij(CONNECTION4)> select * from t where x like '%b';
X
--------------------
b
b
-b
b
4 rows selected
ij(CONNECTION4)> select * from t where x like '%c';
X
--------------------
0 rows selected
ij(CONNECTION4)> select * from t where x like '%';
X
--------------------
Waagan
Wåhan
Wanvik
Wågan
ekstrabetaling
ekstraarbeid
ekstraarbeid
a
a
b
b
-a
-b
a
b
A
B
C
18 rows selected
ij(CONNECTION4)>
> fix like clauses with trailing %
> ---------------------------------
>
> Key: DERBY-2706
> URL: https://issues.apache.org/jira/browse/DERBY-2706
> Project: Derby
> Issue Type: Sub-task
> 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.
