~drizzle-trunk/drizzle/development : contents of tests/r/innodb

~drizzle-trunk/drizzle/development : (revision 971.3.3)

1 by brian clean slate	1	set global innodb_support_xa=default;
	2	set session innodb_support_xa=default;
	3	SET SESSION STORAGE_ENGINE = InnoDB;
	4	drop table if exists t1,t2,t3,t1m,t1i,t2m,t2i,t4;
	5	create table t1 (
520.1.16 by Brian Aker More test updates (one ulong fix)	6	c_id int not null default '0',
520.1.16 by Brian Aker More test updates (one ulong fix)	7	org_id int default null,
1 by brian clean slate	8	unique key contacts$c_id (c_id),
	9	key contacts$org_id (org_id)
	10	);
	11	insert into t1 values
	12	(2,null),(120,null),(141,null),(218,7), (128,1),
	13	(151,2),(234,2),(236,2),(243,2),(255,2),(259,2),(232,3),(235,3),(238,3),
	14	(246,3),(253,3),(269,3),(285,3),(291,3),(293,3),(131,4),(230,4),(231,4);
	15	create table t2 (
520.1.16 by Brian Aker More test updates (one ulong fix)	16	slai_id int not null default '0',
	17	owner_tbl int default null,
	18	owner_id int default null,
	19	sla_id int default null,
	20	inc_web int default null,
	21	inc_email int default null,
	22	inc_chat int default null,
	23	inc_csr int default null,
	24	inc_total int default null,
	25	time_billed int default null,
1 by brian clean slate	26	activedate timestamp null default null,
1 by brian clean slate	27	expiredate timestamp null default null,
520.1.16 by Brian Aker More test updates (one ulong fix)	28	state int default null,
520.1.16 by Brian Aker More test updates (one ulong fix)	29	sla_set int default null,
1 by brian clean slate	30	unique key t2$slai_id (slai_id),
	31	key t2$owner_id (owner_id),
	32	key t2$sla_id (sla_id)
	33	);
	34	insert into t2(slai_id, owner_tbl, owner_id, sla_id) values
	35	(1,3,1,1), (3,3,10,2), (4,3,3,6), (5,3,2,5), (6,3,8,3), (7,3,9,7),
	36	(8,3,6,8), (9,3,4,9), (10,3,5,10), (11,3,11,11), (12,3,7,12);
	37	flush tables;
	38	select si.slai_id
	39	from t1 c join t2 si on
	40	((si.owner_tbl = 3 and si.owner_id = c.org_id) or
	41	( si.owner_tbl = 2 and si.owner_id = c.c_id))
	42	where
	43	c.c_id = 218 and expiredate is null;
	44	slai_id
	45	12
	46	select * from t1 where org_id is null;
	47	c_id org_id
	48	2 NULL
	49	120 NULL
	50	141 NULL
	51	select si.slai_id
	52	from t1 c join t2 si on
	53	((si.owner_tbl = 3 and si.owner_id = c.org_id) or
	54	( si.owner_tbl = 2 and si.owner_id = c.c_id))
	55	where
	56	c.c_id = 218 and expiredate is null;
	57	slai_id
	58	12
	59	drop table t1, t2;
	60	CREATE TABLE t1 (a int, b int, KEY b (b));
	61	CREATE TABLE t2 (a int, b int, PRIMARY KEY (a,b));
	62	CREATE TABLE t3 (a int, b int, c int, PRIMARY KEY (a),
	63	UNIQUE KEY b (b,c), KEY a (a,b,c));
	64	INSERT INTO t1 VALUES (1, 1);
	65	INSERT INTO t1 SELECT a + 1, b + 1 FROM t1;
	66	INSERT INTO t1 SELECT a + 2, b + 2 FROM t1;
	67	INSERT INTO t2 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8);
	68	INSERT INTO t2 SELECT a + 1, b FROM t2;
	69	DELETE FROM t2 WHERE a = 1 AND b < 2;
	70	INSERT INTO t3 VALUES (1,1,1),(2,1,2);
	71	INSERT INTO t3 SELECT a + 2, a + 2, 3 FROM t3;
	72	INSERT INTO t3 SELECT a + 4, a + 4, 3 FROM t3;
520.1.16 by Brian Aker More test updates (one ulong fix)	73	SELECT STRAIGHT_JOIN t1.b, t1.a FROM t1, t3, t2 WHERE
1 by brian clean slate	74	t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
	75	ORDER BY t1.b LIMIT 2;
	76	b a
	77	1 1
	78	2 2
520.1.16 by Brian Aker More test updates (one ulong fix)	79	SELECT STRAIGHT_JOIN t1.b, t1.a FROM t1, t3, t2 WHERE
1 by brian clean slate	80	t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
	81	ORDER BY t1.b LIMIT 5;
	82	b a
	83	1 1
	84	2 2
	85	2 2
	86	3 3
	87	3 3
	88	DROP TABLE t1, t2, t3;
	89	CREATE TABLE `t1` (`id1` INT) ;
	90	INSERT INTO `t1` (`id1`) VALUES (1),(5),(2);
	91	CREATE TABLE `t2` (
	92	`id1` INT,
	93	`id2` INT NOT NULL,
	94	`id3` INT,
	95	`id4` INT NOT NULL,
	96	UNIQUE (`id2`,`id4`),
	97	KEY (`id1`)
	98	);
	99	INSERT INTO `t2`(`id1`,`id2`,`id3`,`id4`) VALUES
	100	(1,1,1,0),
	101	(1,1,2,1),
	102	(5,1,2,2),
	103	(6,1,2,3),
	104	(1,2,2,2),
	105	(1,2,1,1);
	106	SELECT `id1` FROM `t1` WHERE `id1` NOT IN (SELECT `id1` FROM `t2` WHERE `id2` = 1 AND `id3` = 2);
	107	id1
	108	2
	109	DROP TABLE t1, t2;
	110	CREATE TABLE t1(c1 TEXT, UNIQUE (c1(1)), cnt INT DEFAULT 1)
520.1.16 by Brian Aker More test updates (one ulong fix)	111	ENGINE=INNODB;
1 by brian clean slate	112	INSERT INTO t1 (c1) VALUES ('1a');
	113	SELECT * FROM t1;
	114	c1 cnt
	115	1a 1
	116	INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
	117	SELECT * FROM t1;
	118	c1 cnt
	119	1a 2
	120	DROP TABLE t1;
	121	CREATE TABLE t1(c1 VARCHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1)
520.1.16 by Brian Aker More test updates (one ulong fix)	122	ENGINE=INNODB;
1 by brian clean slate	123	INSERT INTO t1 (c1) VALUES ('1a');
	124	SELECT * FROM t1;
	125	c1 cnt
	126	1a 1
	127	INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
	128	SELECT * FROM t1;
	129	c1 cnt
	130	1a 2
	131	DROP TABLE t1;
	132	CREATE TABLE t1(c1 CHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1)
