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

        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.
        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
        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
        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
        Ø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)

          People

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

            Dates

            • Created:
              Updated:
              Resolved:

              Development