MySQL 5.6.14 Source Code Document
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
mix2_ucs2.inc
1 #
2 # Tests from mix2.inc which require ucs2 character sets should go here
3 #
4 
5 #
6 # BUG 14056 Column prefix index on UTF-8 primary key column causes: Can't find record..
7 #
8 
9 eval create table t1 (
10  a int, b char(10), c char(10), filler char(10), primary key(a, b(2)), unique key (a, c(2))
11 ) character set utf8 engine = $engine_type;
12 eval create table t2 (
13  a int, b char(10), c char(10), filler char(10), primary key(a, b(2)), unique key (a, c(2))
14 ) character set ucs2 engine = $engine_type;
15 insert into t1 values (1,'abcdefg','abcdefg','one');
16 insert into t1 values (2,'ijkilmn','ijkilmn','two');
17 insert into t1 values (3,'qrstuvw','qrstuvw','three');
18 insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four');
19 insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five');
20 insert into t1 values (4,_utf8 0xe880bde880bd,_utf8 0xe880bde880bd,'six');
21 insert into t1 values (4,_utf8 0xe880bdD0B1e880bd,_utf8 0xe880bdD0B1e880bd,'seven');
22 insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight');
23 insert into t2 values (1,'abcdefg','abcdefg','one');
24 insert into t2 values (2,'ijkilmn','ijkilmn','two');
25 insert into t2 values (3,'qrstuvw','qrstuvw','three');
26 insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four');
27 insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five');
28 insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six');
29 insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven');
30 insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight');
31 insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten');
32 insert into t2 values (4,_ucs2 0x05630563,_ucs2 0x05630563,'eleven');
33 insert into t2 values (4,_ucs2 0x0563001fc0563,_ucs2 0x0563001fc0563,'point');
34 insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken');
35 update t1 set filler = 'boo' where a = 1;
36 update t2 set filler ='email' where a = 4;
37 select a,hex(b),hex(c),filler from t1 order by filler;
38 select a,hex(b),hex(c),filler from t2 order by filler;
39 drop table t1;
40 drop table t2;
41 
42 eval create table t1 (
43  a int, b varchar(10), c varchar(10), filler varchar(10), primary key(a, b(2)), unique key (a, c(2))
44 ) character set utf8 engine = $engine_type;
45 eval create table t2 (
46  a int, b varchar(10), c varchar(10), filler varchar(10), primary key(a, b(2)), unique key (a, c(2))
47 ) character set ucs2 engine = $engine_type;
48 insert into t1 values (1,'abcdefg','abcdefg','one');
49 insert into t1 values (2,'ijkilmn','ijkilmn','two');
50 insert into t1 values (3,'qrstuvw','qrstuvw','three');
51 insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four');
52 insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five');
53 insert into t1 values (4,_utf8 0xe880bde880bd,_utf8 0xe880bde880bd,'six');
54 insert into t1 values (4,_utf8 0xe880bdD0B1e880bd,_utf8 0xe880bdD0B1e880bd,'seven');
55 insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight');
56 insert into t2 values (1,'abcdefg','abcdefg','one');
57 insert into t2 values (2,'ijkilmn','ijkilmn','two');
58 insert into t2 values (3,'qrstuvw','qrstuvw','three');
59 
60 
61 insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four');
62 insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five');
63 insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six');
64 insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven');
65 insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight');
66 insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten');
67 insert into t2 values (4,_ucs2 0x05630563,_ucs2 0x05630563,'eleven');
68 insert into t2 values (4,_ucs2 0x0563001fc0563,_ucs2 0x0563001fc0563,'point');
69 insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken');
70 update t1 set filler = 'boo' where a = 1;
71 update t2 set filler ='email' where a = 4;
72 select a,hex(b),hex(c),filler from t1 order by filler;
73 select a,hex(b),hex(c),filler from t2 order by filler;
74 drop table t1;
75 drop table t2;
76 
77 eval create table t1 (
78  a int, b text(10), c text(10), filler text(10), primary key(a, b(2)), unique key (a, c(2))
79 ) character set utf8 engine = $engine_type;
80 eval create table t2 (
81  a int, b text(10), c text(10), filler text(10), primary key(a, b(2)), unique key (a, c(2))
82 ) character set ucs2 engine = $engine_type;
83 insert into t1 values (1,'abcdefg','abcdefg','one');
84 insert into t1 values (2,'ijkilmn','ijkilmn','two');
85 insert into t1 values (3,'qrstuvw','qrstuvw','three');
86 insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four');
87 insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five');
88 insert into t1 values (4,_utf8 0xe880bde880bd,_utf8 0xe880bde880bd,'six');
89 insert into t1 values (4,_utf8 0xe880bdD0B1e880bd,_utf8 0xe880bdD0B1e880bd,'seven');
90 insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight');
91 insert into t2 values (1,'abcdefg','abcdefg','one');
92 insert into t2 values (2,'ijkilmn','ijkilmn','two');
93 insert into t2 values (3,'qrstuvw','qrstuvw','three');
94 insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four');
95 insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five');
96 insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six');
97 insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven');
98 insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight');
99 insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten');
100 insert into t2 values (4,_ucs2 0x05630563,_ucs2 0x05630563,'eleven');
101 insert into t2 values (4,_ucs2 0x0563001fc0563,_ucs2 0x0563001fc0563,'point');
102 insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken');
103 update t1 set filler = 'boo' where a = 1;
104 update t2 set filler ='email' where a = 4;
105 select a,hex(b),hex(c),filler from t1 order by filler;
106 select a,hex(b),hex(c),filler from t2 order by filler;
107 drop table t1;
108 drop table t2;
109 
110 eval create table t1 (
111  a int, b blob(10), c blob(10), filler blob(10), primary key(a, b(2)), unique key (a, c(2))
112 ) character set utf8 engine = $engine_type;
113 eval create table t2 (
114  a int, b blob(10), c blob(10), filler blob(10), primary key(a, b(2)), unique key (a, c(2))
115 ) character set ucs2 engine = $engine_type;
116 insert into t1 values (1,'abcdefg','abcdefg','one');
117 insert into t1 values (2,'ijkilmn','ijkilmn','two');
118 insert into t1 values (3,'qrstuvw','qrstuvw','three');
119 insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four');
120 insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five');
121 insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight');
122 insert into t2 values (1,'abcdefg','abcdefg','one');
123 insert into t2 values (2,'ijkilmn','ijkilmn','two');
124 insert into t2 values (3,'qrstuvw','qrstuvw','three');
125 insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four');
126 insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five');
127 insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six');
128 insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven');
129 insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight');
130 insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten');
131 insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken');
132 update t1 set filler = 'boo' where a = 1;
133 update t2 set filler ='email' where a = 4;
134 select a,hex(b),hex(c),filler from t1 order by filler;
135 select a,hex(b),hex(c),filler from t2 order by filler;
136 drop table t1;
137 drop table t2;
138 commit;
139 
140 #
141 # Test cases for bug #15308 Problem of Order with Enum Column in Primary Key
142 #
143 eval CREATE TABLE t1 (
144  ind enum('0','1','2') NOT NULL default '0',
145  string1 varchar(250) NOT NULL,
146  PRIMARY KEY (ind)
147 ) ENGINE=$engine_type DEFAULT CHARSET=utf8;
148 eval CREATE TABLE t2 (
149  ind enum('0','1','2') NOT NULL default '0',
150  string1 varchar(250) NOT NULL,
151  PRIMARY KEY (ind)
152 ) ENGINE=$engine_type DEFAULT CHARSET=ucs2;
153 
154 INSERT INTO t1 VALUES ('1', ''),('2', '');
155 INSERT INTO t2 VALUES ('1', ''),('2', '');
156 SELECT hex(ind),hex(string1) FROM t1 ORDER BY string1;
157 SELECT hex(ind),hex(string1) FROM t2 ORDER BY string1;
158 drop table t1,t2;
159 
160 eval CREATE TABLE t1 (
161  ind set('0','1','2') NOT NULL default '0',
162  string1 varchar(250) NOT NULL,
163  PRIMARY KEY (ind)
164 ) ENGINE=$engine_type DEFAULT CHARSET=utf8;
165 eval CREATE TABLE t2 (
166  ind set('0','1','2') NOT NULL default '0',
167  string1 varchar(250) NOT NULL,
168  PRIMARY KEY (ind)
169 ) ENGINE=$engine_type DEFAULT CHARSET=ucs2;
170 
171 INSERT INTO t1 VALUES ('1', ''),('2', '');
172 INSERT INTO t2 VALUES ('1', ''),('2', '');
173 SELECT hex(ind),hex(string1) FROM t1 ORDER BY string1;
174 SELECT hex(ind),hex(string1) FROM t2 ORDER BY string1;
175 drop table t1,t2;
176 
177 eval CREATE TABLE t1 (
178  ind bit not null,
179  string1 varchar(250) NOT NULL,
180  PRIMARY KEY (ind)
181 ) ENGINE=$engine_type DEFAULT CHARSET=utf8;
182 eval CREATE TABLE t2 (
183  ind bit not null,
184  string1 varchar(250) NOT NULL,
185  PRIMARY KEY (ind)
186 ) ENGINE=$engine_type DEFAULT CHARSET=ucs2;
187 insert into t1 values(0,''),(1,'');
188 insert into t2 values(0,''),(1,'');
189 select hex(ind),hex(string1) from t1 order by string1;
190 select hex(ind),hex(string1) from t2 order by string1;
191 drop table t1,t2;
192 
193 # tests for bug #14056 Column prefix index on UTF-8 primary key column causes 'Can't find record..'
194 
195 eval create table t2 (
196  a int, b char(10), filler char(10), primary key(a, b(2))
197 ) character set utf8 engine = $engine_type;
198 
199 insert into t2 values (1,'abcdefg','one');
200 insert into t2 values (2,'ijkilmn','two');
201 insert into t2 values (3, 'qrstuvw','three');
202 update t2 set a=5, filler='booo' where a=1;
203 drop table t2;
204 eval create table t2 (
205  a int, b char(10), filler char(10), primary key(a, b(2))
206 ) character set ucs2 engine = $engine_type;
207 
208 insert into t2 values (1,'abcdefg','one');
209 insert into t2 values (2,'ijkilmn','two');
210 insert into t2 values (3, 'qrstuvw','three');
211 update t2 set a=5, filler='booo' where a=1;
212 drop table t2;
213 
214 eval create table t1(a int not null, b char(110),primary key(a,b(100))) engine=$engine_type default charset=utf8;
215 insert into t1 values(1,'abcdefg'),(2,'defghijk');
216 insert into t1 values(6,_utf8 0xD0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1);
217 insert into t1 values(7,_utf8 0xD0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B2);
218 select a,hex(b) from t1 order by b;
219 update t1 set b = 'three' where a = 6;
220 drop table t1;
221 eval create table t1(a int not null, b text(110),primary key(a,b(100))) engine=$engine_type default charset=utf8;
222 insert into t1 values(1,'abcdefg'),(2,'defghijk');
223 insert into t1 values(6,_utf8 0xD0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1);
224 insert into t1 values(7,_utf8 0xD0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B2);
225 select a,hex(b) from t1 order by b;
226 update t1 set b = 'three' where a = 6;
227 drop table t1;
228