520.1.16 by Brian Aker More test updates (one ulong fix)	133	ENGINE=INNODB;
1 by brian clean slate	134	INSERT INTO t1 (c1) VALUES ('1a');
	135	SELECT * FROM t1;
	136	c1 cnt
	137	1a 1
	138	INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
	139	SELECT * FROM t1;
	140	c1 cnt
	141	1a 2
	142	DROP TABLE t1;
	143	CREATE TABLE t1 (
	144	a1 decimal(10,0) DEFAULT NULL,
	145	a2 blob,
	146	a4 blob,
	147	a5 char(175) DEFAULT NULL,
907.1.7 by Jay Pipes Merged in remove-timezone work	148	a6 timestamp NOT NULL DEFAULT NOW(),
520.1.16 by Brian Aker More test updates (one ulong fix)	149	a7 blob,
1 by brian clean slate	150	INDEX idx (a6,a7(239),a5)
	151	) ENGINE=InnoDB;
	152	EXPLAIN SELECT a4 FROM t1 WHERE
	153	a6=NULL AND
	154	a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
	155	id select_type table type possible_keys key key_len ref rows Extra
	156	1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
	157	EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE
	158	t.a6=t.a6 AND t1.a6=NULL AND
	159	t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
	160	id select_type table type possible_keys key key_len ref rows Extra
	161	1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
	162	DROP TABLE t1;
	163	create table t1m (a int) engine = MEMORY;
	164	create table t1i (a int);
	165	create table t2m (a int) engine = MEMORY;
	166	create table t2i (a int);
	167	insert into t2m values (5);
	168	insert into t2i values (5);
	169	select 1, min(a) from t1i where a=99;
	170	1 min(a)
	171	1 NULL
	172	select 1, min(a) from t1i where 1=99;
	173	1 min(a)
	174	1 NULL
	175	select 1, min(1) from t1i where a=99;
	176	1 min(1)
	177	1 NULL
	178	select 1, min(1) from t1i where 1=99;
	179	1 min(1)
	180	1 NULL
	181	select 1, max(a) from t1i where a=99;
	182	1 max(a)
	183	1 NULL
	184	select 1, max(a) from t1i where 1=99;
	185	1 max(a)
	186	1 NULL
	187	select 1, max(1) from t1i where a=99;
	188	1 max(1)
	189	1 NULL
	190	select 1, max(1) from t1i where 1=99;
	191	1 max(1)
	192	1 NULL
	193	explain select count(*), min(7), max(7) from t1m, t1i;
	194	id select_type table type possible_keys key key_len ref rows Extra
	195	1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found
	196	1 SIMPLE t1i ALL NULL NULL NULL NULL 1
	197	select count(*), min(7), max(7) from t1m, t1i;
	198	count(*) min(7) max(7)
	199	0 NULL NULL
	200	explain select count(*), min(7), max(7) from t1m, t2i;
	201	id select_type table type possible_keys key key_len ref rows Extra
	202	1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found
	203	1 SIMPLE t2i ALL NULL NULL NULL NULL 1
	204	select count(*), min(7), max(7) from t1m, t2i;
	205	count(*) min(7) max(7)
	206	0 NULL NULL
	207	explain select count(*), min(7), max(7) from t2m, t1i;
	208	id select_type table type possible_keys key key_len ref rows Extra
	209	1 SIMPLE t2m system NULL NULL NULL NULL 1
	210	1 SIMPLE t1i ALL NULL NULL NULL NULL 1
	211	select count(*), min(7), max(7) from t2m, t1i;
	212	count(*) min(7) max(7)
	213	0 NULL NULL
