percona-server-test-5.6 5.6.22-rel71.0-0ubuntu4 / usr / lib / mysql-testsuite / t / multi_update.test

#
# Test of update statement that uses many tables.
#

# Requires grants, so won't work with embedded server test
source include/not_embedded.inc;
source include/have_log_bin.inc;

CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.");
call mtr.add_suppression("Did not write failed .* into binary log while "
                         "granting/revoking privileges in databases.");
call mtr.add_suppression("Did not write failed .* into binary log while "
                         "storing table level and column level grants "
                         "in the privilege tables.");

--disable_warnings
drop table if exists t1,t2,t3;
drop database if exists mysqltest;
drop view if exists v1;
--error 0,ER_NONEXISTING_GRANT,ER_NONEXISTING_TABLE_GRANT
revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;
--error 0,ER_NONEXISTING_GRANT,ER_NONEXISTING_TABLE_GRANT
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
delete from mysql.user where user=_binary'mysqltest_1';
--enable_warnings

create table t1(id1 int not null auto_increment primary key, t char(12));
create table t2(id2 int not null, t char(12));
create table t3(id3 int not null, t char(12), index(id3));
disable_query_log;
let $1 = 100;
while ($1)
 {
  let $2 = 5;
  eval insert into t1(t) values ('$1');
  while ($2)
   {
     eval insert into t2(id2,t) values ($1,'$2');
     let $3 = 10;
     while ($3)
     {
       eval insert into t3(id3,t) values ($1,'$2');
       dec $3;
     }
     dec $2;
   }
  dec $1;
 }
enable_query_log;

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

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;
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;
delete t1.*, t2.*, t3.*  from t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3  and t1.id1 > 95;

check table t1, t2, t3;

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

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;

delete from t1, t2, t3  using t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3  and t1.id1 > 0;

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

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;
disable_query_log;
let $1 = 1000;
while ($1)
 {
  let $2 = 5;
  eval insert into t1 values ($1,'aaaaaaaaaaaaaaaaaaaa');
  while ($2)
   {
     eval insert into t2(id2,t) values ($1,'bbbbbbbbbbbbbbbbb');
     dec $2;
   }
  dec $1;
 }
enable_query_log;
delete t1  from t1,t2 where t1.id1 = t2.id2 and t1.id1 > 500;
drop table t1,t2;

CREATE TABLE t1 (
  id int(11) NOT NULL default '0',
  name varchar(10) default NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1,'aaa'),(2,'aaa'),(3,'aaa');
CREATE TABLE t2 (
  id int(11) NOT NULL default '0',
  name varchar(10) default NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM;
INSERT INTO t2 VALUES (2,'bbb'),(3,'bbb'),(4,'bbb');
CREATE TABLE t3 (
  id int(11) NOT NULL default '0',
  mydate datetime default NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM;
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
00:00:00'),(7,'2002-07-22 00:00:00');
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;
DROP TABLE t1,t2,t3;

CREATE TABLE IF NOT EXISTS `t1` (
  `id` int(11) NOT NULL auto_increment,
  `tst` text,
  `tst1` text,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

CREATE TABLE IF NOT EXISTS `t2` (
  `ID` int(11) NOT NULL auto_increment,
  `ParId` int(11) default NULL,
  `tst` text,
  `tst1` text,
  PRIMARY KEY  (`ID`),
  KEY `IX_ParId_t2` (`ParId`),
  FOREIGN KEY (`ParId`) REFERENCES `t1` (`id`)
) ENGINE=MyISAM;

INSERT INTO t1(tst,tst1) VALUES("MySQL","MySQL AB"), ("MSSQL","Microsoft"), ("ORACLE","ORACLE");

INSERT INTO t2(ParId) VALUES(1), (2), (3);

select * from t2;

--disable_warnings ONCE
UPDATE t2, t1 SET t2.tst = t1.tst, t2.tst1 = t1.tst1 WHERE t2.ParId = t1.Id;

select * from t2;
drop table t1, t2 ;

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);
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 ER_TABLE_NOT_LOCKED_FOR_WRITE
DELETE t1.*, t2.* FROM t1,t2 where t1.n=t2.n;
--error ER_TABLE_NOT_LOCKED_FOR_WRITE
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;

#
# 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 ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
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 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
create table t2 (n int(10) not null primary key, d int(10), t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_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;
select n,d,unix_timestamp(t) from t1;
select n,d,unix_timestamp(t) from t2;
--error ER_PARSE_ERROR
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;
UPDATE t1 a ,t2 b SET a.d=b.d,b.d=30 WHERE a.n=b.n;
select * from t1;
select * from t2;
DELETE a, b  FROM t1 a,t2 b where a.n=b.n;
select * from t1;
select * from t2;
drop table t1,t2;

CREATE TABLE t1 ( broj int(4) unsigned NOT NULL default '0',  naziv char(25) NOT NULL default 'NEPOZNAT',  PRIMARY KEY  (broj)) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1,'jedan'),(2,'dva'),(3,'tri'),(4,'xxxxxxxxxx'),(5,'a'),(10,''),(11,''),(12,''),(13,'');
CREATE TABLE t2 ( broj int(4) unsigned NOT NULL default '0',  naziv char(25) NOT NULL default 'NEPOZNAT',  PRIMARY KEY  (broj)) ENGINE=MyISAM;
INSERT INTO t2 VALUES (1,'jedan'),(2,'dva'),(3,'tri'),(4,'xxxxxxxxxx'),(5,'a');
CREATE TABLE t3 ( broj int(4) unsigned NOT NULL default '0',  naziv char(25) NOT NULL default 'NEPOZNAT',  PRIMARY KEY  (broj)) ENGINE=MyISAM;
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;
drop table t1,t2,t3;

#
# 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)
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;
select * from t1;
select * from t2;

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

drop table t1,t2;
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;
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;
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;

#
# 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);
--disable_warnings ONCE
update t1, t1 as t2 set t1.b=t2.b+1 where t1.a=t2.a;
select * from t1;
drop table t1;

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

#
# 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;
--error ER_UNKNOWN_TABLE
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;

#
# Test update with const tables
#
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);
--disable_warnings ONCE
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;

#
# privilege check for multiupdate with other tables
#

connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
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;
connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK);
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;
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
delete from mysql.user where user=_binary'mysqltest_1';
drop database mysqltest;
connection default;
disconnect user1;
disconnect root;

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

