path: root/mysql-test/main/ctype_utf32.test

-- source include/have_utf32.inc
-- source include/have_utf8mb4.inc

--disable_service_connection
SET TIME_ZONE = '+03:00';

--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

--echo #
--echo # Start of 5.5 tests
--echo #

SET NAMES latin1;
SET character_set_connection=utf32;
select hex('a'), hex('a ');
-- source include/endspace.inc

#
# Check that incomplete utf32 characters in HEX notation
# are left-padded with zeros
#
select hex(_utf32 0x44);
select hex(_utf32 0x3344);
select hex(_utf32 0x103344);

select hex(_utf32 X'44');
select hex(_utf32 X'3344');
select hex(_utf32 X'103344');


#
# Check that 0x20 is only trimmed when it is 
# a part of real SPACE character, not just a part
# of a multibyte sequence.
# Note, CYRILLIC LETTER ER is used as an example, which
# is stored as 0x0420 in UCS2, thus contains 0x20 in the
# low byte. The second character is THREE-PER-M, U+2004,
# which contains 0x20 in the high byte.
#

CREATE TABLE t1 (word VARCHAR(64), word2 CHAR(64)) CHARACTER SET utf32;
INSERT INTO t1 VALUES (_koi8r 0xF2, _koi8r 0xF2), (X'2004',X'2004');
SELECT hex(word) FROM t1 ORDER BY word;
SELECT hex(word2) FROM t1 ORDER BY word2;
DELETE FROM t1;

#
# Check that real spaces are correctly trimmed.
#

INSERT INTO t1 VALUES
  (X'000004200000002000000020',X'000004200000002000000020'),
  (X'000020040000002000000020',X'000020040000002000000020');
SELECT hex(word) FROM t1 ORDER BY word;
SELECT hex(word2) FROM t1 ORDER BY word2;
DROP TABLE t1;

#
# Check LPAD/RPAD
#
SELECT hex(LPAD(_utf32 X'0420',10,_utf32 X'0421'));
SELECT hex(LPAD(_utf32 X'0420',10,_utf32 X'0000042100000422'));
SELECT hex(LPAD(_utf32 X'0420',10,_utf32 X'000004210000042200000423'));
#enable after fix MDEV-27871
--disable_view_protocol
SELECT hex(LPAD(_utf32 X'000004200000042100000422000004230000042400000425000004260000042700000428000004290000042A0000042B',10,_utf32 X'000004210000042200000423'));
--enable_view_protocol

SELECT hex(RPAD(_utf32 X'0420',10,_utf32 X'0421'));
SELECT hex(RPAD(_utf32 X'0420',10,_utf32 X'0000042100000422'));
SELECT hex(RPAD(_utf32 X'0420',10,_utf32 X'000004210000042200000423'));
#enable after fix MDEV-27871
--disable_view_protocol
SELECT hex(RPAD(_utf32 X'000004200000042100000422000004230000042400000425000004260000042700000428000004290000042A0000042B',10,_utf32 X'000004210000042200000423'));
--enable_view_protocol

CREATE TABLE t1 SELECT 
LPAD(_utf32 X'0420',10,_utf32 X'0421') l,
RPAD(_utf32 X'0420',10,_utf32 X'0421') r;
SHOW CREATE TABLE t1;
select hex(l), hex(r) from t1;
DROP TABLE t1;

create table t1 (f1 char(30));
insert into t1 values ("103000"), ("22720000"), ("3401200"), ("78000");
select lpad(f1, 12, "-o-/") from t1;
drop table t1;

######################################################
#
# Test of like
#

SET NAMES latin1;
SET character_set_connection=utf32;
--source include/ctype_like.inc

SET NAMES utf8;
SET character_set_connection=utf32;
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf32);
INSERT INTO t1 VALUES ('фыва'),('Фыва'),('фЫва'),('фыВа'),('фывА'),('ФЫВА');
INSERT INTO t1 VALUES ('фывапролдж'),('Фывапролдж'),('фЫвапролдж'),('фыВапролдж');
INSERT INTO t1 VALUES ('фывАпролдж'),('фываПролдж'),('фывапРолдж'),('фывапрОлдж');
INSERT INTO t1 VALUES ('фывапроЛдж'),('фывапролДж'),('фывапролдЖ'),('ФЫВАПРОЛДЖ');
SELECT * FROM t1 WHERE a LIKE '%фЫва%' ORDER BY BINARY a;
SELECT * FROM t1 WHERE a LIKE '%фЫв%' ORDER BY BINARY a;
SELECT * FROM t1 WHERE a LIKE 'фЫва%' ORDER BY BINARY a;
SELECT * FROM t1 WHERE a LIKE 'фЫва%' COLLATE utf32_bin ORDER BY BINARY a;
DROP TABLE t1;

