~drizzle-trunk/drizzle/development

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 TEMPORARY 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 TEMPORARY 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 (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(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	#
Handler_read_key	#
Handler_read_next	#
Handler_read_prev	#
Handler_read_rnd	#
Handler_read_rnd_next	#
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	#
Handler_read_key	#
Handler_read_next	#
Handler_read_prev	#
Handler_read_rnd	#
Handler_read_rnd_next	#
flush status;
delete from t1 order by a limit 1;
show status like 'handler_read%';
Variable_name	Value
Handler_read_first	#
Handler_read_key	#
Handler_read_next	#
Handler_read_prev	#
Handler_read_rnd	#
Handler_read_rnd_next	#
flush status;
delete from t1 order by a desc limit 1;
show status like 'handler_read%';
Variable_name	Value
Handler_read_first	#
Handler_read_key	#
Handler_read_next	#
Handler_read_prev	#
Handler_read_rnd	#
Handler_read_rnd_next	#
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	#
Handler_read_key	#
Handler_read_next	#
Handler_read_prev	#
Handler_read_rnd	#
Handler_read_rnd_next	#
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 (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	#
Handler_read_key	#
Handler_read_next	#
Handler_read_prev	#
Handler_read_rnd	#
Handler_read_rnd_next	#
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	#
Handler_read_key	#
Handler_read_next	#
Handler_read_prev	#
Handler_read_rnd	#
Handler_read_rnd_next	#
SELECT user_id FROM t1 WHERE request_id=999999999999999999999999999999;
user_id
show status like '%Handler_read%';
Variable_name	Value
Handler_read_first	#
Handler_read_key	#
Handler_read_next	#
Handler_read_prev	#
Handler_read_rnd	#
Handler_read_rnd_next	#
UPDATE t1 SET user_id=null WHERE request_id=9999999999999;
show status like '%Handler_read%';
Variable_name	Value
Handler_read_first	#
Handler_read_key	#
Handler_read_next	#
Handler_read_prev	#
Handler_read_rnd	#
Handler_read_rnd_next	#
UPDATE t1 SET user_id=null WHERE request_id=999999999999999999999999999999;
Warnings:
Warning	1264	Out of range value for column 'request_id' at row 1
Warning	1264	Out of range value for column 'request_id' at row 1
show status like '%Handler_read%';
Variable_name	Value
Handler_read_first	#
Handler_read_key	#
Handler_read_next	#
Handler_read_prev	#
Handler_read_rnd	#
Handler_read_rnd_next	#
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
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 set a=1;
update t2 set a=1;
select count(*) from t1 join t2 on (t1.a=t2.a);
count(*)
16384
drop table t1,t2;
CREATE TABLE t1(col1 enum('a','b') NOT NULL, col2 enum('a','b') DEFAULT NULL, KEY col2 (col2));
UPDATE t1 SET col1 = "crash" WHERE col2 = now() ;
drop table t1;
End of 5.0 tests