• unknown's avatar
    Add new option "check-testcases" to mysql-test-run.pl · 313ea47d
    unknown authored
    Cleanup the sideeffects from most of the  testcases with sideeffects.
    
    
    mysql-test/mysql-test-run.pl:
      Add option "check-testcases" to mysql-test-run.pl
      Will execute "include/check-testcase.test" once before each tescase and record the output into "var/tmp/check-testcase.result"
      After the teastcase it will run again and this time compare the output with previously recorded file.
    mysql-test/r/analyze.result:
      Drop table t1 at end of test
    mysql-test/r/create_select_tmp.result:
      Drop table t1 at end of test
    mysql-test/r/ctype_cp932.result:
      Drop table t1 at end of test
    mysql-test/r/ctype_recoding.result:
      Drop table t1 at end of test
    mysql-test/r/grant2.result:
      Drop user mysqltest_2 and mysqltest_A@'%'
    mysql-test/r/join_outer.result:
      Drop view v1 to cleanup
    mysql-test/r/ps_1general.result:
      Drop table t1 at end of test
    mysql-test/r/query_cache.result:
      Drop function "f1"
    mysql-test/r/read_only.result:
      Reset the "read_only" flag
    mysql-test/r/rpl000001.result:
      Remove user "blafasel2"
    mysql-test/r/rpl000017.result:
      Remove user "replicate"
    mysql-test/r/rpl_failed_optimize.result:
      Drop table t1 to cleanup
    mysql-test/r/rpl_flush_tables.result:
      Drop tables t3, t4, t5
    mysql-test/r/rpl_ignore_revoke.result:
      Delete user "user_foo"
    mysql-test/r/rpl_insert_id.result:
      Drop table t1 to cleanup
    mysql-test/r/rpl_loaddata.result:
      Drop tyable t1 to cleanup
    mysql-test/r/rpl_loaddata_rule_m.result:
      Drop tyable t1 to cleanup
    mysql-test/r/rpl_loaddata_rule_s.result:
      Drop tyable t1 to cleanup
    mysql-test/r/rpl_misc_functions.result:
      Drop tyable t1 to cleanup
    mysql-test/r/rpl_multi_update3.result:
      Drop tyable t1 and t2 to cleanup
    mysql-test/r/rpl_replicate_do.result:
      Drop tyable t1 to cleanup
    mysql-test/r/rpl_skip_error.result:
      Drop tyable t1 to cleanup
    mysql-test/r/rpl_slave_status.result:
      Drop tyable t1 to cleanup
    mysql-test/r/sp-prelocking.result:
      Drop view v1 and tables t1, t2, t3 and t4 to cleanup
    mysql-test/r/sp-security.result:
      Delete users to cleanup
      Delete remaining traces in tables_priv and procs_priv
    mysql-test/r/subselect_innodb.result:
      Drop procedure p1 to cleanup
    mysql-test/r/trigger-compat.result:
      Drop trigger wl2818_trg1 and wl2818_trg2.
      Drop table t1, t2
      Drop database mysqltest_db1
      And the users "mysqltest_dfn@localhost" and "mysqltest_inv@localhost"
    mysql-test/r/type_bit.result:
      Drop tables t1 and t2 to cleanup
    mysql-test/r/variables.result:
      Set GLOBAL max_join_size to 10 as it originally was in variables-master.opt
    mysql-test/r/view_grant.result:
      Dop user "test@localhost" to cleanup
    mysql-test/t/analyze.test:
      Drop table t1 to cleanup
    mysql-test/t/create_select_tmp.test:
      Drop table t1 to cleanup
    mysql-test/t/ctype_cp932.test:
      Drop table t1 to cleanup
    mysql-test/t/ctype_recoding.test:
      Drop table t1 to cleanup
    mysql-test/t/fulltext_var.test:
      Restore the original ft_boolean_syntax
    mysql-test/t/grant2.test:
      Drop users "mysqltest_2" and "mysqltest_A@'%'" to cleanup
    mysql-test/t/innodb_cache.test:
      Reset query_cache_size to original value
    mysql-test/t/join_outer.test:
      Drop view v1 to cleanup
    mysql-test/t/ps_1general.test:
      Drop table t1 to cleanup
    mysql-test/t/query_cache.test:
      Drop function "f1" to cleanup
    mysql-test/t/read_only.test:
      Reset the readonly flag
    mysql-test/t/rpl000001.test:
      Delete user "blafasel2" to cleanup
    mysql-test/t/rpl000017.test:
      Delete user "replicate" to cleanup
    mysql-test/t/rpl_failed_optimize.test:
      Drop table t1 to cleanup
    mysql-test/t/rpl_flush_tables.test:
      Droip table t3, t4 and t5 to cleanup
    mysql-test/t/rpl_ignore_revoke.test:
      Delet user "user_foo" to cleanup
    mysql-test/t/rpl_insert_id.test:
      drop table t1 to cleanup
    mysql-test/t/rpl_loaddata.test:
      Drop table t1 to cleanup
    mysql-test/t/rpl_loaddata_rule_m.test:
      Drop table t1 to cleanup
    mysql-test/t/rpl_loaddata_rule_s.test:
      Drop table t1 to cleanup
    mysql-test/t/rpl_misc_functions.test:
      Drop table t1 to cleanup
    mysql-test/t/rpl_multi_update3.test:
      Drop table t1 and t2 to cleanup
    mysql-test/t/rpl_replicate_do.test:
      Drop table t1 to cleanup
    mysql-test/t/rpl_skip_error.test:
      Drop table t1 to cleanup
    mysql-test/t/rpl_slave_status.test:
      Drop table t1 to cleanup
    mysql-test/t/sp-prelocking.test:
      Drop table t1, t2 t3 and t4 to cleanup
      Drop view v1
    mysql-test/t/sp-security.test:
      Delete  test users from mysql.user, mysql.db, mysql.procs_priv and mysql.tables_priv
      Drop table t1 to cleanup
    mysql-test/t/subselect_innodb.test:
      Drop procedure p1 to cleanup
    mysql-test/t/trigger-compat.test:
      Drop trigger wl2818_trg1 and wl2818_trg2 to cleanup
      Drop table t1, t2
      Drop users
      drop database mysqltest_db1
    mysql-test/t/type_bit.test:
      drop table t1 and t2 to cleanup
    mysql-test/t/variables-master.opt:
      Increase max_join_size to 100.
    mysql-test/t/variables.test:
      Set max_join_size to 10, which was the original value in variables-master.opt
    mysql-test/t/view_grant.test:
      Drop the user "test@localhost"
    mysql-test/include/check-testcase.test:
      New BitKeeper file ``mysql-test/include/check-testcase.test''
    313ea47d
