mariadb-columnstore-engine/mysql-test/columnstore/basic/t/mcol_2000.test

#
#Test column length with different charsets
#

-- source ../include/have_columnstore.inc
-- source include/have_innodb.inc
--source ../include/charset.inc

-- disable_warnings
drop database if exists test_mcol2000;
-- enable_warnings

SET @@SQL_MODE = CONCAT(@@SQL_MODE, ',STRICT_TRANS_TABLES');

#let $saved_cs = `SELECT @@character_set_client`;
#SET CHARSET utf8;

create database test_mcol2000;
use test_mcol2000;

--disable_warnings
drop table if exists orig;
drop table if exists copy1;
drop table if exists copy2;
drop table if exists copy3;
--enable_warnings

# removed datatypes:
# * timestamp - MCOL-3902
# * bit(N) - parser error
# * year - parser error
# * enum - parser error
# * set - parser error
# * binary, varbinary - MCOL-4241
create table orig (a integer not null,
                   b int default 0,
                   c smallint,
                   d tinyint,
                   e mediumint,
                   f bigint,
                   g decimal(3,2),
                   h numeric(4,3),
                   i float,
                   j double,
                   l date,
                   m datetime,
                   o time,
                   s char(17) character set utf8,
                   t varchar(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
                   w blob(10),
                   x tinyblob,
                   y blob,
                   z mediumblob,
                   aa longblob,
                   bb text(17) character set utf8,
                   cc tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
                   dd text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
                   ee mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
                   ff longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
                   ) default charset=koi8r ENGINE=InnoDB;

create table copy1 like orig;
alter table copy1 engine=columnstore;
create table copy2 like copy1;
create table copy3 engine=columnstore as select * from orig;

--replace_regex /(COLLATE utf8mb3_general_ci )// /(COLLATE utf8mb4_general_ci )// /( COLLATE=koi8r_general_ci)// /( COLLATE utf8mb3_uca1400_ai_ci)//
show create table orig;
--replace_regex /(COLLATE utf8mb3_general_ci )// /(COLLATE utf8mb4_general_ci )// /( COLLATE=koi8r_general_ci)// /( COLLATE utf8mb3_uca1400_ai_ci)//
show create table copy1;
--replace_regex /(COLLATE utf8mb3_general_ci )// /(COLLATE utf8mb4_general_ci )// /( COLLATE=koi8r_general_ci)// /( COLLATE utf8mb3_uca1400_ai_ci)//
show create table copy2;
--replace_regex /(COLLATE utf8mb3_general_ci )// /(COLLATE utf8mb4_general_ci )// /( COLLATE=koi8r_general_ci)// /( COLLATE=latin1_swedish_ci)// /( COLLATE utf8mb3_uca1400_ai_ci)// /(CHARACTER SET utf8mb3 )//
show create table copy3;

drop table orig;
drop table copy1;
drop table copy2;
drop table copy3;

# cs1 is for NOT NULL, cs2 is for nullable fields
# VARCHAR tests
# Without character set
--disable_warnings
drop table if exists cs1;
drop table if exists cs2;
drop table if exists cs3;
drop table if exists i1;
drop table if exists i2;
--enable_warnings
create table cs1 (a varchar(1) NOT NULL, b datetime NOT NULL,
                  c varchar(10) NOT NULL, d datetime NOT NULL,
                  e varchar(240) NOT NULL, f datetime NOT NULL,
                  g varchar(2000) NOT NULL, h datetime NOT NULL
                 )engine=columnstore;
create table cs2 (a varchar(1), b datetime NOT NULL,
                  c varchar(10), d datetime NOT NULL,
                  e varchar(240), f datetime NOT NULL,
                  g varchar(2000), h datetime NOT NULL
                 )engine=columnstore;
create table i1 (a varchar(1) NOT NULL, b datetime NOT NULL,
                 c varchar(10) NOT NULL, d datetime NOT NULL,
                 e varchar(240) NOT NULL, f datetime NOT NULL,
                 g varchar(2000) NOT NULL, h datetime NOT NULL
                );
create table i2 (a varchar(1), b datetime NOT NULL,
                 c varchar(10), d datetime NOT NULL,
                 e varchar(240), f datetime NOT NULL,
                 g varchar(2000), h datetime NOT NULL
                );
insert into i1 values ('a', '2020-10-25 01:02:03',
                       'bcd', '2020-10-25 01:02:04',
                       'cde', '2020-10-25 01:02:05',
                       'def', '2020-10-25 01:02:06'
                      );
insert into i2(b, d, f, h) values ('2020-10-25 01:02:03',
                                   '2020-10-25 01:02:04',
                                   '2020-10-25 01:02:05',
                                   '2020-10-25 01:02:06'
                                  );
insert into cs1 select * from i1;
insert into cs2 select * from i2;
select * from cs1;
select * from cs2;

# test some corner cases: wrong encoding, truncating, internal structure
create table cs3 like cs1;
insert into cs3 values ('1', '2020-10-25 01:02:04',
                        '1234567890', '2020-10-25 01:02:05',
                        'abc', '2020-10-25 01:02:06',
                        'cde', '2020-10-25 01:02:07');
--error 1406
insert into cs3 values ('12', '2020-10-25 01:02:05',
                        '1234567890', '2020-10-25 01:02:06',
                        'abc', '2020-10-25 01:02:07',
                        'cde', '2020-10-25 01:02:08');
insert ignore into cs3 values ('12', '2020-10-25 01:02:06',
                               '1234567890', '2020-10-25 01:02:07',
                               'abc', '2020-10-25 01:02:08',
                               'cde', '2020-10-25 01:02:09');
insert into cs3 values (_utf8mb4 0xE1B482, '2020-10-25 01:02:07',
                        '1234567890', '2020-10-25 01:02:08',
                        'abc', '2020-10-25 01:02:09',
                        'cde', '2020-10-25 01:02:10');
select * from cs3;
commit;
select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3';
# for some reason simple where clause "table_schema='test_mcol2000' and table_name='cs3'" gives empty result
select column_name, data_type, column_length from information_schema.columnstore_columns where hex(table_schema)=hex('test_mcol2000') and hex(table_name)=hex('cs3');


# With mix of utf8 and utf8mb4 character sets at the column level
--disable_warnings
drop table if exists cs1;
drop table if exists cs2;
drop table if exists cs3;
drop table if exists i1;
drop table if exists i2;
--enable_warnings
create table cs1 (a varchar(1) CHARACTER SET utf8 NOT NULL, b datetime NOT NULL,
                  c varchar(10) CHARACTER SET utf8mb4 NOT NULL, d datetime NOT NULL,
                  e varchar(240) CHARACTER SET utf8 NOT NULL, f datetime NOT NULL,
                  g varchar(2000) CHARACTER SET utf8mb4 NOT NULL, h datetime NOT NULL
                 )engine=columnstore;
create table cs2 (a varchar(1) CHARACTER SET utf8, b datetime NOT NULL,
                  c varchar(10) CHARACTER SET utf8mb4, d datetime NOT NULL,
                  e varchar(240) CHARACTER SET utf8, f datetime NOT NULL,
                  g varchar(2000) CHARACTER SET utf8mb4, h datetime NOT NULL
                 )engine=columnstore;
create table i1 (a varchar(1) CHARACTER SET utf8 NOT NULL, b datetime NOT NULL,
                 c varchar(10) CHARACTER SET utf8mb4 NOT NULL, d datetime NOT NULL,
                 e varchar(240) CHARACTER SET utf8 NOT NULL, f datetime NOT NULL,
                 g varchar(2000) CHARACTER SET utf8mb4 NOT NULL, h datetime NOT NULL
                );
create table i2 (a varchar(1) CHARACTER SET utf8, b datetime NOT NULL,
                 c varchar(10) CHARACTER SET utf8mb4, d datetime NOT NULL,
                 e varchar(240) CHARACTER SET utf8, f datetime NOT NULL,
                 g varchar(2000) CHARACTER SET utf8mb4, h datetime NOT NULL
                );
insert into i1 values ('a', '2020-10-25 01:02:03',
                       'bcd', '2020-10-25 01:02:04',
                       'cde', '2020-10-25 01:02:05',
                       'def', '2020-10-25 01:02:06'
                      );
insert into i2(b, d, f, h) values ('2020-10-25 01:02:03',
                                   '2020-10-25 01:02:04',
                                   '2020-10-25 01:02:05',
                                   '2020-10-25 01:02:06'
                                  );
insert into cs1 select * from i1;
insert into cs2 select * from i2;
select * from cs1;
select * from cs2;

# test some corner cases: wrong encoding, truncating, internal structure
create table cs3 like cs1;
insert into cs3 values ('1', '2020-10-25 01:02:04',
                        _utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0, '2020-10-25 01:02:05', -- ¹²³⁴⁵⁶⁷⁸⁹⁰
                        'abc', '2020-10-25 01:02:06',
                        'cde', '2020-10-25 01:02:07');
--error 1406
insert into cs3 values ('1', '2020-10-25 01:02:05',
                        _utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:06', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
                        'abc', '2020-10-25 01:02:07',
                        'cde', '2020-10-25 01:02:08');
insert ignore into cs3 values ('12', '2020-10-25 01:02:06',
                               _utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:07', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
                               'abc', '2020-10-25 01:02:08',
                               'cde', '2020-10-25 01:02:09');
--error 1366
insert into cs3 values (_utf8mb4 0xF09F988E, '2020-10-25 01:02:07',
                        '1234567890', '2020-10-25 01:02:08',
                        'abc', '2020-10-25 01:02:09',
                        'cde', '2020-10-25 01:02:10');
select * from cs3;
select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3';
# for some reason simple where clause "table_schema='test_mcol2000' and table_name='cs3'" gives empty result
select column_name, data_type, column_length from information_schema.columnstore_columns where hex(table_schema)=hex('test_mcol2000') and hex(table_name)=hex('cs3');


# With utf8 character set at the table level
--disable_warnings
drop table if exists cs1;
drop table if exists cs2;
drop table if exists cs3;
drop table if exists i1;
drop table if exists i2;
--enable_warnings
create table cs1 (a varchar(1) NOT NULL, b datetime NOT NULL,
                  c varchar(10) NOT NULL, d datetime NOT NULL,
                  e varchar(240) NOT NULL, f datetime NOT NULL,
                  g varchar(2000) NOT NULL, h datetime NOT NULL
                 )engine=columnstore default character set utf8;
create table cs2 (a varchar(1), b datetime NOT NULL,
                  c varchar(10), d datetime NOT NULL,
                  e varchar(240), f datetime NOT NULL,
                  g varchar(2000), h datetime NOT NULL
                 )engine=columnstore default character set utf8;
create table i1 (a varchar(1) NOT NULL, b datetime NOT NULL,
                 c varchar(10) NOT NULL, d datetime NOT NULL,
                 e varchar(240) NOT NULL, f datetime NOT NULL,
                 g varchar(2000) NOT NULL, h datetime NOT NULL
                ) default character set utf8;
create table i2 (a varchar(1), b datetime NOT NULL,
                 c varchar(10), d datetime NOT NULL,
                 e varchar(240), f datetime NOT NULL,
                 g varchar(2000), h datetime NOT NULL
                ) default character set utf8;
insert into i1 values ('a', '2020-10-25 01:02:03',
                       'bcd', '2020-10-25 01:02:04',
                       'cde', '2020-10-25 01:02:05',
                       'def', '2020-10-25 01:02:06'
                      );
insert into i2(b, d, f, h) values ('2020-10-25 01:02:03',
                                   '2020-10-25 01:02:04',
                                   '2020-10-25 01:02:05',
                                   '2020-10-25 01:02:06'
                                  );
insert into cs1 select * from i1;
insert into cs2 select * from i2;
select * from cs1;
select * from cs2;

# test some corner cases: wrong encoding, truncating, internal structure
create table cs3 like cs1;
insert into cs3 values ('1', '2020-10-25 01:02:04',
                        _utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0, '2020-10-25 01:02:05', -- ¹²³⁴⁵⁶⁷⁸⁹⁰
                        'abc', '2020-10-25 01:02:06',
                        'cde', '2020-10-25 01:02:07');
--error 1406
insert into cs3 values ('1', '2020-10-25 01:02:05',
                        _utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:06', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
                        'abc', '2020-10-25 01:02:07',
                        'cde', '2020-10-25 01:02:08');
insert ignore into cs3 values ('12', '2020-10-25 01:02:06',
                               _utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:07', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
                               'abc', '2020-10-25 01:02:08',
                               'cde', '2020-10-25 01:02:09');
--error 1366
insert into cs3 values (_utf8mb4 0xF09F988E, '2020-10-25 01:02:07',
                        '1234567890', '2020-10-25 01:02:08',
                        'abc', '2020-10-25 01:02:09',
                        'cde', '2020-10-25 01:02:10');
select * from cs3;
select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3';
# for some reason simple where clause "table_schema='test_mcol2000' and table_name='cs3'" gives empty result
select column_name, data_type, column_length from information_schema.columnstore_columns where hex(table_schema)=hex('test_mcol2000') and hex(table_name)=hex('cs3');

# With utf8mb4 character set at the table level
--disable_warnings
drop table if exists cs1;
drop table if exists cs2;
drop table if exists cs3;
drop table if exists i1;
drop table if exists i2;
--enable_warnings
create table cs1 (a varchar(1) NOT NULL, b datetime NOT NULL,
                  c varchar(10) NOT NULL, d datetime NOT NULL,
                  e varchar(240) NOT NULL, f datetime NOT NULL,
                  g varchar(2000) NOT NULL, h datetime NOT NULL
                 )engine=columnstore default character set utf8mb4;
create table cs2 (a varchar(1), b datetime NOT NULL,
                  c varchar(10), d datetime NOT NULL,
                  e varchar(240), f datetime NOT NULL,
                  g varchar(2000), h datetime NOT NULL
                 )engine=columnstore default character set utf8mb4;
create table i1 (a varchar(1) NOT NULL, b datetime NOT NULL,
                 c varchar(10) NOT NULL, d datetime NOT NULL,
                 e varchar(240) NOT NULL, f datetime NOT NULL,
                 g varchar(2000) NOT NULL, h datetime NOT NULL
                ) default character set utf8mb4;
create table i2 (a varchar(1), b datetime NOT NULL,
                 c varchar(10), d datetime NOT NULL,
                 e varchar(240), f datetime NOT NULL,
                 g varchar(2000), h datetime NOT NULL
                ) default character set utf8mb4;
insert into i1 values ('a', '2020-10-25 01:02:03',
                       'bcd', '2020-10-25 01:02:04',
                       'cde', '2020-10-25 01:02:05',
                       'def', '2020-10-25 01:02:06'
                      );
insert into i2(b, d, f, h) values ('2020-10-25 01:02:03',
                                   '2020-10-25 01:02:04',
                                   '2020-10-25 01:02:05',
                                   '2020-10-25 01:02:06'
                                  );
insert into cs1 select * from i1;
insert into cs2 select * from i2;
select * from cs1;
select * from cs2;

# test some corner cases: wrong encoding, truncating, internal structure
create table cs3 like cs1;
insert into cs3 values ('1', '2020-10-25 01:02:04',
                        _utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0, '2020-10-25 01:02:05', -- ¹²³⁴⁵⁶⁷⁸⁹⁰
                        'abc', '2020-10-25 01:02:06',
                        'cde', '2020-10-25 01:02:07');
--error 1406
insert into cs3 values ('1', '2020-10-25 01:02:05',
                        _utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:06', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
                        'abc', '2020-10-25 01:02:07',
                        'cde', '2020-10-25 01:02:08');
insert ignore into cs3 values ('12', '2020-10-25 01:02:06',
                               _utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:07', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
                               'abc', '2020-10-25 01:02:08',
                               'cde', '2020-10-25 01:02:09');
insert into cs3 values (_utf8mb4 0xF09F988E, '2020-10-25 01:02:07',
                        '1234567890', '2020-10-25 01:02:08',
                        'abc', '2020-10-25 01:02:09',
                        'cde', '2020-10-25 01:02:10');
select * from cs3;
select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3';
# for some reason simple where clause "table_schema='test_mcol2000' and table_name='cs3'" gives empty result
select column_name, data_type, column_length from information_schema.columnstore_columns where hex(table_schema)=hex('test_mcol2000') and hex(table_name)=hex('cs3');



# CHAR tests
# Without character set
--disable_warnings
drop table if exists cs1;
drop table if exists cs2;
drop table if exists cs3;
drop table if exists i1;
drop table if exists i2;
--enable_warnings
create table cs1 (a char(1) NOT NULL, b datetime NOT NULL,
                  c char(10) NOT NULL, d datetime NOT NULL,
                  e char(240) NOT NULL, f datetime NOT NULL,
                  g char(255) NOT NULL, h datetime NOT NULL
                 )engine=columnstore;
create table cs2 (a char(1), b datetime NOT NULL,
                  c char(10), d datetime NOT NULL,
                  e char(240), f datetime NOT NULL,
                  g char(255), h datetime NOT NULL
                 )engine=columnstore;
create table i1 (a char(1) NOT NULL, b datetime NOT NULL,
                 c char(10) NOT NULL, d datetime NOT NULL,
                 e char(240) NOT NULL, f datetime NOT NULL,
                 g char(255) NOT NULL, h datetime NOT NULL
                );
create table i2 (a char(1), b datetime NOT NULL,
                 c char(10), d datetime NOT NULL,
                 e char(240), f datetime NOT NULL,
                 g char(255), h datetime NOT NULL
                );
insert into i1 values ('a', '2020-10-25 01:02:03',
                       'bcd', '2020-10-25 01:02:04',
                       'cde', '2020-10-25 01:02:05',
                       'def', '2020-10-25 01:02:06'
                      );
insert into i2(b, d, f, h) values ('2020-10-25 01:02:03',
                                   '2020-10-25 01:02:04',
                                   '2020-10-25 01:02:05',
                                   '2020-10-25 01:02:06'
                                  );
insert into cs1 select * from i1;
insert into cs2 select * from i2;
select * from cs1;
select * from cs2;

# test some corner cases: wrong encoding, truncating, internal structure
create table cs3 like cs1;
insert into cs3 values ('1', '2020-10-25 01:02:04',
                        '1234567890', '2020-10-25 01:02:05',
                        'abc', '2020-10-25 01:02:06',
                        'cde', '2020-10-25 01:02:07');
--error 1406
insert into cs3 values ('1', '2020-10-25 01:02:05',
                        '12345678901', '2020-10-25 01:02:06',
                        'abc', '2020-10-25 01:02:07',
                        'cde', '2020-10-25 01:02:08');
insert ignore into cs3 values ('12', '2020-10-25 01:02:06',
                               '12345678901', '2020-10-25 01:02:07',
                               'abc', '2020-10-25 01:02:08',
                               'cde', '2020-10-25 01:02:09');
--error 1366
insert into cs3 values (_utf8mb4 0xF09F988E, '2020-10-25 01:02:07',
                        '1234567890', '2020-10-25 01:02:08',
                        'abc', '2020-10-25 01:02:09',
                        'cde', '2020-10-25 01:02:10');
select * from cs3;
select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3';
# for some reason simple where clause "table_schema='test_mcol2000' and table_name='cs3'" gives empty result
select column_name, data_type, column_length from information_schema.columnstore_columns where hex(table_schema)=hex('test_mcol2000') and hex(table_name)=hex('cs3');


# With mix of utf8 and utf8mb4 character sets at the column level
--disable_warnings
drop table if exists cs1;
drop table if exists cs2;
drop table if exists cs3;
drop table if exists i1;
drop table if exists i2;
--enable_warnings
create table cs1 (a char(1) CHARACTER SET utf8 NOT NULL, b datetime NOT NULL,
                  c char(10) CHARACTER SET utf8mb4 NOT NULL, d datetime NOT NULL,
                  e char(240) CHARACTER SET utf8 NOT NULL, f datetime NOT NULL,
                  g char(255) CHARACTER SET utf8mb4 NOT NULL, h datetime NOT NULL
                 )engine=columnstore;
create table cs2 (a char(1) CHARACTER SET utf8, b datetime NOT NULL,
                  c char(10) CHARACTER SET utf8mb4, d datetime NOT NULL,
                  e char(240) CHARACTER SET utf8, f datetime NOT NULL,
                  g char(255) CHARACTER SET utf8mb4, h datetime NOT NULL
                 )engine=columnstore;
create table i1 (a char(1) CHARACTER SET utf8 NOT NULL, b datetime NOT NULL,
                 c char(10) CHARACTER SET utf8mb4 NOT NULL, d datetime NOT NULL,
                 e char(240) CHARACTER SET utf8 NOT NULL, f datetime NOT NULL,
                 g char(255) CHARACTER SET utf8mb4 NOT NULL, h datetime NOT NULL
                );
create table i2 (a char(1) CHARACTER SET utf8, b datetime NOT NULL,
                 c char(10) CHARACTER SET utf8mb4, d datetime NOT NULL,
                 e char(240) CHARACTER SET utf8, f datetime NOT NULL,
                 g char(255) CHARACTER SET utf8mb4, h datetime NOT NULL
                );
insert into i1 values ('a', '2020-10-25 01:02:03',
                       'bcd', '2020-10-25 01:02:04',
                       'cde', '2020-10-25 01:02:05',
                       'def', '2020-10-25 01:02:06'
                      );
insert into i2(b, d, f, h) values ('2020-10-25 01:02:03',
                                   '2020-10-25 01:02:04',
                                   '2020-10-25 01:02:05',
                                   '2020-10-25 01:02:06'
                                  );
insert into cs1 select * from i1;
insert into cs2 select * from i2;
select * from cs1;
select * from cs2;

# test some corner cases: wrong encoding, truncating, internal structure
create table cs3 like cs1;
insert into cs3 values ('1', '2020-10-25 01:02:04',
                        _utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0, '2020-10-25 01:02:05', -- ¹²³⁴⁵⁶⁷⁸⁹⁰
                        'abc', '2020-10-25 01:02:06',
                        'cde', '2020-10-25 01:02:07');
--error 1406
insert into cs3 values ('1', '2020-10-25 01:02:05',
                        _utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:06', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
                        'abc', '2020-10-25 01:02:07',
                        'cde', '2020-10-25 01:02:08');
insert ignore into cs3 values ('12', '2020-10-25 01:02:06',
                               _utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:07', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
                               'abc', '2020-10-25 01:02:08',
                               'cde', '2020-10-25 01:02:09');
--error 1366
insert into cs3 values (_utf8mb4 0xF09F988E, '2020-10-25 01:02:07',
                        '1234567890', '2020-10-25 01:02:08',
                        'abc', '2020-10-25 01:02:09',
                        'cde', '2020-10-25 01:02:10');
select * from cs3;
select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3';
# for some reason simple where clause "table_schema='test_mcol2000' and table_name='cs3'" gives empty result
select column_name, data_type, column_length from information_schema.columnstore_columns where hex(table_schema)=hex('test_mcol2000') and hex(table_name)=hex('cs3');


# With utf8 character set at the table level
--disable_warnings
drop table if exists cs1;
drop table if exists cs2;
drop table if exists cs3;
drop table if exists i1;
drop table if exists i2;
--enable_warnings
create table cs1 (a char(1) NOT NULL, b datetime NOT NULL,
                  c char(10) NOT NULL, d datetime NOT NULL,
                  e char(240) NOT NULL, f datetime NOT NULL,
                  g char(255) NOT NULL, h datetime NOT NULL
                 )engine=columnstore default character set utf8;
create table cs2 (a char(1), b datetime NOT NULL,
                  c char(10), d datetime NOT NULL,
                  e char(240), f datetime NOT NULL,
                  g char(255), h datetime NOT NULL
                 )engine=columnstore default character set utf8;
create table i1 (a char(1) NOT NULL, b datetime NOT NULL,
                 c char(10) NOT NULL, d datetime NOT NULL,
                 e char(240) NOT NULL, f datetime NOT NULL,
                 g char(255) NOT NULL, h datetime NOT NULL
                ) default character set utf8;
create table i2 (a char(1), b datetime NOT NULL,
                 c char(10), d datetime NOT NULL,
                 e char(240), f datetime NOT NULL,
                 g char(255), h datetime NOT NULL
                ) default character set utf8;
insert into i1 values ('a', '2020-10-25 01:02:03',
                       'bcd', '2020-10-25 01:02:04',
                       'cde', '2020-10-25 01:02:05',
                       'def', '2020-10-25 01:02:06'
                      );
insert into i2(b, d, f, h) values ('2020-10-25 01:02:03',
                                   '2020-10-25 01:02:04',
                                   '2020-10-25 01:02:05',
                                   '2020-10-25 01:02:06'
                                  );
insert into cs1 select * from i1;
insert into cs2 select * from i2;
select * from cs1;
select * from cs2;

# test some corner cases: wrong encoding, truncating, internal structure
create table cs3 like cs1;
insert into cs3 values ('1', '2020-10-25 01:02:04',
                        _utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0, '2020-10-25 01:02:05', -- ¹²³⁴⁵⁶⁷⁸⁹⁰
                        'abc', '2020-10-25 01:02:06',
                        'cde', '2020-10-25 01:02:07');
--error 1406
insert into cs3 values ('1', '2020-10-25 01:02:05',
                        _utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:06', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
                        'abc', '2020-10-25 01:02:07',
                        'cde', '2020-10-25 01:02:08');
insert ignore into cs3 values ('12', '2020-10-25 01:02:06',
                               _utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:07', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
                               'abc', '2020-10-25 01:02:08',
                               'cde', '2020-10-25 01:02:09');
--error 1366
insert into cs3 values (_utf8mb4 0xF09F988E, '2020-10-25 01:02:07',
                        '1234567890', '2020-10-25 01:02:08',
                        'abc', '2020-10-25 01:02:09',
                        'cde', '2020-10-25 01:02:10');
select * from cs3;
select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3';
# for some reason simple where clause "table_schema='test_mcol2000' and table_name='cs3'" gives empty result
select column_name, data_type, column_length from information_schema.columnstore_columns where hex(table_schema)=hex('test_mcol2000') and hex(table_name)=hex('cs3');


# With utf8mb4 character set at the table level
--disable_warnings
drop table if exists cs1;
drop table if exists cs2;
drop table if exists cs3;
drop table if exists i1;
drop table if exists i2;
--enable_warnings
create table cs1 (a char(1) NOT NULL, b datetime NOT NULL,
                  c char(10) NOT NULL, d datetime NOT NULL,
                  e char(240) NOT NULL, f datetime NOT NULL,
                  g char(255) NOT NULL, h datetime NOT NULL
                 )engine=columnstore default character set utf8mb4;
create table cs2 (a char(1), b datetime NOT NULL,
                  c char(10), d datetime NOT NULL,
                  e char(240), f datetime NOT NULL,
                  g char(255), h datetime NOT NULL
                 )engine=columnstore default character set utf8mb4;
create table i1 (a char(1) NOT NULL, b datetime NOT NULL,
                 c char(10) NOT NULL, d datetime NOT NULL,
                 e char(240) NOT NULL, f datetime NOT NULL,
                 g char(255) NOT NULL, h datetime NOT NULL
                ) default character set utf8mb4;
create table i2 (a char(1), b datetime NOT NULL,
                 c char(10), d datetime NOT NULL,
                 e char(240), f datetime NOT NULL,
                 g char(255), h datetime NOT NULL
                ) default character set utf8mb4;
insert into i1 values ('a', '2020-10-25 01:02:03',
                       'bcd', '2020-10-25 01:02:04',
                       'cde', '2020-10-25 01:02:05',
                       'def', '2020-10-25 01:02:06'
                      );
insert into i2(b, d, f, h) values ('2020-10-25 01:02:03',
                                   '2020-10-25 01:02:04',
                                   '2020-10-25 01:02:05',
                                   '2020-10-25 01:02:06'
                                  );
insert into cs1 select * from i1;
insert into cs2 select * from i2;
select * from cs1;
select * from cs2;

# test some corner cases: wrong encoding, truncating, internal structure
create table cs3 like cs1;
insert into cs3 values ('1', '2020-10-25 01:02:04',
                        _utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0, '2020-10-25 01:02:05', -- ¹²³⁴⁵⁶⁷⁸⁹⁰
                        'abc', '2020-10-25 01:02:06',
                        'cde', '2020-10-25 01:02:07');
--error 1406
insert into cs3 values ('1', '2020-10-25 01:02:05',
                        _utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:06', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
                        'abc', '2020-10-25 01:02:07',
                        'cde', '2020-10-25 01:02:08');
insert ignore into cs3 values ('12', '2020-10-25 01:02:06',
                               _utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:07', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
                               'abc', '2020-10-25 01:02:08',
                               'cde', '2020-10-25 01:02:09');
insert into cs3 values (_utf8mb4 0xF09F988E, '2020-10-25 01:02:07',
                        '1234567890', '2020-10-25 01:02:08',
                        'abc', '2020-10-25 01:02:09',
                        'cde', '2020-10-25 01:02:10');
select * from cs3;
select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3';
# for some reason simple where clause "table_schema='test_mcol2000' and table_name='cs3'" gives empty result
select column_name, data_type, column_length from information_schema.columnstore_columns where hex(table_schema)=hex('test_mcol2000') and hex(table_name)=hex('cs3');



# TEXT tests
# Without character set
--disable_warnings
drop table if exists cs1;
drop table if exists cs2;
drop table if exists cs3;
drop table if exists i1;
drop table if exists i2;
--enable_warnings
create table cs1 (a text(8) NOT NULL, b datetime NOT NULL,
                  c text(10) NOT NULL, d datetime NOT NULL,
                  e text(255) NOT NULL, f datetime NOT NULL,
                  g text NOT NULL, h datetime NOT NULL,
                  i text(65535) NOT NULL, j datetime NOT NULL,
                  k text(16777215) NOT NULL, l datetime NOT NULL
                 )engine=columnstore;
create table cs2 (a text(8), b datetime NOT NULL,
                  c text(10), d datetime NOT NULL,
                  e text(255), f datetime NOT NULL,
                  g text, h datetime NOT NULL,
                  i text(65535), j datetime NOT NULL,
                  k text(16777215), l datetime NOT NULL
                 )engine=columnstore;
create table i1  (a text(8) NOT NULL, b datetime NOT NULL,
                  c text(10) NOT NULL, d datetime NOT NULL,
                  e text(255) NOT NULL, f datetime NOT NULL,
                  g text NOT NULL, h datetime NOT NULL,
                  i text(65535) NOT NULL, j datetime NOT NULL,
                  k text(16777215) NOT NULL, l datetime NOT NULL
                 );
create table i2  (a text(8), b datetime NOT NULL,
                  c text(10), d datetime NOT NULL,
                  e text(255), f datetime NOT NULL,
                  g text, h datetime NOT NULL,
                  i text(65535), j datetime NOT NULL,
                  k text(16777215), l datetime NOT NULL
                 );
insert into i1 values ('a', '2020-10-25 01:02:03',
                       'bcd', '2020-10-25 01:02:04',
                       'cde', '2020-10-25 01:02:05',
                       'def', '2020-10-25 01:02:06',
                       'efg', '2020-10-25 01:02:07',
                       'fgh', '2020-10-25 01:02:08'
                      );
insert into i2(b, d, f, h, j, l) values ('2020-10-25 01:02:03',
                                         '2020-10-25 01:02:04',
                                         '2020-10-25 01:02:05',
                                         '2020-10-25 01:02:06',
                                         '2020-10-25 01:02:07',
                                         '2020-10-25 01:02:08'
                                        );
insert into cs1 select * from i1;
insert into cs2 select * from i2;
select * from cs1;
select * from cs2;

# test some corner cases: wrong encoding, truncating, internal structure
create table cs3 like cs1;
insert into cs3 values ('1', '2020-10-25 01:02:04',
                        '1234567890', '2020-10-25 01:02:05',
                        'cde', '2020-10-25 01:02:07',
                        'def', '2020-10-25 01:02:08',
                        'efg', '2020-10-25 01:02:09',
                        'fgh', '2020-10-25 01:02:10');
insert into cs3 values ('1', '2020-10-25 01:02:05',
                        '12345678901', '2020-10-25 01:02:06',
                        'cde', '2020-10-25 01:02:08',
                        'def', '2020-10-25 01:02:09',
                        'efg', '2020-10-25 01:02:10',
                        'fgh', '2020-10-25 01:02:11');
--error 1366
insert into cs3 values (_utf8mb4 0xF09F988E, '2020-10-25 01:02:07',
                        '1234567890', '2020-10-25 01:02:08',
                        'cde', '2020-10-25 01:02:10',
                        'def', '2020-10-25 01:02:11',
                        'efg', '2020-10-25 01:02:12',
                        'fgh', '2020-10-25 01:02:13');
select * from cs3;
select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3';
# for some reason simple where clause "table_schema='test_mcol2000' and table_name='cs3'" gives empty result
select column_name, data_type, column_length from information_schema.columnstore_columns where hex(table_schema)=hex('test_mcol2000') and hex(table_name)=hex('cs3');


# With mix of utf8 and utf8mb4 character sets at the column level
--disable_warnings
drop table if exists cs1;
drop table if exists cs2;
drop table if exists cs3;
drop table if exists i1;
drop table if exists i2;
--enable_warnings
create table cs1 (a text(8) CHARACTER SET utf8 NOT NULL, b datetime NOT NULL,
                  c text(10) CHARACTER SET utf8mb4 NOT NULL, d datetime NOT NULL,
                  e text(255) CHARACTER SET utf8 NOT NULL, f datetime NOT NULL,
                  g text CHARACTER SET utf8mb4 NOT NULL, h datetime NOT NULL,
                  i text(65535) CHARACTER SET utf8 NOT NULL, j datetime NOT NULL,
                  k text(16777215) CHARACTER SET utf8mb4 NOT NULL, l datetime NOT NULL
                 )engine=columnstore;
create table cs2 (a text(8) CHARACTER SET utf8, b datetime NOT NULL,
                  c text(10) CHARACTER SET utf8mb4, d datetime NOT NULL,
                  e text(255) CHARACTER SET utf8, f datetime NOT NULL,
                  g text CHARACTER SET utf8mb4, h datetime NOT NULL,
                  i text(65535) CHARACTER SET utf8, j datetime NOT NULL,
                  k text(16777215) CHARACTER SET utf8mb4, l datetime NOT NULL
                 )engine=columnstore;
create table i1  (a text(8) CHARACTER SET utf8 NOT NULL, b datetime NOT NULL,
                  c text(10) CHARACTER SET utf8mb4 NOT NULL, d datetime NOT NULL,
                  e text(255) CHARACTER SET utf8 NOT NULL, f datetime NOT NULL,
                  g text CHARACTER SET utf8mb4 NOT NULL, h datetime NOT NULL,
                  i text(65535) CHARACTER SET utf8 NOT NULL, j datetime NOT NULL,
                  k text(16777215) CHARACTER SET utf8mb4 NOT NULL, l datetime NOT NULL
                 );
create table i2  (a text(8) CHARACTER SET utf8, b datetime NOT NULL,
                  c text(10) CHARACTER SET utf8mb4, d datetime NOT NULL,
                  e text(255) CHARACTER SET utf8, f datetime NOT NULL,
                  g text CHARACTER SET utf8mb4, h datetime NOT NULL,
                  i text(65535) CHARACTER SET utf8, j datetime NOT NULL,
                  k text(16777215) CHARACTER SET utf8mb4, l datetime NOT NULL
                 );
insert into i1 values ('ab', '2020-10-25 01:02:03',
                       'bcd', '2020-10-25 01:02:04',
                       'cde', '2020-10-25 01:02:05',
                       'def', '2020-10-25 01:02:06',
                       'efg', '2020-10-25 01:02:07',
                       'fgh', '2020-10-25 01:02:08'
                      );
insert into i2(b, d, f, h, j, l) values ('2020-10-25 01:02:03',
                                         '2020-10-25 01:02:04',
                                         '2020-10-25 01:02:05',
                                         '2020-10-25 01:02:06',
                                         '2020-10-25 01:02:07',
                                         '2020-10-25 01:02:08'
                                        );
insert into cs1 select * from i1;
insert into cs2 select * from i2;
select * from cs1;
select * from cs2;

# test some corner cases: wrong encoding, truncating, internal structure
create table cs3 like cs1;
insert into cs3 values ('1', '2020-10-25 01:02:04',
                        _utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0, '2020-10-25 01:02:05', -- ¹²³⁴⁵⁶⁷⁸⁹⁰
                        'cde', '2020-10-25 01:02:07',
                        'def', '2020-10-25 01:02:08',
                        'efg', '2020-10-25 01:02:09',
                        'fgh', '2020-10-25 01:02:10');
insert into cs3 values ('1', '2020-10-25 01:02:05',
                        _utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:06', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
                        'cde', '2020-10-25 01:02:08',
                        'def', '2020-10-25 01:02:09',
                        'efg', '2020-10-25 01:02:10',
                        'fgh', '2020-10-25 01:02:11');
--error 1366
insert into cs3 values (_utf8mb4 0xF09F988E, '2020-10-25 01:02:07',
                        '1234567890', '2020-10-25 01:02:08',
                        'cde', '2020-10-25 01:02:10',
                        'def', '2020-10-25 01:02:11',
                        'efg', '2020-10-25 01:02:12',
                        'fgh', '2020-10-25 01:02:13');
select * from cs3;
select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3';
# for some reason simple where clause "table_schema='test_mcol2000' and table_name='cs3'" gives empty result
select column_name, data_type, column_length from information_schema.columnstore_columns where hex(table_schema)=hex('test_mcol2000') and hex(table_name)=hex('cs3');



# With utf8 character set at the table level
--disable_warnings
drop table if exists cs1;
drop table if exists cs2;
drop table if exists cs3;
drop table if exists i1;
drop table if exists i2;
--enable_warnings
create table cs1 (a text(8) NOT NULL, b datetime NOT NULL,
                  c text(10) NOT NULL, d datetime NOT NULL,
                  e text(255) NOT NULL, f datetime NOT NULL,
                  g text NOT NULL, h datetime NOT NULL,
                  i text(65535) NOT NULL, j datetime NOT NULL,
                  k text(16777215) NOT NULL, l datetime NOT NULL
                 )engine=columnstore default character set utf8;
create table cs2 (a text(8), b datetime NOT NULL,
                  c text(10), d datetime NOT NULL,
                  e text(255), f datetime NOT NULL,
                  g text, h datetime NOT NULL,
                  i text(65535), j datetime NOT NULL,
                  k text(16777215), l datetime NOT NULL
                 )engine=columnstore default character set utf8;
create table i1  (a text(8) NOT NULL, b datetime NOT NULL,
                  c text(10) NOT NULL, d datetime NOT NULL,
                  e text(255) NOT NULL, f datetime NOT NULL,
                  g text NOT NULL, h datetime NOT NULL,
                  i text(65535) NOT NULL, j datetime NOT NULL,
                  k text(16777215) NOT NULL, l datetime NOT NULL
                 ) default character set utf8;
create table i2  (a text(8), b datetime NOT NULL,
                  c text(10), d datetime NOT NULL,
                  e text(255), f datetime NOT NULL,
                  g text, h datetime NOT NULL,
                  i text(65535), j datetime NOT NULL,
                  k text(16777215), l datetime NOT NULL
                 ) default character set utf8;
insert into i1 values ('ab', '2020-10-25 01:02:03',
                       'bcd', '2020-10-25 01:02:04',
                       'cde', '2020-10-25 01:02:05',
                       'def', '2020-10-25 01:02:06',
                       'efg', '2020-10-25 01:02:07',
                       'fgh', '2020-10-25 01:02:08'
                      );
insert into i2(b, d, f, h, j, l) values ('2020-10-25 01:02:03',
                                         '2020-10-25 01:02:04',
                                         '2020-10-25 01:02:05',
                                         '2020-10-25 01:02:06',
                                         '2020-10-25 01:02:07',
                                         '2020-10-25 01:02:08'
                                        );
insert into cs1 select * from i1;
insert into cs2 select * from i2;
select * from cs1;
select * from cs2;

# test some corner cases: wrong encoding, truncating, internal structure
create table cs3 like cs1;
insert into cs3 values ('1', '2020-10-25 01:02:04',
                        _utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0, '2020-10-25 01:02:05', -- ¹²³⁴⁵⁶⁷⁸⁹⁰
                        'cde', '2020-10-25 01:02:07',
                        'def', '2020-10-25 01:02:08',
                        'efg', '2020-10-25 01:02:09',
                        'fgh', '2020-10-25 01:02:10');
insert into cs3 values ('1', '2020-10-25 01:02:05',
                        _utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:06', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
                        'cde', '2020-10-25 01:02:08',
                        'def', '2020-10-25 01:02:09',
                        'efg', '2020-10-25 01:02:10',
                        'fgh', '2020-10-25 01:02:11');
--error 1366
insert into cs3 values (_utf8mb4 0xF09F988E, '2020-10-25 01:02:07',
                        '1234567890', '2020-10-25 01:02:08',
                        'cde', '2020-10-25 01:02:10',
                        'def', '2020-10-25 01:02:11',
                        'efg', '2020-10-25 01:02:12',
                        'fgh', '2020-10-25 01:02:13');
select * from cs3;
select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3';
# for some reason simple where clause "table_schema='test_mcol2000' and table_name='cs3'" gives empty result
select column_name, data_type, column_length from information_schema.columnstore_columns where hex(table_schema)=hex('test_mcol2000') and hex(table_name)=hex('cs3');



# With utf8mb4 character set at the table level
--disable_warnings
drop table if exists cs1;
drop table if exists cs2;
drop table if exists cs3;
drop table if exists i1;
drop table if exists i2;
--enable_warnings
create table cs1 (a text(8) NOT NULL, b datetime NOT NULL,
                  c text(10) NOT NULL, d datetime NOT NULL,
                  e text(255) NOT NULL, f datetime NOT NULL,
                  g text NOT NULL, h datetime NOT NULL,
                  i text(65535) NOT NULL, j datetime NOT NULL,
                  k text(16777215) NOT NULL, l datetime NOT NULL
                 )engine=columnstore default character set utf8mb4;
create table cs2 (a text(8), b datetime NOT NULL,
                  c text(10), d datetime NOT NULL,
                  e text(255), f datetime NOT NULL,
                  g text, h datetime NOT NULL,
                  i text(65535), j datetime NOT NULL,
                  k text(16777215), l datetime NOT NULL
                 )engine=columnstore default character set utf8mb4;
create table i1  (a text(8) NOT NULL, b datetime NOT NULL,
                  c text(10) NOT NULL, d datetime NOT NULL,
                  e text(255) NOT NULL, f datetime NOT NULL,
                  g text NOT NULL, h datetime NOT NULL,
                  i text(65535) NOT NULL, j datetime NOT NULL,
                  k text(16777215) NOT NULL, l datetime NOT NULL
                 ) default character set utf8mb4;
create table i2  (a text(8), b datetime NOT NULL,
                  c text(10), d datetime NOT NULL,
                  e text(255), f datetime NOT NULL,
                  g text, h datetime NOT NULL,
                  i text(65535), j datetime NOT NULL,
                  k text(16777215), l datetime NOT NULL
                 ) default character set utf8mb4;
insert into i1 values ('ab', '2020-10-25 01:02:03',
                       'bcd', '2020-10-25 01:02:04',
                       'cde', '2020-10-25 01:02:05',
                       'def', '2020-10-25 01:02:06',
                       'efg', '2020-10-25 01:02:07',
                       'fgh', '2020-10-25 01:02:08'
                      );
insert into i2(b, d, f, h, j, l) values ('2020-10-25 01:02:03',
                                         '2020-10-25 01:02:04',
                                         '2020-10-25 01:02:05',
                                         '2020-10-25 01:02:06',
                                         '2020-10-25 01:02:07',
                                         '2020-10-25 01:02:08'
                                        );
insert into cs1 select * from i1;
insert into cs2 select * from i2;
select * from cs1;
select * from cs2;

# test some corner cases: wrong encoding, truncating, internal structure
create table cs3 like cs1;
insert into cs3 values ('1', '2020-10-25 01:02:04',
                        _utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0, '2020-10-25 01:02:05', -- ¹²³⁴⁵⁶⁷⁸⁹⁰
                        'cde', '2020-10-25 01:02:07',
                        'def', '2020-10-25 01:02:08',
                        'efg', '2020-10-25 01:02:09',
                        'fgh', '2020-10-25 01:02:10');
insert into cs3 values ('1', '2020-10-25 01:02:05',
                        _utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:06', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
                        'cde', '2020-10-25 01:02:08',
                        'def', '2020-10-25 01:02:09',
                        'efg', '2020-10-25 01:02:10',
                        'fgh', '2020-10-25 01:02:11');
insert into cs3 values (_utf8mb4 0xF09F988E, '2020-10-25 01:02:07',
                        '1234567890', '2020-10-25 01:02:08',
                        'cde', '2020-10-25 01:02:10',
                        'def', '2020-10-25 01:02:11',
                        'efg', '2020-10-25 01:02:12',
                        'fgh', '2020-10-25 01:02:13');
select * from cs3;
select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3';
# for some reason simple where clause "table_schema='test_mcol2000' and table_name='cs3'" gives empty result
select column_name, data_type, column_length from information_schema.columnstore_columns where hex(table_schema)=hex('test_mcol2000') and hex(table_name)=hex('cs3');

drop table cs1;
drop table cs2;
drop table cs3;
drop table i1;
drop table i2;

drop database test_mcol2000;