1SET DEFAULT_STORAGE_ENGINE='tokudb'; 2drop table if exists t1; 3DROP TABLE IF EXISTS t1; 4SHOW COLLATION LIKE 'cp1250_czech_cs'; 5Collation Charset Id Default Compiled Sortlen 6cp1250_czech_cs cp1250 34 Yes 2 7SET @test_character_set= 'cp1250'; 8SET @test_collation= 'cp1250_general_ci'; 9SET @safe_character_set_server= @@character_set_server; 10SET @safe_collation_server= @@collation_server; 11SET @safe_character_set_client= @@character_set_client; 12SET @safe_character_set_results= @@character_set_results; 13SET character_set_server= @test_character_set; 14SET collation_server= @test_collation; 15CREATE DATABASE d1; 16USE d1; 17CREATE TABLE t1 (c CHAR(10), KEY(c)); 18SHOW FULL COLUMNS FROM t1; 19Field Type Collation Null Key Default Extra Privileges Comment 20c char(10) cp1250_general_ci YES MUL NULL 21INSERT INTO t1 VALUES ('aaa'),('aaaa'),('aaaaa'); 22SELECT c as want3results FROM t1 WHERE c LIKE 'aaa%'; 23want3results 24aaa 25aaaa 26aaaaa 27DROP TABLE t1; 28CREATE TABLE t1 (c1 varchar(15), KEY c1 (c1(2))); 29SHOW FULL COLUMNS FROM t1; 30Field Type Collation Null Key Default Extra Privileges Comment 31c1 varchar(15) cp1250_general_ci YES MUL NULL 32INSERT INTO t1 VALUES ('location'),('loberge'),('lotre'),('boabab'); 33SELECT c1 as want3results from t1 where c1 like 'l%'; 34want3results 35location 36loberge 37lotre 38SELECT c1 as want3results from t1 where c1 like 'lo%'; 39want3results 40location 41loberge 42lotre 43SELECT c1 as want1result from t1 where c1 like 'loc%'; 44want1result 45location 46SELECT c1 as want1result from t1 where c1 like 'loca%'; 47want1result 48location 49SELECT c1 as want1result from t1 where c1 like 'locat%'; 50want1result 51location 52SELECT c1 as want1result from t1 where c1 like 'locati%'; 53want1result 54location 55SELECT c1 as want1result from t1 where c1 like 'locatio%'; 56want1result 57location 58SELECT c1 as want1result from t1 where c1 like 'location%'; 59want1result 60location 61DROP TABLE t1; 62create table t1 (a set('a') not null); 63insert ignore into t1 values (),(); 64Warnings: 65Warning 1364 Field 'a' doesn't have a default value 66select cast(a as char(1)) from t1; 67cast(a as char(1)) 68 69 70select a sounds like a from t1; 71a sounds like a 721 731 74select 1 from t1 order by cast(a as char(1)); 751 761 771 78drop table t1; 79# 80# MDEV-6134 SUBSTRING_INDEX returns wrong result for 8bit character sets when delimiter is not found 81# 82SET character_set_client=latin1; 83SET character_set_connection= @test_character_set; 84SET collation_connection= @test_collation; 85SELECT COLLATION('.'), SUBSTRING_INDEX('.wwwmysqlcom', '.', -2) AS c1; 86COLLATION('.') c1 87cp1250_general_ci .wwwmysqlcom 88set names utf8; 89create table t1 ( 90name varchar(10), 91level smallint unsigned); 92show create table t1; 93Table Create Table 94t1 CREATE TABLE `t1` ( 95 `name` varchar(10) DEFAULT NULL, 96 `level` smallint(5) unsigned DEFAULT NULL 97) ENGINE=TokuDB DEFAULT CHARSET=cp1250 98insert into t1 values ('string',1); 99select concat(name,space(level)), concat(name, repeat(' ',level)) from t1; 100concat(name,space(level)) concat(name, repeat(' ',level)) 101string string 102drop table t1; 103DROP DATABASE d1; 104USE test; 105SET character_set_server= @safe_character_set_server; 106SET collation_server= @safe_collation_server; 107SET character_set_client= @safe_character_set_client; 108SET character_set_results= @safe_character_set_results; 109SET @test_character_set= 'cp1250'; 110SET @test_collation= 'cp1250_czech_cs'; 111SET @safe_character_set_server= @@character_set_server; 112SET @safe_collation_server= @@collation_server; 113SET @safe_character_set_client= @@character_set_client; 114SET @safe_character_set_results= @@character_set_results; 115SET character_set_server= @test_character_set; 116SET collation_server= @test_collation; 117CREATE DATABASE d1; 118USE d1; 119CREATE TABLE t1 (c CHAR(10), KEY(c)); 120SHOW FULL COLUMNS FROM t1; 121Field Type Collation Null Key Default Extra Privileges Comment 122c char(10) cp1250_czech_cs YES MUL NULL 123INSERT INTO t1 VALUES ('aaa'),('aaaa'),('aaaaa'); 124SELECT c as want3results FROM t1 WHERE c LIKE 'aaa%'; 125want3results 126aaa 127aaaa 128aaaaa 129DROP TABLE t1; 130CREATE TABLE t1 (c1 varchar(15), KEY c1 (c1(2))); 131SHOW FULL COLUMNS FROM t1; 132Field Type Collation Null Key Default Extra Privileges Comment 133c1 varchar(15) cp1250_czech_cs YES MUL NULL 134INSERT INTO t1 VALUES ('location'),('loberge'),('lotre'),('boabab'); 135SELECT c1 as want3results from t1 where c1 like 'l%'; 136want3results 137location 138loberge 139lotre 140SELECT c1 as want3results from t1 where c1 like 'lo%'; 141want3results 142location 143loberge 144lotre 145SELECT c1 as want1result from t1 where c1 like 'loc%'; 146want1result 147location 148SELECT c1 as want1result from t1 where c1 like 'loca%'; 149want1result 150location 151SELECT c1 as want1result from t1 where c1 like 'locat%'; 152want1result 153location 154SELECT c1 as want1result from t1 where c1 like 'locati%'; 155want1result 156location 157SELECT c1 as want1result from t1 where c1 like 'locatio%'; 158want1result 159location 160SELECT c1 as want1result from t1 where c1 like 'location%'; 161want1result 162location 163DROP TABLE t1; 164create table t1 (a set('a') not null); 165insert ignore into t1 values (),(); 166Warnings: 167Warning 1364 Field 'a' doesn't have a default value 168select cast(a as char(1)) from t1; 169cast(a as char(1)) 170 171 172select a sounds like a from t1; 173a sounds like a 1741 1751 176select 1 from t1 order by cast(a as char(1)); 1771 1781 1791 180drop table t1; 181# 182# MDEV-6134 SUBSTRING_INDEX returns wrong result for 8bit character sets when delimiter is not found 183# 184SET character_set_client=latin1; 185SET character_set_connection= @test_character_set; 186SET collation_connection= @test_collation; 187SELECT COLLATION('.'), SUBSTRING_INDEX('.wwwmysqlcom', '.', -2) AS c1; 188COLLATION('.') c1 189cp1250_czech_cs .wwwmysqlcom 190set names utf8; 191create table t1 ( 192name varchar(10), 193level smallint unsigned); 194show create table t1; 195Table Create Table 196t1 CREATE TABLE `t1` ( 197 `name` varchar(10) COLLATE cp1250_czech_cs DEFAULT NULL, 198 `level` smallint(5) unsigned DEFAULT NULL 199) ENGINE=TokuDB DEFAULT CHARSET=cp1250 COLLATE=cp1250_czech_cs 200insert into t1 values ('string',1); 201select concat(name,space(level)), concat(name, repeat(' ',level)) from t1; 202concat(name,space(level)) concat(name, repeat(' ',level)) 203string string 204drop table t1; 205DROP DATABASE d1; 206USE test; 207SET character_set_server= @safe_character_set_server; 208SET collation_server= @safe_collation_server; 209SET character_set_client= @safe_character_set_client; 210SET character_set_results= @safe_character_set_results; 211CREATE TABLE t1 (a char(16)) character set cp1250 collate cp1250_czech_cs; 212INSERT INTO t1 VALUES (''); 213SELECT a, length(a), a='', a=' ', a=' ' FROM t1; 214a length(a) a='' a=' ' a=' ' 215 0 1 1 1 216DROP TABLE t1; 217CREATE TABLE t1 ( 218popisek varchar(30) collate cp1250_general_ci NOT NULL default '', 219PRIMARY KEY (`popisek`) 220); 221INSERT INTO t1 VALUES ('2005-01-1'); 222SELECT * FROM t1 WHERE popisek = '2005-01-1'; 223popisek 2242005-01-1 225SELECT * FROM t1 WHERE popisek LIKE '2005-01-1'; 226popisek 2272005-01-1 228drop table t1; 229set names cp1250; 230CREATE TABLE t1 231( 232id INT AUTO_INCREMENT PRIMARY KEY, 233str VARCHAR(32) CHARACTER SET cp1250 COLLATE cp1250_czech_cs NOT NULL default '', 234UNIQUE KEY (str) 235); 236INSERT INTO t1 VALUES (NULL, 'a'); 237INSERT INTO t1 VALUES (NULL, 'aa'); 238INSERT INTO t1 VALUES (NULL, 'aaa'); 239INSERT INTO t1 VALUES (NULL, 'aaaa'); 240INSERT INTO t1 VALUES (NULL, 'aaaaa'); 241INSERT INTO t1 VALUES (NULL, 'aaaaaa'); 242INSERT INTO t1 VALUES (NULL, 'aaaaaaa'); 243select * from t1 where str like 'aa%'; 244id str 2452 aa 2463 aaa 2474 aaaa 2485 aaaaa 2496 aaaaaa 2507 aaaaaaa 251drop table t1; 252set names cp1250; 253create table t1 (a varchar(15) collate cp1250_czech_cs NOT NULL, primary key(a)); 254insert into t1 values("abcdefgh�"); 255insert into t1 values("����"); 256select a from t1 where a like "abcdefgh�"; 257a 258abcdefgh� 259drop table t1; 260