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 = InnoDB 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 )); 30INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-4, '(pNeg-)subp0'); 31INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, '(pNeg-)subp0'); 32INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, '(pNeg-)subp0'); 33INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-1, '(pNeg-)subp0'); 34INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (4, '(p0-29-)subp3'); 35INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (3, '(p0-29-)subp3'); 36INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (2, '(p0-29-)subp3'); 37INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (1, '(p0-29-)subp3'); 38INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (24, '(p0-29-)subp5'); 39INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (23, '(p0-29-)subp5'); 40INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (22, '(p0-29-)subp5'); 41INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (21, '(p0-29-)subp5'); 42INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (34, '(p30-299-)subp6'); 43INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (33, '(p30-299-)subp6'); 44INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (32, '(p30-299-)subp6'); 45INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (31, '(p30-299-)subp6'); 46INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (234, '(p30-299-)subp8'); 47INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (233, '(p30-299-)subp8'); 48INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (232, '(p30-299-)subp8'); 49INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (231, '(p30-299-)subp8'); 50INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (304, '(p300-2999-)subp8'); 51INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (303, '(p300-2999-)subp8'); 52INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (302, '(p300-2999-)subp8'); 53INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (301, '(p300-2999-)subp8'); 54INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3004, '(p3000-299999-)subp12'); 55INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3003, '(p3000-299999-)subp12'); 56INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3002, '(p3000-299999-)subp12'); 57INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3001, '(p3000-299999-)subp12'); 58INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299996, '(p3000-299999-)subp14'); 59INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299997, '(p3000-299999-)subp14'); 60INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299998, '(p3000-299999-)subp14'); 61INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299999, '(p3000-299999-)subp14'); 62CREATE USER test_user_1 IDENTIFIED BY 'testpw'; 63CREATE USER test_user_2 IDENTIFIED BY 'testpw'; 64GRANT ALL PRIVILEGES ON test.* TO test_user_1; 65GRANT ALL PRIVILEGES ON test.* TO test_user_2; 66connect session1, localhost, test_user_1,'testpw',test; 67SELECT * FROM t1 PARTITION (`p0-29`); 68a b 691 (p0-29-)subp3 702 (p0-29-)subp3 7121 (p0-29-)subp5 7222 (p0-29-)subp5 7323 (p0-29-)subp5 7424 (p0-29-)subp5 753 (p0-29-)subp3 764 (p0-29-)subp3 77send 78UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-1' WHERE a BETWEEN 0 AND 9; 79connect session2, localhost, test_user_2,'testpw',test; 80SELECT * FROM t1 PARTITION (`p30-299`); 81a b 82231 (p30-299-)subp8 83232 (p30-299-)subp8 84233 (p30-299-)subp8 85234 (p30-299-)subp8 8631 (p30-299-)subp6 8732 (p30-299-)subp6 8833 (p30-299-)subp6 8934 (p30-299-)subp6 90UPDATE t1 PARTITION (`p30-299`) SET b='p30-299-upd' WHERE a BETWEEN 30 AND 39; 91SELECT * FROM t1 PARTITION (`p30-299`); 92a b 93231 (p30-299-)subp8 94232 (p30-299-)subp8 95233 (p30-299-)subp8 96234 (p30-299-)subp8 9731 p30-299-upd 9832 p30-299-upd 9933 p30-299-upd 10034 p30-299-upd 101SELECT * FROM t1 PARTITION (`p300-2999`); 102a b 103301 (p300-2999-)subp8 104302 (p300-2999-)subp8 105303 (p300-2999-)subp8 106304 (p300-2999-)subp8 107UPDATE t1 PARTITION (`p300-2999`) SET b='p300-2999-upd' WHERE a BETWEEN 300 AND 309; 108SELECT * FROM t1 PARTITION (`p300-2999`); 109a b 110301 p300-2999-upd 111302 p300-2999-upd 112303 p300-2999-upd 113304 p300-2999-upd 114SELECT * FROM t1 PARTITION (`p3000-299999`); 115a b 116299996 (p3000-299999-)subp14 117299997 (p3000-299999-)subp14 118299998 (p3000-299999-)subp14 119299999 (p3000-299999-)subp14 1203001 (p3000-299999-)subp12 1213002 (p3000-299999-)subp12 1223003 (p3000-299999-)subp12 1233004 (p3000-299999-)subp12 124UPDATE t1 PARTITION (`p3000-299999`) SET b='p3000-299999-upd' WHERE a BETWEEN 3000 AND 3009; 125SELECT * FROM t1 PARTITION (`p3000-299999`); 126a b 127299996 (p3000-299999-)subp14 128299997 (p3000-299999-)subp14 129299998 (p3000-299999-)subp14 130299999 (p3000-299999-)subp14 1313001 p3000-299999-upd 1323002 p3000-299999-upd 1333003 p3000-299999-upd 1343004 p3000-299999-upd 135UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-2' WHERE a BETWEEN 0 AND 9; 136SELECT * FROM t1 PARTITION (`p0-29`); 137a b 1381 p0-29-upd-2 1392 p0-29-upd-2 14021 (p0-29-)subp5 14122 (p0-29-)subp5 14223 (p0-29-)subp5 14324 (p0-29-)subp5 1443 p0-29-upd-2 1454 p0-29-upd-2 146UPDATE t1 PARTITION (subp3) SET b='subp3-upd'; 147UPDATE t1 PARTITION (subp4) SET b='subp4-upd'; 148UPDATE t1 PARTITION (subp5) SET b='subp5-upd'; 149SELECT * FROM t1 PARTITION (subp3); 150a b 15121 subp3-upd 15224 subp3-upd 1533 subp3-upd 154SELECT * FROM t1 PARTITION (subp4); 155a b 1561 subp4-upd 15722 subp4-upd 1584 subp4-upd 159SELECT * FROM t1 PARTITION (subp5); 160a b 1612 subp5-upd 16223 subp5-upd 163SELECT * FROM t1 PARTITION (subp4,subp5,subp3) order by b,a; 164a b 1653 subp3-upd 16621 subp3-upd 16724 subp3-upd 1681 subp4-upd 1694 subp4-upd 17022 subp4-upd 1712 subp5-upd 17223 subp5-upd 173connection session1; 174reap 175SELECT * FROM t1 PARTITION (`p0-29`); 176a b 1771 subp4-upd 1782 subp5-upd 17921 subp3-upd 18022 subp4-upd 18123 subp5-upd 18224 subp3-upd 1833 subp3-upd 1844 subp4-upd 185connection session2; 186SELECT * FROM t1 PARTITION (`p0-29`); 187a b 1881 subp4-upd 1892 subp5-upd 19021 subp3-upd 19122 subp4-upd 19223 subp5-upd 19324 subp3-upd 1943 subp3-upd 1954 subp4-upd 196SELECT 197(SELECT count(*) FROM t1 PARTITION (pneg,`p0-29`,`p30-299`,`p300-2999`,`p3000-299999`)) = 198(SELECT count(*) FROM t1) as `MUST BE TRUE (1)` ; 199MUST BE TRUE (1) 2001 201CREATE TABLE t2 202(a INT NOT NULL, 203b varchar (64), 204INDEX ind_t2 (b,a), 205PRIMARY KEY (a)) 206ENGINE = InnoDB 207PARTITION BY RANGE (a) 208SUBPARTITION BY HASH (a) SUBPARTITIONS 3 209(PARTITION pNeg VALUES LESS THAN (0) 210(SUBPARTITION subp0 , 211SUBPARTITION subp1 , 212SUBPARTITION subp2 ), 213PARTITION `p0-29` VALUES LESS THAN (30) 214(SUBPARTITION subp3 , 215SUBPARTITION subp4 , 216SUBPARTITION subp5 ), 217PARTITION `p30-299` VALUES LESS THAN (300) 218(SUBPARTITION subp6 , 219SUBPARTITION subp7 , 220SUBPARTITION subp8 ), 221PARTITION `p300-2999` VALUES LESS THAN (3000) 222(SUBPARTITION subp9 , 223SUBPARTITION subp10 , 224SUBPARTITION subp11 ), 225PARTITION `p3000-299999` VALUES LESS THAN (300000) 226(SUBPARTITION subp12 , 227SUBPARTITION subp13 , 228SUBPARTITION subp14 )); 229SELECT * FROM t2 PARTITION (`p30-299`); 230a b 231UPDATE t2 PARTITION (`p30-299`) SET b='p30-299-upd' WHERE a BETWEEN 30 AND 39; 232DELETE FROM t2 PARTITION (`p30-299`) WHERE a BETWEEN 30 AND 39; 233SELECT * FROM t2 PARTITION (`p300-2999`); 234a b 235UPDATE t2 PARTITION (`p300-2999`) SET b='p300-2999-upd' WHERE a BETWEEN 300 AND 309; 236DELETE FROM t2 PARTITION (`p300-2999`) WHERE a BETWEEN 300 AND 309; 237SELECT * FROM t2 PARTITION (`p3000-299999`); 238a b 239UPDATE t2 PARTITION (`p3000-299999`) SET b='p3000-299999-upd' WHERE a BETWEEN 3000 AND 3009; 240DELETE FROM t2 PARTITION (`p3000-299999`) WHERE a BETWEEN 3000 AND 3009; 241SELECT * FROM t2 PARTITION (`p0-29`); 242a b 243UPDATE t2 PARTITION (`p0-29`) SET b='p0-29-upd-2' WHERE a BETWEEN 0 AND 9; 244DELETE FROM t2 PARTITION (`p0-29`) WHERE a BETWEEN 0 AND 9; 245UPDATE t2 PARTITION (subp3) SET b='subp3-upd'; 246DELETE FROM t2 PARTITION (subp3); 247UPDATE t2 PARTITION (subp4) SET b='subp4-upd'; 248DELETE FROM t2 PARTITION (subp4); 249UPDATE t2 PARTITION (subp5) SET b='subp5-upd'; 250DELETE FROM t2 PARTITION (subp5); 251UPDATE t2 PARTITION (subp4,subp5,subp3) SET b='subp-upd'; 252DELETE FROM t2 PARTITION (subp4,subp5,subp3); 253SELECT * FROM t2 PARTITION (subp3); 254a b 255SELECT * FROM t2 PARTITION (subp4); 256a b 257SELECT * FROM t2 PARTITION (subp5); 258a b 259disconnect session1; 260disconnect session2; 261connection default; 262DROP TABLE t1, t2; 263DROP USER test_user_1; 264DROP USER test_user_2; 265