multi_update.test 19.7 KB
Newer Older
1
#
2
# Test of update statement that uses many tables.
3 4
#

5
# Requires grants, so won't work with embedded server test
unknown's avatar
unknown committed
6 7
source include/not_embedded.inc;
source include/have_log_bin.inc;
8

9
--disable_warnings
10
drop table if exists t1,t2,t3;
11
drop database if exists mysqltest;
unknown's avatar
unknown committed
12
drop view if exists v1;
13
--error 0,1141,1147
unknown's avatar
unknown committed
14
revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;
15
--error 0,1141,1147
unknown's avatar
unknown committed
16 17
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
delete from mysql.user where user=_binary'mysqltest_1';
18 19
--enable_warnings

20
create table t1(id1 int not null auto_increment primary key, t char(12));
unknown's avatar
unknown committed
21
create table t2(id2 int not null, t char(12));
22
create table t3(id3 int not null, t char(12), index(id3));
unknown's avatar
unknown committed
23
disable_query_log;
24
let $1 = 100;
25 26
while ($1)
 {
27
  let $2 = 5;
28 29 30 31
  eval insert into t1(t) values ('$1'); 
  while ($2)
   {
     eval insert into t2(id2,t) values ($1,'$2'); 
32 33 34 35 36 37
     let $3 = 10;
     while ($3)
     {
       eval insert into t3(id3,t) values ($1,'$2'); 
       dec $3;
     }
38 39 40 41
     dec $2; 
   }
  dec $1;
 }
unknown's avatar
unknown committed
42
enable_query_log;
43 44 45 46 47

select count(*) from t1 where id1 > 95;
select count(*) from t2 where id2 > 95;
select count(*) from t3 where id3 > 95;

48
update t1,t2,t3 set t1.t="aaa", t2.t="bbb", t3.t="cc" where  t1.id1 = t2.id2 and t2.id2 = t3.id3  and t1.id1 > 90;
49 50 51 52 53 54
select count(*) from t1 where t = "aaa";
select count(*) from t1 where id1 > 90;
select count(*) from t2 where t = "bbb";
select count(*) from t2 where id2 > 90;
select count(*) from t3 where t = "cc";
select count(*) from t3 where id3 > 90;
55
delete t1.*, t2.*, t3.*  from t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3  and t1.id1 > 95;
56 57 58

check table t1, t2, t3;

59 60 61
select count(*) from t1 where id1 > 95;
select count(*) from t2 where id2 > 95;
select count(*) from t3 where id3 > 95;
62

63 64 65 66
delete t1, t2, t3  from t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3  and t1.id1 > 5;
select count(*) from t1 where id1 > 5;
select count(*) from t2 where id2 > 5;
select count(*) from t3 where id3 > 5;
67

68
delete from t1, t2, t3  using t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3  and t1.id1 > 0;
69 70 71 72 73 74

# These queries will force a scan of the table
select count(*) from t1 where id1;
select count(*) from t2 where id2;
select count(*) from t3 where id3;
drop table t1,t2,t3;
unknown's avatar
unknown committed
75

76 77
create table t1(id1 int not null  primary key, t varchar(100)) pack_keys = 1;
create table t2(id2 int not null, t varchar(100), index(id2)) pack_keys = 1;
78 79 80 81 82
disable_query_log;
let $1 = 1000;
while ($1)
 {
  let $2 = 5;
83
  eval insert into t1 values ($1,'aaaaaaaaaaaaaaaaaaaa'); 
84 85 86 87 88 89 90 91
  while ($2)
   {
     eval insert into t2(id2,t) values ($1,'bbbbbbbbbbbbbbbbb'); 
     dec $2; 
   }
  dec $1;
 }
enable_query_log;
92
delete t1  from t1,t2 where t1.id1 = t2.id2 and t1.id1 > 500;
93
drop table t1,t2;
unknown's avatar
unknown committed
94 95

