Edit File - File Manager

Edit File: rpl_multi_update3.test

##############################################################################
#
# Let's verify that multi-update with a subselect does not cause the slave to crash
# (BUG#10442)
#
--disable_query_log
SELECT '-------- Test for BUG#9361 --------' as "";
--enable_query_log

eval CREATE TABLE t1 (
 a int unsigned not null auto_increment primary key,
 b int unsigned
) ENGINE=$engine_type;

eval CREATE TABLE t2 (
 a int unsigned not null auto_increment primary key,
 b int unsigned
) ENGINE=$engine_type;

INSERT INTO t1 VALUES (NULL, 0);

--disable_warnings ONCE
INSERT INTO t1 SELECT NULL, 0 FROM t1;

INSERT INTO t2 VALUES (NULL, 0), (NULL,1);

SELECT * FROM t1 ORDER BY a;
SELECT * FROM t2 ORDER BY a;

--disable_warnings ONCE
UPDATE t2, (SELECT a FROM t1 ORDER BY a) AS t SET t2.b = t.a+5 ;
SELECT * FROM t1 ORDER BY a;
SELECT * FROM t2 ORDER BY a;

sync_slave_with_master;
connection slave;
SELECT * FROM t1 ORDER BY a;
SELECT * FROM t2 ORDER BY a;

connection master;
drop table t1,t2;

##############################################################################
#
#  Test for BUG#9361: 
#  Subselects should work inside multi-updates
#
--disable_query_log
SELECT '-------- Test 1 for BUG#9361 --------' as "";
--enable_query_log

connection master;

--disable_warnings
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
--enable_warnings

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 one row per table
INSERT INTO t1 VALUES ('Yes', 1, NULL, 'foo', 'bar');
INSERT INTO t2 VALUES (1, 'baz');

# This should update the row in t1
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';

sync_slave_with_master;
connection slave;
SELECT * FROM t1;
SELECT * FROM t2;

connection master;
DROP TABLE t1, t2;

##############################################################################
#
# Second test for BUG#9361
#

--disable_query_log
SELECT '-------- Test 2 for BUG#9361 --------' as "";
--enable_query_log

connection master;

--disable_warnings
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
--enable_warnings

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;

sync_slave_with_master;
connection slave;

SELECT * FROM t1;

connection master;
DROP TABLE t1, t2, t3;

##############################################################################
#
# BUG#12618
#
# TEST: Replication of a statement containing a join in a multi-update.

DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS 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);

# This update sets t2 to the minimal prices for each product
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 "";
select * from t1;
select * from t2;

sync_slave_with_master;

select "-- SLAVE AFTER JOIN --" as "";
select * from t1;
select * from t2;

connection master;
DROP TABLE t1, t2;
# End of 4.1 tests

Back to File Manager