1# Additional tests for WL#5217 by QA, testplan 1.1 2INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-4, '(pNeg-)subp0'); 3INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, '(pNeg-)subp0'); 4INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, '(pNeg-)subp0'); 5INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-1, '(pNeg-)subp0'); 6INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (4, '(p0-29-)subp3'); 7INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (3, '(p0-29-)subp3'); 8INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (2, '(p0-29-)subp3'); 9INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (1, '(p0-29-)subp3'); 10INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (24, '(p0-29-)subp5'); 11INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (23, '(p0-29-)subp5'); 12INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (22, '(p0-29-)subp5'); 13INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (21, '(p0-29-)subp5'); 14INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (34, '(p30-299-)subp6'); 15INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (33, '(p30-299-)subp6'); 16INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (32, '(p30-299-)subp6'); 17INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (31, '(p30-299-)subp6'); 18INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (234, '(p30-299-)subp8'); 19INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (233, '(p30-299-)subp8'); 20INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (232, '(p30-299-)subp8'); 21INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (231, '(p30-299-)subp8'); 22INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (304, '(p300-2999-)subp8'); 23INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (303, '(p300-2999-)subp8'); 24INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (302, '(p300-2999-)subp8'); 25INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (301, '(p300-2999-)subp8'); 26INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3004, '(p3000-299999-)subp12'); 27INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3003, '(p3000-299999-)subp12'); 28INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3002, '(p3000-299999-)subp12'); 29INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3001, '(p3000-299999-)subp12'); 30INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299996, '(p3000-299999-)subp14'); 31INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299997, '(p3000-299999-)subp14'); 32INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299998, '(p3000-299999-)subp14'); 33INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299999, '(p3000-299999-)subp14'); 34GRANT ALL PRIVILEGES ON test.* TO test_user_1 IDENTIFIED BY 'testpw'; 35GRANT ALL PRIVILEGES ON test.* TO test_user_2 IDENTIFIED BY 'testpw'; 36# FILE is needed to use the DATA DIRECTORY or INDEX DIRECTORY option 37GRANT FILE ON *.* TO test_user_2 IDENTIFIED BY 'testpw'; 38connect session1, localhost, test_user_1,'testpw',test; 39SELECT * FROM t1 PARTITION (`p0-29`); 40a b 411 (p0-29-)subp3 422 (p0-29-)subp3 4321 (p0-29-)subp5 4422 (p0-29-)subp5 4523 (p0-29-)subp5 4624 (p0-29-)subp5 473 (p0-29-)subp3 484 (p0-29-)subp3 49send 50UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-1' WHERE a BETWEEN 0 AND 9; 51connect session2, localhost, test_user_2,'testpw',test; 52SELECT * FROM t1 PARTITION (`p30-299`); 53a b 54231 (p30-299-)subp8 55232 (p30-299-)subp8 56233 (p30-299-)subp8 57234 (p30-299-)subp8 5831 (p30-299-)subp6 5932 (p30-299-)subp6 6033 (p30-299-)subp6 6134 (p30-299-)subp6 62UPDATE t1 PARTITION (`p30-299`) SET b='p30-299-upd' WHERE a BETWEEN 30 AND 39; 63SELECT * FROM t1 PARTITION (`p30-299`); 64a b 65231 (p30-299-)subp8 66232 (p30-299-)subp8 67233 (p30-299-)subp8 68234 (p30-299-)subp8 6931 p30-299-upd 7032 p30-299-upd 7133 p30-299-upd 7234 p30-299-upd 73SELECT * FROM t1 PARTITION (`p300-2999`); 74a b 75301 (p300-2999-)subp8 76302 (p300-2999-)subp8 77303 (p300-2999-)subp8 78304 (p300-2999-)subp8 79UPDATE t1 PARTITION (`p300-2999`) SET b='p300-2999-upd' WHERE a BETWEEN 300 AND 309; 80SELECT * FROM t1 PARTITION (`p300-2999`); 81a b 82301 p300-2999-upd 83302 p300-2999-upd 84303 p300-2999-upd 85304 p300-2999-upd 86SELECT * FROM t1 PARTITION (`p3000-299999`); 87a b 88299996 (p3000-299999-)subp14 89299997 (p3000-299999-)subp14 90299998 (p3000-299999-)subp14 91299999 (p3000-299999-)subp14 923001 (p3000-299999-)subp12 933002 (p3000-299999-)subp12 943003 (p3000-299999-)subp12 953004 (p3000-299999-)subp12 96UPDATE t1 PARTITION (`p3000-299999`) SET b='p3000-299999-upd' WHERE a BETWEEN 3000 AND 3009; 97SELECT * FROM t1 PARTITION (`p3000-299999`); 98a b 99299996 (p3000-299999-)subp14 100299997 (p3000-299999-)subp14 101299998 (p3000-299999-)subp14 102299999 (p3000-299999-)subp14 1033001 p3000-299999-upd 1043002 p3000-299999-upd 1053003 p3000-299999-upd 1063004 p3000-299999-upd 107UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-2' WHERE a BETWEEN 0 AND 9; 108SELECT * FROM t1 PARTITION (`p0-29`); 109a b 1101 p0-29-upd-2 1112 p0-29-upd-2 11221 (p0-29-)subp5 11322 (p0-29-)subp5 11423 (p0-29-)subp5 11524 (p0-29-)subp5 1163 p0-29-upd-2 1174 p0-29-upd-2 118UPDATE t1 PARTITION (subp3) SET b='subp3-upd'; 119UPDATE t1 PARTITION (subp4) SET b='subp4-upd'; 120UPDATE t1 PARTITION (subp5) SET b='subp5-upd'; 121SELECT * FROM t1 PARTITION (subp3); 122a b 12321 subp3-upd 12424 subp3-upd 1253 subp3-upd 126SELECT * FROM t1 PARTITION (subp4); 127a b 1281 subp4-upd 12922 subp4-upd 1304 subp4-upd 131SELECT * FROM t1 PARTITION (subp5); 132a b 1332 subp5-upd 13423 subp5-upd 135SELECT * FROM t1 PARTITION (subp4,subp5,subp3) order by b,a; 136a b 1373 subp3-upd 13821 subp3-upd 13924 subp3-upd 1401 subp4-upd 1414 subp4-upd 14222 subp4-upd 1432 subp5-upd 14423 subp5-upd 145connection session1; 146reap 147SELECT * FROM t1 PARTITION (`p0-29`); 148a b 1491 subp4-upd 1502 subp5-upd 15121 subp3-upd 15222 subp4-upd 15323 subp5-upd 15424 subp3-upd 1553 subp3-upd 1564 subp4-upd 157connection session2; 158SELECT * FROM t1 PARTITION (`p0-29`); 159a b 1601 subp4-upd 1612 subp5-upd 16221 subp3-upd 16322 subp4-upd 16423 subp5-upd 16524 subp3-upd 1663 subp3-upd 1674 subp4-upd 168SELECT 169(SELECT count(*) FROM t1 PARTITION (pneg,`p0-29`,`p30-299`,`p300-2999`,`p3000-299999`)) = 170(SELECT count(*) FROM t1) as `MUST BE TRUE (1)` ; 171MUST BE TRUE (1) 1721 173SELECT * FROM t2 PARTITION (`p30-299`); 174a b 175UPDATE t2 PARTITION (`p30-299`) SET b='p30-299-upd' WHERE a BETWEEN 30 AND 39; 176DELETE FROM t2 PARTITION (`p30-299`) WHERE a BETWEEN 30 AND 39; 177SELECT * FROM t2 PARTITION (`p300-2999`); 178a b 179UPDATE t2 PARTITION (`p300-2999`) SET b='p300-2999-upd' WHERE a BETWEEN 300 AND 309; 180DELETE FROM t2 PARTITION (`p300-2999`) WHERE a BETWEEN 300 AND 309; 181SELECT * FROM t2 PARTITION (`p3000-299999`); 182a b 183UPDATE t2 PARTITION (`p3000-299999`) SET b='p3000-299999-upd' WHERE a BETWEEN 3000 AND 3009; 184DELETE FROM t2 PARTITION (`p3000-299999`) WHERE a BETWEEN 3000 AND 3009; 185SELECT * FROM t2 PARTITION (`p0-29`); 186a b 187UPDATE t2 PARTITION (`p0-29`) SET b='p0-29-upd-2' WHERE a BETWEEN 0 AND 9; 188DELETE FROM t2 PARTITION (`p0-29`) WHERE a BETWEEN 0 AND 9; 189UPDATE t2 PARTITION (subp3) SET b='subp3-upd'; 190DELETE FROM t2 PARTITION (subp3); 191UPDATE t2 PARTITION (subp4) SET b='subp4-upd'; 192DELETE FROM t2 PARTITION (subp4); 193UPDATE t2 PARTITION (subp5) SET b='subp5-upd'; 194DELETE FROM t2 PARTITION (subp5); 195UPDATE t2 PARTITION (subp4,subp5,subp3) SET b='subp-upd'; 196DELETE FROM t2 PARTITION (subp4,subp5,subp3); 197SELECT * FROM t2 PARTITION (subp3); 198a b 199SELECT * FROM t2 PARTITION (subp4); 200a b 201SELECT * FROM t2 PARTITION (subp5); 202a b 203disconnect session1; 204disconnect session2; 205connection default; 206DROP TABLE t1, t2; 207DROP USER test_user_1; 208DROP USER test_user_2; 209