1# This file is a collection of regression and coverage tests 2# for WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE. 3 4-- disable_query_log 5-- disable_result_log 6SET GLOBAL innodb_stats_persistent=0; 7-- enable_result_log 8-- enable_query_log 9 10set end_markers_in_json=on; 11 12--echo #1 13CREATE TABLE t1 (a INT); 14INSERT INTO t1 VALUES (1), (2), (3); 15--let $query = UPDATE t1 SET a = 10 WHERE a < 10 16--let $select = SELECT * FROM t1 WHERE a < 10 17--source include/explain_utils.inc 18DROP TABLE t1; 19 20--echo #2 21CREATE TABLE t1 (a INT); 22INSERT INTO t1 VALUES (1), (2), (3); 23--let $query = DELETE FROM t1 WHERE a < 10 24--let $select = SELECT * FROM t1 WHERE a < 10 25--source include/explain_utils.inc 26DROP TABLE t1; 27 28--echo #3 29CREATE TABLE t1 (a INT); 30INSERT INTO t1 VALUES (1), (2), (3); 31--let $query = DELETE FROM t1 USING t1 WHERE a = 1 32--let $select = SELECT * FROM t1 WHERE a = 1 33--source include/explain_utils.inc 34DROP TABLE t1; 35 36--echo #4 37CREATE TABLE t1 (a INT); 38INSERT INTO t1 VALUES (1), (2), (3); 39CREATE TABLE t2 (b INT); 40INSERT INTO t2 VALUES (1), (2), (3); 41--let $query = UPDATE t1, t2 SET t1.a = 10 WHERE t1.a = 1 42--let $select = SELECT * FROM t1, t2 WHERE t1.a = 1 43--source include/explain_utils.inc 44DROP TABLE t1, t2; 45 46--echo #5 47CREATE TABLE t1 (a INT); 48INSERT INTO t1 VALUES (1), (2), (3); 49CREATE TABLE t2 (b INT); 50INSERT INTO t2 VALUES (1), (2), (3); 51--let $query = UPDATE t1 t11, (SELECT * FROM t2) t12 SET t11.a = 10 WHERE t11.a = 1 52--let $select = SELECT * FROM t1 t11, (SELECT * FROM t2) t12 WHERE t11.a = 1 53--source include/explain_utils.inc 54DROP TABLE t1, t2; 55 56--echo #6 57CREATE TABLE t1 (a INT); 58INSERT INTO t1 VALUES (1), (2), (3); 59CREATE TABLE t2 (b INT); 60INSERT INTO t2 VALUES (1), (2), (3); 61--let $query = UPDATE t1 SET a = 10 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3) 62--let $select = SELECT * FROM t1 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3) 63--source include/explain_utils.inc 64DROP TABLE t1, t2; 65 66--echo #7 67CREATE TABLE t1 (a INT); 68INSERT INTO t1 VALUES (1), (2), (3); 69CREATE TABLE t2 (b INT); 70INSERT INTO t2 VALUES (1), (2), (3); 71--let $query = UPDATE t1 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3) 72--let $select = SELECT * FROM t1 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3) 73--source include/explain_utils.inc 74DROP TABLE t1, t2; 75 76--echo #7 77CREATE TABLE t1 (a INT); 78INSERT INTO t1 VALUES (1), (2), (3); 79CREATE TABLE t2 (b INT); 80INSERT INTO t2 VALUES (1), (2), (3); 81--let $query = UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3) 82--let $select = SELECT * FROM t1, t2 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3) 83--source include/explain_utils.inc 84DROP TABLE t1, t2; 85 86--echo #8 87CREATE TABLE t1 (a INT); 88INSERT INTO t1 VALUES (1), (2), (3); 89CREATE TABLE t2 (b INT); 90INSERT INTO t2 VALUES (1), (2), (3); 91--let $query = UPDATE t1 t11, (SELECT * FROM t2) t12 SET t11.a = t11.a + 10 92--let $select = SELECT * FROM t1 t11, (SELECT * FROM t2) t12 93--source include/explain_utils.inc 94DROP TABLE t1, t2; 95 96--echo #9 97CREATE TABLE t1 (a INT); 98INSERT INTO t1 VALUES (1), (2), (3); 99CREATE TABLE t2 (b INT); 100INSERT INTO t2 VALUES (1), (2), (3); 101--let $query = UPDATE t1 t11, (SELECT 1 FROM DUAL) t12 SET t11.a = t11.a + 10 102--let $select = SELECT * FROM t1 t11, (SELECT 1 FROM DUAL) t12 103--source include/explain_utils.inc 104DROP TABLE t1, t2; 105 106--echo #10 107CREATE TABLE t1 (a INT); 108INSERT INTO t1 VALUES (1), (2), (3); 109CREATE TABLE t2 (b INT); 110INSERT INTO t2 VALUES (1), (2), (3); 111--let $query = UPDATE t1 t11, (SELECT * FROM t2) t12 SET t11.a = 10 WHERE t11.a > 1 112--let $select = SELECT * FROM t1 t11, (SELECT * FROM t2) t12 WHERE t11.a > 1 113--source include/explain_utils.inc 114DROP TABLE t1, t2; 115 116--echo #11 117CREATE TABLE t1 (a INT); 118INSERT INTO t1 VALUES (1), (2), (3); 119--let $query = DELETE FROM t1 WHERE a > 1 LIMIT 1 120--let $select = SELECT * FROM t1 WHERE a > 1 LIMIT 1 121--source include/explain_utils.inc 122DROP TABLE t1; 123 124--echo #12 125CREATE TABLE t1 (a INT); 126INSERT INTO t1 VALUES (1), (2), (3); 127--let $query = DELETE FROM t1 WHERE 0 128--let $select = SELECT * FROM t1 WHERE 0 129--source include/explain_utils.inc 130DROP TABLE t1; 131 132--echo #13 133CREATE TABLE t1 (a INT); 134INSERT INTO t1 VALUES (1), (2), (3); 135--let $query = DELETE FROM t1 USING t1 WHERE 0 136--let $select = SELECT * FROM t1 WHERE 0 137--source include/explain_utils.inc 138DROP TABLE t1; 139 140--echo #14 141CREATE TABLE t1 (a INT, b INT, UNIQUE KEY (a), KEY (b)); 142INSERT INTO t1 VALUES (3, 3), (7, 7); 143--let $query = DELETE FROM t1 WHERE a = 3 144--let $select = SELECT * FROM t1 WHERE a = 3 145--source include/explain_utils.inc 146DROP TABLE t1; 147 148--echo #15 149CREATE TABLE t1 (a INT, b INT, UNIQUE KEY (a), KEY (b)); 150INSERT INTO t1 VALUES (3, 3), (7, 7); 151--let $query = DELETE FROM t1 WHERE a < 3 152--let $select = SELECT * FROM t1 WHERE a < 3 153--source include/explain_utils.inc 154DROP TABLE t1; 155 156--echo #16 157CREATE TABLE t1 ( a int PRIMARY KEY ); 158--let $query = DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a 159--let $select = SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a 160--source include/explain_utils.inc 161INSERT INTO t1 VALUES (1), (2), (3); 162--let $query = DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a 163--let $select = SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a 164--source include/explain_utils.inc 165DROP TABLE t1; 166 167--echo #17 168CREATE TABLE t1(a INT PRIMARY KEY); 169INSERT INTO t1 VALUES (4),(3),(1),(2); 170--let $query = DELETE FROM t1 WHERE (@a:= a) ORDER BY a LIMIT 1 171--let $select = SELECT * FROM t1 WHERE (@a:= a) ORDER BY a LIMIT 1 172--source include/explain_utils.inc 173DROP TABLE t1; 174 175--echo #18 176CREATE TABLE t1 (a DATE, b TIME, c INT, KEY c(c), KEY b(b), KEY a(a)); 177INSERT INTO t1 VALUES (), (), (), (), (), (), (), (), (), (); 178UPDATE t1 SET a = c, b = c; 179--let $query = DELETE FROM t1 ORDER BY a ASC, b ASC LIMIT 1 180--let $select = SELECT * FROM t1 ORDER BY a ASC, b ASC LIMIT 1 181--source include/explain_utils.inc 182DROP TABLE t1; 183 184--echo #19 185CREATE TABLE t1 (a1 INT NOT NULL, b1 INT NOT NULL); 186CREATE TABLE t2 (a2 INT NOT NULL, b2 INT NOT NULL, PRIMARY KEY (a2,b2)); 187CREATE TABLE t3 (a3 INT NOT NULL, b3 INT NOT NULL, PRIMARY KEY (a3,b3)); 188INSERT INTO t1 VALUES (1,1), (2,1), (1,3); 189INSERT INTO t2 VALUES (1,1), (2,2), (3,3); 190INSERT INTO t3 VALUES (1,1), (2,1), (1,3); 191# Need to run ANALYZE to get correct index statistics 192--disable_query_log 193--disable_result_log 194ANALYZE TABLE t1, t2, t3; 195--enable_result_log 196--enable_query_log 197--let $query = DELETE t1,t2,t3 FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3 198--let $select = SELECT * FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3 199--source include/explain_utils.inc 200DROP TABLE t1, t2, t3; 201 202--echo #20 203CREATE TABLE t1 (a INT); 204INSERT INTO t1 VALUES (1), (2), (3); 205CREATE TABLE t2 (a INT); 206INSERT INTO t2 VALUES (1), (2), (3); 207--let $query = UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2) 208--let $select = SELECT * FROM t1 WHERE a IN (SELECT a FROM t2) 209--source include/explain_utils.inc 210DROP TABLE t1, t2; 211 212--echo #21 213CREATE TABLE t1 (a1 INT); 214INSERT INTO t1 VALUES (1), (2), (3), (4), (5); 215CREATE TABLE t2 (a2 VARCHAR(10)); 216INSERT INTO t2 VALUES (1), (2), (3), (4), (5); 217SET @save_optimizer_switch= @@optimizer_switch; 218--disable_query_log 219if (`select locate('semijoin', @@optimizer_switch) > 0`) 220{ 221 SET @@optimizer_switch= 'semijoin=off'; 222} 223--enable_query_log 224--let $query = DELETE FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2) 225--let $select = SELECT * FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2) 226--source include/explain_utils.inc 227SET @@optimizer_switch= @save_optimizer_switch; 228TRUNCATE t1; 229INSERT INTO t1 VALUES (1), (2), (3), (4), (5); 230--let $query = DELETE FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2) 231--let $select = SELECT * FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2) 232--source include/explain_utils.inc 233DROP TABLE t1, t2; 234 235--echo #22 236CREATE TABLE t1 (i INT, j INT); 237INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); 238--let $query = UPDATE t1 SET i = 10 239--let $select = SELECT * FROM t1 240--source include/explain_utils.inc 241DROP TABLE t1; 242 243--echo #23 244CREATE TABLE t1 (i INT, j INT); 245INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); 246--let $query = DELETE FROM t1 247--let $select = SELECT * FROM t1 248--source include/explain_utils.inc 249DROP TABLE t1; 250 251--echo #24 252CREATE TABLE t1 (i INT); 253INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), 254 (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), 255 (30),(31),(32),(33),(34),(35); 256CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); 257INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; 258INSERT INTO t2 (a, b, c) SELECT t1.i, t1.i, t1.i FROM t1, t1 x1, t1 x2; 259--let $query = DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 260--let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 261--let $no_rows = 1 262--source include/explain_utils.inc 263DROP TABLE t1, t2; 264 265--echo #25 266CREATE TABLE t1 (i INT); 267INSERT INTO t1 VALUES (1), (2), (3); 268CREATE TABLE t2 (i INT); 269--let $query = INSERT INTO t2 SELECT * FROM t1 270--let $select = SELECT * FROM t1 271--source include/explain_utils.inc 272--let $query = INSERT INTO t2 SELECT * FROM t1 JOIN t1 AS tt USING(i) 273--let $select = SELECT * FROM t1 JOIN t1 AS tt USING(i) 274--source include/explain_utils.inc 275DROP TABLE t1, t2; 276 277--echo #26 278CREATE TABLE t1 (i INT); 279INSERT INTO t1 VALUES (1), (2), (3); 280CREATE TABLE t2 (i INT); 281--let $query = REPLACE INTO t2 SELECT * FROM t1 282--let $select = SELECT * FROM t1 283--source include/explain_utils.inc 284DROP TABLE t1, t2; 285 286--echo #27 287CREATE TABLE t1 (i INT); 288--let $query = INSERT INTO t1 SET i = 10 289--source include/explain_utils.inc 290DROP TABLE t1; 291 292--echo #28 293CREATE TABLE t1 (i INT); 294--let $query = REPLACE INTO t1 SET i = 10 295--source include/explain_utils.inc 296DROP TABLE t1; 297 298--echo #29 299CREATE TABLE t1 (a INT, i INT PRIMARY KEY); 300INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), 301 (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), 302 (30),(31),(32),(33),(34),(35); 303--let $query = DELETE FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 1 304--let $select = SELECT * FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 1 305--let $no_rows = 1 306--source include/explain_utils.inc 307--let $no_rows = 0 308DROP TABLE t1; 309 310--echo #30 311CREATE TABLE t1(a INT, i CHAR(2), INDEX(i(1))); 312INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), 313 (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), 314 (30),(31),(32),(33),(34),(35); 315--let $query = DELETE FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 316--let $select = SELECT * FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 317--source include/explain_utils.inc 318DROP TABLE t1; 319 320--echo #31 321CREATE TABLE t1 (i INT); 322INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), 323 (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), 324 (30),(31),(32),(33),(34),(35); 325CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); 326INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; 327--let $query = DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 328--let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 329--source include/explain_utils.inc 330DROP TABLE t1, t2; 331 332--echo #32 333CREATE TABLE t1 (i INT); 334INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), 335 (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), 336 (30),(31),(32),(33),(34),(35); 337CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); 338INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; 339INSERT INTO t2 (a, b, c) SELECT t1.i, t1.i, t1.i FROM t1, t1 x1, t1 x2; 340--let $query = DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 341--let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 342--let $no_rows = 1 343--source include/explain_utils.inc 344DROP TABLE t1, t2; 345 346--echo #33 347CREATE TABLE t1 (i INT); 348INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), 349 (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), 350 (30),(31),(32),(33),(34),(35); 351CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b(1),c)); 352INSERT INTO t2 SELECT i, i, i, i FROM t1; 353--let $query = DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 354--let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 355--source include/explain_utils.inc 356DROP TABLE t1, t2; 357 358--echo #34 359CREATE TABLE t1 (i INT); 360INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), 361 (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), 362 (30),(31),(32),(33),(34),(35); 363CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b,c)) 364 ENGINE=HEAP; 365INSERT INTO t2 SELECT i, i, i, i FROM t1; 366--let $query = DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 367--let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 368--source include/explain_utils.inc 369DROP TABLE t1, t2; 370 371--echo #35 372CREATE TABLE t1 (i INT); 373INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), 374 (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), 375 (30),(31),(32),(33),(34),(35),(36),(37),(38),(39), 376 (40),(41),(42),(43),(44); 377CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2)); 378INSERT INTO t2 (key1, key2) SELECT i, i FROM t1; 379--let $query = DELETE FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1 380--let $select = SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1 381--let $no_rows = 1 382--source include/explain_utils.inc 383--let $no_rows = 0 384DROP TABLE t1, t2; 385 386--echo #36 387CREATE TABLE t1 (i INT); 388INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), 389 (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), 390 (30),(31),(32),(33),(34),(35); 391CREATE TABLE t2(a INT, i INT PRIMARY KEY); 392INSERT INTO t2 (i) SELECT i FROM t1; 393--let $query = DELETE FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 1 394--let $select = SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 1 395--let $no_rows = 1 396--source include/explain_utils.inc 397--let $no_rows = 0 398DROP TABLE t1, t2; 399 400--echo #37 401CREATE TABLE t1 (i INT); 402INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), 403 (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), 404 (30),(31),(32),(33),(34),(35); 405CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b)); 406INSERT INTO t2 SELECT i, i, i FROM t1; 407--let $query = DELETE FROM t2 ORDER BY a, b DESC LIMIT 5 408--let $select = SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5 409--source include/explain_utils.inc 410DROP TABLE t1, t2; 411 412--echo #38 413CREATE TABLE t1 (i INT); 414INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), 415 (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), 416 (30),(31),(32),(33),(34),(35); 417CREATE TABLE t2 (a CHAR(2), b CHAR(2), c INT, INDEX (a, b)); 418INSERT INTO t2 (a, b) SELECT i, i FROM t1; 419INSERT INTO t2 (a, b) SELECT t1.i, t1.i FROM t1, t1 x1, t1 x2; 420--let $query = DELETE FROM t2 ORDER BY a DESC, b DESC LIMIT 5 421--let $select = SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5 422--let $no_rows = 1 423--source include/explain_utils.inc 424DROP TABLE t1, t2; 425 426--echo #39 427CREATE TABLE t1 (i INT); 428INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), 429 (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), 430 (30),(31),(32),(33),(34),(35); 431CREATE TABLE t2(a INT, i INT PRIMARY KEY); 432INSERT INTO t2 (i) SELECT i FROM t1; 433--let $query = UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 1 434--let $select = SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 1 435--let $no_rows = 1 436--source include/explain_utils.inc 437DROP TABLE t1, t2; 438 439--echo #40 440CREATE TABLE t1 (i INT); 441INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), 442 (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), 443 (30),(31),(32),(33),(34),(35); 444CREATE TABLE t2(a INT, i CHAR(2), INDEX(i(1))); 445INSERT INTO t2 (i) SELECT i FROM t1; 446--let $query = UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 447--let $select = SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 448--source include/explain_utils.inc 449DROP TABLE t1, t2; 450 451--echo #41 452CREATE TABLE t1 (i INT); 453INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), 454 (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), 455 (30),(31),(32),(33),(34),(35); 456CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); 457INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; 458--let $query = UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5 459--let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 460--source include/explain_utils.inc 461DROP TABLE t1, t2; 462 463--echo #42 464CREATE TABLE t1 (i INT); 465INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), 466 (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), 467 (30),(31),(32),(33),(34),(35); 468CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); 469INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; 470INSERT INTO t2 (a, b, c) SELECT t1.i, t1.i, t1.i FROM t1, t1 x1, t1 x2; 471--let $query = UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5 472--let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 473--let $no_rows = 1 474--source include/explain_utils.inc 475DROP TABLE t1, t2; 476 477--echo #43 478CREATE TABLE t1 (i INT); 479INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), 480 (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), 481 (30),(31),(32),(33),(34),(35); 482CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b(1),c)); 483INSERT INTO t2 SELECT i, i, i, i FROM t1; 484--let $query = UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5 485--let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 486--source include/explain_utils.inc 487DROP TABLE t1, t2; 488 489--echo #44 490CREATE TABLE t1 (i INT); 491INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), 492 (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), 493 (30),(31),(32),(33),(34),(35); 494CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b,c)) 495 ENGINE=HEAP; 496INSERT INTO t2 SELECT i, i, i, i FROM t1; 497--let $query = UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5 498--let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 499--source include/explain_utils.inc 500DROP TABLE t1, t2; 501 502--echo #45 503CREATE TABLE t1 (i INT); 504INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), 505 (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), 506 (30),(31),(32),(33),(34),(35),(36),(37),(38),(39), 507 (40),(41),(42),(43),(44); 508CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2)); 509INSERT INTO t2 (key1, key2) SELECT i, i FROM t1; 510--let $query = UPDATE t2 SET i = 123 WHERE key1 < 13 or key2 < 14 ORDER BY key1 511--let $select = SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1 512--let $no_rows = 1 513--source include/explain_utils.inc 514--let $no_rows = 0 515DROP TABLE t1, t2; 516 517--echo #46 518CREATE TABLE t1 (i INT); 519INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), 520 (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), 521 (30),(31),(32),(33),(34),(35); 522CREATE TABLE t2(a INT, i INT PRIMARY KEY); 523INSERT INTO t2 (i) SELECT i FROM t1; 524--let $query = UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 1 525--let $select = SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 1 526--let $no_rows = 1 527--source include/explain_utils.inc 528--let $no_rows = 0 529DROP TABLE t1, t2; 530 531--echo #47 532CREATE TABLE t1 (i INT); 533INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), 534 (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), 535 (30),(31),(32),(33),(34),(35); 536CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b)); 537INSERT INTO t2 SELECT i, i, i FROM t1; 538--let $query = UPDATE t2 SET c = 10 ORDER BY a, b DESC LIMIT 5 539--let $select = SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5 540--source include/explain_utils.inc 541DROP TABLE t1, t2; 542 543--echo #48 544CREATE TABLE t1 (i INT); 545INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), 546 (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), 547 (30),(31),(32),(33),(34),(35); 548CREATE TABLE t2 (a CHAR(2), b CHAR(2), c INT, INDEX (a, b)); 549INSERT INTO t2 (a, b) SELECT i, i FROM t1; 550INSERT INTO t2 (a, b) SELECT t1.i, t1.i FROM t1, t1 x1, t1 x2; 551--let $query = UPDATE t2 SET c = 10 ORDER BY a DESC, b DESC LIMIT 5 552--let $select = SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5 553--let $no_rows = 1 554--source include/explain_utils.inc 555DROP TABLE t1, t2; 556 557--echo #49 558CREATE TABLE t1 ( 559 pk INT NOT NULL AUTO_INCREMENT, 560 c1_idx CHAR(1) DEFAULT 'y', 561 c2 INT, 562 PRIMARY KEY (pk), 563 INDEX c1_idx (c1_idx) 564); 565INSERT INTO t1 VALUES (1,'y',1), (2,'n',2), (3,'y',3), (4,'n',4); 566--let $query = UPDATE t1 SET c2 = 0 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2 567--let $select = SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2 568--source include/explain_utils.inc 569--let $query = DELETE FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2 570--let $select = SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2 571--source include/explain_utils.inc 572DROP TABLE t1; 573 574--echo #50 575CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY); 576INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(); 577--let $query = UPDATE t1 SET a=a+10 WHERE a > 34 578--let $select = SELECT * FROM t1 WHERE a > 34 579--source include/explain_utils.inc 580DROP TABLE t1; 581 582--echo #51 583CREATE TABLE t1 (c1 INT, c2 INT, c3 INT); 584CREATE TABLE t2 (c1 INT, c2 INT); 585INSERT INTO t1 VALUES (1, 1, 10), (2, 2, 20); 586--let $query = UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10 587--let $select = SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1 588--source include/explain_utils.inc 589--let $query = UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10 WHERE t1.c3 = 10 590--let $select = SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1 WHERE t1.c3 = 10 591--source include/explain_utils.inc 592DROP TABLE t1, t2; 593 594--echo #52 595CREATE TABLE t1(f1 INT, f2 INT); 596CREATE TABLE t2(f3 INT, f4 INT); 597CREATE INDEX IDX ON t2(f3); 598INSERT INTO t1 VALUES(1,0),(2,0); 599INSERT INTO t2 VALUES(1,1),(2,2); 600# Need to run ANALYZE to get correct index statistics 601-- disable_query_log 602-- disable_result_log 603ANALYZE TABLE t1,t2; 604-- enable_result_log 605-- enable_query_log 606--let $query = UPDATE t1 SET t1.f2=(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1) 607--let $select = SELECT (SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1) FROM t1 608--source include/explain_utils.inc 609DROP TABLE t1, t2; 610 611--echo #55 612CREATE TABLE t1(a INT); 613INSERT INTO t1 VALUES (1); 614SET @a = NULL; 615EXPLAIN DELETE FROM t1 WHERE (@a:= a); 616if (`SELECT @a IS NOT NULL`) { 617 die Unexpectedly modified user variable; 618} 619DROP TABLE t1; 620 621--echo #56 622CREATE TABLE t1 (a INT); 623INSERT INTO t1 VALUES (1), (2), (3); 624--error ER_BAD_FIELD_ERROR 625 DELETE FROM t1 USING t1 WHERE uknown_column = 12345; 626--error ER_BAD_FIELD_ERROR 627EXPLAIN EXTENDED DELETE FROM t1 USING t1 WHERE uknown_column = 12345; 628DROP TABLE t1; 629 630--echo #57 631CREATE TABLE t1(f1 INT); 632--error ER_BAD_FIELD_ERROR 633EXPLAIN EXTENDED UPDATE t1 SET f2=1 ORDER BY f2; 634--error ER_BAD_FIELD_ERROR 635UPDATE t1 SET f2=1 ORDER BY f2; 636DROP TABLE t1; 637 638--echo #59 639CREATE TABLE t1 ( a INT, KEY( a ) ); 640INSERT INTO t1 VALUES (0), (1); 641CREATE VIEW v1 AS SELECT t11.a, t12.a AS b FROM t1 t11, t1 t12; 642SET SESSION sql_safe_updates = 1; 643EXPLAIN EXTENDED UPDATE IGNORE v1 SET a = 1; 644--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE 645UPDATE IGNORE v1 SET a = 1; 646SET SESSION sql_safe_updates = DEFAULT; 647DROP TABLE t1; 648DROP VIEW v1; 649 650--echo #62 651CREATE TABLE t1 (a INT); 652INSERT INTO t1 VALUES (0), (1); 653CREATE VIEW v1 AS SELECT t11.a, t12.a AS b FROM t1 t11, t1 t12; 654--let $query = UPDATE v1 SET a = 1 WHERE a > 0 655--let $select = SELECT * FROM v1 WHERE a > 0 656--source include/explain_utils.inc 657--let $query = UPDATE t1, v1 SET v1.a = 1 WHERE t1.a = v1.a 658--let $select = SELECT * FROM t1, v1 WHERE t1.a = v1.a 659--source include/explain_utils.inc 660DROP TABLE t1; 661DROP VIEW v1; 662 663--echo #63 664CREATE TABLE t1 (a INT, PRIMARY KEY(a)); 665INSERT INTO t1 VALUES (1), (2), (3), (4), (5); 666CREATE VIEW v1 (a) AS SELECT a FROM t1; 667--let $query = DELETE FROM v1 WHERE a < 4 668--let $select = SELECT * FROM v1 WHERE a < 4 669--let $no_rows = 1 670--source include/explain_utils.inc 671--let $no_rows = 0 672DROP TABLE t1; 673DROP VIEW v1; 674 675--echo #64 676CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a)); 677INSERT INTO t1 VALUES (1,2), (2,3), (3,4), (4,5), (5,10); 678CREATE TABLE t2 (x INT); 679INSERT INTO t2 VALUES (1), (2), (3), (4); 680CREATE VIEW v1 (a,c) AS SELECT a, b+1 FROM t1; 681--let $query = DELETE v1 FROM t2, v1 WHERE t2.x = v1.a 682--let $select = SELECT * FROM t2, v1 WHERE t2.x = v1.a 683--source include/explain_utils.inc 684DROP TABLE t1,t2; 685DROP VIEW v1; 686 687--echo #65 688CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a)); 689INSERT INTO t1 VALUES (1,2), (2,3), (3,4), (4,5), (5,10); 690CREATE TABLE t2 (x INT); 691INSERT INTO t2 VALUES (1), (2), (3), (4); 692CREATE VIEW v1 (a,c) AS SELECT a, b+1 FROM t1; 693--let $query = DELETE v1 FROM t2, v1 WHERE t2.x = v1.a 694--let $select = SELECT * FROM t2, v1 WHERE t2.x = v1.a 695--source include/explain_utils.inc 696DROP TABLE t1,t2; 697DROP VIEW v1; 698 699--echo #66 700CREATE TABLE t1 (a INT); 701CREATE VIEW v1 (x) AS SELECT a FROM t1; 702--let $query = INSERT INTO v1 VALUES (10) 703--let $select = SELECT NULL 704--source include/explain_utils.inc 705DROP TABLE t1; 706DROP VIEW v1; 707 708--echo #67 709CREATE TABLE t1 (a INT); 710CREATE TABLE t2 (b INT); 711INSERT INTO t2 VALUES (1), (2), (3); 712CREATE VIEW v1 (x) AS SELECT b FROM t2; 713--let $query = INSERT INTO v1 SELECT * FROM t1 714--let $select = SELECT * FROM t1 715--source include/explain_utils.inc 716DROP TABLE t1, t2; 717DROP VIEW v1; 718 719--echo #69 720CREATE TABLE t1 (a INT); 721INSERT INTO t1 VALUES (1), (2), (3); 722CREATE TABLE t2 (b INT); 723INSERT INTO t2 VALUES (1), (2), (3); 724--let $query = UPDATE t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x) 725--let $select = SELECT * FROM t1 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x) 726--source include/explain_utils.inc 727--let $query = UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x) 728--let $select = SELECT * FROM t1, t2 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x) 729--source include/explain_utils.inc 730--let $query = UPDATE t1, (SELECT * FROM t2) y SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x) 731--let $select = SELECT * FROM t1, (SELECT * FROM t2) y WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x) 732--source include/explain_utils.inc 733DROP TABLE t1,t2; 734 735--echo #70 736CREATE TABLE t1 (c1 INT KEY); 737CREATE TABLE t2 (c2 INT); 738CREATE TABLE t3 (c3 INT); 739EXPLAIN EXTENDED UPDATE t3 SET c3 = ( 740 SELECT COUNT(d1.c1) 741 FROM ( 742 SELECT a11.c1 FROM t1 AS a11 743 STRAIGHT_JOIN t2 AS a21 ON a21.c2 = a11.c1 744 JOIN t1 AS a12 ON a12.c1 = a11.c1 745 ) d1 746); 747 748DROP TABLE t1, t2, t3; 749 750--echo #71 751# 752# Bug: after EXPLAIN bulk INSERT...SELECT and bulk INSERT...SELECT 753# to a # MyISAM table the SELECT query may fail with the 754# "1030: Got error 124 from storage engine" error message. 755# 756CREATE TABLE t1 (c1 INT NOT NULL, c2 INT NOT NULL, INDEX i1(c1)); 757INSERT INTO t1 VALUES (1,0),(2,0),(3,0),(4,0),(5,0),(6,0),(7,0),(8,0); 758--disable_query_log 759let $1=7; 760SET @d=8; 761while ($1) { 762 eval INSERT INTO t1 SELECT c1 + @d, c2 + @d FROM t1; 763 eval SET @d = @d*2; 764 dec $1; 765} 766--enable_query_log 767CREATE TABLE t2 LIKE t1; 768 769# replace "rows" column for InnoDB 770--replace_column 10 X 771EXPLAIN INSERT INTO t2 SELECT * FROM t1; 772INSERT INTO t2 SELECT * FROM t1; 773--disable_result_log 774SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1; 775--enable_result_log 776 777DROP TABLE t1, t2; 778 779--echo #73 780 781CREATE TABLE t1 (id INT); 782CREATE TABLE t2 (id INT); 783INSERT INTO t1 VALUES (1), (2); 784 785EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USING(id) GROUP BY t1.id; 786 787DROP TABLE t1,t2; 788 789--echo #74 790 791CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB; 792INSERT INTO t1 VALUES (1), (2), (3), (4), (5); 793 794--echo # used key is modified & Using temporary 795 796--let $query = UPDATE t1 SET a=a+1 WHERE a>10 797--let $select = SELECT a t1 FROM t1 WHERE a>10 798--source include/explain_utils.inc 799 800--echo # used key is modified & Using filesort 801 802--let $query = UPDATE t1 SET a=a+1 WHERE a>10 ORDER BY a+20 803--let $select = SELECT a t1 FROM t1 WHERE a>10 ORDER BY a+20 804--source include/explain_utils.inc 805 806DROP TABLE t1; 807 808--echo # 809--echo # Bug #12949629: CLIENT LOSES CONNECTION AFTER EXECUTING A PROCEDURE WITH 810--echo # EXPLAIN UPDATE/DEL/INS 811--echo # 812 813CREATE TABLE t1 (i INT); 814CREATE TABLE t2 (i INT); 815 816--delimiter | 817CREATE PROCEDURE p1() BEGIN EXPLAIN INSERT INTO t1 VALUES (1);END| 818CREATE PROCEDURE p2() BEGIN INSERT INTO t1 VALUES (1);END| 819CREATE PROCEDURE p3() BEGIN EXPLAIN INSERT INTO t1 SELECT 1;END| 820CREATE PROCEDURE p4() BEGIN INSERT INTO t1 SELECT 1;END| 821CREATE PROCEDURE p5() BEGIN EXPLAIN REPLACE INTO t1 VALUES (1);END| 822CREATE PROCEDURE p6() BEGIN REPLACE INTO t1 VALUES (1);END| 823CREATE PROCEDURE p7() BEGIN EXPLAIN REPLACE INTO t1 SELECT 1;END| 824CREATE PROCEDURE p8() BEGIN REPLACE INTO t1 SELECT 1;END| 825CREATE PROCEDURE p9() BEGIN EXPLAIN UPDATE t1 SET i = 10;END| 826CREATE PROCEDURE p10() BEGIN UPDATE t1 SET i = 10;END| 827CREATE PROCEDURE p11() BEGIN EXPLAIN UPDATE t1,t2 SET t1.i = 10 WHERE t1.i = t2.i ;END| 828CREATE PROCEDURE p12() BEGIN UPDATE t1,t2 SET t1.i = 10 WHERE t1.i = t2.i ;END| 829CREATE PROCEDURE p13() BEGIN EXPLAIN DELETE FROM t1;END| 830CREATE PROCEDURE p14() BEGIN DELETE FROM t1;END| 831CREATE PROCEDURE p15() BEGIN EXPLAIN DELETE FROM t1 USING t1;END| 832CREATE PROCEDURE p16() BEGIN DELETE FROM t1 USING t1;END| 833--delimiter ; 834 835let $i=16; 836while($i) { 837 eval CALL p$i(); 838 eval DROP PROCEDURE p$i; 839 dec $i; 840} 841 842DROP TABLE t1, t2; 843 844--echo # 845 846-- disable_query_log 847-- disable_result_log 848SET GLOBAL innodb_stats_persistent=default; 849-- enable_result_log 850-- enable_query_log 851