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 de...@db.apache.org on 2004/09/29 21:11:50 UTC
[jira] Created: (DERBY-20) LIKE handles strings with control characters incorrectly.
Message:
A new issue has been created in JIRA.
---------------------------------------------------------------------
View the issue:
http://issues.apache.org/jira/browse/DERBY-20
Here is an overview of the issue:
---------------------------------------------------------------------
Key: DERBY-20
Summary: LIKE handles strings with control characters incorrectly.
Type: Bug
Status: Unassigned
Priority: Minor
Project: Derby
Components:
SQL
Versions:
10.0.2.0
Assignee:
Reporter: Tulika Agrawal
Created: Wed, 29 Sep 2004 12:11 PM
Updated: Wed, 29 Sep 2004 12:11 PM
Description:
Reporting for Daniel John Debrunner.
If a string contains control characters in the regions matched
by wild card characters then in some situations a LIKE will
return false instead of true and the row will not be returned.
Caused by the dynamic like optimization using >= with a prefix
which is is equivalent to >= on the prefeix appended with
blanks and not null (\u0000) characters.
---------------------------------------------------------------------
JIRA INFORMATION:
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
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-20) LIKE handles strings with control characters incorrectly.
Posted by "David Van Couvering (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-20?page=all ]
David Van Couvering updated DERBY-20:
-------------------------------------
Attachment: server.log
> LIKE handles strings with control characters incorrectly.
> ---------------------------------------------------------
>
> Key: DERBY-20
> URL: http://issues.apache.org/jira/browse/DERBY-20
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Reporter: Tulika Agrawal
> Priority: Minor
> Attachments: server.log
>
> Reporting for Daniel John Debrunner.
> If a string contains control characters in the regions matched
> by wild card characters then in some situations a LIKE will
> return false instead of true and the row will not be returned.
> Caused by the dynamic like optimization using >= with a prefix
> which is is equivalent to >= on the prefeix appended with
> blanks and not null (\u0000) characters.
--
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-20) LIKE handles strings with control
characters incorrectly.
Posted by "Øystein Grøvlen (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-20?page=all ]
Øystein Grøvlen closed DERBY-20.
--------------------------------
Fix Version/s: 10.2.0.0
10.1.3.1
Resolution: Duplicate
This is a duplicate of DERBY-1262 (fixed)
> LIKE handles strings with control characters incorrectly.
> ---------------------------------------------------------
>
> Key: DERBY-20
> URL: http://issues.apache.org/jira/browse/DERBY-20
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.0.2.0
> Reporter: Tulika Agrawal
> Priority: Minor
> Fix For: 10.2.0.0, 10.1.3.1
>
> Attachments: server.log
>
>
> Reporting for Daniel John Debrunner.
> If a string contains control characters in the regions matched
> by wild card characters then in some situations a LIKE will
> return false instead of true and the row will not be returned.
> Caused by the dynamic like optimization using >= with a prefix
> which is is equivalent to >= on the prefeix appended with
> blanks and not null (\u0000) characters.
--
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-20) LIKE handles strings with control characters incorrectly.
Posted by "David Van Couvering (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-20?page=comments#action_12316409 ]
David Van Couvering commented on DERBY-20:
------------------------------------------
Paul Riethmuller writes:
That is not a Derby bug - it's being strict about the SQL standard.
Your LIKE string contains no wildcard characters, so you should write either:
le.name = 'Ber%'
or
le.name LIKE 'Ber\%%' ESCAPE '\'
depending on your intention.
HTH
Paul
but then Mitesh Meshwani responds:
FWIW, We get the same result using db2jcc.jar as driver against derby
That is when using the original query (with Ber\%% for the Like clause ), we do not get the expected rows.
When using the modified query (Ber\% for the Like clause ), we get an exception with SQLSTATE=22025 ("The LIKE predicate string pattern contains an invalid occurrence of an escape character.")
Thanks,
Mitesh
> LIKE handles strings with control characters incorrectly.
> ---------------------------------------------------------
>
> Key: DERBY-20
> URL: http://issues.apache.org/jira/browse/DERBY-20
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Reporter: Tulika Agrawal
> Priority: Minor
> Attachments: server.log
>
> Reporting for Daniel John Debrunner.
> If a string contains control characters in the regions matched
> by wild card characters then in some situations a LIKE will
> return false instead of true and the row will not be returned.
> Caused by the dynamic like optimization using >= with a prefix
> which is is equivalent to >= on the prefeix appended with
> blanks and not null (\u0000) characters.
--
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-20) LIKE handles strings with control characters incorrectly.
Posted by "David Van Couvering (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-20?page=comments#action_12316410 ]
David Van Couvering commented on DERBY-20:
------------------------------------------
Lance Anderson adds:
The problem is a Derby engine issue where you do:
Like 'foo\%%' escape '\'
will fail. However,
if I change it to
like 'foo\%bar' escape '\'
The queries will work. so it appears to be an issue parsing the escape when there are multiple wildcard characters in a row.
> LIKE handles strings with control characters incorrectly.
> ---------------------------------------------------------
>
> Key: DERBY-20
> URL: http://issues.apache.org/jira/browse/DERBY-20
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Reporter: Tulika Agrawal
> Priority: Minor
> Attachments: server.log
>
> Reporting for Daniel John Debrunner.
> If a string contains control characters in the regions matched
> by wild card characters then in some situations a LIKE will
> return false instead of true and the row will not be returned.
> Caused by the dynamic like optimization using >= with a prefix
> which is is equivalent to >= on the prefeix appended with
> blanks and not null (\u0000) characters.
--
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] Commented: (DERBY-20) LIKE handles strings with control
characters incorrectly.
Posted by Shreyas Kaushik <Sh...@Sun.COM>.
Updates ?
~ Shreyas
Shreyas Kaushik wrote:
> Any updates here?
>
> ~ Shreyas
>
> Shreyas Kaushik (JIRA) wrote:
>
>> [
>> http://issues.apache.org/jira/browse/DERBY-20?page=comments#action_58547
>> ]
>> Shreyas Kaushik commented on DERBY-20:
>> --------------------------------------
>>
>> A more clear comment here would be useful.
>>
>> Clear meaning, what is the use case that you are talking about here?
>> A small example of the behaviour with expcted output vs. current
>> output would help.
>>
>>
>>
>>> LIKE handles strings with control characters incorrectly.
>>> ---------------------------------------------------------
>>>
>>> Key: DERBY-20
>>> URL: http://issues.apache.org/jira/browse/DERBY-20
>>> Project: Derby
>>> Type: Bug
>>> Components: SQL
>>> Versions: 10.0.2.0
>>> Reporter: Tulika Agrawal
>>> Priority: Minor
>>>
>>
>>
>>
>>
>>> Reporting for Daniel John Debrunner.
>>> If a string contains control characters in the regions matched by
>>> wild card characters then in some situations a LIKE will return
>>> false instead of true and the row will not be returned.
>>> Caused by the dynamic like optimization using >= with a prefix which
>>> is is equivalent to >= on the prefeix appended with blanks and not
>>> null (\u0000) characters.
>>>
>>
>>
>>
>>
Re: [jira] Commented: (DERBY-20) LIKE handles strings with control
characters incorrectly.
Posted by Shreyas Kaushik <Sh...@Sun.COM>.
Any updates here?
~ Shreyas
Shreyas Kaushik (JIRA) wrote:
> [ http://issues.apache.org/jira/browse/DERBY-20?page=comments#action_58547 ]
>
>Shreyas Kaushik commented on DERBY-20:
>--------------------------------------
>
>A more clear comment here would be useful.
>
>Clear meaning, what is the use case that you are talking about here? A small example of the behaviour with expcted output vs. current output would help.
>
>
>
>>LIKE handles strings with control characters incorrectly.
>>---------------------------------------------------------
>>
>> Key: DERBY-20
>> URL: http://issues.apache.org/jira/browse/DERBY-20
>> Project: Derby
>> Type: Bug
>> Components: SQL
>> Versions: 10.0.2.0
>> Reporter: Tulika Agrawal
>> Priority: Minor
>>
>>
>
>
>
>>Reporting for Daniel John Debrunner.
>>If a string contains control characters in the regions matched
>>by wild card characters then in some situations a LIKE will
>>return false instead of true and the row will not be returned.
>>Caused by the dynamic like optimization using >= with a prefix
>>which is is equivalent to >= on the prefeix appended with
>>blanks and not null (\u0000) characters.
>>
>>
>
>
>
[jira] Commented: (DERBY-20) LIKE handles strings with control characters incorrectly.
Posted by "Shreyas Kaushik (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-20?page=comments#action_58547 ]
Shreyas Kaushik commented on DERBY-20:
--------------------------------------
A more clear comment here would be useful.
Clear meaning, what is the use case that you are talking about here? A small example of the behaviour with expcted output vs. current output would help.
> LIKE handles strings with control characters incorrectly.
> ---------------------------------------------------------
>
> Key: DERBY-20
> URL: http://issues.apache.org/jira/browse/DERBY-20
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Reporter: Tulika Agrawal
> Priority: Minor
>
> Reporting for Daniel John Debrunner.
> If a string contains control characters in the regions matched
> by wild card characters then in some situations a LIKE will
> return false instead of true and the row will not be returned.
> Caused by the dynamic like optimization using >= with a prefix
> which is is equivalent to >= on the prefeix appended with
> blanks and not null (\u0000) characters.
--
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
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-20) LIKE handles strings with control characters incorrectly.
Posted by "David Van Couvering (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-20?page=comments#action_12316408 ]
David Van Couvering commented on DERBY-20:
------------------------------------------
This issue came up with EJBQL testing of the app server here at Sun. I am including some comments from the folks doing the testing (Sailaja Rao). Hopefully this will help us resolve this item
The below EJBQL query
> when run does not return any dataset. When it is supposed to return the
> following as possible matches :
>
> Ber%in
> Ber%lin
>
> *SELECT OBJECT(le) FROM LIKEESCAPE le WHERE le.name LIKE 'Ber\%%' ESCAPE
> '\' *
>
> where the dataset contains the values :
>
> insert into LIKEESCAPE values('1', 'Berlin');
> insert into LIKEESCAPE values('2', 'Ber_in');
> insert into LIKEESCAPE values('3', 'Ber\in');
> insert into LIKEESCAPE values('4', 'Ber%in');
> insert into LIKEESCAPE values('5', 'Berin');
> insert into LIKEESCAPE values('6', 'Ber%lin');
Here is the SQL statement, when the test is executed. I am also attaching the server.log file.
[#|2005-07-20T17:43:13.031-0800|FINE|TestAbbrevProductName9.0|javax.enterprise.resource.jdo.sqlstore.sql|_ThreadID=15;|SQL statement<select t0."CUSTNO", t0."NAME" from "LIKEESCAPE" t0 where t0."NAME" LIKE ? ESCAPE ? > with input values:java.lang.String:Ber\%%, java.lang.Character:\|#]
> LIKE handles strings with control characters incorrectly.
> ---------------------------------------------------------
>
> Key: DERBY-20
> URL: http://issues.apache.org/jira/browse/DERBY-20
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Reporter: Tulika Agrawal
> Priority: Minor
> Attachments: server.log
>
> Reporting for Daniel John Debrunner.
> If a string contains control characters in the regions matched
> by wild card characters then in some situations a LIKE will
> return false instead of true and the row will not be returned.
> Caused by the dynamic like optimization using >= with a prefix
> which is is equivalent to >= on the prefeix appended with
> blanks and not null (\u0000) characters.
--
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