~drizzle-trunk/drizzle/development

SET SESSION STORAGE_ENGINE = MEMORY;
drop table if exists t1,t2,t3,t4;
drop database if exists mysqltest;
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;
insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt');
select id, code, name from t1 order by id;
id	code	name
1	1	Tim
2	1	Monty
3	2	David
4	2	Erik
5	3	Sasha
6	3	Jeremy
7	4	Matt
update ignore t1 set id = 8, name = 'Sinisa' where id < 3;
select id, code, name from t1 order by id;
id	code	name
2	1	Monty
3	2	David
4	2	Erik
5	3	Sasha
6	3	Jeremy
7	4	Matt
8	1	Sinisa
update ignore t1 set id = id + 10, name = 'Ralph' where id < 4;
select id, code, name from t1 order by id;
id	code	name
3	2	David
4	2	Erik
5	3	Sasha
6	3	Jeremy
7	4	Matt
8	1	Sinisa
12	1	Ralph
drop table t1;
create TEMPORARY table t1 (
id int NOT NULL auto_increment,
parent_id int DEFAULT '0' NOT NULL,
level int DEFAULT '0' NOT NULL,
PRIMARY KEY (id),
KEY parent_id (parent_id),
KEY level (level)
) engine=MyISAM;
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);
update t1 set parent_id=parent_id+100;
select * from t1 where parent_id=102;
id	parent_id	level
8	102	2
9	102	2
15	102	2
update t1 set id=id+1000;
update t1 set id=1024 where id=1009;
Got one of the listed errors
select * from t1;
id	parent_id	level
1001	100	0
1003	101	1
1004	101	1
1008	102	2
1009	102	2
1017	103	2
1022	104	2
1024	104	2
1028	105	2
1029	105	2
1030	105	2
1031	106	2
1032	106	2
1033	106	2
1203	107	2
1202	107	2
1020	103	2
1157	100	0
1193	105	2
1040	107	2
1002	101	1
1015	102	2
1006	101	1
1034	106	2
1035	106	2
1016	103	2
1007	101	1
1036	107	2
1018	103	2
1026	105	2
1027	105	2
1183	104	2
1038	107	2
1025	105	2
1037	107	2
1021	104	2
1019	103	2
1005	101	1
1179	105	2
update ignore t1 set id=id+1;
select * from t1;
id	parent_id	level
1001	100	0
1003	101	1
1004	101	1
1008	102	2
1010	102	2
1017	103	2
1023	104	2
1024	104	2
1028	105	2
1029	105	2
1030	105	2
1031	106	2
1032	106	2
1033	106	2
1204	107	2
1203	107	2
1020	103	2
1158	100	0
1194	105	2
1041	107	2
1002	101	1
1015	102	2
1006	101	1
1034	106	2
1035	106	2
1016	103	2
1007	101	1
1036	107	2
1018	103	2
1026	105	2
1027	105	2
1184	104	2
1039	107	2
1025	105	2
1038	107	2
1022	104	2
1019	103	2
1005	101	1
1180	105	2
update ignore t1 set id=1023 where id=1010;
select * from t1 where parent_id=102;
id	parent_id	level
1008	102	2
1010	102	2
1015	102	2
explain select level from t1 where level=1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	level	level	4	const	#	Using index
explain select level,id from t1 where level=1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	level	level	4	const	#	
explain select level,id,parent_id from t1 where level=1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	level	level	4	const	#	
select level,id from t1 where level=1;
level	id
1	1003
1	1004
1	1002
1	1006
1	1007
1	1005
select level,id,parent_id from t1 where level=1;
level	id	parent_id
1	1003	101
1	1004	101
1	1002	101
1	1006	101
1	1007	101
1	1005	101
alter table t1 engine=MyISAM;
show keys from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_Comment
t1	0	PRIMARY	1	id	A	#	NULL	NULL		BTREE		
t1	1	parent_id	1	parent_id	A	#	NULL	NULL		BTREE		
t1	1	level	1	level	A	#	NULL	NULL		BTREE		
drop table t1;
create TEMPORARY table t1 (
gesuchnr int DEFAULT '0' NOT NULL,
benutzer_id int DEFAULT '0' NOT NULL,
PRIMARY KEY (gesuchnr,benutzer_id)
) engine=MyISAM;
replace into t1 (gesuchnr,benutzer_id) values (2,1);
replace into t1 (gesuchnr,benutzer_id) values (1,1);
replace into t1 (gesuchnr,benutzer_id) values (1,1);
select * from t1;
gesuchnr	benutzer_id
1	1
2	1
drop table t1;
create TEMPORARY table t1 (a int) engine=MyISAM;
insert into t1 values (1), (2);
alter table t1 ENGINE=MyISAM;
delete from t1 where a = 1;
select * from t1;
a
2
check table t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
drop table t1;
create TEMPORARY table t1 (a int,b varchar(20)) engine=MyISAM;
insert into t1 values (1,""), (2,"testing");
delete from t1 where a = 1;
select * from t1;
a	b
2	testing
create index skr on t1 (a);
insert into t1 values (3,""), (4,"testing");
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	note	The storage engine for the table doesn't support analyze
show keys from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_Comment
t1	1	skr	1	a	A	#	NULL	NULL	YES	BTREE		
drop table t1;
create TEMPORARY table t1 (a int,b varchar(20),key(a)) engine=MyISAM;
insert into t1 values (1,""), (2,"testing");
select * from t1 where a = 1;
a	b
1	
drop table t1;
create TEMPORARY table t1 (
user_id int DEFAULT '0' NOT NULL,
name varchar(100),
phone varchar(100),
ref_email varchar(100) DEFAULT '' NOT NULL,
detail varchar(200),
PRIMARY KEY (user_id,ref_email)
)engine=MyISAM;
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');
select * from t1 where user_id=10292;
user_id	name	phone	ref_email	detail
10292	sanjeev	29153373	sansh777@hotmail.com	xxx
10292	shirish	2333604	shirish@yahoo.com	ddsds
10292	sonali	323232	sonali@bolly.com	filmstar
INSERT INTO t1 VALUES (10291,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10293,'shirish','2333604','shirish@yahoo.com','ddsds');
select * from t1 where user_id=10292;
user_id	name	phone	ref_email	detail
10292	sanjeev	29153373	sansh777@hotmail.com	xxx
10292	shirish	2333604	shirish@yahoo.com	ddsds
10292	sonali	323232	sonali@bolly.com	filmstar
select * from t1 where user_id>=10292;
user_id	name	phone	ref_email	detail
10292	sanjeev	29153373	sansh777@hotmail.com	xxx
10292	shirish	2333604	shirish@yahoo.com	ddsds
10292	sonali	323232	sonali@bolly.com	filmstar
10293	shirish	2333604	shirish@yahoo.com	ddsds
select * from t1 where user_id>10292;
user_id	name	phone	ref_email	detail
10293	shirish	2333604	shirish@yahoo.com	ddsds
select * from t1 where user_id<10292;
user_id	name	phone	ref_email	detail
10291	sanjeev	29153373	sansh777@hotmail.com	xxx
drop table t1;
create TEMPORARY table t1 (a int not null, b int not null,c int not null,
key(a),primary key(a,b), unique(c),key(a),unique(b)) ENGINE = MyISAM;
show index from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_Comment
t1	0	PRIMARY	1	a	A	#	NULL	NULL		BTREE		
t1	0	PRIMARY	2	b	A	#	NULL	NULL		BTREE		
t1	0	c	1	c	A	#	NULL	NULL		BTREE		
t1	0	b	1	b	A	#	NULL	NULL		BTREE		
t1	1	a	1	a	A	#	NULL	NULL		BTREE		
t1	1	a_2	1	a	A	#	NULL	NULL		BTREE		
drop table t1;
create TEMPORARY table t1 (col1 int not null, col2 char(4) not null, primary key(col1)) ENGINE = MEMORY;
alter table t1 engine=MyISAM;
insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4');
select * from t1;
col1	col2
1	1
5	2
2	3
3	4
4	4
update t1 set col2='7' where col1='4';
select * from t1;
col1	col2
1	1
5	2
2	3
3	4
4	7
alter table t1 add co3 int not null;
select * from t1;
col1	col2	co3
1	1	0
5	2	0
2	3	0
3	4	0
4	7	0
update t1 set col2='9' where col1='2';
select * from t1;
col1	col2	co3
1	1	0
5	2	0
2	9	0
3	4	0
4	7	0
drop table t1;
create TEMPORARY table t1 (a int not null , b int, primary key (a)) engine = MyISAM;
create TEMPORARY table t2 (a int not null , b int, primary key (a)) engine = MEMORY;
insert into t1 VALUES (1,3) , (2,3), (3,3);
select * from t1;
a	b
1	3
2	3
3	3
insert into t2 select * from t1;
select * from t2;
a	b
1	3
2	3
3	3
delete from t1 where b = 3;
select * from t1;
a	b
insert into t1 select * from t2;
select * from t1;
a	b
3	3
2	3
1	3
select * from t2;
a	b
1	3
2	3
3	3
drop table t1,t2;
create TEMPORARY table t1 (
id int NOT NULL auto_increment,
ggid varchar(32) DEFAULT '' NOT NULL,
email varchar(64) DEFAULT '' NOT NULL,
passwd varchar(32) DEFAULT '' NOT NULL,
PRIMARY KEY (id),
UNIQUE ggid (ggid)
) ENGINE=MyISAM;
insert into t1 (ggid,passwd) values ('test1','xxx');
insert into t1 (ggid,passwd) values ('test2','yyy');
insert into t1 (ggid,passwd) values ('test2','this will fail');
ERROR 23000: Duplicate entry 'test2' for key 'ggid'
insert into t1 (ggid,id) values ('this will fail',1);
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
select * from t1 where ggid='test1';
id	ggid	email	passwd
1	test1		xxx
select * from t1 where passwd='xxx';
id	ggid	email	passwd
1	test1		xxx
select * from t1 where id=2;
id	ggid	email	passwd
2	test2		yyy
replace into t1 (ggid,id) values ('this will work',1);
replace into t1 (ggid,passwd) values ('test2','this will work');
update t1 set id=100,ggid='test2' where id=1;
ERROR 23000: Duplicate entry 'test2' for key 'ggid'
select * from t1;
id	ggid	email	passwd
1	this will work		
3	test2		this will work
select * from t1 where id=1;
id	ggid	email	passwd
1	this will work		
select * from t1 where id=999;
id	ggid	email	passwd
drop table t1;
create TEMPORARY table t1 (
user_name varchar(12),
password text,
subscribed char(1),
user_id int DEFAULT '0' NOT NULL,
quota bigint,
weight double,
access_date date,
approved datetime,
dummy_primary_key int NOT NULL auto_increment,
PRIMARY KEY (dummy_primary_key)
) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','2000-09-07 23:06:59',1);
INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','2000-09-07 23:06:59',2);
INSERT INTO t1 VALUES ('user_2','somepassword','N',2,2,1.4142135623731,'2000-09-07','2000-09-07 23:06:59',3);
INSERT INTO t1 VALUES ('user_3','somepassword','Y',3,3,1.7320508075689,'2000-09-07','2000-09-07 23:06:59',4);
INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','2000-09-07 23:06:59',5);
select  user_name, password , subscribed, user_id, quota, weight, access_date, approved, dummy_primary_key from t1 order by user_name;
user_name	password	subscribed	user_id	quota	weight	access_date	approved	dummy_primary_key
user_0	somepassword	N	0	0	0	2000-09-07	2000-09-07 23:06:59	1
user_1	somepassword	Y	1	1	1	2000-09-07	2000-09-07 23:06:59	2
user_2	somepassword	N	2	2	1.4142135623731	2000-09-07	2000-09-07 23:06:59	3
user_3	somepassword	Y	3	3	1.7320508075689	2000-09-07	2000-09-07 23:06:59	4
user_4	somepassword	N	4	4	2	2000-09-07	2000-09-07 23:06:59	5
drop table t1;
create TEMPORARY table t1 (
id int NOT NULL auto_increment,
parent_id int DEFAULT '0' NOT NULL,
level int DEFAULT '0' NOT NULL,
KEY (id),
KEY parent_id (parent_id),
KEY level (level)
) engine=MyISAM;
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);
INSERT INTO t1 values (179,5,2);
update t1 set parent_id=parent_id+100;
select * from t1 where parent_id=102;
id	parent_id	level
8	102	2
9	102	2
15	102	2
update t1 set id=id+1000;
update t1 set id=1024 where id=1009;
select * from t1;
id	parent_id	level
1001	100	0
1003	101	1
1004	101	1
1008	102	2
1024	102	2
1017	103	2
1022	104	2
1024	104	2
1028	105	2
1029	105	2
1030	105	2
1031	106	2
1032	106	2
1033	106	2
1203	107	2
1202	107	2
1020	103	2
1157	100	0
1193	105	2
1040	107	2
1002	101	1
1015	102	2
1006	101	1
1034	106	2
1035	106	2
1016	103	2
1007	101	1
1036	107	2
1018	103	2
1026	105	2
1027	105	2
1183	104	2
1038	107	2
1025	105	2
1037	107	2
1021	104	2
1019	103	2
1005	101	1
1179	105	2
update ignore t1 set id=id+1;
select * from t1;
id	parent_id	level
1002	100	0
1004	101	1
1005	101	1
1009	102	2
1025	102	2
1018	103	2
1023	104	2
1025	104	2
1029	105	2
1030	105	2
1031	105	2
1032	106	2
1033	106	2
1034	106	2
1204	107	2
1203	107	2
1021	103	2
1158	100	0
1194	105	2
1041	107	2
1003	101	1
1016	102	2
1007	101	1
1035	106	2
1036	106	2
1017	103	2
1008	101	1
1037	107	2
1019	103	2
1027	105	2
1028	105	2
1184	104	2
1039	107	2
1026	105	2
1038	107	2
1022	104	2
1020	103	2
1006	101	1
1180	105	2
update ignore t1 set id=1023 where id=1010;
select * from t1 where parent_id=102;
id	parent_id	level
1009	102	2
1025	102	2
1016	102	2
explain select level from t1 where level=1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	level	level	4	const	#	Using index
select level,id from t1 where level=1;
level	id
1	1004
1	1005
1	1003
1	1007
1	1008
1	1006
select level,id,parent_id from t1 where level=1;
level	id	parent_id
1	1004	101
1	1005	101
1	1003	101
1	1007	101
1	1008	101
1	1006	101
select level,id from t1 where level=1 order by id;
level	id
1	1003
1	1004
1	1005
1	1006
1	1007
1	1008
delete from t1 where level=1;
select * from t1;
id	parent_id	level
1002	100	0
1009	102	2
1025	102	2
1018	103	2
1023	104	2
1025	104	2
1029	105	2
1030	105	2
1031	105	2
1032	106	2
1033	106	2
1034	106	2
1204	107	2
1203	107	2
1021	103	2
1158	100	0
1194	105	2
1041	107	2
1016	102	2
1035	106	2
1036	106	2
1017	103	2
1037	107	2
1019	103	2
1027	105	2
1028	105	2
1184	104	2
1039	107	2
1026	105	2
1038	107	2
1022	104	2
1020	103	2
1180	105	2
drop table t1;
create TEMPORARY table t1 (
sca_code char(6) NOT NULL,
cat_code char(6) NOT NULL,
sca_desc varchar(50),
lan_code char(2) NOT NULL,
sca_pic varchar(100),
sca_sdesc varchar(50),
sca_sch_desc varchar(16),
PRIMARY KEY (sca_code, cat_code, lan_code),
INDEX sca_pic (sca_pic)
) engine = MyISAM ;
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');
select count(*) from t1 where sca_code = 'PD';
count(*)
1
select count(*) from t1 where sca_code <= 'PD';
count(*)
1
select count(*) from t1 where sca_pic is null;
count(*)
2
alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic);
select count(*) from t1 where sca_code='PD' and sca_pic is null;
count(*)
1
select count(*) from t1 where cat_code='E';
count(*)
0
alter table t1 drop index sca_pic, add index (sca_pic, cat_code);
select count(*) from t1 where sca_code='PD' and sca_pic is null;
count(*)
1
select count(*) from t1 where sca_pic >= 'n';
count(*)
1
select sca_pic from t1 where sca_pic is null;
sca_pic
NULL
NULL
update t1 set sca_pic="test" where sca_pic is null;
delete from t1 where sca_code='pd';
drop table t1;
TEMPORARY
create TEMPORARY table t1 (a varchar(100) not null, primary key(a), b int not null) engine=MyISAM;
insert into t1 values("hello",1),("world",2);
select * from t1 order by b desc;
a	b
world	2
hello	1
alter table t1 ENGINE=MyISAM;
show keys from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_Comment
t1	0	PRIMARY	1	a	A	#	NULL	NULL		BTREE		
drop table t1;
create TEMPORARY table t1 (i int, j int ) ENGINE=MyISAM;
insert into t1 values (1,2);
select * from t1 where i=1 and j=2;
i	j
1	2
create index ax1 on t1 (i,j);
select * from t1 where i=1 and j=2;
i	j
1	2
drop table t1;
create TEMPORARY table t1 (
a int NOT NULL,
b int NOT NULL,
UNIQUE (a, b)
) ENGINE = MyISAM;
INSERT INTO t1 VALUES (1, 1);
SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1;
MIN(B)	MAX(b)
1	1
drop table t1;
create TEMPORARY table t1 (a int NOT NULL) engine=MyISAM;
INSERT INTO t1 VALUES (1);
SELECT * FROM t1;
a
1
DROP TABLE t1;
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;
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);
explain select * from t1 where a > 0 and a < 50;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	#	
drop table t1;
create TEMPORARY table t1 (a char(20), unique (a(5))) engine=MyISAM;
drop table t1;
create TEMPORARY table t1 (a char(20), index (a(5))) engine=MyISAM;
show create table t1;
Table	Create Table
t1	CREATE TEMPORARY TABLE `t1` (
  `a` varchar(20) DEFAULT NULL,
  KEY `a` (`a`(5))
) ENGINE=MyISAM
drop table t1;
create temporary table t1 (a int not null auto_increment, primary key(a)) engine=MyISAM;
insert into t1 values (NULL),(NULL),(NULL);
delete from t1 where a=3;
insert into t1 values (NULL);
select * from t1;
a
1
2
4
alter table t1 add b int;
select * from t1;
a	b
1	NULL
2	NULL
4	NULL
drop table t1;
create TEMPORARY table t1
(
id int auto_increment primary key,
name varchar(32) not null,
value text not null,
uid int not null,
unique key(name,uid)
) engine=MyISAM;
insert into t1 values (1,'one','one value',101),
(2,'two','two value',102),(3,'three','three value',103);
replace into t1 (value,name,uid) values ('other value','two',102);
delete from t1 where uid=102;
replace into t1 (value,name,uid) values ('other value','two',102);
replace into t1 (value,name,uid) values ('other value','two',102);
select * from t1;
id	name	value	uid
1	one	one value	101
6	two	other value	102
3	three	three value	103
drop table t1;
create database mysqltest;
create TEMPORARY table mysqltest.t1 (a int not null) engine= MyISAM;
insert into mysqltest.t1 values(1);
create TEMPORARY table mysqltest.t2 (a int not null) engine= MEMORY;
insert into mysqltest.t2 values(1);
create TEMPORARY table mysqltest.t3 (a int not null) engine= MEMORY;
insert into mysqltest.t3 values(1);
commit;
drop database mysqltest;
show tables from mysqltest;
ERROR 42000: Unknown database 'mysqltest'
set autocommit=0;
create TEMPORARY table t1 (a int not null) engine= MyISAM;
insert into t1 values(1),(2);
truncate table t1;
commit;
truncate table t1;
truncate table t1;
select * from t1;
a
insert into t1 values(1),(2);
delete from t1;
select * from t1;
a
commit;
drop table t1;
set autocommit=1;
create TEMPORARY table t1 (a int not null) engine= MyISAM;
insert into t1 values(1),(2);
truncate table t1;
insert into t1 values(1),(2);
select * from t1;
a
1
2
truncate table t1;
insert into t1 values(1),(2);
delete from t1;
select * from t1;
a
drop table t1;
create TEMPORARY table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=MyISAM;
insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4);
explain select * from t1 order by a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	#	Using filesort
explain select * from t1 order by b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	#	Using filesort
explain select * from t1 order by c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	#	Using filesort
explain select a from t1 order by a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	#	Using index
explain select b from t1 order by b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	b	4	NULL	#	Using index
explain select a,b from t1 order by b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	#	Using filesort
explain select a,b from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	#	
explain select a,b,c from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	#	
drop table t1;
create TEMPORARY table t1 (t int not null default 1, key (t)) engine=MyISAM;
desc t1;
Field	Type	Null	Key	Default	Extra
t	int	NO	MUL	1	
drop table t1;
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;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT @@tx_isolation,@@global.tx_isolation;
@@tx_isolation	@@global.tx_isolation
SERIALIZABLE	REPEATABLE-READ
insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David');
select id, code, name from t1 order by id;
id	code	name
1	1	Tim
2	1	Monty
3	2	David
COMMIT;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha');
select id, code, name from t1 order by id;
id	code	name
1	1	Tim
2	1	Monty
3	2	David
4	2	Erik
5	3	Sasha
COMMIT;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt');
select id, code, name from t1 order by id;
id	code	name
1	1	Tim
2	1	Monty
3	2	David
4	2	Erik
5	3	Sasha
6	3	Jeremy
7	4	Matt
COMMIT;
DROP TABLE t1;
create TEMPORARY table t1 (a int not null primary key, b int not null, unique (b)) engine=MyISAM;
INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000;
SELECT * from t1;
a	b
1	1
102	2
103	3
4	4
5	5
6	6
7	7
8	8
9	9
drop table t1;
create TEMPORARY table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=MyISAM;
create TEMPORARY table t2 (a int not null auto_increment primary key, b int) ENGINE = MEMORY;
insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null);
insert into t2 (a) select b from t1;
insert into t1 (b) select b from t2;
insert into t2 (a) select b from t1;
insert into t1 (a) select b from t2;
insert into t2 (a) select b from t1;
insert into t1 (a) select b from t2;
insert into t2 (a) select b from t1;
insert into t1 (a) select b from t2;
insert into t2 (a) select b from t1;
insert into t1 (a) select b from t2;
insert into t2 (a) select b from t1;
insert into t1 (a) select b from t2;
insert into t2 (a) select b from t1;
insert into t1 (a) select b from t2;
insert into t2 (a) select b from t1;
insert into t1 (a) select b from t2;
insert into t2 (a) select b from t1;
insert into t1 (a) select b from t2;
select count(*) from t1;
count(*)
29267
explain select * from t1 where c between 1 and 2500;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	c	c	5	NULL	#	Using where
update t1 set c=a;
explain select * from t1 where c between 1 and 2500;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	c	c	5	NULL	#	Using where
drop table t1,t2;
create TEMPORARY table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=MyISAM;
insert into t1 (id) values (null),(null),(null),(null),(null);
update t1 set fk=69 where fk is null order by id limit 1;
SELECT * from t1;
id	fk
1	69
2	NULL
3	NULL
4	NULL
5	NULL
drop table t1;
create TEMPORARY table t1 (a int not null, b int not null, key (a)) engine=MyISAM;
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);
SET @tmp=0;
update t1 set b=(@tmp:=@tmp+1) order by a;
update t1 set b=99 where a=1 order by b asc limit 1;
update t1 set b=100 where a=1 order by b desc limit 2;
update t1 set a=a+10+b where a=1 order by b;
select * from t1 order by a,b;
a	b
2	4
2	5
2	6
3	7
3	8
3	9
3	10
3	11
3	12
13	2
111	100
111	100
drop table t1;
SET AUTOCOMMIT=1;
create TEMPORARY table t1 (a integer auto_increment primary key) engine=MyISAM;
insert into t1 (a) values (NULL),(NULL);
truncate table t1;
insert into t1 (a) values (NULL),(NULL);
SELECT * from t1;
a
1
2
drop table t1;
create TEMPORARY table t1 (col1 int)ENGINE=MyISAM;
create TEMPORARY table t2 (col1 int,stamp TIMESTAMP,INDEX stamp_idx
(stamp))ENGINE=MyISAM;
insert into t1 values (1),(2),(3);
insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000);
ERROR HY000: Received an invalid value '20020204310000' for a UNIX timestamp.
SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp <
'20020204120000' GROUP BY col1;
col1
1
2
3
drop table t1,t2;
create TEMPORARY table t1 (a int, b varchar(200), c text not null) engine=MyISAM;
create TEMPORARY table t2 (a int, b varchar(200), c text not null) engine=MyISAM;
create TEMPORARY table t3 (a int, b varchar(200), c varchar(200) not null) engine=MEMORY;
create TEMPORARY table t4 (a int, b varchar(200), c varchar(200) not null) engine=MEMORY;
create TEMPORARY table t5 (a int, b varchar(200), c text not null) engine=MyISAM;
create TEMPORARY table t6 (a int, b varchar(200), c text not null) engine=MyISAM;
insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, "");
insert t2 select * from t1;
insert t3 select * from t1;
insert t4 select * from t1;
insert t5 select * from t1;
insert t6 select * from t1;
checksum table t1, t2, t3, t4, t5, t6, t7;
Table	Checksum
test.t1	2948697075
test.t2	2948697075
test.t3	2948697075
test.t4	2948697075
test.t5	2948697075
test.t6	2948697075
test.t7	NULL
Warnings:
Error	1146	Table 'test.t7' doesn't exist
checksum table t1, t2, t3, t4, t5, t6, t7;
Table	Checksum
test.t1	2948697075
test.t2	2948697075
test.t3	2948697075
test.t4	2948697075
test.t5	2948697075
test.t6	2948697075
test.t7	NULL
Warnings:
Error	1146	Table 'test.t7' doesn't exist
checksum table t1, t2, t3, t4, t5, t6, t7;
Table	Checksum
test.t1	2948697075
test.t2	2948697075
test.t3	2948697075
test.t4	2948697075
test.t5	2948697075
test.t6	2948697075
test.t7	NULL
Warnings:
Error	1146	Table 'test.t7' doesn't exist
drop table t1,t2,t3, t4, t5, t6;
create TEMPORARY table t1 (a int) engine=MyISAM;
create table t2 like t1;
ERROR HY000: Can't create table 'test.t2' (errno: 138)
create table t2 like t1 engine=innodb;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int DEFAULT NULL
) ENGINE=InnoDB
drop table t1,t2;
flush status;
show status like "binlog_cache_use";
Variable_name	Value
show status like "binlog_cache_disk_use";
Variable_name	Value
create TEMPORARY table t1 (a int) engine=MyISAM;
show status like "binlog_cache_use";
Variable_name	Value
show status like "binlog_cache_disk_use";
Variable_name	Value
begin;
delete from t1;
commit;
show status like "binlog_cache_use";
Variable_name	Value
show status like "binlog_cache_disk_use";
Variable_name	Value
drop table t1;
create TEMPORARY table t1 (c char(10), index (c,c)) engine=MyISAM;
ERROR 42S21: Duplicate column name 'c'
create TEMPORARY table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=MyISAM;
ERROR 42S21: Duplicate column name 'c1'
create TEMPORARY table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=MyISAM;
ERROR 42S21: Duplicate column name 'c1'
create TEMPORARY table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=MyISAM;
ERROR 42S21: Duplicate column name 'c1'
create TEMPORARY table t1 (c1 char(10), c2 char(10)) engine=MyISAM;
alter table t1 add key (c1,c1);
ERROR 42S21: Duplicate column name 'c1'
alter table t1 add key (c2,c1,c1);
ERROR 42S21: Duplicate column name 'c1'
alter table t1 add key (c1,c2,c1);
ERROR 42S21: Duplicate column name 'c1'
alter table t1 add key (c1,c1,c2);
ERROR 42S21: Duplicate column name 'c1'
drop table t1;
create TEMPORARY table t1(a int, b int) engine=MyISAM;
insert into t1 values ('1111', '3333');
select distinct concat(a, b) from t1;
concat(a, b)
11113333
drop table t1;
create temporary table t1 (a int) engine=MyISAM;
insert into t1 values (4711);
truncate t1;
insert into t1 values (42);
select * from t1;
a
42
drop table t1;
create TEMPORARY table t1 (a int) engine=MyISAM;
insert into t1 values (4711);
truncate t1;
insert into t1 values (42);
select * from t1;
a
42
drop table t1;
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;
insert into t1 values (2,2,"b",2,2),(1,1,"a",1,1),(3,3,"ab",3,3);
select * from t1 order by a,b,c,d;
a	b	c	d	e
1	1	a	1	1
2	2	b	2	2
3	3	ab	3	3
explain select * from t1 order by a,b,c,d;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using filesort
drop table t1;
create TEMPORARY table t1 (a char(1), b char(1), key(a, b)) engine=MyISAM;
insert into t1 values ('8', '6'), ('4', '7');
select min(a) from t1;
min(a)
4
select min(b) from t1 where a='8';
min(b)
6
drop table t1;
create TEMPORARY table t1 (x bigint not null primary key) engine=MyISAM;
insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1);
ERROR 22003: Out of range value for column 'x' at row 1
select * from t1;
x
select count(*) from t1 where x>0;
count(*)
0
select count(*) from t1 where x=0;
count(*)
0
select count(*) from t1 where x<0;
count(*)
0
select count(*) from t1 where x < -16;
count(*)
0
select count(*) from t1 where x = -16;
count(*)
0
explain select count(*) from t1 where x > -16;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
select count(*) from t1 where x > -16;
count(*)
0
select * from t1 where x > -16;
x
select count(*) from t1 where x = 18446744073709551601;
count(*)
0
drop table t1;
set storage_engine=MyISAM;
drop table if exists t1,t2,t3;
--- Testing varchar ---
--- Testing varchar ---
create TEMPORARY table t1 (v varchar(10), c char(10), t text);
insert into t1 values('+ ', '+ ', '+ ');
set @a=repeat(' ',20);
insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a));
Warnings:
Note	1265	Data truncated for column 'v' at row 1
Note	1265	Data truncated for column 'c' at row 1
select concat('*',v,'*',c,'*',t,'*') from t1;
concat('*',v,'*',c,'*',t,'*')
*+ *+ *+ *
*+         *+         *+                    *
show create table t1;
Table	Create Table
t1	CREATE TEMPORARY TABLE `t1` (
  `v` varchar(10) DEFAULT NULL,
  `c` varchar(10) DEFAULT NULL,
  `t` text
) ENGINE=MyISAM
create TEMPORARY table t2 like t1;
show create table t2;
Table	Create Table
t2	CREATE TEMPORARY TABLE `t2` (
  `v` varchar(10) DEFAULT NULL,
  `c` varchar(10) DEFAULT NULL,
  `t` text
) ENGINE=MyISAM
create TEMPORARY table t3 select * from t1;
show create table t3;
Table	Create Table
t3	CREATE TEMPORARY TABLE `t3` (
  `v` varchar(10) DEFAULT NULL,
  `c` varchar(10) DEFAULT NULL,
  `t` text
) ENGINE=MyISAM
alter table t1 modify c varchar(10);
show create table t1;
Table	Create Table
t1	CREATE TEMPORARY TABLE `t1` (
  `v` varchar(10) DEFAULT NULL,
  `c` varchar(10) DEFAULT NULL,
  `t` text
) ENGINE=MyISAM
alter table t1 modify v char(10);
show create table t1;
Table	Create Table
t1	CREATE TEMPORARY TABLE `t1` (
  `v` varchar(10) DEFAULT NULL,
  `c` varchar(10) DEFAULT NULL,
  `t` text
) ENGINE=MyISAM
alter table t1 modify t varchar(10);
Warnings:
Note	1265	Data truncated for column 't' at row 2
show create table t1;
Table	Create Table
t1	CREATE TEMPORARY TABLE `t1` (
  `v` varchar(10) DEFAULT NULL,
  `c` varchar(10) DEFAULT NULL,
  `t` varchar(10) DEFAULT NULL
) ENGINE=MyISAM
select concat('*',v,'*',c,'*',t,'*') from t1;
concat('*',v,'*',c,'*',t,'*')
*+ *+ *+ *
*+         *+         *+         *
drop table t1,t2,t3;
create TEMPORARY table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10)));
show create table t1;
Table	Create Table
t1	CREATE TEMPORARY TABLE `t1` (
  `v` varchar(10) DEFAULT NULL,
  `c` varchar(10) DEFAULT NULL,
  `t` text,
  KEY `v` (`v`),
  KEY `c` (`c`),
  KEY `t` (`t`(10))
) ENGINE=MyISAM
select count(*) from t1;
count(*)
270
insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1)));
select count(*) from t1 where v='a';
count(*)
10
select count(*) from t1 where c='a';
count(*)
10
select count(*) from t1 where t='a';
count(*)
10
select count(*) from t1 where v='a  ';
count(*)
10
select count(*) from t1 where c='a  ';
count(*)
10
select count(*) from t1 where t='a  ';
count(*)
10
select count(*) from t1 where v between 'a' and 'a ';
count(*)
10
select count(*) from t1 where v between 'a' and 'a ' and v between 'a  ' and 'b\n';
count(*)
10
select count(*) from t1 where v like 'a%';
count(*)
11
select count(*) from t1 where c like 'a%';
count(*)
11
select count(*) from t1 where t like 'a%';
count(*)
11
select count(*) from t1 where v like 'a %';
count(*)
9
explain select count(*) from t1 where v='a  ';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	v	v	43	const	#	Using where; Using index
explain select count(*) from t1 where c='a  ';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	c	c	43	const	#	Using where; Using index
explain select count(*) from t1 where t='a  ';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	t	t	43	const	#	Using where
explain select count(*) from t1 where v like 'a%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	43	NULL	#	Using where; Using index
explain select count(*) from t1 where v between 'a' and 'a ';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	v	v	43	const	#	Using where; Using index
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a  ' and 'b\n';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	v	v	43	const	#	Using where; Using index
alter table t1 add unique(v);
ERROR 23000: Duplicate entry '{ ' for key 'v_2'
alter table t1 add key(v);
select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a';
qq
*a*a*a*
*a *a *a *
*a  *a  *a  *
*a   *a   *a   *
*a    *a    *a    *
*a     *a     *a     *
*a      *a      *a      *
*a       *a       *a       *
*a        *a        *a        *
*a         *a         *a         *
explain select * from t1 where v='a';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	v,v_2	#	43	const	#	Using where
select v,count(*) from t1 group by v limit 10;
v	count(*)
a
	1