CREATE TABLE t1 (word varchar(64) NOT NULL, PRIMARY KEY (word))
ENGINE=MyISAM CHARACTER SET utf32;
INSERT INTO t1 (word) VALUES ("cat");
SELECT * FROM t1 WHERE word LIKE "c%";
SELECT * FROM t1 WHERE word LIKE "ca_";
SELECT * FROM t1 WHERE word LIKE "cat";
SELECT * FROM t1 WHERE word LIKE _utf32 x'0000006300000025'; # "c%"
SELECT * FROM t1 WHERE word LIKE _utf32 x'00000063000000610000005F'; # "ca_"
DROP TABLE t1;


#
# Check that INSERT() works fine. 
# This invokes charpos() function.
#enable after fix MDEV-27871
--disable_view_protocol
select insert(_utf32 0x000000610000006200000063,10,2,_utf32 0x000000640000006500000066);
select insert(_utf32 0x000000610000006200000063,1,2,_utf32 0x000000640000006500000066);
--enable_view_protocol

#######################################################

#
# Bug 1264
#
# Description: 
#
# When using a ucs2 table in MySQL, 
# either with ucs2_general_ci or ucs2_bin collation,
# words are returned in an incorrect order when using ORDER BY
# on an _indexed_ CHAR or VARCHAR column. They are sorted with
# the longest word *first* instead of last. I.E. The word "aardvark"
# is in the results before the word "a".
#
# If there is no index for the column, the problem does not occur.
#
# Interestingly, if there is no second column, the words are returned
# in the correct order. 
#
# According to EXPLAIN, it looks like when the output includes columns that
# are not part of the index sorted on, it does a filesort, which fails. 
# Using a straight index yields correct results.

SET NAMES latin1;

#
# Two fields, index
#

CREATE TABLE t1 (
   word VARCHAR(64),
   bar INT(11) default 0,
   PRIMARY KEY (word))
   ENGINE=MyISAM
   CHARSET utf32
   COLLATE utf32_general_ci ;

INSERT INTO t1 (word) VALUES ("aar");
INSERT INTO t1 (word) VALUES ("a");
INSERT INTO t1 (word) VALUES ("aardvar");
INSERT INTO t1 (word) VALUES ("aardvark");
INSERT INTO t1 (word) VALUES ("aardvara");
INSERT INTO t1 (word) VALUES ("aardvarz");
EXPLAIN SELECT * FROM t1 ORDER BY word;
SELECT * FROM t1 ORDER BY word;
EXPLAIN SELECT word FROM t1 ORDER BY word;
SELECT word FROM t1 ORDER by word;
DROP TABLE t1;


#
# One field, index
# 

CREATE TABLE t1 (
   word VARCHAR(64) ,
   PRIMARY KEY (word))
   ENGINE=MyISAM
   CHARSET utf32
   COLLATE utf32_general_ci;

INSERT INTO t1 (word) VALUES ("aar");
INSERT INTO t1 (word) VALUES ("a");
INSERT INTO t1 (word) VALUES ("aardvar");
INSERT INTO t1 (word) VALUES ("aardvark");
INSERT INTO t1 (word) VALUES ("aardvara");
INSERT INTO t1 (word) VALUES ("aardvarz");
EXPLAIN SELECT * FROM t1 ORDER BY WORD;
SELECT * FROM t1 ORDER BY word;
DROP TABLE t1;


#
# Two fields, no index
#

CREATE TABLE t1 (
   word TEXT,
   bar INT(11) AUTO_INCREMENT,
   PRIMARY KEY (bar))
   ENGINE=MyISAM
   CHARSET utf32
   COLLATE utf32_general_ci ;
INSERT INTO t1 (word) VALUES ("aar");
INSERT INTO t1 (word) VALUES ("a" );
INSERT INTO t1 (word) VALUES ("aardvar");
INSERT INTO t1 (word) VALUES ("aardvark");
INSERT INTO t1 (word) VALUES ("aardvara");
INSERT INTO t1 (word) VALUES ("aardvarz");
EXPLAIN SELECT * FROM t1 ORDER BY word;
SELECT * FROM t1 ORDER BY word;
EXPLAIN SELECT word FROM t1 ORDER BY word;
SELECT word FROM t1 ORDER BY word;
DROP TABLE t1;

#
# END OF Bug 1264 test
#
########################################################


#
# Check alignment for from-binary-conversion with CAST and CONVERT
#
SELECT hex(cast(0xAA as char character set utf32));
SELECT hex(convert(0xAA using utf32));

#
# Check alignment for string types
#
CREATE TABLE t1 (a char(10) character set utf32);
INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111);
SELECT HEX(a) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a varchar(10) character set utf32);
INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111);
SELECT HEX(a) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a text character set utf32);
INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111);
SELECT HEX(a) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a mediumtext character set utf32);
INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111);
SELECT HEX(a) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a longtext character set utf32);
INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111);
SELECT HEX(a) FROM t1;
DROP TABLE t1;

