mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-04-26 11:48:52 +03:00
64f1d541d0
feat(charset)!: utf8 is a new charset default and utf8_general_ci is a new collation default in the engine configuration file shipped --------- Co-authored-by: Leonid Fedorov <leonid.fedorov@mariadb.com> Co-authored-by: mariadb-DanielLee <daniel.lee@mariadb.com>
970 lines
49 KiB
Plaintext
970 lines
49 KiB
Plaintext
#
|
|
#Test column length with different charsets
|
|
#
|
|
|
|
-- source ../include/have_columnstore.inc
|
|
-- source include/have_innodb.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,
|
|
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;
|
|
|
|
--replace_regex /(COLLATE utf8mb3_general_ci )// /(COLLATE utf8mb4_general_ci )// /( COLLATE=koi8r_general_ci)//
|
|
show create table orig;
|
|
--replace_regex /(COLLATE utf8mb3_general_ci )// /(COLLATE utf8mb4_general_ci )// /( COLLATE=koi8r_general_ci)//
|
|
show create table copy1;
|
|
--replace_regex /(COLLATE utf8mb3_general_ci )// /(COLLATE utf8mb4_general_ci )// /( COLLATE=koi8r_general_ci)//
|
|
show create table copy2;
|
|
--replace_regex /(COLLATE utf8mb3_general_ci )// /(COLLATE utf8mb4_general_ci )// /( COLLATE=koi8r_general_ci)// /( COLLATE=latin1_swedish_ci)//
|
|
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;
|