a	10
b	10
c	10
d	10
e	10
f	10
g	10
h	10
i	10
select v,count(t) from t1 group by v limit 10;
v	count(t)
a
	1
a	10
b	10
c	10
d	10
e	10
f	10
g	10
h	10
i	10
select v,count(c) from t1 group by v limit 10;
v	count(c)
a
	1
a	10
b	10
c	10
d	10
e	10
f	10
g	10
h	10
i	10
select sql_big_result v,count(t) from t1 group by v limit 10;
v	count(t)
a
	1
a	10
b	10
c	10
d	10
e	10
f	10
g	10
h	10
i	10
select sql_big_result v,count(c) from t1 group by v limit 10;
v	count(c)
a
	1
a 	10
b     	10
c    	10
d   	10
e  	10
f     	10
g    	10
h	10
i     	10
select c,count(*) from t1 group by c limit 10;
c	count(*)
a
	1
a	10
b	10
c	10
d	10
e	10
f	10
g	10
h	10
i	10
select c,count(t) from t1 group by c limit 10;
c	count(t)
a
	1
a	10
b	10
c	10
d	10
e	10
f	10
g	10
h	10
i	10
select sql_big_result c,count(t) from t1 group by c limit 10;
c	count(t)
a
	1
a	10
b	10
c	10
d	10
e	10
f	10
g	10
h	10
i	10
select t,count(*) from t1 group by t limit 10;
t	count(*)
a
	1