214	drop table t1m, t1i, t2m, t2i;
215	create table t1 (
216	a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
217	) ENGINE = MEMORY;
218	insert into t1 (a1, a2, b, c, d) values
219	('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
220	('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
221	('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
222	('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
223	('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
224	('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
225	('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
226	('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
227	('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
228	('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
229	('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
230	('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
231	('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
232	('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
233	('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
234	('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'),
235	('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
236	('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
237	('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
238	('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
239	('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
240	('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
241	('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
242	('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
243	('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
244	('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
245	('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
246	('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
247	('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
248	('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
249	('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
250	('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4');
251	create table t4 (
252	pk_col int auto_increment primary key, a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
253	);
254	insert into t4 (a1, a2, b, c, d, dummy) select * from t1;
255	create index idx12672_0 on t4 (a1);
256	create index idx12672_1 on t4 (a1,a2,b,c);
257	create index idx12672_2 on t4 (a1,a2,b);
258	analyze table t4;
259	Table Op Msg_type Msg_text
260	test.t4 analyze status OK
261	select distinct a1 from t4 where pk_col not in (1,2,3,4);
262	a1
263	a
264	b
265	c
266	d
267	drop table t1,t4;
268	DROP TABLE IF EXISTS t2, t1;
269	CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE= InnoDB;
270	CREATE TABLE t2 (
271	i INT NOT NULL,
272	FOREIGN KEY (i) REFERENCES t1 (i) ON DELETE NO ACTION
273	) ENGINE= InnoDB;
274	INSERT INTO t1 VALUES (1);
275	INSERT INTO t2 VALUES (1);
276	DELETE IGNORE FROM t1 WHERE i = 1;
277	Warnings:
278	Error 1451 Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`i`) REFERENCES `t1` (`i`) ON DELETE NO ACTION)
279	SELECT * FROM t1, t2;
280	i i
281	1 1
282	DROP TABLE t2, t1;
283	End of 4.1 tests.
284	create table t1 (
285	a varchar(30), b varchar(30), primary key(a), key(b)
286	);
287	select distinct a from t1;
288	a
289	drop table t1;
290	create table t1(a int, key(a));
291	insert into t1 values(1);
292	select a, count(a) from t1 group by a with rollup;
293	a count(a)
294	1 1
295	NULL 1
296	drop table t1;
297	create table t1 (f1 int, f2 char(1), primary key(f1,f2));
298	insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d");
299	alter table t1 drop primary key, add primary key (f2, f1);
300	explain select distinct f1 a, f1 b from t1;
301	id select_type table type possible_keys key key_len ref rows Extra
520.1.16 by Brian Aker More test updates (one ulong fix)	302	1 SIMPLE t1 index NULL PRIMARY 10 NULL 4 Using index; Using temporary
1 by brian clean slate	303	explain select distinct f1, f2 from t1;
1 by brian clean slate	304	id select_type table type possible_keys key key_len ref rows Extra
520.1.16 by Brian Aker More test updates (one ulong fix)	305	1 SIMPLE t1 range NULL PRIMARY 10 NULL 3 Using index for group-by; Using temporary
1 by brian clean slate	306	drop table t1;
520.1.16 by Brian Aker More test updates (one ulong fix)	307	CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, name varchar(20),
1 by brian clean slate	308	INDEX (name));
520.1.16 by Brian Aker More test updates (one ulong fix)	309	CREATE TABLE t2 (id int NOT NULL PRIMARY KEY, fkey int);
1 by brian clean slate	310	ALTER TABLE t2 ADD FOREIGN KEY (fkey) REFERENCES t2(id);
	311	INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B');
	312	INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3);
	313	EXPLAIN
	314	SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
	315	WHERE t1.name LIKE 'A%';
	316	id select_type table type possible_keys key key_len ref rows Extra
	317	1 SIMPLE t1 index PRIMARY,name PRIMARY 4 NULL 3 Using where
	318	1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using index
	319	EXPLAIN
	320	SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
	321	WHERE t1.name LIKE 'A%' OR FALSE;
	322	id select_type table type possible_keys key key_len ref rows Extra
	323	1 SIMPLE t2 index NULL PRIMARY 4 NULL 5
	324	1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.fkey 1 Using where
	325	DROP TABLE t1,t2;
	326	CREATE TABLE t1 (
	327	id int NOT NULL,
	328	name varchar(20) NOT NULL,
	329	dept varchar(20) NOT NULL,
520.1.16 by Brian Aker More test updates (one ulong fix)	330	age int NOT NULL,
1 by brian clean slate	331	PRIMARY KEY (id),
	332	INDEX (name,dept)
	333	) ENGINE=InnoDB;
	334	INSERT INTO t1(id, dept, age, name) VALUES
	335	(3987, 'cs1', 10, 'rs1'), (3988, 'cs2', 20, 'rs1'), (3995, 'cs3', 10, 'rs2'),
	336	(3996, 'cs4', 20, 'rs2'), (4003, 'cs5', 10, 'rs3'), (4004, 'cs6', 20, 'rs3'),
	337	(4011, 'cs7', 10, 'rs4'), (4012, 'cs8', 20, 'rs4'), (4019, 'cs9', 10, 'rs5'),
	338	(4020, 'cs10', 20, 'rs5'),(4027, 'cs11', 10, 'rs6'),(4028, 'cs12', 20, 'rs6');
	339	EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
	340	id select_type table type possible_keys key key_len ref rows Extra
520.1.16 by Brian Aker More test updates (one ulong fix)	341	1 SIMPLE t1 range name name 164 NULL 2 Using where; Using index for group-by
1 by brian clean slate	342	SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
	343	name dept
	344	rs5 cs10
	345	rs5 cs9
	346	DELETE FROM t1;
	347	EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
	348	id select_type table type possible_keys key key_len ref rows Extra
520.1.16 by Brian Aker More test updates (one ulong fix)	349	1 SIMPLE t1 range name name 164 NULL 2 Using where; Using index for group-by
1 by brian clean slate	350	SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
	351	name dept
	352	DROP TABLE t1;
	353	drop table if exists t1;
	354	show variables like 'innodb_rollback_on_timeout';
	355	Variable_name Value
	356	innodb_rollback_on_timeout OFF
520.1.16 by Brian Aker More test updates (one ulong fix)	357	create table t1 (a int not null primary key) engine = innodb;
1 by brian clean slate	358	insert into t1 values (1);
	359	commit;
	360	begin work;
	361	insert into t1 values (2);
	362	select * from t1;
	363	a
	364	1
	365	2
	366	begin work;
	367	insert into t1 values (5);
	368	select * from t1;
	369	a
	370	1
	371	5
	372	insert into t1 values (2);
	373	ERROR HY000: Lock wait timeout exceeded; try restarting transaction
	374	select * from t1;
	375	a
	376	1
	377	5
	378	commit;
	379	select * from t1;
	380	a
	381	1
	382	2
	383	commit;
	384	select * from t1;
	385	a
	386	1
	387	2
	388	5
	389	drop table t1;
	390	drop table if exists `test`;
	391	Warnings:
	392	Note 1051 Unknown table 'test'
	393	CREATE TABLE `test` (`test1` varchar(3) NOT NULL,
	394	`test2` varchar(4) NOT NULL,PRIMARY KEY (`test1`))
520.1.16 by Brian Aker More test updates (one ulong fix)	395	ENGINE=InnoDB;
1 by brian clean slate	396	INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '5678');
	397	select * from test;
	398	test1 test2
	399	tes 5678
	400	INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '1234')
	401	ON DUPLICATE KEY UPDATE `test2` = '1234';
	402	select * from test;
	403	test1 test2
	404	tes 1234
	405	flush tables;
	406	select * from test;
	407	test1 test2
	408	tes 1234
	409	drop table test;
	410	drop table if exists t1;
	411	show variables like 'innodb_rollback_on_timeout';
	412	Variable_name Value
	413	innodb_rollback_on_timeout OFF
520.1.16 by Brian Aker More test updates (one ulong fix)	414	create table t1 (a int not null primary key) engine = innodb;
1 by brian clean slate	415	insert into t1 values (1);
	416	commit;
	417	begin work;
	418	insert into t1 values (2);
	419	select * from t1;
	420	a
	421	1
	422	2
	423	begin work;
	424	insert into t1 values (5);
	425	select * from t1;
	426	a
	427	1
	428	5
	429	insert into t1 values (2);
	430	ERROR HY000: Lock wait timeout exceeded; try restarting transaction
	431	select * from t1;
	432	a
	433	1
	434	5
	435	commit;
	436	select * from t1;
	437	a
	438	1
	439	2
	440	commit;
	441	select * from t1;
	442	a
	443	1
	444	2
	445	5
	446	drop table t1;
	447	create table t1(
	448	id int auto_increment,
	449	c char(1) not null,
	450	counter int not null default 1,
	451	primary key (id),
	452	unique key (c)
	453	) engine=innodb;
	454	insert into t1 (id, c) values
	455	(NULL, 'a'),
	456	(NULL, 'a')
	457	on duplicate key update id = values(id), counter = counter + 1;
	458	select * from t1;
	459	id c counter
	460	2 a 2
	461	insert into t1 (id, c) values
	462	(NULL, 'b')
	463	on duplicate key update id = values(id), counter = counter + 1;
	464	select * from t1;
	465	id c counter
	466	2 a 2
	467	3 b 1
	468	truncate table t1;
	469	insert into t1 (id, c) values (NULL, 'a');
	470	select * from t1;
	471	id c counter
	472	1 a 1
	473	insert into t1 (id, c) values (NULL, 'b'), (NULL, 'b')
	474	on duplicate key update id = values(id), c = values(c), counter = counter + 1;
	475	select * from t1;
	476	id c counter
	477	1 a 1
	478	3 b 2
479	insert into t1 (id, c) values (NULL, 'a')
480	on duplicate key update id = values(id), c = values(c), counter = counter + 1;
481	select * from t1;
482	id c counter
483	3 b 2
484	4 a 2
485	drop table t1;
486	create table t1(a int) engine=innodb;
487	alter table t1 comment '123';
488	show create table t1;
489	Table Create Table
490	t1 CREATE TABLE `t1` (
873.2.35 by Monty Taylor Update tests based on how Toru's latest patch changes create table statements.	491	`a` int DEFAULT NULL
520.1.16 by Brian Aker More test updates (one ulong fix)	492	) ENGINE=InnoDB COMMENT='123'
1 by brian clean slate	493	drop table t1;
520.1.16 by Brian Aker More test updates (one ulong fix)	494	CREATE TABLE t1 (a CHAR(2), KEY (a)) ENGINE = InnoDB;
1 by brian clean slate	495	INSERT INTO t1 VALUES ('uk'),('bg');
	496	SELECT * FROM t1 WHERE a = 'uk';
	497	a
	498	uk
	499	DELETE FROM t1 WHERE a = 'uk';
	500	SELECT * FROM t1 WHERE a = 'uk';
	501	a
	502	UPDATE t1 SET a = 'us' WHERE a = 'uk';
	503	SELECT * FROM t1 WHERE a = 'uk';
	504	a
	505	CREATE TABLE t2 (a CHAR(2), KEY (a)) ENGINE = InnoDB;
	506	INSERT INTO t2 VALUES ('uk'),('bg');
	507	SELECT * FROM t2 WHERE a = 'uk';
	508	a
	509	uk
	510	DELETE FROM t2 WHERE a = 'uk';
	511	SELECT * FROM t2 WHERE a = 'uk';
	512	a
	513	INSERT INTO t2 VALUES ('uk');
	514	UPDATE t2 SET a = 'us' WHERE a = 'uk';
	515	SELECT * FROM t2 WHERE a = 'uk';
	516	a
	517	CREATE TABLE t3 (a CHAR(2), KEY (a)) ENGINE = MyISAM;
	518	INSERT INTO t3 VALUES ('uk'),('bg');
	519	SELECT * FROM t3 WHERE a = 'uk';
	520	a
	521	uk
	522	DELETE FROM t3 WHERE a = 'uk';
	523	SELECT * FROM t3 WHERE a = 'uk';
	524	a
	525	INSERT INTO t3 VALUES ('uk');
	526	UPDATE t3 SET a = 'us' WHERE a = 'uk';
	527	SELECT * FROM t3 WHERE a = 'uk';
	528	a
	529	DROP TABLE t1,t2,t3;
	530	CREATE TABLE t1 (a INT) ENGINE=InnoDB;
	531	CREATE TABLE t2 (a INT) ENGINE=InnoDB;
	532	switch to connection c1
	533	SET AUTOCOMMIT=0;
	534	INSERT INTO t2 VALUES (1);
	535	switch to connection c2
	536	SET AUTOCOMMIT=0;
	537	LOCK TABLES t1 READ, t2 READ;
	538	ERROR HY000: Lock wait timeout exceeded; try restarting transaction
	539	switch to connection c1
	540	COMMIT;
	541	INSERT INTO t1 VALUES (1);
	542	switch to connection default
	543	SET AUTOCOMMIT=default;
	544	DROP TABLE t1,t2;
	545	CREATE TABLE t1 (
	546	id int NOT NULL auto_increment PRIMARY KEY,
	547	b int NOT NULL,
	548	c datetime NOT NULL,
	549	INDEX idx_b(b),
	550	INDEX idx_c(c)
	551	) ENGINE=InnoDB;
	552	CREATE TABLE t2 (
	553	b int NOT NULL auto_increment PRIMARY KEY,
	554	c datetime NOT NULL
	555	) ENGINE= MyISAM;
	556	INSERT INTO t2(c) VALUES ('2007-01-01');
	557	INSERT INTO t2(c) SELECT c FROM t2;
	558	INSERT INTO t2(c) SELECT c FROM t2;
559	INSERT INTO t2(c) SELECT c FROM t2;
560	INSERT INTO t2(c) SELECT c FROM t2;
561	INSERT INTO t2(c) SELECT c FROM t2;
562	INSERT INTO t2(c) SELECT c FROM t2;
563	INSERT INTO t2(c) SELECT c FROM t2;
564	INSERT INTO t2(c) SELECT c FROM t2;
565	INSERT INTO t2(c) SELECT c FROM t2;
566	INSERT INTO t2(c) SELECT c FROM t2;
567	INSERT INTO t1(b,c) SELECT b,c FROM t2;
568	UPDATE t2 SET c='2007-01-02';
569	INSERT INTO t1(b,c) SELECT b,c FROM t2;
570	UPDATE t2 SET c='2007-01-03';
571	INSERT INTO t1(b,c) SELECT b,c FROM t2;
572	set @@sort_buffer_size=8192;
573	Warnings:
910.4.16 by Stewart Smith truncated variable is error (Work In Progress) test fixes	574	Error 1292 Truncated incorrect sort_buffer_size value: '8192'
1 by brian clean slate	575	SELECT COUNT(*) FROM t1;
	576	COUNT(*)
	577	3072
	578	EXPLAIN
	579	SELECT COUNT(*) FROM t1
	580	WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
	581	id select_type table type possible_keys key key_len ref rows Extra
	582	1 SIMPLE t1 ALL idx_b,idx_c NULL NULL NULL # Using where
	583	SELECT COUNT(*) FROM t1
	584	WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
	585	COUNT(*)
	586	3072
	587	EXPLAIN
	588	SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c)
	589	WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
	590	id select_type table type possible_keys key key_len ref rows Extra
	591	1 SIMPLE t1 index_merge idx_b,idx_c idx_c,idx_b 8,4 NULL # Using sort_union(idx_c,idx_b); Using where
	592	SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c)
	593	WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
	594	COUNT(*)
	595	3072
	596	set @@sort_buffer_size=default;
	597	DROP TABLE t1,t2;
	598	CREATE TABLE t1 (a int, b int);
	599	insert into t1 values (1,1),(1,2);
	600	CREATE TABLE t2 (primary key (a)) select * from t1;
	601	ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
	602	drop table if exists t2;
	603	Warnings:
	604	Note 1051 Unknown table 't2'
	605	CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1;
	606	ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
	607	drop table if exists t2;
	608	Warnings:
	609	Note 1051 Unknown table 't2'
	610	CREATE TABLE t2 (a int, b int, primary key (a));
	611	BEGIN;
	612	INSERT INTO t2 values(100,100);
	613	CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
	614	ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
	615	SELECT * from t2;
	616	a b
	617	100 100
	618	ROLLBACK;
	619	SELECT * from t2;
	620	a b
	621	100 100
	622	TRUNCATE table t2;
	623	INSERT INTO t2 select * from t1;
	624	ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
	625	SELECT * from t2;
	626	a b
	627	drop table t2;
	628	CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));
	629	BEGIN;
	630	INSERT INTO t2 values(100,100);
	631	CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
	632	ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
	633	SELECT * from t2;
	634	a b
	635	100 100
	636	COMMIT;
	637	BEGIN;
	638	INSERT INTO t2 values(101,101);
639	CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
640	ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
641	SELECT * from t2;
642	a b
643	100 100
644	101 101
645	ROLLBACK;
646	SELECT * from t2;
647	a b
648	100 100
649	TRUNCATE table t2;
650	INSERT INTO t2 select * from t1;
651	ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
652	SELECT * from t2;
653	a b
654	drop table t1,t2;
520.1.16 by Brian Aker More test updates (one ulong fix)	655	create table t1(f1 varchar(800) not null, key(f1));
1 by brian clean slate	656	Warnings:
	657	Warning 1071 Specified key was too long; max key length is 767 bytes
	658	insert into t1 values('aaa');
	659	drop table t1;
	660	CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c FLOAT, KEY b(b)) ENGINE = INNODB;
	661	INSERT INTO t1 VALUES ( 1 , 1 , 1);
	662	INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20), 1 FROM t1;
	663	INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20), 1 FROM t1;
	664	INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20), 1 FROM t1;
	665	INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20), 1 FROM t1;
	666	INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20), 1 FROM t1;
	667	INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20), 1 FROM t1;
	668	INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20), 1 FROM t1;
	669	EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b;
	670	id select_type table type possible_keys key key_len ref rows Extra
	671	1 SIMPLE t1 index NULL b 5 NULL 128
	672	EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b;
	673	id select_type table type possible_keys key key_len ref rows Extra
	674	1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using filesort
	675	DROP TABLE t1;
	676	drop table if exists t1;
	677	show variables like 'innodb_rollback_on_timeout';
	678	Variable_name Value
	679	innodb_rollback_on_timeout OFF
520.1.16 by Brian Aker More test updates (one ulong fix)	680	create table t1 (a int not null primary key) engine = innodb;
1 by brian clean slate	681	insert into t1 values (1);
	682	commit;
	683	begin work;
	684	insert into t1 values (2);
	685	select * from t1;
	686	a
	687	1
	688	2
	689	begin work;
	690	insert into t1 values (5);
	691	select * from t1;
	692	a
	693	1
	694	5
	695	insert into t1 values (2);
	696	ERROR HY000: Lock wait timeout exceeded; try restarting transaction
	697	select * from t1;
	698	a
	699	1
	700	5
	701	commit;
	702	select * from t1;
	703	a
	704	1
	705	2
	706	commit;
	707	select * from t1;
	708	a
	709	1
	710	2
	711	5
	712	drop table t1;
	713	drop table if exists t1;
	714	create table t1 (a int) engine=innodb;
	715	alter table t1 alter a set default 1;
	716	drop table t1;
	717
	718	Bug#24918 drop table and lock / inconsistent between
	719	perm and temp tables
	720
	721	Check transactional tables under LOCK TABLES
	722
	723	drop table if exists t24918, t24918_tmp, t24918_trans, t24918_trans_tmp,
	724	t24918_access;
	725	create table t24918_access (id int);
	726	create table t24918 (id int) engine=myisam;
	727	create temporary table t24918_tmp (id int) engine=myisam;
	728	create table t24918_trans (id int) engine=innodb;
	729	create temporary table t24918_trans_tmp (id int) engine=innodb;
	730	lock table t24918 write, t24918_tmp write, t24918_trans write, t24918_trans_tmp write;
	731	drop table t24918;
	732	select * from t24918_access;
	733	ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
	734	drop table t24918_trans;
	735	select * from t24918_access;
	736	ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
	737	drop table t24918_trans_tmp;
	738	select * from t24918_access;
	739	ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
	740	drop table t24918_tmp;
	741	select * from t24918_access;
	742	ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
	743	unlock tables;
	744	drop table t24918_access;
745	CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY bkey (b)) ENGINE=InnoDB;
746	INSERT INTO t1 VALUES (1,2),(3,2),(2,2),(4,2),(5,2),(6,2),(7,2),(8,2);
747	INSERT INTO t1 SELECT a + 8, 2 FROM t1;
748	INSERT INTO t1 SELECT a + 16, 1 FROM t1;
749	EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a;
750	id 1
751	select_type SIMPLE
752	table t1
753	type ref
754	possible_keys bkey
755	key bkey
756	key_len 5
757	ref const
758	rows 16
759	Extra Using where; Using index
760	SELECT * FROM t1 WHERE b=2 ORDER BY a;
761	a b
762	1 2
763	2 2
764	3 2
765	4 2
766	5 2
767	6 2
768	7 2
769	8 2
770	9 2
771	10 2
772	11 2
773	12 2
774	13 2
775	14 2
776	15 2
777	16 2
778	EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
779	id 1
780	select_type SIMPLE
781	table t1
782	type index
783	possible_keys bkey
784	key PRIMARY
785	key_len 4
786	ref NULL
787	rows 32
788	Extra Using where
789	SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
790	a b
791	1 2
792	2 2
793	3 2
794	4 2
795	5 2
796	6 2
797	7 2
798	8 2
799	9 2
800	10 2
801	11 2
802	12 2
803	13 2
804	14 2
805	15 2
806	16 2
807	17 1
808	18 1
809	19 1
810	20 1
811	21 1
812	22 1
813	23 1
814	24 1
815	25 1
816	26 1
817	27 1
818	28 1
819	29 1
820	30 1
821	31 1
822	32 1
823	EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
824	id 1
825	select_type SIMPLE
826	table t1
827	type range
828	possible_keys bkey
829	key bkey
830	key_len 5
831	ref NULL
832	rows 16
833	Extra Using where; Using index
834	SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
835	a b
836	17 1
837	18 1
838	19 1
839	20 1
840	21 1
841	22 1
842	23 1
843	24 1
844	25 1
845	26 1
846	27 1
847	28 1
848	29 1
849	30 1
850	31 1
851	32 1
852	1 2
853	2 2
854	3 2
855	4 2
856	5 2
857	6 2
858	7 2
859	8 2
860	9 2
861	10 2
862	11 2
863	12 2
864	13 2
865	14 2
866	15 2
867	16 2
868	CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a), KEY bkey (b,c))
869	ENGINE=InnoDB;
870	INSERT INTO t2 VALUES (1,1,1),(3,1,1),(2,1,1),(4,1,1);
871	INSERT INTO t2 SELECT a + 4, 1, 1 FROM t2;
872	INSERT INTO t2 SELECT a + 8, 1, 1 FROM t2;
873	EXPLAIN SELECT * FROM t2 WHERE b=1 ORDER BY a;
874	id 1
875	select_type SIMPLE
876	table t2
877	type index
878	possible_keys bkey
879	key PRIMARY
880	key_len 4
881	ref NULL
882	rows 16
883	Extra Using where; Using index
884	SELECT * FROM t2 WHERE b=1 ORDER BY a;
885	a b c
886	1 1 1
887	2 1 1
888	3 1 1
889	4 1 1
890	5 1 1
891	6 1 1
892	7 1 1
893	8 1 1
894	9 1 1
895	10 1 1
896	11 1 1
897	12 1 1
898	13 1 1
899	14 1 1
900	15 1 1
901	16 1 1
902	EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
903	id 1
904	select_type SIMPLE
905	table t2
906	type ref
907	possible_keys bkey
908	key bkey
909	key_len 10
910	ref const,const
911	rows 8
912	Extra Using where; Using index
913	SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
914	a b c
915	1 1 1
916	2 1 1
917	3 1 1
918	4 1 1
919	5 1 1
920	6 1 1
921	7 1 1
922	8 1 1
923	9 1 1
924	10 1 1
925	11 1 1
926	12 1 1
927	13 1 1
928	14 1 1
929	15 1 1
930	16 1 1
931	EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
932	id 1
933	select_type SIMPLE
934	table t2
935	type ref
936	possible_keys bkey
937	key bkey
938	key_len 10
939	ref const,const
940	rows 8
941	Extra Using where; Using index
942	SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
943	a b c
944	1 1 1
945	2 1 1
946	3 1 1
947	4 1 1
948	5 1 1
949	6 1 1
950	7 1 1
951	8 1 1
952	9 1 1
953	10 1 1
954	11 1 1
955	12 1 1
956	13 1 1
957	14 1 1
958	15 1 1
959	16 1 1
960	EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
961	id 1
962	select_type SIMPLE
963	table t2
964	type ref
965	possible_keys bkey
966	key bkey
967	key_len 10
968	ref const,const
969	rows 8
970	Extra Using where; Using index
971	SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
972	a b c
973	1 1 1
974	2 1 1
975	3 1 1
976	4 1 1
977	5 1 1
978	6 1 1
979	7 1 1
980	8 1 1
981	9 1 1
982	10 1 1
983	11 1 1
984	12 1 1
985	13 1 1
986	14 1 1
987	15 1 1
988	16 1 1
989	DROP TABLE t1,t2;
520.1.16 by Brian Aker More test updates (one ulong fix)	990	create table t1(a text) engine=innodb;
1 by brian clean slate	991	insert into t1 values('aaa');
	992	alter table t1 add index(a(1024));
	993	Warnings:
	994	Warning 1071 Specified key was too long; max key length is 767 bytes
	995	Warning 1071 Specified key was too long; max key length is 767 bytes
	996	Warning 1071 Specified key was too long; max key length is 767 bytes
	997	show create table t1;
	998	Table Create Table
	999	t1 CREATE TABLE `t1` (
	1000	`a` text,
520.1.16 by Brian Aker More test updates (one ulong fix)	1001	KEY `a` (`a`())
520.1.16 by Brian Aker More test updates (one ulong fix)	1002	) ENGINE=InnoDB
1 by brian clean slate	1003	drop table t1;
	1004	CREATE TABLE t1 (
	1005	a INT,
	1006	b INT,
	1007	KEY (b)
	1008	) ENGINE=InnoDB;
	1009	INSERT INTO t1 VALUES (1,10), (2,10), (2,20), (3,30);
	1010	START TRANSACTION;
	1011	SELECT * FROM t1 WHERE b=20 FOR UPDATE;
	1012	a b
	1013	2 20
	1014	START TRANSACTION;
	1015	SELECT * FROM t1 WHERE b=10 ORDER BY A FOR UPDATE;
	1016	a b
	1017	1 10
	1018	2 10
	1019	ROLLBACK;
	1020	ROLLBACK;
	1021	DROP TABLE t1;
	1022	CREATE TABLE t1(
	1023	a INT,
	1024	b INT NOT NULL,
	1025	c INT NOT NULL,
	1026	d INT,
	1027	UNIQUE KEY (c,b)
	1028	) engine=innodb;
	1029	INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
	1030	EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d;
	1031	id select_type table type possible_keys key key_len ref rows Extra
	1032	1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
	1033	SELECT c,b,d FROM t1 GROUP BY c,b,d;
	1034	c b d
	1035	1 1 50
	1036	3 1 4
	1037	3 2 40
	1038	EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
	1039	id select_type table type possible_keys key key_len ref rows Extra
	1040	1 SIMPLE t1 ALL NULL NULL NULL NULL 3
	1041	SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
	1042	c b d
	1043	1 1 50
	1044	3 1 4
	1045	3 2 40
	1046	EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d;
	1047	id select_type table type possible_keys key key_len ref rows Extra
	1048	1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
	1049	SELECT c,b,d FROM t1 ORDER BY c,b,d;
	1050	c b d
	1051	1 1 50
	1052	3 1 4
	1053	3 2 40
	1054	EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b;
	1055	id select_type table type possible_keys key key_len ref rows Extra
	1056	1 SIMPLE t1 index NULL c 8 NULL 3
	1057	SELECT c,b,d FROM t1 GROUP BY c,b;
	1058	c b d
	1059	1 1 50
	1060	3 1 4
	1061	3 2 40
	1062	EXPLAIN SELECT c,b FROM t1 GROUP BY c,b;
	1063	id select_type table type possible_keys key key_len ref rows Extra
	1064	1 SIMPLE t1 index NULL c 8 NULL 3 Using index
	1065	SELECT c,b FROM t1 GROUP BY c,b;
	1066	c b
1067	1 1
1068	3 1
1069	3 2
1070	DROP TABLE t1;
1071	CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), INDEX b (b)) ENGINE=InnoDB;
1072	INSERT INTO t1(a,b) VALUES (1,1), (2,2), (3,2);
1073	EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
1074	id 1
1075	select_type SIMPLE
1076	table t1
1077	type ref
1078	possible_keys b
1079	key b
1080	key_len 5
1081	ref const
1082	rows 1
1083	Extra Using where; Using index
1084	SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
1085	a b
1086	2 2
1087	3 2
1088	EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
1089	id 1
1090	select_type SIMPLE
1091	table t1
1092	type ref
1093	possible_keys b
1094	key b
1095	key_len 5
1096	ref const
1097	rows 1
1098	Extra Using where; Using index
1099	SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
1100	a b
1101	3 2
1102	2 2
1103	EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a ASC;
1104	id 1
1105	select_type SIMPLE
1106	table t1
1107	type index
1108	possible_keys NULL
1109	key b
1110	key_len 5
1111	ref NULL
1112	rows 3
1113	Extra Using index
1114	SELECT * FROM t1 ORDER BY b ASC, a ASC;
1115	a b
1116	1 1
1117	2 2
1118	3 2
1119	EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a DESC;
1120	id 1
1121	select_type SIMPLE
1122	table t1
1123	type index
1124	possible_keys NULL
1125	key b
1126	key_len 5
1127	ref NULL
1128	rows 3
1129	Extra Using index
1130	SELECT * FROM t1 ORDER BY b DESC, a DESC;
1131	a b
1132	3 2
1133	2 2
1134	1 1
1135	EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a DESC;
1136	id 1
1137	select_type SIMPLE
1138	table t1
1139	type index
1140	possible_keys NULL
1141	key PRIMARY
1142	key_len 4
1143	ref NULL
1144	rows 3
1145	Extra Using filesort
1146	SELECT * FROM t1 ORDER BY b ASC, a DESC;
1147	a b
1148	1 1
1149	3 2
1150	2 2
1151	EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a ASC;
1152	id 1
1153	select_type SIMPLE
1154	table t1
1155	type index
1156	possible_keys NULL
1157	key PRIMARY
1158	key_len 4
1159	ref NULL
1160	rows 3
1161	Extra Using filesort
1162	SELECT * FROM t1 ORDER BY b DESC, a ASC;
1163	a b
1164	2 2
1165	3 2
1166	1 1
1167	DROP TABLE t1;
1168
1169	#
1170	# Bug#27610: ALTER TABLE ROW_FORMAT=... does not rebuild the table.
1171	#
1172
1173	# - prepare;
1174
1175	DROP TABLE IF EXISTS t1;
1176
1177	CREATE TABLE t1(c INT)
1178	ENGINE = InnoDB
1179	ROW_FORMAT = COMPACT;
1180
1181	# - initial check;
1182
1183	SELECT table_schema, table_name, row_format
1184	FROM INFORMATION_SCHEMA.TABLES
1185	WHERE table_schema = DATABASE() AND table_name = 't1';
1186	table_schema table_name row_format
1187	test t1 Compact
1188
1189	# - change ROW_FORMAT and check;
1190
1191	ALTER TABLE t1 ROW_FORMAT = REDUNDANT;
1192
1193	SELECT table_schema, table_name, row_format
1194	FROM INFORMATION_SCHEMA.TABLES
1195	WHERE table_schema = DATABASE() AND table_name = 't1';
1196	table_schema table_name row_format
1197	test t1 Redundant
1198
1199	# - that's it, cleanup.
1200
1201	DROP TABLE t1;
1202	create table t1(a char(10) not null, unique key aa(a(1)),
1203	b char(4) not null, unique key bb(b(4))) engine=innodb;
1204	desc t1;
1205	Field Type Null Key Default Extra
520.1.16 by Brian Aker More test updates (one ulong fix)	1206	a varchar(10) NO UNI NULL
520.1.16 by Brian Aker More test updates (one ulong fix)	1207	b varchar(4) NO PRI NULL
1 by brian clean slate	1208	show create table t1;
	1209	Table Create Table
	1210	t1 CREATE TABLE `t1` (
520.1.16 by Brian Aker More test updates (one ulong fix)	1211	`a` varchar(10) NOT NULL,
520.1.16 by Brian Aker More test updates (one ulong fix)	1212	`b` varchar(4) NOT NULL,
1 by brian clean slate	1213	UNIQUE KEY `bb` (`b`),
520.1.16 by Brian Aker More test updates (one ulong fix)	1214	UNIQUE KEY `aa` (`a`())
520.1.16 by Brian Aker More test updates (one ulong fix)	1215	) ENGINE=InnoDB
1 by brian clean slate	1216	drop table t1;
	1217	CREATE TABLE t1 (id int, type char(6), d int, INDEX idx(id,d)) ENGINE=InnoDB;
	1218	INSERT INTO t1 VALUES
	1219	(191, 'member', 1), (NULL, 'member', 3), (NULL, 'member', 4), (201, 'member', 2);
	1220	EXPLAIN SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
	1221	id select_type table type possible_keys key key_len ref rows Extra
	1222	1 SIMPLE t1 ALL idx NULL NULL NULL 4 Using where; Using filesort
	1223	SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
	1224	id type d
	1225	191 member 1
	1226	NULL member 3
	1227	NULL member 4
	1228	DROP TABLE t1;
	1229	set @my_innodb_autoextend_increment=@@global.innodb_autoextend_increment;
	1230	set global innodb_autoextend_increment=8;
	1231	set global innodb_autoextend_increment=@my_innodb_autoextend_increment;
	1232	set @my_innodb_commit_concurrency=@@global.innodb_commit_concurrency;
	1233	set global innodb_commit_concurrency=0;
	1234	set global innodb_commit_concurrency=@my_innodb_commit_concurrency;
	1235	End of 5.0 tests
	1236	CREATE TABLE `t2` (
520.1.16 by Brian Aker More test updates (one ulong fix)	1237	`k` int NOT NULL auto_increment,
	1238	`a` int default NULL,
	1239	`c` int default NULL,
1 by brian clean slate	1240	PRIMARY KEY (`k`),
	1241	UNIQUE KEY `idx_1` (`a`)
	1242	);
	1243	insert into t2 ( a ) values ( 6 ) on duplicate key update c =
	1244	ifnull( c,
	1245	0 ) + 1;
	1246	insert into t2 ( a ) values ( 7 ) on duplicate key update c =
	1247	ifnull( c,
	1248	0 ) + 1;
	1249	select last_insert_id();
	1250	last_insert_id()
	1251	2
	1252	select * from t2;
	1253	k a c
	1254	1 6 NULL
	1255	2 7 NULL
	1256	insert into t2 ( a ) values ( 6 ) on duplicate key update c =
	1257	ifnull( c,
	1258	0 ) + 1;
	1259	select last_insert_id();
	1260	last_insert_id()
	1261	2
	1262	select last_insert_id(0);
	1263	last_insert_id(0)
	1264	0
	1265	insert into t2 ( a ) values ( 6 ) on duplicate key update c =
	1266	ifnull( c,
	1267	0 ) + 1;
	1268	select last_insert_id();
	1269	last_insert_id()
	1270	0
	1271	select * from t2;
	1272	k a c
	1273	1 6 2
	1274	2 7 NULL
	1275	insert ignore into t2 values (null,6,1),(10,8,1);
	1276	select last_insert_id();
	1277	last_insert_id()
	1278	0
	1279	insert ignore into t2 values (null,6,1),(null,8,1),(null,15,1),(null,20,1);
	1280	select last_insert_id();
	1281	last_insert_id()
	1282	11
	1283	select * from t2;
	1284	k a c
	1285	1 6 2
	1286	2 7 NULL
	1287	10 8 1
	1288	11 15 1
	1289	12 20 1
	1290	insert into t2 ( a ) values ( 6 ) on duplicate key update c =
	1291	ifnull( c,
	1292	0 ) + 1, k=last_insert_id(k);
	1293	select last_insert_id();
	1294	last_insert_id()
	1295	1
	1296	select * from t2;
	1297	k a c
	1298	1 6 3
	1299	2 7 NULL
	1300	10 8 1
	1301	11 15 1
	1302	12 20 1
	1303	drop table t2;
1304	drop table if exists t1, t2;
1305	create table t1 (i int);
1306	alter table t1 modify i int default 1;
1307	alter table t1 modify i int default 2, rename t2;
1308	lock table t2 write;
1309	alter table t2 modify i int default 3;
1310	unlock tables;
1311	lock table t2 write;
1312	alter table t2 modify i int default 4, rename t1;
1313	unlock tables;
1314	drop table t1;
1315	drop table if exists t1;
1316	create table t1 (i int);
1317	insert into t1 values ();
1318	lock table t1 write;
1319	alter table t1 modify i int default 1;
1320	insert into t1 values ();
1321	select * from t1;
1322	i
1323	NULL
1324	1
1325	alter table t1 change i c char(10) default "Two";
1326	insert into t1 values ();
1327	select * from t1;
1328	c
1329	NULL
1330	1
1331	Two
1332	unlock tables;
1333	select * from t1;
1334	c
1335	NULL
1336	1
1337	Two
1338	drop tables t1;
1339	create table t1(f1 varchar(5) unique, f2 timestamp NOT NULL DEFAULT
1340	CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
1341	insert into t1(f1) values(1);
1342	select @a:=f2 from t1;
520.1.16 by Brian Aker More test updates (one ulong fix)	1343	@a:=f2
1 by brian clean slate	1344	#
	1345	update t1 set f1=1;
	1346	select @b:=f2 from t1;
520.1.16 by Brian Aker More test updates (one ulong fix)	1347	@b:=f2
1 by brian clean slate	1348	#
	1349	select if(@a=@b,"ok","wrong");
	1350	if(@a=@b,"ok","wrong")
	1351	ok
	1352	insert into t1(f1) values (1) on duplicate key update f1="1";
	1353	select @b:=f2 from t1;
520.1.16 by Brian Aker More test updates (one ulong fix)	1354	@b:=f2
1 by brian clean slate	1355	#
	1356	select if(@a=@b,"ok","wrong");
	1357	if(@a=@b,"ok","wrong")
	1358	ok
	1359	insert into t1(f1) select f1 from t1 on duplicate key update f1="1";
	1360	select @b:=f2 from t1;
520.1.16 by Brian Aker More test updates (one ulong fix)	1361	@b:=f2
1 by brian clean slate	1362	#
	1363	select if(@a=@b,"ok","wrong");
	1364	if(@a=@b,"ok","wrong")
	1365	ok
	1366	drop table t1;
	1367	CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b)) engine=innodb;
	1368	CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
	1369	CONSTRAINT c2 FOREIGN KEY f2 (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb;
	1370	ERROR 42000: Incorrect foreign key definition for 'f2': Key reference and table reference don't match
	1371	CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
	1372	CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb;
	1373	ERROR 42000: Incorrect foreign key definition for 'c2': Key reference and table reference don't match
	1374	CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
	1375	CONSTRAINT c1 FOREIGN KEY c2 (c) REFERENCES t1 (a) ON DELETE NO ACTION,
	1376	CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb;
	1377	ALTER TABLE t2 DROP FOREIGN KEY c2;
	1378	DROP TABLE t2;
	1379	CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
	1380	FOREIGN KEY (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb;
	1381	ERROR 42000: Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match
	1382	CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
	1383	FOREIGN KEY f1 (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb;
	1384	ERROR 42000: Incorrect foreign key definition for 'f1': Key reference and table reference don't match
	1385	CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
	1386	CONSTRAINT c1 FOREIGN KEY f1 (c) REFERENCES t1 (a) ON DELETE NO ACTION,
	1387	CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION,
	1388	FOREIGN KEY f3 (c) REFERENCES t1 (a) ON UPDATE NO ACTION,
	1389	FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb;
	1390	SHOW CREATE TABLE t2;
	1391	Table Create Table
	1392	t2 CREATE TABLE `t2` (
520.1.16 by Brian Aker More test updates (one ulong fix)	1393	`c` int NOT NULL,
520.1.16 by Brian Aker More test updates (one ulong fix)	1394	`d` int NOT NULL,
1 by brian clean slate	1395	PRIMARY KEY (`c`,`d`),
	1396	CONSTRAINT `c1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION,
	1397	CONSTRAINT `c2` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION,
	1398	CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION,
	1399	CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION
520.1.16 by Brian Aker More test updates (one ulong fix)	1400	) ENGINE=InnoDB
1 by brian clean slate	1401	DROP TABLE t2;
	1402	DROP TABLE t1;
	1403	create table t1 (a int auto_increment primary key) engine=innodb;
	1404	alter table t1 order by a;
520.1.16 by Brian Aker More test updates (one ulong fix)	1405	ERROR HY000: order_st BY ignored because there is a user-defined clustered index in the table 't1'
1 by brian clean slate	1406	drop table t1;
	1407	CREATE TABLE t1
	1408	(vid integer NOT NULL,
	1409	tid integer NOT NULL,
	1410	idx integer NOT NULL,
	1411	name varchar(128) NOT NULL,
	1412	type varchar(128) NULL,
	1413	PRIMARY KEY(idx, vid, tid),
	1414	UNIQUE(vid, tid, name)
	1415	) ENGINE=InnoDB;
	1416	INSERT INTO t1 VALUES
	1417	(1,1,1,'pk',NULL),(2,1,1,'pk',NULL),(3,1,1,'pk',NULL),(4,1,1,'c1',NULL),
	1418	(5,1,1,'pk',NULL),(1,1,2,'c1',NULL),(2,1,2,'c1',NULL),(3,1,2,'c1',NULL),
	1419	(4,1,2,'c2',NULL),(5,1,2,'c1',NULL),(2,1,3,'c2',NULL),(3,1,3,'c2',NULL),
	1420	(4,1,3,'pk',NULL),(5,1,3,'c2',NULL),
	1421	(2,1,4,'c_extra',NULL),(3,1,4,'c_extra',NULL);
	1422	EXPLAIN SELECT * FROM t1 WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
	1423	id select_type table type possible_keys key key_len ref rows Extra
	1424	1 SIMPLE t1 index vid PRIMARY 12 NULL 16 Using where
	1425	SELECT * FROM t1 WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
	1426	vid tid idx name type
	1427	3 1 4 c_extra NULL
	1428	3 1 3 c2 NULL
	1429	3 1 2 c1 NULL
	1430	3 1 1 pk NULL
	1431	DROP TABLE t1;
	1432	DROP TABLE IF EXISTS t1;
	1433	DROP TABLE IF EXISTS t2;
	1434	CREATE TABLE t1(id INT PRIMARY KEY)
	1435	ENGINE=innodb;
	1436	CREATE TABLE t2(
	1437	t1_id INT PRIMARY KEY,
	1438	CONSTRAINT fk1 FOREIGN KEY (t1_id) REFERENCES t1(id))
	1439	ENGINE=innodb;
	1440
	1441	ALTER TABLE t1 CHANGE id id2 INT;
	1442
	1443	DROP TABLE t2;
	1444	DROP TABLE t1;
	1445	End of 5.1 tests