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