1# Additional tests for WL#5217 by QA, testplan 1.1 2CREATE TABLE t1 3(a INT NOT NULL, 4b varchar (64), 5INDEX ind_t1 (b,a), 6PRIMARY KEY (a)) 7ENGINE = MyISAM 8PARTITION BY RANGE (a) 9SUBPARTITION BY HASH (a) SUBPARTITIONS 3 10(PARTITION pNeg VALUES LESS THAN (0) 11(SUBPARTITION subp0 , 12SUBPARTITION subp1 , 13SUBPARTITION subp2 ), 14PARTITION `p0-29` VALUES LESS THAN (30) 15(SUBPARTITION subp3 , 16SUBPARTITION subp4 , 17SUBPARTITION subp5 ), 18PARTITION `p30-299` VALUES LESS THAN (300) 19(SUBPARTITION subp6 , 20SUBPARTITION subp7 , 21SUBPARTITION subp8 ), 22PARTITION `p300-2999` VALUES LESS THAN (3000) 23(SUBPARTITION subp9 , 24SUBPARTITION subp10 , 25SUBPARTITION subp11 ), 26PARTITION `p3000-299999` VALUES LESS THAN (300000) 27(SUBPARTITION subp12 , 28SUBPARTITION subp13 , 29SUBPARTITION subp14 )); 30Warnings: 31Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 32INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-4, '(pNeg-)subp0'); 33Warnings: 34Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 35INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, '(pNeg-)subp0'); 36INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, '(pNeg-)subp0'); 37INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-1, '(pNeg-)subp0'); 38INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (4, '(p0-29-)subp3'); 39INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (3, '(p0-29-)subp3'); 40INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (2, '(p0-29-)subp3'); 41INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (1, '(p0-29-)subp3'); 42INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (24, '(p0-29-)subp5'); 43INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (23, '(p0-29-)subp5'); 44INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (22, '(p0-29-)subp5'); 45INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (21, '(p0-29-)subp5'); 46INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (34, '(p30-299-)subp6'); 47INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (33, '(p30-299-)subp6'); 48INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (32, '(p30-299-)subp6'); 49INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (31, '(p30-299-)subp6'); 50INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (234, '(p30-299-)subp8'); 51INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (233, '(p30-299-)subp8'); 52INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (232, '(p30-299-)subp8'); 53INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (231, '(p30-299-)subp8'); 54INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (304, '(p300-2999-)subp8'); 55INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (303, '(p300-2999-)subp8'); 56INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (302, '(p300-2999-)subp8'); 57INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (301, '(p300-2999-)subp8'); 58INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3004, '(p3000-299999-)subp12'); 59INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3003, '(p3000-299999-)subp12'); 60INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3002, '(p3000-299999-)subp12'); 61INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3001, '(p3000-299999-)subp12'); 62INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299996, '(p3000-299999-)subp14'); 63INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299997, '(p3000-299999-)subp14'); 64INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299998, '(p3000-299999-)subp14'); 65INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299999, '(p3000-299999-)subp14'); 66CREATE TABLE t2 67(a INT NOT NULL, 68b varchar (64), 69INDEX ind_t2 (b,a), 70PRIMARY KEY (a)) 71ENGINE = MyISAM 72PARTITION BY RANGE (a) 73SUBPARTITION BY HASH (a) SUBPARTITIONS 3 74(PARTITION pNeg VALUES LESS THAN (0) 75(SUBPARTITION subp0 , 76SUBPARTITION subp1 , 77SUBPARTITION subp2 ), 78PARTITION `p0-29` VALUES LESS THAN (30) 79(SUBPARTITION subp3 , 80SUBPARTITION subp4 , 81SUBPARTITION subp5 ), 82PARTITION `p30-299` VALUES LESS THAN (300) 83(SUBPARTITION subp6 , 84SUBPARTITION subp7 , 85SUBPARTITION subp8 ), 86PARTITION `p300-2999` VALUES LESS THAN (3000) 87(SUBPARTITION subp9 , 88SUBPARTITION subp10 , 89SUBPARTITION subp11 ), 90PARTITION `p3000-299999` VALUES LESS THAN (300000) 91(SUBPARTITION subp12 , 92SUBPARTITION subp13 , 93SUBPARTITION subp14 )); 94Warnings: 95Warning 1287 The partition engine, used by table 'test.t2', is deprecated and will be removed in a future release. Please use native partitioning instead. 96CREATE 97PROCEDURE p1 () 98BEGIN 99DECLARE c11,c21 int; 100DECLARE c12,c22 varchar(64); 101DECLARE cur1 CURSOR FOR SELECT * FROM t1 PARTITION (pneg,`p0-29`,`p30-299`,`p300-2999`,`p3000-299999`) ORDER BY a,b; 102DECLARE cur2 CURSOR FOR SELECT * FROM t1 ORDER BY a,b; 103DECLARE CONTINUE HANDLER FOR NOT FOUND SET @done = 1; 104OPEN cur1; 105OPEN cur2; 106read_loop: LOOP 107FETCH cur1 INTO c11,c12; 108FETCH cur2 INTO c21,c22; 109IF !((c11=c21) AND (c12=c22)) THEN 110SELECT c11,c12,c21,c22; 111LEAVE read_loop; 112END IF; 113IF @done THEN 114LEAVE read_loop; 115END IF; 116END LOOP; 117CLOSE cur1; 118CLOSE cur2; 119END// 120CREATE PROCEDURE p2 () 121BEGIN 122UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd' WHERE a BETWEEN 3 AND 10; 123END// 124CREATE PROCEDURE p3 () 125BEGIN 126DELETE FROM t1 PARTITION (`p0-29`) WHERE b= 'p0-29-upd'; 127END// 128CREATE TRIGGER tr1 AFTER INSERT ON t2 129FOR EACH ROW BEGIN 130UPDATE t1 PARTITION (`p30-299`) SET b='p30-299-upd-trigger' WHERE a BETWEEN 30 AND 40; 131END// 132Warnings: 133Warning 1287 The partition engine, used by table 'test.t2', is deprecated and will be removed in a future release. Please use native partitioning instead. 134CALL p1; 135CALL p2; 136SELECT * FROM t1 PARTITION (`p0-29`) WHERE a BETWEEN 3 AND 10 ORDER BY a,b ; 137a b 1383 p0-29-upd 1394 p0-29-upd 140SELECT * FROM t1 WHERE a BETWEEN 3 AND 10 ORDER BY a,b ; 141a b 1423 p0-29-upd 1434 p0-29-upd 144CALL p3; 145SELECT * FROM t1 PARTITION (`p0-29`) WHERE a BETWEEN 3 AND 10 ORDER BY a,b ; 146a b 147CALL p3; 148INSERT INTO t2 PARTITION (`p0-29`, subp3) VALUES (4, '(p0-29-)subp3'); 149Warnings: 150Warning 1287 The partition engine, used by table 'test.t2', is deprecated and will be removed in a future release. Please use native partitioning instead. 151SELECT * FROM t2 PARTITION (`p0-29`) WHERE a BETWEEN 3 AND 10 ORDER BY a,b ; 152a b 1534 (p0-29-)subp3 154SELECT * FROM t1 PARTITION (`p30-299`) WHERE a BETWEEN 30 AND 40 ORDER BY a,b ; 155a b 15631 p30-299-upd-trigger 15732 p30-299-upd-trigger 15833 p30-299-upd-trigger 15934 p30-299-upd-trigger 160DELETE FROM t1 PARTITION (pneg, `p30-299`); 161SELECT * FROM t1 ORDER BY a,b; 162a b 1631 (p0-29-)subp3 1642 (p0-29-)subp3 16521 (p0-29-)subp5 16622 (p0-29-)subp5 16723 (p0-29-)subp5 16824 (p0-29-)subp5 169301 (p300-2999-)subp8 170302 (p300-2999-)subp8 171303 (p300-2999-)subp8 172304 (p300-2999-)subp8 1733001 (p3000-299999-)subp12 1743002 (p3000-299999-)subp12 1753003 (p3000-299999-)subp12 1763004 (p3000-299999-)subp12 177299996 (p3000-299999-)subp14 178299997 (p3000-299999-)subp14 179299998 (p3000-299999-)subp14 180299999 (p3000-299999-)subp14 181########## Empty table t1 ######### 182DELETE FROM t1; 183CALL p1; 184CALL p2; 185CALL p3; 186DROP PROCEDURE p1; 187DROP PROCEDURE p2; 188DROP PROCEDURE p3; 189DROP TRIGGER tr1; 190DROP TABLE t1; 191DROP TABLE t2; 192