diff options
Diffstat (limited to 'mysql-test/main/multi_update.result')
| -rw-r--r-- | mysql-test/main/multi_update.result | 120 |
1 files changed, 120 insertions, 0 deletions
diff --git a/mysql-test/main/multi_update.result b/mysql-test/main/multi_update.result index 61e04c3d4a9..d6cf9ba685f 100644 --- a/mysql-test/main/multi_update.result +++ b/mysql-test/main/multi_update.result @@ -1251,3 +1251,123 @@ EXPLAIN } } DROP TABLES t1, t2; +# End of 10.3 tests +# +# MDEV-28538: multi-table UPDATE/DELETE with possible exists-to-in +# +create table t1 (c1 int, c2 int, c3 int, index idx(c2)); +insert into t1 values +(1,1,1),(3,2,2),(1,3,3), +(2,1,4),(2,2,5),(4,3,6), +(2,4,7),(2,5,8); +create table t2 (c1 int, c2 int, c3 int, index idx(c2)); +insert into t2 values +(1,7,1),(1,8,2),(1,3,3), +(2,1,4),(2,2,5),(2,3,6), +(2,4,7),(2,5,8); +create table t3 (c1 int, c2 int, c3 int, index idx(c2)); +insert into t3 values +(1,1,1),(1,2,2),(1,3,3), +(2,1,4),(2,2,5),(2,3,6), +(2,4,7),(2,5,8); +insert into t3 select c1+1, c2+2, c3 from t3; +insert into t3 select c1, c2+2, c3 from t3; +analyze table t1,t2,t3 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status OK +explain select * from t1,t3 +where t1.c2 = t3.c2 and +t1.c1 > 1 and +exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL idx NULL NULL NULL 8 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY t3 ref idx idx 5 test.t1.c2 3 +2 MATERIALIZED t2 range idx idx 5 NULL 3 Using index condition; Using where +explain delete from t1 using t1,t3 +where t1.c2 = t3.c2 and +t1.c1 > 1 and +exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL idx NULL NULL NULL 8 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY t3 ref idx idx 5 test.t1.c2 3 Using index +2 MATERIALIZED t2 range idx idx 5 NULL 3 Using where +explain update t1,t3 set t1.c1 = t1.c1+10 +where t1.c2 = t3.c2 and +t1.c1 > 1 and +exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL idx NULL NULL NULL 8 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY t3 ref idx idx 5 test.t1.c2 3 Using index +2 MATERIALIZED t2 range idx idx 5 NULL 3 Using where +create table t as select * from t1; +select * from t1,t3 +where t1.c2 = t3.c2 and +t1.c1 > 1 and +exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); +c1 c2 c3 c1 c2 c3 +2 1 4 1 1 1 +2 1 4 2 1 4 +2 2 5 1 2 2 +2 2 5 2 2 5 +2 4 7 2 4 7 +2 4 7 2 4 2 +2 4 7 3 4 5 +2 4 7 1 4 2 +2 4 7 2 4 5 +2 5 8 2 5 8 +2 5 8 2 5 3 +2 5 8 3 5 6 +2 5 8 1 5 3 +2 5 8 2 5 6 +2 5 8 2 5 1 +2 5 8 3 5 4 +select * from t1; +c1 c2 c3 +1 1 1 +3 2 2 +1 3 3 +2 1 4 +2 2 5 +4 3 6 +2 4 7 +2 5 8 +delete from t1 using t1,t3 +where t1.c2 = t3.c2 and +t1.c1 > 1 and +exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); +select * from t1; +c1 c2 c3 +1 1 1 +3 2 2 +1 3 3 +4 3 6 +truncate table t1; +insert into t1 select * from t; +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status Table is already up to date +update t1,t3 set t1.c1 = t1.c1+10 +where t1.c2 = t3.c2 and +t1.c1 > 1 and +exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); +select * from t1; +c1 c2 c3 +1 1 1 +3 2 2 +1 3 3 +12 1 4 +12 2 5 +4 3 6 +12 4 7 +12 5 8 +drop table t1,t2,t3,t; +# End of 10.4 tests |