diff options
Diffstat (limited to 'mysql-test/main/multi_update.test')
| -rw-r--r-- | mysql-test/main/multi_update.test | 142 |
1 files changed, 140 insertions, 2 deletions
diff --git a/mysql-test/main/multi_update.test b/mysql-test/main/multi_update.test index 329394e8..c6247a21 100644 --- a/mysql-test/main/multi_update.test +++ b/mysql-test/main/multi_update.test @@ -391,7 +391,6 @@ drop table t1, t2, t3; create table t1 (col1 int); create table t2 (col1 int); update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1; --- error ER_UPDATE_TABLE_USED delete t1 from t1,t2 where t1.col1 < (select max(col1) from t1) and t1.col1 = t2.col1; drop table t1,t2; @@ -400,7 +399,6 @@ drop table t1,t2; # create table t1(a int); create table t2(a int); ---error ER_UPDATE_TABLE_USED delete from t1,t2 using t1,t2 where t1.a=(select a from t1); drop table t1, t2; # End of 4.1 tests @@ -1121,12 +1119,15 @@ INSERT INTO t1 (part,a,b) VALUES (0,0,0),(1,1,1),(2,2,2); INSERT INTO t2 (part,a,b) VALUES (0,0,0),(1,1,1),(2,2,2); --echo # Expecting partition "Current" +--source include/explain-no-costs.inc EXPLAIN FORMAT=JSON UPDATE t2 JOIN t1 USING(a) SET t2.part=3 WHERE t2.part=0 AND t1.part=0; --echo # Expecting partition "Relevant" +--source include/explain-no-costs.inc EXPLAIN FORMAT=JSON UPDATE t2 JOIN t1 USING(a) SET t2.part=2 WHERE t2.part=1 AND t1.part=1; --echo # Expecting partition "Archive" +--source include/explain-no-costs.inc EXPLAIN FORMAT=JSON UPDATE t2 JOIN t1 USING(a) SET t2.part=3 WHERE t2.part=2 AND t1.part=2; DROP TABLES t1, t2; @@ -1200,3 +1201,140 @@ select * from t1; drop table t1,t2,t3,t; --echo # End of 10.4 tests + +--echo # +--echo # MDEV-31150: 2nd execution of multi-update with +--echo # mergeable derived table in WHERE +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (3),(7),(1),(3); +CREATE TABLE t2 (b int); +INSERT INTO t2 VALUES (3),(4),(1); +CREATE TABLE t3 (c int); +INSERT INTO t3 VALUES (3),(4),(1),(5),(1); + +--delimiter | +CREATE PROCEDURE restore_t1() +BEGIN +DELETE FROM t1; +INSERT INTO t1 VALUES (3),(7),(1),(3); +END| +--delimiter ; + +let $q1= +UPDATE t1,t3 SET a = NULL + WHERE a=c AND a IN (SELECT * FROM (SELECT b FROM t2) dt); + +SELECT * FROM t1; +eval EXPLAIN $q1; +eval $q1; +SELECT * FROM t1; +CALL restore_t1(); +eval PREPARE stmt FROM "$q1"; +EXECUTE stmt; +SELECT * FROM t1; +CALL restore_t1(); +EXECUTE stmt; +SELECT * FROM t1; +CALL restore_t1(); +DEALLOCATE PREPARE stmt; + +eval CREATE PROCEDURE p1() $q1; +SELECT * FROM t1; +CALL p1(); +SELECT * FROM t1; +CALL restore_t1(); +CALL p1(); +SELECT * FROM t1; +CALL restore_t1(); +DROP procedure p1; + +let $q2= +UPDATE t1,t3 SET a = NULL + WHERE a=c AND a IN (WITH cte AS (SELECT b FROM t2) SELECT * FROM cte); + +SELECT * FROM t1; +eval EXPLAIN $q2; +eval $q2; +SELECT * FROM t1; +CALL restore_t1(); +eval PREPARE stmt FROM "$q2"; +EXECUTE stmt; +SELECT * FROM t1; +CALL restore_t1(); +EXECUTE stmt; +SELECT * FROM t1; +CALL restore_t1(); +DEALLOCATE PREPARE stmt; + +eval CREATE PROCEDURE p1() $q2; +SELECT * FROM t1; +CALL p1(); +SELECT * FROM t1; +CALL restore_t1(); +CALL p1(); +SELECT * FROM t1; +CALL restore_t1(); +DROP procedure p1; + +let $q3= +UPDATE t1 SET a = NULL + WHERE a IN (SELECT * FROM (SELECT b FROM t2) dt); + +SELECT * FROM t1; +eval EXPLAIN $q3; +eval $q3; +SELECT * FROM t1; +CALL restore_t1(); +eval PREPARE stmt FROM "$q3"; +EXECUTE stmt; +SELECT * FROM t1; +CALL restore_t1(); +EXECUTE stmt; +SELECT * FROM t1; +CALL restore_t1(); +DEALLOCATE PREPARE stmt; + +eval CREATE PROCEDURE p1() $q3; +SELECT * FROM t1; +CALL p1(); +SELECT * FROM t1; +CALL restore_t1(); +CALL p1(); +SELECT * FROM t1; +CALL restore_t1(); +DROP procedure p1; + +let $q4= +UPDATE t1 SET a = NULL + WHERE a IN (WITH cte AS (SELECT b FROM t2) SELECT * FROM cte); + +SELECT * FROM t1; +eval EXPLAIN $q4; +eval $q4; +SELECT * FROM t1; +CALL restore_t1(); +eval PREPARE stmt FROM "$q4"; +EXECUTE stmt; +SELECT * FROM t1; +CALL restore_t1(); +EXECUTE stmt; +SELECT * FROM t1; +CALL restore_t1(); +DEALLOCATE PREPARE stmt; + +eval CREATE PROCEDURE p1() $q4; +SELECT * FROM t1; +CALL p1(); +SELECT * FROM t1; +CALL restore_t1(); +CALL p1(); +SELECT * FROM t1; +CALL restore_t1(); +DROP procedure p1; + +DROP TABLE t1,t2,t3; +DROP PROCEDURE restore_t1; + +--echo # End of 11.1 tests |