1# 2# Check for problems with delete 3# 4 5--disable_warnings 6drop table if exists t1,t2,t3,t11,t12; 7--enable_warnings 8CREATE TABLE t1 (a tinyint(3), b tinyint(5)); 9INSERT INTO t1 VALUES (1,1); 10INSERT LOW_PRIORITY INTO t1 VALUES (1,2); 11INSERT INTO t1 VALUES (1,3); 12DELETE from t1 where a=1 limit 1; 13DELETE LOW_PRIORITY from t1 where a=1; 14 15INSERT INTO t1 VALUES (1,1); 16DELETE from t1; 17LOCK TABLE t1 write; 18INSERT INTO t1 VALUES (1,2); 19DELETE from t1; 20UNLOCK TABLES; 21INSERT INTO t1 VALUES (1,2); 22SET AUTOCOMMIT=0; 23DELETE from t1; 24SET AUTOCOMMIT=1; 25drop table t1; 26 27# 28# Test of delete when the delete will cause a node to disappear and reappear 29# (This assumes a block size of 1024) 30# 31 32create table t1 ( 33 a bigint not null, 34 b bigint not null default 0, 35 c bigint not null default 0, 36 d bigint not null default 0, 37 e bigint not null default 0, 38 f bigint not null default 0, 39 g bigint not null default 0, 40 h bigint not null default 0, 41 i bigint not null default 0, 42 j bigint not null default 0, 43 primary key (a,b,c,d,e,f,g,h,i,j)); 44insert into t1 (a) values (2),(4),(6),(8),(10),(12),(14),(16),(18),(20),(22),(24),(26),(23); 45delete from t1 where a=26; 46drop table t1; 47create table t1 ( 48 a bigint not null, 49 b bigint not null default 0, 50 c bigint not null default 0, 51 d bigint not null default 0, 52 e bigint not null default 0, 53 f bigint not null default 0, 54 g bigint not null default 0, 55 h bigint not null default 0, 56 i bigint not null default 0, 57 j bigint not null default 0, 58 primary key (a,b,c,d,e,f,g,h,i,j)); 59insert into t1 (a) values (2),(4),(6),(8),(10),(12),(14),(16),(18),(20),(22),(24),(26),(23),(27); 60delete from t1 where a=27; 61drop table t1; 62 63CREATE TABLE `t1` ( 64 `i` int(10) NOT NULL default '0', 65 `i2` int(10) NOT NULL default '0', 66 PRIMARY KEY (`i`) 67); 68-- error 1054 69DELETE FROM t1 USING t1 WHERE post='1'; 70drop table t1; 71 72# 73# CHAR(0) bug - not actually DELETE bug, but anyway... 74# 75 76CREATE TABLE t1 ( 77 bool char(0) default NULL, 78 not_null varchar(20) binary NOT NULL default '', 79 misc integer not null, 80 PRIMARY KEY (not_null) 81) ENGINE=MyISAM; 82 83INSERT INTO t1 VALUES (NULL,'a',4), (NULL,'b',5), (NULL,'c',6), (NULL,'d',7); 84 85select * from t1 where misc > 5 and bool is null; 86delete from t1 where misc > 5 and bool is null; 87select * from t1 where misc > 5 and bool is null; 88 89select count(*) from t1; 90delete from t1 where 1 > 2; 91select count(*) from t1; 92delete from t1 where 3 > 2; 93select count(*) from t1; 94 95drop table t1; 96# 97# Bug #5733: Table handler error with self-join multi-table DELETE 98# 99 100create table t1 (a int not null auto_increment primary key, b char(32)); 101insert into t1 (b) values ('apple'), ('apple'); 102select * from t1; 103delete t1 from t1, t1 as t2 where t1.b = t2.b and t1.a > t2.a; 104select * from t1; 105drop table t1; 106 107# 108# IGNORE option 109# 110create table t11 (a int NOT NULL, b int, primary key (a)); 111create table t12 (a int NOT NULL, b int, primary key (a)); 112create table t2 (a int NOT NULL, b int, primary key (a)); 113insert into t11 values (0, 10),(1, 11),(2, 12); 114insert into t12 values (33, 10),(0, 11),(2, 12); 115insert into t2 values (1, 21),(2, 12),(3, 23); 116select * from t11; 117select * from t12; 118select * from t2; 119-- error 1242 120delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a); 121select * from t11; 122select * from t12; 123delete ignore t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a); 124select * from t11; 125select * from t12; 126insert into t11 values (2, 12); 127-- error 1242 128delete from t11 where t11.b <> (select b from t2 where t11.a < t2.a); 129select * from t11; 130delete ignore from t11 where t11.b <> (select b from t2 where t11.a < t2.a); 131select * from t11; 132drop table t11, t12, t2; 133 134# 135# Bug #4198: deletion and KEYREAD 136# 137 138create table t1 (a int, b int, unique key (a), key (b)); 139insert into t1 values (3, 3), (7, 7); 140delete t1 from t1 where a = 3; 141check table t1; 142select * from t1; 143drop table t1; 144 145# 146# Bug #8392: delete with ORDER BY containing a direct reference to the table 147# 148 149CREATE TABLE t1 ( a int PRIMARY KEY ); 150DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a; 151INSERT INTO t1 VALUES (0),(1),(2); 152DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a LIMIT 1; 153SELECT * FROM t1; 154DROP TABLE t1; 155 156# 157# Bug #21392: multi-table delete with alias table name fails with 158# 1003: Incorrect table name 159# 160 161create table t1 (a int); 162delete `4.t1` from t1 as `4.t1` where `4.t1`.a = 5; 163delete FROM `4.t1` USING t1 as `4.t1` where `4.t1`.a = 5; 164drop table t1; 165 166# 167# Bug#17711: DELETE doesn't use index when ORDER BY, LIMIT and 168# non-restricting WHERE is present. 169# 170create table t1(f1 int primary key); 171insert into t1 values (4),(3),(1),(2); 172delete from t1 where (@a:= f1) order by f1 limit 1; 173select @a; 174drop table t1; 175 176# BUG#30385 "Server crash when deleting with order by and limit" 177CREATE TABLE t1 ( 178 `date` date , 179 `time` time , 180 `seq` int(10) unsigned NOT NULL auto_increment, 181 PRIMARY KEY (`seq`), 182 KEY `seq` (`seq`), 183 KEY `time` (`time`), 184 KEY `date` (`date`) 185); 186DELETE FROM t1 ORDER BY date ASC, time ASC LIMIT 1; 187drop table t1; 188 189--echo End of 4.1 tests 190 191# 192# Test of multi-delete where we are not scanning the first table 193# 194 195CREATE TABLE t1 (a int not null,b int not null); 196CREATE TABLE t2 (a int not null, b int not null, primary key (a,b)); 197CREATE TABLE t3 (a int not null, b int not null, primary key (a,b)); 198insert into t1 values (1,1),(2,1),(1,3); 199insert into t2 values (1,1),(2,2),(3,3); 200insert into t3 values (1,1),(2,1),(1,3); 201select * from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b; 202explain select * from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b; 203delete t2.*,t3.* from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b; 204# This should be empty 205select * from t3; 206drop table t1,t2,t3; 207 208# 209# Bug #8143: deleting '0000-00-00' values using IS NULL 210# 211 212create table t1(a date not null); 213insert into t1 values (0); 214select * from t1 where a is null; 215delete from t1 where a is null; 216select count(*) from t1; 217drop table t1; 218 219# 220# Bug #26186: delete order by, sometimes accept unknown column 221# 222CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1); 223 224--error ER_BAD_FIELD_ERROR 225DELETE FROM t1 ORDER BY x; 226 227# even columns from a table not used in query (and not even existing) 228--error ER_BAD_FIELD_ERROR 229DELETE FROM t1 ORDER BY t2.x; 230 231# subquery (as long as the subquery from is valid or DUAL) 232--error ER_BAD_FIELD_ERROR 233DELETE FROM t1 ORDER BY (SELECT x); 234 235DROP TABLE t1; 236 237# 238# Bug #30234: Unexpected behavior using DELETE with AS and USING 239# ' 240CREATE TABLE t1 ( 241 a INT 242); 243 244CREATE TABLE t2 ( 245 a INT 246); 247 248CREATE DATABASE db1; 249CREATE TABLE db1.t1 ( 250 a INT 251); 252INSERT INTO db1.t1 (a) SELECT * FROM t1; 253 254CREATE DATABASE db2; 255CREATE TABLE db2.t1 ( 256 a INT 257); 258INSERT INTO db2.t1 (a) SELECT * FROM t2; 259 260--error ER_PARSE_ERROR 261DELETE FROM t1 alias USING t1, t2 alias WHERE t1.a = alias.a; 262DELETE FROM alias USING t1, t2 alias WHERE t1.a = alias.a; 263DELETE FROM t1, alias USING t1, t2 alias WHERE t1.a = alias.a; 264--error ER_UNKNOWN_TABLE 265DELETE FROM t1, t2 USING t1, t2 alias WHERE t1.a = alias.a; 266--error ER_PARSE_ERROR 267DELETE FROM db1.t1 alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a; 268DELETE FROM alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a; 269--error ER_UNKNOWN_TABLE 270DELETE FROM db2.alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a; 271DELETE FROM t1 USING t1 WHERE a = 1; 272SELECT * FROM t1; 273--error ER_PARSE_ERROR 274DELETE FROM t1 alias USING t1 alias WHERE a = 2; 275SELECT * FROM t1; 276 277DROP TABLE t1, t2; 278DROP DATABASE db1; 279DROP DATABASE db2; 280 281# 282# Bug 31742: delete from ... order by function call that causes an error, 283# asserts server 284# 285 286CREATE FUNCTION f1() RETURNS INT RETURN 1; 287CREATE TABLE t1 (a INT); 288INSERT INTO t1 VALUES (0); 289--error 1318 290DELETE FROM t1 ORDER BY (f1(10)) LIMIT 1; 291DROP TABLE t1; 292DROP FUNCTION f1; 293 294 295--echo # 296--echo # Bug #49552 : sql_buffer_result cause crash + not found records 297--echo # in multitable delete/subquery 298--echo # 299 300CREATE TABLE t1(a INT); 301INSERT INTO t1 VALUES (1),(2),(3); 302SET SESSION SQL_BUFFER_RESULT=1; 303DELETE t1 FROM (SELECT SUM(a) a FROM t1) x,t1; 304 305SET SESSION SQL_BUFFER_RESULT=DEFAULT; 306SELECT * FROM t1; 307DROP TABLE t1; 308 309--echo End of 5.0 tests 310 311# 312# Bug#27525: table not found when using multi-table-deletes with aliases over 313# several databas 314# Bug#21148: MULTI-DELETE fails to resolve a table by alias if it's from a 315# different database 316# 317 318--disable_warnings 319DROP DATABASE IF EXISTS db1; 320DROP DATABASE IF EXISTS db2; 321DROP DATABASE IF EXISTS db3; 322DROP DATABASE IF EXISTS db4; 323DROP TABLE IF EXISTS t1, t2; 324DROP PROCEDURE IF EXISTS count; 325--enable_warnings 326USE test; 327CREATE DATABASE db1; 328CREATE DATABASE db2; 329 330CREATE TABLE db1.t1 (a INT, b INT); 331INSERT INTO db1.t1 VALUES (1,1),(2,2),(3,3); 332CREATE TABLE db1.t2 AS SELECT * FROM db1.t1; 333CREATE TABLE db2.t1 AS SELECT * FROM db1.t2; 334CREATE TABLE db2.t2 AS SELECT * FROM db2.t1; 335CREATE TABLE t1 AS SELECT * FROM db2.t2; 336CREATE TABLE t2 AS SELECT * FROM t1; 337 338delimiter |; 339CREATE PROCEDURE count_rows() 340BEGIN 341 SELECT COUNT(*) AS "COUNT(db1.t1)" FROM db1.t1; 342 SELECT COUNT(*) AS "COUNT(db1.t2)" FROM db1.t2; 343 SELECT COUNT(*) AS "COUNT(db2.t1)" FROM db2.t1; 344 SELECT COUNT(*) AS "COUNT(db2.t2)" FROM db2.t2; 345 SELECT COUNT(*) AS "COUNT(test.t1)" FROM test.t1; 346 SELECT COUNT(*) AS "COUNT(test.t2)" FROM test.t2; 347END| 348delimiter ;| 349 350# 351# Testing without a selected database 352# 353 354CREATE DATABASE db3; 355USE db3; 356DROP DATABASE db3; 357--error ER_NO_DB_ERROR 358SELECT * FROM t1; 359 360# Detect missing table references 361 362--error ER_NO_DB_ERROR 363DELETE a1,a2 FROM db1.t1, db2.t2; 364--error ER_NO_DB_ERROR 365DELETE a1,a2 FROM db1.t1, db2.t2; 366--error ER_NO_DB_ERROR 367DELETE a1,a2 FROM db1.t1 AS a1, db2.t2; 368--error ER_NO_DB_ERROR 369DELETE a1,a2 FROM db1.t1, db2.t2 AS a2; 370--error ER_NO_DB_ERROR 371DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2; 372--error ER_NO_DB_ERROR 373DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2; 374 375--error ER_NO_DB_ERROR 376DELETE FROM a1,a2 USING db1.t1, db2.t2; 377--error ER_NO_DB_ERROR 378DELETE FROM a1,a2 USING db1.t1, db2.t2; 379--error ER_NO_DB_ERROR 380DELETE FROM a1,a2 USING db1.t1 AS a1, db2.t2; 381--error ER_NO_DB_ERROR 382DELETE FROM a1,a2 USING db1.t1, db2.t2 AS a2; 383--error ER_NO_DB_ERROR 384DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2; 385--error ER_NO_DB_ERROR 386DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2; 387 388# Ambiguous table references 389 390--error ER_NO_DB_ERROR 391DELETE a1 FROM db1.t1 AS a1, db2.t2 AS a1; 392--error ER_NO_DB_ERROR 393DELETE a1 FROM db1.a1, db2.t2 AS a1; 394--error ER_NO_DB_ERROR 395DELETE a1 FROM a1, db1.t1 AS a1; 396--error ER_NO_DB_ERROR 397DELETE t1 FROM db1.t1, db2.t1 AS a1; 398--error ER_NO_DB_ERROR 399DELETE t1 FROM db1.t1 AS a1, db2.t1 AS a2; 400--error ER_NO_DB_ERROR 401DELETE t1 FROM db1.t1, db2.t1; 402 403# Test all again, now with a selected database 404 405USE test; 406 407# Detect missing table references 408 409--error ER_UNKNOWN_TABLE 410DELETE a1,a2 FROM db1.t1, db2.t2; 411--error ER_UNKNOWN_TABLE 412DELETE a1,a2 FROM db1.t1, db2.t2; 413--error ER_UNKNOWN_TABLE 414DELETE a1,a2 FROM db1.t1 AS a1, db2.t2; 415--error ER_UNKNOWN_TABLE 416DELETE a1,a2 FROM db1.t1, db2.t2 AS a2; 417--error ER_NO_SUCH_TABLE 418DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2; 419--error ER_NO_SUCH_TABLE 420DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2; 421 422--error ER_UNKNOWN_TABLE 423DELETE FROM a1,a2 USING db1.t1, db2.t2; 424--error ER_UNKNOWN_TABLE 425DELETE FROM a1,a2 USING db1.t1, db2.t2; 426--error ER_UNKNOWN_TABLE 427DELETE FROM a1,a2 USING db1.t1 AS a1, db2.t2; 428--error ER_UNKNOWN_TABLE 429DELETE FROM a1,a2 USING db1.t1, db2.t2 AS a2; 430--error ER_NO_SUCH_TABLE 431DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2; 432--error ER_NO_SUCH_TABLE 433DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2; 434 435# Ambiguous table references 436 437--error ER_NONUNIQ_TABLE 438DELETE a1 FROM db1.t1 AS a1, db2.t2 AS a1; 439--error ER_NO_SUCH_TABLE 440DELETE a1 FROM db1.a1, db2.t2 AS a1; 441--error ER_NONUNIQ_TABLE 442DELETE a1 FROM a1, db1.t1 AS a1; 443--error ER_UNKNOWN_TABLE 444DELETE t1 FROM db1.t1, db2.t1 AS a1; 445--error ER_UNKNOWN_TABLE 446DELETE t1 FROM db1.t1 AS a1, db2.t1 AS a2; 447--error ER_UNKNOWN_TABLE 448DELETE t1 FROM db1.t1, db2.t1; 449 450# Test multiple-table cross database deletes 451 452DELETE t1 FROM db1.t2 AS t1, db2.t2 AS t2 WHERE t2.a = 1 AND t1.a = t2.a; 453SELECT ROW_COUNT(); 454CALL count_rows(); 455DELETE a1, a2 FROM db2.t1 AS a1, t2 AS a2 WHERE a1.a = 2 AND a2.a = 2; 456SELECT ROW_COUNT(); 457CALL count_rows(); 458 459DROP DATABASE db1; 460DROP DATABASE db2; 461DROP PROCEDURE count_rows; 462DROP TABLE t1, t2; 463 464--echo # 465--echo # Bug#46958: Assertion in Diagnostics_area::set_ok_status, trigger, 466--echo # merge table 467--echo # 468CREATE TABLE t1 ( a INT ); 469CREATE TABLE t2 ( a INT ); 470CREATE TABLE t3 ( a INT ); 471 472INSERT INTO t1 VALUES (1), (2); 473INSERT INTO t2 VALUES (1), (2); 474INSERT INTO t3 VALUES (1), (2); 475 476CREATE TRIGGER tr1 BEFORE DELETE ON t2 477FOR EACH ROW INSERT INTO no_such_table VALUES (1); 478 479--error ER_NO_SUCH_TABLE 480DELETE t1, t2, t3 FROM t1, t2, t3; 481 482SELECT * FROM t1; 483SELECT * FROM t2; 484SELECT * FROM t3; 485 486DROP TABLE t1, t2, t3; 487 488CREATE TABLE t1 ( a INT ); 489CREATE TABLE t2 ( a INT ); 490CREATE TABLE t3 ( a INT ); 491 492INSERT INTO t1 VALUES (1), (2); 493INSERT INTO t2 VALUES (1), (2); 494INSERT INTO t3 VALUES (1), (2); 495 496CREATE TRIGGER tr1 AFTER DELETE ON t2 497FOR EACH ROW INSERT INTO no_such_table VALUES (1); 498 499--error ER_NO_SUCH_TABLE 500DELETE t1, t2, t3 FROM t1, t2, t3; 501 502SELECT * FROM t1; 503SELECT * FROM t2; 504SELECT * FROM t3; 505 506DROP TABLE t1, t2, t3; 507 508--echo # 509--echo # Bug #46425 crash in Diagnostics_area::set_ok_status, 510--echo # empty statement, DELETE IGNORE 511--echo # 512 513CREATE table t1 (i INTEGER); 514 515INSERT INTO t1 VALUES (1); 516 517--delimiter | 518 519CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW 520BEGIN 521 INSERT INTO t1 SELECT * FROM t1 AS A; 522END | 523 524--delimiter ; 525--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG 526DELETE IGNORE FROM t1; 527 528DROP TABLE t1; 529 530--echo # 531--echo # Bug #53450: Crash/assertion 532--echo # "virtual int ha_myisam::index_first(uchar*)") at assert.c:81 533--echo # 534 535CREATE TABLE t1 (a INT, b INT, c INT, 536 INDEX(a), INDEX(b), INDEX(c)); 537INSERT INTO t1 VALUES (1,2,3), (4,5,6), (7,8,9); 538 539DELETE FROM t1 WHERE a = 10 OR b = 20 ORDER BY c LIMIT 1; 540 541DROP TABLE t1; 542 543--echo # 544--echo # Bug #53034: Multiple-table DELETE statements not accepting 545--echo # "Access compatibility" syntax 546--echo # 547 548CREATE TABLE t1 (id INT); 549CREATE TABLE t2 LIKE t1; 550CREATE TABLE t3 LIKE t1; 551 552DELETE FROM t1.*, test.t2.*, a.* USING t1, t2, t3 AS a; 553 554DROP TABLE t1, t2, t3; 555 556--echo End of 5.1 tests 557 558 559--echo # 560--echo # Bug#51099 Assertion in mysql_multi_delete_prepare() 561--echo # 562 563--disable_warnings 564DROP TABLE IF EXISTS t1, t2; 565DROP VIEW IF EXISTS v1, v2; 566--enable_warnings 567 568CREATE TABLE t1(a INT); 569CREATE TABLE t2(b INT); 570CREATE VIEW v1 AS SELECT a, b FROM t1, t2; 571CREATE VIEW v2 AS SELECT a FROM v1; 572 573# This is a normal delete 574--error ER_VIEW_DELETE_MERGE_VIEW 575DELETE FROM v2; 576# This is a multi table delete, check that we get the same error 577# This caused the assertion. 578--error ER_VIEW_DELETE_MERGE_VIEW 579DELETE v2 FROM v2; 580 581DROP VIEW v2, v1; 582DROP TABLE t1, t2; 583 584 585--echo # 586--echo # Bug#58709 assert in mysql_execute_command 587--echo # 588 589--disable_warnings 590DROP TABLE IF EXISTS t2, t1; 591DROP PROCEDURE IF EXISTS p1; 592--enable_warnings 593 594CREATE TABLE t1 (i INT PRIMARY KEY) engine=InnoDB; 595CREATE TABLE t2 (i INT, FOREIGN KEY (i) REFERENCES t1 (i) ON DELETE NO ACTION) engine=InnoDB; 596 597INSERT INTO t1 VALUES (1); 598INSERT INTO t2 VALUES (1); 599 600DELETE IGNORE FROM t1 WHERE i = 1; 601 602CREATE PROCEDURE p1() DELETE IGNORE FROM t1 WHERE i = 1; 603# This triggered the assert 604CALL p1(); 605 606PREPARE stm FROM 'CALL p1()'; 607# This also triggered the assert 608EXECUTE stm; 609DEALLOCATE PREPARE stm; 610 611DROP TABLE t2, t1; 612DROP PROCEDURE p1; 613