1set @save_sqlmode= @@global.sql_mode; 2# Additional tests for WL#5217 by QA, testplan 1.1 3CREATE TABLE t1 4(a INT NOT NULL, 5b varchar (64), 6INDEX ind_t1 (b,a), 7PRIMARY KEY (a)) 8ENGINE = InnoDB 9PARTITION BY RANGE (a) 10SUBPARTITION BY HASH (a) SUBPARTITIONS 3 11(PARTITION pNeg VALUES LESS THAN (0) 12(SUBPARTITION subp0 , 13SUBPARTITION subp1 , 14SUBPARTITION subp2 ), 15PARTITION `p0-29` VALUES LESS THAN (30) 16(SUBPARTITION subp3 , 17SUBPARTITION subp4 , 18SUBPARTITION subp5 ), 19PARTITION `p30-299` VALUES LESS THAN (300) 20(SUBPARTITION subp6 , 21SUBPARTITION subp7 , 22SUBPARTITION subp8 ), 23PARTITION `p300-2999` VALUES LESS THAN (3000) 24(SUBPARTITION subp9 , 25SUBPARTITION subp10 , 26SUBPARTITION subp11 ), 27PARTITION `p3000-299999` VALUES LESS THAN (300000) 28(SUBPARTITION subp12 , 29SUBPARTITION subp13 , 30SUBPARTITION subp14 )); 31INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-4, '(pNeg-)subp0'); 32INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, '(pNeg-)subp0'); 33INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, '(pNeg-)subp0'); 34INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-1, '(pNeg-)subp0'); 35INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (4, '(p0-29-)subp3'); 36INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (3, '(p0-29-)subp3'); 37INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (2, '(p0-29-)subp3'); 38INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (1, '(p0-29-)subp3'); 39INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (24, '(p0-29-)subp5'); 40INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (23, '(p0-29-)subp5'); 41INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (22, '(p0-29-)subp5'); 42INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (21, '(p0-29-)subp5'); 43INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (34, '(p30-299-)subp6'); 44INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (33, '(p30-299-)subp6'); 45INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (32, '(p30-299-)subp6'); 46INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (31, '(p30-299-)subp6'); 47INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (234, '(p30-299-)subp8'); 48INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (233, '(p30-299-)subp8'); 49INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (232, '(p30-299-)subp8'); 50INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (231, '(p30-299-)subp8'); 51INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (304, '(p300-2999-)subp8'); 52INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (303, '(p300-2999-)subp8'); 53INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (302, '(p300-2999-)subp8'); 54INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (301, '(p300-2999-)subp8'); 55INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3004, '(p3000-299999-)subp12'); 56INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3003, '(p3000-299999-)subp12'); 57INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3002, '(p3000-299999-)subp12'); 58INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3001, '(p3000-299999-)subp12'); 59INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299996, '(p3000-299999-)subp14'); 60INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299997, '(p3000-299999-)subp14'); 61INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299998, '(p3000-299999-)subp14'); 62INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299999, '(p3000-299999-)subp14'); 63CREATE USER test_user_1 IDENTIFIED BY 'testpw'; 64CREATE USER test_user_2 IDENTIFIED BY 'testpw'; 65GRANT ALL PRIVILEGES ON test.* TO test_user_1 ; 66GRANT ALL PRIVILEGES ON test.* TO test_user_2 ; 67# Run in empty mode 68set @@global.sql_mode= ''; 69Warnings: 70Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 71####################################################### 72# UPDATE without partition selection 73connect session1, localhost, test_user_1,'testpw',test; 74SELECT * FROM t1 PARTITION (`p0-29`); 75a b 763 (p0-29-)subp3 7721 (p0-29-)subp5 7824 (p0-29-)subp5 791 (p0-29-)subp3 804 (p0-29-)subp3 8122 (p0-29-)subp5 822 (p0-29-)subp3 8323 (p0-29-)subp5 84send 85UPDATE t1 SET b='table-upd-1' WHERE a BETWEEN 0 AND 9; 86connect session2, localhost, test_user_2,'testpw',test; 87UPDATE t1 SET b='table-upd-2' WHERE a BETWEEN 0 AND 9; 88SELECT * FROM t1 PARTITION (`p0-29`); 89a b 9021 (p0-29-)subp5 9124 (p0-29-)subp5 923 table-upd-2 9322 (p0-29-)subp5 941 table-upd-2 954 table-upd-2 9623 (p0-29-)subp5 972 table-upd-2 98INSERT INTO t1 VALUES (11, 'table-ins'); 99INSERT INTO t1 VALUES (12, 'table-ins'); 100SELECT * FROM t1 PARTITION (`p0-29`); 101a b 10221 (p0-29-)subp5 10324 (p0-29-)subp5 10412 table-ins 1053 table-upd-2 10622 (p0-29-)subp5 1071 table-upd-2 1084 table-upd-2 10923 (p0-29-)subp5 11011 table-ins 1112 table-upd-2 112DELETE FROM t1 WHERE b= 'table-ins'; 113SELECT * FROM t1 PARTITION (`p0-29`); 114a b 11521 (p0-29-)subp5 11624 (p0-29-)subp5 1173 table-upd-2 11822 (p0-29-)subp5 1191 table-upd-2 1204 table-upd-2 12123 (p0-29-)subp5 1222 table-upd-2 123connection session1; 124reap 125SELECT * FROM t1 PARTITION (`p0-29`); 126a b 12721 (p0-29-)subp5 12824 (p0-29-)subp5 1293 table-upd-2 13022 (p0-29-)subp5 1311 table-upd-2 1324 table-upd-2 13323 (p0-29-)subp5 1342 table-upd-2 135connection session2; 136SELECT * FROM t1 PARTITION (`p0-29`); 137a b 13821 (p0-29-)subp5 13924 (p0-29-)subp5 1403 table-upd-2 14122 (p0-29-)subp5 1421 table-upd-2 1434 table-upd-2 14423 (p0-29-)subp5 1452 table-upd-2 146####################################################### 147# UPDATE with partition selection 148connection session1; 149SELECT * FROM t1 PARTITION (`p0-29`); 150a b 15121 (p0-29-)subp5 15224 (p0-29-)subp5 1533 table-upd-2 15422 (p0-29-)subp5 1551 table-upd-2 1564 table-upd-2 15723 (p0-29-)subp5 1582 table-upd-2 159send 160UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-1' WHERE a BETWEEN 0 AND 9; 161connection session2; 162UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-2' WHERE a BETWEEN 0 AND 9; 163SELECT * FROM t1 PARTITION (`p0-29`); 164a b 16521 (p0-29-)subp5 16624 (p0-29-)subp5 1673 p0-29-upd-2 16822 (p0-29-)subp5 1691 p0-29-upd-2 1704 p0-29-upd-2 17123 (p0-29-)subp5 1722 p0-29-upd-2 173INSERT INTO t1 PARTITION (`p0-29`, subp4) VALUES (11, '(p0-29-)ins'); 174INSERT INTO t1 PARTITION (`p0-29`, subp4) VALUES (12, '(p0-29-)ins'); 175SELECT * FROM t1 PARTITION (`p0-29`); 176a b 17712 (p0-29-)ins 17821 (p0-29-)subp5 17924 (p0-29-)subp5 1803 p0-29-upd-2 18122 (p0-29-)subp5 1821 p0-29-upd-2 1834 p0-29-upd-2 18411 (p0-29-)ins 18523 (p0-29-)subp5 1862 p0-29-upd-2 187DELETE FROM t1 PARTITION (`p0-29`,subp5) WHERE b= '(p0-29-)ins'; 188SELECT * FROM t1 PARTITION (`p0-29`); 189a b 19021 (p0-29-)subp5 19124 (p0-29-)subp5 1923 p0-29-upd-2 19322 (p0-29-)subp5 1941 p0-29-upd-2 1954 p0-29-upd-2 19623 (p0-29-)subp5 1972 p0-29-upd-2 198connection session1; 199reap 200SELECT * FROM t1 PARTITION (`p0-29`); 201a b 20221 (p0-29-)subp5 20324 (p0-29-)subp5 2043 p0-29-upd-2 20522 (p0-29-)subp5 2061 p0-29-upd-2 2074 p0-29-upd-2 20823 (p0-29-)subp5 2092 p0-29-upd-2 210connection session2; 211SELECT * FROM t1 PARTITION (`p0-29`); 212a b 21321 (p0-29-)subp5 21424 (p0-29-)subp5 2153 p0-29-upd-2 21622 (p0-29-)subp5 2171 p0-29-upd-2 2184 p0-29-upd-2 21923 (p0-29-)subp5 2202 p0-29-upd-2 221disconnect session1; 222disconnect session2; 223connection default; 224DROP TABLE t1; 225CREATE TABLE t1 226(a INT NOT NULL, 227b varchar (64), 228INDEX ind_t1 (b,a), 229PRIMARY KEY (a)) 230ENGINE = InnoDB 231PARTITION BY RANGE (a) 232SUBPARTITION BY HASH (a) SUBPARTITIONS 3 233(PARTITION pNeg VALUES LESS THAN (0) 234(SUBPARTITION subp0 , 235SUBPARTITION subp1 , 236SUBPARTITION subp2 ), 237PARTITION `p0-29` VALUES LESS THAN (30) 238(SUBPARTITION subp3 , 239SUBPARTITION subp4 , 240SUBPARTITION subp5 ), 241PARTITION `p30-299` VALUES LESS THAN (300) 242(SUBPARTITION subp6 , 243SUBPARTITION subp7 , 244SUBPARTITION subp8 ), 245PARTITION `p300-2999` VALUES LESS THAN (3000) 246(SUBPARTITION subp9 , 247SUBPARTITION subp10 , 248SUBPARTITION subp11 ), 249PARTITION `p3000-299999` VALUES LESS THAN (300000) 250(SUBPARTITION subp12 , 251SUBPARTITION subp13 , 252SUBPARTITION subp14 )); 253INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-4, '(pNeg-)subp0'); 254INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, '(pNeg-)subp0'); 255INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, '(pNeg-)subp0'); 256INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-1, '(pNeg-)subp0'); 257INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (4, '(p0-29-)subp3'); 258INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (3, '(p0-29-)subp3'); 259INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (2, '(p0-29-)subp3'); 260INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (1, '(p0-29-)subp3'); 261INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (24, '(p0-29-)subp5'); 262INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (23, '(p0-29-)subp5'); 263INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (22, '(p0-29-)subp5'); 264INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (21, '(p0-29-)subp5'); 265INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (34, '(p30-299-)subp6'); 266INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (33, '(p30-299-)subp6'); 267INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (32, '(p30-299-)subp6'); 268INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (31, '(p30-299-)subp6'); 269INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (234, '(p30-299-)subp8'); 270INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (233, '(p30-299-)subp8'); 271INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (232, '(p30-299-)subp8'); 272INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (231, '(p30-299-)subp8'); 273INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (304, '(p300-2999-)subp8'); 274INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (303, '(p300-2999-)subp8'); 275INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (302, '(p300-2999-)subp8'); 276INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (301, '(p300-2999-)subp8'); 277INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3004, '(p3000-299999-)subp12'); 278INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3003, '(p3000-299999-)subp12'); 279INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3002, '(p3000-299999-)subp12'); 280INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3001, '(p3000-299999-)subp12'); 281INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299996, '(p3000-299999-)subp14'); 282INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299997, '(p3000-299999-)subp14'); 283INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299998, '(p3000-299999-)subp14'); 284INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299999, '(p3000-299999-)subp14'); 285# Run in ANSI mode 286set @@global.sql_mode= ansi; 287Warnings: 288Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 289####################################################### 290# UPDATE without partition selection 291connect session1, localhost, test_user_1,'testpw',test; 292SELECT * FROM t1 PARTITION (`p0-29`); 293a b 2943 (p0-29-)subp3 29521 (p0-29-)subp5 29624 (p0-29-)subp5 2971 (p0-29-)subp3 2984 (p0-29-)subp3 29922 (p0-29-)subp5 3002 (p0-29-)subp3 30123 (p0-29-)subp5 302send 303UPDATE t1 SET b='table-upd-1' WHERE a BETWEEN 0 AND 9; 304connect session2, localhost, test_user_2,'testpw',test; 305UPDATE t1 SET b='table-upd-2' WHERE a BETWEEN 0 AND 9; 306SELECT * FROM t1 PARTITION (`p0-29`); 307a b 30821 (p0-29-)subp5 30924 (p0-29-)subp5 3103 table-upd-2 31122 (p0-29-)subp5 3121 table-upd-2 3134 table-upd-2 31423 (p0-29-)subp5 3152 table-upd-2 316INSERT INTO t1 VALUES (11, 'table-ins'); 317INSERT INTO t1 VALUES (12, 'table-ins'); 318SELECT * FROM t1 PARTITION (`p0-29`); 319a b 32021 (p0-29-)subp5 32124 (p0-29-)subp5 32212 table-ins 3233 table-upd-2 32422 (p0-29-)subp5 3251 table-upd-2 3264 table-upd-2 32723 (p0-29-)subp5 32811 table-ins 3292 table-upd-2 330DELETE FROM t1 WHERE b= 'table-ins'; 331SELECT * FROM t1 PARTITION (`p0-29`); 332a b 33321 (p0-29-)subp5 33424 (p0-29-)subp5 3353 table-upd-2 33622 (p0-29-)subp5 3371 table-upd-2 3384 table-upd-2 33923 (p0-29-)subp5 3402 table-upd-2 341connection session1; 342reap 343SELECT * FROM t1 PARTITION (`p0-29`); 344a b 34521 (p0-29-)subp5 34624 (p0-29-)subp5 3473 table-upd-2 34822 (p0-29-)subp5 3491 table-upd-2 3504 table-upd-2 35123 (p0-29-)subp5 3522 table-upd-2 353connection session2; 354SELECT * FROM t1 PARTITION (`p0-29`); 355a b 35621 (p0-29-)subp5 35724 (p0-29-)subp5 3583 table-upd-2 35922 (p0-29-)subp5 3601 table-upd-2 3614 table-upd-2 36223 (p0-29-)subp5 3632 table-upd-2 364####################################################### 365# UPDATE with partition selection 366connection session1; 367SELECT * FROM t1 PARTITION (`p0-29`); 368a b 36921 (p0-29-)subp5 37024 (p0-29-)subp5 3713 table-upd-2 37222 (p0-29-)subp5 3731 table-upd-2 3744 table-upd-2 37523 (p0-29-)subp5 3762 table-upd-2 377send 378UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-1' WHERE a BETWEEN 0 AND 9; 379connection session2; 380UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-2' WHERE a BETWEEN 0 AND 9; 381SELECT * FROM t1 PARTITION (`p0-29`); 382a b 38321 (p0-29-)subp5 38424 (p0-29-)subp5 3853 p0-29-upd-2 38622 (p0-29-)subp5 3871 p0-29-upd-2 3884 p0-29-upd-2 38923 (p0-29-)subp5 3902 p0-29-upd-2 391INSERT INTO t1 PARTITION (`p0-29`, subp4) VALUES (11, '(p0-29-)ins'); 392INSERT INTO t1 PARTITION (`p0-29`, subp4) VALUES (12, '(p0-29-)ins'); 393SELECT * FROM t1 PARTITION (`p0-29`); 394a b 39512 (p0-29-)ins 39621 (p0-29-)subp5 39724 (p0-29-)subp5 3983 p0-29-upd-2 39922 (p0-29-)subp5 4001 p0-29-upd-2 4014 p0-29-upd-2 40211 (p0-29-)ins 40323 (p0-29-)subp5 4042 p0-29-upd-2 405DELETE FROM t1 PARTITION (`p0-29`,subp5) WHERE b= '(p0-29-)ins'; 406SELECT * FROM t1 PARTITION (`p0-29`); 407a b 40821 (p0-29-)subp5 40924 (p0-29-)subp5 4103 p0-29-upd-2 41122 (p0-29-)subp5 4121 p0-29-upd-2 4134 p0-29-upd-2 41423 (p0-29-)subp5 4152 p0-29-upd-2 416connection session1; 417reap 418SELECT * FROM t1 PARTITION (`p0-29`); 419a b 42021 (p0-29-)subp5 42124 (p0-29-)subp5 4223 p0-29-upd-2 42322 (p0-29-)subp5 4241 p0-29-upd-2 4254 p0-29-upd-2 42623 (p0-29-)subp5 4272 p0-29-upd-2 428connection session2; 429SELECT * FROM t1 PARTITION (`p0-29`); 430a b 43121 (p0-29-)subp5 43224 (p0-29-)subp5 4333 p0-29-upd-2 43422 (p0-29-)subp5 4351 p0-29-upd-2 4364 p0-29-upd-2 43723 (p0-29-)subp5 4382 p0-29-upd-2 439disconnect session1; 440disconnect session2; 441connection default; 442DROP TABLE t1; 443CREATE TABLE t1 444(a INT NOT NULL, 445b varchar (64), 446INDEX ind_t1 (b,a), 447PRIMARY KEY (a)) 448ENGINE = InnoDB 449PARTITION BY RANGE (a) 450SUBPARTITION BY HASH (a) SUBPARTITIONS 3 451(PARTITION pNeg VALUES LESS THAN (0) 452(SUBPARTITION subp0 , 453SUBPARTITION subp1 , 454SUBPARTITION subp2 ), 455PARTITION `p0-29` VALUES LESS THAN (30) 456(SUBPARTITION subp3 , 457SUBPARTITION subp4 , 458SUBPARTITION subp5 ), 459PARTITION `p30-299` VALUES LESS THAN (300) 460(SUBPARTITION subp6 , 461SUBPARTITION subp7 , 462SUBPARTITION subp8 ), 463PARTITION `p300-2999` VALUES LESS THAN (3000) 464(SUBPARTITION subp9 , 465SUBPARTITION subp10 , 466SUBPARTITION subp11 ), 467PARTITION `p3000-299999` VALUES LESS THAN (300000) 468(SUBPARTITION subp12 , 469SUBPARTITION subp13 , 470SUBPARTITION subp14 )); 471INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-4, '(pNeg-)subp0'); 472INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, '(pNeg-)subp0'); 473INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, '(pNeg-)subp0'); 474INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-1, '(pNeg-)subp0'); 475INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (4, '(p0-29-)subp3'); 476INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (3, '(p0-29-)subp3'); 477INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (2, '(p0-29-)subp3'); 478INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (1, '(p0-29-)subp3'); 479INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (24, '(p0-29-)subp5'); 480INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (23, '(p0-29-)subp5'); 481INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (22, '(p0-29-)subp5'); 482INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (21, '(p0-29-)subp5'); 483INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (34, '(p30-299-)subp6'); 484INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (33, '(p30-299-)subp6'); 485INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (32, '(p30-299-)subp6'); 486INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (31, '(p30-299-)subp6'); 487INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (234, '(p30-299-)subp8'); 488INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (233, '(p30-299-)subp8'); 489INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (232, '(p30-299-)subp8'); 490INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (231, '(p30-299-)subp8'); 491INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (304, '(p300-2999-)subp8'); 492INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (303, '(p300-2999-)subp8'); 493INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (302, '(p300-2999-)subp8'); 494INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (301, '(p300-2999-)subp8'); 495INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3004, '(p3000-299999-)subp12'); 496INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3003, '(p3000-299999-)subp12'); 497INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3002, '(p3000-299999-)subp12'); 498INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3001, '(p3000-299999-)subp12'); 499INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299996, '(p3000-299999-)subp14'); 500INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299997, '(p3000-299999-)subp14'); 501INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299998, '(p3000-299999-)subp14'); 502INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299999, '(p3000-299999-)subp14'); 503# Run in traditional mode 504set @@global.sql_mode= traditional; 505####################################################### 506# UPDATE without partition selection 507connect session1, localhost, test_user_1,'testpw',test; 508SELECT * FROM t1 PARTITION (`p0-29`); 509a b 5103 (p0-29-)subp3 51121 (p0-29-)subp5 51224 (p0-29-)subp5 5131 (p0-29-)subp3 5144 (p0-29-)subp3 51522 (p0-29-)subp5 5162 (p0-29-)subp3 51723 (p0-29-)subp5 518send 519UPDATE t1 SET b='table-upd-1' WHERE a BETWEEN 0 AND 9; 520connect session2, localhost, test_user_2,'testpw',test; 521UPDATE t1 SET b='table-upd-2' WHERE a BETWEEN 0 AND 9; 522SELECT * FROM t1 PARTITION (`p0-29`); 523a b 52421 (p0-29-)subp5 52524 (p0-29-)subp5 5263 table-upd-2 52722 (p0-29-)subp5 5281 table-upd-2 5294 table-upd-2 53023 (p0-29-)subp5 5312 table-upd-2 532INSERT INTO t1 VALUES (11, 'table-ins'); 533INSERT INTO t1 VALUES (12, 'table-ins'); 534SELECT * FROM t1 PARTITION (`p0-29`); 535a b 53621 (p0-29-)subp5 53724 (p0-29-)subp5 53812 table-ins 5393 table-upd-2 54022 (p0-29-)subp5 5411 table-upd-2 5424 table-upd-2 54323 (p0-29-)subp5 54411 table-ins 5452 table-upd-2 546DELETE FROM t1 WHERE b= 'table-ins'; 547SELECT * FROM t1 PARTITION (`p0-29`); 548a b 54921 (p0-29-)subp5 55024 (p0-29-)subp5 5513 table-upd-2 55222 (p0-29-)subp5 5531 table-upd-2 5544 table-upd-2 55523 (p0-29-)subp5 5562 table-upd-2 557connection session1; 558reap 559SELECT * FROM t1 PARTITION (`p0-29`); 560a b 56121 (p0-29-)subp5 56224 (p0-29-)subp5 5633 table-upd-2 56422 (p0-29-)subp5 5651 table-upd-2 5664 table-upd-2 56723 (p0-29-)subp5 5682 table-upd-2 569connection session2; 570SELECT * FROM t1 PARTITION (`p0-29`); 571a b 57221 (p0-29-)subp5 57324 (p0-29-)subp5 5743 table-upd-2 57522 (p0-29-)subp5 5761 table-upd-2 5774 table-upd-2 57823 (p0-29-)subp5 5792 table-upd-2 580####################################################### 581# UPDATE with partition selection 582connection session1; 583SELECT * FROM t1 PARTITION (`p0-29`); 584a b 58521 (p0-29-)subp5 58624 (p0-29-)subp5 5873 table-upd-2 58822 (p0-29-)subp5 5891 table-upd-2 5904 table-upd-2 59123 (p0-29-)subp5 5922 table-upd-2 593send 594UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-1' WHERE a BETWEEN 0 AND 9; 595connection session2; 596UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-2' WHERE a BETWEEN 0 AND 9; 597SELECT * FROM t1 PARTITION (`p0-29`); 598a b 59921 (p0-29-)subp5 60024 (p0-29-)subp5 6013 p0-29-upd-2 60222 (p0-29-)subp5 6031 p0-29-upd-2 6044 p0-29-upd-2 60523 (p0-29-)subp5 6062 p0-29-upd-2 607INSERT INTO t1 PARTITION (`p0-29`, subp4) VALUES (11, '(p0-29-)ins'); 608INSERT INTO t1 PARTITION (`p0-29`, subp4) VALUES (12, '(p0-29-)ins'); 609SELECT * FROM t1 PARTITION (`p0-29`); 610a b 61112 (p0-29-)ins 61221 (p0-29-)subp5 61324 (p0-29-)subp5 6143 p0-29-upd-2 61522 (p0-29-)subp5 6161 p0-29-upd-2 6174 p0-29-upd-2 61811 (p0-29-)ins 61923 (p0-29-)subp5 6202 p0-29-upd-2 621DELETE FROM t1 PARTITION (`p0-29`,subp5) WHERE b= '(p0-29-)ins'; 622SELECT * FROM t1 PARTITION (`p0-29`); 623a b 62421 (p0-29-)subp5 62524 (p0-29-)subp5 6263 p0-29-upd-2 62722 (p0-29-)subp5 6281 p0-29-upd-2 6294 p0-29-upd-2 63023 (p0-29-)subp5 6312 p0-29-upd-2 632connection session1; 633reap 634SELECT * FROM t1 PARTITION (`p0-29`); 635a b 63621 (p0-29-)subp5 63724 (p0-29-)subp5 6383 p0-29-upd-2 63922 (p0-29-)subp5 6401 p0-29-upd-2 6414 p0-29-upd-2 64223 (p0-29-)subp5 6432 p0-29-upd-2 644connection session2; 645SELECT * FROM t1 PARTITION (`p0-29`); 646a b 64721 (p0-29-)subp5 64824 (p0-29-)subp5 6493 p0-29-upd-2 65022 (p0-29-)subp5 6511 p0-29-upd-2 6524 p0-29-upd-2 65323 (p0-29-)subp5 6542 p0-29-upd-2 655disconnect session1; 656disconnect session2; 657connection default; 658DROP TABLE t1; 659DROP USER test_user_1; 660DROP USER test_user_2; 661set @@global.sql_mode= @save_sqlmode; 662