1call mtr.add_suppression("Sort aborted.*"); 2drop table if exists t1,t2,t3,t11,t12; 3CREATE TABLE t1 (a tinyint(3), b tinyint(5)); 4INSERT INTO t1 VALUES (1,1); 5INSERT LOW_PRIORITY INTO t1 VALUES (1,2); 6INSERT INTO t1 VALUES (1,3); 7DELETE from t1 where a=1 limit 1; 8DELETE LOW_PRIORITY from t1 where a=1; 9INSERT INTO t1 VALUES (1,1); 10DELETE from t1; 11LOCK TABLE t1 write; 12INSERT INTO t1 VALUES (1,2); 13DELETE from t1; 14UNLOCK TABLES; 15INSERT INTO t1 VALUES (1,2); 16SET AUTOCOMMIT=0; 17DELETE from t1; 18SET AUTOCOMMIT=1; 19drop table t1; 20create table t1 ( 21a bigint not null, 22b bigint not null default 0, 23c bigint not null default 0, 24d bigint not null default 0, 25e bigint not null default 0, 26f bigint not null default 0, 27g bigint not null default 0, 28h bigint not null default 0, 29i bigint not null default 0, 30j bigint not null default 0, 31primary key (a,b,c,d,e,f,g,h,i,j)); 32insert into t1 (a) values (2),(4),(6),(8),(10),(12),(14),(16),(18),(20),(22),(24),(26),(23); 33delete from t1 where a=26; 34drop table t1; 35create table t1 ( 36a bigint not null, 37b bigint not null default 0, 38c bigint not null default 0, 39d bigint not null default 0, 40e bigint not null default 0, 41f bigint not null default 0, 42g bigint not null default 0, 43h bigint not null default 0, 44i bigint not null default 0, 45j bigint not null default 0, 46primary key (a,b,c,d,e,f,g,h,i,j)); 47insert into t1 (a) values (2),(4),(6),(8),(10),(12),(14),(16),(18),(20),(22),(24),(26),(23),(27); 48delete from t1 where a=27; 49drop table t1; 50CREATE TABLE `t1` ( 51`i` int(10) NOT NULL default '0', 52`i2` int(10) NOT NULL default '0', 53PRIMARY KEY (`i`) 54); 55DELETE FROM t1 USING t1 WHERE post='1'; 56ERROR 42S22: Unknown column 'post' in 'where clause' 57drop table t1; 58CREATE TABLE t1 ( 59bool char(0) default NULL, 60not_null varchar(20) binary NOT NULL default '', 61misc integer not null, 62PRIMARY KEY (not_null) 63) ENGINE=MyISAM; 64INSERT INTO t1 VALUES (NULL,'a',4), (NULL,'b',5), (NULL,'c',6), (NULL,'d',7); 65select * from t1 where misc > 5 and bool is null; 66bool not_null misc 67NULL c 6 68NULL d 7 69delete from t1 where misc > 5 and bool is null; 70select * from t1 where misc > 5 and bool is null; 71bool not_null misc 72select count(*) from t1; 73count(*) 742 75delete from t1 where 1 > 2; 76select count(*) from t1; 77count(*) 782 79delete from t1 where 3 > 2; 80select count(*) from t1; 81count(*) 820 83drop table t1; 84create table t1 (a int not null auto_increment primary key, b char(32)); 85insert into t1 (b) values ('apple'), ('apple'); 86select * from t1; 87a b 881 apple 892 apple 90delete t1 from t1, t1 as t2 where t1.b = t2.b and t1.a > t2.a; 91select * from t1; 92a b 931 apple 94drop table t1; 95create table t11 (a int NOT NULL, b int, primary key (a)); 96create table t12 (a int NOT NULL, b int, primary key (a)); 97create table t2 (a int NOT NULL, b int, primary key (a)); 98insert into t11 values (0, 10),(1, 11),(2, 12); 99insert into t12 values (33, 10),(0, 11),(2, 12); 100insert into t2 values (1, 21),(2, 12),(3, 23); 101select * from t11; 102a b 1030 10 1041 11 1052 12 106select * from t12; 107a b 10833 10 1090 11 1102 12 111select * from t2; 112a b 1131 21 1142 12 1153 23 116delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a); 117ERROR 21000: Subquery returns more than 1 row 118select * from t11; 119a b 1200 10 1211 11 1222 12 123select * from t12; 124a b 12533 10 1260 11 1272 12 128delete ignore t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a); 129Warnings: 130Warning 1242 Subquery returns more than 1 row 131Warning 1242 Subquery returns more than 1 row 132select * from t11; 133a b 1340 10 1351 11 136select * from t12; 137a b 13833 10 1390 11 140insert into t11 values (2, 12); 141delete from t11 where t11.b <> (select b from t2 where t11.a < t2.a); 142ERROR 21000: Subquery returns more than 1 row 143select * from t11; 144a b 1450 10 1461 11 1472 12 148delete ignore from t11 where t11.b <> (select b from t2 where t11.a < t2.a); 149Warnings: 150Warning 1242 Subquery returns more than 1 row 151Warning 1242 Subquery returns more than 1 row 152select * from t11; 153a b 1540 10 1551 11 156drop table t11, t12, t2; 157create table t1 (a int, b int, unique key (a), key (b)); 158insert into t1 values (3, 3), (7, 7); 159delete t1 from t1 where a = 3; 160check table t1; 161Table Op Msg_type Msg_text 162test.t1 check status OK 163select * from t1; 164a b 1657 7 166drop table t1; 167CREATE TABLE t1 ( a int PRIMARY KEY ); 168DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a; 169INSERT INTO t1 VALUES (0),(1),(2); 170DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a LIMIT 1; 171SELECT * FROM t1; 172a 1730 1742 175DROP TABLE t1; 176create table t1 (a int); 177delete `4.t1` from t1 as `4.t1` where `4.t1`.a = 5; 178delete FROM `4.t1` USING t1 as `4.t1` where `4.t1`.a = 5; 179drop table t1; 180create table t1(f1 int primary key); 181insert into t1 values (4),(3),(1),(2); 182delete from t1 where (@a:= f1) order by f1 limit 1; 183select @a; 184@a 1851 186drop table t1; 187CREATE TABLE t1 ( 188`date` date , 189`time` time , 190`seq` int(10) unsigned NOT NULL auto_increment, 191PRIMARY KEY (`seq`), 192KEY `seq` (`seq`), 193KEY `time` (`time`), 194KEY `date` (`date`) 195); 196DELETE FROM t1 ORDER BY date ASC, time ASC LIMIT 1; 197drop table t1; 198End of 4.1 tests 199CREATE TABLE t1 (a int not null,b int not null); 200CREATE TABLE t2 (a int not null, b int not null, primary key (a,b)); 201CREATE TABLE t3 (a int not null, b int not null, primary key (a,b)); 202insert into t1 values (1,1),(2,1),(1,3); 203insert into t2 values (1,1),(2,2),(3,3); 204insert into t3 values (1,1),(2,1),(1,3); 205select * from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b; 206a b a b a b 2071 1 1 1 1 1 2082 1 2 2 2 1 2091 3 1 1 1 3 210explain select * from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b; 211id select_type table type possible_keys key key_len ref rows Extra 2121 SIMPLE t1 ALL NULL NULL NULL NULL 3 2131 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a 1 Using index 2141 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t2.b,test.t1.b 1 Using index 215delete t2.*,t3.* from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b; 216select * from t3; 217a b 218drop table t1,t2,t3; 219create table t1(a date not null); 220insert into t1 values (0); 221select * from t1 where a is null; 222a 2230000-00-00 224delete from t1 where a is null; 225select count(*) from t1; 226count(*) 2270 228drop table t1; 229CREATE TABLE t1 (a INT); 230INSERT INTO t1 VALUES (1); 231DELETE FROM t1 ORDER BY x; 232ERROR 42S22: Unknown column 'x' in 'order clause' 233DELETE FROM t1 ORDER BY t2.x; 234ERROR 42S22: Unknown column 't2.x' in 'order clause' 235DELETE FROM t1 ORDER BY (SELECT x); 236ERROR 42S22: Unknown column 'x' in 'field list' 237DROP TABLE t1; 238CREATE TABLE t1 ( 239a INT 240); 241CREATE TABLE t2 ( 242a INT 243); 244CREATE DATABASE db1; 245CREATE TABLE db1.t1 ( 246a INT 247); 248INSERT INTO db1.t1 (a) SELECT * FROM t1; 249CREATE DATABASE db2; 250CREATE TABLE db2.t1 ( 251a INT 252); 253INSERT INTO db2.t1 (a) SELECT * FROM t2; 254DELETE FROM t1 alias USING t1, t2 alias WHERE t1.a = alias.a; 255ERROR 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 'alias USING t1, t2 alias WHERE t1.a = alias.a' at line 1 256DELETE FROM alias USING t1, t2 alias WHERE t1.a = alias.a; 257DELETE FROM t1, alias USING t1, t2 alias WHERE t1.a = alias.a; 258DELETE FROM t1, t2 USING t1, t2 alias WHERE t1.a = alias.a; 259ERROR 42S02: Unknown table 't2' in MULTI DELETE 260DELETE FROM db1.t1 alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a; 261ERROR 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 'alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a' at line 1 262DELETE FROM alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a; 263DELETE FROM db2.alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a; 264ERROR 42S02: Unknown table 'alias' in MULTI DELETE 265DELETE FROM t1 USING t1 WHERE a = 1; 266SELECT * FROM t1; 267a 268DELETE FROM t1 alias USING t1 alias WHERE a = 2; 269ERROR 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 'alias USING t1 alias WHERE a = 2' at line 1 270SELECT * FROM t1; 271a 272DROP TABLE t1, t2; 273DROP DATABASE db1; 274DROP DATABASE db2; 275CREATE FUNCTION f1() RETURNS INT RETURN 1; 276CREATE TABLE t1 (a INT); 277INSERT INTO t1 VALUES (0); 278DELETE FROM t1 ORDER BY (f1(10)) LIMIT 1; 279ERROR 42000: Incorrect number of arguments for FUNCTION test.f1; expected 0, got 1 280DROP TABLE t1; 281DROP FUNCTION f1; 282# 283# Bug #49552 : sql_buffer_result cause crash + not found records 284# in multitable delete/subquery 285# 286CREATE TABLE t1(a INT); 287INSERT INTO t1 VALUES (1),(2),(3); 288SET SESSION SQL_BUFFER_RESULT=1; 289DELETE t1 FROM (SELECT SUM(a) a FROM t1) x,t1; 290SET SESSION SQL_BUFFER_RESULT=DEFAULT; 291SELECT * FROM t1; 292a 293DROP TABLE t1; 294End of 5.0 tests 295DROP DATABASE IF EXISTS db1; 296DROP DATABASE IF EXISTS db2; 297DROP DATABASE IF EXISTS db3; 298DROP DATABASE IF EXISTS db4; 299DROP TABLE IF EXISTS t1, t2; 300DROP PROCEDURE IF EXISTS count; 301USE test; 302CREATE DATABASE db1; 303CREATE DATABASE db2; 304CREATE TABLE db1.t1 (a INT, b INT); 305INSERT INTO db1.t1 VALUES (1,1),(2,2),(3,3); 306CREATE TABLE db1.t2 AS SELECT * FROM db1.t1; 307CREATE TABLE db2.t1 AS SELECT * FROM db1.t2; 308CREATE TABLE db2.t2 AS SELECT * FROM db2.t1; 309CREATE TABLE t1 AS SELECT * FROM db2.t2; 310CREATE TABLE t2 AS SELECT * FROM t1; 311CREATE PROCEDURE count_rows() 312BEGIN 313SELECT COUNT(*) AS "COUNT(db1.t1)" FROM db1.t1; 314SELECT COUNT(*) AS "COUNT(db1.t2)" FROM db1.t2; 315SELECT COUNT(*) AS "COUNT(db2.t1)" FROM db2.t1; 316SELECT COUNT(*) AS "COUNT(db2.t2)" FROM db2.t2; 317SELECT COUNT(*) AS "COUNT(test.t1)" FROM test.t1; 318SELECT COUNT(*) AS "COUNT(test.t2)" FROM test.t2; 319END| 320CREATE DATABASE db3; 321USE db3; 322DROP DATABASE db3; 323SELECT * FROM t1; 324ERROR 3D000: No database selected 325DELETE a1,a2 FROM db1.t1, db2.t2; 326ERROR 3D000: No database selected 327DELETE a1,a2 FROM db1.t1, db2.t2; 328ERROR 3D000: No database selected 329DELETE a1,a2 FROM db1.t1 AS a1, db2.t2; 330ERROR 3D000: No database selected 331DELETE a1,a2 FROM db1.t1, db2.t2 AS a2; 332ERROR 3D000: No database selected 333DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2; 334ERROR 3D000: No database selected 335DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2; 336ERROR 3D000: No database selected 337DELETE FROM a1,a2 USING db1.t1, db2.t2; 338ERROR 3D000: No database selected 339DELETE FROM a1,a2 USING db1.t1, db2.t2; 340ERROR 3D000: No database selected 341DELETE FROM a1,a2 USING db1.t1 AS a1, db2.t2; 342ERROR 3D000: No database selected 343DELETE FROM a1,a2 USING db1.t1, db2.t2 AS a2; 344ERROR 3D000: No database selected 345DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2; 346ERROR 3D000: No database selected 347DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2; 348ERROR 3D000: No database selected 349DELETE a1 FROM db1.t1 AS a1, db2.t2 AS a1; 350ERROR 3D000: No database selected 351DELETE a1 FROM db1.a1, db2.t2 AS a1; 352ERROR 3D000: No database selected 353DELETE a1 FROM a1, db1.t1 AS a1; 354ERROR 3D000: No database selected 355DELETE t1 FROM db1.t1, db2.t1 AS a1; 356ERROR 3D000: No database selected 357DELETE t1 FROM db1.t1 AS a1, db2.t1 AS a2; 358ERROR 3D000: No database selected 359DELETE t1 FROM db1.t1, db2.t1; 360ERROR 3D000: No database selected 361USE test; 362DELETE a1,a2 FROM db1.t1, db2.t2; 363ERROR 42S02: Unknown table 'a1' in MULTI DELETE 364DELETE a1,a2 FROM db1.t1, db2.t2; 365ERROR 42S02: Unknown table 'a1' in MULTI DELETE 366DELETE a1,a2 FROM db1.t1 AS a1, db2.t2; 367ERROR 42S02: Unknown table 'a2' in MULTI DELETE 368DELETE a1,a2 FROM db1.t1, db2.t2 AS a2; 369ERROR 42S02: Unknown table 'a1' in MULTI DELETE 370DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2; 371ERROR 42S02: Table 'db3.t1' doesn't exist 372DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2; 373ERROR 42S02: Table 'db3.t1' doesn't exist 374DELETE FROM a1,a2 USING db1.t1, db2.t2; 375ERROR 42S02: Unknown table 'a1' in MULTI DELETE 376DELETE FROM a1,a2 USING db1.t1, db2.t2; 377ERROR 42S02: Unknown table 'a1' in MULTI DELETE 378DELETE FROM a1,a2 USING db1.t1 AS a1, db2.t2; 379ERROR 42S02: Unknown table 'a2' in MULTI DELETE 380DELETE FROM a1,a2 USING db1.t1, db2.t2 AS a2; 381ERROR 42S02: Unknown table 'a1' in MULTI DELETE 382DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2; 383ERROR 42S02: Table 'db3.t1' doesn't exist 384DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2; 385ERROR 42S02: Table 'db3.t1' doesn't exist 386DELETE a1 FROM db1.t1 AS a1, db2.t2 AS a1; 387ERROR 42000: Not unique table/alias: 'a1' 388DELETE a1 FROM db1.a1, db2.t2 AS a1; 389ERROR 42S02: Table 'db1.a1' doesn't exist 390DELETE a1 FROM a1, db1.t1 AS a1; 391ERROR 42000: Not unique table/alias: 'a1' 392DELETE t1 FROM db1.t1, db2.t1 AS a1; 393ERROR 42S02: Unknown table 't1' in MULTI DELETE 394DELETE t1 FROM db1.t1 AS a1, db2.t1 AS a2; 395ERROR 42S02: Unknown table 't1' in MULTI DELETE 396DELETE t1 FROM db1.t1, db2.t1; 397ERROR 42S02: Unknown table 't1' in MULTI DELETE 398DELETE t1 FROM db1.t2 AS t1, db2.t2 AS t2 WHERE t2.a = 1 AND t1.a = t2.a; 399SELECT ROW_COUNT(); 400ROW_COUNT() 4011 402CALL count_rows(); 403COUNT(db1.t1) 4043 405COUNT(db1.t2) 4062 407COUNT(db2.t1) 4083 409COUNT(db2.t2) 4103 411COUNT(test.t1) 4123 413COUNT(test.t2) 4143 415DELETE a1, a2 FROM db2.t1 AS a1, t2 AS a2 WHERE a1.a = 2 AND a2.a = 2; 416SELECT ROW_COUNT(); 417ROW_COUNT() 4182 419CALL count_rows(); 420COUNT(db1.t1) 4213 422COUNT(db1.t2) 4232 424COUNT(db2.t1) 4252 426COUNT(db2.t2) 4273 428COUNT(test.t1) 4293 430COUNT(test.t2) 4312 432DROP DATABASE db1; 433DROP DATABASE db2; 434DROP PROCEDURE count_rows; 435DROP TABLE t1, t2; 436# 437# Bug#46958: Assertion in Diagnostics_area::set_ok_status, trigger, 438# merge table 439# 440CREATE TABLE t1 ( a INT ); 441CREATE TABLE t2 ( a INT ); 442CREATE TABLE t3 ( a INT ); 443INSERT INTO t1 VALUES (1), (2); 444INSERT INTO t2 VALUES (1), (2); 445INSERT INTO t3 VALUES (1), (2); 446CREATE TRIGGER tr1 BEFORE DELETE ON t2 447FOR EACH ROW INSERT INTO no_such_table VALUES (1); 448DELETE t1, t2, t3 FROM t1, t2, t3; 449ERROR 42S02: Table 'test.no_such_table' doesn't exist 450SELECT * FROM t1; 451a 452SELECT * FROM t2; 453a 4541 4552 456SELECT * FROM t3; 457a 4581 4592 460DROP TABLE t1, t2, t3; 461CREATE TABLE t1 ( a INT ); 462CREATE TABLE t2 ( a INT ); 463CREATE TABLE t3 ( a INT ); 464INSERT INTO t1 VALUES (1), (2); 465INSERT INTO t2 VALUES (1), (2); 466INSERT INTO t3 VALUES (1), (2); 467CREATE TRIGGER tr1 AFTER DELETE ON t2 468FOR EACH ROW INSERT INTO no_such_table VALUES (1); 469DELETE t1, t2, t3 FROM t1, t2, t3; 470ERROR 42S02: Table 'test.no_such_table' doesn't exist 471SELECT * FROM t1; 472a 473SELECT * FROM t2; 474a 4752 476SELECT * FROM t3; 477a 4781 4792 480DROP TABLE t1, t2, t3; 481# 482# Bug #46425 crash in Diagnostics_area::set_ok_status, 483# empty statement, DELETE IGNORE 484# 485CREATE table t1 (i INTEGER); 486INSERT INTO t1 VALUES (1); 487CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW 488BEGIN 489INSERT INTO t1 SELECT * FROM t1 AS A; 490END | 491DELETE IGNORE FROM t1; 492ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger 493DROP TABLE t1; 494# 495# Bug #53450: Crash/assertion 496# "virtual int ha_myisam::index_first(uchar*)") at assert.c:81 497# 498CREATE TABLE t1 (a INT, b INT, c INT, 499INDEX(a), INDEX(b), INDEX(c)); 500INSERT INTO t1 VALUES (1,2,3), (4,5,6), (7,8,9); 501DELETE FROM t1 WHERE a = 10 OR b = 20 ORDER BY c LIMIT 1; 502DROP TABLE t1; 503# 504# Bug #53034: Multiple-table DELETE statements not accepting 505# "Access compatibility" syntax 506# 507CREATE TABLE t1 (id INT); 508CREATE TABLE t2 LIKE t1; 509CREATE TABLE t3 LIKE t1; 510DELETE FROM t1.*, test.t2.*, a.* USING t1, t2, t3 AS a; 511DROP TABLE t1, t2, t3; 512End of 5.1 tests 513# 514# Bug#51099 Assertion in mysql_multi_delete_prepare() 515# 516DROP TABLE IF EXISTS t1, t2; 517DROP VIEW IF EXISTS v1, v2; 518CREATE TABLE t1(a INT); 519CREATE TABLE t2(b INT); 520CREATE VIEW v1 AS SELECT a, b FROM t1, t2; 521CREATE VIEW v2 AS SELECT a FROM v1; 522DELETE FROM v2; 523ERROR HY000: Can not delete from join view 'test.v2' 524DELETE v2 FROM v2; 525ERROR HY000: Can not delete from join view 'test.v2' 526DROP VIEW v2, v1; 527DROP TABLE t1, t2; 528