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

-- source include/have_ucs2.inc
-- source include/have_utf16.inc

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

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

set names utf8;
set collation_connection=utf16_unicode_ci;
select hex('a'), hex('a ');
-- source include/endspace.inc

#
# Bug #6787 LIKE not working properly with _ and utf8 data
#
select 'c' like '\_' as want0; 

#
# Bug #5679 utf8_unicode_ci LIKE--trailing % doesn't equal zero characters
#
CREATE TABLE t (
  c char(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARACTER SET utf16 COLLATE utf16_unicode_ci;
INSERT INTO t VALUES ('a'),('ab'),('aba');
ALTER TABLE t ADD INDEX (c);
SELECT c FROM t WHERE c LIKE 'a%';
DROP TABLE t;


create table t1 (c1 char(10) character set utf16 collate utf16_bin);

--source include/ctype_unicode_latin.inc

select group_concat(c1 order by binary c1 separator '') from t1 group by c1 collate utf16_unicode_ci;
select group_concat(c1 order by binary c1 separator '') from t1 group by c1 collate utf16_icelandic_ci;
select group_concat(c1 order by binary c1 separator '') from t1 group by c1 collate utf16_latvian_ci;
select group_concat(c1 order by binary c1 separator '') from t1 group by c1 collate utf16_romanian_ci;
select group_concat(c1 order by binary c1 separator '') from t1 group by c1 collate utf16_slovenian_ci;
select group_concat(c1 order by binary c1 separator '') from t1 group by c1 collate utf16_polish_ci;
select group_concat(c1 order by binary c1 separator '') from t1 group by c1 collate utf16_estonian_ci;
select group_concat(c1 order by binary c1 separator '') from t1 group by c1 collate utf16_spanish_ci;
select group_concat(c1 order by binary c1 separator '') from t1 group by c1 collate utf16_swedish_ci;
select group_concat(c1 order by binary c1 separator '') from t1 group by c1 collate utf16_turkish_ci;
select group_concat(c1 order by binary c1 separator '') from t1 group by c1 collate utf16_czech_ci;
select group_concat(c1 order by binary c1 separator '') from t1 group by c1 collate utf16_danish_ci;
select group_concat(c1 order by binary c1 separator '') from t1 group by c1 collate utf16_lithuanian_ci;
select group_concat(c1 order by binary c1 separator '') from t1 group by c1 collate utf16_slovak_ci;
select group_concat(c1 order by binary c1 separator '') from t1 group by c1 collate utf16_spanish2_ci;
select group_concat(c1 order by binary c1 separator '') from t1 group by c1 collate utf16_roman_ci;
select group_concat(c1 order by binary c1 separator '') from t1 group by c1 collate utf16_esperanto_ci;
select group_concat(c1 order by binary c1 separator '') from t1 group by c1 collate utf16_hungarian_ci;
select group_concat(c1 order by binary c1 separator '') from t1 group by c1 collate utf16_croatian_mysql561_ci;
select group_concat(c1 order by binary c1 separator '') from t1 group by c1 collate utf16_croatian_ci;
select group_concat(c1 order by binary c1 separator '') from t1 group by c1 collate utf16_german2_ci;
select group_concat(c1 order by binary c1 separator '') from t1 group by c1 collate utf16_unicode_520_ci;
select group_concat(c1 order by binary c1 separator '') from t1 group by c1 collate utf16_vietnamese_ci;

drop table t1;

#
# Bug#5324
#
SET NAMES utf8;

CREATE TABLE t1 (c varchar(200) CHARACTER SET utf16 COLLATE utf16_general_ci NOT NULL, INDEX (c));
INSERT INTO t1 VALUES (0x039C03C903B403B11F770308);
#Check one row
SELECT * FROM t1 WHERE c LIKE _utf16 0x039C0025 COLLATE utf16_general_ci;
INSERT INTO t1 VALUES (0x039C03C903B4);
#Check two rows
SELECT * FROM t1 WHERE c LIKE _utf16 0x039C0025
COLLATE utf16_general_ci ORDER BY c;
DROP TABLE t1;

CREATE TABLE t1 (c varchar(200) CHARACTER SET utf16 COLLATE utf16_unicode_ci NOT NULL, INDEX (c));
INSERT INTO t1 VALUES (0x039C03C903B403B11F770308);
#Check one row
SELECT * FROM t1 WHERE c LIKE _utf16 0x039C0025 COLLATE utf16_unicode_ci;
INSERT INTO t1 VALUES (0x039C03C903B4);
#Check two rows
SELECT * FROM t1 WHERE c LIKE _utf16 0x039C0025
COLLATE utf16_unicode_ci ORDER BY c;
DROP TABLE t1;

CREATE TABLE t1 (c varchar(200) CHARACTER SET utf16 COLLATE utf16_unicode_ci NOT NULL, INDEX (c));
INSERT INTO t1 VALUES (0x039C03C903B403B11F770308);
#Check one row row
SELECT * FROM t1 WHERE c LIKE _utf16 0x039C0025 COLLATE utf16_unicode_ci;
INSERT INTO t1 VALUES (0x039C03C903B4);
#Check two rows
SELECT * FROM t1 WHERE c LIKE _utf16 0x039C0025
COLLATE utf16_unicode_ci ORDER BY c;
DROP TABLE t1;


SET NAMES utf8;
SET @test_character_set='utf16';
SET @test_collation='utf16_swedish_ci';
-- source include/ctype_common.inc


SET collation_connection='utf16_unicode_ci';
-- source include/ctype_filesort.inc
-- source include/ctype_like_escape.inc
-- source include/ctype_german.inc

--echo End of 4.1 tests

#
# Check UPPER/LOWER changing length
#
# Result shorter than argument
CREATE TABLE t1 (id int, a varchar(30) character set utf16);
INSERT INTO t1 VALUES (1, 0x01310069), (2, 0x01310131);
INSERT INTO t1 VALUES (3, 0x00690069), (4, 0x01300049);
INSERT INTO t1 VALUES (5, 0x01300130), (6, 0x00490049);
SELECT a, length(a) la, @l:=lower(a) l, length(@l) ll, @u:=upper(a) u, length(@u) lu
FROM t1 ORDER BY id;
ALTER TABLE t1 MODIFY a VARCHAR(30) character set utf16 collate utf16_turkish_ci;
SELECT a, length(a) la, @l:=lower(a) l, length(@l) ll, @u:=upper(a) u, length(@u) lu
FROM t1 ORDER BY id;
DROP TABLE t1;

#
# Bug #27079 Crash while grouping empty ucs2 strings
#
CREATE TABLE t1 (
 c1 text character set utf16 collate utf16_polish_ci NOT NULL
) ENGINE=MyISAM;
insert into t1 values (''),('a');
SELECT COUNT(*), c1 FROM t1 GROUP BY c1;
DROP TABLE IF EXISTS t1;


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

#
# Test like_range and contractions
#
SET collation_connection=utf16_czech_ci;
--source include/ctype_czech.inc
--source include/ctype_like_ignorable.inc


--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=utf16_unicode_ci;
--source include/weight_string.inc
--source include/weight_string_euro.inc
select hex(weight_string(_utf16 0xD800DC00 collate utf16_unicode_ci));
select hex(weight_string(_utf16 0xD800DC01 collate utf16_unicode_ci));
--source include/weight_string_l1.inc

set @@collation_connection=utf16_czech_ci;
--source include/weight_string_chde.inc

#
# WL#4013 Unicode german2 collation
#
SET NAMES utf8;
SET collation_connection=utf16_german2_ci;
--source include/ctype_german.inc

--echo #
--echo # WL#2673 Unicode Collation Algorithm new version
--echo #
SET NAMES utf8mb4;
SET collation_connection=utf16_unicode_520_ci;
--source include/ctype_unicode520.inc 

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


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

--echo #
--echo # MDEV-4929 Myanmar collation
--echo #
SET NAMES utf8;
SET collation_connection=utf16_myanmar_ci;
--source include/ctype_myanmar.inc

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

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

SET NAMES utf8;
SET collation_connection=utf16_thai_520_w2;
--source include/ctype_uca_w2.inc
--source include/ctype_thai.inc

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

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

--echo #
--echo # MDEV-10877 xxx_unicode_nopad_ci collations
--echo #
SET NAMES utf8, character_set_connection=utf16;
let $coll='utf16_unicode_nopad_ci';
let $coll_pad='utf16_unicode_ci';
--source include/ctype_pad_all_engines.inc

let $coll='utf16_unicode_520_nopad_ci';
let $coll_pad='utf16_unicode_520_ci';
--source include/ctype_pad_all_engines.inc

SET NAMES utf8, collation_connection=utf16_unicode_520_nopad_ci;
--source include/ctype_like_range_mdev14350.inc
SET NAMES utf8;


--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=utf16_hungarian_ci;
CREATE TABLE t1(c ENUM('aaaaaaaa') CHARACTER SET 'Binary',d JSON);   # ERROR 1064 (42000): You have an error in your SQL syntax
SHOW CREATE TABLE t1;
INSERT INTO t1 (c) VALUES (1);
SELECT HEX(c) FROM t1;
DROP TABLE t1;

CREATE OR REPLACE TABLE t1(c ENUM('aaaaaaaaa') CHARACTER SET 'Binary',d JSON);   # ERROR 1033 (HY000): Incorrect information in file: './test/t.frm'
SHOW CREATE TABLE t1;
INSERT INTO t1 (c) VALUES (1);
SELECT HEX(c) FROM t1;
DROP TABLE t1;

CREATE OR REPLACE TABLE t1(c ENUM('aaaaaaaaaa') CHARACTER SET 'Binary',d JSON);   # Sig 11
SHOW CREATE TABLE t1;
INSERT INTO t1 (c) VALUES (1);
SELECT HEX(c) FROM t1;
DROP TABLE t1;


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