a	10
b	10
c	10
d	10
e	10
f	10
g	10
h	10
i	10
select t,count(t) from t1 group by t limit 10;
t	count(t)
a
	1
a	10
b	10
c	10
d	10
e	10
f	10
g	10
h	10
i	10
select sql_big_result t,count(t) from t1 group by t limit 10;
t	count(t)
a
	1
a	10
b	10
c	10
d	10
e	10
f	10
g	10
h	10
i	10
alter table t1 modify v varchar(300), drop key v, drop key v_2, add key v (v);
show create table t1;
Table	Create Table
t1	CREATE TEMPORARY TABLE `t1` (
  `v` varchar(300) DEFAULT NULL,
  `c` varchar(10) DEFAULT NULL,
  `t` text,
  KEY `c` (`c`),
  KEY `t` (`t`(10)),
  KEY `v` (`v`)
) ENGINE=MyISAM
select count(*) from t1 where v='a';
count(*)
10
select count(*) from t1 where v='a  ';
count(*)
10
select count(*) from t1 where v between 'a' and 'a ';
count(*)
10
select count(*) from t1 where v between 'a' and 'a ' and v between 'a  ' and 'b\n';
count(*)
10
select count(*) from t1 where v like 'a%';
count(*)
11
select count(*) from t1 where v like 'a %';
count(*)
9
explain select count(*) from t1 where v='a  ';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	v	v	1203	const	#	Using where; Using index
explain select count(*) from t1 where v like 'a%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	1203	NULL	#	Using where; Using index
explain select count(*) from t1 where v between 'a' and 'a ';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	v	v	1203	const	#	Using where; Using index
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a  ' and 'b\n';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	v	v	1203	const	#	Using where; Using index
explain select * from t1 where v='a';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	v	v	1203	const	#	Using where
select v,count(*) from t1 group by v limit 10;
v	count(*)
a
	1