##
## Bug #5024 Server crashes with queries on fields
##  with certain charset/collation settings
##
#
#create table t1 (s1 char character set `ucs2` collate `ucs2_czech_ci`);
#insert into t1 values ('0'),('1'),('2'),('a'),('b'),('c');
#select s1 from t1 where s1 > 'a' order by s1;
#drop table t1;

#
# Bug #5081 : UCS2 fields are filled with '0x2020'
# after extending field length
#
create table t1(a char(1)) default charset utf32;
insert into t1 values ('a'),('b'),('c');
alter table t1 modify a char(5);
select a, hex(a) from t1;
drop table t1;

#
# Check prepare statement from an UTF32 string
#
set @ivar= 1234;
set @str1 = 'select ?';
set @str2 = convert(@str1 using utf32);
prepare stmt1 from @str2;
execute stmt1 using @ivar;

#
# Check that utf32 works with ENUM and SET type
#
set names utf8;
create table t1 (a enum('x','y','z') character set utf32);
show create table t1;
insert into t1 values ('x');
insert into t1 values ('y');
insert into t1 values ('z');
select a, hex(a) from t1 order by a;
alter table t1 change a a enum('x','y','z','d','e','ä','ö','ü') character set utf32;
show create table t1;
insert into t1 values ('D');
insert into t1 values ('E ');
insert into t1 values ('ä');
insert into t1 values ('ö');
insert into t1 values ('ü');
select a, hex(a) from t1 order by a;
drop table t1;

create table t1 (a set ('x','y','z','ä','ö','ü') character set utf32);
show create table t1;
insert into t1 values ('x');
insert into t1 values ('y');
insert into t1 values ('z');
insert into t1 values ('x,y');
insert into t1 values ('x,y,z,ä,ö,ü');
select a, hex(a) from t1 order by a;
drop table t1;

#
# Bug#7302 UCS2 data in ENUM fields get truncated when new column is added
#
create table t1(a enum('a','b','c')) default character set utf32;
insert into t1 values('a'),('b'),('c');
alter table t1 add b char(1);
show warnings;
select * from t1 order by a;
drop table t1;

SET NAMES latin1;
SET collation_connection='utf32_general_ci';
-- source include/ctype_filesort.inc
-- source include/ctype_like_escape.inc
SET NAMES latin1;
SET collation_connection='utf32_bin';
-- source include/ctype_filesort.inc
-- source include/ctype_filesort2.inc
-- source include/ctype_like_escape.inc

#
# Bug#10344 Some string functions fail for UCS2
#
select hex(substr(_utf32 0x000000e4000000e500000068,1));
select hex(substr(_utf32 0x000000e4000000e500000068,2));
select hex(substr(_utf32 0x000000e4000000e500000068,3));
select hex(substr(_utf32 0x000000e4000000e500000068,-1));
select hex(substr(_utf32 0x000000e4000000e500000068,-2));
select hex(substr(_utf32 0x000000e4000000e500000068,-3));

#SET NAMES latin1;
#
# Bug#8235
#
# This bug also helped to find another problem that
# INSERT of a UCS2 string containing a negative number
# into a unsigned int column didn't produce warnings.
# This test covers both problems.
#
#SET collation_connection='ucs2_swedish_ci';
#CREATE TABLE t1 (Field1 int(10) default '0');
## no warnings, negative numbers are allowed
#INSERT INTO t1 VALUES ('-1');
#SELECT * FROM t1;
#DROP TABLE t1;
#CREATE TABLE t1 (Field1 int(10) unsigned default '0');
## this should generate a "Data truncated" warning
#INSERT INTO t1 VALUES ('-1');
#DROP TABLE t1;
#SET NAMES latin1;

#
##
## Bug#18691 Converting number to UNICODE string returns invalid result
##
#SELECT CONVERT(103, CHAR(50) UNICODE);
#SELECT CONVERT(103.0, CHAR(50) UNICODE);
#SELECT CONVERT(-103, CHAR(50) UNICODE);
#SELECT CONVERT(-103.0, CHAR(50) UNICODE);

#
# Bug#9557 MyISAM utf8 table crash
#
CREATE TABLE t1 (
  a varchar(250) NOT NULL default '',
  KEY a (a)
) ENGINE=MyISAM DEFAULT CHARSET=utf32 COLLATE utf32_general_ci;
insert into t1 values (0x803d);
insert into t1 values (0x005b);
select hex(a) from t1;
drop table t1;

##
## Bug #14583 Bug on query using a LIKE on indexed field with ucs2_bin collation
##
#--disable_warnings
#create table t1(f1 varchar(5) CHARACTER SET utf32 COLLATE utf32_bin NOT NULL) engine=InnoDB;
#--enable_warnings
#insert into t1 values('a');
#create index t1f1 on t1(f1);
#select f1 from t1 where f1 like 'a%';
#drop table t1;

