drop table if exists t1; SET NAMES cp1251; create table t1 (a varchar(10) not null) character set cp1251; insert into t1 values ("a"),("ab"),("abc"); select * from t1; a a ab abc select a, left(a,1) as b from t1; a b a a ab a abc a select a, left(a,1) as b from t1 group by a; a b a a ab a abc a SELECT DISTINCT RIGHT(a,1) from t1; RIGHT(a,1) a b c drop table t1; create table t1 (a char(3) binary, b binary(3)) character set cp1251; insert into t1 values ('aaa','bbb'),('AAA','BBB'); select upper(a),upper(b) from t1; upper(a) upper(b) AAA bbb AAA BBB select lower(a),lower(b) from t1; lower(a) lower(b) aaa bbb aaa BBB select * from t1 where upper(a)='AAA'; a b aaa bbb AAA BBB select * from t1 where lower(a)='aaa'; a b aaa bbb AAA BBB select * from t1 where upper(b)='BBB'; a b AAA BBB select * from t1 where lower(b)='bbb'; a b aaa bbb select charset(a), charset(b), charset(binary 'ccc') from t1 limit 1; charset(a) charset(b) charset(binary 'ccc') cp1251 binary binary select collation(a), collation(b), collation(binary 'ccc') from t1 limit 1; collation(a) collation(b) collation(binary 'ccc') cp1251_bin binary binary drop table t1; create table t1 ( a varchar(16) character set cp1251 collate cp1251_bin not null, b int(10) default null, primary key(a) ) charset=cp1251; insert into t1 (a) values ('air'), ('we'),('g'),('we_toshko'), ('s0urce'),('we_ivo'),('we_iliyan'), ('we_martin'),('vw_grado'),('vw_vasko'),('tn_vili'),('tn_kalina'), ('tn_fakira'),('vw_silvia'),('vw_starshi'),('vw_geo'),('vw_b0x1'); select * from t1 where a like 'we_%'; a b we_iliyan NULL we_ivo NULL we_martin NULL we_toshko NULL drop table t1; # # Start of 5.1 tests # SELECT @@collation_connection; @@collation_connection cp1251_general_ci CREATE TABLE t1 AS SELECT ' ' AS a LIMIT 0; INSERT INTO t1 VALUES (0x00),(0x01),(0x02),(0x03),(0x04),(0x05),(0x06),(0x07); INSERT INTO t1 VALUES (0x08),(0x09),(0x0A),(0x0B),(0x0C),(0x0D),(0x0E),(0x0F); INSERT INTO t1 VALUES (0x10),(0x11),(0x12),(0x13),(0x14),(0x15),(0x16),(0x17); INSERT INTO t1 VALUES (0x18),(0x19),(0x1A),(0x1B),(0x1C),(0x1D),(0x1E),(0x1F); INSERT INTO t1 VALUES (0x20),(0x21),(0x22),(0x23),(0x24),(0x25),(0x26),(0x27); INSERT INTO t1 VALUES (0x28),(0x29),(0x2A),(0x2B),(0x2C),(0x2D),(0x2E),(0x2F); INSERT INTO t1 VALUES (0x30),(0x31),(0x32),(0x33),(0x34),(0x35),(0x36),(0x37); INSERT INTO t1 VALUES (0x38),(0x39),(0x3A),(0x3B),(0x3C),(0x3D),(0x3E),(0x3F); INSERT INTO t1 VALUES (0x40),(0x41),(0x42),(0x43),(0x44),(0x45),(0x46),(0x47); INSERT INTO t1 VALUES (0x48),(0x49),(0x4A),(0x4B),(0x4C),(0x4D),(0x4E),(0x4F); INSERT INTO t1 VALUES (0x50),(0x51),(0x52),(0x53),(0x54),(0x55),(0x56),(0x57); INSERT INTO t1 VALUES (0x58),(0x59),(0x5A),(0x5B),(0x5C),(0x5D),(0x5E),(0x5F); INSERT INTO t1 VALUES (0x60),(0x61),(0x62),(0x63),(0x64),(0x65),(0x66),(0x67); INSERT INTO t1 VALUES (0x68),(0x69),(0x6A),(0x6B),(0x6C),(0x6D),(0x6E),(0x6F); INSERT INTO t1 VALUES (0x70),(0x71),(0x72),(0x73),(0x74),(0x75),(0x76),(0x77); INSERT INTO t1 VALUES (0x78),(0x79),(0x7A),(0x7B),(0x7C),(0x7D),(0x7E),(0x7F); INSERT INTO t1 VALUES (0x80),(0x81),(0x82),(0x83),(0x84),(0x85),(0x86),(0x87); INSERT INTO t1 VALUES (0x88),(0x89),(0x8A),(0x8B),(0x8C),(0x8D),(0x8E),(0x8F); INSERT INTO t1 VALUES (0x90),(0x91),(0x92),(0x93),(0x94),(0x95),(0x96),(0x97); INSERT INTO t1 VALUES (0x98),(0x99),(0x9A),(0x9B),(0x9C),(0x9D),(0x9E),(0x9F); INSERT INTO t1 VALUES (0xA0),(0xA1),(0xA2),(0xA3),(0xA4),(0xA5),(0xA6),(0xA7); INSERT INTO t1 VALUES (0xA8),(0xA9),(0xAA),(0xAB),(0xAC),(0xAD),(0xAE),(0xAF); INSERT INTO t1 VALUES (0xB0),(0xB1),(0xB2),(0xB3),(0xB4),(0xB5),(0xB6),(0xB7); INSERT INTO t1 VALUES (0xB8),(0xB9),(0xBA),(0xBB),(0xBC),(0xBD),(0xBE),(0xBF); INSERT INTO t1 VALUES (0xC0),(0xC1),(0xC2),(0xC3),(0xC4),(0xC5),(0xC6),(0xC7); INSERT INTO t1 VALUES (0xC8),(0xC9),(0xCA),(0xCB),(0xCC),(0xCD),(0xCE),(0xCF); INSERT INTO t1 VALUES (0xD0),(0xD1),(0xD2),(0xD3),(0xD4),(0xD5),(0xD6),(0xD7); INSERT INTO t1 VALUES (0xD8),(0xD9),(0xDA),(0xDB),(0xDC),(0xDD),(0xDE),(0xDF); INSERT INTO t1 VALUES (0xE0),(0xE1),(0xE2),(0xE3),(0xE4),(0xE5),(0xE6),(0xE7); INSERT INTO t1 VALUES (0xE8),(0xE9),(0xEA),(0xEB),(0xEC),(0xED),(0xEE),(0xEF); INSERT INTO t1 VALUES (0xF0),(0xF1),(0xF2),(0xF3),(0xF4),(0xF5),(0xF6),(0xF7); INSERT INTO t1 VALUES (0xF8),(0xF9),(0xFA),(0xFB),(0xFC),(0xFD),(0xFE),(0xFF); SELECT HEX(a) AS chr, HEX(LOWER(a)) AS upper, HEX(LOWER(a)) AS lower, HEX(@utf8:=CONVERT(a USING utf8)) AS utf8, HEX(@roundtrip:=CAST(@utf8 AS CHAR)) AS roundtrip, if(a=BINARY @roundtrip,'','Round trip unsafe') AS issafe FROM t1 ORDER BY chr; chr upper lower utf8 roundtrip issafe 00 00 00 00 00 01 01 01 01 01 02 02 02 02 02 03 03 03 03 03 04 04 04 04 04 05 05 05 05 05 06 06 06 06 06 07 07 07 07 07 08 08 08 08 08 09 09 09 09 09 0A 0A 0A 0A 0A 0B 0B 0B 0B 0B 0C 0C 0C 0C 0C 0D 0D 0D 0D 0D 0E 0E 0E 0E 0E 0F 0F 0F 0F 0F 10 10 10 10 10 11 11 11 11 11 12 12 12 12 12 13 13 13 13 13 14 14 14 14 14 15 15 15 15 15 16 16 16 16 16 17 17 17 17 17 18 18 18 18 18 19 19 19 19 19 1A 1A 1A 1A 1A 1B 1B 1B 1B 1B 1C 1C 1C 1C 1C 1D 1D 1D 1D 1D 1E 1E 1E 1E 1E 1F 1F 1F 1F 1F 20 20 20 20 20 21 21 21 21 21 22 22 22 22 22 23 23 23 23 23 24 24 24 24 24 25 25 25 25 25 26 26 26 26 26 27 27 27 27 27 28 28 28 28 28 29 29 29 29 29 2A 2A 2A 2A 2A 2B 2B 2B 2B 2B 2C 2C 2C 2C 2C 2D 2D 2D 2D 2D 2E 2E 2E 2E 2E 2F 2F 2F 2F 2F 30 30 30 30 30 31 31 31 31 31 32 32 32 32 32 33 33 33 33 33 34 34 34 34 34 35 35 35 35 35 36 36 36 36 36 37 37 37 37 37 38 38 38 38 38 39 39 39 39 39 3A 3A 3A 3A 3A 3B 3B 3B 3B 3B 3C 3C 3C 3C 3C 3D 3D 3D 3D 3D 3E 3E 3E 3E 3E 3F 3F 3F 3F 3F 40 40 40 40 40 41 61 61 41 41 42 62 62 42 42 43 63 63 43 43 44 64 64 44 44 45 65 65 45 45 46 66 66 46 46 47 67 67 47 47 48 68 68 48 48 49 69 69 49 49 4A 6A 6A 4A 4A 4B 6B 6B 4B 4B 4C 6C 6C 4C 4C 4D 6D 6D 4D 4D 4E 6E 6E 4E 4E 4F 6F 6F 4F 4F 50 70 70 50 50 51 71 71 51 51 52 72 72 52 52 53 73 73 53 53 54 74 74 54 54 55 75 75 55 55 56 76 76 56 56 57 77 77 57 57 58 78 78 58 58 59 79 79 59 59 5A 7A 7A 5A 5A 5B 5B 5B 5B 5B 5C 5C 5C 5C 5C 5D 5D 5D 5D 5D 5E 5E 5E 5E 5E 5F 5F 5F 5F 5F 60 60 60 60 60 61 61 61 61 61 62 62 62 62 62 63 63 63 63 63 64 64 64 64 64 65 65 65 65 65 66 66 66 66 66 67 67 67 67 67 68 68 68 68 68 69 69 69 69 69 6A 6A 6A 6A 6A 6B 6B 6B 6B 6B 6C 6C 6C 6C 6C 6D 6D 6D 6D 6D 6E 6E 6E 6E 6E 6F 6F 6F 6F 6F 70 70 70 70 70 71 71 71 71 71 72 72 72 72 72 73 73 73 73 73 74 74 74 74 74 75 75 75 75 75 76 76 76 76 76 77 77 77 77 77 78 78 78 78 78 79 79 79 79 79 7A 7A 7A 7A 7A 7B 7B 7B 7B 7B 7C 7C 7C 7C 7C 7D 7D 7D 7D 7D 7E 7E 7E 7E 7E 7F 7F 7F 7F 7F 80 90 90 D082 80 81 83 83 D083 81 82 82 82 E2809A 82 83 83 83 D193 83 84 84 84 E2809E 84 85 85 85 E280A6 85 86 86 86 E280A0 86 87 87 87 E280A1 87 88 88 88 E282AC 88 89 89 89 E280B0 89 8A 9A 9A D089 8A 8B 8B 8B E280B9 8B 8C 9C 9C D08A 8C 8D 9D 9D D08C 8D 8E 9E 9E D08B 8E 8F 9F 9F D08F 8F 90 90 90 D192 90 91 91 91 E28098 91 92 92 92 E28099 92 93 93 93 E2809C 93 94 94 94 E2809D 94 95 95 95 E280A2 95 96 96 96 E28093 96 97 97 97 E28094 97 98 98 98 3F 3F Round trip unsafe 99 99 99 E284A2 99 9A 9A 9A D199 9A 9B 9B 9B E280BA 9B 9C 9C 9C D19A 9C 9D 9D 9D D19C 9D 9E 9E 9E D19B 9E 9F 9F 9F D19F 9F A0 A0 A0 C2A0 A0 A1 A2 A2 D08E A1 A2 A2 A2 D19E A2 A3 BC BC D088 A3 A4 A4 A4 C2A4 A4 A5 B4 B4 D290 A5 A6 A6 A6 C2A6 A6 A7 A7 A7 C2A7 A7 A8 B8 B8 D081 A8 A9 A9 A9 C2A9 A9 AA BA BA D084 AA AB AB AB C2AB AB AC AC AC C2AC AC AD AD AD C2AD AD AE AE AE C2AE AE AF BF BF D087 AF B0 B0 B0 C2B0 B0 B1 B1 B1 C2B1 B1 B2 B3 B3 D086 B2 B3 B3 B3 D196 B3 B4 B4 B4 D291 B4 B5 B5 B5 C2B5 B5 B6 B6 B6 C2B6 B6 B7 B7 B7 C2B7 B7 B8 B8 B8 D191 B8 B9 B9 B9 E28496 B9 BA BA BA D194 BA BB BB BB C2BB BB BC BC BC D198 BC BD BE BE D085 BD BE BE BE D195 BE BF BF BF D197 BF C0 E0 E0 D090 C0 C1 E1 E1 D091 C1 C2 E2 E2 D092 C2 C3 E3 E3 D093 C3 C4 E4 E4 D094 C4 C5 E5 E5 D095 C5 C6 E6 E6 D096 C6 C7 E7 E7 D097 C7 C8 E8 E8 D098 C8 C9 E9 E9 D099 C9 CA EA EA D09A CA CB EB EB D09B CB CC EC EC D09C CC CD ED ED D09D CD CE EE EE D09E CE CF EF EF D09F CF D0 F0 F0 D0A0 D0 D1 F1 F1 D0A1 D1 D2 F2 F2 D0A2 D2 D3 F3 F3 D0A3 D3 D4 F4 F4 D0A4 D4 D5 F5 F5 D0A5 D5 D6 F6 F6 D0A6 D6 D7 F7 F7 D0A7 D7 D8 F8 F8 D0A8 D8 D9 F9 F9 D0A9 D9 DA FA FA D0AA DA DB FB FB D0AB DB DC FC FC D0AC DC DD FD FD D0AD DD DE FE FE D0AE DE DF FF FF D0AF DF E0 E0 E0 D0B0 E0 E1 E1 E1 D0B1 E1 E2 E2 E2 D0B2 E2 E3 E3 E3 D0B3 E3 E4 E4 E4 D0B4 E4 E5 E5 E5 D0B5 E5 E6 E6 E6 D0B6 E6 E7 E7 E7 D0B7 E7 E8 E8 E8 D0B8 E8 E9 E9 E9 D0B9 E9 EA EA EA D0BA EA EB EB EB D0BB EB EC EC EC D0BC EC ED ED ED D0BD ED EE EE EE D0BE EE EF EF EF D0BF EF F0 F0 F0 D180 F0 F1 F1 F1 D181 F1 F2 F2 F2 D182 F2 F3 F3 F3 D183 F3 F4 F4 F4 D184 F4 F5 F5 F5 D185 F5 F6 F6 F6 D186 F6 F7 F7 F7 D187 F7 F8 F8 F8 D188 F8 F9 F9 F9 D189 F9 FA FA FA D18A FA FB FB FB D18B FB FC FC FC D18C FC FD FD FD D18D FD FE FE FE D18E FE FF FF FF D18F FF DROP TABLE t1; # # End of 5.1 tests #