diff options
Diffstat (limited to 'mysql-test/t/multi_update.test')
| -rw-r--r-- | mysql-test/t/multi_update.test | 41 |
1 files changed, 39 insertions, 2 deletions
diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index d0edebe2da3..68d8af87e45 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -4,6 +4,7 @@ --disable_warnings drop table if exists t1,t2,t3; +drop database if exists mysqltest; --enable_warnings create table t1(id1 int not null auto_increment primary key, t char(12)); @@ -259,6 +260,10 @@ update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t2.a=t1 select * from t1; select * from t2; +# test for non-updating table which is also used in sub-select + +update t1,t2 set t1.b=t2.b, t1.a=t2.a where t1.a=t2.a and not exists (select * from t2 where t2.a > 10); + drop table t1,t2; CREATE TABLE t3 ( KEY1 varchar(50) NOT NULL default '', PARAM_CORR_DISTANCE_RUSH double default NULL, PARAM_CORR_DISTANCE_GEM double default NULL, PARAM_AVG_TARE double default NULL, PARAM_AVG_NB_DAYS double default NULL, PARAM_DEFAULT_PROP_GEM_SRVC varchar(50) default NULL, PARAM_DEFAULT_PROP_GEM_NO_ETIK varchar(50) default NULL, PARAM_SCENARIO_COSTS varchar(50) default NULL, PARAM_DEFAULT_WAGON_COST double default NULL, tmp int(11) default NULL, PRIMARY KEY (KEY1)) ENGINE=MyISAM; INSERT INTO t3 VALUES ('A',1,1,22,3.2,'R','R','BASE2',0.24,NULL); @@ -354,7 +359,7 @@ CREATE TABLE t2 ( a int ); DELETE t1 FROM t1, t2 AS t3; DELETE t4 FROM t1, t1 AS t4; DELETE t3 FROM t1 AS t3, t1 AS t4; ---error 1066 +--error 1109 DELETE t1 FROM t1 AS t3, t2 AS t4; INSERT INTO t1 values (1),(2); INSERT INTO t2 values (1),(2); @@ -369,7 +374,6 @@ DROP TABLE t1,t2; # # Test update with const tables # - create table `t1` (`p_id` int(10) unsigned NOT NULL auto_increment, `p_code` varchar(20) NOT NULL default '', `p_active` tinyint(1) unsigned NOT NULL default '1', PRIMARY KEY (`p_id`) ); create table `t2` (`c2_id` int(10) unsigned NULL auto_increment, `c2_p_id` int(10) unsigned NOT NULL default '0', `c2_note` text NOT NULL, `c2_active` tinyint(1) unsigned NOT NULL default '1', PRIMARY KEY (`c2_id`), KEY `c2_p_id` (`c2_p_id`) ); insert into t1 values (0,'A01-Comp',1); @@ -381,6 +385,39 @@ select * from t2; drop table t1, t2; # +# prevelege chexk for multiupdate with other tables +# + +connect (root,localhost,root,,test,$MASTER_MYPORT,master.sock); +connection root; +--disable_warnings +create database mysqltest; +--enable_warnings +create table mysqltest.t1 (a int, b int, primary key (a)); +create table mysqltest.t2 (a int, b int, primary key (a)); +create table mysqltest.t3 (a int, b int, primary key (a)); +grant select on mysqltest.* to mysqltest_1@localhost; +grant update on mysqltest.t1 to mysqltest_1@localhost; +connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,master.sock); +connection user1; +update t1, t2 set t1.b=1 where t1.a=t2.a; +update t1, t2 set t1.b=(select t3.b from t3 where t1.a=t3.a) where t1.a=t2.a; +connection root; +revoke all privileges on mysqltest.t1 from mysqltest_1@localhost; +delete from mysql.user where user='mysqltest_1'; +drop database mysqltest; + +# +# multi delete wrong table check +# +create table t1 (a int, primary key (a)); +create table t2 (a int, primary key (a)); +create table t3 (a int, primary key (a)); +-- error 1109 +delete t1,t3 from t1,t2 where t1.a=t2.a and t2.a=(select t3.a from t3 where t1.a=t3.a); +drop table t1, t2, t3; + +# # Test for bug #1980. # set @ttype_save=@@storage_engine; |