#
# Bug#9442 Set parameter make query fail if column character set is UCS2
#
create table t1 (utext varchar(20) character set utf32);
insert into t1 values ("lily");
insert into t1 values ("river");
prepare stmt from 'select utext from t1 where utext like ?';
set @param1='%%';
execute stmt using @param1;
execute stmt using @param1;
select utext from t1 where utext like '%%';
drop table t1;
deallocate prepare stmt;

#
# Bug#22052 Trailing spaces are not removed from UNICODE fields in an index
#
create table t1 (
  a char(10) character set utf32 not null, 
  index a (a)
) engine=myisam;
insert into t1 values (repeat(0x0000201f, 10));
insert into t1 values (repeat(0x00002020, 10));
insert into t1 values (repeat(0x00002021, 10));
# make sure "index read" is used
explain select hex(a) from t1 order by a;
select hex(a) from t1 order by a;
alter table t1 drop index a;
select hex(a) from t1 order by a;
drop table t1;

#
# Bug #20076: server crashes for a query with GROUP BY if MIN/MAX aggregation
#             over a 'ucs2' field uses a temporary table 
#
#CREATE TABLE t1 (id int, s char(5) CHARACTER SET ucs2 COLLATE ucs2_unicode_ci);
#INSERT INTO t1 VALUES (1, 'ZZZZZ'), (1, 'ZZZ'), (2, 'ZZZ'), (2, 'ZZZZZ');
#SELECT id, MIN(s) FROM t1 GROUP BY id;
#DROP TABLE t1;

##
## Bug #20536: md5() with GROUP BY and UCS2 return different results on myisam/innodb
##
#
#--disable_warnings
#drop table if exists bug20536;
#--enable_warnings
#
#set names latin1;
#create table bug20536 (id bigint not null auto_increment primary key, name
#varchar(255) character set ucs2 not null);
#insert into `bug20536` (`id`,`name`) values (1, _latin1 x'7465737431'), (2, "'test\\_2'");
#select md5(name) from bug20536;
#select sha1(name) from bug20536;
#select make_set(3, name, upper(name)) from bug20536;
#select export_set(5, name, upper(name)) from bug20536;
#select export_set(5, name, upper(name), ",", 5) from bug20536;

#
# Bug #20108: corrupted default enum value for a ucs2 field              
#

CREATE TABLE t1 (
  status enum('active','passive') character set utf32 collate utf32_general_ci 
    NOT NULL default 'passive'
);
SHOW CREATE TABLE t1;
ALTER TABLE t1 ADD a int NOT NULL AFTER status; 
SHOW CREATE TABLE t1;
DROP TABLE t1;

#CREATE TABLE t2 (
#  status enum('active','passive') collate ucs2_turkish_ci 
#    NOT NULL default 'passive'
#);
#SHOW CREATE TABLE t2;
#ALTER TABLE t2 ADD a int NOT NULL AFTER status; 
#DROP TABLE t2;


## Some broken functions:  add these tests just to document current behavior.
#
## PASSWORD and OLD_PASSWORD don't work with UCS2 strings, but to fix it would
## not be backwards compatible in all cases, so it's best to leave it alone
#select password(name) from bug20536;
#select old_password(name) from bug20536;
#
## Disable test case as encrypt relies on 'crypt' function.
## "decrypt" is noramlly tested in func_crypt.test which have a
## "have_crypt.inc" test
#--disable_parsing
## ENCRYPT relies on OS function crypt() which takes a NUL-terminated string; it
## doesn't return good results for strings with embedded 0 bytes.  It won't be
## fixed unless we choose to re-implement the crypt() function ourselves to take
## an extra size_t string_length argument.
#select encrypt(name, 'SALT') from bug20536;
#--enable_parsing
#
## QUOTE doesn't work with UCS2 data.  It would require a total rewrite
## of Item_func_quote::val_str(), which isn't worthwhile until UCS2 is
## supported fully as a client character set.
#select quote(name) from bug20536;
#
#drop table bug20536;
#
--echo End of 4.1 tests


#
# Conversion from an UTF32 string to a decimal column
#
CREATE TABLE t1 (a varchar(64) character set utf32, b decimal(10,3));
INSERT INTO t1 VALUES ("1.1", 0), ("2.1", 0);
update t1 set b=a;
SELECT *, hex(a) FROM t1;
DROP TABLE t1;

#
# Bug#9442 Set parameter make query fail if column character set is UCS2
#
create table t1 (utext varchar(20) character set utf32);
insert into t1 values ("lily");
insert into t1 values ("river");
prepare stmt from 'select utext from t1 where utext like ?';
set @param1='%%';
execute stmt using @param1;
execute stmt using @param1;
select utext from t1 where utext like '%%';
drop table t1;
deallocate prepare stmt;

