drop database if exists test_mcol2000; SET @@SQL_MODE = CONCAT(@@SQL_MODE, ',STRICT_TRANS_TABLES'); create database test_mcol2000; use test_mcol2000; drop table if exists orig; drop table if exists copy1; drop table if exists copy2; drop table if exists copy3; 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, w blob(10), x tinyblob, y blob, z mediumblob, aa longblob, bb text(17) character set utf8, cc tinytext character set utf8mb4, dd text character set utf8mb4, ee mediumtext character set utf8mb4, ff longtext character set utf8mb4 ) 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; show create table orig; Table Create Table orig CREATE TABLE `orig` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT 0, `c` smallint(6) DEFAULT NULL, `d` tinyint(4) DEFAULT NULL, `e` mediumint(9) DEFAULT NULL, `f` bigint(20) DEFAULT NULL, `g` decimal(3,2) DEFAULT NULL, `h` decimal(4,3) DEFAULT NULL, `i` float DEFAULT NULL, `j` double DEFAULT NULL, `l` date DEFAULT NULL, `m` datetime DEFAULT NULL, `o` time DEFAULT NULL, `s` char(17) CHARACTER SET utf8mb3 DEFAULT NULL, `t` varchar(17) CHARACTER SET utf8mb4 DEFAULT NULL, `w` tinyblob DEFAULT NULL, `x` tinyblob DEFAULT NULL, `y` blob DEFAULT NULL, `z` mediumblob DEFAULT NULL, `aa` longblob DEFAULT NULL, `bb` tinytext CHARACTER SET utf8mb3 DEFAULT NULL, `cc` tinytext CHARACTER SET utf8mb4 DEFAULT NULL, `dd` text CHARACTER SET utf8mb4 DEFAULT NULL, `ee` mediumtext CHARACTER SET utf8mb4 DEFAULT NULL, `ff` longtext CHARACTER SET utf8mb4 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=koi8r show create table copy1; Table Create Table copy1 CREATE TABLE `copy1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT 0, `c` smallint(6) DEFAULT NULL, `d` tinyint(4) DEFAULT NULL, `e` mediumint(9) DEFAULT NULL, `f` bigint(20) DEFAULT NULL, `g` decimal(3,2) DEFAULT NULL, `h` decimal(4,3) DEFAULT NULL, `i` float DEFAULT NULL, `j` double DEFAULT NULL, `l` date DEFAULT NULL, `m` datetime DEFAULT NULL, `o` time DEFAULT NULL, `s` char(17) CHARACTER SET utf8mb3 DEFAULT NULL, `t` varchar(17) CHARACTER SET utf8mb4 DEFAULT NULL, `w` tinyblob DEFAULT NULL, `x` tinyblob DEFAULT NULL, `y` blob DEFAULT NULL, `z` mediumblob DEFAULT NULL, `aa` longblob DEFAULT NULL, `bb` tinytext CHARACTER SET utf8mb3 DEFAULT NULL, `cc` tinytext CHARACTER SET utf8mb4 DEFAULT NULL, `dd` text CHARACTER SET utf8mb4 DEFAULT NULL, `ee` mediumtext CHARACTER SET utf8mb4 DEFAULT NULL, `ff` longtext CHARACTER SET utf8mb4 DEFAULT NULL ) ENGINE=Columnstore DEFAULT CHARSET=koi8r show create table copy2; Table Create Table copy2 CREATE TABLE `copy2` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT 0, `c` smallint(6) DEFAULT NULL, `d` tinyint(4) DEFAULT NULL, `e` mediumint(9) DEFAULT NULL, `f` bigint(20) DEFAULT NULL, `g` decimal(3,2) DEFAULT NULL, `h` decimal(4,3) DEFAULT NULL, `i` float DEFAULT NULL, `j` double DEFAULT NULL, `l` date DEFAULT NULL, `m` datetime DEFAULT NULL, `o` time DEFAULT NULL, `s` char(17) CHARACTER SET utf8mb3 DEFAULT NULL, `t` varchar(17) CHARACTER SET utf8mb4 DEFAULT NULL, `w` tinyblob DEFAULT NULL, `x` tinyblob DEFAULT NULL, `y` blob DEFAULT NULL, `z` mediumblob DEFAULT NULL, `aa` longblob DEFAULT NULL, `bb` tinytext CHARACTER SET utf8mb3 DEFAULT NULL, `cc` tinytext CHARACTER SET utf8mb4 DEFAULT NULL, `dd` text CHARACTER SET utf8mb4 DEFAULT NULL, `ee` mediumtext CHARACTER SET utf8mb4 DEFAULT NULL, `ff` longtext CHARACTER SET utf8mb4 DEFAULT NULL ) ENGINE=Columnstore DEFAULT CHARSET=koi8r show create table copy3; Table Create Table copy3 CREATE TABLE `copy3` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT 0, `c` smallint(6) DEFAULT NULL, `d` tinyint(4) DEFAULT NULL, `e` mediumint(9) DEFAULT NULL, `f` bigint(20) DEFAULT NULL, `g` decimal(3,2) DEFAULT NULL, `h` decimal(4,3) DEFAULT NULL, `i` float DEFAULT NULL, `j` double DEFAULT NULL, `l` date DEFAULT NULL, `m` datetime DEFAULT NULL, `o` time DEFAULT NULL, `s` char(17) DEFAULT NULL, `t` varchar(17) CHARACTER SET utf8mb4 DEFAULT NULL, `w` tinyblob DEFAULT NULL, `x` tinyblob DEFAULT NULL, `y` blob DEFAULT NULL, `z` mediumblob DEFAULT NULL, `aa` longblob DEFAULT NULL, `bb` tinytext DEFAULT NULL, `cc` tinytext CHARACTER SET utf8mb4 DEFAULT NULL, `dd` text CHARACTER SET utf8mb4 DEFAULT NULL, `ee` mediumtext CHARACTER SET utf8mb4 DEFAULT NULL, `ff` longtext CHARACTER SET utf8mb4 DEFAULT NULL ) ENGINE=Columnstore DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci drop table orig; drop table copy1; drop table copy2; drop table copy3; 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; 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; a b c d e f g h 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 select * from cs2; a b c d e f g h NULL 2020-10-25 01:02:03 NULL 2020-10-25 01:02:04 NULL 2020-10-25 01:02:05 NULL 2020-10-25 01:02:06 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'); 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'); ERROR 22001: Data too long for column 'a' at row 1 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'); Warnings: Warning 1265 Data truncated for column 'a' at row 1 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; a b c d e f g h 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 1 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 ? 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 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'; column_name data_type character_maximum_length character_octet_length a varchar 1 3 b datetime NULL NULL c varchar 10 30 d datetime NULL NULL e varchar 240 720 f datetime NULL NULL g varchar 2000 6000 h datetime NULL NULL 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'); column_name data_type column_length a varchar 3 b datetime 8 c varchar 30 d datetime 8 e varchar 720 f datetime 8 g varchar 6000 h datetime 8 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; 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; a b c d e f g h 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 select * from cs2; a b c d e f g h NULL 2020-10-25 01:02:03 NULL 2020-10-25 01:02:04 NULL 2020-10-25 01:02:05 NULL 2020-10-25 01:02:06 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'); 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'); ERROR 22001: Data too long for column 'c' at row 1 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'); Warnings: Warning 1265 Data truncated for column 'a' at row 1 Warning 1265 Data truncated for column 'c' at row 1 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'); ERROR 22007: Incorrect string value: '\xF0\x9F\x98\x8E' for column `test_mcol2000`.`cs3`.`a` at row 1 select * from cs3; a b c d e f g h 1 2020-10-25 01:02:04 ??????? 2020-10-25 01:02:05 abc 2020-10-25 01:02:06 cde 2020-10-25 01:02:07 1 2020-10-25 01:02:06 ??????? 2020-10-25 01:02:07 abc 2020-10-25 01:02:08 cde 2020-10-25 01:02:09 select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3'; column_name data_type character_maximum_length character_octet_length a varchar 1 3 b datetime NULL NULL c varchar 10 40 d datetime NULL NULL e varchar 240 720 f datetime NULL NULL g varchar 2000 8000 h datetime NULL NULL 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'); column_name data_type column_length a varchar 3 b datetime 8 c varchar 40 d datetime 8 e varchar 720 f datetime 8 g varchar 8000 h datetime 8 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; 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; a b c d e f g h 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 select * from cs2; a b c d e f g h NULL 2020-10-25 01:02:03 NULL 2020-10-25 01:02:04 NULL 2020-10-25 01:02:05 NULL 2020-10-25 01:02:06 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'); 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'); ERROR 22001: Data too long for column 'c' at row 1 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'); Warnings: Warning 1265 Data truncated for column 'a' at row 1 Warning 1265 Data truncated for column 'c' at row 1 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'); ERROR 22007: Incorrect string value: '\xF0\x9F\x98\x8E' for column `test_mcol2000`.`cs3`.`a` at row 1 select * from cs3; a b c d e f g h 1 2020-10-25 01:02:04 ??????? 2020-10-25 01:02:05 abc 2020-10-25 01:02:06 cde 2020-10-25 01:02:07 1 2020-10-25 01:02:06 ??????? 2020-10-25 01:02:07 abc 2020-10-25 01:02:08 cde 2020-10-25 01:02:09 select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3'; column_name data_type character_maximum_length character_octet_length a varchar 1 3 b datetime NULL NULL c varchar 10 30 d datetime NULL NULL e varchar 240 720 f datetime NULL NULL g varchar 2000 6000 h datetime NULL NULL 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'); column_name data_type column_length a varchar 3 b datetime 8 c varchar 30 d datetime 8 e varchar 720 f datetime 8 g varchar 6000 h datetime 8 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; 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; a b c d e f g h 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 select * from cs2; a b c d e f g h NULL 2020-10-25 01:02:03 NULL 2020-10-25 01:02:04 NULL 2020-10-25 01:02:05 NULL 2020-10-25 01:02:06 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'); 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'); ERROR 22001: Data too long for column 'c' at row 1 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'); Warnings: Warning 1265 Data truncated for column 'a' at row 1 Warning 1265 Data truncated for column 'c' at row 1 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; a b c d e f g h 1 2020-10-25 01:02:04 ??????? 2020-10-25 01:02:05 abc 2020-10-25 01:02:06 cde 2020-10-25 01:02:07 1 2020-10-25 01:02:06 ??????? 2020-10-25 01:02:07 abc 2020-10-25 01:02:08 cde 2020-10-25 01:02:09 ? 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 column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3'; column_name data_type character_maximum_length character_octet_length a varchar 1 4 b datetime NULL NULL c varchar 10 40 d datetime NULL NULL e varchar 240 960 f datetime NULL NULL g varchar 2000 8000 h datetime NULL NULL 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'); column_name data_type column_length a varchar 4 b datetime 8 c varchar 40 d datetime 8 e varchar 960 f datetime 8 g varchar 8000 h datetime 8 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; 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; a b c d e f g h 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 select * from cs2; a b c d e f g h NULL 2020-10-25 01:02:03 NULL 2020-10-25 01:02:04 NULL 2020-10-25 01:02:05 NULL 2020-10-25 01:02:06 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'); 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'); ERROR 22001: Data too long for column 'c' at row 1 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'); Warnings: Warning 1265 Data truncated for column 'a' at row 1 Warning 1265 Data truncated for column 'c' at row 1 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'); ERROR 22007: Incorrect string value: '\xF0\x9F\x98\x8E' for column `test_mcol2000`.`cs3`.`a` at row 1 select * from cs3; a b c d e f g h 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 1 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 select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3'; column_name data_type character_maximum_length character_octet_length a char 1 3 b datetime NULL NULL c char 10 30 d datetime NULL NULL e char 240 720 f datetime NULL NULL g char 255 765 h datetime NULL NULL 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'); column_name data_type column_length a char 3 b datetime 8 c char 30 d datetime 8 e char 720 f datetime 8 g char 765 h datetime 8 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; 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; a b c d e f g h 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 select * from cs2; a b c d e f g h NULL 2020-10-25 01:02:03 NULL 2020-10-25 01:02:04 NULL 2020-10-25 01:02:05 NULL 2020-10-25 01:02:06 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'); 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'); ERROR 22001: Data too long for column 'c' at row 1 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'); Warnings: Warning 1265 Data truncated for column 'a' at row 1 Warning 1265 Data truncated for column 'c' at row 1 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'); ERROR 22007: Incorrect string value: '\xF0\x9F\x98\x8E' for column `test_mcol2000`.`cs3`.`a` at row 1 select * from cs3; a b c d e f g h 1 2020-10-25 01:02:04 ??????? 2020-10-25 01:02:05 abc 2020-10-25 01:02:06 cde 2020-10-25 01:02:07 1 2020-10-25 01:02:06 ??????? 2020-10-25 01:02:07 abc 2020-10-25 01:02:08 cde 2020-10-25 01:02:09 select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3'; column_name data_type character_maximum_length character_octet_length a char 1 3 b datetime NULL NULL c char 10 40 d datetime NULL NULL e char 240 720 f datetime NULL NULL g char 255 1020 h datetime NULL NULL 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'); column_name data_type column_length a char 3 b datetime 8 c char 40 d datetime 8 e char 720 f datetime 8 g char 1020 h datetime 8 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; 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; a b c d e f g h 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 select * from cs2; a b c d e f g h NULL 2020-10-25 01:02:03 NULL 2020-10-25 01:02:04 NULL 2020-10-25 01:02:05 NULL 2020-10-25 01:02:06 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'); 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'); ERROR 22001: Data too long for column 'c' at row 1 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'); Warnings: Warning 1265 Data truncated for column 'a' at row 1 Warning 1265 Data truncated for column 'c' at row 1 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'); ERROR 22007: Incorrect string value: '\xF0\x9F\x98\x8E' for column `test_mcol2000`.`cs3`.`a` at row 1 select * from cs3; a b c d e f g h 1 2020-10-25 01:02:04 ??????? 2020-10-25 01:02:05 abc 2020-10-25 01:02:06 cde 2020-10-25 01:02:07 1 2020-10-25 01:02:06 ??????? 2020-10-25 01:02:07 abc 2020-10-25 01:02:08 cde 2020-10-25 01:02:09 select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3'; column_name data_type character_maximum_length character_octet_length a char 1 3 b datetime NULL NULL c char 10 30 d datetime NULL NULL e char 240 720 f datetime NULL NULL g char 255 765 h datetime NULL NULL 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'); column_name data_type column_length a char 3 b datetime 8 c char 30 d datetime 8 e char 720 f datetime 8 g char 765 h datetime 8 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; 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; a b c d e f g h 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 select * from cs2; a b c d e f g h NULL 2020-10-25 01:02:03 NULL 2020-10-25 01:02:04 NULL 2020-10-25 01:02:05 NULL 2020-10-25 01:02:06 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'); 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'); ERROR 22001: Data too long for column 'c' at row 1 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'); Warnings: Warning 1265 Data truncated for column 'a' at row 1 Warning 1265 Data truncated for column 'c' at row 1 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; a b c d e f g h 1 2020-10-25 01:02:04 ??????? 2020-10-25 01:02:05 abc 2020-10-25 01:02:06 cde 2020-10-25 01:02:07 1 2020-10-25 01:02:06 ??????? 2020-10-25 01:02:07 abc 2020-10-25 01:02:08 cde 2020-10-25 01:02:09 ? 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 column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3'; column_name data_type character_maximum_length character_octet_length a char 1 4 b datetime NULL NULL c char 10 40 d datetime NULL NULL e char 240 960 f datetime NULL NULL g char 255 1020 h datetime NULL NULL 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'); column_name data_type column_length a char 4 b datetime 8 c char 40 d datetime 8 e char 960 f datetime 8 g char 1020 h datetime 8 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; 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; a b c d e f g h i j k l 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 select * from cs2; a b c d e f g h i j k l NULL 2020-10-25 01:02:03 NULL 2020-10-25 01:02:04 NULL 2020-10-25 01:02:05 NULL 2020-10-25 01:02:06 NULL 2020-10-25 01:02:07 NULL 2020-10-25 01:02:08 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'); 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'); ERROR 22007: Incorrect string value: '\xF0\x9F\x98\x8E' for column `test_mcol2000`.`cs3`.`a` at row 1 select * from cs3; a b c d e f g h i j k l 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 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 select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3'; column_name data_type character_maximum_length character_octet_length a tinytext 255 255 b datetime NULL NULL c tinytext 255 255 d datetime NULL NULL e text 65535 65535 f datetime NULL NULL g text 65535 65535 h datetime NULL NULL i mediumtext 16777215 16777215 j datetime NULL NULL k longtext 4294967295 4294967295 l datetime NULL NULL 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'); column_name data_type column_length a text 255 b datetime 8 c text 255 d datetime 8 e text 65535 f datetime 8 g text 65535 h datetime 8 i text 16777215 j datetime 8 k text 16777215 l datetime 8 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; 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; a b c d e f g h i j k l 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 select * from cs2; a b c d e f g h i j k l NULL 2020-10-25 01:02:03 NULL 2020-10-25 01:02:04 NULL 2020-10-25 01:02:05 NULL 2020-10-25 01:02:06 NULL 2020-10-25 01:02:07 NULL 2020-10-25 01:02:08 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'); ERROR 22007: Incorrect string value: '\xF0\x9F\x98\x8E' for column `test_mcol2000`.`cs3`.`a` at row 1 select * from cs3; a b c d e f g h i j k l 1 2020-10-25 01:02:04 ??????? 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 1 2020-10-25 01:02:05 ??????? 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 select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3'; column_name data_type character_maximum_length character_octet_length a tinytext 255 255 b datetime NULL NULL c tinytext 255 255 d datetime NULL NULL e text 65535 65535 f datetime NULL NULL g text 65535 65535 h datetime NULL NULL i mediumtext 16777215 16777215 j datetime NULL NULL k longtext 4294967295 4294967295 l datetime NULL NULL 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'); column_name data_type column_length a text 255 b datetime 8 c text 255 d datetime 8 e text 65535 f datetime 8 g text 65535 h datetime 8 i text 16777215 j datetime 8 k text 16777215 l datetime 8 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; 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; a b c d e f g h i j k l 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 select * from cs2; a b c d e f g h i j k l NULL 2020-10-25 01:02:03 NULL 2020-10-25 01:02:04 NULL 2020-10-25 01:02:05 NULL 2020-10-25 01:02:06 NULL 2020-10-25 01:02:07 NULL 2020-10-25 01:02:08 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'); ERROR 22007: Incorrect string value: '\xF0\x9F\x98\x8E' for column `test_mcol2000`.`cs3`.`a` at row 1 select * from cs3; a b c d e f g h i j k l 1 2020-10-25 01:02:04 ??????? 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 1 2020-10-25 01:02:05 ??????? 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 select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3'; column_name data_type character_maximum_length character_octet_length a tinytext 255 255 b datetime NULL NULL c tinytext 255 255 d datetime NULL NULL e text 65535 65535 f datetime NULL NULL g text 65535 65535 h datetime NULL NULL i mediumtext 16777215 16777215 j datetime NULL NULL k longtext 4294967295 4294967295 l datetime NULL NULL 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'); column_name data_type column_length a text 255 b datetime 8 c text 255 d datetime 8 e text 65535 f datetime 8 g text 65535 h datetime 8 i text 16777215 j datetime 8 k text 16777215 l datetime 8 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; 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; a b c d e f g h i j k l 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 select * from cs2; a b c d e f g h i j k l NULL 2020-10-25 01:02:03 NULL 2020-10-25 01:02:04 NULL 2020-10-25 01:02:05 NULL 2020-10-25 01:02:06 NULL 2020-10-25 01:02:07 NULL 2020-10-25 01:02:08 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; a b c d e f g h i j k l 1 2020-10-25 01:02:04 ??????? 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 1 2020-10-25 01:02:05 ??????? 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 ? 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 column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3'; column_name data_type character_maximum_length character_octet_length a tinytext 255 255 b datetime NULL NULL c tinytext 255 255 d datetime NULL NULL e text 65535 65535 f datetime NULL NULL g text 65535 65535 h datetime NULL NULL i mediumtext 16777215 16777215 j datetime NULL NULL k longtext 4294967295 4294967295 l datetime NULL NULL 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'); column_name data_type column_length a text 255 b datetime 8 c text 255 d datetime 8 e text 65535 f datetime 8 g text 65535 h datetime 8 i text 16777215 j datetime 8 k text 16777215 l datetime 8 drop table cs1; drop table cs2; drop table cs3; drop table i1; drop table i2; drop database test_mcol2000;