Derby
  1. Derby
  2. DERBY-20

LIKE handles strings with control characters incorrectly.

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Duplicate
    • Affects Version/s: 10.0.2.0
    • Fix Version/s: 10.1.3.1, 10.2.1.6
    • Component/s: SQL
    • Labels:
      None

      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.

      1. server.log
        395 kB
        David Van Couvering

        Activity

        Transition Time In Source Status Execution Times Last Executer Last Execution Date
        Open Open Closed Closed
        669d 11h 22m 1 Øystein Grøvlen 31/Jul/06 07:33
        Gavin made changes -
        Workflow jira [ 37520 ] Default workflow, editable Closed status [ 12801242 ]
        Øystein Grøvlen made changes -
        Resolution Duplicate [ 3 ]
        Status Open [ 1 ] Closed [ 6 ]
        Fix Version/s 10.2.0.0 [ 11187 ]
        Fix Version/s 10.1.3.1 [ 12311953 ]
        Hide
        Øystein Grøvlen added a comment -

        This is a duplicate of DERBY-1262 (fixed)

        Show
        Øystein Grøvlen added a comment - This is a duplicate of DERBY-1262 (fixed)
        Hide
        David Van Couvering added a comment -

        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.

        Show
        David Van Couvering added a comment - 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.
        Hide
        David Van Couvering added a comment -

        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

        Show
        David Van Couvering added a comment - 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
        David Van Couvering made changes -
        Field Original Value New Value
        Attachment server.log [ 12311348 ]
        Hide
        David Van Couvering added a comment -

        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:\|#]

        Show
        David Van Couvering added a comment - 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:\|#]
        Hide
        Shreyas Kaushik added a comment -

        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.

        Show
        Shreyas Kaushik added a comment - 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.
        Tulika Agrawal created issue -

          People

          • Assignee:
            Unassigned
            Reporter:
            Tulika Agrawal
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development