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