[MCOL-4899] IN operation doesn't follow COLLATE Created: 2021-10-16  Updated: 2022-03-01  Resolved: 2022-01-14

Status: Closed
Project: MariaDB ColumnStore
Component/s: ?
Affects Version/s: 5.6.2, 6.2.1
Fix Version/s: 6.2.3

Type: Bug Priority: Major
Reporter: Tobias Deussing Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 1
Labels: None
Environment:

mariadb/columnstore Docker container

Sprint: 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'.

 Comments   
Comment by Valerii Kravchuk [ 2021-10-18 ]

This is how it works for InnoDB:

MariaDB [test]> CREATE TABLE `collation_test` (
 
    ->   `LETTERS` text COLLATE utf8mb4_bin DEFAULT NULL
 
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
 
Query OK, 0 rows affected (1.051 sec)
 
 
 
MariaDB [test]> insert into collation_test values ('a'), ('A');
 
Query OK, 2 rows affected (0.267 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> select * from collation_test where LETTERS like 'a';
 
+---------+
 
| LETTERS |
 
+---------+
 
| a       |
 
+---------+
 
1 row in set (0.032 sec)
 
 
 
MariaDB [test]> select * from collation_test where LETTERS = 'a';
 
+---------+
 
| LETTERS |
 
+---------+
 
| a       |
 
+---------+
 
1 row in set (0.051 sec)
 
 
 
MariaDB [test]> select * from collation_test where LETTERS in ('a');
 
+---------+
 
| LETTERS |
 
+---------+
 
| a       |
 
+---------+
 
1 row in set (0.002 sec)

As we have a difference in behavior here for Columnstore and I can not find any obvious explanation or logic (LIKE vs IN behavior) I'd agree that this is a bug.

Comment by Roman [ 2021-12-09 ]

valerii tdeussing_appian MCS doesn't leverage encoding settings set at database level so I am not sure the target table has encoding set. That is why MDB returns ERROR 1253 (42000): COLLATION 'utf8mb4_bin' is not valid for CHARACTER SET 'latin1'. I suggest to explicitly set encoding/collation creating the table.

Comment by Roman [ 2021-12-09 ]

After certain testing I need to confess there must be wrong collation propagation somewhere in MCS so that latin1_swedish_ci is used as the collation.
This is a legitimate bug and we will address this.

Comment by Roman [ 2021-12-30 ]

Plz review.

Comment by Daniel Lee (Inactive) [ 2022-01-14 ]

Build verified: 6.2.3-1 (#3675)

Verified the fix using the test case in the bug description.

Generated at Thu Feb 08 02:53:51 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.