CREATE TABLE t1 (
96 97 98
  id int(11) NOT NULL default '0',
  name varchar(10) default NULL,
  PRIMARY KEY  (id)
unknown's avatar
unknown committed
99
) ENGINE=MyISAM;
unknown's avatar
unknown committed
100 101
INSERT INTO t1 VALUES (1,'aaa'),(2,'aaa'),(3,'aaa');
CREATE TABLE t2 (
102 103 104
  id int(11) NOT NULL default '0',
  name varchar(10) default NULL,
  PRIMARY KEY  (id)
unknown's avatar
unknown committed
105
) ENGINE=MyISAM;
unknown's avatar
unknown committed
106 107
INSERT INTO t2 VALUES (2,'bbb'),(3,'bbb'),(4,'bbb');
CREATE TABLE t3 (
108 109 110
  id int(11) NOT NULL default '0',
  mydate datetime default NULL,
  PRIMARY KEY  (id)
unknown's avatar
unknown committed
111
) ENGINE=MyISAM;
unknown's avatar
unknown committed
112
INSERT INTO t3 VALUES (1,'2002-02-04 00:00:00'),(3,'2002-05-12 00:00:00'),(5,'2002-05-12 00:00:00'),(6,'2002-06-22
113
00:00:00'),(7,'2002-07-22 00:00:00');
unknown's avatar
unknown committed
114 115
delete t1,t2,t3 from t1,t2,t3 where to_days(now())-to_days(t3.mydate)>=30 and t3.id=t1.id and t3.id=t2.id;
select * from t3;
116
DROP TABLE t1,t2,t3;
unknown's avatar
unknown committed
117 118

CREATE TABLE IF NOT EXISTS `t1` (
119 120 121 122
  `id` int(11) NOT NULL auto_increment,
  `tst` text,
  `tst1` text,
  PRIMARY KEY  (`id`)
unknown's avatar
unknown committed
123
) ENGINE=MyISAM;
124

unknown's avatar
unknown committed
125
CREATE TABLE IF NOT EXISTS `t2` (
126 127 128 129 130
  `ID` int(11) NOT NULL auto_increment,
  `ParId` int(11) default NULL,
  `tst` text,
  `tst1` text,
  PRIMARY KEY  (`ID`),
unknown's avatar
unknown committed
131 132
  KEY `IX_ParId_t2` (`ParId`),
  FOREIGN KEY (`ParId`) REFERENCES `t1` (`id`)
unknown's avatar
unknown committed
133
) ENGINE=MyISAM;
134

unknown's avatar
unknown committed
135
INSERT INTO t1(tst,tst1) VALUES("MySQL","MySQL AB"), ("MSSQL","Microsoft"), ("ORACLE","ORACLE");
136

unknown's avatar
unknown committed
137
INSERT INTO t2(ParId) VALUES(1), (2), (3);
138

unknown's avatar
unknown committed
139
select * from t2;
140

unknown's avatar
unknown committed
141
UPDATE t2, t1 SET t2.tst = t1.tst, t2.tst1 = t1.tst1 WHERE t2.ParId = t1.Id;
142

unknown's avatar
unknown committed
143
select * from t2;
144
drop table t1, t2 ;
unknown's avatar
unknown committed
145

unknown's avatar
unknown committed
146 147 148 149 150 151
create table t1 (n numeric(10));
create table t2 (n numeric(10));
insert into t2 values (1),(2),(4),(8),(16),(32);
select * from t2 left outer join t1  using (n);
delete  t1,t2 from t2 left outer join t1  using (n);
select * from t2 left outer join t1  using (n);
unknown's avatar
unknown committed
152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176
drop table t1,t2 ;

#
# Test with locking
#

create table t1 (n int(10) not null primary key, d int(10));
create table t2 (n int(10) not null primary key, d int(10));
insert into t1 values(1,1);
insert into t2 values(1,10),(2,20);
LOCK TABLES t1 write, t2 read;
--error 1099
DELETE t1.*, t2.* FROM t1,t2 where t1.n=t2.n;
--error 1099
UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
unlock tables;
LOCK TABLES t1 write, t2 write;
UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
select * from t1;
DELETE t1.*, t2.* FROM t1,t2 where t1.n=t2.n;
select * from t1;
select * from t2;
unlock tables;
drop table t1,t2;
177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196

#
# Test safe updates and timestamps
#
set sql_safe_updates=1;
create table t1 (n int(10), d int(10));
create table t2 (n int(10), d int(10));
insert into t1 values(1,1);
insert into t2 values(1,10),(2,20);
--error 1175
UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
set sql_safe_updates=0;
drop table t1,t2;
set timestamp=1038401397;
create table t1 (n int(10) not null primary key, d int(10), t timestamp);
create table t2 (n int(10) not null primary key, d int(10), t timestamp);
insert into t1 values(1,1,NULL);
insert into t2 values(1,10,NULL),(2,20,NULL);
set timestamp=1038000000;
UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
unknown's avatar
unknown committed
197 198
select n,d,unix_timestamp(t) from t1;
select n,d,unix_timestamp(t) from t2;
199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226
--error 1064
UPDATE t1,t2 SET 1=2 WHERE t1.n=t2.n;
drop table t1,t2;
set timestamp=0;
set sql_safe_updates=0;
create table t1 (n int(10) not null primary key, d int(10));
create table t2 (n int(10) not null primary key, d int(10));
insert into t1 values(1,1), (3,3);
insert into t2 values(1,10),(2,20);
UPDATE t2 left outer join t1 on t1.n=t2.n  SET t1.d=t2.d;
select * from t1;
select * from t2;
drop table t1,t2;
create table t1 (n int(10), d int(10));
create table t2 (n int(10), d int(10));
insert into t1 values(1,1),(1,2);
insert into t2 values(1,10),(2,20);
UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
select * from t1;
select * from t2;
drop table t1,t2;
create table t1 (n int(10), d int(10));
create table t2 (n int(10), d int(10));
insert into t1 values(1,1),(3,2);
insert into t2 values(1,10),(1,20);
UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
select * from t1;
select * from t2;
227
UPDATE t1 a ,t2 b SET a.d=b.d,b.d=30 WHERE a.n=b.n;
unknown's avatar
unknown committed
228 229
select * from t1;
select * from t2;
230
DELETE a, b  FROM t1 a,t2 b where a.n=b.n;
unknown's avatar
unknown committed
231 232
select * from t1;
select * from t2;
233
drop table t1,t2;
234

unknown's avatar
unknown committed
235
CREATE TABLE t1 ( broj int(4) unsigned NOT NULL default '0',  naziv char(25) NOT NULL default 'NEPOZNAT',  PRIMARY KEY  (broj)) ENGINE=MyISAM;
236
INSERT INTO t1 VALUES (1,'jedan'),(2,'dva'),(3,'tri'),(4,'xxxxxxxxxx'),(5,'a'),(10,''),(11,''),(12,''),(13,'');
unknown's avatar
unknown committed
237
CREATE TABLE t2 ( broj int(4) unsigned NOT NULL default '0',  naziv char(25) NOT NULL default 'NEPOZNAT',  PRIMARY KEY  (broj)) ENGINE=MyISAM;
238
INSERT INTO t2 VALUES (1,'jedan'),(2,'dva'),(3,'tri'),(4,'xxxxxxxxxx'),(5,'a');
unknown's avatar
unknown committed
239
CREATE TABLE t3 ( broj int(4) unsigned NOT NULL default '0',  naziv char(25) NOT NULL default 'NEPOZNAT',  PRIMARY KEY  (broj)) ENGINE=MyISAM;
240 241 242
INSERT INTO t3 VALUES (1,'jedan'),(2,'dva');
update t1,t2 set t1.naziv="aaaa" where t1.broj=t2.broj;
update t1,t2,t3 set t1.naziv="bbbb", t2.naziv="aaaa" where t1.broj=t2.broj and t2.broj=t3.broj;
243
drop table t1,t2,t3;
244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266

#
# Test multi update with different join methods
#

CREATE TABLE t1 (a int not null primary key, b int not null, key (b));
CREATE TABLE t2 (a int not null primary key, b int not null, key (b));
INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);

