~drizzle-trunk/drizzle/development : contents of tests/r/update.result at revision 878

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

drop table if exists t1,t2;
create table t1 (a int auto_increment , primary key (a));
insert into t1 values (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
update t1 set a=a+10 where a > 34;
update t1 set a=a+100 where a > 0;
update t1 set a=a+100 where a=1 and a=2;
update t1 set a=b+100 where a=1 and a=2;
ERROR 42S22: Unknown column 'b' in 'field list'
update t1 set a=b+100 where c=1 and a=2;
ERROR 42S22: Unknown column 'c' in 'where clause'
update t1 set d=a+100 where a=1;
ERROR 42S22: Unknown column 'd' in 'field list'
select * from t1;
a
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
145
146
drop table t1;
CREATE TABLE t1
(
place_id int NOT NULL,
shows int DEFAULT '0' NOT NULL,
ishows int DEFAULT '0' NOT NULL,
ushows int DEFAULT '0' NOT NULL,
clicks int DEFAULT '0' NOT NULL,
iclicks int DEFAULT '0' NOT NULL,
uclicks int DEFAULT '0' NOT NULL,
ts timestamp,
PRIMARY KEY (place_id,ts)
);
INSERT INTO t1 (place_id,shows,ishows,ushows,clicks,iclicks,uclicks,ts)
VALUES (1,0,0,0,0,0,0,20000928174434);
UPDATE t1 SET shows=shows+1,ishows=ishows+1,ushows=ushows+1,clicks=clicks+1,iclicks=iclicks+1,uclicks=uclicks+1 WHERE place_id=1 AND ts>="2000-09-28 00:00:00";
select place_id,shows from t1;
place_id	shows
1	1
drop table t1;
CREATE TABLE t1 (
lfdnr int NOT NULL default '0',
ticket int NOT NULL default '0',
client varchar(255) NOT NULL default '',
replyto varchar(255) NOT NULL default '',
subject varchar(100) NOT NULL default '',
timestamp int NOT NULL default '0',
tstamp timestamp NOT NULL,
status int NOT NULL default '0',
type varchar(15) NOT NULL default '',
assignment int NOT NULL default '0',
fupcount int NOT NULL default '0',
parent int NOT NULL default '0',
activity int NOT NULL default '0',
priority int NOT NULL default '1',
cc varchar(255) NOT NULL default '',
bcc varchar(255) NOT NULL default '',
body text NOT NULL,
comment text,
header text,
PRIMARY KEY  (lfdnr),
KEY k1 (timestamp),
KEY k2 (type),
KEY k3 (parent),
KEY k4 (assignment),
KEY ticket (ticket)
) ENGINE=MyISAM;
INSERT INTO t1 VALUES (773,773,'','','',980257344,20010318180652,0,'Open',10,0,0,0,1,'','','','','');
alter table t1 change lfdnr lfdnr int not null auto_increment;
update t1 set status=1 where type='Open';
select status from t1;
status
1
drop table t1;
create table t1 (a int not null, b int not null, key (a));
insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3);
SET @tmp=0;
update t1 set b=(@tmp:=@tmp+1) order by a;
update t1 set b=99 where a=1 order by b asc limit 1;
select * from t1 order by a,b;
a	b
1	2
1	3
1	99
2	4
2	5
2	6
3	7
3	8
3	9
3	10
3	11
3	12
update t1 set b=100 where a=1 order by b desc limit 2;
update t1 set a=a+10+b where a=1 order by b;
select * from t1 order by a,b;
a	b
2	4
2	5
2	6
3	7
3	8
3	9
3	10
3	11
3	12
13	2
111	100
111	100
create table t2 (a int not null, b int not null);
insert into t2 values (1,1),(1,2),(1,3);
update t1 set b=(select distinct 1 from (select * from t2) a);
drop table t1,t2;
CREATE TABLE t1 (
`id_param` int NOT NULL default '0',
`nom_option` char(40) NOT NULL default '',
`valid` int NOT NULL default '0',
KEY `id_param` (`id_param`,`nom_option`)
) ENGINE=MyISAM;
INSERT INTO t1 (id_param,nom_option,valid) VALUES (185,'600x1200',1);
UPDATE t1 SET nom_option='test' WHERE id_param=185 AND nom_option='600x1200' AND valid=1 LIMIT 1;
select * from t1;
id_param	nom_option	valid
185	test	1
drop table t1;
create table t1 (F1 VARCHAR(30), F2 VARCHAR(30), F3 VARCHAR(30), cnt int, groupid int, KEY groupid_index (groupid));
insert into t1 (F1,F2,F3,cnt,groupid) values ('0','0','0',1,6),
('0','1','2',1,5), ('0','2','0',1,3), ('1','0','1',1,2),
('1','2','1',1,1), ('1','2','2',1,1), ('2','0','1',2,4),
('2','2','0',1,7);
delete from m1 using t1 m1,t1 m2 where m1.groupid=m2.groupid and (m1.cnt < m2.cnt or m1.cnt=m2.cnt and m1.F3>m2.F3);
select * from t1;
F1	F2	F3	cnt	groupid
0	0	0	1	6
0	1	2	1	5
0	2	0	1	3
1	0	1	1	2
1	2	1	1	1
2	0	1	2	4
2	2	0	1	7
drop table t1;
create table t1 (c1 int, c2 char(6), c3 int);
create table t2 (c1 int, c2 char(6));
insert into t1 values (1, "t1c2-1", 10), (2, "t1c2-2", 20);
update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1";
update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1" where t1.c3 = 10;
drop table t1, t2;
create table t1 (id int not null auto_increment primary key, id_str varchar(32));
insert into t1 (id_str) values ("test");
update t1 set id_str = concat(id_str, id) where id = last_insert_id();
select * from t1;
id	id_str
1	test1
drop table t1;
create table t1 (a int, b char(255), key(a, b(20)));
insert into t1 values (0, '1');
update t1 set b = b + 1 where a = 0;
select * from t1;
a	b
0	2
drop table t1;
create table t1 (a int, b varchar(10), key b(b(5))) engine=myisam;
create table t2 (a int, b varchar(10)) engine=myisam;
insert into t1 values ( 1, 'abcd1e');
insert into t1 values ( 2, 'abcd2e');
insert into t2 values ( 1, 'abcd1e');
insert into t2 values ( 2, 'abcd2e');
analyze table t1,t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
update t1, t2 set t1.a = t2.a where t2.b = t1.b;
show warnings;
Level	Code	Message
drop table t1, t2;
create table t1(f1 int, f2 int);
create table t2(f3 int, f4 int);
create index idx on t2(f3);
insert into t1 values(1,0),(2,0);
insert into t2 values(1,1),(2,2);
UPDATE t1 SET t1.f2=(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1);
select * from t1;
f1	f2
1	1
2	2
drop table t1,t2;
create table t1(f1 int);
select DATABASE();
DATABASE()
test
update t1 set f1=1 where count(*)=1;
ERROR HY000: Invalid use of group function
select DATABASE();
DATABASE()
test
delete from t1 where count(*)=1;
ERROR HY000: Invalid use of group function
drop table t1;
create table t1 ( a int, b int default 0, index (a) );
insert into t1 (a) values (0),(0),(0),(0),(0),(0),(0),(0);
flush status;
select a from t1 order by a limit 1;
a
0
show status like 'handler_read%';
Variable_name	Value
Handler_read_first	1
Handler_read_key	2
Handler_read_next	0
Handler_read_prev	0
Handler_read_rnd	0
Handler_read_rnd_next	0
flush status;
update t1 set a=9999 order by a limit 1;
update t1 set b=9999 order by a limit 1;
show status like 'handler_read%';
Variable_name	Value
Handler_read_first	2
Handler_read_key	12
Handler_read_next	0
Handler_read_prev	0
Handler_read_rnd	2
Handler_read_rnd_next	18
flush status;
delete from t1 order by a limit 1;
show status like 'handler_read%';
Variable_name	Value
Handler_read_first	1
Handler_read_key	6
Handler_read_next	0
Handler_read_prev	0
Handler_read_rnd	1
Handler_read_rnd_next	9
flush status;
delete from t1 order by a desc limit 1;
show status like 'handler_read%';
Variable_name	Value
Handler_read_first	1
Handler_read_key	6
Handler_read_next	0
Handler_read_prev	0
Handler_read_rnd	1
Handler_read_rnd_next	8
alter table t1 disable keys;
Warnings:
Note	1031	Table storage engine for 't1' doesn't have this option
flush status;
delete from t1 order by a limit 1;
show status like 'handler_read%';
Variable_name	Value
Handler_read_first	1
Handler_read_key	6
Handler_read_next	0
Handler_read_prev	0
Handler_read_rnd	1
Handler_read_rnd_next	7
select * from t1;
a	b
0	0
0	0
0	0
0	0
0	0
update t1 set a=a+10,b=1 order by a limit 3;
update t1 set a=a+11,b=2 order by a limit 3;
update t1 set a=a+12,b=3 order by a limit 3;
select * from t1 order by a;
a	b
11	2
21	2
22	3
22	3
23	3
drop table t1;
create table t1 (f1 date NULL);
insert into t1 values('2000-01-01'),(NULL);
update t1 set f1='2002-02-02' where f1 is null;
select * from t1;
f1
2000-01-01
2002-02-02
drop table t1;
create table t1 (f1 int);
create table t2 (f2 int);
insert into t1 values(1),(2);
insert into t2 values(1),(1);
update t1,t2 set f1=3,f2=3 where f1=f2 and f1=1;
affected rows: 3
info: Rows matched: 3  Changed: 3  Warnings: 0
update t2 set f2=1;
update t1 set f1=1 where f1=3;
update t2,t1 set f1=3,f2=3 where f1=f2 and f1=1;
affected rows: 3
info: Rows matched: 3  Changed: 3  Warnings: 0
drop table t1,t2;
create table t1 (a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2 (a int, filler1 char(200), filler2 char(200), key(a));
insert into t2 select A.a + 10*B.a, 'filler','filler' from t1 A, t1 B;
flush status;
update t2 set a=3 where a=2;
show status like 'handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	6
Handler_read_next	1
Handler_read_prev	0
Handler_read_rnd	1
Handler_read_rnd_next	0
drop table t1, t2;
create table t1(f1 int, `*f2` int);
insert into t1 values (1,1);
update t1 set `*f2`=1;
drop table t1;
create table t1(f1 int);
update t1 set f2=1 order by f2;
ERROR 42S22: Unknown column 'f2' in 'order clause'
drop table t1;
CREATE TABLE t1 (
request_id int NOT NULL auto_increment,
user_id varchar(12) default NULL,
time_stamp datetime,
ip_address varchar(15) default NULL,
PRIMARY KEY (request_id),
KEY user_id_2 (user_id,time_stamp)
);
INSERT INTO t1 (user_id) VALUES ('user1');
INSERT INTO t1(user_id) SELECT user_id FROM t1;
INSERT INTO t1(user_id) SELECT user_id FROM t1;
INSERT INTO t1(user_id) SELECT user_id FROM t1;
INSERT INTO t1(user_id) SELECT user_id FROM t1;
INSERT INTO t1(user_id) SELECT user_id FROM t1;
INSERT INTO t1(user_id) SELECT user_id FROM t1;
INSERT INTO t1(user_id) SELECT user_id FROM t1;
INSERT INTO t1(user_id) SELECT user_id FROM t1;
flush status;
SELECT user_id FROM t1 WHERE request_id=9999999999999;
user_id
show status like '%Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	2
Handler_read_next	0
Handler_read_prev	0
Handler_read_rnd	0
Handler_read_rnd_next	0
SELECT user_id FROM t1 WHERE request_id=999999999999999999999999999999;
user_id
show status like '%Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	4
Handler_read_next	0
Handler_read_prev	0
Handler_read_rnd	0
Handler_read_rnd_next	0
UPDATE t1 SET user_id=null WHERE request_id=9999999999999;
show status like '%Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	6
Handler_read_next	0
Handler_read_prev	0
Handler_read_rnd	0
Handler_read_rnd_next	0
UPDATE t1 SET user_id=null WHERE request_id=999999999999999999999999999999;
show status like '%Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	6
Handler_read_next	0
Handler_read_prev	0
Handler_read_rnd	0
Handler_read_rnd_next	0
DROP TABLE t1;
CREATE TABLE t1 (
a int,
quux decimal( 31, 30 ),
UNIQUE KEY bar (a),
KEY quux (quux)
);
INSERT INTO
t1 ( a, quux )
VALUES
( 1,    1 ),
( 2,  0.1 );
INSERT INTO t1( a )
SELECT @newA := 1 + a FROM t1 WHERE quux <= 0.1;
SELECT * FROM t1;
a	quux
1	1.000000000000000000000000000000
2	0.100000000000000000000000000000
3	NULL
DROP TABLE t1;
set tmp_table_size=1024;
create table t1 (id int, a int, key idx(a));
create table t2 (id int not null auto_increment primary key, a int);
insert into t2(a) values(1),(2),(3),(4),(5),(6),(7),(8);
insert into t2(a) select a from t2;
insert into t2(a) select a from t2;
insert into t2(a) select a from t2;
update t2 set a=id;
insert into t1 select * from t2;
select count(*) from t1 join t2 on (t1.a=t2.a);
count(*)
64
update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
affected rows: 0
info: Rows matched: 64  Changed: 0  Warnings: 0
insert into t2(a) select a from t2;
update t2 set a=id;
truncate t1;
insert into t1 select * from t2;
select count(*) from t1 join t2 on (t1.a=t2.a);
count(*)
128
update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
affected rows: 0
info: Rows matched: 128  Changed: 0  Warnings: 0
update t1 set a=1;
update t2 set a=1;
select count(*) from t1 join t2 on (t1.a=t2.a);
count(*)
16384
update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
affected rows: 127
info: Rows matched: 128  Changed: 127  Warnings: 0
drop table t1,t2;
End of 5.0 tests

1 by brian clean slate	1	drop table if exists t1,t2;
	2	create table t1 (a int auto_increment , primary key (a));
	3	insert into t1 values (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
	4	update t1 set a=a+10 where a > 34;
	5	update t1 set a=a+100 where a > 0;
	6	update t1 set a=a+100 where a=1 and a=2;
	7	update t1 set a=b+100 where a=1 and a=2;
	8	ERROR 42S22: Unknown column 'b' in 'field list'
	9	update t1 set a=b+100 where c=1 and a=2;
	10	ERROR 42S22: Unknown column 'c' in 'where clause'
	11	update t1 set d=a+100 where a=1;
	12	ERROR 42S22: Unknown column 'd' in 'field list'
	13	select * from t1;
	14	a
	15	101
	16	102
	17	103
	18	104
	19	105
	20	106
	21	107
	22	108
	23	109
	24	110
	25	111
	26	112
	27	113
	28	114
	29	115
	30	116
	31	117
	32	118
	33	119
	34	120
	35	121
	36	122
	37	123
	38	124
	39	125
	40	126
	41	127
	42	128
	43	129
	44	130
	45	131
	46	132
	47	133
	48	134
	49	145
	50	146
	51	drop table t1;
	52	CREATE TABLE t1
	53	(
413.2.2 by Brian Aker Removed UNSIGNED from parser.	54	place_id int NOT NULL,
	55	shows int DEFAULT '0' NOT NULL,
	56	ishows int DEFAULT '0' NOT NULL,
	57	ushows int DEFAULT '0' NOT NULL,
	58	clicks int DEFAULT '0' NOT NULL,
	59	iclicks int DEFAULT '0' NOT NULL,
	60	uclicks int DEFAULT '0' NOT NULL,
1 by brian clean slate	61	ts timestamp,
	62	PRIMARY KEY (place_id,ts)
	63	);
	64	INSERT INTO t1 (place_id,shows,ishows,ushows,clicks,iclicks,uclicks,ts)
	65	VALUES (1,0,0,0,0,0,0,20000928174434);
	66	UPDATE t1 SET shows=shows+1,ishows=ishows+1,ushows=ushows+1,clicks=clicks+1,iclicks=iclicks+1,uclicks=uclicks+1 WHERE place_id=1 AND ts>="2000-09-28 00:00:00";
	67	select place_id,shows from t1;
	68	place_id shows
	69	1 1
	70	drop table t1;
	71	CREATE TABLE t1 (
413.2.2 by Brian Aker Removed UNSIGNED from parser.	72	lfdnr int NOT NULL default '0',
413.2.2 by Brian Aker Removed UNSIGNED from parser.	73	ticket int NOT NULL default '0',
1 by brian clean slate	74	client varchar(255) NOT NULL default '',
	75	replyto varchar(255) NOT NULL default '',
	76	subject varchar(100) NOT NULL default '',
413.2.2 by Brian Aker Removed UNSIGNED from parser.	77	timestamp int NOT NULL default '0',
1 by brian clean slate	78	tstamp timestamp NOT NULL,
223 by Brian Aker Cleanup int() work.	79	status int NOT NULL default '0',
1 by brian clean slate	80	type varchar(15) NOT NULL default '',
413.2.2 by Brian Aker Removed UNSIGNED from parser.	81	assignment int NOT NULL default '0',
	82	fupcount int NOT NULL default '0',
	83	parent int NOT NULL default '0',
	84	activity int NOT NULL default '0',
	85	priority int NOT NULL default '1',
1 by brian clean slate	86	cc varchar(255) NOT NULL default '',
	87	bcc varchar(255) NOT NULL default '',
	88	body text NOT NULL,
	89	comment text,
	90	header text,
	91	PRIMARY KEY (lfdnr),
	92	KEY k1 (timestamp),
	93	KEY k2 (type),
	94	KEY k3 (parent),
	95	KEY k4 (assignment),
	96	KEY ticket (ticket)
	97	) ENGINE=MyISAM;
	98	INSERT INTO t1 VALUES (773,773,'','','',980257344,20010318180652,0,'Open',10,0,0,0,1,'','','','','');
413.2.2 by Brian Aker Removed UNSIGNED from parser.	99	alter table t1 change lfdnr lfdnr int not null auto_increment;
1 by brian clean slate	100	update t1 set status=1 where type='Open';
	101	select status from t1;
	102	status
	103	1
	104	drop table t1;
	105	create table t1 (a int not null, b int not null, key (a));
	106	insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3);
	107	SET @tmp=0;
	108	update t1 set b=(@tmp:=@tmp+1) order by a;
	109	update t1 set b=99 where a=1 order by b asc limit 1;
	110	select * from t1 order by a,b;
	111	a b
	112	1 2
	113	1 3
	114	1 99
	115	2 4
	116	2 5
	117	2 6
	118	3 7
	119	3 8
	120	3 9
	121	3 10
	122	3 11
	123	3 12
	124	update t1 set b=100 where a=1 order by b desc limit 2;
	125	update t1 set a=a+10+b where a=1 order by b;
	126	select * from t1 order by a,b;
	127	a b
	128	2 4
	129	2 5
	130	2 6
	131	3 7
	132	3 8
	133	3 9
	134	3 10
	135	3 11
	136	3 12
	137	13 2
	138	111 100
	139	111 100
	140	create table t2 (a int not null, b int not null);
	141	insert into t2 values (1,1),(1,2),(1,3);
	142	update t1 set b=(select distinct 1 from (select * from t2) a);
	143	drop table t1,t2;
	144	CREATE TABLE t1 (
413.2.2 by Brian Aker Removed UNSIGNED from parser.	145	`id_param` int NOT NULL default '0',
1 by brian clean slate	146	`nom_option` char(40) NOT NULL default '',
396 by Brian Aker Cleanup tiny and small int.	147	`valid` int NOT NULL default '0',
1 by brian clean slate	148	KEY `id_param` (`id_param`,`nom_option`)
	149	) ENGINE=MyISAM;
	150	INSERT INTO t1 (id_param,nom_option,valid) VALUES (185,'600x1200',1);
	151	UPDATE t1 SET nom_option='test' WHERE id_param=185 AND nom_option='600x1200' AND valid=1 LIMIT 1;
	152	select * from t1;
	153	id_param nom_option valid
	154	185 test 1
	155	drop table t1;
	156	create table t1 (F1 VARCHAR(30), F2 VARCHAR(30), F3 VARCHAR(30), cnt int, groupid int, KEY groupid_index (groupid));
	157	insert into t1 (F1,F2,F3,cnt,groupid) values ('0','0','0',1,6),
	158	('0','1','2',1,5), ('0','2','0',1,3), ('1','0','1',1,2),
	159	('1','2','1',1,1), ('1','2','2',1,1), ('2','0','1',2,4),
	160	('2','2','0',1,7);
	161	delete from m1 using t1 m1,t1 m2 where m1.groupid=m2.groupid and (m1.cnt < m2.cnt or m1.cnt=m2.cnt and m1.F3>m2.F3);
	162	select * from t1;
	163	F1 F2 F3 cnt groupid
	164	0 0 0 1 6
	165	0 1 2 1 5
	166	0 2 0 1 3
	167	1 0 1 1 2
	168	1 2 1 1 1
	169	2 0 1 2 4
	170	2 2 0 1 7
	171	drop table t1;
	172	create table t1 (c1 int, c2 char(6), c3 int);
	173	create table t2 (c1 int, c2 char(6));
	174	insert into t1 values (1, "t1c2-1", 10), (2, "t1c2-2", 20);
	175	update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1";
	176	update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1" where t1.c3 = 10;
	177	drop table t1, t2;
	178	create table t1 (id int not null auto_increment primary key, id_str varchar(32));
	179	insert into t1 (id_str) values ("test");
	180	update t1 set id_str = concat(id_str, id) where id = last_insert_id();
	181	select * from t1;
	182	id id_str
	183	1 test1
	184	drop table t1;
	185	create table t1 (a int, b char(255), key(a, b(20)));
	186	insert into t1 values (0, '1');
	187	update t1 set b = b + 1 where a = 0;
	188	select * from t1;
	189	a b
	190	0 2
	191	drop table t1;
	192	create table t1 (a int, b varchar(10), key b(b(5))) engine=myisam;
	193	create table t2 (a int, b varchar(10)) engine=myisam;
	194	insert into t1 values ( 1, 'abcd1e');
	195	insert into t1 values ( 2, 'abcd2e');
	196	insert into t2 values ( 1, 'abcd1e');
	197	insert into t2 values ( 2, 'abcd2e');
	198	analyze table t1,t2;
	199	Table Op Msg_type Msg_text
	200	test.t1 analyze status OK
	201	test.t2 analyze status OK
	202	update t1, t2 set t1.a = t2.a where t2.b = t1.b;
	203	show warnings;
	204	Level Code Message
	205	drop table t1, t2;
	206	create table t1(f1 int, f2 int);
	207	create table t2(f3 int, f4 int);
	208	create index idx on t2(f3);
	209	insert into t1 values(1,0),(2,0);
	210	insert into t2 values(1,1),(2,2);
	211	UPDATE t1 SET t1.f2=(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1);
212	select * from t1;
213	f1 f2
214	1 1
215	2 2
216	drop table t1,t2;
217	create table t1(f1 int);
218	select DATABASE();
219	DATABASE()
220	test
221	update t1 set f1=1 where count(*)=1;
222	ERROR HY000: Invalid use of group function
223	select DATABASE();
224	DATABASE()
225	test
226	delete from t1 where count(*)=1;
227	ERROR HY000: Invalid use of group function
228	drop table t1;
229	create table t1 ( a int, b int default 0, index (a) );
230	insert into t1 (a) values (0),(0),(0),(0),(0),(0),(0),(0);
231	flush status;
232	select a from t1 order by a limit 1;
233	a
234	0
235	show status like 'handler_read%';
236	Variable_name Value
237	Handler_read_first 1
201 by Brian Aker Convert default engine to Innodb	238	Handler_read_key 2
1 by brian clean slate	239	Handler_read_next 0
	240	Handler_read_prev 0
	241	Handler_read_rnd 0
	242	Handler_read_rnd_next 0
	243	flush status;
	244	update t1 set a=9999 order by a limit 1;
	245	update t1 set b=9999 order by a limit 1;
	246	show status like 'handler_read%';
	247	Variable_name Value
201 by Brian Aker Convert default engine to Innodb	248	Handler_read_first 2
520.4.1 by Monty Taylor Imported InnoDB plugin with changes.	249	Handler_read_key 12
1 by brian clean slate	250	Handler_read_next 0
	251	Handler_read_prev 0
	252	Handler_read_rnd 2
201 by Brian Aker Convert default engine to Innodb	253	Handler_read_rnd_next 18
1 by brian clean slate	254	flush status;
	255	delete from t1 order by a limit 1;
	256	show status like 'handler_read%';
	257	Variable_name Value
	258	Handler_read_first 1
520.4.1 by Monty Taylor Imported InnoDB plugin with changes.	259	Handler_read_key 6
1 by brian clean slate	260	Handler_read_next 0
1 by brian clean slate	261	Handler_read_prev 0
201 by Brian Aker Convert default engine to Innodb	262	Handler_read_rnd 1
201 by Brian Aker Convert default engine to Innodb	263	Handler_read_rnd_next 9
1 by brian clean slate	264	flush status;
	265	delete from t1 order by a desc limit 1;
	266	show status like 'handler_read%';
	267	Variable_name Value
201 by Brian Aker Convert default engine to Innodb	268	Handler_read_first 1
520.4.1 by Monty Taylor Imported InnoDB plugin with changes.	269	Handler_read_key 6
1 by brian clean slate	270	Handler_read_next 0
	271	Handler_read_prev 0
	272	Handler_read_rnd 1
201 by Brian Aker Convert default engine to Innodb	273	Handler_read_rnd_next 8
1 by brian clean slate	274	alter table t1 disable keys;
201 by Brian Aker Convert default engine to Innodb	275	Warnings:
201 by Brian Aker Convert default engine to Innodb	276	Note 1031 Table storage engine for 't1' doesn't have this option
1 by brian clean slate	277	flush status;
	278	delete from t1 order by a limit 1;
	279	show status like 'handler_read%';
	280	Variable_name Value
201 by Brian Aker Convert default engine to Innodb	281	Handler_read_first 1
520.4.1 by Monty Taylor Imported InnoDB plugin with changes.	282	Handler_read_key 6
1 by brian clean slate	283	Handler_read_next 0
	284	Handler_read_prev 0
	285	Handler_read_rnd 1
201 by Brian Aker Convert default engine to Innodb	286	Handler_read_rnd_next 7
1 by brian clean slate	287	select * from t1;
	288	a b
	289	0 0
	290	0 0
	291	0 0
	292	0 0
	293	0 0
	294	update t1 set a=a+10,b=1 order by a limit 3;
	295	update t1 set a=a+11,b=2 order by a limit 3;
	296	update t1 set a=a+12,b=3 order by a limit 3;
	297	select * from t1 order by a;
	298	a b
	299	11 2
	300	21 2
	301	22 3
	302	22 3
	303	23 3
	304	drop table t1;
873.1.1 by Jay Pipes Fixes the Field_date class to not allow any invalid input at	305	create table t1 (f1 date NULL);
	306	insert into t1 values('2000-01-01'),(NULL);
1 by brian clean slate	307	update t1 set f1='2002-02-02' where f1 is null;
	308	select * from t1;
	309	f1
	310	2000-01-01
	311	2002-02-02
	312	drop table t1;
	313	create table t1 (f1 int);
	314	create table t2 (f2 int);
	315	insert into t1 values(1),(2);
	316	insert into t2 values(1),(1);
	317	update t1,t2 set f1=3,f2=3 where f1=f2 and f1=1;
	318	affected rows: 3
	319	info: Rows matched: 3 Changed: 3 Warnings: 0
	320	update t2 set f2=1;
	321	update t1 set f1=1 where f1=3;
	322	update t2,t1 set f1=3,f2=3 where f1=f2 and f1=1;
	323	affected rows: 3
	324	info: Rows matched: 3 Changed: 3 Warnings: 0
	325	drop table t1,t2;
	326	create table t1 (a int);
	327	insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
	328	create table t2 (a int, filler1 char(200), filler2 char(200), key(a));
	329	insert into t2 select A.a + 10*B.a, 'filler','filler' from t1 A, t1 B;
	330	flush status;
	331	update t2 set a=3 where a=2;
	332	show status like 'handler_read%';
	333	Variable_name Value
	334	Handler_read_first 0
520.4.1 by Monty Taylor Imported InnoDB plugin with changes.	335	Handler_read_key 6
1 by brian clean slate	336	Handler_read_next 1
	337	Handler_read_prev 0
	338	Handler_read_rnd 1
	339	Handler_read_rnd_next 0
	340	drop table t1, t2;
	341	create table t1(f1 int, `*f2` int);
	342	insert into t1 values (1,1);
	343	update t1 set `*f2`=1;
	344	drop table t1;
	345	create table t1(f1 int);
	346	update t1 set f2=1 order by f2;
	347	ERROR 42S22: Unknown column 'f2' in 'order clause'
	348	drop table t1;
	349	CREATE TABLE t1 (
413.2.2 by Brian Aker Removed UNSIGNED from parser.	350	request_id int NOT NULL auto_increment,
1 by brian clean slate	351	user_id varchar(12) default NULL,
873.1.2 by Jay Pipes Fixed Field_datetime to never accept any bad datetimes as a string. This broke	352	time_stamp datetime,
1 by brian clean slate	353	ip_address varchar(15) default NULL,
	354	PRIMARY KEY (request_id),
	355	KEY user_id_2 (user_id,time_stamp)
	356	);
	357	INSERT INTO t1 (user_id) VALUES ('user1');
	358	INSERT INTO t1(user_id) SELECT user_id FROM t1;
	359	INSERT INTO t1(user_id) SELECT user_id FROM t1;
	360	INSERT INTO t1(user_id) SELECT user_id FROM t1;
	361	INSERT INTO t1(user_id) SELECT user_id FROM t1;
	362	INSERT INTO t1(user_id) SELECT user_id FROM t1;
	363	INSERT INTO t1(user_id) SELECT user_id FROM t1;
	364	INSERT INTO t1(user_id) SELECT user_id FROM t1;
	365	INSERT INTO t1(user_id) SELECT user_id FROM t1;
	366	flush status;
	367	SELECT user_id FROM t1 WHERE request_id=9999999999999;
	368	user_id
	369	show status like '%Handler_read%';
	370	Variable_name Value
	371	Handler_read_first 0
201 by Brian Aker Convert default engine to Innodb	372	Handler_read_key 2
1 by brian clean slate	373	Handler_read_next 0
	374	Handler_read_prev 0
	375	Handler_read_rnd 0
	376	Handler_read_rnd_next 0
	377	SELECT user_id FROM t1 WHERE request_id=999999999999999999999999999999;
	378	user_id
	379	show status like '%Handler_read%';
	380	Variable_name Value
	381	Handler_read_first 0
201 by Brian Aker Convert default engine to Innodb	382	Handler_read_key 4
1 by brian clean slate	383	Handler_read_next 0
	384	Handler_read_prev 0
	385	Handler_read_rnd 0
	386	Handler_read_rnd_next 0
	387	UPDATE t1 SET user_id=null WHERE request_id=9999999999999;
	388	show status like '%Handler_read%';
	389	Variable_name Value
	390	Handler_read_first 0
201 by Brian Aker Convert default engine to Innodb	391	Handler_read_key 6
1 by brian clean slate	392	Handler_read_next 0
	393	Handler_read_prev 0
	394	Handler_read_rnd 0
	395	Handler_read_rnd_next 0
	396	UPDATE t1 SET user_id=null WHERE request_id=999999999999999999999999999999;
	397	show status like '%Handler_read%';
	398	Variable_name Value
	399	Handler_read_first 0
201 by Brian Aker Convert default engine to Innodb	400	Handler_read_key 6
1 by brian clean slate	401	Handler_read_next 0
	402	Handler_read_prev 0
	403	Handler_read_rnd 0
	404	Handler_read_rnd_next 0
	405	DROP TABLE t1;
	406	CREATE TABLE t1 (
223 by Brian Aker Cleanup int() work.	407	a int,
1 by brian clean slate	408	quux decimal( 31, 30 ),
	409	UNIQUE KEY bar (a),
	410	KEY quux (quux)
	411	);
	412	INSERT INTO
	413	t1 ( a, quux )
	414	VALUES
	415	( 1, 1 ),
	416	( 2, 0.1 );
	417	INSERT INTO t1( a )
	418	SELECT @newA := 1 + a FROM t1 WHERE quux <= 0.1;
	419	SELECT * FROM t1;
	420	a quux
	421	1 1.000000000000000000000000000000
	422	2 0.100000000000000000000000000000
	423	3 NULL
	424	DROP TABLE t1;
	425	set tmp_table_size=1024;
	426	create table t1 (id int, a int, key idx(a));
413.2.2 by Brian Aker Removed UNSIGNED from parser.	427	create table t2 (id int not null auto_increment primary key, a int);
1 by brian clean slate	428	insert into t2(a) values(1),(2),(3),(4),(5),(6),(7),(8);
	429	insert into t2(a) select a from t2;
	430	insert into t2(a) select a from t2;
	431	insert into t2(a) select a from t2;
	432	update t2 set a=id;
	433	insert into t1 select * from t2;
	434	select count(*) from t1 join t2 on (t1.a=t2.a);
	435	count(*)
	436	64
	437	update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
520.4.1 by Monty Taylor Imported InnoDB plugin with changes.	438	affected rows: 0
	439	info: Rows matched: 64 Changed: 0 Warnings: 0
1 by brian clean slate	440	insert into t2(a) select a from t2;
	441	update t2 set a=id;
	442	truncate t1;
	443	insert into t1 select * from t2;
	444	select count(*) from t1 join t2 on (t1.a=t2.a);
	445	count(*)
	446	128
	447	update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
520.4.1 by Monty Taylor Imported InnoDB plugin with changes.	448	affected rows: 0
	449	info: Rows matched: 128 Changed: 0 Warnings: 0
1 by brian clean slate	450	update t1 set a=1;
	451	update t2 set a=1;
	452	select count(*) from t1 join t2 on (t1.a=t2.a);
	453	count(*)
	454	16384
	455	update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
520.4.1 by Monty Taylor Imported InnoDB plugin with changes.	456	affected rows: 127
	457	info: Rows matched: 128 Changed: 127 Warnings: 0
1 by brian clean slate	458	drop table t1,t2;
1 by brian clean slate	459	End of 5.0 tests