Edit File: partition-dml-1-2-innodb.result
# Additional tests for WL#5217 by QA, testplan 1.1 CREATE TABLE t1 (a INT NOT NULL, b varchar (64), INDEX ind_t1 (b,a), PRIMARY KEY (a)) ENGINE = InnoDB PARTITION BY RANGE (a) SUBPARTITION BY HASH (a) SUBPARTITIONS 3 (PARTITION pNeg VALUES LESS THAN (0) (SUBPARTITION subp0 , SUBPARTITION subp1 , SUBPARTITION subp2 ), PARTITION `p0-29` VALUES LESS THAN (30) (SUBPARTITION subp3 , SUBPARTITION subp4 , SUBPARTITION subp5 ), PARTITION `p30-299` VALUES LESS THAN (300) (SUBPARTITION subp6 , SUBPARTITION subp7 , SUBPARTITION subp8 ), PARTITION `p300-2999` VALUES LESS THAN (3000) (SUBPARTITION subp9 , SUBPARTITION subp10 , SUBPARTITION subp11 ), PARTITION `p3000-299999` VALUES LESS THAN (300000) (SUBPARTITION subp12 , SUBPARTITION subp13 , SUBPARTITION subp14 )); INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-4, '(pNeg-)subp0'); INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, '(pNeg-)subp0'); INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, '(pNeg-)subp0'); INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-1, '(pNeg-)subp0'); INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (4, '(p0-29-)subp3'); INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (3, '(p0-29-)subp3'); INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (2, '(p0-29-)subp3'); INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (1, '(p0-29-)subp3'); INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (24, '(p0-29-)subp5'); INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (23, '(p0-29-)subp5'); INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (22, '(p0-29-)subp5'); INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (21, '(p0-29-)subp5'); INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (34, '(p30-299-)subp6'); INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (33, '(p30-299-)subp6'); INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (32, '(p30-299-)subp6'); INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (31, '(p30-299-)subp6'); INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (234, '(p30-299-)subp8'); INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (233, '(p30-299-)subp8'); INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (232, '(p30-299-)subp8'); INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (231, '(p30-299-)subp8'); INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (304, '(p300-2999-)subp8'); INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (303, '(p300-2999-)subp8'); INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (302, '(p300-2999-)subp8'); INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (301, '(p300-2999-)subp8'); INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3004, '(p3000-299999-)subp12'); INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3003, '(p3000-299999-)subp12'); INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3002, '(p3000-299999-)subp12'); INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3001, '(p3000-299999-)subp12'); INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299996, '(p3000-299999-)subp14'); INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299997, '(p3000-299999-)subp14'); INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299998, '(p3000-299999-)subp14'); INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299999, '(p3000-299999-)subp14'); CREATE TABLE t2 (a INT NOT NULL, b varchar (64), INDEX ind_t2 (b,a), PRIMARY KEY (a)) ENGINE = InnoDB PARTITION BY RANGE (a) SUBPARTITION BY HASH (a) SUBPARTITIONS 3 (PARTITION pNeg VALUES LESS THAN (0) (SUBPARTITION subp0 , SUBPARTITION subp1 , SUBPARTITION subp2 ), PARTITION `p0-29` VALUES LESS THAN (30) (SUBPARTITION subp3 , SUBPARTITION subp4 , SUBPARTITION subp5 ), PARTITION `p30-299` VALUES LESS THAN (300) (SUBPARTITION subp6 , SUBPARTITION subp7 , SUBPARTITION subp8 ), PARTITION `p300-2999` VALUES LESS THAN (3000) (SUBPARTITION subp9 , SUBPARTITION subp10 , SUBPARTITION subp11 ), PARTITION `p3000-299999` VALUES LESS THAN (300000) (SUBPARTITION subp12 , SUBPARTITION subp13 , SUBPARTITION subp14 )); CREATE PROCEDURE p1 () BEGIN DECLARE c11,c21 int; DECLARE c12,c22 varchar(64); DECLARE cur1 CURSOR FOR SELECT * FROM t1 PARTITION (pneg,`p0-29`,`p30-299`,`p300-2999`,`p3000-299999`) ORDER BY a,b; DECLARE cur2 CURSOR FOR SELECT * FROM t1 ORDER BY a,b; DECLARE CONTINUE HANDLER FOR NOT FOUND SET @done = 1; OPEN cur1; OPEN cur2; read_loop: LOOP FETCH cur1 INTO c11,c12; FETCH cur2 INTO c21,c22; IF !((c11=c21) AND (c12=c22)) THEN SELECT c11,c12,c21,c22; LEAVE read_loop; END IF; IF @done THEN LEAVE read_loop; END IF; END LOOP; CLOSE cur1; CLOSE cur2; END// CREATE PROCEDURE p2 () BEGIN UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd' WHERE a BETWEEN 3 AND 10; END// CREATE PROCEDURE p3 () BEGIN DELETE FROM t1 PARTITION (`p0-29`) WHERE b= 'p0-29-upd'; END// CREATE TRIGGER tr1 AFTER INSERT ON t2 FOR EACH ROW BEGIN UPDATE t1 PARTITION (`p30-299`) SET b='p30-299-upd-trigger' WHERE a BETWEEN 30 AND 40; END// CALL p1; CALL p2; SELECT * FROM t1 PARTITION (`p0-29`) WHERE a BETWEEN 3 AND 10 ORDER BY a,b ; a b 3 p0-29-upd 4 p0-29-upd SELECT * FROM t1 WHERE a BETWEEN 3 AND 10 ORDER BY a,b ; a b 3 p0-29-upd 4 p0-29-upd CALL p3; SELECT * FROM t1 PARTITION (`p0-29`) WHERE a BETWEEN 3 AND 10 ORDER BY a,b ; a b CALL p3; INSERT INTO t2 PARTITION (`p0-29`, subp3) VALUES (4, '(p0-29-)subp3'); SELECT * FROM t2 PARTITION (`p0-29`) WHERE a BETWEEN 3 AND 10 ORDER BY a,b ; a b 4 (p0-29-)subp3 SELECT * FROM t1 PARTITION (`p30-299`) WHERE a BETWEEN 30 AND 40 ORDER BY a,b ; a b 31 p30-299-upd-trigger 32 p30-299-upd-trigger 33 p30-299-upd-trigger 34 p30-299-upd-trigger DELETE FROM t1 PARTITION (pneg, `p30-299`); SELECT * FROM t1 ORDER BY a,b; a b 1 (p0-29-)subp3 2 (p0-29-)subp3 21 (p0-29-)subp5 22 (p0-29-)subp5 23 (p0-29-)subp5 24 (p0-29-)subp5 301 (p300-2999-)subp8 302 (p300-2999-)subp8 303 (p300-2999-)subp8 304 (p300-2999-)subp8 3001 (p3000-299999-)subp12 3002 (p3000-299999-)subp12 3003 (p3000-299999-)subp12 3004 (p3000-299999-)subp12 299996 (p3000-299999-)subp14 299997 (p3000-299999-)subp14 299998 (p3000-299999-)subp14 299999 (p3000-299999-)subp14 ########## Empty table t1 ######### DELETE FROM t1; CALL p1; CALL p2; CALL p3; DROP PROCEDURE p1; DROP PROCEDURE p2; DROP PROCEDURE p3; DROP TRIGGER tr1; DROP TABLE t1; DROP TABLE t2;