#
# multi* unique updating table check
#
create table t1 (col1 int);
create table t2 (col1 int);
-- error ER_UPDATE_TABLE_USED
update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1;
-- error ER_UPDATE_TABLE_USED
delete t1 from t1,t2 where t1.col1 < (select max(col1) from t1) and t1.col1 = t2.col1;
drop table t1,t2;

# Test for Bug#5837 delete with outer join and const tables
--disable_warnings
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;
--enable_warnings
insert into t2 values(1,null);
delete t2, t1 from t2 left join t1 on (t2.aclid=t1.aclid) where t2.refid='1';
drop table t1, t2;

#
# Bug#19225 unchecked error leads to server crash
#
create table t1(a int);
create table t2(a int);
--error ER_UPDATE_TABLE_USED
delete from t1,t2 using t1,t2 where t1.a=(select a from t1);
drop table t1, t2;
# End of 4.1 tests

#
# Test for Bug#1980.
#
--disable_warnings
create table t1 ( c char(8) not null ) engine=innodb;
--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
create table t1 ( c char(8) not null ) engine=innodb;
--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;

#
# Test alter table and a concurrent multi update
# (Before we have introduced data-lock-aware metadata locks
#  this test case forced update to reopen tables).
#

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;
# Wait till "alter table t1 ..." of session changer is in work.
# = There is one session waiting.
let $wait_condition= select count(*)= 1 from information_schema.processlist
                     where state= 'Waiting for table metadata lock';
--source include/wait_condition.inc
send update t1, v1 set t1.b=t1.a+t1.b+v1.b where t1.a=v1.a;

connection locker;
# Wait till
# - "alter table t1 ..." of session changer and
# - "update t1, v1 ..." of session updater
# are in work.
# = There are two session waiting.
let $wait_condition= select count(*)= 2 from information_schema.processlist
                     where state= 'Waiting for table metadata lock';
--source include/wait_condition.inc
unlock tables;

connection changer;
reap;

connection updater;
reap;
select * from t1;
select * from t2;
drop view v1;
drop table t1, t2;

connection default;
disconnect locker;
disconnect changer;
disconnect updater;

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

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

# as the test is about to see erroed queries in binlog
set @sav_binlog_format=  @@session.binlog_format;
set @@session.binlog_format= mixed;


# A. testing multi_update::send_error() effective update
insert into t1 values (1,1),(2,2);
insert into t2 values (1,1),(4,4);
reset master;
--error ER_DUP_ENTRY
UPDATE t2,t1 SET t2.a=t1.a+2;
# check
select * from t2 /* must be (3,1), (4,4) */;
source include/show_binlog_events.inc;

# B. testing multi_update::send_error() ineffective update
# (as there is a policy described at mysql_update() still go to binlog)
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;
--error ER_DUP_ENTRY
UPDATE t2,t1  SET t2.a=t2.b where t2.a=t1.a;
source include/show_binlog_events.inc;

# cleanup
drop table t1, t2;
set @@session.binlog_format= @sav_binlog_format;

#
# 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
drop table t1, t2, t3;

#
# Add further tests from here
#

--echo #
--echo # Bug#49534: multitable IGNORE update with sql_safe_updates error
--echo # causes debug assertion
--echo #
CREATE TABLE t1( a INT, KEY( a ) );
INSERT INTO t1 VALUES (1), (2), (3);
SET SESSION sql_safe_updates = 1;
--echo # Must not cause failed assertion
--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
UPDATE IGNORE t1, t1 t1a SET t1.a = 1 WHERE t1a.a = 1;
DROP TABLE t1;