# Full join, without key
update t1,t2 set t1.a=t1.a+100;
select * from t1;

# unique key
update t1,t2 set t1.a=t1.a+100 where t1.a=101;
select * from t1;

# ref key
update t1,t2 set t1.b=t1.b+10 where t1.b=2;
select * from t1;

# Range key (in t1)
unknown's avatar
unknown committed
267
update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t2.a=t1.a-100;
268 269 270
select * from t1;
select * from t2;

271 272 273 274
# test for non-updating table which is also used in sub-select

update t1,t2 set t1.b=t2.b, t1.a=t2.a where t1.a=t2.a and not exists (select * from t2 where t2.a > 10);

275
drop table t1,t2;
unknown's avatar
unknown committed
276
CREATE TABLE t3 (  KEY1 varchar(50) NOT NULL default '',  PARAM_CORR_DISTANCE_RUSH double default NULL,  PARAM_CORR_DISTANCE_GEM double default NULL,  PARAM_AVG_TARE double default NULL,  PARAM_AVG_NB_DAYS double default NULL,  PARAM_DEFAULT_PROP_GEM_SRVC varchar(50) default NULL,  PARAM_DEFAULT_PROP_GEM_NO_ETIK varchar(50) default NULL,  PARAM_SCENARIO_COSTS varchar(50) default NULL,  PARAM_DEFAULT_WAGON_COST double default NULL,  tmp int(11) default NULL,  PRIMARY KEY  (KEY1)) ENGINE=MyISAM;
277 278 279 280 281 282 283
INSERT INTO t3 VALUES ('A',1,1,22,3.2,'R','R','BASE2',0.24,NULL);
create table t1 (A varchar(1));
insert into t1 values  ("A") ,("B"),("C"),("D");
create table t2(Z varchar(15));
insert into t2(Z)  select concat(a.a,b.a,c.a,d.a) from t1 as a, t1 as b, t1 as c, t1 as d;
update t2,t3 set Z =param_scenario_costs;
drop table t1,t2,t3;
unknown's avatar
unknown committed
284 285 286 287 288 289 290
create table t1 (a int, b int);
create table t2 (a int, b int);
insert into t1 values (1,1),(2,1),(3,1);
insert into t2 values (1,1), (3,1);
update t1 left join t2  on t1.a=t2.a set t1.b=2, t2.b=2 where t1.b=1 and t2.b=1 or t2.a is NULL;
select t1.a, t1.b,t2.a, t2.b from t1 left join t2  on t1.a=t2.a where t1.b=1 and t2.b=1 or t2.a is NULL;
drop table t1,t2;
unknown's avatar
unknown committed
291

292 293 294 295 296 297 298 299 300
#
# Test reuse of same table
#

create table t1 (a int not null auto_increment primary key, b int not null);
insert into t1 (b) values (1),(2),(3),(4);
update t1, t1 as t2 set t1.b=t2.b+1 where t1.a=t2.a;
select * from t1;
drop table t1;
unknown's avatar
Merge  
unknown committed
301

unknown's avatar
unknown committed
302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325
# Test multi-update and multi-delete with impossible where

create table t1(id1 smallint(5), field char(5));
create table t2(id2 smallint(5), field char(5));

insert into t1 values (1, 'a'), (2, 'aa');
insert into t2 values (1, 'b'), (2, 'bb');

select * from t1;
select * from t2;

update t2 inner join t1 on t1.id1=t2.id2
  set t2.field=t1.field
  where 0=1;
update t2, t1 set t2.field=t1.field
  where t1.id1=t2.id2 and 0=1;

delete t1, t2 from t2 inner join t1 on t1.id1=t2.id2
  where 0=1;
delete t1, t2 from t2,t1 
  where t1.id1=t2.id2 and 0=1;

drop table t1,t2;

unknown's avatar
unknown committed
326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358
#
# Test for bug #1820.
#

create table t1 ( a int not null, b int not null) ;
--disable_query_log
insert into t1 values (1,1),(2,2),(3,3),(4,4);
let $1=19;
set @d=4;
while ($1)
{
  eval insert into t1 select a+@d,b+@d from t1;
  eval set @d=@d*2;
  dec $1;
}

--enable_query_log
alter table t1 add index i1(a);
delete from t1 where a > 2000000;
create table t2 like t1;
insert into t2 select * from t1;

select 't2 rows before small delete', count(*) from t1;
delete t1,t2 from t1,t2 where t1.b=t2.a and t1.a < 2;
select 't2 rows after small delete', count(*) from t2;
select 't1 rows after small delete', count(*) from t1;

## Try deleting many rows 

delete t1,t2 from t1,t2 where t1.b=t2.a and t1.a < 100*1000;
select 't2 rows after big delete', count(*) from t2;
select 't1 rows after big delete', count(*) from t1;

unknown's avatar
unknown committed
359 360
drop table t1,t2;

361 362 363 364 365 366 367 368 369
#
# Test alias (this is not correct in 4.0)
#

CREATE TABLE t1 ( a int );
CREATE TABLE t2 ( a int );
DELETE t1 FROM t1, t2 AS t3;
DELETE t4 FROM t1, t1 AS t4;
DELETE t3 FROM t1 AS t3, t1 AS t4;
370
--error 1109
371 372 373 374 375 376 377 378 379 380
DELETE t1 FROM t1 AS t3, t2 AS t4;
INSERT INTO t1 values (1),(2);
INSERT INTO t2 values (1),(2);
DELETE t1 FROM t1 AS t2, t2 AS t1 where t1.a=t2.a and t1.a=1;
SELECT * from t1;
SELECT * from t2;
DELETE t2 FROM t1 AS t2, t2 AS t1 where t1.a=t2.a and t1.a=2;
SELECT * from t1;
SELECT * from t2;
DROP TABLE t1,t2;
unknown's avatar
unknown committed
381 382 383 384

#
# Test update with const tables
#
unknown's avatar
unknown committed
385 386 387 388 389
create table `t1` (`p_id` int(10) unsigned NOT NULL auto_increment, `p_code` varchar(20) NOT NULL default '', `p_active` tinyint(1) unsigned NOT NULL default '1', PRIMARY KEY (`p_id`) );
create table `t2` (`c2_id` int(10) unsigned NULL auto_increment, `c2_p_id` int(10) unsigned NOT NULL default '0', `c2_note` text NOT NULL, `c2_active` tinyint(1) unsigned NOT NULL default '1', PRIMARY KEY (`c2_id`), KEY `c2_p_id` (`c2_p_id`) );
insert into t1 values (0,'A01-Comp',1);
insert into t1 values (0,'B01-Comp',1);
insert into t2 values (0,1,'A Note',1);
unknown's avatar
unknown committed
390 391 392 393
update t1 left join t2 on p_id = c2_p_id set c2_note = 'asdf-1' where p_id = 2; 
select * from t1;
select * from t2;
drop table t1, t2;
unknown's avatar
unknown committed
394

395
#
unknown's avatar
unknown committed
396
# privilege check for multiupdate with other tables
397 398
#

unknown's avatar
unknown committed
399
connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
400 401 402 403 404 405 406 407 408
connection root;
--disable_warnings
create database mysqltest;
--enable_warnings
create table mysqltest.t1 (a int, b int, primary key (a));
create table mysqltest.t2 (a int, b int, primary key (a));
create table mysqltest.t3 (a int, b int, primary key (a));
grant select on mysqltest.* to mysqltest_1@localhost;
grant update on mysqltest.t1 to mysqltest_1@localhost;
unknown's avatar
unknown committed
409
connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK);
410 411 412 413 414
connection user1;
update t1, t2 set t1.b=1 where t1.a=t2.a;
update t1, t2 set t1.b=(select t3.b from t3 where t1.a=t3.a) where t1.a=t2.a;
connection root;
revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;
415
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
416
delete from mysql.user where user=_binary'mysqltest_1';
417 418 419 420 421 422 423 424 425 426 427
drop database mysqltest;

#
# multi delete wrong table check
#
create table t1 (a int, primary key (a));
create table t2 (a int, primary key (a));
create table t3 (a int, primary key (a));
-- error 1109
delete t1,t3 from t1,t2 where t1.a=t2.a and t2.a=(select t3.a from t3 where t1.a=t3.a);
drop table t1, t2, t3;
unknown's avatar
unknown committed
428

429 430 431 432 433
#
# multi* unique updating table check
#
create table t1 (col1 int); 
create table t2 (col1 int);
unknown's avatar
unknown committed
434
-- error 1093
435 436 437 438
update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1;
-- error 1093
delete t1 from t1,t2 where t1.col1 < (select max(col1) from t1) and t1.col1 = t2.col1;
drop table t1,t2;
unknown's avatar
unknown committed
439

unknown's avatar
unknown committed
440
# Test for BUG#5837 - delete with outer join and const tables
unknown's avatar
unknown committed
441
--disable_warnings
unknown's avatar
unknown committed
442 443 444 445 446 447 448 449 450
create table t1 (
  aclid bigint not null primary key, 
  status tinyint(1) not null 
) engine = innodb;

create table t2 (
  refid bigint not null primary key, 
  aclid bigint, index idx_acl(aclid) 
) engine = innodb;
unknown's avatar
unknown committed
451
--enable_warnings
unknown's avatar
unknown committed
452
insert into t2 values(1,null);
unknown's avatar
unknown committed
453
delete t2, t1 from t2 left join t1 on (t2.aclid=t1.aclid) where t2.refid='1';
unknown's avatar
unknown committed
454 455
drop table t1, t2;

456 457 458 459 460 461 462 463
#
# Bug#19225: unchecked error leads to server crash
#
create table t1(a int);
create table t2(a int);
--error 1093
delete from t1,t2 using t1,t2 where t1.a=(select a from t1);
drop table t1, t2;
464
# End of 4.1 tests
unknown's avatar
unknown committed
465

unknown's avatar
unknown committed
466 467 468 469
#
# Test for bug #1980.
#
--disable_warnings
470
create table t1 ( c char(8) not null ) engine=innodb;
unknown's avatar
unknown committed
471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488
--enable_warnings

insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');

alter table t1 add b char(8) not null;
alter table t1 add a char(8) not null;
alter table t1 add primary key (a,b,c);
update t1 set a=c, b=c;

create table t2 like t1;
insert into t2 select * from t1;

delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;

drop table t1,t2;

--disable_warnings
489
create table t1 ( c char(8) not null ) engine=innodb;
unknown's avatar
unknown committed
490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505
--enable_warnings

insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');

alter table t1 add b char(8) not null;
alter table t1 add a char(8) not null;
alter table t1 add primary key (a,b,c);
update t1 set a=c, b=c;

create table t2 like t1;
insert into t2 select * from t1;

delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;

drop table t1,t2;
unknown's avatar
unknown committed
506

unknown's avatar
unknown committed
507 508 509 510 511
#
# Test alter table and a concurrent multi update
# (This will force update to reopen tables)
#

unknown's avatar
unknown committed
512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527
create table t1 (a int, b int);
insert into t1 values (1, 2), (2, 3), (3, 4);
create table t2 (a int);
insert into t2 values (10), (20), (30);
create view v1 as select a as b, a/10 as a from t2;

connect (locker,localhost,root,,test);
connection locker;
lock table t1 write;

connect (changer,localhost,root,,test);
connection changer;
send alter table t1 add column c int default 100 after a;

connect (updater,localhost,root,,test);
connection updater;
unknown's avatar
unknown committed
528
sleep 2;
unknown's avatar
unknown committed
529 530 531 532 533 534 535 536 537 538 539 540 541 542 543
send update t1, v1 set t1.b=t1.a+t1.b+v1.b where t1.a=v1.a;

connection locker;
sleep 2;
unlock tables;

connection changer;
reap;

connection updater;
reap;
select * from t1;
select * from t2;
drop view v1;
drop table t1, t2;
544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573

