Edit File: partition-dml-1-9-innodb.result
# Additional tests for WL#5217 by QA, testplan 1.1 CREATE TABLE t1 (a INT NOT NULL, b varchar (64), INDEX ind_t1 (b,a), PRIMARY KEY (a)) ENGINE = InnoDB PARTITION BY RANGE (a) SUBPARTITION BY HASH (a) SUBPARTITIONS 3 (PARTITION pNeg VALUES LESS THAN (0) (SUBPARTITION subp0 , SUBPARTITION subp1 , SUBPARTITION subp2 ), PARTITION `p0-29` VALUES LESS THAN (30) (SUBPARTITION subp3 , SUBPARTITION subp4 , SUBPARTITION subp5 ), PARTITION `p30-299` VALUES LESS THAN (300) (SUBPARTITION subp6 , SUBPARTITION subp7 , SUBPARTITION subp8 ), PARTITION `p300-2999` VALUES LESS THAN (3000) (SUBPARTITION subp9 , SUBPARTITION subp10 , SUBPARTITION subp11 ), PARTITION `p3000-299999` VALUES LESS THAN (300000) (SUBPARTITION subp12 , SUBPARTITION subp13 , SUBPARTITION subp14 )); INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-4, '(pNeg-)subp0'); INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, '(pNeg-)subp0'); INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, '(pNeg-)subp0'); INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-1, '(pNeg-)subp0'); INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (4, '(p0-29-)subp3'); INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (3, '(p0-29-)subp3'); INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (2, '(p0-29-)subp3'); INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (1, '(p0-29-)subp3'); INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (24, '(p0-29-)subp5'); INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (23, '(p0-29-)subp5'); INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (22, '(p0-29-)subp5'); INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (21, '(p0-29-)subp5'); INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (34, '(p30-299-)subp6'); INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (33, '(p30-299-)subp6'); INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (32, '(p30-299-)subp6'); INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (31, '(p30-299-)subp6'); INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (234, '(p30-299-)subp8'); INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (233, '(p30-299-)subp8'); INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (232, '(p30-299-)subp8'); INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (231, '(p30-299-)subp8'); INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (304, '(p300-2999-)subp8'); INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (303, '(p300-2999-)subp8'); INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (302, '(p300-2999-)subp8'); INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (301, '(p300-2999-)subp8'); INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3004, '(p3000-299999-)subp12'); INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3003, '(p3000-299999-)subp12'); INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3002, '(p3000-299999-)subp12'); INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3001, '(p3000-299999-)subp12'); INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299996, '(p3000-299999-)subp14'); INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299997, '(p3000-299999-)subp14'); INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299998, '(p3000-299999-)subp14'); INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299999, '(p3000-299999-)subp14'); CREATE TABLE t2 (a INT NOT NULL, b varchar (64), INDEX ind_t2 (b,a), PRIMARY KEY (a)) ENGINE = InnoDB PARTITION BY RANGE (a) SUBPARTITION BY HASH (a) SUBPARTITIONS 3 (PARTITION pNeg VALUES LESS THAN (0) (SUBPARTITION subp0 , SUBPARTITION subp1 , SUBPARTITION subp2 ), PARTITION `p0-29` VALUES LESS THAN (30) (SUBPARTITION subp3 , SUBPARTITION subp4 , SUBPARTITION subp5 ), PARTITION `p30-299` VALUES LESS THAN (300) (SUBPARTITION subp6 , SUBPARTITION subp7 , SUBPARTITION subp8 ), PARTITION `p300-2999` VALUES LESS THAN (3000) (SUBPARTITION subp9 , SUBPARTITION subp10 , SUBPARTITION subp11 ), PARTITION `p3000-299999` VALUES LESS THAN (300000) (SUBPARTITION subp12 , SUBPARTITION subp13 , SUBPARTITION subp14 )); INSERT INTO t2 PARTITION (pNeg, subp0) VALUES (-4, '(pNeg-)subp0'); INSERT INTO t2 PARTITION (pNeg, subp0) VALUES (-3, '(pNeg-)subp0'); INSERT INTO t2 PARTITION (pNeg, subp0) VALUES (-2, '(pNeg-)subp0'); INSERT INTO t2 PARTITION (pNeg, subp0) VALUES (-1, '(pNeg-)subp0'); INSERT INTO t2 PARTITION (`p0-29`, subp3) VALUES (4, '(p0-29-)subp3'); INSERT INTO t2 PARTITION (`p0-29`, subp3) VALUES (3, '(p0-29-)subp3'); INSERT INTO t2 PARTITION (`p0-29`, subp3) VALUES (2, '(p0-29-)subp3'); INSERT INTO t2 PARTITION (`p0-29`, subp3) VALUES (1, '(p0-29-)subp3'); INSERT INTO t2 PARTITION (`p0-29`, subp5) VALUES (24, '(p0-29-)subp5'); INSERT INTO t2 PARTITION (`p0-29`, subp5) VALUES (23, '(p0-29-)subp5'); INSERT INTO t2 PARTITION (`p0-29`, subp5) VALUES (22, '(p0-29-)subp5'); INSERT INTO t2 PARTITION (`p0-29`, subp5) VALUES (21, '(p0-29-)subp5'); INSERT INTO t2 PARTITION (`p30-299`, subp9) VALUES (34, '(p30-299-)subp6'); INSERT INTO t2 PARTITION (`p30-299`, subp9) VALUES (33, '(p30-299-)subp6'); INSERT INTO t2 PARTITION (`p30-299`, subp9) VALUES (32, '(p30-299-)subp6'); INSERT INTO t2 PARTITION (`p30-299`, subp9) VALUES (31, '(p30-299-)subp6'); INSERT INTO t2 PARTITION (`p30-299`, subp8) VALUES (234, '(p30-299-)subp8'); INSERT INTO t2 PARTITION (`p30-299`, subp8) VALUES (233, '(p30-299-)subp8'); INSERT INTO t2 PARTITION (`p30-299`, subp8) VALUES (232, '(p30-299-)subp8'); INSERT INTO t2 PARTITION (`p30-299`, subp8) VALUES (231, '(p30-299-)subp8'); INSERT INTO t2 PARTITION (`p300-2999`, subp8) VALUES (304, '(p300-2999-)subp8'); INSERT INTO t2 PARTITION (`p300-2999`, subp8) VALUES (303, '(p300-2999-)subp8'); INSERT INTO t2 PARTITION (`p300-2999`, subp8) VALUES (302, '(p300-2999-)subp8'); INSERT INTO t2 PARTITION (`p300-2999`, subp8) VALUES (301, '(p300-2999-)subp8'); INSERT INTO t2 PARTITION (`p3000-299999`, subp12) VALUES (3004, '(p3000-299999-)subp12'); INSERT INTO t2 PARTITION (`p3000-299999`, subp12) VALUES (3003, '(p3000-299999-)subp12'); INSERT INTO t2 PARTITION (`p3000-299999`, subp12) VALUES (3002, '(p3000-299999-)subp12'); INSERT INTO t2 PARTITION (`p3000-299999`, subp12) VALUES (3001, '(p3000-299999-)subp12'); INSERT INTO t2 PARTITION (`p3000-299999`, subp14) VALUES (299996, '(p3000-299999-)subp14'); INSERT INTO t2 PARTITION (`p3000-299999`, subp14) VALUES (299997, '(p3000-299999-)subp14'); INSERT INTO t2 PARTITION (`p3000-299999`, subp14) VALUES (299998, '(p3000-299999-)subp14'); INSERT INTO t2 PARTITION (`p3000-299999`, subp14) VALUES (299999, '(p3000-299999-)subp14'); GRANT ALL PRIVILEGES ON test.* TO test_user_1 IDENTIFIED BY 'testpw'; connect session1, localhost, test_user_1,'testpw',test; CREATE VIEW v1 AS SELECT t1.a, t2.b FROM t1 PARTITION (`p0-29`), t2 WHERE t1.a = t2.a; SELECT * FROM v1; a b 1 (p0-29-)subp3 2 (p0-29-)subp3 21 (p0-29-)subp5 22 (p0-29-)subp5 23 (p0-29-)subp5 24 (p0-29-)subp5 3 (p0-29-)subp3 4 (p0-29-)subp3 SELECT t1.a, t2.b FROM t1 PARTITION (`p0-29`), t2 WHERE t1.a = t2.a; a b 1 (p0-29-)subp3 2 (p0-29-)subp3 21 (p0-29-)subp5 22 (p0-29-)subp5 23 (p0-29-)subp5 24 (p0-29-)subp5 3 (p0-29-)subp3 4 (p0-29-)subp3 SELECT * FROM t1 PARTITION (`p0-29`); a b 1 (p0-29-)subp3 2 (p0-29-)subp3 21 (p0-29-)subp5 22 (p0-29-)subp5 23 (p0-29-)subp5 24 (p0-29-)subp5 3 (p0-29-)subp3 4 (p0-29-)subp3 SELECT * FROM t2 PARTITION (`p30-299`); a b 231 (p30-299-)subp8 232 (p30-299-)subp8 233 (p30-299-)subp8 234 (p30-299-)subp8 31 (p30-299-)subp6 32 (p30-299-)subp6 33 (p30-299-)subp6 34 (p30-299-)subp6 SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 PARTITION (`p0-29`), t2 PARTITION (`p30-299`); t1a t2a t1b t2b 1 231 (p0-29-)subp3 (p30-299-)subp8 1 232 (p0-29-)subp3 (p30-299-)subp8 1 233 (p0-29-)subp3 (p30-299-)subp8 1 234 (p0-29-)subp3 (p30-299-)subp8 1 31 (p0-29-)subp3 (p30-299-)subp6 1 32 (p0-29-)subp3 (p30-299-)subp6 1 33 (p0-29-)subp3 (p30-299-)subp6 1 34 (p0-29-)subp3 (p30-299-)subp6 2 231 (p0-29-)subp3 (p30-299-)subp8 2 232 (p0-29-)subp3 (p30-299-)subp8 2 233 (p0-29-)subp3 (p30-299-)subp8 2 234 (p0-29-)subp3 (p30-299-)subp8 2 31 (p0-29-)subp3 (p30-299-)subp6 2 32 (p0-29-)subp3 (p30-299-)subp6 2 33 (p0-29-)subp3 (p30-299-)subp6 2 34 (p0-29-)subp3 (p30-299-)subp6 21 231 (p0-29-)subp5 (p30-299-)subp8 21 232 (p0-29-)subp5 (p30-299-)subp8 21 233 (p0-29-)subp5 (p30-299-)subp8 21 234 (p0-29-)subp5 (p30-299-)subp8 21 31 (p0-29-)subp5 (p30-299-)subp6 21 32 (p0-29-)subp5 (p30-299-)subp6 21 33 (p0-29-)subp5 (p30-299-)subp6 21 34 (p0-29-)subp5 (p30-299-)subp6 22 231 (p0-29-)subp5 (p30-299-)subp8 22 232 (p0-29-)subp5 (p30-299-)subp8 22 233 (p0-29-)subp5 (p30-299-)subp8 22 234 (p0-29-)subp5 (p30-299-)subp8 22 31 (p0-29-)subp5 (p30-299-)subp6 22 32 (p0-29-)subp5 (p30-299-)subp6 22 33 (p0-29-)subp5 (p30-299-)subp6 22 34 (p0-29-)subp5 (p30-299-)subp6 23 231 (p0-29-)subp5 (p30-299-)subp8 23 232 (p0-29-)subp5 (p30-299-)subp8 23 233 (p0-29-)subp5 (p30-299-)subp8 23 234 (p0-29-)subp5 (p30-299-)subp8 23 31 (p0-29-)subp5 (p30-299-)subp6 23 32 (p0-29-)subp5 (p30-299-)subp6 23 33 (p0-29-)subp5 (p30-299-)subp6 23 34 (p0-29-)subp5 (p30-299-)subp6 24 231 (p0-29-)subp5 (p30-299-)subp8 24 232 (p0-29-)subp5 (p30-299-)subp8 24 233 (p0-29-)subp5 (p30-299-)subp8 24 234 (p0-29-)subp5 (p30-299-)subp8 24 31 (p0-29-)subp5 (p30-299-)subp6 24 32 (p0-29-)subp5 (p30-299-)subp6 24 33 (p0-29-)subp5 (p30-299-)subp6 24 34 (p0-29-)subp5 (p30-299-)subp6 3 231 (p0-29-)subp3 (p30-299-)subp8 3 232 (p0-29-)subp3 (p30-299-)subp8 3 233 (p0-29-)subp3 (p30-299-)subp8 3 234 (p0-29-)subp3 (p30-299-)subp8 3 31 (p0-29-)subp3 (p30-299-)subp6 3 32 (p0-29-)subp3 (p30-299-)subp6 3 33 (p0-29-)subp3 (p30-299-)subp6 3 34 (p0-29-)subp3 (p30-299-)subp6 4 231 (p0-29-)subp3 (p30-299-)subp8 4 232 (p0-29-)subp3 (p30-299-)subp8 4 233 (p0-29-)subp3 (p30-299-)subp8 4 234 (p0-29-)subp3 (p30-299-)subp8 4 31 (p0-29-)subp3 (p30-299-)subp6 4 32 (p0-29-)subp3 (p30-299-)subp6 4 33 (p0-29-)subp3 (p30-299-)subp6 4 34 (p0-29-)subp3 (p30-299-)subp6 SELECT * FROM t1 PARTITION (subp3); a b 21 (p0-29-)subp5 24 (p0-29-)subp5 3 (p0-29-)subp3 SELECT * FROM t2 PARTITION (subp6); a b 231 (p30-299-)subp8 234 (p30-299-)subp8 33 (p30-299-)subp6 SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 PARTITION (subp3), t2 PARTITION (subp6); t1a t2a t1b t2b 21 231 (p0-29-)subp5 (p30-299-)subp8 21 234 (p0-29-)subp5 (p30-299-)subp8 21 33 (p0-29-)subp5 (p30-299-)subp6 24 231 (p0-29-)subp5 (p30-299-)subp8 24 234 (p0-29-)subp5 (p30-299-)subp8 24 33 (p0-29-)subp5 (p30-299-)subp6 3 231 (p0-29-)subp3 (p30-299-)subp8 3 234 (p0-29-)subp3 (p30-299-)subp8 3 33 (p0-29-)subp3 (p30-299-)subp6 SELECT * FROM t1 PARTITION (subp3,subp5); a b 2 (p0-29-)subp3 21 (p0-29-)subp5 23 (p0-29-)subp5 24 (p0-29-)subp5 3 (p0-29-)subp3 SELECT * FROM t2 PARTITION (subp6,subp8); a b 231 (p30-299-)subp8 233 (p30-299-)subp8 234 (p30-299-)subp8 32 (p30-299-)subp6 33 (p30-299-)subp6 SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 PARTITION (subp3,subp5), t2 PARTITION (subp6,subp8); t1a t2a t1b t2b 2 231 (p0-29-)subp3 (p30-299-)subp8 2 233 (p0-29-)subp3 (p30-299-)subp8 2 234 (p0-29-)subp3 (p30-299-)subp8 2 32 (p0-29-)subp3 (p30-299-)subp6 2 33 (p0-29-)subp3 (p30-299-)subp6 21 231 (p0-29-)subp5 (p30-299-)subp8 21 233 (p0-29-)subp5 (p30-299-)subp8 21 234 (p0-29-)subp5 (p30-299-)subp8 21 32 (p0-29-)subp5 (p30-299-)subp6 21 33 (p0-29-)subp5 (p30-299-)subp6 23 231 (p0-29-)subp5 (p30-299-)subp8 23 233 (p0-29-)subp5 (p30-299-)subp8 23 234 (p0-29-)subp5 (p30-299-)subp8 23 32 (p0-29-)subp5 (p30-299-)subp6 23 33 (p0-29-)subp5 (p30-299-)subp6 24 231 (p0-29-)subp5 (p30-299-)subp8 24 233 (p0-29-)subp5 (p30-299-)subp8 24 234 (p0-29-)subp5 (p30-299-)subp8 24 32 (p0-29-)subp5 (p30-299-)subp6 24 33 (p0-29-)subp5 (p30-299-)subp6 3 231 (p0-29-)subp3 (p30-299-)subp8 3 233 (p0-29-)subp3 (p30-299-)subp8 3 234 (p0-29-)subp3 (p30-299-)subp8 3 32 (p0-29-)subp3 (p30-299-)subp6 3 33 (p0-29-)subp3 (p30-299-)subp6 DELETE FROM t2 PARTITION (`p30-299`); SELECT * FROM t1 PARTITION (`p0-29`); a b 1 (p0-29-)subp3 2 (p0-29-)subp3 21 (p0-29-)subp5 22 (p0-29-)subp5 23 (p0-29-)subp5 24 (p0-29-)subp5 3 (p0-29-)subp3 4 (p0-29-)subp3 SELECT * FROM t2 PARTITION (`p30-299`); a b SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 PARTITION (`p0-29`), t2 PARTITION (`p30-299`); t1a t2a t1b t2b SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 PARTITION (`p0-29`,`p30-299`) LEFT JOIN t2 USING (a,b); t1a t2a t1b t2b 1 1 (p0-29-)subp3 (p0-29-)subp3 2 2 (p0-29-)subp3 (p0-29-)subp3 21 21 (p0-29-)subp5 (p0-29-)subp5 22 22 (p0-29-)subp5 (p0-29-)subp5 23 23 (p0-29-)subp5 (p0-29-)subp5 231 NULL (p30-299-)subp8 NULL 232 NULL (p30-299-)subp8 NULL 233 NULL (p30-299-)subp8 NULL 234 NULL (p30-299-)subp8 NULL 24 24 (p0-29-)subp5 (p0-29-)subp5 3 3 (p0-29-)subp3 (p0-29-)subp3 31 NULL (p30-299-)subp6 NULL 32 NULL (p30-299-)subp6 NULL 33 NULL (p30-299-)subp6 NULL 34 NULL (p30-299-)subp6 NULL 4 4 (p0-29-)subp3 (p0-29-)subp3 SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 LEFT JOIN t2 PARTITION (`p0-29`,`p30-299`) USING (a,b); t1a t2a t1b t2b -1 NULL (pNeg-)subp0 NULL -2 NULL (pNeg-)subp0 NULL -3 NULL (pNeg-)subp0 NULL -4 NULL (pNeg-)subp0 NULL 1 1 (p0-29-)subp3 (p0-29-)subp3 2 2 (p0-29-)subp3 (p0-29-)subp3 21 21 (p0-29-)subp5 (p0-29-)subp5 22 22 (p0-29-)subp5 (p0-29-)subp5 23 23 (p0-29-)subp5 (p0-29-)subp5 231 NULL (p30-299-)subp8 NULL 232 NULL (p30-299-)subp8 NULL 233 NULL (p30-299-)subp8 NULL 234 NULL (p30-299-)subp8 NULL 24 24 (p0-29-)subp5 (p0-29-)subp5 299996 NULL (p3000-299999-)subp14 NULL 299997 NULL (p3000-299999-)subp14 NULL 299998 NULL (p3000-299999-)subp14 NULL 299999 NULL (p3000-299999-)subp14 NULL 3 3 (p0-29-)subp3 (p0-29-)subp3 3001 NULL (p3000-299999-)subp12 NULL 3002 NULL (p3000-299999-)subp12 NULL 3003 NULL (p3000-299999-)subp12 NULL 3004 NULL (p3000-299999-)subp12 NULL 301 NULL (p300-2999-)subp8 NULL 302 NULL (p300-2999-)subp8 NULL 303 NULL (p300-2999-)subp8 NULL 304 NULL (p300-2999-)subp8 NULL 31 NULL (p30-299-)subp6 NULL 32 NULL (p30-299-)subp6 NULL 33 NULL (p30-299-)subp6 NULL 34 NULL (p30-299-)subp6 NULL 4 4 (p0-29-)subp3 (p0-29-)subp3 SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 PARTITION (`p0-29`,`p30-299`) RIGHT JOIN t2 USING (a,b); t1a t2a t1b t2b 1 1 (p0-29-)subp3 (p0-29-)subp3 2 2 (p0-29-)subp3 (p0-29-)subp3 21 21 (p0-29-)subp5 (p0-29-)subp5 22 22 (p0-29-)subp5 (p0-29-)subp5 23 23 (p0-29-)subp5 (p0-29-)subp5 24 24 (p0-29-)subp5 (p0-29-)subp5 3 3 (p0-29-)subp3 (p0-29-)subp3 4 4 (p0-29-)subp3 (p0-29-)subp3 NULL -1 NULL (pNeg-)subp0 NULL -2 NULL (pNeg-)subp0 NULL -3 NULL (pNeg-)subp0 NULL -4 NULL (pNeg-)subp0 NULL 299996 NULL (p3000-299999-)subp14 NULL 299997 NULL (p3000-299999-)subp14 NULL 299998 NULL (p3000-299999-)subp14 NULL 299999 NULL (p3000-299999-)subp14 NULL 3001 NULL (p3000-299999-)subp12 NULL 3002 NULL (p3000-299999-)subp12 NULL 3003 NULL (p3000-299999-)subp12 NULL 3004 NULL (p3000-299999-)subp12 NULL 301 NULL (p300-2999-)subp8 NULL 302 NULL (p300-2999-)subp8 NULL 303 NULL (p300-2999-)subp8 NULL 304 NULL (p300-2999-)subp8 SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 RIGHT JOIN t2 PARTITION (`p0-29`,`p30-299`) USING (a,b); t1a t2a t1b t2b 1 1 (p0-29-)subp3 (p0-29-)subp3 2 2 (p0-29-)subp3 (p0-29-)subp3 21 21 (p0-29-)subp5 (p0-29-)subp5 22 22 (p0-29-)subp5 (p0-29-)subp5 23 23 (p0-29-)subp5 (p0-29-)subp5 24 24 (p0-29-)subp5 (p0-29-)subp5 3 3 (p0-29-)subp3 (p0-29-)subp3 4 4 (p0-29-)subp3 (p0-29-)subp3 DELETE FROM t1 PARTITION (`p0-29`); SELECT * FROM t1 PARTITION (`p0-29`); a b SELECT * FROM t2 PARTITION (`p30-299`); a b SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 PARTITION (`p0-29`), t2 PARTITION (`p30-299`); t1a t2a t1b t2b INSERT INTO t1 VALUES (1,'t1-p0-29-ins'); SELECT * FROM t1 PARTITION (`p0-29`); a b 1 t1-p0-29-ins SELECT * FROM t2 PARTITION (`p30-299`); a b SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 PARTITION (`p0-29`), t2 PARTITION (`p30-299`); t1a t2a t1b t2b INSERT INTO t2 VALUES (32,'t2-p30-299-ins'); SELECT * FROM t1 PARTITION (`p0-29`); a b 1 t1-p0-29-ins SELECT * FROM t2 PARTITION (`p30-299`); a b 32 t2-p30-299-ins SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 PARTITION (`p0-29`), t2 PARTITION (`p30-299`); t1a t2a t1b t2b 1 32 t1-p0-29-ins t2-p30-299-ins DELETE FROM t1 PARTITION (`p30-299`); INSERT INTO t1 VALUES (31,'t1-p30-299-ins'); DELETE FROM t2 PARTITION (`p0-29`); INSERT INTO t2 VALUES (2,'t2-p0-29-ins'); SELECT * FROM t1 PARTITION (`p0-29`,`p30-299`); a b 1 t1-p0-29-ins 31 t1-p30-299-ins SELECT * FROM t2 PARTITION (`p0-29`,`p30-299`); a b 2 t2-p0-29-ins 32 t2-p30-299-ins SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 PARTITION (`p0-29`,`p30-299`), t2 PARTITION (`p0-29`,`p30-299`); t1a t2a t1b t2b 1 2 t1-p0-29-ins t2-p0-29-ins 1 32 t1-p0-29-ins t2-p30-299-ins 31 2 t1-p30-299-ins t2-p0-29-ins 31 32 t1-p30-299-ins t2-p30-299-ins SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 PARTITION (`p0-29`,`p30-299`) JOIN t2 PARTITION (`p0-29`,`p30-299`); t1a t2a t1b t2b 1 2 t1-p0-29-ins t2-p0-29-ins 1 32 t1-p0-29-ins t2-p30-299-ins 31 2 t1-p30-299-ins t2-p0-29-ins 31 32 t1-p30-299-ins t2-p30-299-ins SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 PARTITION (`p0-29`,`p30-299`) INNER JOIN t2 PARTITION (`p0-29`,`p30-299`); t1a t2a t1b t2b 1 2 t1-p0-29-ins t2-p0-29-ins 1 32 t1-p0-29-ins t2-p30-299-ins 31 2 t1-p30-299-ins t2-p0-29-ins 31 32 t1-p30-299-ins t2-p30-299-ins SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 PARTITION (`p0-29`,`p30-299`) INNER JOIN t2 PARTITION (`p0-29`,`p30-299`) USING (a,b); t1a t2a t1b t2b SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 PARTITION (`p0-29`,`p30-299`) NATURAL JOIN t2 PARTITION (`p0-29`,`p30-299`); t1a t2a t1b t2b SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 PARTITION (`p0-29`,`p30-299`) LEFT JOIN t2 PARTITION (`p0-29`,`p30-299`) USING (a,b); t1a t2a t1b t2b 1 NULL t1-p0-29-ins NULL 31 NULL t1-p30-299-ins NULL SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 PARTITION (`p0-29`,`p30-299`) RIGHT JOIN t2 PARTITION (`p0-29`,`p30-299`) USING (a,b); t1a t2a t1b t2b NULL 2 NULL t2-p0-29-ins NULL 32 NULL t2-p30-299-ins INSERT INTO t1 VALUES (32,'t2-p30-299-ins'); INSERT INTO t2 VALUES (1,'t1-p0-29-ins'); SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 PARTITION (`p0-29`,`p30-299`) INNER JOIN t2 PARTITION (`p0-29`,`p30-299`) USING (a,b); t1a t2a t1b t2b 1 1 t1-p0-29-ins t1-p0-29-ins 32 32 t2-p30-299-ins t2-p30-299-ins SELECT t1.a AS t1a,t2.a AS t2a,t1.b AS t1b,t2.b AS t2b FROM t1 PARTITION (`p0-29`,`p30-299`) NATURAL JOIN t2 PARTITION (`p0-29`,`p30-299`); t1a t2a t1b t2b 1 1 t1-p0-29-ins t1-p0-29-ins 32 32 t2-p30-299-ins t2-p30-299-ins disconnect session1; connection default; DROP VIEW v1; DROP TABLE t1, t2; DROP USER test_user_1;