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 "Øystein Grøvlen (JIRA)" <de...@db.apache.org> on 2006/04/27 13:23:37 UTC
[jira] Created: (DERBY-1262) Like-predicates: % does not match tab
character
Like-predicates: % does not match tab character
-----------------------------------------------
Key: DERBY-1262
URL: http://issues.apache.org/jira/browse/DERBY-1262
Project: Derby
Type: Bug
Components: SQL
Versions: 10.0.2.0
Environment: Embedded, Solaris 10 x86
Reporter: Øystein Grøvlen
% in like predicates does not seem to match tab character. In the following example all whitespace in character literals is the tab character:
ij> create table t4(i integer primary key, j integer, c varchar(10));
0 rows inserted/updated/deleted
ij> insert into t4 values (1, 1, 'abc def');
1 row inserted/updated/deleted
ij> select * from t4 where c like 'abc%';
I |J |C
----------------------------------
0 rows selected
ij> select * from t4 where c like 'abc %';
I |J |C
----------------------------------
1 |1 |abc def
1 row selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
Re: [jira] Updated: (DERBY-1262) Like-predicates: % does not match tab
character
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
Håvard,
I think it would be useful if you filed an ICLA (See
http://wiki.apache.org/db-derby/DerbyContributorChecklist) if you
haven't already. It will be required the day you file a large patch (I
don't know the exact definition of "large" in this context), but the
Apache "bureaucratic mill" may be somewhat slow with the registering
of ICLAs.
>>>>>>>>>>>> Håvard Mork (JIRA) wrote (2006-06-01 10:55:30):
> [ http://issues.apache.org/jira/browse/DERBY-1262?page=all ]
>
> Håvard Mork updated DERBY-1262:
> -------------------------------
>
> Attachment: 1262_4.diff
>
> Attached new patch with corrections (thanks, Bernt)
> - using the more efficient stringbuffer.setLength for null-char padding
> - esc (0x1b) as control char in test case, to avoid potential future tab->space conversion.
>
>
> > Like-predicates: % does not match tab character
> > -----------------------------------------------
> >
> > Key: DERBY-1262
> > URL: http://issues.apache.org/jira/browse/DERBY-1262
> > Project: Derby
> > Type: Bug
>
> > Components: SQL
> > Versions: 10.0.2.0
> > Environment: Embedded, Solaris 10 x86
> > Reporter: Øystein Grøvlen
> > Attachments: 1262.diff, 1262_3.diff, 1262_4.diff
> >
> > % in like predicates does not seem to match tab character. In the following example all whitespace in character literals is the tab character:
> > ij> create table t4(i integer primary key, j integer, c varchar(10));
> > 0 rows inserted/updated/deleted
> > ij> insert into t4 values (1, 1, 'abc def');
> > 1 row inserted/updated/deleted
> > ij> select * from t4 where c like 'abc%';
> > I |J |C
> > ----------------------------------
> > 0 rows selected
> > ij> select * from t4 where c like 'abc %';
> > I |J |C
> > ----------------------------------
> > 1 |1 |abc def
> > 1 row selected
>
> --
> This message is automatically generated by JIRA.
> -
> If you think it was sent incorrectly contact one of the administrators:
> http://issues.apache.org/jira/secure/Administrators.jspa
> -
> For more information on JIRA, see:
> http://www.atlassian.com/software/jira
>
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
[jira] Commented: (DERBY-1262) Like-predicates: % does not match
tab character
Posted by "Knut Anders Hatlen (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1262?page=comments#action_12414440 ]
Knut Anders Hatlen commented on DERBY-1262:
-------------------------------------------
All tests ran successfully. Committed 1262_6.diff to trunk with revision 411174. I have started derbyall on 10.1 with this fix, and I intend to check it in on that branch too.
> Like-predicates: % does not match tab character
> -----------------------------------------------
>
> Key: DERBY-1262
> URL: http://issues.apache.org/jira/browse/DERBY-1262
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Environment: Embedded, Solaris 10 x86
> Reporter: Øystein Grøvlen
> Attachments: 1262.diff, 1262_3.diff, 1262_4.diff, 1262_5.diff, 1262_6.diff
>
> % in like predicates does not seem to match tab character. In the following example all whitespace in character literals is the tab character:
> ij> create table t4(i integer primary key, j integer, c varchar(10));
> 0 rows inserted/updated/deleted
> ij> insert into t4 values (1, 1, 'abc def');
> 1 row inserted/updated/deleted
> ij> select * from t4 where c like 'abc%';
> I |J |C
> ----------------------------------
> 0 rows selected
> ij> select * from t4 where c like 'abc %';
> I |J |C
> ----------------------------------
> 1 |1 |abc def
> 1 row selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Reopened: (DERBY-1262) Like-predicates: % does not match tab
character
Posted by "A B (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1262?page=all ]
A B reopened DERBY-1262:
------------------------
I think there's a *regression* after this patch was checked in. The following query returns 2 rows before svn revision 411174 and NO rows after. I admit I haven't looked closely enough to figure out which behavior is correct, but some more investigation is definitely required (at the very least, a release note is needed if the new behavior is correct).
This change went into the 10.1 codeline as well, and I have verified that it returned 2 rows with the snapshot 405564 but no rows with the latest 10.1 codeline.
So I'm reopening this issue. Note: it's currently marked as "Unassigned", so anyone who is inclined to pick it up should certainly feel free...
> Like-predicates: % does not match tab character
> -----------------------------------------------
>
> Key: DERBY-1262
> URL: http://issues.apache.org/jira/browse/DERBY-1262
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Environment: Embedded, Solaris 10 x86
> Reporter: Øystein Grøvlen
> Fix For: 10.2.0.0, 10.1.3.0, 10.1.2.5
> Attachments: 1262.diff, 1262_3.diff, 1262_4.diff, 1262_5.diff, 1262_6.diff
>
> % in like predicates does not seem to match tab character. In the following example all whitespace in character literals is the tab character:
> ij> create table t4(i integer primary key, j integer, c varchar(10));
> 0 rows inserted/updated/deleted
> ij> insert into t4 values (1, 1, 'abc def');
> 1 row inserted/updated/deleted
> ij> select * from t4 where c like 'abc%';
> I |J |C
> ----------------------------------
> 0 rows selected
> ij> select * from t4 where c like 'abc %';
> I |J |C
> ----------------------------------
> 1 |1 |abc def
> 1 row selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1262) Like-predicates: % does not match tab
character
Posted by "Håvard Mork (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1262?page=all ]
Håvard Mork updated DERBY-1262:
-------------------------------
Attachment: 1262.diff
Thanks Satheesh for your input. I believe you are right that correcting the lessthan/greaterthan values is the way to go. I made a tentative patch for this issue, which pads the greaterthan and lessthan values with null chars, up to the number of chars in the 'receiver' column (at the left side of the LIKE statement).
As always, I'm not confident this is the most efficient approach. A different solution than adding a new param for Like.greaterThanXX() would be to insert a method call node that adds the padding (for preparedstatement executions).
Patch passes derbylang.
> Like-predicates: % does not match tab character
> -----------------------------------------------
>
> Key: DERBY-1262
> URL: http://issues.apache.org/jira/browse/DERBY-1262
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Environment: Embedded, Solaris 10 x86
> Reporter: Øystein Grøvlen
> Attachments: 1262.diff
>
> % in like predicates does not seem to match tab character. In the following example all whitespace in character literals is the tab character:
> ij> create table t4(i integer primary key, j integer, c varchar(10));
> 0 rows inserted/updated/deleted
> ij> insert into t4 values (1, 1, 'abc def');
> 1 row inserted/updated/deleted
> ij> select * from t4 where c like 'abc%';
> I |J |C
> ----------------------------------
> 0 rows selected
> ij> select * from t4 where c like 'abc %';
> I |J |C
> ----------------------------------
> 1 |1 |abc def
> 1 row selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Resolved: (DERBY-1262) Like-predicates: % does not match tab
character
Posted by "A B (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1262?page=all ]
A B resolved DERBY-1262:
------------------------
Resolution: Fixed
Putting this back to "Resolved" after having filed DERBY-1386 for the regression. If anyone decides to back out this change in 10.1 (instead of trying to fix DERBY-1386), please reopen as appropriate. Thanks.
> Like-predicates: % does not match tab character
> -----------------------------------------------
>
> Key: DERBY-1262
> URL: http://issues.apache.org/jira/browse/DERBY-1262
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Environment: Embedded, Solaris 10 x86
> Reporter: Øystein Grøvlen
> Fix For: 10.2.0.0, 10.1.3.0, 10.1.2.5
> Attachments: 1262.diff, 1262_3.diff, 1262_4.diff, 1262_5.diff, 1262_6.diff
>
> % in like predicates does not seem to match tab character. In the following example all whitespace in character literals is the tab character:
> ij> create table t4(i integer primary key, j integer, c varchar(10));
> 0 rows inserted/updated/deleted
> ij> insert into t4 values (1, 1, 'abc def');
> 1 row inserted/updated/deleted
> ij> select * from t4 where c like 'abc%';
> I |J |C
> ----------------------------------
> 0 rows selected
> ij> select * from t4 where c like 'abc %';
> I |J |C
> ----------------------------------
> 1 |1 |abc def
> 1 row selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1262) Like-predicates: % does not match
tab character
Posted by "Håvard Mork (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1262?page=comments#action_12377762 ]
Håvard Mork commented on DERBY-1262:
------------------------------------
I believe this is a duplicate of DERBY-20.
This behaviour is due to the optimization of LikeEscapeOperatorNode. This node is converted to less-than and greater-than expressions ("abc" < column < "abd" ) that are sent to the scan controller through 'qualifier' objects. When these are evaluated with SQLChar.stringCompare, the shorter string is padded with spaces, and the comparison fail due to that the ordinal value of TAB is less than Space. The comparison is correct, but optimization probably not.
> Like-predicates: % does not match tab character
> -----------------------------------------------
>
> Key: DERBY-1262
> URL: http://issues.apache.org/jira/browse/DERBY-1262
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Environment: Embedded, Solaris 10 x86
> Reporter: Øystein Grøvlen
>
> % in like predicates does not seem to match tab character. In the following example all whitespace in character literals is the tab character:
> ij> create table t4(i integer primary key, j integer, c varchar(10));
> 0 rows inserted/updated/deleted
> ij> insert into t4 values (1, 1, 'abc def');
> 1 row inserted/updated/deleted
> ij> select * from t4 where c like 'abc%';
> I |J |C
> ----------------------------------
> 0 rows selected
> ij> select * from t4 where c like 'abc %';
> I |J |C
> ----------------------------------
> 1 |1 |abc def
> 1 row selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Assigned: (DERBY-1262) Like-predicates: % does not match tab
character
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1262?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen reassigned DERBY-1262:
-----------------------------------------
Assignee: Håvard Mork
Assigning the issue to Håvard since he was the one fixing it.
> Like-predicates: % does not match tab character
> -----------------------------------------------
>
> Key: DERBY-1262
> URL: https://issues.apache.org/jira/browse/DERBY-1262
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.0.2.0
> Environment: Embedded, Solaris 10 x86
> Reporter: Øystein Grøvlen
> Assigned To: Håvard Mork
> Fix For: 10.1.3.1, 10.2.1.6
>
> Attachments: 1262.diff, 1262_3.diff, 1262_4.diff, 1262_5.diff, 1262_6.diff
>
>
> % in like predicates does not seem to match tab character. In the following example all whitespace in character literals is the tab character:
> ij> create table t4(i integer primary key, j integer, c varchar(10));
> 0 rows inserted/updated/deleted
> ij> insert into t4 values (1, 1, 'abc def');
> 1 row inserted/updated/deleted
> ij> select * from t4 where c like 'abc%';
> I |J |C
> ----------------------------------
> 0 rows selected
> ij> select * from t4 where c like 'abc %';
> I |J |C
> ----------------------------------
> 1 |1 |abc def
> 1 row selected
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-1262) Like-predicates: % does not match
tab character
Posted by "Bernt M. Johnsen (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1262?page=comments#action_12414032 ]
Bernt M. Johnsen commented on DERBY-1262:
-----------------------------------------
Instead of the loop in padWithNulls, you could use StringBuffer.setLength(len) instead, e.g:
StringBuffer buf = new StringBuffer(len).append(string);
buf.setLength(len);
return buf.toString();
> Like-predicates: % does not match tab character
> -----------------------------------------------
>
> Key: DERBY-1262
> URL: http://issues.apache.org/jira/browse/DERBY-1262
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Environment: Embedded, Solaris 10 x86
> Reporter: Øystein Grøvlen
> Attachments: 1262.diff, 1262_3.diff
>
> % in like predicates does not seem to match tab character. In the following example all whitespace in character literals is the tab character:
> ij> create table t4(i integer primary key, j integer, c varchar(10));
> 0 rows inserted/updated/deleted
> ij> insert into t4 values (1, 1, 'abc def');
> 1 row inserted/updated/deleted
> ij> select * from t4 where c like 'abc%';
> I |J |C
> ----------------------------------
> 0 rows selected
> ij> select * from t4 where c like 'abc %';
> I |J |C
> ----------------------------------
> 1 |1 |abc def
> 1 row selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1262) Like-predicates: % does not match
tab character
Posted by "A B (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1262?page=comments#action_12415185 ]
A B commented on DERBY-1262:
----------------------------
Oops, forgot to include the query:
create table escTable (c1 char(10));
insert into escTable values ('%_\a');
insert into escTable values ('%_b');
insert into escTable values ('%c');
insert into escTable values ('d');
insert into escTable values ('%_\e');
select c1 from escTable where c1 like '\%\_\\%' ESCAPE '\';
> Like-predicates: % does not match tab character
> -----------------------------------------------
>
> Key: DERBY-1262
> URL: http://issues.apache.org/jira/browse/DERBY-1262
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Environment: Embedded, Solaris 10 x86
> Reporter: Øystein Grøvlen
> Fix For: 10.2.0.0, 10.1.3.0, 10.1.2.5
> Attachments: 1262.diff, 1262_3.diff, 1262_4.diff, 1262_5.diff, 1262_6.diff
>
> % in like predicates does not seem to match tab character. In the following example all whitespace in character literals is the tab character:
> ij> create table t4(i integer primary key, j integer, c varchar(10));
> 0 rows inserted/updated/deleted
> ij> insert into t4 values (1, 1, 'abc def');
> 1 row inserted/updated/deleted
> ij> select * from t4 where c like 'abc%';
> I |J |C
> ----------------------------------
> 0 rows selected
> ij> select * from t4 where c like 'abc %';
> I |J |C
> ----------------------------------
> 1 |1 |abc def
> 1 row selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1262) Like-predicates: % does not match
tab character
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1262?page=comments#action_12414239 ]
Daniel John Debrunner commented on DERBY-1262:
----------------------------------------------
Couple of minor comments on the patch (1262_5.diff)
In padWithNulls() can StandardException ever be thrown? If not the method should not declare that it throws it.
In padWithNulls() can the input ever be null? Seems all the callers have already checked for null.
> Like-predicates: % does not match tab character
> -----------------------------------------------
>
> Key: DERBY-1262
> URL: http://issues.apache.org/jira/browse/DERBY-1262
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Environment: Embedded, Solaris 10 x86
> Reporter: Øystein Grøvlen
> Attachments: 1262.diff, 1262_3.diff, 1262_4.diff, 1262_5.diff
>
> % in like predicates does not seem to match tab character. In the following example all whitespace in character literals is the tab character:
> ij> create table t4(i integer primary key, j integer, c varchar(10));
> 0 rows inserted/updated/deleted
> ij> insert into t4 values (1, 1, 'abc def');
> 1 row inserted/updated/deleted
> ij> select * from t4 where c like 'abc%';
> I |J |C
> ----------------------------------
> 0 rows selected
> ij> select * from t4 where c like 'abc %';
> I |J |C
> ----------------------------------
> 1 |1 |abc def
> 1 row selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1262) Like-predicates: % does not match tab
character
Posted by "Håvard Mork (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1262?page=all ]
Håvard Mork updated DERBY-1262:
-------------------------------
Attachment: 1262_6.diff
Removed unnecessary code. Thanks Daniel.
> Like-predicates: % does not match tab character
> -----------------------------------------------
>
> Key: DERBY-1262
> URL: http://issues.apache.org/jira/browse/DERBY-1262
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Environment: Embedded, Solaris 10 x86
> Reporter: Øystein Grøvlen
> Attachments: 1262.diff, 1262_3.diff, 1262_4.diff, 1262_5.diff, 1262_6.diff
>
> % in like predicates does not seem to match tab character. In the following example all whitespace in character literals is the tab character:
> ij> create table t4(i integer primary key, j integer, c varchar(10));
> 0 rows inserted/updated/deleted
> ij> insert into t4 values (1, 1, 'abc def');
> 1 row inserted/updated/deleted
> ij> select * from t4 where c like 'abc%';
> I |J |C
> ----------------------------------
> 0 rows selected
> ij> select * from t4 where c like 'abc %';
> I |J |C
> ----------------------------------
> 1 |1 |abc def
> 1 row selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1262) Like-predicates: % does not match
tab character
Posted by "Kathey Marsden (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1262?page=comments#action_12415186 ]
Kathey Marsden commented on DERBY-1262:
---------------------------------------
Army said:
|I think there's a *regression* after this patch was checked in. The following |query returns 2 rows before svn revision 411174 and NO rows after.
As I read it, the fix for this issue created a bug that is a wrong Results
regression that is in the 10.1 maintenance branch.
I think instead of reopening this bug there should be a new bug marked regression and we should not release 10.1.3 while it exists.
> Like-predicates: % does not match tab character
> -----------------------------------------------
>
> Key: DERBY-1262
> URL: http://issues.apache.org/jira/browse/DERBY-1262
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Environment: Embedded, Solaris 10 x86
> Reporter: Øystein Grøvlen
> Fix For: 10.2.0.0, 10.1.3.0, 10.1.2.5
> Attachments: 1262.diff, 1262_3.diff, 1262_4.diff, 1262_5.diff, 1262_6.diff
>
> % in like predicates does not seem to match tab character. In the following example all whitespace in character literals is the tab character:
> ij> create table t4(i integer primary key, j integer, c varchar(10));
> 0 rows inserted/updated/deleted
> ij> insert into t4 values (1, 1, 'abc def');
> 1 row inserted/updated/deleted
> ij> select * from t4 where c like 'abc%';
> I |J |C
> ----------------------------------
> 0 rows selected
> ij> select * from t4 where c like 'abc %';
> I |J |C
> ----------------------------------
> 1 |1 |abc def
> 1 row selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1262) Like-predicates: % does not match
tab character
Posted by "Øystein Grøvlen (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1262?page=comments#action_12413100 ]
Øystein Grøvlen commented on DERBY-1262:
----------------------------------------
Patch looks good, but I am not expert on this code. I would very much like this to be fixed in 10.1.3 so it would be nice if someone with more experience with this code could look at it.
One minor comment:
- Why are the old versions of greaterEqualStringFromParameter() and greaterEqualStringFromParameterWithEsc() kept? Do you need them anymore? Seems like they are only called from one location.
> Like-predicates: % does not match tab character
> -----------------------------------------------
>
> Key: DERBY-1262
> URL: http://issues.apache.org/jira/browse/DERBY-1262
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Environment: Embedded, Solaris 10 x86
> Reporter: Øystein Grøvlen
> Attachments: 1262.diff
>
> % in like predicates does not seem to match tab character. In the following example all whitespace in character literals is the tab character:
> ij> create table t4(i integer primary key, j integer, c varchar(10));
> 0 rows inserted/updated/deleted
> ij> insert into t4 values (1, 1, 'abc def');
> 1 row inserted/updated/deleted
> ij> select * from t4 where c like 'abc%';
> I |J |C
> ----------------------------------
> 0 rows selected
> ij> select * from t4 where c like 'abc %';
> I |J |C
> ----------------------------------
> 1 |1 |abc def
> 1 row selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1262) Like-predicates: % does not match
tab character
Posted by "Bernt M. Johnsen (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1262?page=comments#action_12414035 ]
Bernt M. Johnsen commented on DERBY-1262:
-----------------------------------------
Isn't it somewhat dnagerous to use tab-cahacters in strings in dynamicLikeOptimization.sql since it seems to me that Derby developers converts tabs to spaces and vice versa almost at will?
> Like-predicates: % does not match tab character
> -----------------------------------------------
>
> Key: DERBY-1262
> URL: http://issues.apache.org/jira/browse/DERBY-1262
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Environment: Embedded, Solaris 10 x86
> Reporter: Øystein Grøvlen
> Attachments: 1262.diff, 1262_3.diff
>
> % in like predicates does not seem to match tab character. In the following example all whitespace in character literals is the tab character:
> ij> create table t4(i integer primary key, j integer, c varchar(10));
> 0 rows inserted/updated/deleted
> ij> insert into t4 values (1, 1, 'abc def');
> 1 row inserted/updated/deleted
> ij> select * from t4 where c like 'abc%';
> I |J |C
> ----------------------------------
> 0 rows selected
> ij> select * from t4 where c like 'abc %';
> I |J |C
> ----------------------------------
> 1 |1 |abc def
> 1 row selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Resolved: (DERBY-1262) Like-predicates: % does not match tab
character
Posted by "Knut Anders Hatlen (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1262?page=all ]
Knut Anders Hatlen resolved DERBY-1262:
---------------------------------------
Fix Version: 10.2.0.0
10.1.3.0
10.1.2.5
Resolution: Fixed
Committed to 10.1 with revision 411223.
> Like-predicates: % does not match tab character
> -----------------------------------------------
>
> Key: DERBY-1262
> URL: http://issues.apache.org/jira/browse/DERBY-1262
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Environment: Embedded, Solaris 10 x86
> Reporter: Øystein Grøvlen
> Fix For: 10.2.0.0, 10.1.3.0, 10.1.2.5
> Attachments: 1262.diff, 1262_3.diff, 1262_4.diff, 1262_5.diff, 1262_6.diff
>
> % in like predicates does not seem to match tab character. In the following example all whitespace in character literals is the tab character:
> ij> create table t4(i integer primary key, j integer, c varchar(10));
> 0 rows inserted/updated/deleted
> ij> insert into t4 values (1, 1, 'abc def');
> 1 row inserted/updated/deleted
> ij> select * from t4 where c like 'abc%';
> I |J |C
> ----------------------------------
> 0 rows selected
> ij> select * from t4 where c like 'abc %';
> I |J |C
> ----------------------------------
> 1 |1 |abc def
> 1 row selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Closed: (DERBY-1262) Like-predicates: % does not match tab
character
Posted by "Øystein Grøvlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1262?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Øystein Grøvlen closed DERBY-1262.
----------------------------------
> Like-predicates: % does not match tab character
> -----------------------------------------------
>
> Key: DERBY-1262
> URL: https://issues.apache.org/jira/browse/DERBY-1262
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.0.2.0
> Environment: Embedded, Solaris 10 x86
> Reporter: Øystein Grøvlen
> Fix For: 10.1.3.1, 10.2.1.6
>
> Attachments: 1262.diff, 1262_3.diff, 1262_4.diff, 1262_5.diff, 1262_6.diff
>
>
> % in like predicates does not seem to match tab character. In the following example all whitespace in character literals is the tab character:
> ij> create table t4(i integer primary key, j integer, c varchar(10));
> 0 rows inserted/updated/deleted
> ij> insert into t4 values (1, 1, 'abc def');
> 1 row inserted/updated/deleted
> ij> select * from t4 where c like 'abc%';
> I |J |C
> ----------------------------------
> 0 rows selected
> ij> select * from t4 where c like 'abc %';
> I |J |C
> ----------------------------------
> 1 |1 |abc def
> 1 row selected
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-1262) Like-predicates: % does not match tab
character
Posted by "Håvard Mork (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1262?page=all ]
Håvard Mork updated DERBY-1262:
-------------------------------
Attachment: 1262_4.diff
Attached new patch with corrections (thanks, Bernt)
- using the more efficient stringbuffer.setLength for null-char padding
- esc (0x1b) as control char in test case, to avoid potential future tab->space conversion.
> Like-predicates: % does not match tab character
> -----------------------------------------------
>
> Key: DERBY-1262
> URL: http://issues.apache.org/jira/browse/DERBY-1262
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Environment: Embedded, Solaris 10 x86
> Reporter: Øystein Grøvlen
> Attachments: 1262.diff, 1262_3.diff, 1262_4.diff
>
> % in like predicates does not seem to match tab character. In the following example all whitespace in character literals is the tab character:
> ij> create table t4(i integer primary key, j integer, c varchar(10));
> 0 rows inserted/updated/deleted
> ij> insert into t4 values (1, 1, 'abc def');
> 1 row inserted/updated/deleted
> ij> select * from t4 where c like 'abc%';
> I |J |C
> ----------------------------------
> 0 rows selected
> ij> select * from t4 where c like 'abc %';
> I |J |C
> ----------------------------------
> 1 |1 |abc def
> 1 row selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1262) Like-predicates: % does not match
tab character
Posted by "Håvard Mork (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1262?page=comments#action_12413834 ]
Håvard Mork commented on DERBY-1262:
------------------------------------
Thanks for your comment. The reason I didn't remove the old functions was due to metadata upgrade. Since stored preparedstatements in EmbedDatabaseMetaData is referring to the old functions, it wasn't immediately apparent to me how to deal with this.
But it is now my understanding that this is dealt with automatically by the upgrade mechanism? http://wiki.apache.org/db-derby/MetadataUpgrade
I will make the changes.
> Like-predicates: % does not match tab character
> -----------------------------------------------
>
> Key: DERBY-1262
> URL: http://issues.apache.org/jira/browse/DERBY-1262
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Environment: Embedded, Solaris 10 x86
> Reporter: Øystein Grøvlen
> Attachments: 1262.diff
>
> % in like predicates does not seem to match tab character. In the following example all whitespace in character literals is the tab character:
> ij> create table t4(i integer primary key, j integer, c varchar(10));
> 0 rows inserted/updated/deleted
> ij> insert into t4 values (1, 1, 'abc def');
> 1 row inserted/updated/deleted
> ij> select * from t4 where c like 'abc%';
> I |J |C
> ----------------------------------
> 0 rows selected
> ij> select * from t4 where c like 'abc %';
> I |J |C
> ----------------------------------
> 1 |1 |abc def
> 1 row selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
Re: [jira] Updated: (DERBY-1262) Like-predicates: % does not match tab
character
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
"H?vard Mork (JIRA)" <de...@db.apache.org> writes:
> [ http://issues.apache.org/jira/browse/DERBY-1262?page=all ]
>
> H?vard Mork updated DERBY-1262:
> -------------------------------
>
> Attachment: 1262_5.diff
>
> Sorry about bugspam. :-)
> Last patch was unnecessarily resource-hungry, use this one.
Great! I have started derbyall and will commit the patch if there are
no more comments. I'll also see if I can merge the fix to 10.1, since
I think this would be a great fix for 10.1.3 as well.
By the way, you should send a mail to the list requesting developer
status in JIRA. Then the issue could be assigned to you, and it would
show up as fixed by you.
--
Knut Anders
[jira] Updated: (DERBY-1262) Like-predicates: % does not match tab
character
Posted by "Håvard Mork (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1262?page=all ]
Håvard Mork updated DERBY-1262:
-------------------------------
Attachment: 1262_5.diff
Sorry about bugspam. :-)
Last patch was unnecessarily resource-hungry, use this one.
> Like-predicates: % does not match tab character
> -----------------------------------------------
>
> Key: DERBY-1262
> URL: http://issues.apache.org/jira/browse/DERBY-1262
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Environment: Embedded, Solaris 10 x86
> Reporter: Øystein Grøvlen
> Attachments: 1262.diff, 1262_3.diff, 1262_4.diff, 1262_5.diff
>
> % in like predicates does not seem to match tab character. In the following example all whitespace in character literals is the tab character:
> ij> create table t4(i integer primary key, j integer, c varchar(10));
> 0 rows inserted/updated/deleted
> ij> insert into t4 values (1, 1, 'abc def');
> 1 row inserted/updated/deleted
> ij> select * from t4 where c like 'abc%';
> I |J |C
> ----------------------------------
> 0 rows selected
> ij> select * from t4 where c like 'abc %';
> I |J |C
> ----------------------------------
> 1 |1 |abc def
> 1 row selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1262) Like-predicates: % does not match
tab character
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1262?page=comments#action_12377878 ]
Satheesh Bandaram commented on DERBY-1262:
------------------------------------------
Good find. By avoiding Like optimization, the results appear to be right. Like optimizations can be disabled by having an expression on the left, as below. But it has unwanted side effect to prevent using of any indexes, if present.
Possible work around could be:
ij> select * from t4 where c like 'abc%';
I |J |C
----------------------------------
0 rows selected
ij> select * from t4 where cast(c as varchar(20)) like 'abc%';
I |J |C
----------------------------------
1 |1 |abc def
1 row selected
Fixing the code issue will involve correctly generating a lessThan/greaterThan value, I think.
> Like-predicates: % does not match tab character
> -----------------------------------------------
>
> Key: DERBY-1262
> URL: http://issues.apache.org/jira/browse/DERBY-1262
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Environment: Embedded, Solaris 10 x86
> Reporter: Øystein Grøvlen
>
> % in like predicates does not seem to match tab character. In the following example all whitespace in character literals is the tab character:
> ij> create table t4(i integer primary key, j integer, c varchar(10));
> 0 rows inserted/updated/deleted
> ij> insert into t4 values (1, 1, 'abc def');
> 1 row inserted/updated/deleted
> ij> select * from t4 where c like 'abc%';
> I |J |C
> ----------------------------------
> 0 rows selected
> ij> select * from t4 where c like 'abc %';
> I |J |C
> ----------------------------------
> 1 |1 |abc def
> 1 row selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1262) Like-predicates: % does not match tab
character
Posted by "Håvard Mork (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1262?page=all ]
Håvard Mork updated DERBY-1262:
-------------------------------
Attachment: 1262_3.diff
New patch:
Removed duplicate greaterThan/lessThan methods, and now relying on the upgrade mechanism to upgrade the stored preparedstatements (SPS).
The patch addresses both greaterThan and lessThan, and adds null padding on the right side so the comparisons will correctly respond to control characters. Changing lessThan was necessary to make 'asdf%' not include 'asdg\t' (\t is tab char).
All derbylang still passes.
> Like-predicates: % does not match tab character
> -----------------------------------------------
>
> Key: DERBY-1262
> URL: http://issues.apache.org/jira/browse/DERBY-1262
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Environment: Embedded, Solaris 10 x86
> Reporter: Øystein Grøvlen
> Attachments: 1262.diff, 1262_3.diff
>
> % in like predicates does not seem to match tab character. In the following example all whitespace in character literals is the tab character:
> ij> create table t4(i integer primary key, j integer, c varchar(10));
> 0 rows inserted/updated/deleted
> ij> insert into t4 values (1, 1, 'abc def');
> 1 row inserted/updated/deleted
> ij> select * from t4 where c like 'abc%';
> I |J |C
> ----------------------------------
> 0 rows selected
> ij> select * from t4 where c like 'abc %';
> I |J |C
> ----------------------------------
> 1 |1 |abc def
> 1 row selected
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira