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

5 6 7
# Requires grants, so won't work with embedded server test
-- source include/not_embedded.inc

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

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

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

47
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;
48 49 50 51 52 53
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;
54
delete t1.*, t2.*, t3.*  from t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3  and t1.id1 > 95;
55 56 57

check table t1, t2, t3;

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

62 63 64 65
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;
66

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

# 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
74

75 76
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;
77 78 79 80 81
disable_query_log;
let $1 = 1000;
while ($1)
 {
  let $2 = 5;
82
  eval insert into t1 values ($1,'aaaaaaaaaaaaaaaaaaaa'); 
83 84 85 86 87 88 89 90
  while ($2)
   {
     eval insert into t2(id2,t) values ($1,'bbbbbbbbbbbbbbbbb'); 
     dec $2; 
   }
  dec $1;
 }
enable_query_log;
91
delete t1  from t1,t2 where t1.id1 = t2.id2 and t1.id1 > 500;
92
drop table t1,t2;
unknown's avatar
unknown committed
93 94

CREATE TABLE t1 (
95 96 97
  id int(11) NOT NULL default '0',
  name varchar(10) default NULL,
  PRIMARY KEY  (id)
unknown's avatar
unknown committed
98
) ENGINE=MyISAM;
unknown's avatar
unknown committed
99 100
INSERT INTO t1 VALUES (1,'aaa'),(2,'aaa'),(3,'aaa');
CREATE TABLE t2 (
101 102 103
  id int(11) NOT NULL default '0',
  name varchar(10) default NULL,
  PRIMARY KEY  (id)
unknown's avatar
unknown committed
104
) ENGINE=MyISAM;
unknown's avatar
unknown committed
105 106
INSERT INTO t2 VALUES (2,'bbb'),(3,'bbb'),(4,'bbb');
CREATE TABLE t3 (
107 108 109
  id int(11) NOT NULL default '0',
  mydate datetime default NULL,
  PRIMARY KEY  (id)
unknown's avatar
unknown committed
110
) ENGINE=MyISAM;
unknown's avatar
unknown committed
111
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
112
00:00:00'),(7,'2002-07-22 00:00:00');
unknown's avatar
unknown committed
113 114
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;
115
DROP TABLE t1,t2,t3;
unknown's avatar
unknown committed
116 117

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

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

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

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

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

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

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

unknown's avatar
unknown committed
145 146 147 148 149 150
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
151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175
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;
176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195

#
# 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
196 197
select n,d,unix_timestamp(t) from t1;
select n,d,unix_timestamp(t) from t2;
198 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
--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;
226
UPDATE t1 a ,t2 b SET a.d=b.d,b.d=30 WHERE a.n=b.n;
unknown's avatar
unknown committed
227 228
select * from t1;
select * from t2;
229
DELETE a, b  FROM t1 a,t2 b where a.n=b.n;
unknown's avatar
unknown committed
230 231
select * from t1;
select * from t2;
232
drop table t1,t2;
233

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

#
# 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
266
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;
267 268 269
select * from t1;
select * from t2;

270 271 272 273
# 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);

274
drop table t1,t2;
unknown's avatar
unknown committed
275
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;
276 277 278 279 280 281 282
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
283 284 285 286 287 288 289
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
290

291 292 293 294 295 296 297 298 299
#
# 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
300

unknown's avatar
unknown committed
301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324
# 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
325 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
#
# 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
358 359
drop table t1,t2;

360 361 362 363 364 365 366 367 368
#
# 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;
369
--error 1109
370 371 372 373 374 375 376 377 378 379
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
380 381 382 383

#
# Test update with const tables
#
unknown's avatar
unknown committed
384 385 386 387 388
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
389 390 391 392
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
393

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

unknown's avatar
unknown committed
398
connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
399 400 401 402 403 404 405 406 407
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
408
connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK);
409 410 411 412 413
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;
414
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
415
delete from mysql.user where user=_binary'mysqltest_1';
416 417 418 419 420 421 422 423 424 425 426
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
427

428 429 430 431 432
#
# multi* unique updating table check
#
create table t1 (col1 int); 
create table t2 (col1 int);
unknown's avatar
unknown committed
433
-- error 1093
434 435 436 437
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
438

unknown's avatar
unknown committed
439
# Test for BUG#5837 - delete with outer join and const tables
unknown's avatar
unknown committed
440
--disable_warnings
unknown's avatar
unknown committed
441 442 443 444 445 446 447 448 449
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
450
--enable_warnings
unknown's avatar
unknown committed
451
insert into t2 values(1,null);
unknown's avatar
unknown committed
452
delete t2, t1 from t2 left join t1 on (t2.aclid=t1.aclid) where t2.refid='1';
unknown's avatar
unknown committed
453 454
drop table t1, t2;

455 456 457 458 459 460 461 462
#
# 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;
463
# End of 4.1 tests
unknown's avatar
unknown committed
464

unknown's avatar
unknown committed
465 466 467 468
#
# Test for bug #1980.
#
--disable_warnings
469
create table t1 ( c char(8) not null ) engine=innodb;
unknown's avatar
unknown committed
470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487
--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
488
create table t1 ( c char(8) not null ) engine=innodb;
unknown's avatar
unknown committed
489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504
--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
505

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

unknown's avatar
unknown committed
511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526
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
527
sleep 2;
unknown's avatar
unknown committed
528 529 530 531 532 533 534 535 536 537 538 539 540 541 542
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;
543 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

#
# 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;
573

574 575 576 577 578 579 580 581 582 583 584 585 586 587 588
#
# 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 ;

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


# A. testing multi_update::send_error() effective update
594 595 596
insert into t1 values (1,1),(2,2);
insert into t2 values (1,1),(4,4);
reset master;
597
--error ER_DUP_ENTRY_WITH_KEY_NAME
598 599 600 601 602
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 */;

603 604
# B. testing multi_update::send_error() ineffective update 
# (as there is a policy described at mysql_update() still go to binlog) 
605 606 607 608 609
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;
610
--error ER_DUP_ENTRY_WITH_KEY_NAME
611 612 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;

--echo end of tests