#
# Bug#22638 SOUNDEX broken for international characters
#
set names latin1;
set character_set_connection=utf32;
select soundex(''),soundex('he'),soundex('hello all folks'),soundex('#3556 in bugdb');
select hex(soundex('')),hex(soundex('he')),hex(soundex('hello all folks')),hex(soundex('#3556 in bugdb'));
select 'mood' sounds like 'mud';
# Cyrillic A, BE, VE
select hex(soundex(_utf32 0x000004100000041100000412));
# Make sure that "U+00BF INVERTED QUESTION MARK" is not considered as letter
select hex(soundex(_utf32 0x000000BF000000C0));
set names latin1;

#
# Bug #14290: character_maximum_length for text fields
#
create table t1(a blob, b text charset utf32);
select data_type, character_octet_length, character_maximum_length
  from information_schema.columns where table_name='t1';
drop table t1;


set names latin1;
set collation_connection=utf32_general_ci;
#
# Testing instr()
#
select position('bb' in 'abba');

#
# Testing hash_sort()
#
create table t1 (a varchar(10) character set utf32) engine=heap;
insert into t1 values ('a'),('A'),('b'),('B');
select * from t1 where a='a' order by binary a;
select hex(min(binary a)),count(*) from t1 group by a;
drop table t1;

#
# Testing numchars()
#
select char_length('abcd'), octet_length('abcd');

#
# Testing charpos()
#
select left('abcd',2);

#
# Testing well_formed_length()
#
create table t1 (a varchar(10) character set utf32);
insert into t1 values (_utf32 0x0010FFFF);
--error ER_INVALID_CHARACTER_STRING
insert into t1 values (_utf32 0x00110000);
--error ER_INVALID_CHARACTER_STRING
insert into t1 values (_utf32 0x00110101);
--error ER_INVALID_CHARACTER_STRING
insert into t1 values (_utf32 0x01000101);
--error ER_INVALID_CHARACTER_STRING
insert into t1 values (_utf32 0x11000101);
select hex(a) from t1;
drop table t1;

#
# Bug#32914 Character sets: illegal characters in utf8 and utf32 columns
#
create table t1 (utf32 varchar(2) character set utf32);
--echo Wrong character with pad
insert ignore into t1 values (0x110000);
--echo Wrong chsaracter without pad
insert ignore into t1 values (0x00110000);
--echo Wrong character with pad followed by another wrong character
insert ignore into t1 values (0x11000000110000);
--echo Good character with pad followed by bad character
insert ignore into t1 values (0x10000000110000);
--echo Good character without pad followed by bad character
insert ignore into t1 values (0x0010000000110000);
--echo Wrong character with the second byte higher than 0x10
insert ignore into t1 values (0x00800037);
--echo Wrong character with pad with the second byte higher than 0x10
insert ignore into t1 values (0x00800037);
drop table t1;

#
# Bug#32394 Character sets: crash if comparison with 0xfffd
#
select _utf32'a' collate utf32_general_ci = 0xfffd;
select hex(concat(_utf32 0x0410 collate utf32_general_ci, 0x61));
create table t1 (s1 varchar(5) character set utf32);
insert into t1 values (0xfffd);
select case when s1 = 0xfffd then 1 else 0 end from t1;
select hex(s1) from t1 where s1 = 0xfffd;
drop table t1;

#
# Testing lengthsp()
#
create table t1 (a char(10)) character set utf32;
insert into t1 values ('a   ');
select hex(a) from t1;
drop table t1;

#
# Testing caseup() and casedn()
#
select upper('abcd'), lower('ABCD');

#
# TODO: str_to_datetime() is broken and doesn't work with ucs2 and utf32
# Testing snprintf()
#
#create table t1 (a date);
#insert into t1 values ('2007-09-16');
#select * from t1;
#drop table t1;

#
# Testing l10tostr
# !!! Not used in the code

#
# Testing ll10tostr
#
create table t1 (a varchar(10) character set utf32);
insert into t1 values (123456);
select a, hex(a) from t1;
drop table t1;

#
# Testing fill
# SOUNDEX fills strings with DIGIT ZERO up to four characters
select hex(soundex('a'));

#
# Testing strntol
# !!! Not used in the code

#
# Testing strntoul
#
create table t1 (a enum ('a','b','c')) character set utf32;
insert into t1 values ('1');
select * from t1;
drop table t1;

#
# Testing strntoll and strntoull
#
set names latin1;
select hex(conv(convert('123' using utf32), -10, 16));
select hex(conv(convert('123' using utf32), 10, 16));

#
# Testing strntod
#
set names latin1;
set character_set_connection=utf32;
select 1.1 + '1.2';
select 1.1 + '1.2xxx';

# Testing strntoll10_utf32
# Testing strtoll10
select left('aaa','1');
--source include/ctype_strtoll10.inc

