1
SET SESSION STORAGE_ENGINE = MEMORY;
2
drop table if exists t1,t2,t3,t4;
3
drop database if exists mysqltest;
4
create TEMPORARY table t1 (id int not null auto_increment, code int not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=MyISAM;
5
insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt');
6
select id, code, name from t1 order by id;
15
update ignore t1 set id = 8, name = 'Sinisa' where id < 3;
16
select id, code, name from t1 order by id;
25
update ignore t1 set id = id + 10, name = 'Ralph' where id < 4;
26
select id, code, name from t1 order by id;
36
create TEMPORARY table t1 (
37
id int NOT NULL auto_increment,
38
parent_id int DEFAULT '0' NOT NULL,
39
level int DEFAULT '0' NOT NULL,
41
KEY parent_id (parent_id),
44
INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1),(179,5,2);
45
update t1 set parent_id=parent_id+100;
46
select * from t1 where parent_id=102;
51
update t1 set id=id+1000;
52
update t1 set id=1024 where id=1009;
53
Got one of the listed errors
95
update ignore t1 set id=id+1;
137
update ignore t1 set id=1023 where id=1010;
138
select * from t1 where parent_id=102;
143
explain select level from t1 where level=1;
144
id select_type table type possible_keys key key_len ref rows Extra
145
1 SIMPLE t1 ref level level 4 const # Using index
146
explain select level,id from t1 where level=1;
147
id select_type table type possible_keys key key_len ref rows Extra
148
1 SIMPLE t1 ref level level 4 const #
149
explain select level,id,parent_id from t1 where level=1;
150
id select_type table type possible_keys key key_len ref rows Extra
151
1 SIMPLE t1 ref level level 4 const #
152
select level,id from t1 where level=1;
160
select level,id,parent_id from t1 where level=1;
168
alter table t1 engine=MyISAM;
170
create TEMPORARY table t1 (
171
gesuchnr int DEFAULT '0' NOT NULL,
172
benutzer_id int DEFAULT '0' NOT NULL,
173
PRIMARY KEY (gesuchnr,benutzer_id)
175
replace into t1 (gesuchnr,benutzer_id) values (2,1);
176
replace into t1 (gesuchnr,benutzer_id) values (1,1);
177
replace into t1 (gesuchnr,benutzer_id) values (1,1);
183
create TEMPORARY table t1 (a int) engine=MyISAM;
184
insert into t1 values (1), (2);
185
alter table t1 ENGINE=MyISAM;
186
delete from t1 where a = 1;
191
Table Op Msg_type Msg_text
192
test.t1 check status OK
194
create TEMPORARY table t1 (a int,b varchar(20)) engine=MyISAM;
195
insert into t1 values (1,""), (2,"testing");
196
delete from t1 where a = 1;
200
create index skr on t1 (a);
201
insert into t1 values (3,""), (4,"testing");
203
Table Op Msg_type Msg_text
204
test.t1 analyze note The storage engine for the table doesn't support analyze
206
create TEMPORARY table t1 (a int,b varchar(20),key(a)) engine=MyISAM;
207
insert into t1 values (1,""), (2,"testing");
208
select * from t1 where a = 1;
212
create TEMPORARY table t1 (
213
user_id int DEFAULT '0' NOT NULL,
216
ref_email varchar(100) DEFAULT '' NOT NULL,
218
PRIMARY KEY (user_id,ref_email)
220
INSERT INTO t1 VALUES (10292,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10292,'shirish','2333604','shirish@yahoo.com','ddsds'),(10292,'sonali','323232','sonali@bolly.com','filmstar');
221
select * from t1 where user_id=10292;
222
user_id name phone ref_email detail
223
10292 sanjeev 29153373 sansh777@hotmail.com xxx
224
10292 shirish 2333604 shirish@yahoo.com ddsds
225
10292 sonali 323232 sonali@bolly.com filmstar
226
INSERT INTO t1 VALUES (10291,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10293,'shirish','2333604','shirish@yahoo.com','ddsds');
227
select * from t1 where user_id=10292;
228
user_id name phone ref_email detail
229
10292 sanjeev 29153373 sansh777@hotmail.com xxx
230
10292 shirish 2333604 shirish@yahoo.com ddsds
231
10292 sonali 323232 sonali@bolly.com filmstar
232
select * from t1 where user_id>=10292;
233
user_id name phone ref_email detail
234
10292 sanjeev 29153373 sansh777@hotmail.com xxx
235
10292 shirish 2333604 shirish@yahoo.com ddsds
236
10292 sonali 323232 sonali@bolly.com filmstar
237
10293 shirish 2333604 shirish@yahoo.com ddsds
238
select * from t1 where user_id>10292;
239
user_id name phone ref_email detail
240
10293 shirish 2333604 shirish@yahoo.com ddsds
241
select * from t1 where user_id<10292;
242
user_id name phone ref_email detail
243
10291 sanjeev 29153373 sansh777@hotmail.com xxx
245
create TEMPORARY table t1 (a int not null, b int not null,c int not null,
246
key(a),primary key(a,b), unique(c),key(a),unique(b)) ENGINE = MyISAM;
248
create TEMPORARY table t1 (col1 int not null, col2 char(4) not null, primary key(col1)) ENGINE = MEMORY;
249
alter table t1 engine=MyISAM;
250
insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4');
258
update t1 set col2='7' where col1='4';
266
alter table t1 add co3 int not null;
274
update t1 set col2='9' where col1='2';
283
create TEMPORARY table t1 (a int not null , b int, primary key (a)) engine = MyISAM;
284
create TEMPORARY table t2 (a int not null , b int, primary key (a)) engine = MEMORY;
285
insert into t1 VALUES (1,3) , (2,3), (3,3);
291
insert into t2 select * from t1;
297
delete from t1 where b = 3;
300
insert into t1 select * from t2;
312
create TEMPORARY table t1 (
313
id int NOT NULL auto_increment,
314
ggid varchar(32) DEFAULT '' NOT NULL,
315
email varchar(64) DEFAULT '' NOT NULL,
316
passwd varchar(32) DEFAULT '' NOT NULL,
320
insert into t1 (ggid,passwd) values ('test1','xxx');
321
insert into t1 (ggid,passwd) values ('test2','yyy');
322
insert into t1 (ggid,passwd) values ('test2','this will fail');
323
ERROR 23000: Duplicate entry 'test2' for key 'ggid'
324
insert into t1 (ggid,id) values ('this will fail',1);
325
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
326
select * from t1 where ggid='test1';
329
select * from t1 where passwd='xxx';
332
select * from t1 where id=2;
335
replace into t1 (ggid,id) values ('this will work',1);
336
replace into t1 (ggid,passwd) values ('test2','this will work');
337
update t1 set id=100,ggid='test2' where id=1;
338
ERROR 23000: Duplicate entry 'test2' for key 'ggid'
342
3 test2 this will work
343
select * from t1 where id=1;
346
select * from t1 where id=999;
349
create TEMPORARY table t1 (
350
user_name varchar(12),
353
user_id int DEFAULT '0' NOT NULL,
358
dummy_primary_key int NOT NULL auto_increment,
359
PRIMARY KEY (dummy_primary_key)
361
INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','2000-09-07 23:06:59',1);
362
INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','2000-09-07 23:06:59',2);
363
INSERT INTO t1 VALUES ('user_2','somepassword','N',2,2,1.4142135623731,'2000-09-07','2000-09-07 23:06:59',3);
364
INSERT INTO t1 VALUES ('user_3','somepassword','Y',3,3,1.7320508075689,'2000-09-07','2000-09-07 23:06:59',4);
365
INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','2000-09-07 23:06:59',5);
366
select user_name, password , subscribed, user_id, quota, weight, access_date, approved, dummy_primary_key from t1 order by user_name;
367
user_name password subscribed user_id quota weight access_date approved dummy_primary_key
368
user_0 somepassword N 0 0 0 2000-09-07 2000-09-07 23:06:59 1
369
user_1 somepassword Y 1 1 1 2000-09-07 2000-09-07 23:06:59 2
370
user_2 somepassword N 2 2 1.4142135623731 2000-09-07 2000-09-07 23:06:59 3
371
user_3 somepassword Y 3 3 1.7320508075689 2000-09-07 2000-09-07 23:06:59 4
372
user_4 somepassword N 4 4 2 2000-09-07 2000-09-07 23:06:59 5
374
create TEMPORARY table t1 (
375
id int NOT NULL auto_increment,
376
parent_id int DEFAULT '0' NOT NULL,
377
level int DEFAULT '0' NOT NULL,
379
KEY parent_id (parent_id),
382
INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1);
383
INSERT INTO t1 values (179,5,2);
384
update t1 set parent_id=parent_id+100;
385
select * from t1 where parent_id=102;
390
update t1 set id=id+1000;
391
update t1 set id=1024 where id=1009;
433
update ignore t1 set id=id+1;
475
update ignore t1 set id=1023 where id=1010;
476
select * from t1 where parent_id=102;
481
explain select level from t1 where level=1;
482
id select_type table type possible_keys key key_len ref rows Extra
483
1 SIMPLE t1 ref level level 4 const # Using index
484
select level,id from t1 where level=1;
492
select level,id,parent_id from t1 where level=1;
500
select level,id from t1 where level=1 order by id;
508
delete from t1 where level=1;
545
create TEMPORARY table t1 (
546
sca_code char(6) NOT NULL,
547
cat_code char(6) NOT NULL,
548
sca_desc varchar(50),
549
lan_code char(2) NOT NULL,
550
sca_pic varchar(100),
551
sca_sdesc varchar(50),
552
sca_sch_desc varchar(16),
553
PRIMARY KEY (sca_code, cat_code, lan_code),
554
INDEX sca_pic (sca_pic)
556
INSERT INTO t1 ( sca_code, cat_code, sca_desc, lan_code, sca_pic, sca_sdesc, sca_sch_desc) VALUES ( 'PD', 'J', 'PENDANT', 'EN', NULL, NULL, 'PENDANT'),( 'RI', 'J', 'RING', 'EN', NULL, NULL, 'RING'),( 'QQ', 'N', 'RING', 'EN', 'not null', NULL, 'RING');
557
select count(*) from t1 where sca_code = 'PD';
560
select count(*) from t1 where sca_code <= 'PD';
563
select count(*) from t1 where sca_pic is null;
566
alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic);
567
select count(*) from t1 where sca_code='PD' and sca_pic is null;
570
select count(*) from t1 where cat_code='E';
573
alter table t1 drop index sca_pic, add index (sca_pic, cat_code);
574
select count(*) from t1 where sca_code='PD' and sca_pic is null;
577
select count(*) from t1 where sca_pic >= 'n';
580
select sca_pic from t1 where sca_pic is null;
584
update t1 set sca_pic="test" where sca_pic is null;
585
delete from t1 where sca_code='pd';
588
create TEMPORARY table t1 (a varchar(100) not null, primary key(a), b int not null) engine=MyISAM;
589
insert into t1 values("hello",1),("world",2);
590
select * from t1 order by b desc;
594
alter table t1 ENGINE=MyISAM;
596
create TEMPORARY table t1 (i int, j int ) ENGINE=MyISAM;
597
insert into t1 values (1,2);
598
select * from t1 where i=1 and j=2;
601
create index ax1 on t1 (i,j);
602
select * from t1 where i=1 and j=2;
606
create TEMPORARY table t1 (
611
INSERT INTO t1 VALUES (1, 1);
612
SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1;
616
create TEMPORARY table t1 (a int NOT NULL) engine=MyISAM;
617
INSERT INTO t1 VALUES (1);
622
create TEMPORARY table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int, a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, b1 int, b2 int, b3 int, b4 int, b5 int, b6 int) engine = MyISAM;
623
insert into t1 values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);
624
explain select * from t1 where a > 0 and a < 50;
625
id select_type table type possible_keys key key_len ref rows Extra
626
1 SIMPLE t1 system PRIMARY NULL NULL NULL #
628
create TEMPORARY table t1 (a char(20), unique (a(5))) engine=MyISAM;
630
create TEMPORARY table t1 (a char(20), index (a(5))) engine=MyISAM;
631
show create table t1;
633
t1 CREATE TEMPORARY TABLE `t1` (
634
`a` varchar(20) DEFAULT NULL,
638
create temporary table t1 (a int not null auto_increment, primary key(a)) engine=MyISAM;
639
insert into t1 values (NULL),(NULL),(NULL);
640
delete from t1 where a=3;
641
insert into t1 values (NULL);
647
alter table t1 add b int;
654
create TEMPORARY table t1
656
id int auto_increment primary key,
657
name varchar(32) not null,
662
insert into t1 values (1,'one','one value',101),
663
(2,'two','two value',102),(3,'three','three value',103);
664
replace into t1 (value,name,uid) values ('other value','two',102);
665
delete from t1 where uid=102;
666
replace into t1 (value,name,uid) values ('other value','two',102);
667
replace into t1 (value,name,uid) values ('other value','two',102);
671
6 two other value 102
672
3 three three value 103
674
create database mysqltest;
675
create TEMPORARY table mysqltest.t1 (a int not null) engine= MyISAM;
676
insert into mysqltest.t1 values(1);
677
create TEMPORARY table mysqltest.t2 (a int not null) engine= MEMORY;
678
insert into mysqltest.t2 values(1);
679
create TEMPORARY table mysqltest.t3 (a int not null) engine= MEMORY;
680
insert into mysqltest.t3 values(1);
682
drop database mysqltest;
683
show tables from mysqltest;
684
ERROR 42000: Unknown database 'mysqltest'
686
create TEMPORARY table t1 (a int not null) engine= MyISAM;
687
insert into t1 values(1),(2);
694
insert into t1 values(1),(2);
701
create TEMPORARY table t1 (a int not null) engine= MyISAM;
702
insert into t1 values(1),(2);
704
insert into t1 values(1),(2);
710
insert into t1 values(1),(2);
715
create TEMPORARY table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=MyISAM;
716
insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4);
717
explain select * from t1 order by a;
718
id select_type table type possible_keys key key_len ref rows Extra
719
1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort
720
explain select * from t1 order by b;
721
id select_type table type possible_keys key key_len ref rows Extra
722
1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort
723
explain select * from t1 order by c;
724
id select_type table type possible_keys key key_len ref rows Extra
725
1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort
726
explain select a from t1 order by a;
727
id select_type table type possible_keys key key_len ref rows Extra
728
1 SIMPLE t1 index NULL PRIMARY 4 NULL # Using index
729
explain select b from t1 order by b;
730
id select_type table type possible_keys key key_len ref rows Extra
731
1 SIMPLE t1 index NULL b 4 NULL # Using index
732
explain select a,b from t1 order by b;
733
id select_type table type possible_keys key key_len ref rows Extra
734
1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort
735
explain select a,b from t1;
736
id select_type table type possible_keys key key_len ref rows Extra
737
1 SIMPLE t1 ALL NULL NULL NULL NULL #
738
explain select a,b,c from t1;
739
id select_type table type possible_keys key key_len ref rows Extra
740
1 SIMPLE t1 ALL NULL NULL NULL NULL #
742
create TEMPORARY table t1 (t int not null default 1, key (t)) engine=MyISAM;
744
create TEMPORARY table t1 (id int not null auto_increment, code int not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=MyISAM;
746
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
747
SELECT @@tx_isolation,@@global.tx_isolation;
748
@@tx_isolation @@global.tx_isolation
749
SERIALIZABLE REPEATABLE-READ
750
insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David');
751
select id, code, name from t1 order by id;
758
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
759
insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha');
760
select id, code, name from t1 order by id;
769
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
770
insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt');
771
select id, code, name from t1 order by id;
782
create TEMPORARY table t1 (a int not null primary key, b int not null, unique (b)) engine=MyISAM;
783
INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
784
UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000;
797
create TEMPORARY table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=MyISAM;
798
create TEMPORARY table t2 (a int not null auto_increment primary key, b int) ENGINE = MEMORY;
799
insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null);
800
insert into t2 (a) select b from t1;
801
insert into t1 (b) select b from t2;
802
insert into t2 (a) select b from t1;
803
insert into t1 (a) select b from t2;
804
insert into t2 (a) select b from t1;
805
insert into t1 (a) select b from t2;
806
insert into t2 (a) select b from t1;
807
insert into t1 (a) select b from t2;
808
insert into t2 (a) select b from t1;
809
insert into t1 (a) select b from t2;
810
insert into t2 (a) select b from t1;
811
insert into t1 (a) select b from t2;
812
insert into t2 (a) select b from t1;
813
insert into t1 (a) select b from t2;
814
insert into t2 (a) select b from t1;
815
insert into t1 (a) select b from t2;
816
insert into t2 (a) select b from t1;
817
insert into t1 (a) select b from t2;
818
select count(*) from t1;
821
explain select * from t1 where c between 1 and 2500;
822
id select_type table type possible_keys key key_len ref rows Extra
823
1 SIMPLE t1 range c c 5 NULL # Using where
825
explain select * from t1 where c between 1 and 2500;
826
id select_type table type possible_keys key key_len ref rows Extra
827
1 SIMPLE t1 range c c 5 NULL # Using where
829
create TEMPORARY table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=MyISAM;
830
insert into t1 (id) values (null),(null),(null),(null),(null);
831
update t1 set fk=69 where fk is null order by id limit 1;
840
create TEMPORARY table t1 (a int not null, b int not null, key (a)) engine=MyISAM;
841
insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3);
843
update t1 set b=(@tmp:=@tmp+1) order by a;
844
update t1 set b=99 where a=1 order by b asc limit 1;
845
update t1 set b=100 where a=1 order by b desc limit 2;
846
update t1 set a=a+10+b where a=1 order by b;
847
select * from t1 order by a,b;
863
create TEMPORARY table t1 (a integer auto_increment primary key) engine=MyISAM;
864
insert into t1 (a) values (NULL),(NULL);
866
insert into t1 (a) values (NULL),(NULL);
872
create TEMPORARY table t1 (col1 int)ENGINE=MyISAM;
873
create TEMPORARY table t2 (col1 int,stamp TIMESTAMP,INDEX stamp_idx
874
(stamp))ENGINE=MyISAM;
875
insert into t1 values (1),(2),(3);
876
insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000);
877
ERROR HY000: Received an invalid value '20020204310000' for a UNIX timestamp.
878
SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp <
879
'20020204120000' GROUP BY col1;
885
create TEMPORARY table t1 (a int, b varchar(200), c text not null) engine=MyISAM;
886
create TEMPORARY table t2 (a int, b varchar(200), c text not null) engine=MyISAM;
887
create TEMPORARY table t3 (a int, b varchar(200), c varchar(200) not null) engine=MEMORY;
888
create TEMPORARY table t4 (a int, b varchar(200), c varchar(200) not null) engine=MEMORY;
889
create TEMPORARY table t5 (a int, b varchar(200), c text not null) engine=MyISAM;
890
create TEMPORARY table t6 (a int, b varchar(200), c text not null) engine=MyISAM;
891
insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, "");
892
insert t2 select * from t1;
893
insert t3 select * from t1;
894
insert t4 select * from t1;
895
insert t5 select * from t1;
896
insert t6 select * from t1;
897
checksum table t1, t2, t3, t4, t5, t6, t7;
907
Error 1146 Table 'test.t7' doesn't exist
908
checksum table t1, t2, t3, t4, t5, t6, t7;
918
Error 1146 Table 'test.t7' doesn't exist
919
checksum table t1, t2, t3, t4, t5, t6, t7;
929
Error 1146 Table 'test.t7' doesn't exist
930
drop table t1,t2,t3, t4, t5, t6;
931
create TEMPORARY table t1 (a int) engine=MyISAM;
932
create table t2 like t1;
933
ERROR HY000: Can't create table 'test.t2' (errno: 138)
934
create table t2 like t1 engine=innodb;
935
show create table t2;
937
t2 CREATE TABLE `t2` (
942
show status like "binlog_cache_use";
944
show status like "binlog_cache_disk_use";
946
create TEMPORARY table t1 (a int) engine=MyISAM;
947
show status like "binlog_cache_use";
949
show status like "binlog_cache_disk_use";
954
show status like "binlog_cache_use";
956
show status like "binlog_cache_disk_use";
959
create TEMPORARY table t1 (c char(10), index (c,c)) engine=MyISAM;
960
ERROR 42S21: Duplicate column name 'c'
961
create TEMPORARY table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=MyISAM;
962
ERROR 42S21: Duplicate column name 'c1'
963
create TEMPORARY table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=MyISAM;
964
ERROR 42S21: Duplicate column name 'c1'
965
create TEMPORARY table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=MyISAM;
966
ERROR 42S21: Duplicate column name 'c1'
967
create TEMPORARY table t1 (c1 char(10), c2 char(10)) engine=MyISAM;
968
alter table t1 add key (c1,c1);
969
ERROR 42S21: Duplicate column name 'c1'
970
alter table t1 add key (c2,c1,c1);
971
ERROR 42S21: Duplicate column name 'c1'
972
alter table t1 add key (c1,c2,c1);
973
ERROR 42S21: Duplicate column name 'c1'
974
alter table t1 add key (c1,c1,c2);
975
ERROR 42S21: Duplicate column name 'c1'
977
create TEMPORARY table t1(a int, b int) engine=MyISAM;
978
insert into t1 values ('1111', '3333');
979
select distinct concat(a, b) from t1;
983
create temporary table t1 (a int) engine=MyISAM;
984
insert into t1 values (4711);
986
insert into t1 values (42);
991
create TEMPORARY table t1 (a int) engine=MyISAM;
992
insert into t1 values (4711);
994
insert into t1 values (42);
999
create TEMPORARY table t1 (a int not null, b int not null, c blob not null, d int not null, e int, primary key (a,b,c(255),d)) engine=MyISAM;
1000
insert into t1 values (2,2,"b",2,2),(1,1,"a",1,1),(3,3,"ab",3,3);
1001
select * from t1 order by a,b,c,d;
1006
explain select * from t1 order by a,b,c,d;
1007
id select_type table type possible_keys key key_len ref rows Extra
1008
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
1010
create TEMPORARY table t1 (a char(1), b char(1), key(a, b)) engine=MyISAM;
1011
insert into t1 values ('8', '6'), ('4', '7');
1012
select min(a) from t1;
1015
select min(b) from t1 where a='8';
1019
create TEMPORARY table t1 (x bigint not null primary key) engine=MyISAM;
1020
insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1);
1021
ERROR 22003: Out of range value for column 'x' at row 1
1024
select count(*) from t1 where x>0;
1027
select count(*) from t1 where x=0;
1030
select count(*) from t1 where x<0;
1033
select count(*) from t1 where x < -16;
1036
select count(*) from t1 where x = -16;
1039
explain select count(*) from t1 where x > -16;
1040
id select_type table type possible_keys key key_len ref rows Extra
1041
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
1042
select count(*) from t1 where x > -16;
1045
select * from t1 where x > -16;
1047
select count(*) from t1 where x = 18446744073709551601;
1051
set storage_engine=MyISAM;
1052
drop table if exists t1,t2,t3;
1053
--- Testing varchar ---
1054
--- Testing varchar ---
1055
create TEMPORARY table t1 (v varchar(10), c char(10), t text);
1056
insert into t1 values('+ ', '+ ', '+ ');
1057
set @a=repeat(' ',20);
1058
insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a));
1060
Note 1265 Data truncated for column 'v' at row 1
1061
Note 1265 Data truncated for column 'c' at row 1
1062
select concat('*',v,'*',c,'*',t,'*') from t1;
1063
concat('*',v,'*',c,'*',t,'*')
1066
show create table t1;
1068
t1 CREATE TEMPORARY TABLE `t1` (
1069
`v` varchar(10) DEFAULT NULL,
1070
`c` varchar(10) DEFAULT NULL,
1073
create TEMPORARY table t2 like t1;
1074
show create table t2;
1076
t2 CREATE TEMPORARY TABLE `t2` (
1077
`v` varchar(10) DEFAULT NULL,
1078
`c` varchar(10) DEFAULT NULL,
1081
create TEMPORARY table t3 select * from t1;
1082
show create table t3;
1084
t3 CREATE TEMPORARY TABLE `t3` (
1085
`v` varchar(10) DEFAULT NULL,
1086
`c` varchar(10) DEFAULT NULL,
1089
alter table t1 modify c varchar(10);
1090
show create table t1;
1092
t1 CREATE TEMPORARY TABLE `t1` (
1093
`v` varchar(10) DEFAULT NULL,
1094
`c` varchar(10) DEFAULT NULL,
1097
alter table t1 modify v char(10);
1098
show create table t1;
1100
t1 CREATE TEMPORARY TABLE `t1` (
1101
`v` varchar(10) DEFAULT NULL,
1102
`c` varchar(10) DEFAULT NULL,
1105
alter table t1 modify t varchar(10);
1107
Note 1265 Data truncated for column 't' at row 2
1108
show create table t1;
1110
t1 CREATE TEMPORARY TABLE `t1` (
1111
`v` varchar(10) DEFAULT NULL,
1112
`c` varchar(10) DEFAULT NULL,
1113
`t` varchar(10) DEFAULT NULL
1115
select concat('*',v,'*',c,'*',t,'*') from t1;
1116
concat('*',v,'*',c,'*',t,'*')
1119
drop table t1,t2,t3;
1120
create TEMPORARY table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10)));
1121
show create table t1;
1123
t1 CREATE TEMPORARY TABLE `t1` (
1124
`v` varchar(10) DEFAULT NULL,
1125
`c` varchar(10) DEFAULT NULL,
1131
select count(*) from t1;
1134
insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1)));
1135
select count(*) from t1 where v='a';
1138
select count(*) from t1 where c='a';
1141
select count(*) from t1 where t='a';
1144
select count(*) from t1 where v='a ';
1147
select count(*) from t1 where c='a ';
1150
select count(*) from t1 where t='a ';
1153
select count(*) from t1 where v between 'a' and 'a ';
1156
select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
1159
select count(*) from t1 where v like 'a%';
1162
select count(*) from t1 where c like 'a%';
1165
select count(*) from t1 where t like 'a%';
1168
select count(*) from t1 where v like 'a %';
1171
explain select count(*) from t1 where v='a ';
1172
id select_type table type possible_keys key key_len ref rows Extra
1173
1 SIMPLE t1 ref v v 43 const # Using where; Using index
1174
explain select count(*) from t1 where c='a ';
1175
id select_type table type possible_keys key key_len ref rows Extra
1176
1 SIMPLE t1 ref c c 43 const # Using where; Using index
1177
explain select count(*) from t1 where t='a ';
1178
id select_type table type possible_keys key key_len ref rows Extra
1179
1 SIMPLE t1 ref t t 43 const # Using where
1180
explain select count(*) from t1 where v like 'a%';
1181
id select_type table type possible_keys key key_len ref rows Extra
1182
1 SIMPLE t1 range v v 43 NULL # Using where; Using index
1183
explain select count(*) from t1 where v between 'a' and 'a ';
1184
id select_type table type possible_keys key key_len ref rows Extra
1185
1 SIMPLE t1 ref v v 43 const # Using where; Using index
1186
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
1187
id select_type table type possible_keys key key_len ref rows Extra
1188
1 SIMPLE t1 ref v v 43 const # Using where; Using index
1189
alter table t1 add unique(v);
1190
ERROR 23000: Duplicate entry '{ ' for key 'v_2'
1191
alter table t1 add key(v);
1192
select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a';
1204
explain select * from t1 where v='a';
1205
id select_type table type possible_keys key key_len ref rows Extra
1206
1 SIMPLE t1 ref v,v_2 # 43 const # Using where
1207
select v,count(*) from t1 group by v limit 10;
1219
select v,count(t) from t1 group by v limit 10;
1231
select v,count(c) from t1 group by v limit 10;
1243
select sql_big_result v,count(t) from t1 group by v limit 10;
1255
select sql_big_result v,count(c) from t1 group by v limit 10;
1267
select c,count(*) from t1 group by c limit 10;
1279
select c,count(t) from t1 group by c limit 10;
1291
select sql_big_result c,count(t) from t1 group by c limit 10;
1303
select t,count(*) from t1 group by t limit 10;
1315
select t,count(t) from t1 group by t limit 10;
1327
select sql_big_result t,count(t) from t1 group by t limit 10;
1339
alter table t1 modify v varchar(300), drop key v, drop key v_2, add key v (v);
1340
show create table t1;
1342
t1 CREATE TEMPORARY TABLE `t1` (
1343
`v` varchar(300) DEFAULT NULL,
1344
`c` varchar(10) DEFAULT NULL,
1350
select count(*) from t1 where v='a';
1353
select count(*) from t1 where v='a ';
1356
select count(*) from t1 where v between 'a' and 'a ';
1359
select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
1362
select count(*) from t1 where v like 'a%';
1365
select count(*) from t1 where v like 'a %';
1368
explain select count(*) from t1 where v='a ';
1369
id select_type table type possible_keys key key_len ref rows Extra
1370
1 SIMPLE t1 ref v v 1203 const # Using where; Using index
1371
explain select count(*) from t1 where v like 'a%';
1372
id select_type table type possible_keys key key_len ref rows Extra
1373
1 SIMPLE t1 range v v 1203 NULL # Using where; Using index
1374
explain select count(*) from t1 where v between 'a' and 'a ';
1375
id select_type table type possible_keys key key_len ref rows Extra
1376
1 SIMPLE t1 ref v v 1203 const # Using where; Using index
1377
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
1378
id select_type table type possible_keys key key_len ref rows Extra
1379
1 SIMPLE t1 ref v v 1203 const # Using where; Using index
1380
explain select * from t1 where v='a';
1381
id select_type table type possible_keys key key_len ref rows Extra
1382
1 SIMPLE t1 ref v v 1203 const # Using where
1383
select v,count(*) from t1 group by v limit 10;
1395
select v,count(t) from t1 group by v limit 10;
1407
select sql_big_result v,count(t) from t1 group by v limit 10;
1419
alter table t1 drop key v, add key v (v(30));
1420
show create table t1;
1422
t1 CREATE TEMPORARY TABLE `t1` (
1423
`v` varchar(300) DEFAULT NULL,
1424
`c` varchar(10) DEFAULT NULL,
1430
select count(*) from t1 where v='a';
1433
select count(*) from t1 where v='a ';
1436
select count(*) from t1 where v between 'a' and 'a ';
1439
select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
1442
select count(*) from t1 where v like 'a%';
1445
select count(*) from t1 where v like 'a %';
1448
explain select count(*) from t1 where v='a ';
1449
id select_type table type possible_keys key key_len ref rows Extra
1450
1 SIMPLE t1 ref v v 123 const # Using where
1451
explain select count(*) from t1 where v like 'a%';
1452
id select_type table type possible_keys key key_len ref rows Extra
1453
1 SIMPLE t1 range v v 123 NULL # Using where
1454
explain select count(*) from t1 where v between 'a' and 'a ';
1455
id select_type table type possible_keys key key_len ref rows Extra
1456
1 SIMPLE t1 ref v v 123 const # Using where
1457
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
1458
id select_type table type possible_keys key key_len ref rows Extra
1459
1 SIMPLE t1 ref v v 123 const # Using where
1460
explain select * from t1 where v='a';
1461
id select_type table type possible_keys key key_len ref rows Extra
1462
1 SIMPLE t1 ref v v 123 const # Using where
1463
select v,count(*) from t1 group by v limit 10;
1475
select v,count(t) from t1 group by v limit 10;
1487
select sql_big_result v,count(t) from t1 group by v limit 10;
1499
alter table t1 modify v varchar(600), drop key v, add key v (v);
1501
Warning 1071 Specified key was too long; max key length is 1332 bytes
1502
show create table t1;
1504
t1 CREATE TEMPORARY TABLE `t1` (
1505
`v` varchar(600) DEFAULT NULL,
1506
`c` varchar(10) DEFAULT NULL,
1512
select v,count(*) from t1 group by v limit 10;
1524
select v,count(t) from t1 group by v limit 10;
1536
select sql_big_result v,count(t) from t1 group by v limit 10;
1549
create TEMPORARY table t1 (a char(10), unique (a));
1550
insert into t1 values ('a ');
1551
insert into t1 values ('a ');
1552
ERROR 23000: Duplicate entry 'a ' for key 'a'
1553
alter table t1 modify a varchar(10);
1554
insert into t1 values ('a '),('a '),('a '),('a ');
1555
ERROR 23000: Duplicate entry 'a ' for key 'a'
1556
insert into t1 values ('a ');
1557
ERROR 23000: Duplicate entry 'a ' for key 'a'
1558
insert into t1 values ('a ');
1559
ERROR 23000: Duplicate entry 'a ' for key 'a'
1560
insert into t1 values ('a ');
1561
ERROR 23000: Duplicate entry 'a ' for key 'a'
1562
update t1 set a='a ' where a like 'a%';
1563
select concat(a,'.') from t1;
1566
update t1 set a='abc ' where a like 'a ';
1567
select concat(a,'.') from t1;
1570
update t1 set a='a ' where a like 'a %';
1571
select concat(a,'.') from t1;
1574
update t1 set a='a ' where a like 'a ';
1575
select concat(a,'.') from t1;
1579
create TEMPORARY table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5)));
1580
show create table t1;
1582
t1 CREATE TEMPORARY TABLE `t1` (
1583
`v` varchar(10) DEFAULT NULL,
1584
`c` varchar(10) DEFAULT NULL,
1591
create TEMPORARY table t1 (v char(10));
1592
show create table t1;
1594
t1 CREATE TEMPORARY TABLE `t1` (
1595
`v` varchar(10) DEFAULT NULL
1598
create TEMPORARY table t1 (v varchar(10), c char(10));
1599
show create table t1;
1601
t1 CREATE TEMPORARY TABLE `t1` (
1602
`v` varchar(10) DEFAULT NULL,
1603
`c` varchar(10) DEFAULT NULL
1605
insert into t1 values('a','a'),('a ','a ');
1606
select concat('*',v,'*',c,'*') from t1;
1607
concat('*',v,'*',c,'*')
1611
create TEMPORARY table t1(a int, b varchar(12), key ba(b, a));
1612
insert into t1 values (1, 'A'), (20, NULL);
1613
explain select * from t1 where a=20 and b is null;
1614
id select_type table type possible_keys key key_len ref rows Extra
1615
1 SIMPLE t1 ref ba ba 56 const,const 1 Using where; Using index
1616
select * from t1 where a=20 and b is null;
1620
create TEMPORARY table t1 (a char(1), b char(1), key(a, b)) engine=MyISAM;
1621
insert into t1 values ('8', '6'), ('4', '7');
1622
select min(a) from t1;
1625
select min(b) from t1 where a='8';
1629
create TEMPORARY table t1 ( `a` int NOT NULL auto_increment, `b` int default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=MyISAM;
1630
insert into t1 (b) values (1);
1631
replace into t1 (b) values (2), (1), (3);
1638
insert into t1 (b) values (1);
1639
replace into t1 (b) values (2);
1640
replace into t1 (b) values (1);
1641
replace into t1 (b) values (3);
1648
create TEMPORARY table t1 (rowid int not null auto_increment, val int not null,primary
1649
key (rowid), unique(val)) engine=MyISAM;
1650
replace into t1 (val) values ('1'),('2');
1651
replace into t1 (val) values ('1'),('2');
1652
insert into t1 (val) values ('1'),('2');
1653
ERROR 23000: Duplicate entry '1' for key 'val'
1659
create TEMPORARY table t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=MyISAM;
1660
INSERT INTO t1 (GRADE) VALUES (151),(252),(343);
1661
SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300;
1664
SELECT GRADE FROM t1 WHERE GRADE= 151;
1668
create TEMPORARY table t1(a date) engine=MyISAM;
1669
create TEMPORARY table t2(a date, key(a)) engine=MyISAM;
1670
insert into t1 values('2005-10-01');
1671
insert into t2 values('2005-10-01');
1672
select * from t1, t2
1673
where t2.a between t1.a - interval 2 day and t1.a + interval 2 day;
1675
2005-10-01 2005-10-01
1677
create TEMPORARY table t1 (id int not null, f_id int not null, f int not null,
1678
primary key(f_id, id)) engine=MyISAM;
1679
create TEMPORARY table t2 (id int not null,s_id int not null,s varchar(200),
1680
primary key(id)) engine=MyISAM;
1681
INSERT INTO t1 VALUES (8, 1, 3);
1682
INSERT INTO t1 VALUES (1, 2, 1);
1683
INSERT INTO t2 VALUES (1, 0, '');
1684
INSERT INTO t2 VALUES (8, 1, '');
1686
select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)
1687
where mm.id is null lock in share mode;
1690
create TEMPORARY table t1 ( a int ) ENGINE=MyISAM;
1692
INSERT INTO t1 VALUES (1);
1693
ALTER TABLE t1 ENGINE=MyISAM;