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)); select count(*) from t1 where id1 > 95; count(*) 5 select count(*) from t2 where id2 > 95; count(*) 25 select count(*) from t3 where id3 > 95; count(*) 250 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"; count(*) 10 select count(*) from t1 where id1 > 90; count(*) 10 select count(*) from t2 where t = "bbb"; count(*) 50 select count(*) from t2 where id2 > 90; count(*) 50 select count(*) from t3 where t = "cc"; count(*) 500 select count(*) from t3 where id3 > 90; count(*) 500 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; Table Op Msg_type Msg_text test.t1 check status OK test.t2 check status OK test.t3 check status OK select count(*) from t1 where id1 > 95; count(*) 0 select count(*) from t2 where id2 > 95; count(*) 0 select count(*) from t3 where id3 > 95; count(*) 0 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; count(*) 0 select count(*) from t2 where id2 > 5; count(*) 0 select count(*) from t3 where id3 > 5; count(*) 0 delete from t1, t2, t3 using t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 0; select count(*) from t1 where id1; count(*) 0 select count(*) from t2 where id2; count(*) 0 select count(*) from t3 where id3; count(*) 0 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; 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; id mydate 1 2002-02-04 00:00:00 5 2002-05-12 00:00:00 6 2002-06-22 00:00:00 7 2002-07-22 00:00:00 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; ID ParId tst tst1 1 1 NULL NULL 2 2 NULL NULL 3 3 NULL NULL UPDATE t2, t1 SET t2.tst = t1.tst, t2.tst1 = t1.tst1 WHERE t2.ParId = t1.Id; select * from t2; ID ParId tst tst1 1 1 MySQL MySQL AB 2 2 MSSQL Microsoft 3 3 ORACLE ORACLE 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); n 1 2 4 8 16 32 delete t1,t2 from t2 left outer join t1 using (n); select * from t2 left outer join t1 using (n); n drop table t1,t2 ; 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; DELETE t1.*, t2.* FROM t1,t2 where t1.n=t2.n; ERROR HY000: Table 't2' was locked with a READ lock and can't be updated UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n; ERROR HY000: Table 't2' was locked with a READ lock and can't be updated 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; n d 1 10 DELETE t1.*, t2.* FROM t1,t2 where t1.n=t2.n; select * from t1; n d select * from t2; n d 2 20 unlock tables; drop table t1,t2; 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); UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n; ERROR HY000: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column 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; n d unix_timestamp(t) 1 10 1038000000 select n,d,unix_timestamp(t) from t2; n d unix_timestamp(t) 1 10 1038401397 2 20 1038401397 UPDATE t1,t2 SET 1=2 WHERE t1.n=t2.n; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1=2 WHERE t1.n=t2.n' at line 1 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; n d 1 10 3 3 select * from t2; n d 1 10 2 20 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; n d 1 10 1 10 select * from t2; n d 1 30 2 20 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; n d 1 10 3 2 select * from t2; n d 1 30 1 30 UPDATE t1 a ,t2 b SET a.d=b.d,b.d=30 WHERE a.n=b.n; select * from t1; n d 1 30 3 2 select * from t2; n d 1 30 1 30 DELETE a, b FROM t1 a,t2 b where a.n=b.n; select * from t1; n d 3 2 select * from t2; n d 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; 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); update t1,t2 set t1.a=t1.a+100; select * from t1; a b 101 1 102 2 103 3 104 4 105 5 106 6 107 7 108 8 109 9 update t1,t2 set t1.a=t1.a+100 where t1.a=101; select * from t1; a b 201 1 102 2 103 3 104 4 105 5 106 6 107 7 108 8 109 9 update t1,t2 set t1.b=t1.b+10 where t1.b=2; select * from t1; a b 201 1 102 12 103 3 104 4 105 5 106 6 107 7 108 8 109 9 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; a b 201 1 102 12 103 5 104 6 105 7 106 6 107 7 108 8 109 9 select * from t2; a b 1 1 2 2 3 13 4 14 5 15 6 6 7 7 8 8 9 9 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; a b a b 2 2 NULL NULL drop table t1,t2; 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; a b 1 2 2 3 3 4 4 5 drop table t1; 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; id1 field 1 a 2 aa select * from t2; id2 field 1 b 2 bb 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; 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; DELETE t1 FROM t1 AS t3, t2 AS t4; ERROR 42S02: Unknown table 't1' in MULTI DELETE 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; a 1 2 SELECT * from t2; a 2 DELETE t2 FROM t1 AS t2, t2 AS t1 where t1.a=t2.a and t1.a=2; SELECT * from t1; a 1 SELECT * from t2; a 2 DROP TABLE t1,t2; 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); update t1 left join t2 on p_id = c2_p_id set c2_note = 'asdf-1' where p_id = 2; select * from t1; p_id p_code p_active 1 A01-Comp 1 2 B01-Comp 1 select * from t2; c2_id c2_p_id c2_note c2_active 1 1 A Note 1 drop table t1, t2; connect root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK; create database mysqltest; 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)); create user mysqltest_1@localhost; 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; 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'; flush privileges; drop database mysqltest; connection default; disconnect user1; disconnect root; create table t1 (a int, primary key (a)); create table t2 (a int, primary key (a)); create table t3 (a int, primary key (a)); delete t1,t3 from t1,t2 where t1.a=t2.a and t2.a=(select t3.a from t3 where t1.a=t3.a); ERROR 42S02: Unknown table 't3' in MULTI DELETE drop table t1, t2, t3; create table t1 (col1 int); create table t2 (col1 int); update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1; delete t1 from t1,t2 where t1.col1 < (select max(col1) from t1) and t1.col1 = t2.col1; ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data drop table t1,t2; create table t1(a int); create table t2(a int); delete from t1,t2 using t1,t2 where t1.a=(select a from t1); ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data drop table t1, t2; 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; lock table t1 write; connect changer,localhost,root,,test; alter table t1 add column c int default 100 after a; connect updater,localhost,root,,test; update t1, v1 set t1.b=t1.a+t1.b+v1.b where t1.a=v1.a; connection locker; unlock tables; connection changer; connection updater; select * from t1; a c b 1 100 13 2 100 25 3 100 37 select * from t2; a 10 20 30 drop view v1; drop table t1, t2; connection default; disconnect locker; disconnect changer; disconnect updater; 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; i1 i2 i3 1 5 10 2 2 2 3 7 12 4 5 2 9 10 15 select * from t2; id c1 c2 9 abc def 5 opq lmn 2 test t t test update t1,t2 set t1.i2=15, t2.c2="ppc" where t1.i1=t2.id; select * from t1 order by i1; i1 i2 i3 1 5 10 2 15 2 3 7 12 4 5 2 9 15 15 select * from t2 order by id; id c1 c2 2 test t ppc 5 opq lmn 9 abc ppc delete t1.*,t2.* from t1,t2 where t1.i2=t2.id; select * from t1 order by i1; i1 i2 i3 2 15 2 3 7 12 9 15 15 select * from t2 order by id; id c1 c2 2 test t ppc 9 abc ppc 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; i1 i2 i3 1 5 10 2 2 2 3 7 12 4 5 2 9 10 15 select * from t2 order by id; id c1 c2 2 test t t test 5 opq lmn 9 abc def update t1,t2 set t1.i2=15, t2.c2="ppc" where t1.i1=t2.id; select * from t1 order by i1; i1 i2 i3 1 5 10 2 15 2 3 7 12 4 5 2 9 15 15 select * from t2 order by id; id c1 c2 2 test t ppc 5 opq lmn 9 abc ppc delete t1.*,t2.* from t1,t2 where t1.i2=t2.id; select * from t1 order by i1; i1 i2 i3 2 15 2 3 7 12 9 15 15 select * from t2 order by id; id c1 c2 2 test t ppc 9 abc ppc drop table t1, t2; # # Bug#49534: multitable IGNORE update with sql_safe_updates error # causes debug assertion # CREATE TABLE t1( a INT, KEY( a ) ); INSERT INTO t1 VALUES (1), (2), (3); SET SESSION sql_safe_updates = 1; # Must not cause failed assertion UPDATE IGNORE t1, t1 t1a SET t1.a = 1 WHERE t1a.a = 1; ERROR HY000: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column DROP TABLE t1; # # Bug#54543: update ignore with incorrect subquery leads to assertion # failure: inited==INDEX # 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); # Should not crash UPDATE IGNORE ( SELECT ( SELECT COUNT(*) FROM t1 GROUP BY a, @v ) a FROM t2 ) x, t3 SET t3.a = 0; Warnings: Warning 1242 Subquery returns more than 1 row Warning 1242 Subquery returns more than 1 row DROP TABLE t1, t2, t3; SET SESSION sql_safe_updates = DEFAULT; # # Bug#52157 various crashes and assertions with multi-table update, stored function # CREATE FUNCTION f1 () RETURNS BLOB RETURN 1; CREATE TABLE t1 (f1 DATE); INSERT INTO t1 VALUES('2001-01-01'); UPDATE IGNORE (SELECT 1 FROM t1 WHERE f1 = (SELECT f1() FROM t1)) x, t1 SET f1 = 1; Warnings: Warning 1292 Truncated incorrect datetime value: '1' CREATE view v1 as SELECT f1() FROM t1; UPDATE IGNORE (SELECT 1 FROM t1 WHERE f1 = (select * from v1)) x, t1 SET f1 = 1; Warnings: Warning 1292 Truncated incorrect datetime value: '1' DROP VIEW v1; DROP FUNCTION f1; DROP TABLE t1; # # MDEV-4123: Incorrect results after multi-table update or # assertion `!table || (!table->read_set || # bitmap_is_set(table->read_set, field_index))' failure # DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 'test.t1' CREATE TABLE t1 ( id int(10) unsigned NOT NULL, level tinyint(3) unsigned NOT NULL, PRIMARY KEY (id) ); INSERT INTO t1 VALUES (2519583,1); DROP TABLE IF EXISTS t2; Warnings: Note 1051 Unknown table 'test.t2' CREATE TABLE t2 ( club_id int(11) NOT NULL DEFAULT '0', profile_id int(11) NOT NULL DEFAULT '0', member_level_id int(11) NOT NULL DEFAULT '0', PRIMARY KEY (profile_id,club_id) ); INSERT INTO t2 VALUES (2,2519583,12); DROP TABLE IF EXISTS t3; Warnings: Note 1051 Unknown table 'test.t3' CREATE TABLE t3 ( member_level_id int(11) unsigned NOT NULL DEFAULT '0', map_level int(11) unsigned NOT NULL DEFAULT '0', map_status int(11) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (member_level_id) ); INSERT INTO t3 VALUES (12,12,1); CREATE VIEW v1 AS select club_id,profile_id, map_level AS member_level_id,map_status AS member_status from (t2 tc join t3 map on(((tc.member_level_id = map.member_level_id) and (club_id = 2)))); select level, count(*) as cnt from t1 group by level; level cnt 1 1 UPDATE t1 c LEFT JOIN v1 t ON (c.id = t.profile_id AND t.club_id = 2) SET c.level = IF (t.member_status IS NULL, 1, IF (t.member_status = 1, 2,3)); select level, count(*) as cnt from t1 group by level; level cnt 2 1 drop view v1; drop table t1,t2,t3; end of tests # # BUG#57373: Multi update+InnoDB reports ER_KEY_NOT_FOUND if a # table is updated twice # 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; # Should be (1,2) SELECT * FROM t1; pk a 1 2 DROP TABLE t1; # # BUG#11882110: UPDATE REPORTS ER_KEY_NOT_FOUND IF TABLE IS # UPDATED TWICE # 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); 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); UPDATE t1 AS A NATURAL JOIN t1 B SET A.pk=5,B.pk=7; SELECT * FROM t1; col_int_key pk col_int 1 7 3 UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_1=5,B.pk_1=7; UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_2=10,B.pk_2=11; SELECT * FROM t2; col_int_key pk_1 pk_2 col_int 1 7 11 4 DROP TABLE t1,t2; # # MDEV-6139: UPDATE w/ join against MRG_MyISAM table with read-only # sub-table fails # MDEV-6193: Problems with multi-table updates that JOIN against # read-only table # CREATE TABLE t1 ( id int(10) unsigned, a int(11) ) ENGINE=MyISAM; CREATE TABLE t3 ( id int(10) unsigned, b int(11) ) ENGINE=MyISAM; CREATE TABLE t2 ( id int(10) unsigned, b int(11) ) ENGINE=MRG_MyISAM UNION=(t3); FLUSH TABLES; update t1 join t2 using (id) set t1.a=t2.b; create view v2 as select * from t2; update t1 join v2 using (id) set t1.a=0; create view v1 as select * from t3; update t1 join v1 using (id) set t1.a=0; update t1 join INFORMATION_SCHEMA.CHARACTER_SETS on (id=MAXLEN) set t1.a=0; create view v3 as select t2.id, t3.b from t2 join t3 using(id); update t1 join v3 using (id) set t1.a=0; drop view v1, v2, v3; drop table t2, t3, t1; # # MDEV-7613: MariaDB 5.5.40 server crash on update table left join # with a view # CREATE TABLE `t1` ( `f1` varchar(6) COLLATE latin1_general_ci DEFAULT NULL, `f2` varchar(6) COLLATE latin1_general_ci DEFAULT NULL, `f3` varchar(7) COLLATE latin1_general_ci DEFAULT NULL, `f4` varchar(15) COLLATE latin1_general_ci DEFAULT NULL, `f5` datetime DEFAULT NULL, `f6` varchar(2) COLLATE latin1_general_ci DEFAULT NULL, `f7` varchar(2) COLLATE latin1_general_ci DEFAULT NULL, `ff1` int(1) DEFAULT NULL, `ff2` int(1) DEFAULT NULL, `ff3` int(1) DEFAULT NULL, `ff4` int(1) DEFAULT NULL, `ff5` int(1) DEFAULT NULL, `ff6` int(1) DEFAULT NULL, `ff7` int(1) DEFAULT NULL, `ff8` int(2) DEFAULT NULL, `ff9` int(1) DEFAULT NULL, `ff10` int(1) DEFAULT NULL, `ff11` int(1) DEFAULT NULL, `ff12` int(1) DEFAULT NULL, `ff13` int(1) DEFAULT NULL, `ff14` int(1) DEFAULT NULL, `ff15` int(1) DEFAULT NULL, `f8` varchar(70) COLLATE latin1_general_ci DEFAULT NULL, `f9` varchar(20) COLLATE latin1_general_ci DEFAULT NULL, `f10` varchar(50) COLLATE latin1_general_ci NOT NULL, `f11` varchar(50) COLLATE latin1_general_ci DEFAULT NULL, `f12` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, `f13` text COLLATE latin1_general_ci, `f14` time DEFAULT NULL, `f15` varchar(30) COLLATE latin1_general_ci DEFAULT NULL, `fg1` int(11) DEFAULT NULL, `fg2` int(11) DEFAULT NULL, `fg3` int(11) DEFAULT NULL, `fg4` int(11) DEFAULT NULL, `fg5` int(11) DEFAULT NULL, `fg6` int(11) DEFAULT NULL, `fg7` int(11) DEFAULT NULL, `fg9` int(11) DEFAULT NULL, `fg10` int(11) DEFAULT NULL, `fg11` int(11) DEFAULT NULL, `fg12` int(11) DEFAULT NULL, `fg13` int(11) DEFAULT NULL, `fg14` int(11) DEFAULT NULL, `fg15` int(11) DEFAULT NULL, `f16` double DEFAULT NULL, `f17` double DEFAULT NULL, `f18` int(11) DEFAULT NULL, `f19` int(11) DEFAULT NULL, `f20` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, `f21` int(11) DEFAULT NULL, `f22` int(11) DEFAULT NULL, `f23` int(11) DEFAULT NULL, `f24` double DEFAULT NULL, `f25` int(11) DEFAULT NULL, `f26` double DEFAULT NULL, `f27` int(11) DEFAULT NULL, `f28` int(11) DEFAULT NULL, `f29` double DEFAULT NULL, `f30` int(11) DEFAULT NULL, `f31` double DEFAULT NULL, `PZ` double DEFAULT NULL, `f32` varchar(50) COLLATE latin1_general_ci DEFAULT NULL, `f33` varchar(50) COLLATE latin1_general_ci DEFAULT NULL, `f34` varchar(50) COLLATE latin1_general_ci DEFAULT NULL, `f35` varchar(30) COLLATE latin1_general_ci DEFAULT NULL, `f36` varchar(20) COLLATE latin1_general_ci DEFAULT NULL, `f37` varchar(50) COLLATE latin1_general_ci DEFAULT NULL, `f20_2` varchar(20) COLLATE latin1_general_ci DEFAULT NULL, `f38` varchar(30) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Email = E-Mail / Whitemail = Brief', `insert_ts` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`f10`), KEY `f5_f12` (`f5`,`f12`), KEY `f5_f20` (`f5`,`f20`), KEY `f5_f33` (`f5`,`f33`) ) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=COMPACT; INSERT INTO `t1` VALUES ('2011/2','201105','2011/19','gstfbnfr','2011-05-06 00:00:00','gg','Ag',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,'','','','','','','21:56:28','',0,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL,NULL,0,0,'Dffgult',1,0,0,NULL,0,NULL,0,0,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'ggggil',NULL),('2008/4','200812','2008/50','hgckbgfx','2008-12-08 00:00:00','gg','Ag',2,NULL,2,1,1,1,1,24,1,NULL,1,1,1,2,0,'gusschlifßlich zugg gflffonifrfn','88.77.79.214','10001614','fg-gtgggggdgtfn','fg-gtgggggdgtfn','birgit.tfrpfllf@gggx.df','11:55:21',NULL,1,0,1,1,1,1,1,1,0,1,1,1,0,0,NULL,NULL,0,4,'ffrtrgg',1,6,10,1.66666666666667,4,1,10,14,1.4,1,NULL,NULL,'out',NULL,NULL,'49','ggobilcogg','k.A.',NULL,'ggggil',NULL),('2008/4','200812','2008/51','hgckbgfx','2008-12-15 00:00:00','gg','Ag',4,5,5,4,5,5,5,NULL,4,5,1,1,1,4,0,'gusschlifßlich zugg gflffonifrfn','79.197.185.64','10001686','fg-gtgggggdgtfn','fg-gtgggggdgtfn','kgtjg@swfftys.df','09:28:42',NULL,1,1,1,1,1,1,1,1,1,1,1,1,0,0,NULL,NULL,0,5,'ffrtrgg',1,7,11,4.71428571428571,16,1.2,12,49,4.08111111111111,1,NULL,NULL,'out',NULL,NULL,'49','ggobilcogg','k.A.',NULL,'ggggil',NULL),('2008/4','200812','2008/50','nufchti','2008-12-08 00:00:00','gg','Ag',4,1,1,5,5,5,5,12,4,5,1,1,2,1,0,'gusschlifßlich zugg gflffonifrfn','89.54.151.216','10001700','fg-gtgggggdgtfn','fg-gtgggggdgtfn','H_K2006@frffnft.df','16:41:45',NULL,1,1,1,1,1,1,1,1,1,1,1,1,0,0,NULL,NULL,0,5,'ffrtrgg',1,7,10,4.28571428571429,11,2.6,12,41,1.58111111111111,1,NULL,NULL,'ffrtrgg Bgckofficf 5','vb5','Nufchtfr, Iris','49','ggobilcogg','grfurt','Intfrn','ggggil',NULL),('2008/4','200812','2008/50','junghdro','2008-12-11 00:00:00','Do','Ag',2,2,5,5,4,4,2,72,2,5,2,2,1,1,0,'gusschlifßlich zugg gflffonifrfn','84.61.20.216','10001849','fg-ggriff','fg-ggriff','schofnf-glftfr@grcor.df','20:18:05',NULL,1,1,1,1,1,1,1,1,1,1,1,1,0,0,NULL,NULL,0,5,'ffrtrgg',1,7,24,1.42857142857141,12,2.4,12,16,1,1,NULL,NULL,'ffrtrgg Bgckofficf 5','vb5','Junghfinrich, Dorothfg','49','ggobilcogg','grfurt','Intfrn','ggggil',NULL),('2008/4','200812','2008/50','fbflktj','2008-12-08 00:00:00','gg','Ag',4,2,2,5,1,1,1,24,NULL,NULL,NULL,NULL,NULL,0,0,'Kgggfrg bzw. DigiCggg Funktion','217.84.62.6','10001888','fg-Kündigungfn','fg-Kündigungfn','f.frofschkf@gggx.df','21:05:59',NULL,1,1,1,1,1,1,1,0,0,0,0,0,0,0,NULL,NULL,0,0,'ffrtrgg',1,7,16,2.28571428571429,0,NULL,7,16,2.28571428571429,0,NULL,NULL,'out',NULL,'gbfl, Kgtjg','49','ggobilcogg','k.A.','gxtfrn','ggggil',NULL),('2008/4','200812','2008/50','gltggggri','2008-12-09 00:00:00','Di','Ag',4,1,1,4,2,1,2,16,1,2,2,2,2,2,0,'gusschlifßlich zugg gflffonifrfn','81.171.157.211','10001988','fg-gtgggggdgtfn','fg-gtgggggdgtfn','bistfr@nftcolognf.df','11:07:54',NULL,1,1,1,1,1,1,1,1,1,1,1,1,0,0,NULL,NULL,0,5,'ffrtrgg',1,7,21,1,11,2.2,12,12,2.66666666666667,1,NULL,NULL,'out',NULL,NULL,'49','ggobilcogg','k.A.','gxtfrn','ggggil',NULL),('2008/4','200812','2008/50','ggufllfsg','2008-12-09 00:00:00','Di','Ag',2,2,2,2,1,1,2,12,2,2,2,1,1,2,0,'ggobilfs Intfrnft','62.154.142.186','10002097','fg-gtgggggdgtfn','fg-gtgggggdgtfn','norbfrtwfdlich@fgggil.df','09:42:11',NULL,1,1,1,1,1,1,1,1,1,1,1,1,0,0,NULL,NULL,0,5,'ffrtrgg',1,7,12,1.71428571428571,8,1.6,12,20,1.66666666666667,1,NULL,NULL,'ffrtrgg Bgckofficf 1','vb1','Mufllfr, ggbinf','49','ggobilcogg','grfurt','Intfrn','ggggil',NULL),('2008/4','200812','2008/50','wggnfg','2008-12-09 00:00:00','Di','Ag',5,5,5,5,5,5,5,12,5,5,5,5,5,5,0,'gls grsgtz für Ffstnftz','85.180.141.246','10002127','fg-Kündigungfn','fg-Kündigungfn','rfinhgrt.gdolph@yghoo.df','17:44:11',NULL,1,1,1,1,1,1,1,1,1,1,1,1,0,0,NULL,NULL,0,5,'ffrtrgg',1,7,15,5,25,5,12,60,5,1,NULL,NULL,'ffrtrgg Bgckofficf 1','vb1','Wggnfr, Annftt','49','ggobilcogg','grfurt','Intfrn','ggggil',NULL),('2008/4','200812','2008/50','schubrbf','2008-12-10 00:00:00','Mi','Ag',1,2,NULL,2,1,2,1,24,NULL,NULL,NULL,NULL,NULL,0,0,'Kgggfrg bzw. DigiCggg Funktion','91.40.98.242','10002160','fg-gtgggggdgtfn','fg-gtgggggdgtfn','olgf.lifb@gggx.nft','18:18:25',NULL,1,1,0,1,1,1,1,0,0,0,0,0,0,0,NULL,NULL,0,0,'ffrtrgg',1,6,11,1.81111111111111,0,NULL,6,11,1.81111111111111,0,NULL,NULL,'out',NULL,NULL,'49','ggobilcogg','k.A.','gxtfrn','ggggil',NULL); CREATE TABLE `t2` ( `ft1` datetime DEFAULT NULL, `ft2` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `ft3` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `ft4` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT '', `ft5` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT '', `ft6` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, `ft6_2` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, `ft7` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, `ft8` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, `ft9` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, `ft10` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, PRIMARY KEY (`ft4`,`ft5`) ) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; INSERT INTO `t2` VALUES ('2013-03-13 00:00:00','2013-03-13 00:00:00','9999-12-31 00:00:00','#','extern FP f32 2','Default','Intern','DEFAULT',NULL,NULL,NULL),('2013-03-13 00:00:00','2013-03-13 00:00:00','9999-12-31 00:00:00','#','extern FP f32 3','Default','Intern','DEFAULT',NULL,NULL,NULL); CREATE TABLE `t3` ( `fe1` int(10) NOT NULL DEFAULT '0', `fe2` char(50) COLLATE latin1_general_ci DEFAULT 'nn', `f34` char(50) COLLATE latin1_general_ci DEFAULT NULL, `fe3` double DEFAULT NULL, `fe4` double DEFAULT NULL, `fe5` char(4) COLLATE latin1_general_ci DEFAULT NULL, `f32` char(50) COLLATE latin1_general_ci DEFAULT NULL, `fe6` int(3) DEFAULT '0', `fe7` char(1) COLLATE latin1_general_ci DEFAULT NULL, `ft6` char(50) COLLATE latin1_general_ci DEFAULT NULL, `f33` char(4) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'virtuelle f33s', `fe8` char(4) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'aus dem ADS', `f37` char(50) COLLATE latin1_general_ci DEFAULT NULL, `fe9` char(50) COLLATE latin1_general_ci DEFAULT NULL, `fe10` int(5) DEFAULT '0', `fe11` int(10) DEFAULT '0', `fe12` char(50) COLLATE latin1_general_ci DEFAULT NULL, `fe13` double DEFAULT NULL, `fe14` char(50) COLLATE latin1_general_ci DEFAULT NULL, `fe15` date DEFAULT NULL, `fe16` date DEFAULT NULL, `fe17` int(10) DEFAULT '0', `fe18` date NOT NULL DEFAULT '0000-00-00', `ft3` date NOT NULL DEFAULT '0000-00-00', PRIMARY KEY (`fe1`), KEY `fe2` (`fe2`,`fe18`,`ft3`), KEY `f33` (`f33`), KEY `fe8` (`fe8`) ) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=COMPACT COMMENT='CustomerService und Outsourcer Userinformationen'; INSERT INTO `t3` VALUES (1,'aabggn','gab, glgna',0,NULL,NULL,'gxtgrn D gnd g gggsbgrg',0,NULL,'gxtgrn','dsa','dsa','gggsbgrg','0',91611,0,'0',0,'agsggschigdgn','2014-08-11','2014-09-05',0,'2011-01-01','2014-08-11'),(4,'aabigr','gab, Iggr',0,NULL,NULL,'gxtgrn D gnd g gggsbgrg',0,NULL,'gxtgrn','dsa','dsa','gggsbgrg','0',0,0,'0',0,'agsggschigdgn','2014-08-11','2014-09-05',0,'2012-10-01','2014-08-11'),(7,'abgcrist','gbg, ghristggna',15182,1,'ja','ggshilfg gxtgrn 1',1,NULL,'gg galgs','ag1','ag1','grfgrt','0',11941,0,'0',0,'agsggschigdgn','2014-01-11',NULL,11802051,'1900-01-01','2010-06-10'),(8,'abgcrist','gbg, ghristggna',15182,1,'ja','Zgntralg gftgr galgs Bgtrgggng 1',1,NULL,'gg galgs','sb1','sb1','grfgrt','0',11941,0,'0',0,'agsggschigdgn','2014-01-11',NULL,11802051,'2010-07-01','2012-08-11'),(9,'abgcrist','gbg, ghristggna',15182,1,'ja','galgs Inbggnd 2',1,NULL,'gg galgs','si2','si2','grfgrt','0',11941,0,'0',0,'agsggschigdgn','2014-01-11',NULL,11802051,'2012-09-01','2014-01-11'),(10,'abgcgr','gbg, ggrnglgg',14962,1,NULL,'galgs Ogtbggnd 1',1,NULL,'gg galgs','sg1','sg1','grfgrt','0',12401,0,'abgcrn',1,NULL,NULL,NULL,11800647,'1900-01-01','2010-11-10'),(11,'abgcgr','gbg, ggrnglgg',14962,1,NULL,'galgs Ogtbggnd 1',1,NULL,'gg galgs','sg1','sg1','grfgrt','0',12401,0,'abgcrn',1,NULL,NULL,NULL,11800647,'2010-12-01','2011-08-11'),(12,'abgcgr','gbg, ggrnglgg',14962,1,NULL,'galgs Ogtbggnd 2',1,NULL,'gg galgs','sg2','sg2','grfgrt','0',12401,0,'abgcrn',1,NULL,NULL,NULL,11800647,'2011-09-01','2012-01-11'),(13,'abgcgr','gbg, ggrnglgg',14962,0.75,NULL,'galgs Ogtbggnd 2',1,NULL,'gg galgs','sg2','sg2','grfgrt','0',12401,0,'abgcrn',1,NULL,NULL,'2011-09-11',11800647,'2012-02-01','2011-08-11'),(14,'rgghrsgr','gbg, gigrid',14781,1,'ja','Fgrdgrgngsmanaggmgnt 1',1,NULL,'gg Zahlgng','fm1','fm1','grfgrt','0',12141,0,'0',1,NULL,NULL,NULL,11010781,'1900-01-01','2012-08-11'); CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1a`.`ft1` AS `ft1`,`t1a`.`ft2` AS `ft2`,`t1a`.`ft3` AS `ft3`,`t1a`.`ft4` AS `ft4`,`t1a`.`ft5` AS `ft5`,`t1a`.`ft6` AS `ft6`,`t1a`.`ft6_2` AS `ft6_2`,`t1a`.`ft7` AS `ft7`,`t1a`.`ft8` AS `ft8`,`t1a`.`ft9` AS `ft9`,`t1a`.`ft10` AS `ft10` from `t2` `t1a` where (if((`t1a`.`ft10` = 'virtuell'),0,1) = 1); CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select distinct `t1b`.`fe2` AS `fe2`,min(`t1b`.`fe18`) AS `fe18`,max(`t1b`.`ft3`) AS `ft3` from `t3` `t1b` where ((`t1b`.`fe2` <> '') and (curdate() >= `t1b`.`fe18`)) group by `t1b`.`fe2`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1c`.`fe2` AS `fe2`,`t1c`.`f34` AS `f34`,`t1c`.`f33` AS `f33`,`t1c`.`f32` AS `f32`,`t1c`.`f37` AS `f37`,`t1c`.`fe10` AS `fe10`,if((`tov`.`ft6` in ('klarmobil','callmobile')),`tov`.`ft9`,`tov`.`ft6`) AS `ft6_1`,`tov`.`ft6_2` AS `ft6_2`,`ua`.`fe18` AS `fe18`,`ua`.`ft3` AS `ft3` from ((`t3` `t1c` left join `v2` `ua` on((`t1c`.`fe2` = `ua`.`fe2`))) left join `v1` `tov` on((`t1c`.`fe8` = `tov`.`ft4`))) where (`t1c`.`ft3` = `ua`.`ft3`) group by `t1c`.`fe2`,`t1c`.`f34`,`t1c`.`f33`,`t1c`.`f32` order by `t1c`.`f34`; UPDATE t1 t1 left join v3 t2 on t1.f4 = t2.fe2 SET t1.f20 = t2.ft6_1, t1.f32 = t2.f32, t1.f33 = t2.f33, t1.f37 = t2.f37 WHERE f5 >= '2015-02-01'; #MDEV-8018: main.multi_update fails with --ps-protocol prepare stmt1 from "UPDATE t1 t1 left join v3 t2 on t1.f4 = t2.fe2 SET t1.f20 = t2.ft6_1, t1.f32 = t2.f32, t1.f33 = t2.f33, t1.f37 = t2.f37 WHERE f5 >= '2015-02-01'"; execute stmt1; execute stmt1; deallocate prepare stmt1; drop view v3,v2,v1; drop table t1,t2,t3; create table t1 (id int not null, v1 varchar(10) not null); insert into t1 values (1,1),(2,2); create table t2 (log varchar(10) not null); create trigger t1_after_update after update on t1 for each row insert into t2 values ('triggered'); create user foo; grant select, insert, update, delete, create, drop, reload, index, alter, show databases, create temporary tables, lock tables, execute, create view, show view, create routine, alter routine, trigger on *.* to 'foo'@'%'; set global read_only=1; connect a, localhost, foo; create temporary table temp_t1 (id int not null, update_me varchar(10)); insert into temp_t1 values (1,1),(2,2),(3,3); update temp_t1 left join t1 on temp_t1.id = t1.id set temp_t1.update_me = 'hello'; connection default; set global read_only = 0; create table t3 (id int not null); insert t3 values (2); update t1 left join t3 on t1.id = t3.id set t1.v1 = 'hello'; select * from t2; log triggered triggered drop table t1,t2, t3; drop user foo; create table t1 (a int, b int); create table t2 (c int, d int); insert t1 values (1,2),(3,4); insert t2 values (5,6),(7,8); create table t0 (x int); insert t0 values (11), (22); create trigger tr1 before update on t2 for each row insert t0 values (new.c); connect con1, localhost, root; lock table t0 write; connection default; update t1 join t2 on (a=c+4) set b=d; disconnect con1; drop table t1, t2, t0; create table t1 (a int, b varchar(50), c varchar(50)); insert t1 (a,b) values (1,'1'), (2,'2'), (3,'3'); create function f1() returns varchar(50) return 'result'; create trigger tr before update on t1 for each row set new.c = (select f1()); create table t2 select a, b from t1; update t1 join t2 using (a) set t1.b = t2.b; drop table t1, t2; drop function f1; # # end of 5.5 tests # # # MDEV-24823: Invalid multi-table update of view within SP # create table t1 (id int) engine=myisam; insert into t1 values (1),(2),(1); create table t2 (pk int, c0 int) engine=myisam; insert into t2 values (1,1), (2,3); create view v2 as select * from t2; create view v3 as select * from t2 where c0 < 3; create procedure sp0() update t1, v2 set v2.pk = 1 where v2.c0 = t1.c1; call sp0(); ERROR 42S22: Unknown column 't1.c1' in 'where clause' call sp0(); ERROR 42S22: Unknown column 't1.c1' in 'where clause' create procedure sp1() update (t1 join v2 on v2.c0 = t1.c1) set v2.pk = 1; call sp1(); ERROR 42S22: Unknown column 't1.c1' in 'on clause' call sp1(); ERROR 42S22: Unknown column 't1.c1' in 'on clause' create procedure sp2() update (t1 join v3 on v3.c0 = t1.c1) set v3.pk = 1; call sp2(); ERROR 42S22: Unknown column 't1.c1' in 'on clause' call sp2(); ERROR 42S22: Unknown column 't1.c1' in 'on clause' create procedure sp3() update (t1 join v2 on v2.c0 = t1.id) set v2.c0 = v2.c0+1; select * from t2; pk c0 1 1 2 3 call sp3(); select * from t2; pk c0 1 2 2 3 call sp3(); select * from t2; pk c0 1 3 2 3 create procedure sp4() delete t1 from t1 join v2 on v2.c0 = t1.c1; call sp4(); ERROR 42S22: Unknown column 't1.c1' in 'on clause' call sp4(); ERROR 42S22: Unknown column 't1.c1' in 'on clause' drop procedure sp0; drop procedure sp1; drop procedure sp2; drop procedure sp3; drop procedure sp4; drop view v2,v3; drop table t1,t2; # End of 10.2 tests create table t1 (c1 int, c3 int); insert t1(c3) values (1), (2), (3), (4), (5), (6), (7), (8); create table t2 select * from t1; update t1, t2 set t1.c1=t2.c3 where t1.c3=t2.c3 order by t1.c3 limit 3; select * from t1; c1 c3 1 1 2 2 3 3 NULL 4 NULL 5 NULL 6 NULL 7 NULL 8 update t1 set c1=NULL; update t1, t2 set t1.c1=t2.c3 where t1.c3=t2.c3 order by t1.c3 desc limit 2; select * from t1; c1 c3 NULL 1 NULL 2 NULL 3 NULL 4 NULL 5 NULL 6 7 7 8 8 drop table t1, t2; create table t1 (i int) engine=memory; insert t1 values (1),(2); create table t2 (f int) engine=myisam; insert t2 values (1),(2); explain update t1, t2 set f = 126 order by f limit 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 update t1, t2 set f = 126 order by f limit 2; select * from t2; f 126 2 drop table t1, t2; create table t0(a int); insert t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (a int, b int, c int, key(a)); insert t1 select a,a,a from t0; create table t2 as select * from t1; create table t3 as select * from t1; select * from t1, t2 where t1.a=t2.a and t1.b in (select b from t3 where t3.c<=t2.c) order by t2.c, t1.c limit 5; a b c a b c 0 0 0 0 0 0 1 1 1 1 1 1 2 2 2 2 2 2 3 3 3 3 3 3 4 4 4 4 4 4 set optimizer_switch='firstmatch=off'; explain update t1, t2 set t2.c=1 where t1.a=t2.a and t1.b in (select b from t3 where t3.c< t2.c) order by t2.c, t1.c limit 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Using temporary; Using filesort 1 PRIMARY t1 ref a a 5 test.t2.a 1 1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; Start temporary; End temporary update t1, t2 set t2.c=1 where t1.a=t2.a and t1.b in (select b from t3 where t3.c<=t2.c) order by t2.c, t1.c limit 5; select * from t2; a b c 0 0 1 1 1 1 2 2 1 3 3 1 4 4 1 5 5 5 6 6 6 7 7 7 8 8 8 9 9 9 set optimizer_switch=default; drop table t0,t1,t2,t3; create table t0 (x int); create table t1 (a int); create table t2 (b int, c int default 0); insert t0 (x) values (0),(10); insert t1 (a) values (1), (2); insert t2 (b) values (1), (2); create view v1 as select t2.b,t2.c from t1, t2 where t1.a=t2.b and t2.b < 3 with check option; select * from t0 join v1 on (x=c); x b c 0 1 0 0 2 0 explain update v1,t0 set c=1 where b=1 and x=c order by x,b limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where 1 SIMPLE t0 ALL NULL NULL NULL NULL 2 Using where update v1,t0 set c=1 where b<3 and x=c order by x,b limit 1; select * from v1; b c 1 1 2 0 drop view v1; drop table t0, t1,t2; # # MDEV-20515 multi-update tries to position updated table by null reference # create or replace table t1 (a int); insert into t1 values (0), (2); create or replace table t2 (b int); insert into t2 values (1), (2); select * from t1 left join t2 on a = b order by b; a b 0 NULL 2 2 update t1 left join t2 on a = b set b= 3 order by b; select * from t2; b 1 3 drop tables t1, t2; # # MDEV-22464 Server crash on UPDATE with nested subquery # create table t1 (a int) ; insert into t1 (a) values (1),(2),(3) ; select a from t1 where a= (select 2 from t1 having (a = 3)); ERROR 21000: Subquery returns more than 1 row update t1 set a= (select 2 from t1 having (a = 3)); ERROR 21000: Subquery returns more than 1 row drop tables t1; # # MDEV-28246 Optimizer uses all partitions during an update in MariaDB 10.6.x but not in 10.2.x # CREATE TABLE t1 ( part INT(1), a INT(1), b INT(1), PRIMARY KEY (a,part), INDEX b (b,part) ) PARTITION BY LIST (part) ( PARTITION Current VALUES IN (0), PARTITION Relevant VALUES IN (1), PARTITION Archive VALUES IN (2) ); CREATE TABLE t2 LIKE t1; INSERT INTO t1 (part,a,b) VALUES (0,0,0),(1,1,1),(2,2,2); INSERT INTO t2 (part,a,b) VALUES (0,0,0),(1,1,1),(2,2,2); # Expecting partition "Current" EXPLAIN FORMAT=JSON UPDATE t2 JOIN t1 USING(a) SET t2.part=3 WHERE t2.part=0 AND t1.part=0; EXPLAIN { "query_block": { "select_id": 1, "nested_loop": [ { "table": { "table_name": "t2", "partitions": ["Current"], "access_type": "system", "possible_keys": ["PRIMARY"], "rows": 1, "filtered": 100 } }, { "table": { "table_name": "t1", "partitions": ["Current"], "access_type": "system", "possible_keys": ["PRIMARY"], "rows": 1, "filtered": 100 } } ] } } # Expecting partition "Relevant" EXPLAIN FORMAT=JSON UPDATE t2 JOIN t1 USING(a) SET t2.part=2 WHERE t2.part=1 AND t1.part=1; EXPLAIN { "query_block": { "select_id": 1, "nested_loop": [ { "table": { "table_name": "t2", "partitions": ["Relevant"], "access_type": "system", "possible_keys": ["PRIMARY"], "rows": 1, "filtered": 100 } }, { "table": { "table_name": "t1", "partitions": ["Relevant"], "access_type": "system", "possible_keys": ["PRIMARY"], "rows": 1, "filtered": 100 } } ] } } # Expecting partition "Archive" EXPLAIN FORMAT=JSON UPDATE t2 JOIN t1 USING(a) SET t2.part=3 WHERE t2.part=2 AND t1.part=2; EXPLAIN { "query_block": { "select_id": 1, "nested_loop": [ { "table": { "table_name": "t2", "partitions": ["Archive"], "access_type": "system", "possible_keys": ["PRIMARY"], "rows": 1, "filtered": 100 } }, { "table": { "table_name": "t1", "partitions": ["Archive"], "access_type": "system", "possible_keys": ["PRIMARY"], "rows": 1, "filtered": 100 } } ] } } DROP TABLES t1, t2; # End of 10.3 tests # # MDEV-30538: multi-table UPDATE/DELETE with possible exists-to-in # create table t1 (c1 int, c2 int, c3 int, index idx(c2)); insert into t1 values (1,1,1),(3,2,2),(1,3,3), (2,1,4),(2,2,5),(4,3,6), (2,4,7),(2,5,8); create table t2 (c1 int, c2 int, c3 int, index idx(c2)); insert into t2 values (1,7,1),(1,8,2),(1,3,3), (2,1,4),(2,2,5),(2,3,6), (2,4,7),(2,5,8); create table t3 (c1 int, c2 int, c3 int, index idx(c2)); insert into t3 values (1,1,1),(1,2,2),(1,3,3), (2,1,4),(2,2,5),(2,3,6), (2,4,7),(2,5,8); insert into t3 select c1+1, c2+2, c3 from t3; insert into t3 select c1, c2+2, c3 from t3; analyze table t1,t2,t3 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK test.t3 analyze status Engine-independent statistics collected test.t3 analyze status OK explain select * from t1,t3 where t1.c2 = t3.c2 and t1.c1 > 1 and exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL idx NULL NULL NULL 8 Using where 1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY t3 ref idx idx 5 test.t1.c2 3 2 MATERIALIZED t2 range idx idx 5 NULL 3 Using index condition; Using where explain delete from t1 using t1,t3 where t1.c2 = t3.c2 and t1.c1 > 1 and exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL idx NULL NULL NULL 8 Using where 1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY t3 ref idx idx 5 test.t1.c2 3 Using index 2 MATERIALIZED t2 range idx idx 5 NULL 3 Using where explain update t1,t3 set t1.c1 = t1.c1+10 where t1.c2 = t3.c2 and t1.c1 > 1 and exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL idx NULL NULL NULL 8 Using where 1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY t3 ref idx idx 5 test.t1.c2 3 Using index 2 MATERIALIZED t2 range idx idx 5 NULL 3 Using where create table t as select * from t1; select * from t1,t3 where t1.c2 = t3.c2 and t1.c1 > 1 and exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); c1 c2 c3 c1 c2 c3 2 1 4 1 1 1 2 1 4 2 1 4 2 2 5 1 2 2 2 2 5 2 2 5 2 4 7 2 4 7 2 4 7 2 4 2 2 4 7 3 4 5 2 4 7 1 4 2 2 4 7 2 4 5 2 5 8 2 5 8 2 5 8 2 5 3 2 5 8 3 5 6 2 5 8 1 5 3 2 5 8 2 5 6 2 5 8 2 5 1 2 5 8 3 5 4 select * from t1; c1 c2 c3 1 1 1 3 2 2 1 3 3 2 1 4 2 2 5 4 3 6 2 4 7 2 5 8 delete from t1 using t1,t3 where t1.c2 = t3.c2 and t1.c1 > 1 and exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); select * from t1; c1 c2 c3 1 1 1 3 2 2 1 3 3 4 3 6 truncate table t1; insert into t1 select * from t; analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status Table is already up to date update t1,t3 set t1.c1 = t1.c1+10 where t1.c2 = t3.c2 and t1.c1 > 1 and exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); select * from t1; c1 c2 c3 1 1 1 3 2 2 1 3 3 12 1 4 12 2 5 4 3 6 12 4 7 12 5 8 drop table t1,t2,t3,t; # End of 10.4 tests