Derby
  1. Derby
  2. DERBY-6030

Length of escape string in LIKE ... ESCAPE not properly checked with territory based collation

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.9.1.0
    • Fix Version/s: 10.8.3.3, 10.9.2.2, 10.10.1.1
    • Component/s: SQL
    • Labels:
      None
    • Issue & fix info:
      Repro attached

      Description

      When an ESCAPE clause contains multiple characters, but only a single collation element, they are accepted as long as it's not a literal:

      ij> connect 'jdbc:derby:memory:nodb;create=true;territory=no;collation=TERRITORY_BASED';
      ij> create table t(x varchar(20), y varchar(20), z varchar(20));
      0 rows inserted/updated/deleted
      ij> insert into t values ('abc', 'def', 'aa');
      1 row inserted/updated/deleted
      ij> select * from t where x like y escape z;
      X |Y |Z
      --------------------------------------------------------------

      0 rows selected

      If the same query is performed with a literal in the ESCAPE clause, the problem is detected and correctly reported:

      ij> select * from t where x like y escape 'aa';
      ERROR 22019: Invalid escape sequence, 'aa'. The escape string must be exactly one character. It cannot be a null or more than one character.

      1. d6030-1a.diff
        4 kB
        Knut Anders Hatlen

        Activity

        Knut Anders Hatlen created issue -
        Knut Anders Hatlen made changes -
        Field Original Value New Value
        Assignee Knut Anders Hatlen [ knutanders ]
        Knut Anders Hatlen made changes -
        Status Open [ 1 ] In Progress [ 3 ]
        Hide
        Knut Anders Hatlen added a comment -

        The attached patch makes WorkHorseForCollatorDatatypes.like() check the character length of the escape sequence as well as the number of collation elements, and adds a test case to verify the fix.

        All of the regression tests passed.

        Show
        Knut Anders Hatlen added a comment - The attached patch makes WorkHorseForCollatorDatatypes.like() check the character length of the escape sequence as well as the number of collation elements, and adds a test case to verify the fix. All of the regression tests passed.
        Knut Anders Hatlen made changes -
        Attachment d6030-1a.diff [ 12563933 ]
        Knut Anders Hatlen made changes -
        Issue & fix info Repro attached [ 10424 ] Patch Available,Repro attached [ 10102, 10424 ]
        Hide
        Dag H. Wanvik added a comment -

        Looks like a good patch to me. Thanks for good explanation in the test case javadoc +1. Tiny nit: patch added a line with tabs in it (#16).

        Show
        Dag H. Wanvik added a comment - Looks like a good patch to me. Thanks for good explanation in the test case javadoc +1. Tiny nit: patch added a line with tabs in it (#16).
        Hide
        Knut Anders Hatlen added a comment -

        Thanks, Dag. I cleaned up the patch as suggested and committed revision 1431259.

        Show
        Knut Anders Hatlen added a comment - Thanks, Dag. I cleaned up the patch as suggested and committed revision 1431259.
        Knut Anders Hatlen made changes -
        Status In Progress [ 3 ] Resolved [ 5 ]
        Issue & fix info Patch Available,Repro attached [ 10102, 10424 ] Repro attached [ 10424 ]
        Fix Version/s 10.10.0.0 [ 12321550 ]
        Resolution Fixed [ 1 ]
        Knut Anders Hatlen made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        Gavin made changes -
        Workflow jira [ 12744116 ] Default workflow, editable Closed status [ 12802829 ]
        Hide
        Mamta A. Satoor added a comment -

        I am working on backporting this jira but the changes seem to depend on DERBY-2699( performance of like in territory based collation databases may be improved by changing way collation elements are calculated. ). I will work on backporting DERBY-2699 first and then come back to this jira.

        Show
        Mamta A. Satoor added a comment - I am working on backporting this jira but the changes seem to depend on DERBY-2699 ( performance of like in territory based collation databases may be improved by changing way collation elements are calculated. ). I will work on backporting DERBY-2699 first and then come back to this jira.
        Mamta A. Satoor made changes -
        Assignee Knut Anders Hatlen [ knutanders ] Mamta A. Satoor [ mamtas ]
        Hide
        ASF subversion and git services added a comment -

        Commit 1510801 from Mamta A. Satoor in branch 'code/branches/10.9'
        [ https://svn.apache.org/r1510801 ]

        Backporting revision 1430735 to 10.9

        Backporting DERBY-6030(Length of escape string in LIKE ... ESCAPE not properly checked with territory based collation) to 10.9

        Show
        ASF subversion and git services added a comment - Commit 1510801 from Mamta A. Satoor in branch 'code/branches/10.9' [ https://svn.apache.org/r1510801 ] Backporting revision 1430735 to 10.9 Backporting DERBY-6030 (Length of escape string in LIKE ... ESCAPE not properly checked with territory based collation) to 10.9
        Hide
        ASF subversion and git services added a comment -

        Commit 1511054 from Mamta A. Satoor in branch 'code/branches/10.8'
        [ https://svn.apache.org/r1511054 ]

        Backporting revision 1430735 to 10.8

        Backporting DERBY-6030(Length of escape string in LIKE ... ESCAPE not properly checked with territory based collation) to 10.8

        Show
        ASF subversion and git services added a comment - Commit 1511054 from Mamta A. Satoor in branch 'code/branches/10.8' [ https://svn.apache.org/r1511054 ] Backporting revision 1430735 to 10.8 Backporting DERBY-6030 (Length of escape string in LIKE ... ESCAPE not properly checked with territory based collation) to 10.8
        Mamta A. Satoor made changes -
        Fix Version/s 10.8.3.1 [ 12323475 ]
        Fix Version/s 10.9.2.2 [ 12323562 ]
        Transition Time In Source Status Execution Times Last Executer Last Execution Date
        Open Open In Progress In Progress
        6d 20h 1 Knut Anders Hatlen 09/Jan/13 12:32
        In Progress In Progress Resolved Resolved
        22h 1 Knut Anders Hatlen 10/Jan/13 10:33
        Resolved Resolved Closed Closed
        157d 22h 53m 1 Knut Anders Hatlen 17/Jun/13 10:27

          People

          • Assignee:
            Mamta A. Satoor
            Reporter:
            Knut Anders Hatlen
          • Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development