1SET GLOBAL innodb_strict_mode=OFF; 2SET innodb_strict_mode=OFF; 3# Additional tests for WL#5217 by QA, testplan 1.1 4INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-4, '(pNeg-)subp0'); 5INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, '(pNeg-)subp0'); 6INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, '(pNeg-)subp0'); 7INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-1, '(pNeg-)subp0'); 8INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (4, '(p0-29-)subp3'); 9INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (3, '(p0-29-)subp3'); 10INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (2, '(p0-29-)subp3'); 11INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (1, '(p0-29-)subp3'); 12INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (24, '(p0-29-)subp5'); 13INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (23, '(p0-29-)subp5'); 14INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (22, '(p0-29-)subp5'); 15INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (21, '(p0-29-)subp5'); 16INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (34, '(p30-299-)subp6'); 17INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (33, '(p30-299-)subp6'); 18INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (32, '(p30-299-)subp6'); 19INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (31, '(p30-299-)subp6'); 20INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (234, '(p30-299-)subp8'); 21INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (233, '(p30-299-)subp8'); 22INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (232, '(p30-299-)subp8'); 23INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (231, '(p30-299-)subp8'); 24INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (304, '(p300-2999-)subp8'); 25INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (303, '(p300-2999-)subp8'); 26INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (302, '(p300-2999-)subp8'); 27INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (301, '(p300-2999-)subp8'); 28INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3004, '(p3000-299999-)subp12'); 29INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3003, '(p3000-299999-)subp12'); 30INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3002, '(p3000-299999-)subp12'); 31INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3001, '(p3000-299999-)subp12'); 32INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299996, '(p3000-299999-)subp14'); 33INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299997, '(p3000-299999-)subp14'); 34INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299998, '(p3000-299999-)subp14'); 35INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299999, '(p3000-299999-)subp14'); 36CREATE USER test_user_1 IDENTIFIED BY 'testpw'; 37CREATE USER test_user_2 IDENTIFIED BY 'testpw'; 38GRANT ALL PRIVILEGES ON test.* TO test_user_1; 39GRANT ALL PRIVILEGES ON test.* TO test_user_2; 40# FILE is needed to use the DATA DIRECTORY or INDEX DIRECTORY option 41# Need to disable warnings as this gives warning 1287 except when 42# running in embedded mode 43GRANT FILE ON *.* TO test_user_2 IDENTIFIED BY 'testpw'; 44connect session1, localhost, test_user_1,'testpw',test; 45SELECT * FROM t1 PARTITION (`p0-29`); 46a b 471 (p0-29-)subp3 482 (p0-29-)subp3 4921 (p0-29-)subp5 5022 (p0-29-)subp5 5123 (p0-29-)subp5 5224 (p0-29-)subp5 533 (p0-29-)subp3 544 (p0-29-)subp3 55send 56UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-1' WHERE a BETWEEN 0 AND 9; 57connect session2, localhost, test_user_2,'testpw',test; 58SELECT * FROM t1 PARTITION (`p30-299`); 59a b 60231 (p30-299-)subp8 61232 (p30-299-)subp8 62233 (p30-299-)subp8 63234 (p30-299-)subp8 6431 (p30-299-)subp6 6532 (p30-299-)subp6 6633 (p30-299-)subp6 6734 (p30-299-)subp6 68UPDATE t1 PARTITION (`p30-299`) SET b='p30-299-upd' WHERE a BETWEEN 30 AND 39; 69SELECT * FROM t1 PARTITION (`p30-299`); 70a b 71231 (p30-299-)subp8 72232 (p30-299-)subp8 73233 (p30-299-)subp8 74234 (p30-299-)subp8 7531 p30-299-upd 7632 p30-299-upd 7733 p30-299-upd 7834 p30-299-upd 79SELECT * FROM t1 PARTITION (`p300-2999`); 80a b 81301 (p300-2999-)subp8 82302 (p300-2999-)subp8 83303 (p300-2999-)subp8 84304 (p300-2999-)subp8 85UPDATE t1 PARTITION (`p300-2999`) SET b='p300-2999-upd' WHERE a BETWEEN 300 AND 309; 86SELECT * FROM t1 PARTITION (`p300-2999`); 87a b 88301 p300-2999-upd 89302 p300-2999-upd 90303 p300-2999-upd 91304 p300-2999-upd 92SELECT * FROM t1 PARTITION (`p3000-299999`); 93a b 94299996 (p3000-299999-)subp14 95299997 (p3000-299999-)subp14 96299998 (p3000-299999-)subp14 97299999 (p3000-299999-)subp14 983001 (p3000-299999-)subp12 993002 (p3000-299999-)subp12 1003003 (p3000-299999-)subp12 1013004 (p3000-299999-)subp12 102UPDATE t1 PARTITION (`p3000-299999`) SET b='p3000-299999-upd' WHERE a BETWEEN 3000 AND 3009; 103SELECT * FROM t1 PARTITION (`p3000-299999`); 104a b 105299996 (p3000-299999-)subp14 106299997 (p3000-299999-)subp14 107299998 (p3000-299999-)subp14 108299999 (p3000-299999-)subp14 1093001 p3000-299999-upd 1103002 p3000-299999-upd 1113003 p3000-299999-upd 1123004 p3000-299999-upd 113UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-2' WHERE a BETWEEN 0 AND 9; 114SELECT * FROM t1 PARTITION (`p0-29`); 115a b 1161 p0-29-upd-2 1172 p0-29-upd-2 11821 (p0-29-)subp5 11922 (p0-29-)subp5 12023 (p0-29-)subp5 12124 (p0-29-)subp5 1223 p0-29-upd-2 1234 p0-29-upd-2 124UPDATE t1 PARTITION (subp3) SET b='subp3-upd'; 125UPDATE t1 PARTITION (subp4) SET b='subp4-upd'; 126UPDATE t1 PARTITION (subp5) SET b='subp5-upd'; 127SELECT * FROM t1 PARTITION (subp3); 128a b 12921 subp3-upd 13024 subp3-upd 1313 subp3-upd 132SELECT * FROM t1 PARTITION (subp4); 133a b 1341 subp4-upd 13522 subp4-upd 1364 subp4-upd 137SELECT * FROM t1 PARTITION (subp5); 138a b 1392 subp5-upd 14023 subp5-upd 141SELECT * FROM t1 PARTITION (subp4,subp5,subp3) order by b,a; 142a b 1433 subp3-upd 14421 subp3-upd 14524 subp3-upd 1461 subp4-upd 1474 subp4-upd 14822 subp4-upd 1492 subp5-upd 15023 subp5-upd 151connection session1; 152reap 153SELECT * FROM t1 PARTITION (`p0-29`); 154a b 1551 subp4-upd 1562 subp5-upd 15721 subp3-upd 15822 subp4-upd 15923 subp5-upd 16024 subp3-upd 1613 subp3-upd 1624 subp4-upd 163connection session2; 164SELECT * FROM t1 PARTITION (`p0-29`); 165a b 1661 subp4-upd 1672 subp5-upd 16821 subp3-upd 16922 subp4-upd 17023 subp5-upd 17124 subp3-upd 1723 subp3-upd 1734 subp4-upd 174SELECT 175(SELECT count(*) FROM t1 PARTITION (pneg,`p0-29`,`p30-299`,`p300-2999`,`p3000-299999`)) = 176(SELECT count(*) FROM t1) as `MUST BE TRUE (1)` ; 177MUST BE TRUE (1) 1781 179SELECT * FROM t2 PARTITION (`p30-299`); 180a b 181UPDATE t2 PARTITION (`p30-299`) SET b='p30-299-upd' WHERE a BETWEEN 30 AND 39; 182DELETE FROM t2 PARTITION (`p30-299`) WHERE a BETWEEN 30 AND 39; 183SELECT * FROM t2 PARTITION (`p300-2999`); 184a b 185UPDATE t2 PARTITION (`p300-2999`) SET b='p300-2999-upd' WHERE a BETWEEN 300 AND 309; 186DELETE FROM t2 PARTITION (`p300-2999`) WHERE a BETWEEN 300 AND 309; 187SELECT * FROM t2 PARTITION (`p3000-299999`); 188a b 189UPDATE t2 PARTITION (`p3000-299999`) SET b='p3000-299999-upd' WHERE a BETWEEN 3000 AND 3009; 190DELETE FROM t2 PARTITION (`p3000-299999`) WHERE a BETWEEN 3000 AND 3009; 191SELECT * FROM t2 PARTITION (`p0-29`); 192a b 193UPDATE t2 PARTITION (`p0-29`) SET b='p0-29-upd-2' WHERE a BETWEEN 0 AND 9; 194DELETE FROM t2 PARTITION (`p0-29`) WHERE a BETWEEN 0 AND 9; 195UPDATE t2 PARTITION (subp3) SET b='subp3-upd'; 196DELETE FROM t2 PARTITION (subp3); 197UPDATE t2 PARTITION (subp4) SET b='subp4-upd'; 198DELETE FROM t2 PARTITION (subp4); 199UPDATE t2 PARTITION (subp5) SET b='subp5-upd'; 200DELETE FROM t2 PARTITION (subp5); 201UPDATE t2 PARTITION (subp4,subp5,subp3) SET b='subp-upd'; 202DELETE FROM t2 PARTITION (subp4,subp5,subp3); 203SELECT * FROM t2 PARTITION (subp3); 204a b 205SELECT * FROM t2 PARTITION (subp4); 206a b 207SELECT * FROM t2 PARTITION (subp5); 208a b 209disconnect session1; 210disconnect session2; 211connection default; 212DROP TABLE t1, t2; 213DROP USER test_user_1; 214DROP USER test_user_2; 215SET GLOBAL innodb_strict_mode=default; 216