1# The include statement below is a temp one for tests that are yet to 2#be ported to run with InnoDB, 3#but needs to be kept for tests that would need MyISAM in future. 4--source include/force_myisam_default.inc 5 6# 7# Check for problems with delete 8# 9 10--disable_warnings 11drop table if exists t1,t2,t3,t11,t12; 12--enable_warnings 13CREATE TABLE t1 (a tinyint(3), b tinyint(5)); 14INSERT INTO t1 VALUES (1,1); 15INSERT LOW_PRIORITY INTO t1 VALUES (1,2); 16INSERT INTO t1 VALUES (1,3); 17DELETE from t1 where a=1 limit 1; 18DELETE LOW_PRIORITY from t1 where a=1; 19 20INSERT INTO t1 VALUES (1,1); 21DELETE from t1; 22LOCK TABLE t1 write; 23INSERT INTO t1 VALUES (1,2); 24DELETE from t1; 25UNLOCK TABLES; 26INSERT INTO t1 VALUES (1,2); 27SET AUTOCOMMIT=0; 28DELETE from t1; 29SET AUTOCOMMIT=1; 30drop table t1; 31 32# 33# Test of delete when the delete will cause a node to disappear and reappear 34# (This assumes a block size of 1024) 35# 36 37create table t1 ( 38 a bigint not null, 39 b bigint not null default 0, 40 c bigint not null default 0, 41 d bigint not null default 0, 42 e bigint not null default 0, 43 f bigint not null default 0, 44 g bigint not null default 0, 45 h bigint not null default 0, 46 i bigint not null default 0, 47 j bigint not null default 0, 48 primary key (a,b,c,d,e,f,g,h,i,j)); 49insert into t1 (a) values (2),(4),(6),(8),(10),(12),(14),(16),(18),(20),(22),(24),(26),(23); 50delete from t1 where a=26; 51drop table t1; 52create table t1 ( 53 a bigint not null, 54 b bigint not null default 0, 55 c bigint not null default 0, 56 d bigint not null default 0, 57 e bigint not null default 0, 58 f bigint not null default 0, 59 g bigint not null default 0, 60 h bigint not null default 0, 61 i bigint not null default 0, 62 j bigint not null default 0, 63 primary key (a,b,c,d,e,f,g,h,i,j)); 64insert into t1 (a) values (2),(4),(6),(8),(10),(12),(14),(16),(18),(20),(22),(24),(26),(23),(27); 65delete from t1 where a=27; 66drop table t1; 67 68CREATE TABLE `t1` ( 69 `i` int(10) NOT NULL default '0', 70 `i2` int(10) NOT NULL default '0', 71 PRIMARY KEY (`i`) 72); 73-- error 1054 74DELETE FROM t1 USING t1 WHERE post='1'; 75drop table t1; 76 77# 78# CHAR(0) bug - not actually DELETE bug, but anyway... 79# 80 81CREATE TABLE t1 ( 82 bool char(0) default NULL, 83 not_null varchar(20) binary NOT NULL default '', 84 misc integer not null, 85 PRIMARY KEY (not_null) 86) ENGINE=MyISAM; 87 88INSERT INTO t1 VALUES (NULL,'a',4), (NULL,'b',5), (NULL,'c',6), (NULL,'d',7); 89 90select * from t1 where misc > 5 and bool is null; 91delete from t1 where misc > 5 and bool is null; 92select * from t1 where misc > 5 and bool is null; 93 94select count(*) from t1; 95delete from t1 where 1 > 2; 96select count(*) from t1; 97delete from t1 where 3 > 2; 98select count(*) from t1; 99 100drop table t1; 101# 102# Bug #5733: Table handler error with self-join multi-table DELETE 103# 104 105create table t1 (a int not null auto_increment primary key, b char(32)); 106insert into t1 (b) values ('apple'), ('apple'); 107select * from t1; 108delete t1 from t1, t1 as t2 where t1.b = t2.b and t1.a > t2.a; 109select * from t1; 110drop table t1; 111 112# 113# IGNORE option 114# 115create table t11 (a int NOT NULL, b int, primary key (a)); 116create table t12 (a int NOT NULL, b int, primary key (a)); 117create table t2 (a int NOT NULL, b int, primary key (a)); 118insert into t11 values (0, 10),(1, 11),(2, 12); 119insert into t12 values (33, 10),(0, 11),(2, 12); 120insert into t2 values (1, 21),(2, 12),(3, 23); 121select * from t11; 122select * from t12; 123select * from t2; 124-- error 1242 125delete 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; 128delete ignore t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a); 129select * from t11; 130select * from t12; 131insert into t11 values (2, 12); 132-- error 1242 133delete from t11 where t11.b <> (select b from t2 where t11.a < t2.a); 134select * from t11; 135delete ignore from t11 where t11.b <> (select b from t2 where t11.a < t2.a); 136select * from t11; 137drop table t11, t12, t2; 138 139--echo # sql_safe_updates mode with multi-table DELETE 140 141CREATE TABLE t1(a INTEGER PRIMARY KEY); 142INSERT INTO t1 VALUES(10),(20); 143 144CREATE TABLE t2(b INTEGER); 145INSERT INTO t2 VALUES(10),(20); 146 147SET SESSION sql_safe_updates=1; 148 149EXPLAIN DELETE t2 FROM t1 JOIN t2 WHERE t1.a = 10; 150-- error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE 151DELETE t2 FROM t1 JOIN t2 WHERE t1.a = 10; 152 153SET SESSION sql_safe_updates=default; 154 155DROP TABLE t1, t2; 156 157# 158# Bug #4198: deletion and KEYREAD 159# 160 161create table t1 (a int, b int, unique key (a), key (b)); 162insert into t1 values (3, 3), (7, 7); 163delete t1 from t1 where a = 3; 164check table t1; 165select * from t1; 166drop table t1; 167 168# 169# Bug #8392: delete with ORDER BY containing a direct reference to the table 170# 171 172CREATE TABLE t1 ( a int PRIMARY KEY ); 173DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a; 174INSERT INTO t1 VALUES (0),(1),(2); 175DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a LIMIT 1; 176SELECT * FROM t1; 177DROP TABLE t1; 178 179# 180# Bug #21392: multi-table delete with alias table name fails with 181# 1003: Incorrect table name 182# 183 184create table t1 (a int); 185delete `4.t1` from t1 as `4.t1` where `4.t1`.a = 5; 186delete FROM `4.t1` USING t1 as `4.t1` where `4.t1`.a = 5; 187drop table t1; 188 189# 190# Bug#17711: DELETE doesn't use index when ORDER BY, LIMIT and 191# non-restricting WHERE is present. 192# 193create table t1(f1 int primary key); 194insert into t1 values (4),(3),(1),(2); 195delete from t1 where (@a:= f1) order by f1 limit 1; 196select @a; 197drop table t1; 198 199# BUG#30385 "Server crash when deleting with order by and limit" 200CREATE TABLE t1 ( 201 `date` date , 202 `time` time , 203 `seq` int(10) unsigned NOT NULL auto_increment, 204 PRIMARY KEY (`seq`), 205 KEY `seq` (`seq`), 206 KEY `time` (`time`), 207 KEY `date` (`date`) 208); 209DELETE FROM t1 ORDER BY date ASC, time ASC LIMIT 1; 210drop table t1; 211 212--echo End of 4.1 tests 213 214# 215# Test of multi-delete where we are not scanning the first table 216# 217 218CREATE TABLE t1 (a int not null,b int not null); 219CREATE TABLE t2 (a int not null, b int not null, primary key (a,b)); 220CREATE TABLE t3 (a int not null, b int not null, primary key (a,b)); 221insert into t1 values (1,1),(2,1),(1,3); 222insert into t2 values (1,1),(2,2),(3,3); 223insert into t3 values (1,1),(2,1),(1,3); 224select * from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b; 225explain select * from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b; 226delete t2.*,t3.* from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b; 227# This should be empty 228select * from t3; 229drop table t1,t2,t3; 230 231# 232# Bug #8143: deleting '0000-00-00' values using IS NULL 233# 234 235create table t1(a date not null); 236insert ignore into t1 values (0); 237select * from t1 where a is null; 238delete from t1 where a is null; 239select count(*) from t1; 240drop table t1; 241 242# 243# Bug #26186: delete order by, sometimes accept unknown column 244# 245CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1); 246 247--error ER_BAD_FIELD_ERROR 248DELETE FROM t1 ORDER BY x; 249 250# even columns from a table not used in query (and not even existing) 251--error ER_BAD_FIELD_ERROR 252DELETE FROM t1 ORDER BY t2.x; 253 254# subquery (as long as the subquery from is valid or DUAL) 255--error ER_BAD_FIELD_ERROR 256DELETE FROM t1 ORDER BY (SELECT x); 257 258DROP TABLE t1; 259 260# 261# Bug #30234: Unexpected behavior using DELETE with AS and USING 262# ' 263CREATE TABLE t1 ( 264 a INT 265); 266 267CREATE TABLE t2 ( 268 a INT 269); 270 271CREATE DATABASE db1; 272CREATE TABLE db1.t1 ( 273 a INT 274); 275INSERT INTO db1.t1 (a) SELECT * FROM t1; 276 277CREATE DATABASE db2; 278CREATE TABLE db2.t1 ( 279 a INT 280); 281INSERT INTO db2.t1 (a) SELECT * FROM t2; 282 283--error ER_PARSE_ERROR 284DELETE FROM t1 alias USING t1, t2 alias WHERE t1.a = alias.a; 285DELETE FROM alias USING t1, t2 alias WHERE t1.a = alias.a; 286DELETE FROM t1, alias USING t1, t2 alias WHERE t1.a = alias.a; 287--error ER_UNKNOWN_TABLE 288DELETE FROM t1, t2 USING t1, t2 alias WHERE t1.a = alias.a; 289--error ER_PARSE_ERROR 290DELETE FROM db1.t1 alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a; 291DELETE FROM alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a; 292--error ER_UNKNOWN_TABLE 293DELETE FROM db2.alias USING db1.t1, db2.t1 alias WHERE db1.t1.a = alias.a; 294DELETE FROM t1 USING t1 WHERE a = 1; 295SELECT * FROM t1; 296--error ER_PARSE_ERROR 297DELETE FROM t1 alias USING t1 alias WHERE a = 2; 298SELECT * FROM t1; 299 300DROP TABLE t1, t2; 301DROP DATABASE db1; 302DROP DATABASE db2; 303 304# 305# Bug 31742: delete from ... order by function call that causes an error, 306# asserts server 307# 308 309CREATE FUNCTION f1() RETURNS INT RETURN 1; 310CREATE TABLE t1 (a INT); 311INSERT INTO t1 VALUES (0); 312--error 1318 313DELETE FROM t1 ORDER BY (f1(10)) LIMIT 1; 314DROP TABLE t1; 315DROP FUNCTION f1; 316 317 318--echo # 319--echo # Bug #49552 : sql_buffer_result cause crash + not found records 320--echo # in multitable delete/subquery 321--echo # 322 323CREATE TABLE t1(a INT); 324INSERT INTO t1 VALUES (1),(2),(3); 325SET SESSION SQL_BUFFER_RESULT=1; 326DELETE t1 FROM (SELECT SUM(a) a FROM t1) x,t1; 327 328SET SESSION SQL_BUFFER_RESULT=DEFAULT; 329SELECT * FROM t1; 330DROP TABLE t1; 331 332--echo End of 5.0 tests 333 334# 335# Bug#27525: table not found when using multi-table-deletes with aliases over 336# several databas 337# Bug#21148: MULTI-DELETE fails to resolve a table by alias if it is from a 338# different database 339# 340 341--disable_warnings 342DROP DATABASE IF EXISTS db1; 343DROP DATABASE IF EXISTS db2; 344DROP DATABASE IF EXISTS db3; 345DROP DATABASE IF EXISTS db4; 346DROP TABLE IF EXISTS t1, t2; 347DROP PROCEDURE IF EXISTS count; 348--enable_warnings 349USE test; 350CREATE DATABASE db1; 351CREATE DATABASE db2; 352 353CREATE TABLE db1.t1 (a INT, b INT); 354INSERT INTO db1.t1 VALUES (1,1),(2,2),(3,3); 355CREATE TABLE db1.t2 AS SELECT * FROM db1.t1; 356CREATE TABLE db2.t1 AS SELECT * FROM db1.t2; 357CREATE TABLE db2.t2 AS SELECT * FROM db2.t1; 358CREATE TABLE t1 AS SELECT * FROM db2.t2; 359CREATE TABLE t2 AS SELECT * FROM t1; 360 361delimiter |; 362CREATE PROCEDURE count_rows() 363BEGIN 364 SELECT COUNT(*) AS "COUNT(db1.t1)" FROM db1.t1; 365 SELECT COUNT(*) AS "COUNT(db1.t2)" FROM db1.t2; 366 SELECT COUNT(*) AS "COUNT(db2.t1)" FROM db2.t1; 367 SELECT COUNT(*) AS "COUNT(db2.t2)" FROM db2.t2; 368 SELECT COUNT(*) AS "COUNT(test.t1)" FROM test.t1; 369 SELECT COUNT(*) AS "COUNT(test.t2)" FROM test.t2; 370END| 371delimiter ;| 372 373# 374# Testing without a selected database 375# 376 377CREATE DATABASE db3; 378USE db3; 379DROP DATABASE db3; 380--error ER_NO_DB_ERROR 381SELECT * FROM t1; 382 383# Detect missing table references 384 385--error ER_NO_DB_ERROR 386DELETE a1,a2 FROM db1.t1, db2.t2; 387--error ER_NO_DB_ERROR 388DELETE a1,a2 FROM db1.t1, db2.t2; 389--error ER_NO_DB_ERROR 390DELETE a1,a2 FROM db1.t1 AS a1, db2.t2; 391--error ER_NO_DB_ERROR 392DELETE a1,a2 FROM db1.t1, db2.t2 AS a2; 393--error ER_NO_DB_ERROR 394DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2; 395--error ER_NO_DB_ERROR 396DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2; 397 398--error ER_NO_DB_ERROR 399DELETE FROM a1,a2 USING db1.t1, db2.t2; 400--error ER_NO_DB_ERROR 401DELETE FROM a1,a2 USING db1.t1, db2.t2; 402--error ER_NO_DB_ERROR 403DELETE FROM a1,a2 USING db1.t1 AS a1, db2.t2; 404--error ER_NO_DB_ERROR 405DELETE FROM a1,a2 USING db1.t1, db2.t2 AS a2; 406--error ER_NO_DB_ERROR 407DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2; 408--error ER_NO_DB_ERROR 409DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2; 410 411# Ambiguous table references 412 413--error ER_NO_DB_ERROR 414DELETE a1 FROM db1.t1 AS a1, db2.t2 AS a1; 415--error ER_NO_DB_ERROR 416DELETE a1 FROM db1.a1, db2.t2 AS a1; 417--error ER_NO_DB_ERROR 418DELETE a1 FROM a1, db1.t1 AS a1; 419--error ER_NO_DB_ERROR 420DELETE t1 FROM db1.t1, db2.t1 AS a1; 421--error ER_NO_DB_ERROR 422DELETE t1 FROM db1.t1 AS a1, db2.t1 AS a2; 423--error ER_NO_DB_ERROR 424DELETE t1 FROM db1.t1, db2.t1; 425 426# Test all again, now with a selected database 427 428USE test; 429 430# Detect missing table references 431 432--error ER_UNKNOWN_TABLE 433DELETE a1,a2 FROM db1.t1, db2.t2; 434--error ER_UNKNOWN_TABLE 435DELETE a1,a2 FROM db1.t1, db2.t2; 436--error ER_UNKNOWN_TABLE 437DELETE a1,a2 FROM db1.t1 AS a1, db2.t2; 438--error ER_UNKNOWN_TABLE 439DELETE a1,a2 FROM db1.t1, db2.t2 AS a2; 440--error ER_NO_SUCH_TABLE 441DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2; 442--error ER_NO_SUCH_TABLE 443DELETE a1,a2 FROM db3.t1 AS a1, db4.t2 AS a2; 444 445--error ER_UNKNOWN_TABLE 446DELETE FROM a1,a2 USING db1.t1, db2.t2; 447--error ER_UNKNOWN_TABLE 448DELETE FROM a1,a2 USING db1.t1, db2.t2; 449--error ER_UNKNOWN_TABLE 450DELETE FROM a1,a2 USING db1.t1 AS a1, db2.t2; 451--error ER_UNKNOWN_TABLE 452DELETE FROM a1,a2 USING db1.t1, db2.t2 AS a2; 453--error ER_NO_SUCH_TABLE 454DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2; 455--error ER_NO_SUCH_TABLE 456DELETE FROM a1,a2 USING db3.t1 AS a1, db4.t2 AS a2; 457 458# Ambiguous table references 459 460--error ER_NONUNIQ_TABLE 461DELETE a1 FROM db1.t1 AS a1, db2.t2 AS a1; 462--error ER_NO_SUCH_TABLE 463DELETE a1 FROM db1.a1, db2.t2 AS a1; 464--error ER_NONUNIQ_TABLE 465DELETE a1 FROM a1, db1.t1 AS a1; 466--error ER_UNKNOWN_TABLE 467DELETE t1 FROM db1.t1, db2.t1 AS a1; 468--error ER_UNKNOWN_TABLE 469DELETE t1 FROM db1.t1 AS a1, db2.t1 AS a2; 470--error ER_UNKNOWN_TABLE 471DELETE t1 FROM db1.t1, db2.t1; 472 473# Test multiple-table cross database deletes 474 475DELETE t1 FROM db1.t2 AS t1, db2.t2 AS t2 WHERE t2.a = 1 AND t1.a = t2.a; 476SELECT ROW_COUNT(); 477CALL count_rows(); 478DELETE a1, a2 FROM db2.t1 AS a1, t2 AS a2 WHERE a1.a = 2 AND a2.a = 2; 479SELECT ROW_COUNT(); 480CALL count_rows(); 481 482DROP DATABASE db1; 483DROP DATABASE db2; 484DROP PROCEDURE count_rows; 485DROP TABLE t1, t2; 486 487--echo # 488--echo # Bug#46958: Assertion in Diagnostics_area::set_ok_status, trigger, 489--echo # merge table 490--echo # 491CREATE TABLE t1 ( a INT ); 492CREATE TABLE t2 ( a INT ); 493CREATE TABLE t3 ( a INT ); 494 495INSERT INTO t1 VALUES (1), (2); 496INSERT INTO t2 VALUES (1), (2); 497INSERT INTO t3 VALUES (1), (2); 498 499CREATE TRIGGER tr1 BEFORE DELETE ON t2 500FOR EACH ROW INSERT INTO no_such_table VALUES (1); 501 502--error ER_NO_SUCH_TABLE 503DELETE t1, t2, t3 FROM t1, t2, t3; 504 505SELECT * FROM t1; 506SELECT * FROM t2; 507SELECT * FROM t3; 508 509DROP TABLE t1, t2, t3; 510 511CREATE TABLE t1 ( a INT ); 512CREATE TABLE t2 ( a INT ); 513CREATE TABLE t3 ( a INT ); 514 515INSERT INTO t1 VALUES (1), (2); 516INSERT INTO t2 VALUES (1), (2); 517INSERT INTO t3 VALUES (1), (2); 518 519CREATE TRIGGER tr1 AFTER DELETE ON t2 520FOR EACH ROW INSERT INTO no_such_table VALUES (1); 521 522--error ER_NO_SUCH_TABLE 523DELETE t1, t2, t3 FROM t1, t2, t3; 524 525SELECT * FROM t1; 526SELECT * FROM t2; 527SELECT * FROM t3; 528 529DROP TABLE t1, t2, t3; 530 531--echo # 532--echo # Bug #46425 crash in Diagnostics_area::set_ok_status, 533--echo # empty statement, DELETE IGNORE 534--echo # 535 536CREATE table t1 (i INTEGER); 537 538INSERT INTO t1 VALUES (1); 539 540--delimiter | 541 542CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW 543BEGIN 544 INSERT INTO t1 SELECT * FROM t1 AS A; 545END | 546 547--delimiter ; 548--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG 549DELETE IGNORE FROM t1; 550 551DROP TABLE t1; 552 553--echo # 554--echo # Bug #53450: Crash/assertion 555--echo # "virtual int ha_myisam::index_first(uchar*)") at assert.c:81 556--echo # 557 558CREATE TABLE t1 (a INT, b INT, c INT, 559 INDEX(a), INDEX(b), INDEX(c)); 560INSERT INTO t1 VALUES (1,2,3), (4,5,6), (7,8,9); 561 562DELETE FROM t1 WHERE a = 10 OR b = 20 ORDER BY c LIMIT 1; 563 564DROP TABLE t1; 565 566--echo # 567--echo # Bug #53034: Multiple-table DELETE statements not accepting 568--echo # "Access compatibility" syntax 569--echo # 570 571CREATE TABLE t1 (id INT); 572CREATE TABLE t2 LIKE t1; 573CREATE TABLE t3 LIKE t1; 574 575DELETE FROM t1.*, test.t2.*, a.* USING t1, t2, t3 AS a; 576 577DROP TABLE t1, t2, t3; 578 579--echo End of 5.1 tests 580 581 582--echo # 583--echo # Bug#51099 Assertion in mysql_multi_delete_prepare() 584--echo # 585 586--disable_warnings 587DROP TABLE IF EXISTS t1, t2; 588DROP VIEW IF EXISTS v1, v2; 589--enable_warnings 590 591CREATE TABLE t1(a INT); 592CREATE TABLE t2(b INT); 593CREATE VIEW v1 AS SELECT a, b FROM t1, t2; 594CREATE VIEW v2 AS SELECT a FROM v1; 595 596# This is a normal delete 597--error ER_VIEW_DELETE_MERGE_VIEW 598DELETE FROM v2; 599# This is a multi table delete, check that we get the same error 600# This caused the assertion. 601--error ER_VIEW_DELETE_MERGE_VIEW 602DELETE v2 FROM v2; 603 604DROP VIEW v2, v1; 605DROP TABLE t1, t2; 606 607 608--echo # 609--echo # Bug#58709 assert in mysql_execute_command 610--echo # 611 612--disable_warnings 613DROP TABLE IF EXISTS t2, t1; 614DROP PROCEDURE IF EXISTS p1; 615--enable_warnings 616 617CREATE TABLE t1 (i INT PRIMARY KEY) engine=InnoDB; 618CREATE TABLE t2 (i INT, FOREIGN KEY (i) REFERENCES t1 (i) ON DELETE NO ACTION) engine=InnoDB; 619 620INSERT INTO t1 VALUES (1); 621INSERT INTO t2 VALUES (1); 622 623DELETE IGNORE FROM t1 WHERE i = 1; 624 625CREATE PROCEDURE p1() DELETE IGNORE FROM t1 WHERE i = 1; 626# This triggered the assert 627CALL p1(); 628 629PREPARE stm FROM 'CALL p1()'; 630# This also triggered the assert 631EXECUTE stm; 632DEALLOCATE PREPARE stm; 633 634DROP TABLE t2, t1; 635DROP PROCEDURE p1; 636 637--echo # 638--echo # Bug#18345346 Assertion failed: table_ref->view && table_ref->table == 0 639--echo # 640 641CREATE TABLE b(a INTEGER); 642CREATE VIEW y AS SELECT 1 FROM b, b AS e; 643CREATE VIEW x AS SELECT 1 FROM y; 644CREATE VIEW z AS SELECT 1 FROM x LIMIT 1; 645 646--error ER_NON_UPDATABLE_TABLE 647DELETE z FROM (SELECT 1) AS x, z; 648 649DROP VIEW z, x, y; 650DROP TABLE b; 651 652--echo # Bug#11752648 : MULTI-DELETE IGNORE DOES NOT REPORT WARNINGS 653--echo # 654CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; 655INSERT INTO t1 (a) VALUES (1); 656CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB; 657INSERT INTO t2 (a) VALUES (1); 658CREATE TABLE t3 (a INT, b INT, CONSTRAINT c_a FOREIGN KEY (a) 659REFERENCES t1 (a), CONSTRAINT c_b FOREIGN KEY (b) REFERENCES t2 (a)) ENGINE=InnoDB; 660INSERT INTO t3 (a, b) VALUES (1, 1); 661DELETE IGNORE FROM t1; 662DELETE IGNORE t1,t2 FROM t1,t2; 663SELECT * FROM t1; 664SELECT * FROM t2; 665DROP TABLE t3,t2,t1; 666 667--echo # 668--echo # Bug#17550423 : DELETE IGNORE GIVES INCORRECT RESULT WITH FOREIGN KEY 669--echo # FOR PARENT TABLE 670--echo # 671CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; 672CREATE TABLE t2 (b INT) ENGINE=InnoDB; 673INSERT INTO t1 VALUES (1), (2), (5); 674INSERT INTO t2 VALUES (1), (5); 675--replace_regex /#sql-[[0-9a-f]*_[0-9a-f]*]*/#sql-temporary/ 676ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY(b) REFERENCES t1(a); 677SELECT * FROM t2; 678DELETE IGNORE FROM t1; 679SELECT * FROM t1; 680DROP TABLE t2,t1; 681 682--echo # 683--echo # Bug#17787664 : ASSERT `! IS_SET()` IN DIAGNOSTICS_AREA::SET_OK_STATUS 684--echo # ON ER_SUBQUERY_NO_1_ROW 685--echo # 686 687CREATE TABLE t1 (a INT) ENGINE=MyISAM; 688INSERT INTO t1 VALUES (1); 689 690CREATE TABLE t2 (b INT) ENGINE=MyISAM; 691INSERT INTO t2 VALUES (8); 692INSERT INTO t2 VALUES (24); 693 694CREATE TABLE t3 (c INT) ENGINE=MyISAM; 695INSERT INTO t3 VALUES (43); 696INSERT INTO t3 VALUES (1); 697 698#This statement triggers an assert in DEBUG build without the patch. 699--error ER_SUBQUERY_NO_1_ROW 700DELETE FROM OUTR1.* USING t1 AS OUTR1 701 LEFT OUTER JOIN t2 AS OUTR2 ON ( OUTR1.a = OUTR2.b ) 702WHERE OUTR1.a < ( SELECT t3.c from t3 where 1 XOR OUTR2.b IS NOT NULL); 703 704DROP TABLE t1,t2,t3; 705 706--echo # 707--echo # Bug#20460208: !table || (!table->read_set || bitmap_is_set) 708--echo # 709 710CREATE TABLE t1(a INTEGER) engine=innodb; 711CREATE TABLE t2(a BLOB) engine=innodb; 712--error ER_BAD_FIELD_ERROR 713INSERT INTO t1 SET z=1; 714INSERT INTO t2 VALUES('a'); 715DELETE FROM t2 WHERE 1 = a; 716DROP TABLE t1, t2; 717 718--echo # 719--echo # Bug#20086791 ASSERT `! IS_SET()` IN DIAGNOSTICS_AREA::SET_OK_STATUS 720--echo # ON DELETE (ER_SUBQUERY..) 721--echo # 722 723SET sql_mode=''; 724 725CREATE TABLE t1 ( 726 col_int_key int, 727 pk integer auto_increment, 728 col_datetime_key datetime, 729 /*Indices*/ 730 key (col_int_key ), 731 primary key (pk), 732 key (col_datetime_key ) 733) ENGINE=memory; 734 735CREATE TABLE t2 ( 736 col_varchar_key varchar (1), 737 col_date_key date, 738 pk integer auto_increment, 739 /*Indices*/ 740 key (col_varchar_key ), 741 key (col_date_key ), 742 primary key (pk) 743) ENGINE=memory; 744 745INSERT INTO t2 VALUES 746('v', '2002-05-01', NULL) , 747('d', '2001-01-01', NULL) 748; 749 750 751CREATE TABLE t3 ( 752 pk integer auto_increment, 753 col_int_key int, 754 col_varchar_key varchar (1), 755 /*Indices*/ 756 primary key (pk), 757 key (col_int_key ), 758 key (col_varchar_key )) 759ENGINE=memory; 760 761INSERT INTO t3 VALUES 762(NULL, 3, 'n') , 763(NULL, 1, 'r') 764; 765 766CREATE TABLE t4 ( 767 pk integer auto_increment, 768 col_varchar_key varchar (1), 769 col_int_key int, 770 /*Indices*/ 771 primary key (pk), 772 key (col_varchar_key ), 773 key (col_int_key ) 774) ENGINE=memory; 775 776CREATE TABLE t5 ( 777 col_datetime_key datetime, 778 pk integer auto_increment, 779 col_int_key int, 780 /*Indices*/ 781 key (col_datetime_key ), 782 primary key (pk), 783 key (col_int_key )) 784ENGINE=memory; 785 786INSERT INTO t5 VALUES 787('2007-10-01', NULL, 8) , 788('2002-10-01', NULL, 3) 789; 790 791DELETE OUTR1.* FROM t2 AS OUTR1 792JOIN t3 AS OUTR2 793ON ( OUTR1 . col_varchar_key = OUTR2 . col_varchar_key ) 794WHERE OUTR1 . col_varchar_key NOT IN 795 ( SELECT INNR1 . col_varchar_key AS y FROM t5 AS INNR2 796 RIGHT JOIN t4 AS INNR1 ON ( INNR2 . pk < INNR1 . col_int_key ) 797 WHERE INNR1 . col_int_key <= INNR1 . col_int_key 798 AND OUTR2 . col_int_key >= 3 ); 799 800DELETE QUICK 801FROM OUTR1.* USING t2 AS OUTR1 802LEFT OUTER JOIN t1 AS OUTR2 803ON ( OUTR1 . col_date_key = OUTR2 . col_datetime_key ) 804WHERE OUTR1 . pk NOT IN ( SELECT 2 UNION SELECT 7 ); 805 806--error ER_SUBQUERY_NO_1_ROW 807DELETE OUTR1.* 808FROM t2 AS OUTR1 809 LEFT JOIN t1 AS OUTR2 810 ON ( OUTR1 . pk = OUTR2 . col_int_key ) 811WHERE OUTR1 . pk <> ( 812 SELECT DISTINCT INNR1 . col_int_key AS y 813 FROM t5 AS INNR1 WHERE OUTR1 . pk <= 5 814 ORDER BY INNR1 . col_datetime_key 815); 816 817DROP TABLE t1, t2, t3, t4, t5; 818 819SET sql_mode=default; 820 821--echo # Bug#20450013 assertion 'select_lex::having_cond() == __null ... 822 823CREATE TABLE t1(a TEXT, FULLTEXT (a)); 824CREATE VIEW v1 AS SELECT a FROM t1 ORDER BY a; 825 826let $query= 827DELETE FROM t1 USING v1,t1; 828 829eval explain $query; 830eval $query; 831 832DROP VIEW v1; 833DROP TABLE t1; 834