#
# Test multi updates and deletes using primary key and without.
#
create table t1 (i1 int, i2 int, i3 int);
create table t2 (id int, c1 varchar(20), c2 varchar(20));
insert into t1 values (1,5,10),(3,7,12),(4,5,2),(9,10,15),(2,2,2);
insert into t2 values (9,"abc","def"),(5,"opq","lmn"),(2,"test t","t test");
select * from t1 order by i1;
select * from t2;
update t1,t2 set t1.i2=15, t2.c2="ppc" where t1.i1=t2.id;
select * from t1 order by i1;
select * from t2 order by id;
delete t1.*,t2.* from t1,t2 where t1.i2=t2.id;
select * from t1 order by i1;
select * from t2 order by id;
drop table t1, t2;
create table t1 (i1 int auto_increment not null, i2 int, i3 int, primary key (i1));
create table t2 (id int auto_increment not null, c1 varchar(20), c2 varchar(20), primary key(id));
insert into t1 values (1,5,10),(3,7,12),(4,5,2),(9,10,15),(2,2,2);
insert into t2 values (9,"abc","def"),(5,"opq","lmn"),(2,"test t","t test");
select * from t1 order by i1;
select * from t2 order by id;
update t1,t2 set t1.i2=15, t2.c2="ppc" where t1.i1=t2.id;
select * from t1 order by i1;
select * from t2 order by id;
delete t1.*,t2.* from t1,t2 where t1.i2=t2.id;
select * from t1 order by i1;
select * from t2 order by id;
drop table t1, t2;
574

575 576 577 578 579 580 581 582 583 584 585 586 587 588 589
#
# Bug#27716  	multi-update did partially and has not binlogged
#
CREATE TABLE `t1` (
  `a` int(11) NOT NULL auto_increment,
  `b` int(11) default NULL,
  PRIMARY KEY  (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;

CREATE TABLE `t2` (
  `a` int(11) NOT NULL auto_increment,
  `b` int(11) default NULL,
  PRIMARY KEY  (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;

590
# as the test is about to see erroed queries in binlog
591
set @sav_binlog_format=  @@session.binlog_format;
592 593 594 595
set @@session.binlog_format= mixed;


# A. testing multi_update::send_error() effective update
596 597 598
insert into t1 values (1,1),(2,2);
insert into t2 values (1,1),(4,4);
reset master;
unknown's avatar
unknown committed
599
error ER_DUP_ENTRY;
600 601 602 603 604
UPDATE t2,t1 SET t2.a=t1.a+2;
# check
select * from t2 /* must be (3,1), (4,4) */;
show master status /* there must be the UPDATE query event */;

605 606
# B. testing multi_update::send_error() ineffective update 
# (as there is a policy described at mysql_update() still go to binlog) 
607 608 609 610 611
delete from t1;
delete from t2;
insert into t1 values (1,2),(3,4),(4,4);
insert into t2 values (1,2),(3,4),(4,4);
reset master;
unknown's avatar
unknown committed
612
error ER_DUP_ENTRY;
613 614 615 616 617
UPDATE t2,t1  SET t2.a=t2.b where t2.a=t1.a;
show master status /* there must be the UPDATE query event */;

# cleanup bug#27716
drop table t1, t2;
618
set @@session.binlog_format= @sav_binlog_format;
619

620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650
#
# Bug #29136  	erred multi-delete on trans table does not rollback 
#

# prepare
--disable_warnings
drop table if exists t1, t2, t3;
--enable_warnings
CREATE TABLE t1 (a int, PRIMARY KEY (a));
CREATE TABLE t2 (a int, PRIMARY KEY (a));
CREATE TABLE t3 (a int, PRIMARY KEY (a)) ENGINE=MyISAM;
create trigger trg_del_t3 before  delete on t3 for each row insert into t1 values (1);

insert into t2 values (1),(2);
insert into t3 values (1),(2);
reset master;

# exec cases B, A - see innodb.test

# B. send_eof() and send_error() afterward

--error ER_DUP_ENTRY
delete t3.* from t2,t3 where t2.a=t3.a;

# check
select count(*) from t1 /* must be 1 */;
select count(*) from t3 /* must be 1 */;

# cleanup bug#29136
drop table t1, t2, t3;

651 652 653 654
#
# Add further tests from here
#

655

656
--echo end of tests