a	10
b	10
c	10
d	10
e	10
f	10
g	10
h	10
i	10
select v,count(t) from t1 group by v limit 10;
v	count(t)
a
	1
a	10
b	10
c	10
d	10
e	10
f	10
g	10
h	10
i	10
select sql_big_result v,count(t) from t1 group by v limit 10;
v	count(t)
a
	1
a	10
b	10
c	10
d	10
e	10
f	10
g	10
h	10
i	10
alter table t1 drop key v, add key v (v(30));
show create table t1;
Table	Create Table
t1	CREATE TEMPORARY TABLE `t1` (
  `v` varchar(300) DEFAULT NULL,
  `c` varchar(10) DEFAULT NULL,
  `t` text,
  KEY `c` (`c`),
  KEY `t` (`t`(10)),
  KEY `v` (`v`(30))
) ENGINE=MyISAM
select count(*) from t1 where v='a';
count(*)
10
select count(*) from t1 where v='a  ';
count(*)
10
select count(*) from t1 where v between 'a' and 'a ';
count(*)
10
select count(*) from t1 where v between 'a' and 'a ' and v between 'a  ' and 'b\n';
count(*)
10
select count(*) from t1 where v like 'a%';
count(*)
11
select count(*) from t1 where v like 'a %';
count(*)
9
explain select count(*) from t1 where v='a  ';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	v	v	123	const	#	Using where
explain select count(*) from t1 where v like 'a%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	123	NULL	#	Using where
explain select count(*) from t1 where v between 'a' and 'a ';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	v	v	123	const	#	Using where
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a  ' and 'b\n';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	v	v	123	const	#	Using where
explain select * from t1 where v='a';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	v	v	123	const	#	Using where
select v,count(*) from t1 group by v limit 10;
v	count(*)
a
	1
