1create table t1(id1 int not null auto_increment primary key, t char(12)); 2create table t2(id2 int not null, t char(12)); 3create table t3(id3 int not null, t char(12), index(id3)); 4select count(*) from t1 where id1 > 95; 5count(*) 65 7select count(*) from t2 where id2 > 95; 8count(*) 925 10select count(*) from t3 where id3 > 95; 11count(*) 12250 13update 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; 14select count(*) from t1 where t = "aaa"; 15count(*) 1610 17select count(*) from t1 where id1 > 90; 18count(*) 1910 20select count(*) from t2 where t = "bbb"; 21count(*) 2250 23select count(*) from t2 where id2 > 90; 24count(*) 2550 26select count(*) from t3 where t = "cc"; 27count(*) 28500 29select count(*) from t3 where id3 > 90; 30count(*) 31500 32delete t1.*, t2.*, t3.* from t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 95; 33check table t1, t2, t3; 34Table Op Msg_type Msg_text 35test.t1 check status OK 36test.t2 check status OK 37test.t3 check status OK 38select count(*) from t1 where id1 > 95; 39count(*) 400 41select count(*) from t2 where id2 > 95; 42count(*) 430 44select count(*) from t3 where id3 > 95; 45count(*) 460 47delete t1, t2, t3 from t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 5; 48select count(*) from t1 where id1 > 5; 49count(*) 500 51select count(*) from t2 where id2 > 5; 52count(*) 530 54select count(*) from t3 where id3 > 5; 55count(*) 560 57delete from t1, t2, t3 using t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 0; 58select count(*) from t1 where id1; 59count(*) 600 61select count(*) from t2 where id2; 62count(*) 630 64select count(*) from t3 where id3; 65count(*) 660 67drop table t1,t2,t3; 68create table t1(id1 int not null primary key, t varchar(100)) pack_keys = 1; 69create table t2(id2 int not null, t varchar(100), index(id2)) pack_keys = 1; 70delete t1 from t1,t2 where t1.id1 = t2.id2 and t1.id1 > 500; 71drop table t1,t2; 72CREATE TABLE t1 ( 73id int(11) NOT NULL default '0', 74name varchar(10) default NULL, 75PRIMARY KEY (id) 76) ENGINE=MyISAM; 77INSERT INTO t1 VALUES (1,'aaa'),(2,'aaa'),(3,'aaa'); 78CREATE TABLE t2 ( 79id int(11) NOT NULL default '0', 80name varchar(10) default NULL, 81PRIMARY KEY (id) 82) ENGINE=MyISAM; 83INSERT INTO t2 VALUES (2,'bbb'),(3,'bbb'),(4,'bbb'); 84CREATE TABLE t3 ( 85id int(11) NOT NULL default '0', 86mydate datetime default NULL, 87PRIMARY KEY (id) 88) ENGINE=MyISAM; 89INSERT 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 9000:00:00'),(7,'2002-07-22 00:00:00'); 91delete 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; 92select * from t3; 93id mydate 941 2002-02-04 00:00:00 955 2002-05-12 00:00:00 966 2002-06-22 00:00:00 977 2002-07-22 00:00:00 98DROP TABLE t1,t2,t3; 99CREATE TABLE IF NOT EXISTS `t1` ( 100`id` int(11) NOT NULL auto_increment, 101`tst` text, 102`tst1` text, 103PRIMARY KEY (`id`) 104) ENGINE=MyISAM; 105CREATE TABLE IF NOT EXISTS `t2` ( 106`ID` int(11) NOT NULL auto_increment, 107`ParId` int(11) default NULL, 108`tst` text, 109`tst1` text, 110PRIMARY KEY (`ID`), 111KEY `IX_ParId_t2` (`ParId`), 112FOREIGN KEY (`ParId`) REFERENCES `t1` (`id`) 113) ENGINE=MyISAM; 114INSERT INTO t1(tst,tst1) VALUES("MySQL","MySQL AB"), ("MSSQL","Microsoft"), ("ORACLE","ORACLE"); 115INSERT INTO t2(ParId) VALUES(1), (2), (3); 116select * from t2; 117ID ParId tst tst1 1181 1 NULL NULL 1192 2 NULL NULL 1203 3 NULL NULL 121UPDATE t2, t1 SET t2.tst = t1.tst, t2.tst1 = t1.tst1 WHERE t2.ParId = t1.Id; 122select * from t2; 123ID ParId tst tst1 1241 1 MySQL MySQL AB 1252 2 MSSQL Microsoft 1263 3 ORACLE ORACLE 127drop table t1, t2 ; 128create table t1 (n numeric(10)); 129create table t2 (n numeric(10)); 130insert into t2 values (1),(2),(4),(8),(16),(32); 131select * from t2 left outer join t1 using (n); 132n 1331 1342 1354 1368 13716 13832 139delete t1,t2 from t2 left outer join t1 using (n); 140select * from t2 left outer join t1 using (n); 141n 142drop table t1,t2 ; 143create table t1 (n int(10) not null primary key, d int(10)); 144create table t2 (n int(10) not null primary key, d int(10)); 145insert into t1 values(1,1); 146insert into t2 values(1,10),(2,20); 147LOCK TABLES t1 write, t2 read; 148DELETE t1.*, t2.* FROM t1,t2 where t1.n=t2.n; 149ERROR HY000: Table 't2' was locked with a READ lock and can't be updated 150UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n; 151ERROR HY000: Table 't2' was locked with a READ lock and can't be updated 152UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n; 153unlock tables; 154LOCK TABLES t1 write, t2 write; 155UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n; 156select * from t1; 157n d 1581 10 159DELETE t1.*, t2.* FROM t1,t2 where t1.n=t2.n; 160select * from t1; 161n d 162select * from t2; 163n d 1642 20 165unlock tables; 166drop table t1,t2; 167set sql_safe_updates=1; 168create table t1 (n int(10), d int(10)); 169create table t2 (n int(10), d int(10)); 170insert into t1 values(1,1); 171insert into t2 values(1,10),(2,20); 172UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n; 173ERROR HY000: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column 174set sql_safe_updates=0; 175drop table t1,t2; 176set timestamp=1038401397; 177create table t1 (n int(10) not null primary key, d int(10), t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 178create table t2 (n int(10) not null primary key, d int(10), t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 179insert into t1 values(1,1,NULL); 180insert into t2 values(1,10,NULL),(2,20,NULL); 181set timestamp=1038000000; 182UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n; 183select n,d,unix_timestamp(t) from t1; 184n d unix_timestamp(t) 1851 10 1038000000 186select n,d,unix_timestamp(t) from t2; 187n d unix_timestamp(t) 1881 10 1038401397 1892 20 1038401397 190UPDATE t1,t2 SET 1=2 WHERE t1.n=t2.n; 191ERROR 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 192drop table t1,t2; 193set timestamp=0; 194set sql_safe_updates=0; 195create table t1 (n int(10) not null primary key, d int(10)); 196create table t2 (n int(10) not null primary key, d int(10)); 197insert into t1 values(1,1), (3,3); 198insert into t2 values(1,10),(2,20); 199UPDATE t2 left outer join t1 on t1.n=t2.n SET t1.d=t2.d; 200select * from t1; 201n d 2021 10 2033 3 204select * from t2; 205n d 2061 10 2072 20 208drop table t1,t2; 209create table t1 (n int(10), d int(10)); 210create table t2 (n int(10), d int(10)); 211insert into t1 values(1,1),(1,2); 212insert into t2 values(1,10),(2,20); 213UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n; 214select * from t1; 215n d 2161 10 2171 10 218select * from t2; 219n d 2201 30 2212 20 222drop table t1,t2; 223create table t1 (n int(10), d int(10)); 224create table t2 (n int(10), d int(10)); 225insert into t1 values(1,1),(3,2); 226insert into t2 values(1,10),(1,20); 227UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n; 228select * from t1; 229n d 2301 10 2313 2 232select * from t2; 233n d 2341 30 2351 30 236UPDATE t1 a ,t2 b SET a.d=b.d,b.d=30 WHERE a.n=b.n; 237select * from t1; 238n d 2391 30 2403 2 241select * from t2; 242n d 2431 30 2441 30 245DELETE a, b FROM t1 a,t2 b where a.n=b.n; 246select * from t1; 247n d 2483 2 249select * from t2; 250n d 251drop table t1,t2; 252CREATE TABLE t1 ( broj int(4) unsigned NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) ENGINE=MyISAM; 253INSERT INTO t1 VALUES (1,'jedan'),(2,'dva'),(3,'tri'),(4,'xxxxxxxxxx'),(5,'a'),(10,''),(11,''),(12,''),(13,''); 254CREATE TABLE t2 ( broj int(4) unsigned NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) ENGINE=MyISAM; 255INSERT INTO t2 VALUES (1,'jedan'),(2,'dva'),(3,'tri'),(4,'xxxxxxxxxx'),(5,'a'); 256CREATE TABLE t3 ( broj int(4) unsigned NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) ENGINE=MyISAM; 257INSERT INTO t3 VALUES (1,'jedan'),(2,'dva'); 258update t1,t2 set t1.naziv="aaaa" where t1.broj=t2.broj; 259update t1,t2,t3 set t1.naziv="bbbb", t2.naziv="aaaa" where t1.broj=t2.broj and t2.broj=t3.broj; 260drop table t1,t2,t3; 261CREATE TABLE t1 (a int not null primary key, b int not null, key (b)); 262CREATE TABLE t2 (a int not null primary key, b int not null, key (b)); 263INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); 264INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); 265update t1,t2 set t1.a=t1.a+100; 266select * from t1; 267a b 268101 1 269102 2 270103 3 271104 4 272105 5 273106 6 274107 7 275108 8 276109 9 277update t1,t2 set t1.a=t1.a+100 where t1.a=101; 278select * from t1; 279a b 280201 1 281102 2 282103 3 283104 4 284105 5 285106 6 286107 7 287108 8 288109 9 289update t1,t2 set t1.b=t1.b+10 where t1.b=2; 290select * from t1; 291a b 292201 1 293102 12 294103 3 295104 4 296105 5 297106 6 298107 7 299108 8 300109 9 301update 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; 302select * from t1; 303a b 304201 1 305102 12 306103 5 307104 6 308105 7 309106 6 310107 7 311108 8 312109 9 313select * from t2; 314a b 3151 1 3162 2 3173 13 3184 14 3195 15 3206 6 3217 7 3228 8 3239 9 324update 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); 325drop table t1,t2; 326CREATE 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; 327INSERT INTO t3 VALUES ('A',1,1,22,3.2,'R','R','BASE2',0.24,NULL); 328create table t1 (A varchar(1)); 329insert into t1 values ("A") ,("B"),("C"),("D"); 330create table t2(Z varchar(15)); 331insert 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; 332update t2,t3 set Z =param_scenario_costs; 333drop table t1,t2,t3; 334create table t1 (a int, b int); 335create table t2 (a int, b int); 336insert into t1 values (1,1),(2,1),(3,1); 337insert into t2 values (1,1), (3,1); 338update 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; 339select 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; 340a b a b 3412 2 NULL NULL 342drop table t1,t2; 343create table t1 (a int not null auto_increment primary key, b int not null); 344insert into t1 (b) values (1),(2),(3),(4); 345update t1, t1 as t2 set t1.b=t2.b+1 where t1.a=t2.a; 346select * from t1; 347a b 3481 2 3492 3 3503 4 3514 5 352drop table t1; 353create table t1(id1 smallint(5), field char(5)); 354create table t2(id2 smallint(5), field char(5)); 355insert into t1 values (1, 'a'), (2, 'aa'); 356insert into t2 values (1, 'b'), (2, 'bb'); 357select * from t1; 358id1 field 3591 a 3602 aa 361select * from t2; 362id2 field 3631 b 3642 bb 365update t2 inner join t1 on t1.id1=t2.id2 366set t2.field=t1.field 367where 0=1; 368update t2, t1 set t2.field=t1.field 369where t1.id1=t2.id2 and 0=1; 370delete t1, t2 from t2 inner join t1 on t1.id1=t2.id2 371where 0=1; 372delete t1, t2 from t2,t1 373where t1.id1=t2.id2 and 0=1; 374drop table t1,t2; 375CREATE TABLE t1 ( a int ); 376CREATE TABLE t2 ( a int ); 377DELETE t1 FROM t1, t2 AS t3; 378DELETE t4 FROM t1, t1 AS t4; 379DELETE t3 FROM t1 AS t3, t1 AS t4; 380DELETE t1 FROM t1 AS t3, t2 AS t4; 381ERROR 42S02: Unknown table 't1' in MULTI DELETE 382INSERT INTO t1 values (1),(2); 383INSERT INTO t2 values (1),(2); 384DELETE t1 FROM t1 AS t2, t2 AS t1 where t1.a=t2.a and t1.a=1; 385SELECT * from t1; 386a 3871 3882 389SELECT * from t2; 390a 3912 392DELETE t2 FROM t1 AS t2, t2 AS t1 where t1.a=t2.a and t1.a=2; 393SELECT * from t1; 394a 3951 396SELECT * from t2; 397a 3982 399DROP TABLE t1,t2; 400create 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`) ); 401create 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`) ); 402insert into t1 values (0,'A01-Comp',1); 403insert into t1 values (0,'B01-Comp',1); 404insert into t2 values (0,1,'A Note',1); 405update t1 left join t2 on p_id = c2_p_id set c2_note = 'asdf-1' where p_id = 2; 406select * from t1; 407p_id p_code p_active 4081 A01-Comp 1 4092 B01-Comp 1 410select * from t2; 411c2_id c2_p_id c2_note c2_active 4121 1 A Note 1 413drop table t1, t2; 414connect root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK; 415create database mysqltest; 416create table mysqltest.t1 (a int, b int, primary key (a)); 417create table mysqltest.t2 (a int, b int, primary key (a)); 418create table mysqltest.t3 (a int, b int, primary key (a)); 419create user mysqltest_1@localhost; 420grant select on mysqltest.* to mysqltest_1@localhost; 421grant update on mysqltest.t1 to mysqltest_1@localhost; 422connect user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK; 423update t1, t2 set t1.b=1 where t1.a=t2.a; 424update t1, t2 set t1.b=(select t3.b from t3 where t1.a=t3.a) where t1.a=t2.a; 425connection root; 426revoke all privileges on mysqltest.t1 from mysqltest_1@localhost; 427revoke all privileges on mysqltest.* from mysqltest_1@localhost; 428delete from mysql.user where user=_binary'mysqltest_1'; 429flush privileges; 430drop database mysqltest; 431connection default; 432disconnect user1; 433disconnect root; 434create table t1 (a int, primary key (a)); 435create table t2 (a int, primary key (a)); 436create table t3 (a int, primary key (a)); 437delete t1,t3 from t1,t2 where t1.a=t2.a and t2.a=(select t3.a from t3 where t1.a=t3.a); 438ERROR 42S02: Unknown table 't3' in MULTI DELETE 439drop table t1, t2, t3; 440create table t1 (col1 int); 441create table t2 (col1 int); 442update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1; 443delete t1 from t1,t2 where t1.col1 < (select max(col1) from t1) and t1.col1 = t2.col1; 444ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data 445drop table t1,t2; 446create table t1(a int); 447create table t2(a int); 448delete from t1,t2 using t1,t2 where t1.a=(select a from t1); 449ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data 450drop table t1, t2; 451create table t1 (a int, b int); 452insert into t1 values (1, 2), (2, 3), (3, 4); 453create table t2 (a int); 454insert into t2 values (10), (20), (30); 455create view v1 as select a as b, a/10 as a from t2; 456connect locker,localhost,root,,test; 457lock table t1 write; 458connect changer,localhost,root,,test; 459alter table t1 add column c int default 100 after a; 460connect updater,localhost,root,,test; 461update t1, v1 set t1.b=t1.a+t1.b+v1.b where t1.a=v1.a; 462connection locker; 463unlock tables; 464connection changer; 465connection updater; 466select * from t1; 467a c b 4681 100 13 4692 100 25 4703 100 37 471select * from t2; 472a 47310 47420 47530 476drop view v1; 477drop table t1, t2; 478connection default; 479disconnect locker; 480disconnect changer; 481disconnect updater; 482create table t1 (i1 int, i2 int, i3 int); 483create table t2 (id int, c1 varchar(20), c2 varchar(20)); 484insert into t1 values (1,5,10),(3,7,12),(4,5,2),(9,10,15),(2,2,2); 485insert into t2 values (9,"abc","def"),(5,"opq","lmn"),(2,"test t","t test"); 486select * from t1 order by i1; 487i1 i2 i3 4881 5 10 4892 2 2 4903 7 12 4914 5 2 4929 10 15 493select * from t2; 494id c1 c2 4959 abc def 4965 opq lmn 4972 test t t test 498update t1,t2 set t1.i2=15, t2.c2="ppc" where t1.i1=t2.id; 499select * from t1 order by i1; 500i1 i2 i3 5011 5 10 5022 15 2 5033 7 12 5044 5 2 5059 15 15 506select * from t2 order by id; 507id c1 c2 5082 test t ppc 5095 opq lmn 5109 abc ppc 511delete t1.*,t2.* from t1,t2 where t1.i2=t2.id; 512select * from t1 order by i1; 513i1 i2 i3 5142 15 2 5153 7 12 5169 15 15 517select * from t2 order by id; 518id c1 c2 5192 test t ppc 5209 abc ppc 521drop table t1, t2; 522create table t1 (i1 int auto_increment not null, i2 int, i3 int, primary key (i1)); 523create table t2 (id int auto_increment not null, c1 varchar(20), c2 varchar(20), primary key(id)); 524insert into t1 values (1,5,10),(3,7,12),(4,5,2),(9,10,15),(2,2,2); 525insert into t2 values (9,"abc","def"),(5,"opq","lmn"),(2,"test t","t test"); 526select * from t1 order by i1; 527i1 i2 i3 5281 5 10 5292 2 2 5303 7 12 5314 5 2 5329 10 15 533select * from t2 order by id; 534id c1 c2 5352 test t t test 5365 opq lmn 5379 abc def 538update t1,t2 set t1.i2=15, t2.c2="ppc" where t1.i1=t2.id; 539select * from t1 order by i1; 540i1 i2 i3 5411 5 10 5422 15 2 5433 7 12 5444 5 2 5459 15 15 546select * from t2 order by id; 547id c1 c2 5482 test t ppc 5495 opq lmn 5509 abc ppc 551delete t1.*,t2.* from t1,t2 where t1.i2=t2.id; 552select * from t1 order by i1; 553i1 i2 i3 5542 15 2 5553 7 12 5569 15 15 557select * from t2 order by id; 558id c1 c2 5592 test t ppc 5609 abc ppc 561drop table t1, t2; 562# 563# Bug#49534: multitable IGNORE update with sql_safe_updates error 564# causes debug assertion 565# 566CREATE TABLE t1( a INT, KEY( a ) ); 567INSERT INTO t1 VALUES (1), (2), (3); 568SET SESSION sql_safe_updates = 1; 569# Must not cause failed assertion 570UPDATE IGNORE t1, t1 t1a SET t1.a = 1 WHERE t1a.a = 1; 571ERROR HY000: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column 572DROP TABLE t1; 573# 574# Bug#54543: update ignore with incorrect subquery leads to assertion 575# failure: inited==INDEX 576# 577SET SESSION sql_safe_updates = 0; 578CREATE TABLE t1 ( a INT ); 579INSERT INTO t1 VALUES (1), (2); 580CREATE TABLE t2 ( a INT ); 581INSERT INTO t2 VALUES (1), (2); 582CREATE TABLE t3 ( a INT ); 583INSERT INTO t3 VALUES (1), (2); 584# Should not crash 585UPDATE IGNORE 586( SELECT ( SELECT COUNT(*) FROM t1 GROUP BY a, @v ) a FROM t2 ) x, t3 587SET t3.a = 0; 588Warnings: 589Warning 1242 Subquery returns more than 1 row 590Warning 1242 Subquery returns more than 1 row 591DROP TABLE t1, t2, t3; 592SET SESSION sql_safe_updates = DEFAULT; 593# 594# Bug#52157 various crashes and assertions with multi-table update, stored function 595# 596CREATE FUNCTION f1 () RETURNS BLOB RETURN 1; 597CREATE TABLE t1 (f1 DATE); 598INSERT INTO t1 VALUES('2001-01-01'); 599UPDATE IGNORE (SELECT 1 FROM t1 WHERE f1 = (SELECT f1() FROM t1)) x, t1 SET f1 = 1; 600Warnings: 601Warning 1292 Truncated incorrect datetime value: '1' 602CREATE view v1 as SELECT f1() FROM t1; 603UPDATE IGNORE (SELECT 1 FROM t1 WHERE f1 = (select * from v1)) x, t1 SET f1 = 1; 604Warnings: 605Warning 1292 Truncated incorrect datetime value: '1' 606DROP VIEW v1; 607DROP FUNCTION f1; 608DROP TABLE t1; 609# 610# MDEV-4123: Incorrect results after multi-table update or 611# assertion `!table || (!table->read_set || 612# bitmap_is_set(table->read_set, field_index))' failure 613# 614DROP TABLE IF EXISTS t1; 615Warnings: 616Note 1051 Unknown table 'test.t1' 617CREATE TABLE t1 ( 618id int(10) unsigned NOT NULL, 619level tinyint(3) unsigned NOT NULL, 620PRIMARY KEY (id) 621); 622INSERT INTO t1 VALUES (2519583,1); 623DROP TABLE IF EXISTS t2; 624Warnings: 625Note 1051 Unknown table 'test.t2' 626CREATE TABLE t2 ( 627club_id int(11) NOT NULL DEFAULT '0', 628profile_id int(11) NOT NULL DEFAULT '0', 629member_level_id int(11) NOT NULL DEFAULT '0', 630PRIMARY KEY (profile_id,club_id) 631); 632INSERT INTO t2 VALUES (2,2519583,12); 633DROP TABLE IF EXISTS t3; 634Warnings: 635Note 1051 Unknown table 'test.t3' 636CREATE TABLE t3 ( 637member_level_id int(11) unsigned NOT NULL DEFAULT '0', 638map_level int(11) unsigned NOT NULL DEFAULT '0', 639map_status int(11) unsigned NOT NULL DEFAULT '0', 640PRIMARY KEY (member_level_id) 641); 642INSERT INTO t3 VALUES (12,12,1); 643CREATE 644VIEW v1 AS 645select club_id,profile_id, 646map_level AS member_level_id,map_status AS member_status 647from (t2 tc join t3 map 648on(((tc.member_level_id = map.member_level_id) and 649(club_id = 2)))); 650select level, count(*) as cnt from t1 group by level; 651level cnt 6521 1 653UPDATE t1 c LEFT JOIN v1 t ON (c.id = t.profile_id AND t.club_id = 2) 654SET c.level = IF (t.member_status IS NULL, 1, IF (t.member_status = 1, 2,3)); 655select level, count(*) as cnt from t1 group by level; 656level cnt 6572 1 658drop view v1; 659drop table t1,t2,t3; 660end of tests 661# 662# BUG#57373: Multi update+InnoDB reports ER_KEY_NOT_FOUND if a 663# table is updated twice 664# 665CREATE TABLE t1( 666pk INT, 667a INT, 668PRIMARY KEY (pk) 669) ENGINE=MyISAM; 670INSERT INTO t1 VALUES (0,0); 671UPDATE t1 AS A, t1 AS B SET A.pk = 1, B.a = 2; 672 673# Should be (1,2) 674SELECT * FROM t1; 675pk a 6761 2 677DROP TABLE t1; 678# 679# BUG#11882110: UPDATE REPORTS ER_KEY_NOT_FOUND IF TABLE IS 680# UPDATED TWICE 681# 682CREATE TABLE t1 ( 683col_int_key int, 684pk int, 685col_int int, 686key(col_int_key), 687primary key (pk) 688) ENGINE=MyISAM; 689INSERT INTO t1 VALUES (1,2,3); 690 691CREATE TABLE t2 ( 692col_int_key int, 693pk_1 int, 694pk_2 int, 695col_int int, 696key(col_int_key), 697primary key (pk_1,pk_2) 698) ENGINE=MyISAM; 699INSERT INTO t2 VALUES (1,2,3,4); 700 701UPDATE t1 AS A NATURAL JOIN t1 B SET A.pk=5,B.pk=7; 702 703SELECT * FROM t1; 704col_int_key pk col_int 7051 7 3 706 707UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_1=5,B.pk_1=7; 708 709UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_2=10,B.pk_2=11; 710 711SELECT * FROM t2; 712col_int_key pk_1 pk_2 col_int 7131 7 11 4 714DROP TABLE t1,t2; 715# 716# MDEV-6139: UPDATE w/ join against MRG_MyISAM table with read-only 717# sub-table fails 718# MDEV-6193: Problems with multi-table updates that JOIN against 719# read-only table 720# 721CREATE TABLE t1 ( 722id int(10) unsigned, 723a int(11) 724) ENGINE=MyISAM; 725CREATE TABLE t3 ( 726id int(10) unsigned, 727b int(11) 728) ENGINE=MyISAM; 729CREATE TABLE t2 ( 730id int(10) unsigned, 731b int(11) 732) ENGINE=MRG_MyISAM UNION=(t3); 733FLUSH TABLES; 734update t1 join t2 using (id) set t1.a=t2.b; 735create view v2 as select * from t2; 736update t1 join v2 using (id) set t1.a=0; 737create view v1 as select * from t3; 738update t1 join v1 using (id) set t1.a=0; 739update t1 join INFORMATION_SCHEMA.CHARACTER_SETS on (id=MAXLEN) set t1.a=0; 740create view v3 as select t2.id, t3.b from t2 join t3 using(id); 741update t1 join v3 using (id) set t1.a=0; 742drop view v1, v2, v3; 743drop table t2, t3, t1; 744# 745# MDEV-7613: MariaDB 5.5.40 server crash on update table left join 746# with a view 747# 748CREATE TABLE `t1` ( 749`f1` varchar(6) COLLATE latin1_general_ci DEFAULT NULL, 750`f2` varchar(6) COLLATE latin1_general_ci DEFAULT NULL, 751`f3` varchar(7) COLLATE latin1_general_ci DEFAULT NULL, 752`f4` varchar(15) COLLATE latin1_general_ci DEFAULT NULL, 753`f5` datetime DEFAULT NULL, 754`f6` varchar(2) COLLATE latin1_general_ci DEFAULT NULL, 755`f7` varchar(2) COLLATE latin1_general_ci DEFAULT NULL, 756`ff1` int(1) DEFAULT NULL, 757`ff2` int(1) DEFAULT NULL, 758`ff3` int(1) DEFAULT NULL, 759`ff4` int(1) DEFAULT NULL, 760`ff5` int(1) DEFAULT NULL, 761`ff6` int(1) DEFAULT NULL, 762`ff7` int(1) DEFAULT NULL, 763`ff8` int(2) DEFAULT NULL, 764`ff9` int(1) DEFAULT NULL, 765`ff10` int(1) DEFAULT NULL, 766`ff11` int(1) DEFAULT NULL, 767`ff12` int(1) DEFAULT NULL, 768`ff13` int(1) DEFAULT NULL, 769`ff14` int(1) DEFAULT NULL, 770`ff15` int(1) DEFAULT NULL, 771`f8` varchar(70) COLLATE latin1_general_ci DEFAULT NULL, 772`f9` varchar(20) COLLATE latin1_general_ci DEFAULT NULL, 773`f10` varchar(50) COLLATE latin1_general_ci NOT NULL, 774`f11` varchar(50) COLLATE latin1_general_ci DEFAULT NULL, 775`f12` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, 776`f13` text COLLATE latin1_general_ci, 777`f14` time DEFAULT NULL, 778`f15` varchar(30) COLLATE latin1_general_ci DEFAULT NULL, 779`fg1` int(11) DEFAULT NULL, 780`fg2` int(11) DEFAULT NULL, 781`fg3` int(11) DEFAULT NULL, 782`fg4` int(11) DEFAULT NULL, 783`fg5` int(11) DEFAULT NULL, 784`fg6` int(11) DEFAULT NULL, 785`fg7` int(11) DEFAULT NULL, 786`fg9` int(11) DEFAULT NULL, 787`fg10` int(11) DEFAULT NULL, 788`fg11` int(11) DEFAULT NULL, 789`fg12` int(11) DEFAULT NULL, 790`fg13` int(11) DEFAULT NULL, 791`fg14` int(11) DEFAULT NULL, 792`fg15` int(11) DEFAULT NULL, 793`f16` double DEFAULT NULL, 794`f17` double DEFAULT NULL, 795`f18` int(11) DEFAULT NULL, 796`f19` int(11) DEFAULT NULL, 797`f20` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, 798`f21` int(11) DEFAULT NULL, 799`f22` int(11) DEFAULT NULL, 800`f23` int(11) DEFAULT NULL, 801`f24` double DEFAULT NULL, 802`f25` int(11) DEFAULT NULL, 803`f26` double DEFAULT NULL, 804`f27` int(11) DEFAULT NULL, 805`f28` int(11) DEFAULT NULL, 806`f29` double DEFAULT NULL, 807`f30` int(11) DEFAULT NULL, 808`f31` double DEFAULT NULL, 809`PZ` double DEFAULT NULL, 810`f32` varchar(50) COLLATE latin1_general_ci DEFAULT NULL, 811`f33` varchar(50) COLLATE latin1_general_ci DEFAULT NULL, 812`f34` varchar(50) COLLATE latin1_general_ci DEFAULT NULL, 813`f35` varchar(30) COLLATE latin1_general_ci DEFAULT NULL, 814`f36` varchar(20) COLLATE latin1_general_ci DEFAULT NULL, 815`f37` varchar(50) COLLATE latin1_general_ci DEFAULT NULL, 816`f20_2` varchar(20) COLLATE latin1_general_ci DEFAULT NULL, 817`f38` varchar(30) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Email = E-Mail / Whitemail = Brief', 818`insert_ts` timestamp NULL DEFAULT CURRENT_TIMESTAMP, 819PRIMARY KEY (`f10`), 820KEY `f5_f12` (`f5`,`f12`), 821KEY `f5_f20` (`f5`,`f20`), 822KEY `f5_f33` (`f5`,`f33`) 823) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=COMPACT; 824INSERT INTO `t1` VALUES ('2011/2','201105','2011/19','gstfbnfr','2011-05-06 82500: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 82600:00:00','gg','Ag',2,NULL,2,1,1,1,1,24,1,NULL,1,1,1,2,0,'gusschlifßlich 827zugg 828gflffonifrfn','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 82900:00:00','gg','Ag',4,5,5,4,5,5,5,NULL,4,5,1,1,1,4,0,'gusschlifßlich zugg 830gflffonifrfn','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 83100:00:00','gg','Ag',4,1,1,5,5,5,5,12,4,5,1,1,2,1,0,'gusschlifßlich zugg 832gflffonifrfn','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 833Bgckofficf 5','vb5','Nufchtfr, 834Iris','49','ggobilcogg','grfurt','Intfrn','ggggil',NULL),('2008/4','200812','2008/50','junghdro','2008-12-11 83500:00:00','Do','Ag',2,2,5,5,4,4,2,72,2,5,2,2,1,1,0,'gusschlifßlich zugg 836gflffonifrfn','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 837Bgckofficf 5','vb5','Junghfinrich, 838Dorothfg','49','ggobilcogg','grfurt','Intfrn','ggggil',NULL),('2008/4','200812','2008/50','fbflktj','2008-12-08 83900:00:00','gg','Ag',4,2,2,5,1,1,1,24,NULL,NULL,NULL,NULL,NULL,0,0,'Kgggfrg 840bzw. DigiCggg 841Funktion','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, 842Kgtjg','49','ggobilcogg','k.A.','gxtfrn','ggggil',NULL),('2008/4','200812','2008/50','gltggggri','2008-12-09 84300:00:00','Di','Ag',4,1,1,4,2,1,2,16,1,2,2,2,2,2,0,'gusschlifßlich zugg 844gflffonifrfn','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 84500:00:00','Di','Ag',2,2,2,2,1,1,2,12,2,2,2,1,1,2,0,'ggobilfs 846Intfrnft','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 847Bgckofficf 1','vb1','Mufllfr, 848ggbinf','49','ggobilcogg','grfurt','Intfrn','ggggil',NULL),('2008/4','200812','2008/50','wggnfg','2008-12-09 84900:00:00','Di','Ag',5,5,5,5,5,5,5,12,5,5,5,5,5,5,0,'gls grsgtz für 850Ffstnftz','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 851Bgckofficf 1','vb1','Wggnfr, 852Annftt','49','ggobilcogg','grfurt','Intfrn','ggggil',NULL),('2008/4','200812','2008/50','schubrbf','2008-12-10 85300:00:00','Mi','Ag',1,2,NULL,2,1,2,1,24,NULL,NULL,NULL,NULL,NULL,0,0,'Kgggfrg 854bzw. DigiCggg 855Funktion','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); 856CREATE TABLE `t2` ( 857`ft1` datetime DEFAULT NULL, 858`ft2` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', 859`ft3` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', 860`ft4` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT '', 861`ft5` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT '', 862`ft6` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, 863`ft6_2` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, 864`ft7` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, 865`ft8` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, 866`ft9` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, 867`ft10` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, 868PRIMARY KEY (`ft4`,`ft5`) 869) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; 870INSERT 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); 871CREATE TABLE `t3` ( 872`fe1` int(10) NOT NULL DEFAULT '0', 873`fe2` char(50) COLLATE latin1_general_ci DEFAULT 'nn', 874`f34` char(50) COLLATE latin1_general_ci DEFAULT NULL, 875`fe3` double DEFAULT NULL, 876`fe4` double DEFAULT NULL, 877`fe5` char(4) COLLATE latin1_general_ci DEFAULT NULL, 878`f32` char(50) COLLATE latin1_general_ci DEFAULT NULL, 879`fe6` int(3) DEFAULT '0', 880`fe7` char(1) COLLATE latin1_general_ci DEFAULT NULL, 881`ft6` char(50) COLLATE latin1_general_ci DEFAULT NULL, 882`f33` char(4) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'virtuelle f33s', 883`fe8` char(4) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'aus dem ADS', 884`f37` char(50) COLLATE latin1_general_ci DEFAULT NULL, 885`fe9` char(50) COLLATE latin1_general_ci DEFAULT NULL, 886`fe10` int(5) DEFAULT '0', 887`fe11` int(10) DEFAULT '0', 888`fe12` char(50) COLLATE latin1_general_ci DEFAULT NULL, 889`fe13` double DEFAULT NULL, 890`fe14` char(50) COLLATE latin1_general_ci DEFAULT NULL, 891`fe15` date DEFAULT NULL, 892`fe16` date DEFAULT NULL, 893`fe17` int(10) DEFAULT '0', 894`fe18` date NOT NULL DEFAULT '0000-00-00', 895`ft3` date NOT NULL DEFAULT '0000-00-00', 896PRIMARY KEY (`fe1`), 897KEY `fe2` (`fe2`,`fe18`,`ft3`), 898KEY `f33` (`f33`), 899KEY `fe8` (`fe8`) 900) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=COMPACT COMMENT='CustomerService und Outsourcer Userinformationen'; 901INSERT INTO `t3` VALUES (1,'aabggn','gab, glgna',0,NULL,NULL,'gxtgrn D gnd g 902gggsbgrg',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, 903Iggr',0,NULL,NULL,'gxtgrn D gnd g 904gggsbgrg',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, 905ghristggna',15182,1,'ja','ggshilfg gxtgrn 1',1,NULL,'gg 906galgs','ag1','ag1','grfgrt','0',11941,0,'0',0,'agsggschigdgn','2014-01-11',NULL,11802051,'1900-01-01','2010-06-10'),(8,'abgcrist','gbg, 907ghristggna',15182,1,'ja','Zgntralg gftgr galgs Bgtrgggng 1',1,NULL,'gg 908galgs','sb1','sb1','grfgrt','0',11941,0,'0',0,'agsggschigdgn','2014-01-11',NULL,11802051,'2010-07-01','2012-08-11'),(9,'abgcrist','gbg, 909ghristggna',15182,1,'ja','galgs Inbggnd 2',1,NULL,'gg 910galgs','si2','si2','grfgrt','0',11941,0,'0',0,'agsggschigdgn','2014-01-11',NULL,11802051,'2012-09-01','2014-01-11'),(10,'abgcgr','gbg, 911ggrnglgg',14962,1,NULL,'galgs Ogtbggnd 1',1,NULL,'gg 912galgs','sg1','sg1','grfgrt','0',12401,0,'abgcrn',1,NULL,NULL,NULL,11800647,'1900-01-01','2010-11-10'),(11,'abgcgr','gbg, 913ggrnglgg',14962,1,NULL,'galgs Ogtbggnd 1',1,NULL,'gg 914galgs','sg1','sg1','grfgrt','0',12401,0,'abgcrn',1,NULL,NULL,NULL,11800647,'2010-12-01','2011-08-11'),(12,'abgcgr','gbg, 915ggrnglgg',14962,1,NULL,'galgs Ogtbggnd 2',1,NULL,'gg 916galgs','sg2','sg2','grfgrt','0',12401,0,'abgcrn',1,NULL,NULL,NULL,11800647,'2011-09-01','2012-01-11'),(13,'abgcgr','gbg, 917ggrnglgg',14962,0.75,NULL,'galgs Ogtbggnd 2',1,NULL,'gg 918galgs','sg2','sg2','grfgrt','0',12401,0,'abgcrn',1,NULL,NULL,'2011-09-11',11800647,'2012-02-01','2011-08-11'),(14,'rgghrsgr','gbg, 919gigrid',14781,1,'ja','Fgrdgrgngsmanaggmgnt 1',1,NULL,'gg 920Zahlgng','fm1','fm1','grfgrt','0',12141,0,'0',1,NULL,NULL,NULL,11010781,'1900-01-01','2012-08-11'); 921CREATE ALGORITHM=MERGE 922DEFINER=`root`@`localhost` SQL SECURITY DEFINER 923VIEW `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); 924CREATE ALGORITHM=UNDEFINED 925DEFINER=`root`@`localhost` SQL SECURITY DEFINER 926VIEW `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`; 927CREATE ALGORITHM=UNDEFINED 928DEFINER=`root`@`localhost` SQL SECURITY DEFINER 929VIEW `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`; 930UPDATE 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'; 931#MDEV-8018: main.multi_update fails with --ps-protocol 932prepare 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'"; 933execute stmt1; 934execute stmt1; 935deallocate prepare stmt1; 936drop view v3,v2,v1; 937drop table t1,t2,t3; 938create table t1 (id int not null, v1 varchar(10) not null); 939insert into t1 values (1,1),(2,2); 940create table t2 (log varchar(10) not null); 941create trigger t1_after_update after update on t1 942for each row insert into t2 values ('triggered'); 943create user foo; 944grant 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'@'%'; 945set global read_only=1; 946connect a, localhost, foo; 947create temporary table temp_t1 (id int not null, update_me varchar(10)); 948insert into temp_t1 values (1,1),(2,2),(3,3); 949update temp_t1 left join t1 on temp_t1.id = t1.id set temp_t1.update_me = 'hello'; 950connection default; 951set global read_only = 0; 952create table t3 (id int not null); 953insert t3 values (2); 954update t1 left join t3 on t1.id = t3.id set t1.v1 = 'hello'; 955select * from t2; 956log 957triggered 958triggered 959drop table t1,t2, t3; 960drop user foo; 961create table t1 (a int, b int); 962create table t2 (c int, d int); 963insert t1 values (1,2),(3,4); 964insert t2 values (5,6),(7,8); 965create table t0 (x int); 966insert t0 values (11), (22); 967create trigger tr1 before update on t2 for each row insert t0 values (new.c); 968connect con1, localhost, root; 969lock table t0 write; 970connection default; 971update t1 join t2 on (a=c+4) set b=d; 972disconnect con1; 973drop table t1, t2, t0; 974create table t1 (a int, b varchar(50), c varchar(50)); 975insert t1 (a,b) values (1,'1'), (2,'2'), (3,'3'); 976create function f1() returns varchar(50) return 'result'; 977create trigger tr before update on t1 for each row set new.c = (select f1()); 978create table t2 select a, b from t1; 979update t1 join t2 using (a) set t1.b = t2.b; 980drop table t1, t2; 981drop function f1; 982# 983# end of 5.5 tests 984# 985# 986# MDEV-24823: Invalid multi-table update of view within SP 987# 988create table t1 (id int) engine=myisam; 989insert into t1 values (1),(2),(1); 990create table t2 (pk int, c0 int) engine=myisam; 991insert into t2 values (1,1), (2,3); 992create view v2 as select * from t2; 993create view v3 as select * from t2 where c0 < 3; 994create procedure sp0() update t1, v2 set v2.pk = 1 where v2.c0 = t1.c1; 995call sp0(); 996ERROR 42S22: Unknown column 't1.c1' in 'where clause' 997call sp0(); 998ERROR 42S22: Unknown column 't1.c1' in 'where clause' 999create procedure sp1() update (t1 join v2 on v2.c0 = t1.c1) set v2.pk = 1; 1000call sp1(); 1001ERROR 42S22: Unknown column 't1.c1' in 'on clause' 1002call sp1(); 1003ERROR 42S22: Unknown column 't1.c1' in 'on clause' 1004create procedure sp2() update (t1 join v3 on v3.c0 = t1.c1) set v3.pk = 1; 1005call sp2(); 1006ERROR 42S22: Unknown column 't1.c1' in 'on clause' 1007call sp2(); 1008ERROR 42S22: Unknown column 't1.c1' in 'on clause' 1009create procedure sp3() 1010update (t1 join v2 on v2.c0 = t1.id) set v2.c0 = v2.c0+1; 1011select * from t2; 1012pk c0 10131 1 10142 3 1015call sp3(); 1016select * from t2; 1017pk c0 10181 2 10192 3 1020call sp3(); 1021select * from t2; 1022pk c0 10231 3 10242 3 1025create procedure sp4() delete t1 from t1 join v2 on v2.c0 = t1.c1; 1026call sp4(); 1027ERROR 42S22: Unknown column 't1.c1' in 'on clause' 1028call sp4(); 1029ERROR 42S22: Unknown column 't1.c1' in 'on clause' 1030drop procedure sp0; 1031drop procedure sp1; 1032drop procedure sp2; 1033drop procedure sp3; 1034drop procedure sp4; 1035drop view v2,v3; 1036drop table t1,t2; 1037# End of 10.2 tests 1038create table t1 (c1 int, c3 int); 1039insert t1(c3) values (1), (2), (3), (4), (5), (6), (7), (8); 1040create table t2 select * from t1; 1041update t1, t2 set t1.c1=t2.c3 where t1.c3=t2.c3 order by t1.c3 limit 3; 1042select * from t1; 1043c1 c3 10441 1 10452 2 10463 3 1047NULL 4 1048NULL 5 1049NULL 6 1050NULL 7 1051NULL 8 1052update t1 set c1=NULL; 1053update t1, t2 set t1.c1=t2.c3 where t1.c3=t2.c3 order by t1.c3 desc limit 2; 1054select * from t1; 1055c1 c3 1056NULL 1 1057NULL 2 1058NULL 3 1059NULL 4 1060NULL 5 1061NULL 6 10627 7 10638 8 1064drop table t1, t2; 1065create table t1 (i int) engine=memory; 1066insert t1 values (1),(2); 1067create table t2 (f int) engine=myisam; 1068insert t2 values (1),(2); 1069explain update t1, t2 set f = 126 order by f limit 2; 1070id select_type table type possible_keys key key_len ref rows Extra 10711 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort 10721 SIMPLE t2 ALL NULL NULL NULL NULL 2 1073update t1, t2 set f = 126 order by f limit 2; 1074select * from t2; 1075f 1076126 10772 1078drop table t1, t2; 1079create table t0(a int); 1080insert t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1081create table t1 (a int, b int, c int, key(a)); 1082insert t1 select a,a,a from t0; 1083create table t2 as select * from t1; 1084create table t3 as select * from t1; 1085select * 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; 1086a b c a b c 10870 0 0 0 0 0 10881 1 1 1 1 1 10892 2 2 2 2 2 10903 3 3 3 3 3 10914 4 4 4 4 4 1092set optimizer_switch='firstmatch=off'; 1093explain 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; 1094id select_type table type possible_keys key key_len ref rows Extra 10951 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Using temporary; Using filesort 10961 PRIMARY t1 ref a a 5 test.t2.a 1 10971 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; Start temporary; End temporary 1098update 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; 1099select * from t2; 1100a b c 11010 0 1 11021 1 1 11032 2 1 11043 3 1 11054 4 1 11065 5 5 11076 6 6 11087 7 7 11098 8 8 11109 9 9 1111set optimizer_switch=default; 1112drop table t0,t1,t2,t3; 1113create table t0 (x int); 1114create table t1 (a int); 1115create table t2 (b int, c int default 0); 1116insert t0 (x) values (0),(10); 1117insert t1 (a) values (1), (2); 1118insert t2 (b) values (1), (2); 1119create view v1 as select t2.b,t2.c from t1, t2 1120where t1.a=t2.b and t2.b < 3 with check option; 1121select * from t0 join v1 on (x=c); 1122x b c 11230 1 0 11240 2 0 1125explain update v1,t0 set c=1 where b=1 and x=c order by x,b limit 1; 1126id select_type table type possible_keys key key_len ref rows Extra 11271 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort 11281 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where 11291 SIMPLE t0 ALL NULL NULL NULL NULL 2 Using where 1130update v1,t0 set c=1 where b<3 and x=c order by x,b limit 1; 1131select * from v1; 1132b c 11331 1 11342 0 1135drop view v1; 1136drop table t0, t1,t2; 1137# 1138# MDEV-20515 multi-update tries to position updated table by null reference 1139# 1140create or replace table t1 (a int); 1141insert into t1 values (0), (2); 1142create or replace table t2 (b int); 1143insert into t2 values (1), (2); 1144select * from t1 left join t2 on a = b order by b; 1145a b 11460 NULL 11472 2 1148update t1 left join t2 on a = b set b= 3 order by b; 1149select * from t2; 1150b 11511 11523 1153drop tables t1, t2; 1154# 1155# MDEV-22464 Server crash on UPDATE with nested subquery 1156# 1157create table t1 (a int) ; 1158insert into t1 (a) values (1),(2),(3) ; 1159select a from t1 where a= (select 2 from t1 having (a = 3)); 1160ERROR 21000: Subquery returns more than 1 row 1161update t1 set a= (select 2 from t1 having (a = 3)); 1162ERROR 21000: Subquery returns more than 1 row 1163drop tables t1; 1164