#
# Testing strntoull10rnd
#
create table t1 (a int);
insert into t1 values ('-1234.1e2');
insert ignore into t1 values ('-1234.1e2xxxx');
insert into t1 values ('-1234.1e2    ');
select * from t1;
drop table t1;

#
# Testing scan
#
create table t1 (a int);
insert into t1 values ('1 ');
insert ignore into t1 values ('1 x');
select * from t1;
drop table t1;

#
# Testing auto-conversion to TEXT
#
SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
create table t1 (a varchar(17000) character set utf32);
show create table t1;
drop table t1;

#
# Testing that maximim possible key length is 1332 bytes
#
create table t1 (a varchar(250) character set utf32 primary key);
show create table t1;
drop table t1;
--error ER_TOO_LONG_KEY
create table t1 (a varchar(334) character set utf32 primary key);

#
# Testing mi_check with long key values
#
create table t1 (a varchar(333) character set utf32, key(a));
insert into t1 values (repeat('a',333)), (repeat('b',333));
flush tables;
check table t1;
drop table t1;

#
# Test how character set works with date/time
#
SET collation_connection=utf32_general_ci;
--source include/ctype_datetime.inc
SET NAMES latin1;

#
# Test basic regex functionality
#
set collation_connection=utf32_general_ci;
--source include/ctype_regex.inc
set names latin1;


# TODO: add tests for all engines

#
# Bug #36418 Character sets: crash if char(256 using utf32)
#
select hex(char(0x01 using utf32));
select hex(char(0x0102 using utf32));
select hex(char(0x010203 using utf32));
select hex(char(0x01020304 using utf32));
create table t1 (s1 varchar(1) character set utf32, s2 text character set utf32);
create index i on t1 (s1);
insert into t1 values (char(256 using utf32), char(256 using utf32));
select hex(s1), hex(s2) from t1;
drop table t1;


#
# Bug#33073 Character sets: ordering fails with utf32
#
SET collation_connection=utf32_general_ci;
CREATE TABLE t1 AS SELECT repeat('a',2) as s1 LIMIT 0;
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES ('ab'),('AE'),('ab'),('AE');
SELECT * FROM t1 ORDER BY s1;
SET max_sort_length=8;
SELECT * FROM t1 ORDER BY s1;
DROP TABLE t1;
SET max_sort_length=DEFAULT;
SET NAMES latin1;

--echo #
--echo # Bug#52520 Difference in tinytext utf column metadata
--echo #
CREATE TABLE t1 (
  s1 TINYTEXT CHARACTER SET utf32,
  s2 TEXT CHARACTER SET utf32,
  s3 MEDIUMTEXT CHARACTER SET utf32,
  s4 LONGTEXT CHARACTER SET utf32
);
--disable_view_protocol
--enable_metadata
SET NAMES utf8mb4, @@character_set_results=NULL;
SELECT *, HEX(s1) FROM t1;
SET NAMES latin1;
SELECT *, HEX(s1) FROM t1;
SET NAMES utf8mb4;
SELECT *, HEX(s1) FROM t1;
--disable_metadata
--enable_view_protocol
CREATE TABLE t2 AS SELECT CONCAT(s1) FROM t1;
SHOW CREATE TABLE t2;
DROP TABLE t1, t2;


--echo #
--echo # Bug#45263 utf32_general_ci, bad effects around CREATE TABLE AS SELECT
--echo #
SET collation_connection=utf32_general_ci;
CREATE TABLE t1 AS SELECT HEX(0x00) AS my_col;
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Bug#32859 Character sets: no warning with non-fitting chariot wheel
--echo #
CREATE TABLE t1 (utf32 CHAR(5) CHARACTER SET utf32, latin1 CHAR(5) CHARACTER SET latin1);
INSERT INTO t1 (utf32) VALUES (0xc581);
UPDATE IGNORE t1 SET latin1 = utf32;
DELETE FROM t1;
INSERT INTO t1 (utf32) VALUES (0x100cc);
UPDATE IGNORE t1 SET latin1 = utf32;
DROP TABLE t1;

--echo #
--echo # Bug#55912 FORMAT with locale set fails for numbers < 1000
--echo #
SET collation_connection=utf32_general_ci;
CREATE TABLE t1 AS SELECT format(123,2,'no_NO');
SHOW CREATE TABLE t1;
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Bug#42511 mysqld: ctype-ucs2.c:2044: my_strnncollsp_utf32: Assertion (tlen % 4) == 0' faied
--echo #
CREATE TABLE t1 (
 b char(250) CHARACTER SET utf32,
 key (b)
) ENGINE=MYISAM;
INSERT INTO t1 VALUES ('d'),('f');
SELECT * FROM t1 WHERE b BETWEEN 'a' AND 'z';
DROP TABLE t1;