--echo #
--echo # Bug#54543: update ignore with incorrect subquery leads to assertion
--echo # failure: inited==INDEX
--echo #
SET SESSION sql_safe_updates = 0;
CREATE TABLE t1 ( a INT );
INSERT INTO t1 VALUES (1), (2);

CREATE TABLE t2 ( a INT );
INSERT INTO t2 VALUES (1), (2);

CREATE TABLE t3 ( a INT );
INSERT INTO t3 VALUES (1), (2);

--echo # Should not crash
UPDATE IGNORE
  ( SELECT ( SELECT COUNT(*) FROM t1 GROUP BY a, @v ) a FROM t2 ) x, t3
SET t3.a = 0;

DROP TABLE t1, t2, t3;
SET SESSION sql_safe_updates = DEFAULT;

--echo #
--echo # Bug#52157 various crashes and assertions with multi-table update, stored function
--echo #

CREATE FUNCTION f1 () RETURNS BLOB RETURN 1;
CREATE TABLE t1 (f1 DATE);
INSERT INTO t1 VALUES('2001-01-01');
UPDATE (SELECT 1 FROM t1 WHERE f1 = (SELECT f1() FROM t1)) x, t1 SET f1 = 1;
DROP FUNCTION f1;
DROP TABLE t1;

--echo #
--echo # BUG#57373: Multi update+InnoDB reports ER_KEY_NOT_FOUND if a
--echo #            table is updated twice
--echo #

# Results differ between storage engines.
# See multi_update_innodb.test for the InnoDB variant of this test
CREATE TABLE t1(
  pk INT,
  a INT,
  PRIMARY KEY (pk)
) ENGINE=MyISAM;

INSERT INTO t1 VALUES (0,0);
UPDATE t1 AS A, t1 AS B SET A.pk = 1, B.a = 2;
--echo
--echo # Should be (1,2)
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # BUG#11882110: UPDATE REPORTS ER_KEY_NOT_FOUND IF TABLE IS 
--echo #               UPDATED TWICE
--echo #

# Results differ between storage engines. This test is to verify that
# the bugfix did NOT change behavior for MyISAM.
# See multi_update_innodb.test for the InnoDB variant of this test
CREATE TABLE t1 ( 
  col_int_key int, 
  pk int, 
  col_int int, 
  key(col_int_key), 
  primary key (pk)
) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1,2,3);

--echo
CREATE TABLE t2 ( 
  col_int_key int, 
  pk_1 int, 
  pk_2 int, 
  col_int int, 
  key(col_int_key), 
  primary key (pk_1,pk_2)
) ENGINE=MyISAM;
INSERT INTO t2 VALUES (1,2,3,4);

--echo
UPDATE t1 AS A NATURAL JOIN t1 B SET A.pk=5,B.pk=7;

--echo
SELECT * FROM t1;

--echo
UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_1=5,B.pk_1=7;
--echo
UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_2=10,B.pk_2=11;

--echo
SELECT * FROM t2;

DROP TABLE t1,t2;

--echo # Bug#13256831 - ERROR 1032 (HY000): CAN'T FIND RECORD

CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT) ENGINE=InnoDB;
CREATE TABLE t2 (f1 INT PRIMARY KEY, f2 INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (5, 7);
INSERT INTO t2 VALUES (6, 97);

CREATE ALGORITHM = MERGE VIEW v1 AS 
SELECT a2.f1 AS f1, a2.f2 AS f2
FROM t1 AS a1 JOIN t2 AS a2 ON a1.f2 > a2.f1 
WITH LOCAL CHECK OPTION; 

SELECT * FROM v1;
UPDATE v1 SET f1 = 1;
SELECT * FROM v1;

DROP TABLE t1, t2;
DROP VIEW v1;

--echo #
--echo # BUG #11766576 - 59715: UPDATE IGNORE, 1 ROW AFFECTED
--echo #

--disable_warnings
DROP TABLE IF EXISTS t1, t2;
--enable_warnings

CREATE TABLE t1 (id INT PRIMARY KEY);
INSERT INTO t1 VALUES (1), (2);

--enable_info
UPDATE IGNORE t1, (SELECT 1 AS duplicate_id) AS t2 SET t1.id=t2.duplicate_id;
--disable_info

--echo # Check that no rows changed.
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo #Bug 11757486 - 49539: NON-DESCRIPTIVE ERR (ERROR 0 FROM STORAGE ENGINE)
--echo #                      WITH MULTI-TABLE UPDATE
--echo #

CREATE TABLE table_11757486 (field1 tinyint) ENGINE=MYISAM;
INSERT INTO table_11757486 VALUES (0),(0);
SET SESSION SQL_MODE='STRICT_ALL_TABLES';
UPDATE IGNORE (SELECT 128 as col1) x, table_11757486 SET field1=x.col1;

--error ER_WARN_DATA_OUT_OF_RANGE
UPDATE (SELECT 128 as col1) x, table_11757486 SET field1=x.col1;

SET SESSION SQL_MODE='';
UPDATE IGNORE (SELECT 128 as col1) x, table_11757486 SET field1=x.col1;
DROP TABLE table_11757486;