Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-4388

Equality does not respect the NOPAD collation attribute

    XMLWordPrintable

    Details

      Description

      I create a table with a VARCHAR column using latin1_swedish_nopad_ci as a collation. I expect strings that have different number of trailing spaces to be compared as not equal. But in fact ColumnStore ignores the NOPAD collation attribute in the equality operator and compares 'a' and 'a ' as equal.

      This script demonstrates the problem:

      DROP TABLE IF EXISTS t5;
      CREATE TABLE t5 (a VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_swedish_nopad_ci) ENGINE=COLUMNSTORE;
      INSERT INTO t5 VALUES ('a'),('a ');
      SELECT HEX(a) FROM t5 WHERE a='a';
      

      +--------+
      | HEX(a) |
      +--------+
      | 61     |
      | 6120   |
      +--------+
      

      Notice, it returned two rows. Only the row with the hex value 61 is expected.

      SELECT HEX(a) FROM t5 WHERE a='a ';
      

      +--------+
      | HEX(a) |
      +--------+
      | 61     |
      | 6120   |
      +--------+
      

      Notice, it returned two rows. Only the row with the hex value 6120 is expected.

      Note, if I run the same script with another engine, e.g. InnoDB, it returns a correct result:

      DROP TABLE IF EXISTS t5;
      CREATE TABLE t5 (a VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_swedish_nopad_ci) ENGINE=INNODB;
      INSERT INTO t5 VALUES ('a'),('a ');
      SELECT HEX(a) FROM t5 WHERE a='a';
      

      +--------+
      | HEX(a) |
      +--------+
      | 61     |
      +--------+
      

      SELECT HEX(a) FROM t5 WHERE a='a ';
      

      +--------+
      | HEX(a) |
      +--------+
      | 6120   |
      +--------+
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              dleeyh Daniel Lee
              Reporter:
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration