Derby
  1. Derby
  2. DERBY-1386

Wrong results with query using LIKE and ESCAPE clause that includes "%", "\", and "_"

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 10.1.3.1, 10.2.1.6
    • Fix Version/s: 10.1.3.1, 10.2.1.6
    • Component/s: SQL
    • Labels:
      None
    • Issue & fix info:
      Patch Available
    • Bug behavior facts:
      Regression

      Description

      After the fix for DERBY-1262 was checked in, I'm noticing that the following query now returns different results. Prior to the fix for DERBY-1262 the query returned 2 rows; now it doesn't return any rows.

      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 '\';

      Before DERBY-1262, the SELECT returned:

      C1
      ----------
      %_\a
      %_\e

      2 rows selected

      Now it returns:

      C1
      ----------

      0 rows selected

      Brief inspection of the query and data suggest to me that these new results (i.e. no rows) are wrong, and that Derby should in fact return 2 rows/.

      Based on comments in DERBY-1262, I'm creating a new Jira issue for the regression since it has been checked into the 10.1 maintenance branch. I've set the priority to "Critical" since this could potentially delay a 10.1.3 release--I.e. I don't think we'd want to release 10.1.3 knowing that we have a wrong results regression. But if anyone thinks that's not the correct priority, feel free to speak up.

      Other option, of course, is to back out the change for DERBY-1262 in 10.1 and then lower the priority accordingly.

      Input/feedback/comments would be appreciated.

      1. derby-1386-v1.stat
        0.2 kB
        Knut Anders Hatlen
      2. derby-1386-v1.diff
        9 kB
        Knut Anders Hatlen

        Issue Links

          Activity

            People

            • Assignee:
              Knut Anders Hatlen
              Reporter:
              A B
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development