Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-6030

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

    Details

    • Type: Bug
    • Status: Closed
    • Priority: 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.

        Attachments

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

          Activity

            People

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

              Dates

              • Created:
                Updated:
                Resolved: