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

Concatenation operator confuses collation

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.7.1.1, 10.8.1.2, 10.8.2.2, 10.8.3.0, 10.9.1.0, 10.10.1.1
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
      Urgent
    • Bug behavior facts:
      Regression

      Description

      Given this table:

      ij> connect 'jdbc:derby:memory:db;territory=en;collation=TERRITORY_BASED;create=true';
      ij> create table t(x varchar(10));
      0 rows inserted/updated/deleted
      ij> insert into t values 'a' || 'b';
      1 row inserted/updated/deleted

      The following query works:

      ij> select * from t where x = 'ab';
      X
      ----------
      ab

      1 row selected

      However, if the string is generated with the concatenation operator, an exception is thrown:

      ij> select * from t where x = 'a' || 'b';
      ERROR 42818: Comparisons between 'VARCHAR (TERRITORY_BASED)' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')

      I'd expect the two queries to be equivalent.

        Attachments

          Activity

            People

            • Assignee:
              kasunprabath Kasun Prabath Amarasinghe
              Reporter:
              knutanders Knut Anders Hatlen
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated: