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

IN operation doesn't follow COLLATE

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.6.2, 6.2.1
    • 6.2.3
    • ?
    • None
    • mariadb/columnstore Docker container
    • 2021-16

    Description

      I currently write some queries during migration to ColumnStore from another Database and stumbled upon a probable bug concerning collation.

      We have columns where we use WHERE clauses where the matching has to be case sensitive. Therefore I created the database with a binary collation as default settings.

      When filtering with a WHERE clause using the LIKE operator, the collation settings seem to be used. When using with IN this seem to be not the case.

      Expected result: IN operator should also be case sensitive when set in the database settings.

      MariaDB [dac]> CREATE DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
      Query OK, 1 row affected (0.028 sec)
       
      MariaDB [dac]> USE test;
      Database changed
      MariaDB [test]> DROP TABLE IF EXISTS collation_test;
      Query OK, 0 rows affected, 1 warning (0.325 sec)
       
      MariaDB [test]>
      MariaDB [test]> CREATE TABLE collation_test (
          -> LETTERS TEXT NULL
          -> )
          -> ENGINE=ColumnStore;
      Query OK, 0 rows affected (0.878 sec)
       
      MariaDB [test]>
      MariaDB [test]> INSERT INTO collation_test (LETTERS) VALUES ('a'), ('A'), ('b'), ('B');
      Query OK, 4 rows affected (0.827 sec)
      Records: 4  Duplicates: 0  Warnings: 0
       
      MariaDB [test]>
      MariaDB [test]> SELECT * FROM collation_test;
      +---------+
      | LETTERS |
      +---------+
      | a       |
      | A       |
      | b       |
      | B       |
      +---------+
      4 rows in set (0.396 sec)
       
      MariaDB [test]>
      MariaDB [test]> SELECT * FROM collation_test WHERE LETTERS LIKE 'a';
      +---------+
      | LETTERS |
      +---------+
      | a       |
      +---------+
      1 row in set (0.029 sec)
       
      MariaDB [test]> SELECT * FROM collation_test WHERE LETTERS IN ('a');
      +---------+
      | LETTERS |
      +---------+
      | a       |
      | A       |
      +---------+
      2 rows in set (0.047 sec)
       
      MariaDB [test]>
      

      Setting the collation explicitly does not help either

      MariaDB [test]> SELECT * FROM collation_test WHERE LETTERS IN ('a' COLLATE utf8mb4_bin);
      ERROR 1253 (42000): COLLATION 'utf8mb4_bin' is not valid for CHARACTER SET 'latin1'
      

      The setting is though set to utf8mb4_bin. (checked via SHOW TABLE STATUS LIKE 'collation_test'.

      Attachments

        Activity

          People

            dleeyh Daniel Lee (Inactive)
            tdeussing_appian Tobias Deussing
            Votes:
            1 Vote for this issue
            Watchers:
            5 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.