--echo #
--echo # Bug#11753363 (Bug#44793) Character sets: case clause, ucs2 or utf32, failure
--echo #
SELECT CASE _latin1'a' WHEN _utf32'a' THEN 'A' END;
SELECT CASE _utf32'a' WHEN _latin1'a' THEN 'A' END;
CREATE TABLE t1 (s1 CHAR(5) CHARACTER SET utf32);
INSERT INTO t1 VALUES ('a');
SELECT CASE s1 WHEN 'a' THEN 'b' ELSE 'c' END FROM t1;
DROP TABLE t1;

--echo #
--echo # Bug#11750518 41090: ORDER BY TRUNCATES GROUP_CONCAT RESULT
--echo #

SET NAMES utf8, @@character_set_connection=utf32;
SET STATEMENT group_concat_max_len=1024 FOR
SELECT id, CHAR_LENGTH(GROUP_CONCAT(body)) AS l
FROM (SELECT 'a' AS id, REPEAT('foo bar', 100) AS body
UNION ALL
SELECT 'a' AS id, REPEAT('bla bla', 100) AS body) t1
GROUP BY id
ORDER BY l DESC;

--echo #
--echo # incorrect charset for val_str_ascii
--echo #
#enable after fix MDEV-27871
--disable_view_protocol
SELECT '2010-10-10 10:10:10' + INTERVAL GeometryType(GeomFromText('POINT(1 1)')) hour_second;
--enable_view_protocol

--echo #
--echo # MDEV-5745 analyze MySQL fix for bug#12368495
--echo #
SELECT CHAR_LENGTH(TRIM(LEADING 0x0000000000 FROM _utf32 0x00000061));
SELECT CHAR_LENGTH(TRIM(LEADING 0x0001 FROM _utf32 0x00000061));
SELECT CHAR_LENGTH(TRIM(LEADING 0x00 FROM _utf32 0x00000061));

SELECT CHAR_LENGTH(TRIM(TRAILING 0x0000000000 FROM _utf32 0x00000061));
SELECT CHAR_LENGTH(TRIM(TRAILING 0x0001 FROM _utf32 0x00000061));
SELECT CHAR_LENGTH(TRIM(TRAILING 0x61 FROM _utf32 0x00000061));

SELECT CHAR_LENGTH(TRIM(BOTH 0x0000000000 FROM _utf32 0x00000061));
SELECT CHAR_LENGTH(TRIM(BOTH 0x0001 FROM _utf32 0x00000061));
SELECT CHAR_LENGTH(TRIM(BOTH 0x61 FROM _utf32 0x00000061));
SELECT CHAR_LENGTH(TRIM(BOTH 0x00 FROM _utf32 0x00000061));

#
# potential signedness issue
#
select hex(lower(cast(0xffff0000 as char character set utf32))) as c;

--echo #
--echo # MDEV-11685: sql_mode can't be set with non-ascii connection charset
--echo #
SET character_set_connection=utf32;
SET sql_mode='NO_ENGINE_SUBSTITUTION';
SELECT @@sql_mode;
SET sql_mode=DEFAULT;
SET NAMES utf8;

--echo #
--echo # MDEV-13530 VARBINARY doesn't convert to to BLOB for sizes 65533, 65534 and 65535
--echo #

set sql_mode='';
CREATE TABLE t1 (c1 VARCHAR(16383) CHARACTER SET utf32);
DESCRIBE t1;
DROP TABLE t1;

CREATE TABLE t1 (c1 VARCHAR(16384) CHARACTER SET utf32);
DESCRIBE t1;
DROP TABLE t1;
set sql_mode=default;


--echo #
--echo # End of 5.5 tests
--echo #


--echo #
--echo # Start of 5.6 tests
--echo #

--echo #
--echo # WL#3664 WEIGHT_STRING
--echo #

set collation_connection=utf32_general_ci;
--source include/weight_string.inc
--source include/weight_string_euro.inc
select hex(weight_string(_utf32 0x10000));
select hex(weight_string(_utf32 0x10001));
--source include/weight_string_l1.inc

set collation_connection=utf32_bin;
--source include/weight_string.inc
--source include/weight_string_euro.inc
--source include/weight_string_l1.inc

--echo #
--echo # End of 5.6 tests
--echo #


--echo #
--echo # Start of 10.0 tests
--echo #

--echo #
--echo # MDEV-6661 PI() does not work well in UCS2/UTF16/UTF32 context
--echo #
SELECT CONCAT(CONVERT('pi=' USING utf32),PI()) AS PI;

--echo #
--echo # MDEV-6666 Malformed result for CONCAT(utf8_column, binary_string)
--echo #