a	10
b	10
c	10
d	10
e	10
f	10
g	10
h	10
i	10
select v,count(t) from t1 group by v limit 10;
v	count(t)
a
	1
a	10
b	10
c	10
d	10
e	10
f	10
g	10
h	10
i	10
select sql_big_result v,count(t) from t1 group by v limit 10;
v	count(t)
a
	1
a	10
b	10
c	10
d	10
e	10
f	10
g	10
h	10
i	10
alter table t1 modify v varchar(600), drop key v, add key v (v);
Warnings:
Warning	1071	Specified key was too long; max key length is 1332 bytes
show create table t1;
Table	Create Table
t1	CREATE TEMPORARY TABLE `t1` (
  `v` varchar(600) DEFAULT NULL,
  `c` varchar(10) DEFAULT NULL,
  `t` text,
  KEY `c` (`c`),
  KEY `t` (`t`(10)),
  KEY `v` (`v`(333))
) ENGINE=MyISAM
select v,count(*) from t1 group by v limit 10;
v	count(*)
a
	1
a	10
b	10
c	10
d	10
e	10
f	10
g	10
h	10
i	10
select v,count(t) from t1 group by v limit 10;
v	count(t)
a
	1
a	10
b	10
c	10
d	10
e	10
f	10
g	10
h	10
i	10
select sql_big_result v,count(t) from t1 group by v limit 10;
v	count(t)
a
	1