rpl_multi_update3.result 3.38 KB
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;

-------- Test for BUG#9361 --------
CREATE TABLE t1 (
a int unsigned not null auto_increment primary key,
b int unsigned
) ENGINE=MyISAM;
CREATE TABLE t2 (
a int unsigned not null auto_increment primary key,
b int unsigned
) ENGINE=MyISAM;
INSERT INTO t1 VALUES (NULL, 0);
INSERT INTO t1 SELECT NULL, 0 FROM t1;
INSERT INTO t2 VALUES (NULL, 0), (NULL,1);
SELECT * FROM t1 ORDER BY a;
a	b
1	0
2	0
SELECT * FROM t2 ORDER BY a;
a	b
1	0
2	1
UPDATE t2, (SELECT a FROM t1) AS t SET t2.b = t.a+5 ;
SELECT * FROM t1 ORDER BY a;
a	b
1	0
2	0
SELECT * FROM t2 ORDER BY a;
a	b
1	6
2	6
SELECT * FROM t1 ORDER BY a;
a	b
1	0
2	0
SELECT * FROM t2 ORDER BY a;
a	b
1	6
2	6
drop table t1,t2;

-------- Test 1 for BUG#9361 --------
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1 (
a1  char(30),
a2  int,
a3  int,
a4  char(30),
a5  char(30)
);
CREATE TABLE t2 (
b1  int,
b2  char(30)
);
INSERT INTO t1 VALUES ('Yes', 1, NULL, 'foo', 'bar');
INSERT INTO t2 VALUES (1, 'baz');
UPDATE t1 a, t2 
SET    a.a1 = 'No' 
WHERE  a.a2 = 
(SELECT  b1 
FROM    t2 
WHERE   b2 = 'baz') 
AND a.a3 IS NULL 
AND a.a4 = 'foo' 
AND a.a5 = 'bar';
SELECT * FROM t1;
a1	a2	a3	a4	a5
No	1	NULL	foo	bar
SELECT * FROM t2;
b1	b2
1	baz
DROP TABLE t1, t2;

