1# 2# Common tests for all character sets and collations. 3# Include this file from a test with @test_characrer_set 4# and @test_collation set to desired values. 5# 6# Please don't use SHOW CREATE TABLE in this file, 7# we want it to be HANDLER independent. You can 8# use SHOW FULL COLUMNS instead. 9# 10# Please surround all CREATE TABLE with --disable_warnings 11# and --enable_warnings to be able to set storage_engine 12# without having to check if the hanlder exists. 13 14SET @safe_character_set_server= @@character_set_server; 15SET @safe_collation_server= @@collation_server; 16SET @safe_character_set_client= @@character_set_client; 17SET @safe_character_set_results= @@character_set_results; 18SET character_set_server= @test_character_set; 19SET collation_server= @test_collation; 20CREATE DATABASE d1; 21USE d1; 22 23# 24# Bug 1883: LIKE did not work in some cases with a key. 25# 26--disable_warnings 27CREATE TABLE t1 (c CHAR(10), KEY(c)); 28--enable_warnings 29# check the column was created with the expected charset/collation 30--replace_result select,insert,update,references "" 31SHOW FULL COLUMNS FROM t1; 32INSERT INTO t1 VALUES ('aaa'),('aaaa'),('aaaaa'); 33SELECT c as want3results FROM t1 WHERE c LIKE 'aaa%'; 34DROP TABLE t1; 35 36# 37# Bug 6643 incorrect response with partial utf8 index 38# 39--disable_warnings 40CREATE TABLE t1 (c1 varchar(15), KEY c1 (c1(2))); 41--enable_warnings 42# check the column was created with the expected charset/collation 43--replace_result select,insert,update,references "" 44SHOW FULL COLUMNS FROM t1; 45INSERT INTO t1 VALUES ('location'),('loberge'),('lotre'),('boabab'); 46SELECT c1 as want3results from t1 where c1 like 'l%'; 47SELECT c1 as want3results from t1 where c1 like 'lo%'; 48SELECT c1 as want1result from t1 where c1 like 'loc%'; 49SELECT c1 as want1result from t1 where c1 like 'loca%'; 50SELECT c1 as want1result from t1 where c1 like 'locat%'; 51SELECT c1 as want1result from t1 where c1 like 'locati%'; 52SELECT c1 as want1result from t1 where c1 like 'locatio%'; 53SELECT c1 as want1result from t1 where c1 like 'location%'; 54DROP TABLE t1; 55 56# 57# Bug #31070: crash during conversion of charsets 58# Bug #32726: crash with cast in order by clause and cp932 charset 59# 60create table t1 (a set('a') not null); 61insert ignore into t1 values (),(); 62select cast(a as char(1)) from t1; 63select a sounds like a from t1; 64select 1 from t1 order by cast(a as char(1)); 65drop table t1; 66 67--echo # 68--echo # MDEV-6134 SUBSTRING_INDEX returns wrong result for 8bit character sets when delimiter is not found 69--echo # 70SET character_set_client=latin1; 71SET character_set_connection= @test_character_set; 72SET collation_connection= @test_collation; 73SELECT COLLATION('.'), SUBSTRING_INDEX('.wwwmysqlcom', '.', -2) AS c1; 74 75# 76# Bug#27580 SPACE() function collation bug? 77# 78set names utf8; 79create table t1 ( 80 name varchar(10), 81 level smallint unsigned); 82show create table t1; 83insert into t1 values ('string',1); 84select concat(name,space(level)), concat(name, repeat(' ',level)) from t1; 85drop table t1; 86 87DROP DATABASE d1; 88# Restore settings 89USE test; 90SET character_set_server= @safe_character_set_server; 91SET collation_server= @safe_collation_server; 92SET character_set_client= @safe_character_set_client; 93SET character_set_results= @safe_character_set_results; 94