a	10
b	10
c	10
d	10
e	10
f	10
g	10
h	10
i	10
drop table t1;
create TEMPORARY table t1 (a char(10), unique (a));
insert into t1 values ('a   ');
insert into t1 values ('a ');
ERROR 23000: Duplicate entry 'a ' for key 'a'
alter table t1 modify a varchar(10);
insert into t1 values ('a '),('a  '),('a   '),('a         ');
ERROR 23000: Duplicate entry 'a ' for key 'a'
insert into t1 values ('a     ');
ERROR 23000: Duplicate entry 'a     ' for key 'a'
insert into t1 values ('a          ');
ERROR 23000: Duplicate entry 'a         ' for key 'a'
insert into t1 values ('a ');
ERROR 23000: Duplicate entry 'a ' for key 'a'
update t1 set a='a  ' where a like 'a%';
select concat(a,'.') from t1;
concat(a,'.')
a  .
update t1 set a='abc    ' where a like 'a ';
select concat(a,'.') from t1;
concat(a,'.')
a  .
update t1 set a='a      ' where a like 'a %';
select concat(a,'.') from t1;
concat(a,'.')
a      .
update t1 set a='a  ' where a like 'a      ';
select concat(a,'.') from t1;
concat(a,'.')
a  .
drop table t1;
create TEMPORARY table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5)));
show create table t1;
Table	Create Table
t1	CREATE TEMPORARY TABLE `t1` (
  `v` varchar(10) DEFAULT NULL,
  `c` varchar(10) DEFAULT NULL,
  `t` text,
  KEY `v` (`v`(5)),
  KEY `c` (`c`(5)),
  KEY `t` (`t`(5))
) ENGINE=MyISAM
drop table t1;
create TEMPORARY table t1 (v char(10));
show create table t1;
Table	Create Table
t1	CREATE TEMPORARY TABLE `t1` (
  `v` varchar(10) DEFAULT NULL
) ENGINE=MyISAM
drop table t1;
create TEMPORARY table t1 (v varchar(10), c char(10));
show create table t1;
Table	Create Table
t1	CREATE TEMPORARY TABLE `t1` (
  `v` varchar(10) DEFAULT NULL,
  `c` varchar(10) DEFAULT NULL
) ENGINE=MyISAM
insert into t1 values('a','a'),('a ','a ');
select concat('*',v,'*',c,'*') from t1;
concat('*',v,'*',c,'*')
*a*a*
*a *a *
drop table t1;
create TEMPORARY table t1(a int, b varchar(12), key ba(b, a));
insert into t1 values (1, 'A'), (20, NULL);
explain select * from t1 where a=20 and b is null;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	ba	ba	56	const,const	1	Using where; Using index
select * from t1 where a=20 and b is null;
a	b
20	NULL
drop table t1;
create TEMPORARY table t1 (a char(1), b char(1), key(a, b)) engine=MyISAM;
insert into t1 values ('8', '6'), ('4', '7');
select min(a) from t1;
min(a)
4
select min(b) from t1 where a='8';
min(b)
6
drop table t1;
create TEMPORARY table t1 ( `a` int NOT NULL auto_increment, `b` int default NULL,PRIMARY KEY  (`a`),UNIQUE KEY `b` (`b`)) ENGINE=MyISAM;
insert into t1 (b) values (1);
replace into t1 (b) values (2), (1), (3);
select * from t1;
a	b
3	1
2	2
4	3
truncate table t1;
insert into t1 (b) values (1);
replace into t1 (b) values (2);
replace into t1 (b) values (1);
replace into t1 (b) values (3);
select * from t1;
a	b
3	1
2	2
4	3
drop table t1;
create TEMPORARY table t1 (rowid int not null auto_increment, val int not null,primary
key (rowid), unique(val)) engine=MyISAM;
replace into t1 (val) values ('1'),('2');
replace into t1 (val) values ('1'),('2');
insert into t1 (val) values ('1'),('2');
ERROR 23000: Duplicate entry '1' for key 'val'
select * from t1;
rowid	val
3	1
4	2
drop table t1;
create TEMPORARY table t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=MyISAM;
INSERT INTO t1 (GRADE) VALUES (151),(252),(343);
SELECT GRADE  FROM t1 WHERE GRADE > 160 AND GRADE < 300;
GRADE
252
SELECT GRADE  FROM t1 WHERE GRADE= 151;
GRADE
151
DROP TABLE t1;
create TEMPORARY table t1(a date) engine=MyISAM;
create TEMPORARY table t2(a date, key(a)) engine=MyISAM;
insert into t1 values('2005-10-01');
insert into t2 values('2005-10-01');
select * from t1, t2
where t2.a between t1.a - interval 2 day and t1.a + interval 2 day;
a	a
2005-10-01	2005-10-01
drop table t1, t2;
create TEMPORARY table t1 (id int not null, f_id int not null, f int not null,
primary key(f_id, id)) engine=MyISAM;
create TEMPORARY table t2 (id int not null,s_id int not null,s varchar(200),
primary key(id)) engine=MyISAM;
INSERT INTO t1 VALUES (8, 1, 3);
INSERT INTO t1 VALUES (1, 2, 1);
INSERT INTO t2 VALUES (1, 0, '');
INSERT INTO t2 VALUES (8, 1, '');
commit;
select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)
where mm.id is null lock in share mode;
id	f_id	f
drop table t1,t2;
create TEMPORARY table t1 ( a int ) ENGINE=MyISAM;
BEGIN;
INSERT INTO t1 VALUES (1);
ALTER TABLE t1 ENGINE=MyISAM;
DROP TABLE t1;