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

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

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