You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Mike Matrigali (JIRA)" <ji...@apache.org> on 2007/05/27 20:07:16 UTC
[jira] Created: (DERBY-2706) fix like clauses with trailing %
fix like clauses with trailing %
---------------------------------
Key: DERBY-2706
URL: https://issues.apache.org/jira/browse/DERBY-2706
Project: Derby
Issue Type: Sub-task
Reporter: Mike Matrigali
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2706) fix like clauses with trailing %
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Updated: (DERBY-2706) fix like clauses with trailing %
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2706?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-2706:
----------------------------------
Component/s: SQL
> 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.
[jira] Resolved: (DERBY-2706) fix like clauses with trailing %
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2706?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali resolved DERBY-2706.
-----------------------------------
Resolution: Fixed
Fix Version/s: 10.3.0.0
Assignee: Mike Matrigali
This was indeed fixed by disabling dynamic like optimization - checkin 542553. If you still see any problems let me know.
> 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
> Assignee: Mike Matrigali
> Fix For: 10.3.0.0
>
> 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.
[jira] Updated: (DERBY-2706) fix like clauses with trailing %
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2706?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-2706:
----------------------------------
thanks knut, I am not sure if that is the problem - but we are definitely not doing the right thing for dynamic like. I am going to open another task, and leave this one open. Hopefully it will be fixed by fixing dynamic like.
> 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.
[jira] Updated: (DERBY-2706) fix like clauses with trailing %
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2706?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-2706:
----------------------------------
Description:
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
Affects Version/s: 10.3.0.0
> 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
>
> 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.
[jira] Commented: (DERBY-2706) fix like clauses with trailing %
Posted by "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.