Hi. I am using mariadb columnstore and I have a problem with text comparison.

The tables using innodb use utf8mb4 for character set because of emoticon,
and the table using columnstore is set to use utf8.

So my.cnf file is set as below.

[client]
default-character-set = utf8mb4

[mysqld]
skip-character-set-client-handshake
collation-server = utf8mb4_unicode_ci
character-set-server = utf8mb4

and columnstore.xml file also change the setting
<SystemLang>en_US.utf8</SystemLang>

and then create table and test

------ create table
create table test.test1 (
seq int,
name varchar(20)
) engine=innodb default charset=utf8;

create table test.test2 (
seq int
) engine=columnstore default charset=utf8;

insert into test.test1 values(1,'사과나무');
insert into test.test1 values(1,'포도나무');
insert into test.test2 values(1);

----- use single table select then correct result

select seq,name
from test.test1
where 1=1
and substr(name,1,2) = '포도';

result)
seq name
1 포도나무

----- but innodb + columnstore table join then incorrect result
select T1.seq,name
from test.test1 T1
, test.test2 T2
where 1=1
and T1.seq = T2.seq
and substr(name,1,2) = '포도';

result)
seq name
1 사과나무
1 포도나무

I want to search for '포도'(grapes), but it also includes the result of '사과'(apple)

Is there any way I can solve it?