SET NAMES utf8mb4;
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf32);
INSERT INTO t1 VALUES ('a');
--error ER_INVALID_CHARACTER_STRING
SELECT CONCAT(a,0x20FFFF) FROM t1;
SELECT CONCAT(a,0x010000) FROM t1;
SELECT CONCAT(a,0x00FF) FROM t1;
DROP TABLE t1;
--error ER_INVALID_CHARACTER_STRING
SELECT CONCAT(_utf32'a' COLLATE utf32_unicode_ci, _binary 0x20FFFF);
PREPARE stmt FROM "SELECT CONCAT(_utf32'a' COLLATE utf32_unicode_ci, ?)";
SET @arg00=_binary 0x20FFFF;
--error ER_INVALID_CHARACTER_STRING
EXECUTE stmt USING @arg00;
SET @arg00=_binary 0x010000;
EXECUTE stmt USING @arg00;
SET @arg00=_binary 0x00FF;
EXECUTE stmt USING @arg00;
DEALLOCATE PREPARE stmt;


#
# MDEV-13118 Wrong results with LOWER and UPPER and subquery
#
SET NAMEs utf8, character_set_connection=utf32;
--source include/ctype_mdev13118.inc


--echo #
--echo # End of 10.0 tests
--echo #

--echo #
--echo # Start of 10.1 tests
--echo #

--echo #
--echo # MDEV-8419 utf32: compare broken bytes as "greater than any non-broken character"
--echo #
# Make sure that all non-BMP characters are compared as equal
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf32, KEY(a));
INSERT INTO t1 VALUES (0x10000),(0x10001),(0x10002);
SELECT COUNT(DISTINCT a) FROM t1;
DROP TABLE t1;
SELECT _utf32 0x10001=_utf32 0x10002;

--echo #
--echo # MDEV-9178 Wrong result for CAST(CONVERT('1IJ3' USING ucs2) AS SIGNED)
--echo #
SET NAMES utf8;
SELECT CAST(CONVERT('1IJ3' USING utf32) AS SIGNED);

--echo #
--echo # End of 10.1 tests
--echo #

--echo #
--echo # Start of 10.2 tests
--echo #

--echo #
--echo # MDEV-9711 NO PAD Collatons
--echo #
SET character_set_connection=utf32;
let $coll='utf32_general_nopad_ci';
let $coll_pad='utf32_general_ci';
--source include/ctype_pad_all_engines.inc

let $coll='utf32_nopad_bin';
let $coll_pad='utf32_bin';
--source include/ctype_pad_all_engines.inc


--echo #
--echo # MDEV-22111 ERROR 1064 & 1033 and SIGSEGV on CREATE TABLE w/ various charsets on 10.4/5 optimized builds | Assertion `(uint) (table_check_constraints - share->check_constraints) == (uint) (share->table_check_constraints - share->field_check_constraints)' failed
--echo # 10.2 tests
--echo #

SET NAMES utf8, COLLATION_CONNECTION=utf32_bin;
CREATE TABLE t1(c1 ENUM('a','b','ac') CHARACTER SET 'Binary',c2 JSON,c3 INT);
SHOW CREATE TABLE t1;
INSERT INTO t1 (c1) VALUES (1),(2),(3);
SELECT HEX(c1) FROM t1 ORDER BY c1;
DROP TABLE t1;


--echo #
--echo # MDEV-23210 Assertion `(length % 4) == 0' failed in my_lengthsp_utf32 on ALTER TABLE, SELECT and INSERT
--echo #

CREATE TABLE t1 (a CHAR(1));
SET COLLATION_CONNECTION=utf32_general_ci, CHARACTER_SET_CLIENT=binary;
--error ER_DUPLICATED_VALUE_IN_TYPE
ALTER TABLE t1 CHANGE a a ENUM('a','a') CHARACTER SET utf32;
--error ER_INVALID_CHARACTER_STRING
ALTER TABLE t1 CHANGE a a ENUM('aaa') CHARACTER SET utf32;
ALTER TABLE t1 CHANGE a a ENUM('aa') CHARACTER SET utf32;
SHOW CREATE TABLE t1;
ALTER TABLE t1 CHANGE a a ENUM('a','b') CHARACTER SET utf32;
SHOW CREATE TABLE t1;
DROP TABLE t1;
SET NAMES utf8;


--echo #
--echo # MDEV-28078 Garbage on multiple equal ENUMs with tricky character sets
--echo #

CREATE TABLE t1 (
  c1 ENUM ('a','b') CHARACTER SET utf32 DEFAULT 'a',
  c2 ENUM ('a','b') CHARACTER SET utf32 DEFAULT 'a'
);
SHOW CREATE TABLE t1;
DROP TABLE t1;

CREATE TABLE t1 (
  c1 ENUM ('00000061','00000062') DEFAULT '00000061' COLLATE latin1_bin,
  c2 ENUM ('a','b') DEFAULT 'a' COLLATE utf32_general_ci
);
SHOW CREATE TABLE t1;
DROP TABLE t1;

--enable_service_connection
--echo #
--echo # End of 10.2 tests
--echo #