db-derby-dev mailing list archives

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

     [ 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', 'ekstra­arbeid', '­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      
ekstra­arbeid       
­a                  
a                   
-a                  
 a                  

10 rows selected
ij(CONNECTION2)> select * from t where x like '%b%';
X                   
--------------------
ekstrabetaling      
ekstraarbeid        
ekstra­arbeid       
­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        
ekstra­arbeid       
­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', 'ekstra­arbeid', '­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        
ekstra­arbeid       
­a                  
a                   
-a                  
 a                  

11 rows selected
ij(CONNECTION3)> select * from t where x like '%b%';
X                   
--------------------
ekstrabetaling      
ekstraarbeid        
ekstra­arbeid       
­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        
ekstra­arbeid       
­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', 'ekstra­arbeid', '­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        
ekstra­arbeid       
­a                  
a                   
-a                  
 a                  

11 rows selected
ij(CONNECTION4)> select * from t where x like '%b%';
X                   
--------------------
ekstrabetaling      
ekstraarbeid        
ekstra­arbeid       
­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        
ekstra­arbeid       
­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.


Mime
View raw message