-------- Test 2 for BUG#9361 --------
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
CREATE TABLE t1 (
i   INT,
j   INT,
x   INT,
y   INT,
z   INT
);
CREATE TABLE t2 (
i   INT,
k   INT,
x   INT,
y   INT,
z   INT
);
CREATE TABLE t3 (
j   INT,
k   INT,
x   INT,
y   INT,
z   INT
);
INSERT INTO t1 VALUES ( 1, 2,13,14,15);
INSERT INTO t2 VALUES ( 1, 3,23,24,25);
INSERT INTO t3 VALUES ( 2, 3, 1,34,35), ( 2, 3, 1,34,36);
UPDATE      t1 AS a  
INNER JOIN  t2 AS b 
ON a.i = b.i
INNER JOIN  t3 AS c 
ON a.j = c.j  AND  b.k = c.k
SET         a.x = b.x, 
a.y = b.y, 
a.z = (
SELECT  sum(z) 
FROM    t3
WHERE   y = 34 
) 
WHERE       b.x = 23;
SELECT * FROM t1;
i	j	x	y	z
1	2	23	24	71
DROP TABLE t1, t2, t3;
DROP TABLE IF EXISTS t1;
Warnings:
Note	1051	Unknown table 't1'
DROP TABLE IF EXISTS t2;
Warnings:
Note	1051	Unknown table 't2'
CREATE TABLE t1 (
idp int(11) NOT NULL default '0',
idpro int(11) default NULL,
price decimal(19,4) default NULL,
PRIMARY KEY (idp)
);
CREATE TABLE t2 (
idpro int(11) NOT NULL default '0',
price decimal(19,4) default NULL,
nbprice int(11) default NULL,
PRIMARY KEY (idpro)
);
INSERT INTO t1 VALUES 
(1,1,'3.0000'),
(2,2,'1.0000'),
(3,1,'1.0000'),
(4,1,'4.0000'),
(5,3,'2.0000'),
(6,2,'4.0000');
INSERT INTO t2 VALUES 
(1,'0.0000',0),
(2,'0.0000',0),
(3,'0.0000',0);
update 
t2
join 
( select    idpro, min(price) as min_price, count(*) as nbr_price
from      t1 
where     idpro>0 and price>0 
group by  idpro
) as table_price
on   t2.idpro = table_price.idpro 
set  t2.price = table_price.min_price, 
t2.nbprice = table_price.nbr_price;
select "-- MASTER AFTER JOIN --" as "";

-- MASTER AFTER JOIN --
select * from t1;
idp	idpro	price
1	1	3.0000
2	2	1.0000
3	1	1.0000
4	1	4.0000
5	3	2.0000
6	2	4.0000
select * from t2;
idpro	price	nbprice
1	1.0000	3
2	1.0000	2
3	2.0000	1
select "-- SLAVE AFTER JOIN --" as "";

-- SLAVE AFTER JOIN --
select * from t1;
idp	idpro	price
1	1	3.0000
2	2	1.0000
3	1	1.0000
4	1	4.0000
5	3	2.0000
6	2	4.0000
select * from t2;
idpro	price	nbprice
1	1.0000	3
2	1.0000	2
3	2.0000	1
drop table t1, t2;