diff options
Diffstat (limited to 'mysql-test/include/update_use_source_cases.inc')
-rw-r--r-- | mysql-test/include/update_use_source_cases.inc | 183 |
1 files changed, 183 insertions, 0 deletions
diff --git a/mysql-test/include/update_use_source_cases.inc b/mysql-test/include/update_use_source_cases.inc new file mode 100644 index 00000000..f1025f75 --- /dev/null +++ b/mysql-test/include/update_use_source_cases.inc @@ -0,0 +1,183 @@ +--echo # +--echo # Update with value from subquery on the same table +--echo # + +analyze table t1 persistent for all; +let $q = update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3); +eval explain select * from t1; +eval explain $q; +--enable_info ONCE +eval $q; +select concat(old_c1,'->',c1),c3, + case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +truncate table t1; +insert into t1 select * from tmp; + +--echo # +--echo # Update with EXISTS subquery over the updated table +--echo # in WHERE + possibly sargable condition +--echo # + +analyze table t1 persistent for all; +let $c = c1 <2 + and exists (select 'X' from t1 a where a.c1 = t1.c1); +let $q = update t1 set c1=10 where $c; +eval explain select * from t1 where $c; +eval explain $q; +--enable_info ONCE +eval $q; +select concat(old_c1,'->',c1),c3, + case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +truncate table t1; +insert into t1 select * from tmp; + +--echo # +--echo # Update with EXISTS subquery over the updated table +--echo # in WHERE + non-sargable condition +--echo # + +analyze table t1 persistent for all; +let $c = exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; +let $q = update t1 set c1=c1+10 where $c; +eval explain select * from t1 where $c; +eval explain $q; +--enable_info ONCE +eval $q; +select concat(old_c1,'->',c1),c3, + case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +truncate table t1; +insert into t1 select * from tmp; + +--echo # +--echo # Update with order by +--echo # + +analyze table t1 persistent for all; +let $c = exists (select 'X' from t1 a where a.c2 = t1.c2) + and c2 >= 3 order by c2; +let $q = update t1 set c1=c1+10 where $c; +eval explain select * from t1 where $c; +eval explain $q; +--enable_info ONCE +eval $q; +select concat(old_c1,'->',c1),c3, + case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +truncate table t1; +insert into t1 select * from tmp; + +--echo # +--echo # Update with a reference to view in subquery +--echo # in settable value +--echo # + +analyze table t1 persistent for all; +let $q = update t1 set c1=c1 +(select max(a.c2) from v1 a + where a.c1 = t1.c1); +eval explain select * from t1; +eval explain $q; +--enable_info ONCE +eval $q; +select concat(old_c1,'->',c1),c3, + case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +truncate table t1; +insert into t1 select * from tmp; + +--echo # +--echo # Update view +--echo # + +analyze table t1 persistent for all; +let $q = update v1 set c1=c1 + (select max(a.c2) from t1 a + where a.c1 = v1.c1) +10 where c3 > 3; +eval explain select * from v1; +eval explain $q; +--enable_info ONCE +eval $q; +select concat(old_c1,'->',c1),c3, + case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +truncate table t1; +insert into t1 select * from tmp; + +--echo # +--echo # Update view with reference to the same view in subquery +--echo # + +analyze table t1 persistent for all; +let $c = c1 <2 + and exists (select 'X' from v1 a where a.c1 = v1.c1); +let $q = update v1 set c1=c1 + 1 where $c; +eval explain select * from v1 where $c; +eval explain $q; +--enable_info ONCE +eval $q; +select concat(old_c1,'->',c1),c3, + case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +truncate table t1; +insert into t1 select * from tmp; + +--echo # +--echo # Update view with EXISTS and reference to the same view in subquery +--echo # + +analyze table t1 persistent for all; +let $c = c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2); +let $q = update v1 + set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1) + where $c; +eval explain select * from v1 where $c; +eval explain $q; +--enable_info ONCE +eval $q; +select concat(old_c1,'->',c1),c3, + case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +truncate table t1; +insert into t1 select * from tmp; + +--echo # +--echo # Update with IN predicand over the updated table in WHERE +--echo # + +analyze table t1 persistent for all; +let $c = c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1); +let $q = update t1 set c3=c3+110 where $c; +eval explain select * from t1 where $c; +eval explain $q; +--enable_info ONCE +eval $q; +--sorted_result +select c3 from t1; +truncate table t1; +insert into t1 select * from tmp; + +--echo # +--echo # Update with a limit +--echo # + +analyze table t1 persistent for all; +let $q = update t1 + set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2; +eval explain select * from t1 limit 2; +eval explain $q; +--enable_info ONCE +eval $q; +select concat(old_c1,'->',c1),c3, + case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +truncate table t1; +insert into t1 select * from tmp; + +--echo # +--echo # Update with a limit and an order by +--echo # + +analyze table t1 persistent for all; +let $q=update t1 + set c1=(select a.c3 from t1 a where a.c3 = t1.c3) + order by c3 desc limit 2; +eval explain select * from t1 order by c3 desc limit 2; +eval explain $q; +--enable_info ONCE +eval $q; +select concat(old_c1,'->',c1),c3, + case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +truncate table t1; +insert into t1 select * from tmp; |