diff options
Diffstat (limited to 'mysql-test/main/update_use_source.result')
| -rw-r--r-- | mysql-test/main/update_use_source.result | 1201 |
1 files changed, 1201 insertions, 0 deletions
diff --git a/mysql-test/main/update_use_source.result b/mysql-test/main/update_use_source.result new file mode 100644 index 00000000000..e5585fcee5d --- /dev/null +++ b/mysql-test/main/update_use_source.result @@ -0,0 +1,1201 @@ +create table t1 (old_c1 integer, old_c2 integer,c1 integer, c2 integer, c3 integer) engine=InnoDb; +create view v1 as select * from t1 where c2=2; +create trigger trg_t1 before update on t1 for each row +begin +set new.old_c1=old.c1; +set new.old_c2=old.c2; +end; +/ +insert into t1(c1,c2,c3) values (1,1,1); +insert into t1(c1,c2,c3) values (1,2,2); +insert into t1(c1,c2,c3) values (1,3,3); +insert into t1(c1,c2,c3) values (2,1,4); +insert into t1(c1,c2,c3) values (2,2,5); +insert into t1(c1,c2,c3) values (2,3,6); +insert into t1(c1,c2,c3) values (2,4,7); +insert into t1(c1,c2,c3) values (2,5,8); +commit; +select * from t1; +old_c1 old_c2 c1 c2 c3 +NULL NULL 1 1 1 +NULL NULL 1 2 2 +NULL NULL 1 3 3 +NULL NULL 2 1 4 +NULL NULL 2 2 5 +NULL NULL 2 3 6 +NULL NULL 2 4 7 +NULL NULL 2 5 8 +Test without any index +# +# Update a with value from subquery on the same table, no search clause. ALL access +# +start transaction; +update t1 +set c1=(select a.c3 +from t1 a +where a.c3 = t1.c3); +affected rows: 8 +info: Rows matched: 8 Changed: 8 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ; +concat(old_c1,'->',c1) c3 Changed +1->1 1 +1->2 2 * +1->3 3 * +2->4 4 * +2->5 5 * +2->6 6 * +2->7 7 * +2->8 8 * +rollback; +# +# Update with search clause on the same table +# +start transaction; +update t1 +set c1=10 +where c1 <2 +and exists (select 'X' + from t1 a +where a.c1 = t1.c1); +affected rows: 3 +info: Rows matched: 3 Changed: 3 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ; +concat(old_c1,'->',c1) c3 Changed +1->10 1 * +1->10 2 * +1->10 3 * +NULL 4 +NULL 5 +NULL 6 +NULL 7 +NULL 8 +rollback; +# +# Update via RANGE or INDEX access if an index or a primary key exists +# +explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where +2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 8 Using where +start transaction; +update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; +affected rows: 4 +info: Rows matched: 4 Changed: 4 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +concat(old_c1,'->',c1) c3 Changed +NULL 1 +NULL 2 +1->11 3 * +NULL 4 +NULL 5 +2->12 6 * +2->12 7 * +2->12 8 * +rollback; +# +# Update with order by +# +start transaction; +update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +concat(old_c1,'->',c1) c3 Changed +NULL 1 +NULL 2 +1->11 3 * +NULL 4 +NULL 5 +2->12 6 * +2->12 7 * +2->12 8 * +rollback; +# +Update using a view in subquery +# +start transaction; +update t1 +set c1=c1 +(select max(a.c2) +from v1 a +where a.c1 = t1.c1) ; +affected rows: 8 +info: Rows matched: 8 Changed: 8 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +concat(old_c1,'->',c1) c3 Changed +1->3 1 * +1->3 2 * +1->3 3 * +2->4 4 * +2->4 5 * +2->4 6 * +2->4 7 * +2->4 8 * +rollback; +# +# Update throw a view +# +start transaction; +update v1 +set c1=c1 + (select max(a.c2) +from t1 a +where a.c1 = v1.c1) +10 +where c3 > 3; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +concat(old_c1,'->',c1) c3 Changed +NULL 1 +NULL 2 +NULL 3 +NULL 4 +2->17 5 * +NULL 6 +NULL 7 +NULL 8 +rollback; +# +# Update through a view and using the view in subquery +# +start transaction; +update v1 +set c1=c1 + 1 +where c1 <2 +and exists (select 'X' + from v1 a +where a.c1 = v1.c1); +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +concat(old_c1,'->',c1) c3 Changed +NULL 1 +1->2 2 * +NULL 3 +NULL 4 +NULL 5 +NULL 6 +NULL 7 +NULL 8 +rollback; +# +# Update through a view and using the view in subquery +# +start transaction; +update v1 +set c1=(select max(a.c1)+10 +from v1 a +where a.c1 = v1.c1) +where c1 <10 +and exists (select 'X' + from v1 a +where a.c2 = v1.c2); +affected rows: 2 +info: Rows matched: 2 Changed: 2 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +concat(old_c1,'->',c1) c3 Changed +NULL 1 +1->11 2 * +NULL 3 +NULL 4 +2->12 5 * +NULL 6 +NULL 7 +NULL 8 +rollback; +# +# Update of the index or primary key (c3) +# +start transaction; +explain update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 +1 PRIMARY a ALL NULL NULL NULL NULL 8 Using where; FirstMatch(t1) +update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1); +affected rows: 8 +info: Rows matched: 8 Changed: 8 Warnings: 0 +select c3 from t1; +c3 +11 +12 +13 +14 +15 +16 +17 +18 +rollback; +# +# update with a limit +# +start transaction; +update t1 +set c1=(select a.c3 +from t1 a +where a.c3 = t1.c3) +limit 2; +affected rows: 2 +info: Rows matched: 2 Changed: 2 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ; +concat(old_c1,'->',c1) c3 Changed +1->1 1 +1->2 2 * +NULL 3 +NULL 4 +NULL 5 +NULL 6 +NULL 7 +NULL 8 +rollback; +# +# update with a limit and an order by +# +start transaction; +update t1 +set c1=(select a.c3 +from t1 a +where a.c3 = t1.c3) +order by c3 desc limit 2; +affected rows: 2 +info: Rows matched: 2 Changed: 2 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ; +concat(old_c1,'->',c1) c3 Changed +NULL 1 +NULL 2 +NULL 3 +NULL 4 +NULL 5 +NULL 6 +2->7 7 * +2->8 8 * +rollback; +Test with an index on updated columns +create index t1_c2 on t1 (c2,c1); +# +# Update a with value from subquery on the same table, no search clause. ALL access +# +start transaction; +update t1 +set c1=(select a.c3 +from t1 a +where a.c3 = t1.c3); +affected rows: 8 +info: Rows matched: 8 Changed: 8 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ; +concat(old_c1,'->',c1) c3 Changed +1->1 1 +1->2 2 * +1->3 3 * +2->4 4 * +2->5 5 * +2->6 6 * +2->7 7 * +2->8 8 * +rollback; +# +# Update with search clause on the same table +# +start transaction; +update t1 +set c1=10 +where c1 <2 +and exists (select 'X' + from t1 a +where a.c1 = t1.c1); +affected rows: 3 +info: Rows matched: 3 Changed: 3 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ; +concat(old_c1,'->',c1) c3 Changed +1->10 1 * +1->10 2 * +1->10 3 * +NULL 4 +NULL 5 +NULL 6 +NULL 7 +NULL 8 +rollback; +# +# Update via RANGE or INDEX access if an index or a primary key exists +# +explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using where +2 DEPENDENT SUBQUERY a ref t1_c2 t1_c2 5 test.t1.c2 4 Using index +start transaction; +update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; +affected rows: 4 +info: Rows matched: 4 Changed: 4 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +concat(old_c1,'->',c1) c3 Changed +NULL 1 +NULL 2 +1->11 3 * +NULL 4 +NULL 5 +2->12 6 * +2->12 7 * +2->12 8 * +rollback; +# +# Update with order by +# +start transaction; +update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +concat(old_c1,'->',c1) c3 Changed +NULL 1 +NULL 2 +1->11 3 * +NULL 4 +NULL 5 +2->12 6 * +2->12 7 * +2->12 8 * +rollback; +# +Update using a view in subquery +# +start transaction; +update t1 +set c1=c1 +(select max(a.c2) +from v1 a +where a.c1 = t1.c1) ; +affected rows: 8 +info: Rows matched: 8 Changed: 8 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +concat(old_c1,'->',c1) c3 Changed +1->3 1 * +1->3 2 * +1->3 3 * +2->4 4 * +2->4 5 * +2->4 6 * +2->4 7 * +2->4 8 * +rollback; +# +# Update throw a view +# +start transaction; +update v1 +set c1=c1 + (select max(a.c2) +from t1 a +where a.c1 = v1.c1) +10 +where c3 > 3; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +concat(old_c1,'->',c1) c3 Changed +NULL 1 +NULL 2 +NULL 3 +NULL 4 +2->17 5 * +NULL 6 +NULL 7 +NULL 8 +rollback; +# +# Update through a view and using the view in subquery +# +start transaction; +update v1 +set c1=c1 + 1 +where c1 <2 +and exists (select 'X' + from v1 a +where a.c1 = v1.c1); +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +concat(old_c1,'->',c1) c3 Changed +NULL 1 +1->2 2 * +NULL 3 +NULL 4 +NULL 5 +NULL 6 +NULL 7 +NULL 8 +rollback; +# +# Update through a view and using the view in subquery +# +start transaction; +update v1 +set c1=(select max(a.c1)+10 +from v1 a +where a.c1 = v1.c1) +where c1 <10 +and exists (select 'X' + from v1 a +where a.c2 = v1.c2); +affected rows: 2 +info: Rows matched: 2 Changed: 2 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +concat(old_c1,'->',c1) c3 Changed +NULL 1 +1->11 2 * +NULL 3 +NULL 4 +2->12 5 * +NULL 6 +NULL 7 +NULL 8 +rollback; +# +# Update of the index or primary key (c3) +# +start transaction; +explain update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 8 Using where +1 PRIMARY a ref t1_c2 t1_c2 10 test.t1.c2,test.t1.c1 1 Using index; FirstMatch(t1) +update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1); +affected rows: 8 +info: Rows matched: 8 Changed: 8 Warnings: 0 +select c3 from t1; +c3 +11 +12 +13 +14 +15 +16 +17 +18 +rollback; +# +# update with a limit +# +start transaction; +update t1 +set c1=(select a.c3 +from t1 a +where a.c3 = t1.c3) +limit 2; +affected rows: 2 +info: Rows matched: 2 Changed: 2 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ; +concat(old_c1,'->',c1) c3 Changed +1->1 1 +1->2 2 * +NULL 3 +NULL 4 +NULL 5 +NULL 6 +NULL 7 +NULL 8 +rollback; +# +# update with a limit and an order by +# +start transaction; +update t1 +set c1=(select a.c3 +from t1 a +where a.c3 = t1.c3) +order by c3 desc limit 2; +affected rows: 2 +info: Rows matched: 2 Changed: 2 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ; +concat(old_c1,'->',c1) c3 Changed +NULL 1 +NULL 2 +NULL 3 +NULL 4 +NULL 5 +NULL 6 +2->7 7 * +2->8 8 * +rollback; +Test with an index on updated columns +create index t1_c3 on t1 (c3); +# +# Update a with value from subquery on the same table, no search clause. ALL access +# +start transaction; +update t1 +set c1=(select a.c3 +from t1 a +where a.c3 = t1.c3); +affected rows: 8 +info: Rows matched: 8 Changed: 8 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ; +concat(old_c1,'->',c1) c3 Changed +1->1 1 +1->2 2 * +1->3 3 * +2->4 4 * +2->5 5 * +2->6 6 * +2->7 7 * +2->8 8 * +rollback; +# +# Update with search clause on the same table +# +start transaction; +update t1 +set c1=10 +where c1 <2 +and exists (select 'X' + from t1 a +where a.c1 = t1.c1); +affected rows: 3 +info: Rows matched: 3 Changed: 3 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ; +concat(old_c1,'->',c1) c3 Changed +1->10 1 * +1->10 2 * +1->10 3 * +NULL 4 +NULL 5 +NULL 6 +NULL 7 +NULL 8 +rollback; +# +# Update via RANGE or INDEX access if an index or a primary key exists +# +explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using where +2 DEPENDENT SUBQUERY a ref t1_c2 t1_c2 5 test.t1.c2 1 Using index +start transaction; +update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; +affected rows: 4 +info: Rows matched: 4 Changed: 4 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +concat(old_c1,'->',c1) c3 Changed +NULL 1 +NULL 2 +1->11 3 * +NULL 4 +NULL 5 +2->12 6 * +2->12 7 * +2->12 8 * +rollback; +# +# Update with order by +# +start transaction; +update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +concat(old_c1,'->',c1) c3 Changed +NULL 1 +NULL 2 +1->11 3 * +NULL 4 +NULL 5 +2->12 6 * +2->12 7 * +2->12 8 * +rollback; +# +Update using a view in subquery +# +start transaction; +update t1 +set c1=c1 +(select max(a.c2) +from v1 a +where a.c1 = t1.c1) ; +affected rows: 8 +info: Rows matched: 8 Changed: 8 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +concat(old_c1,'->',c1) c3 Changed +1->3 1 * +1->3 2 * +1->3 3 * +2->4 4 * +2->4 5 * +2->4 6 * +2->4 7 * +2->4 8 * +rollback; +# +# Update throw a view +# +start transaction; +update v1 +set c1=c1 + (select max(a.c2) +from t1 a +where a.c1 = v1.c1) +10 +where c3 > 3; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +concat(old_c1,'->',c1) c3 Changed +NULL 1 +NULL 2 +NULL 3 +NULL 4 +2->17 5 * +NULL 6 +NULL 7 +NULL 8 +rollback; +# +# Update through a view and using the view in subquery +# +start transaction; +update v1 +set c1=c1 + 1 +where c1 <2 +and exists (select 'X' + from v1 a +where a.c1 = v1.c1); +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +concat(old_c1,'->',c1) c3 Changed +NULL 1 +1->2 2 * +NULL 3 +NULL 4 +NULL 5 +NULL 6 +NULL 7 +NULL 8 +rollback; +# +# Update through a view and using the view in subquery +# +start transaction; +update v1 +set c1=(select max(a.c1)+10 +from v1 a +where a.c1 = v1.c1) +where c1 <10 +and exists (select 'X' + from v1 a +where a.c2 = v1.c2); +affected rows: 2 +info: Rows matched: 2 Changed: 2 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +concat(old_c1,'->',c1) c3 Changed +NULL 1 +1->11 2 * +NULL 3 +NULL 4 +2->12 5 * +NULL 6 +NULL 7 +NULL 8 +rollback; +# +# Update of the index or primary key (c3) +# +start transaction; +explain update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 8 Using where +1 PRIMARY a ref t1_c2 t1_c2 10 test.t1.c2,test.t1.c1 1 Using index; FirstMatch(t1) +update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1); +affected rows: 8 +info: Rows matched: 8 Changed: 8 Warnings: 0 +select c3 from t1; +c3 +11 +12 +13 +14 +15 +16 +17 +18 +rollback; +# +# update with a limit +# +start transaction; +update t1 +set c1=(select a.c3 +from t1 a +where a.c3 = t1.c3) +limit 2; +affected rows: 2 +info: Rows matched: 2 Changed: 2 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ; +concat(old_c1,'->',c1) c3 Changed +1->1 1 +1->2 2 * +NULL 3 +NULL 4 +NULL 5 +NULL 6 +NULL 7 +NULL 8 +rollback; +# +# update with a limit and an order by +# +start transaction; +update t1 +set c1=(select a.c3 +from t1 a +where a.c3 = t1.c3) +order by c3 desc limit 2; +affected rows: 2 +info: Rows matched: 2 Changed: 2 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ; +concat(old_c1,'->',c1) c3 Changed +NULL 1 +NULL 2 +NULL 3 +NULL 4 +NULL 5 +NULL 6 +2->7 7 * +2->8 8 * +rollback; +Test with a primary key on updated columns +drop index t1_c3 on t1; +alter table t1 add primary key (c3); +# +# Update a with value from subquery on the same table, no search clause. ALL access +# +start transaction; +update t1 +set c1=(select a.c3 +from t1 a +where a.c3 = t1.c3); +affected rows: 8 +info: Rows matched: 8 Changed: 8 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ; +concat(old_c1,'->',c1) c3 Changed +1->1 1 +1->2 2 * +1->3 3 * +2->4 4 * +2->5 5 * +2->6 6 * +2->7 7 * +2->8 8 * +rollback; +# +# Update with search clause on the same table +# +start transaction; +update t1 +set c1=10 +where c1 <2 +and exists (select 'X' + from t1 a +where a.c1 = t1.c1); +affected rows: 3 +info: Rows matched: 3 Changed: 3 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ; +concat(old_c1,'->',c1) c3 Changed +1->10 1 * +1->10 2 * +1->10 3 * +NULL 4 +NULL 5 +NULL 6 +NULL 7 +NULL 8 +rollback; +# +# Update via RANGE or INDEX access if an index or a primary key exists +# +explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using where +2 DEPENDENT SUBQUERY a ref t1_c2 t1_c2 5 test.t1.c2 1 Using index +start transaction; +update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; +affected rows: 4 +info: Rows matched: 4 Changed: 4 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +concat(old_c1,'->',c1) c3 Changed +NULL 1 +NULL 2 +1->11 3 * +NULL 4 +NULL 5 +2->12 6 * +2->12 7 * +2->12 8 * +rollback; +# +# Update with order by +# +start transaction; +update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +concat(old_c1,'->',c1) c3 Changed +NULL 1 +NULL 2 +1->11 3 * +NULL 4 +NULL 5 +2->12 6 * +2->12 7 * +2->12 8 * +rollback; +# +Update using a view in subquery +# +start transaction; +update t1 +set c1=c1 +(select max(a.c2) +from v1 a +where a.c1 = t1.c1) ; +affected rows: 8 +info: Rows matched: 8 Changed: 8 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +concat(old_c1,'->',c1) c3 Changed +1->3 1 * +1->3 2 * +1->3 3 * +2->4 4 * +2->4 5 * +2->4 6 * +2->4 7 * +2->4 8 * +rollback; +# +# Update throw a view +# +start transaction; +update v1 +set c1=c1 + (select max(a.c2) +from t1 a +where a.c1 = v1.c1) +10 +where c3 > 3; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +concat(old_c1,'->',c1) c3 Changed +NULL 1 +NULL 2 +NULL 3 +NULL 4 +2->17 5 * +NULL 6 +NULL 7 +NULL 8 +rollback; +# +# Update through a view and using the view in subquery +# +start transaction; +update v1 +set c1=c1 + 1 +where c1 <2 +and exists (select 'X' + from v1 a +where a.c1 = v1.c1); +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +concat(old_c1,'->',c1) c3 Changed +NULL 1 +1->2 2 * +NULL 3 +NULL 4 +NULL 5 +NULL 6 +NULL 7 +NULL 8 +rollback; +# +# Update through a view and using the view in subquery +# +start transaction; +update v1 +set c1=(select max(a.c1)+10 +from v1 a +where a.c1 = v1.c1) +where c1 <10 +and exists (select 'X' + from v1 a +where a.c2 = v1.c2); +affected rows: 2 +info: Rows matched: 2 Changed: 2 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +concat(old_c1,'->',c1) c3 Changed +NULL 1 +1->11 2 * +NULL 3 +NULL 4 +2->12 5 * +NULL 6 +NULL 7 +NULL 8 +rollback; +# +# Update of the index or primary key (c3) +# +start transaction; +explain update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 8 Using where +1 PRIMARY a ref t1_c2 t1_c2 10 test.t1.c2,test.t1.c1 1 Using index; FirstMatch(t1) +update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1); +affected rows: 8 +info: Rows matched: 8 Changed: 8 Warnings: 0 +select c3 from t1; +c3 +11 +14 +12 +15 +13 +16 +17 +18 +rollback; +# +# update with a limit +# +start transaction; +update t1 +set c1=(select a.c3 +from t1 a +where a.c3 = t1.c3) +limit 2; +affected rows: 2 +info: Rows matched: 2 Changed: 2 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ; +concat(old_c1,'->',c1) c3 Changed +1->1 1 +1->2 2 * +NULL 3 +NULL 4 +NULL 5 +NULL 6 +NULL 7 +NULL 8 +rollback; +# +# update with a limit and an order by +# +start transaction; +update t1 +set c1=(select a.c3 +from t1 a +where a.c3 = t1.c3) +order by c3 desc limit 2; +affected rows: 2 +info: Rows matched: 2 Changed: 2 Warnings: 0 +select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ; +concat(old_c1,'->',c1) c3 Changed +NULL 1 +NULL 2 +NULL 3 +NULL 4 +NULL 5 +NULL 6 +2->7 7 * +2->8 8 * +rollback; +# Update with error "Subquery returns more than 1 row" +update t1 set c2=(select c2 from t1); +ERROR 21000: Subquery returns more than 1 row +# Update with error "Subquery returns more than 1 row" and order by +update t1 set c2=(select c2 from t1) order by c3; +ERROR 21000: Subquery returns more than 1 row +Duplicate value on update a primary key +start transaction; +update t1 set c3=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; +ERROR 23000: Duplicate entry '0' for key 'PRIMARY' +rollback; +Duplicate value on update a primary key with ignore +start transaction; +update ignore t1 set c3=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; +affected rows: 4 +info: Rows matched: 4 Changed: 4 Warnings: 0 +rollback; +Duplicate value on update a primary key and limit +start transaction; +update t1 set c3=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 limit 2; +ERROR 23000: Duplicate entry '0' for key 'PRIMARY' +rollback; +Duplicate value on update a primary key with ignore and limit +start transaction; +update ignore t1 set c3=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 limit 2; +affected rows: 2 +info: Rows matched: 2 Changed: 2 Warnings: 0 +rollback; +# Update no rows found +update t1 +set c1=10 +where c1 <2 +and exists (select 'X' + from t1 a +where a.c1 = t1.c1 + 10); +affected rows: 0 +info: Rows matched: 0 Changed: 0 Warnings: 0 +# Update no rows changed +drop trigger trg_t1; +start transaction; +update t1 +set c1=c1 +where c1 <2 +and exists (select 'X' + from t1 a +where a.c1 = t1.c1); +affected rows: 0 +info: Rows matched: 3 Changed: 0 Warnings: 0 +rollback; +# +# Check call of after trigger +# +create or replace trigger trg_t2 after update on t1 for each row +begin +declare msg varchar(100); +if (new.c3 = 5) then +set msg=concat('in after update trigger on ',new.c3); +SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg; +end if; +end; +/ +update t1 set c1=2 where c3 in (select distinct a.c3 from t1 a where a.c1=t1.c1); +ERROR 45000: in after update trigger on 5 +# +# Check update with order by and after trigger +# +update t1 set c1=2 where c3 in (select distinct a.c3 from t1 a where a.c1=t1.c1) order by t1.c2; +ERROR 45000: in after update trigger on 5 +drop view v1; +# +# Check update on view with check option +# +create view v1 as select * from t1 where c2=2 with check option; +start transaction; +update v1 set c2=3 where c1=1; +ERROR 44000: CHECK OPTION failed `test`.`v1` +rollback; +start transaction; +update v1 set c2=(select max(c3) from v1) where c1=1; +ERROR 44000: CHECK OPTION failed `test`.`v1` +rollback; +start transaction; +update v1 set c2=(select min(va.c3) from v1 va), c1=0 where c1=1; +rollback; +drop view v1; +drop table t1; +# +# Test with a temporary table +# +create temporary table t1 (c1 integer, c2 integer, c3 integer) engine=InnoDb; +insert into t1(c1,c2,c3) values (1,1,1); +insert into t1(c1,c2,c3) values (1,2,2); +insert into t1(c1,c2,c3) values (1,3,3); +insert into t1(c1,c2,c3) values (2,1,4); +insert into t1(c1,c2,c3) values (2,2,5); +insert into t1(c1,c2,c3) values (2,3,6); +insert into t1(c1,c2,c3) values (2,4,7); +insert into t1(c1,c2,c3) values (2,5,8); +start transaction; +update t1 +set c1=(select a.c2 +from t1 a +where a.c3 = t1.c3) limit 3; +affected rows: 2 +info: Rows matched: 3 Changed: 2 Warnings: 0 +select * from t1 ; +c1 c2 c3 +1 1 1 +2 2 2 +3 3 3 +2 1 4 +2 2 5 +2 3 6 +2 4 7 +2 5 8 +rollback; +drop table t1; +# +# Test on dynamic columns (blob) +# +create table assets ( +item_name varchar(32) primary key, -- A common attribute for all items +dynamic_cols blob -- Dynamic columns will be stored here +); +INSERT INTO assets VALUES ('MariaDB T-shirt', COLUMN_CREATE('color', 'blue', 'size', 'XL')); +INSERT INTO assets VALUES ('Thinkpad Laptop', COLUMN_CREATE('color', 'black', 'price', 500)); +SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color FROM assets; +item_name color +MariaDB T-shirt blue +Thinkpad Laptop black +UPDATE assets SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '3 years') WHERE item_name='Thinkpad Laptop'; +SELECT item_name, COLUMN_GET(dynamic_cols, 'warranty' as char) AS color FROM assets; +item_name color +MariaDB T-shirt NULL +Thinkpad Laptop 3 years +UPDATE assets SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '4 years') +WHERE item_name in (select b.item_name +from assets b +where COLUMN_GET(b.dynamic_cols, 'color' as char) ='black'); +SELECT item_name, COLUMN_GET(dynamic_cols, 'warranty' as char) AS color FROM assets; +item_name color +MariaDB T-shirt NULL +Thinkpad Laptop 4 years +UPDATE assets SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', (select COLUMN_GET(b.dynamic_cols, 'color' as char) +from assets b +where assets.item_name = item_name)); +SELECT item_name, COLUMN_GET(dynamic_cols, 'warranty' as char) AS color FROM assets; +item_name color +MariaDB T-shirt blue +Thinkpad Laptop black +drop table assets ; +# +# Test on fulltext columns +# +CREATE TABLE ft2(copy TEXT,FULLTEXT(copy)) ENGINE=MyISAM; +INSERT INTO ft2(copy) VALUES +('MySQL vs MariaDB database'), +('Oracle vs MariaDB database'), +('PostgreSQL vs MariaDB database'), +('MariaDB overview'), +('Foreign keys'), +('Primary keys'), +('Indexes'), +('Transactions'), +('Triggers'); +SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database'); +copy +MySQL vs MariaDB database +Oracle vs MariaDB database +PostgreSQL vs MariaDB database +update ft2 set copy = (select max(concat('mykeyword ',substr(b.copy,1,5))) from ft2 b WHERE MATCH(b.copy) AGAINST('database')) +where MATCH(copy) AGAINST('keys'); +SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('mykeyword'); +copy +mykeyword Postg +mykeyword Postg +drop table ft2; +# +# Test with MyISAM +# +create table t1 (old_c1 integer, old_c2 integer,c1 integer, c2 integer, c3 integer) engine=MyISAM; +insert t1 (c1,c2,c3) select 0,seq,seq%10 from seq_1_to_500; +insert t1 (c1,c2,c3) select 1,seq,seq%10 from seq_1_to_400; +insert t1 (c1,c2,c3) select 2,seq,seq%10 from seq_1_to_300; +insert t1 (c1,c2,c3) select 3,seq,seq%10 from seq_1_to_200; +create index t1_idx1 on t1(c3); +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Table is already up to date +update t1 set c1=2 where exists (select 'x' from t1); +select count(*) from t1 where c1=2; +count(*) +1400 +update t1 set c1=3 where c3 in (select c3 from t1 b where t1.c3=b.c1); +select count(*) from t1 where c1=3; +count(*) +140 +drop table t1; +# +# Test error on multi_update conversion on view with order by or limit +# +create table t1 (c1 integer) engine=InnoDb; +create table t2 (c1 integer) engine=InnoDb; +create view v1 as select t1.c1 as "t1c1" ,t2.c1 as "t2c1" from t1,t2 where t1.c1=t2.c1; +update v1 set t1c1=2 order by 1; +ERROR 42S22: Unknown column '1' in 'order clause' +update v1 set t1c1=2 limit 1; +drop table t1; +drop table t2; +drop view v1; |