1
drop table if exists t1;
2
drop database if exists test2;
5
a int not null primary key,
11
set @x0 = '01234567012345670123456701234567';
12
set @x0 = concat(@x0,@x0,@x0,@x0,@x0,@x0,@x0,@x0);
14
set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
15
set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
16
set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
17
set @b1 = concat(@b1,@x0);
19
set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1);
20
set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1);
21
set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1);
23
set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
24
set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
25
set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
26
set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
28
set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
29
set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
30
set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
31
set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2);
32
select length(@x0),length(@b1),length(@d1) from dual;
33
length(@x0) length(@b1) length(@d1)
35
select length(@x0),length(@b2),length(@d2) from dual;
36
length(@x0) length(@b2) length(@d2)
38
insert into t1 values(1,@b1,111,@d1);
39
insert into t1 values(2,@b2,222,@d2);
41
explain select * from t1 where a = 1;
42
id select_type table type possible_keys key key_len ref rows Extra
43
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
44
select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
46
a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3)
48
select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3)
50
a length(b) substr(b,1+2*9000,2) length(d) substr(d,1+3*9000,3)
52
update t1 set b=@b2,d=@d2 where a=1;
53
update t1 set b=@b1,d=@d1 where a=2;
55
select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3)
57
a length(b) substr(b,1+2*9000,2) length(d) substr(d,1+3*9000,3)
59
select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
61
a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3)
63
update t1 set b=concat(b,b),d=concat(d,d) where a=1;
64
update t1 set b=concat(b,b),d=concat(d,d) where a=2;
66
select a,length(b),substr(b,1+4*9000,2),length(d),substr(d,1+6*9000,3)
68
a length(b) substr(b,1+4*9000,2) length(d) substr(d,1+6*9000,3)
70
select a,length(b),substr(b,1+4*900,2),length(d),substr(d,1+6*900,3)
72
a length(b) substr(b,1+4*900,2) length(d) substr(d,1+6*900,3)
74
update t1 set d=null where a=1;
76
select a from t1 where d is null;
79
delete from t1 where a=1;
80
delete from t1 where a=2;
82
select count(*) from t1;
85
replace t1 set a=1,b=@b1,c=111,d=@d1;
86
replace t1 set a=2,b=@b2,c=222,d=@d2;
88
explain select * from t1 where a = 1;
89
id select_type table type possible_keys key key_len ref rows Extra
90
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
91
select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
93
a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3)
95
select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3)
97
a length(b) substr(b,1+2*9000,2) length(d) substr(d,1+3*9000,3)
99
replace t1 set a=1,b=@b2,c=111,d=@d2;
100
replace t1 set a=2,b=@b1,c=222,d=@d1;
102
select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3)
104
a length(b) substr(b,1+2*9000,2) length(d) substr(d,1+3*9000,3)
106
select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
108
a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3)
110
replace t1 set a=1,b=concat(@b2,@b2),c=111,d=concat(@d2,@d2);
111
replace t1 set a=2,b=concat(@b1,@b1),c=222,d=concat(@d1,@d1);
113
select a,length(b),substr(b,1+4*9000,2),length(d),substr(d,1+6*9000,3)
115
a length(b) substr(b,1+4*9000,2) length(d) substr(d,1+6*9000,3)
117
select a,length(b),substr(b,1+4*900,2),length(d),substr(d,1+6*900,3)
119
a length(b) substr(b,1+4*900,2) length(d) substr(d,1+6*900,3)
121
replace t1 set a=1,b='xyz',c=111,d=null;
123
select a,b from t1 where d is null;
126
delete from t1 where a=1;
127
delete from t1 where a=2;
129
select count(*) from t1;
132
insert into t1 values(1,@b1,111,@d1);
133
insert into t1 values(2,@b2,222,@d2);
135
explain select * from t1 where c = 111;
136
id select_type table type possible_keys key key_len ref rows Extra
137
1 SIMPLE t1 ref c c 4 const 1
138
select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
140
a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3)
142
select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3)
144
a length(b) substr(b,1+2*9000,2) length(d) substr(d,1+3*9000,3)
146
update t1 set b=@b2,d=@d2 where c=111;
147
update t1 set b=@b1,d=@d1 where c=222;
149
select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3)
151
a length(b) substr(b,1+2*9000,2) length(d) substr(d,1+3*9000,3)
153
select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
155
a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3)
157
update t1 set d=null where c=111;
159
select a from t1 where d is null;
162
delete from t1 where c=111;
163
delete from t1 where c=222;
165
select count(*) from t1;
168
insert into t1 values(1,'b1',111,'dd1');
169
insert into t1 values(2,'b2',222,'dd2');
170
insert into t1 values(3,'b3',333,'dd3');
171
insert into t1 values(4,'b4',444,'dd4');
172
insert into t1 values(5,'b5',555,'dd5');
173
insert into t1 values(6,'b6',666,'dd6');
174
insert into t1 values(7,'b7',777,'dd7');
175
insert into t1 values(8,'b8',888,'dd8');
176
insert into t1 values(9,'b9',999,'dd9');
178
explain select * from t1;
179
id select_type table type possible_keys key key_len ref rows Extra
180
1 SIMPLE t1 ALL NULL NULL NULL NULL 9
181
select * from t1 order by a;
192
update t1 set b=concat(a,'x',b),d=concat(a,'x',d);
194
select * from t1 order by a;
207
select count(*) from t1;
210
insert into t1 values(1,@b1,111,@d1);
211
insert into t1 values(2,@b2,222,@d2);
213
explain select * from t1;
214
id select_type table type possible_keys key key_len ref rows Extra
215
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
216
select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
218
a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3)
221
update t1 set b=concat(b,b),d=concat(d,d);
223
select a,length(b),substr(b,1+4*9000,2),length(d),substr(d,1+6*9000,3)
225
a length(b) substr(b,1+4*9000,2) length(d) substr(d,1+6*9000,3)
230
select count(*) from t1;
233
insert into t1 values(1,'b1',111,'dd1');
234
insert into t1 values(2,'b2',222,'dd2');
235
insert into t1 values(3,'b3',333,'dd3');
236
insert into t1 values(4,'b4',444,'dd4');
237
insert into t1 values(5,'b5',555,'dd5');
238
insert into t1 values(6,'b6',666,'dd6');
239
insert into t1 values(7,'b7',777,'dd7');
240
insert into t1 values(8,'b8',888,'dd8');
241
insert into t1 values(9,'b9',999,'dd9');
243
explain select * from t1 where c >= 100 order by a;
244
id select_type table type possible_keys key key_len ref rows Extra
245
1 SIMPLE t1 range c c 4 NULL 9 Using where; Using filesort
246
select * from t1 where c >= 100 order by a;
257
update t1 set b=concat(a,'x',b),d=concat(a,'x',d)
260
select * from t1 where c >= 100 order by a;
271
delete from t1 where c >= 100;
273
select count(*) from t1;
276
insert into t1 values(1,@b1,111,@d1);
277
insert into t1 values(2,@b2,222,@d2);
279
explain select * from t1 where c >= 100 order by a;
280
id select_type table type possible_keys key key_len ref rows Extra
281
1 SIMPLE t1 range c c 4 NULL 2 Using where; Using filesort
282
select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
283
from t1 where c >= 100 order by a;
284
a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3)
287
update t1 set b=concat(b,b),d=concat(d,d);
289
select a,length(b),substr(b,1+4*9000,2),length(d),substr(d,1+6*9000,3)
290
from t1 where c >= 100 order by a;
291
a length(b) substr(b,1+4*9000,2) length(d) substr(d,1+6*9000,3)
294
delete from t1 where c >= 100;
296
select count(*) from t1;
299
insert into t1 values(1,@b1,111,@d1);
300
insert into t1 values(2,@b2,222,@d2);
301
select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
303
a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3)
304
select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
306
a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3)
308
select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
310
a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3)
312
select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3)
314
a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3)
318
select count(*) from t1;
321
insert into t1 values(1,'b1',111,'dd1');
322
insert into t1 values(2,'b2',222,'dd2');
323
insert into t1 values(3,'b3',333,'dd3');
324
insert into t1 values(4,'b4',444,'dd4');
325
insert into t1 values(5,'b5',555,'dd5');
326
insert into t1 values(6,'b6',666,'dd6');
327
insert into t1 values(7,'b7',777,'dd7');
328
insert into t1 values(8,'b8',888,'dd8');
329
insert into t1 values(9,'b9',999,'dd9');
331
select * from t1 order by a;
342
alter table t1 add x int;
343
select * from t1 order by a;
354
alter table t1 drop x;
355
select * from t1 order by a;
366
create database test2;
369
a bigint unsigned NOT NULL PRIMARY KEY,
370
b int unsigned not null,
373
insert into t2 values (1,1,1),(2,2,2);
374
select * from test.t1,t2 where test.t1.a = t2.a order by test.t1.a;
380
select * from t1 order by a;
391
alter table t1 add x int;
392
select * from t1 order by a;
403
alter table t1 drop x;
404
select * from t1 order by a;
419
a int not null primary key,
422
insert into t1 values(1, 'x');
423
update t1 set b = 'y';
431
replace t1 set a=2, b='y';
441
a int not null primary key,
444
insert into t1 values(1, '');
456
) ENGINE=PBXT DEFAULT CHARSET=latin1;
457
INSERT INTO t1 VALUES
458
(1,'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
459
INSERT INTO t1 VALUES
460
(2,'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB');
461
select * from t1 order by a;
463
1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
464
2 BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
465
alter table t1 engine=ndb;
466
select * from t1 order by a;
468
1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
469
2 BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
471
alter table t1 engine=myisam;
472
select * from t1 order by a;
474
1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
475
2 BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
478
id int(11) unsigned primary key NOT NULL auto_increment,
480
) engine=ndbcluster default charset=utf8;
481
insert into t1 (msg) values(
482
'Tries to validate (8 byte length + inline bytes) as UTF8 :(
483
Fast fix: removed validation for Text. It is not yet indexable
484
so bad data will not crash kernel.');
487
1 Tries to validate (8 byte length + inline bytes) as UTF8 :(
488
Fast fix: removed validation for Text. It is not yet indexable
489
so bad data will not crash kernel.
492
a int primary key not null auto_increment,
495
select count(*) from t1;
499
select count(*) from t1;
504
a varchar(40) not null,
505
b mediumint not null,
507
c varchar(2) not null,
513
insert into t1 (a,b,c,d,t) values ('a',1110,'a',1,@v1);
514
insert into t1 (a,b,c,d,t) values ('b',1110,'a',2,@v2);
515
insert into t1 (a,b,c,d,t) values ('a',1110,'b',3,@v3);
516
insert into t1 (a,b,c,d,t) values ('b',1110,'b',4,@v4);
517
select a,b,c,d,sha1(t) from t1 order by c,a;
519
a 1110 a 1 558a30713786aa72f66abc1e6a521d55aacdeeb5
520
b 1110 a 2 b238654911689bfb626a3ef9dba4a1ca074e6a5e
521
a 1110 b 3 2b6515f29c20b8e9e17cc597527e516c0de8d612
523
select a,b,c,d,sha1(t) from t1 where a='a' and b=1110 and c='a';
525
a 1110 a 1 558a30713786aa72f66abc1e6a521d55aacdeeb5
526
select a,b,c,d,sha1(t) from t1 where a='a' and b=1110 and c='b';
528
a 1110 b 3 2b6515f29c20b8e9e17cc597527e516c0de8d612
529
update t1 set t=@v4 where a='b' and b=1110 and c='a';
530
update t1 set t=@v2 where a='b' and b=1110 and c='b';
531
select a,b,c,d,sha1(t) from t1 order by c,a;
533
a 1110 a 1 558a30713786aa72f66abc1e6a521d55aacdeeb5
535
a 1110 b 3 2b6515f29c20b8e9e17cc597527e516c0de8d612
536
b 1110 b 4 b238654911689bfb626a3ef9dba4a1ca074e6a5e
537
update t1 set t=@v2 where d=2;
538
update t1 set t=@v4 where d=4;
539
select a,b,c,d,sha1(t) from t1 order by c,a;
541
a 1110 a 1 558a30713786aa72f66abc1e6a521d55aacdeeb5
542
b 1110 a 2 b238654911689bfb626a3ef9dba4a1ca074e6a5e
543
a 1110 b 3 2b6515f29c20b8e9e17cc597527e516c0de8d612
545
update t1 set t=@v4 where a='b' and c='a';
546
update t1 set t=@v2 where a='b' and c='b';
547
select a,b,c,d,sha1(t) from t1 order by c,a;
549
a 1110 a 1 558a30713786aa72f66abc1e6a521d55aacdeeb5
551
a 1110 b 3 2b6515f29c20b8e9e17cc597527e516c0de8d612
552
b 1110 b 4 b238654911689bfb626a3ef9dba4a1ca074e6a5e
553
update t1 set t=@v2 where b+d=1112;
554
update t1 set t=@v4 where b+d=1114;
555
select a,b,c,d,sha1(t) from t1 order by c,a;
557
a 1110 a 1 558a30713786aa72f66abc1e6a521d55aacdeeb5
558
b 1110 a 2 b238654911689bfb626a3ef9dba4a1ca074e6a5e
559
a 1110 b 3 2b6515f29c20b8e9e17cc597527e516c0de8d612
561
delete from t1 where a='a' and b=1110 and c='a';
562
delete from t1 where a='b' and c='a';
563
delete from t1 where d=3;
564
delete from t1 where b+d=1114;
565
select count(*) from t1;