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

              dleeyh Daniel Lee (Inactive)
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.