Debian Codegraph @

mysql-test/suite/parts/r/partition-dml-1-2-innodb.result

raw
1 # Additional tests for WL#5217 by QA, testplan 1.1
2 CREATE TABLE t1
3 (a INT NOT NULL,
4 b varchar (64),
5 INDEX ind_t1 (b,a),
6 PRIMARY KEY (a))
7 ENGINE = InnoDB
8 PARTITION BY RANGE (a)
9 SUBPARTITION BY HASH (a) SUBPARTITIONS 3
10 (PARTITION pNeg VALUES LESS THAN (0)
11 (SUBPARTITION subp0 ,
12 SUBPARTITION subp1 ,
13 SUBPARTITION subp2 ),
14 PARTITION `p0-29` VALUES LESS THAN (30)
15 (SUBPARTITION subp3 ,
16 SUBPARTITION subp4 ,
17 SUBPARTITION subp5 ),
18 PARTITION `p30-299` VALUES LESS THAN (300)
19 (SUBPARTITION subp6 ,
20 SUBPARTITION subp7 ,
21 SUBPARTITION subp8 ),
22 PARTITION `p300-2999` VALUES LESS THAN (3000)
23 (SUBPARTITION subp9 ,
24 SUBPARTITION subp10 ,
25 SUBPARTITION subp11 ),
26 PARTITION `p3000-299999` VALUES LESS THAN (300000)
27 (SUBPARTITION subp12 ,
28 SUBPARTITION subp13 ,
29 SUBPARTITION subp14 ));
30 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-4, '(pNeg-)subp0');
31 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, '(pNeg-)subp0');
32 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, '(pNeg-)subp0');
33 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-1, '(pNeg-)subp0');
34 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (4, '(p0-29-)subp3');
35 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (3, '(p0-29-)subp3');
36 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (2, '(p0-29-)subp3');
37 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (1, '(p0-29-)subp3');
38 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (24, '(p0-29-)subp5');
39 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (23, '(p0-29-)subp5');
40 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (22, '(p0-29-)subp5');
41 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (21, '(p0-29-)subp5');
42 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (34, '(p30-299-)subp6');
43 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (33, '(p30-299-)subp6');
44 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (32, '(p30-299-)subp6');
45 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (31, '(p30-299-)subp6');
46 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (234, '(p30-299-)subp8');
47 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (233, '(p30-299-)subp8');
48 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (232, '(p30-299-)subp8');
49 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (231, '(p30-299-)subp8');
50 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (304, '(p300-2999-)subp8');
51 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (303, '(p300-2999-)subp8');
52 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (302, '(p300-2999-)subp8');
53 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (301, '(p300-2999-)subp8');
54 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3004, '(p3000-299999-)subp12');
55 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3003, '(p3000-299999-)subp12');
56 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3002, '(p3000-299999-)subp12');
57 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3001, '(p3000-299999-)subp12');
58 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299996, '(p3000-299999-)subp14');
59 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299997, '(p3000-299999-)subp14');
60 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299998, '(p3000-299999-)subp14');
61 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299999, '(p3000-299999-)subp14');
62 CREATE TABLE t2
63 (a INT NOT NULL,
64 b varchar (64),
65 INDEX ind_t2 (b,a),
66 PRIMARY KEY (a))
67 ENGINE = InnoDB
68 PARTITION BY RANGE (a)
69 SUBPARTITION BY HASH (a) SUBPARTITIONS 3
70 (PARTITION pNeg VALUES LESS THAN (0)
71 (SUBPARTITION subp0 ,
72 SUBPARTITION subp1 ,
73 SUBPARTITION subp2 ),
74 PARTITION `p0-29` VALUES LESS THAN (30)
75 (SUBPARTITION subp3 ,
76 SUBPARTITION subp4 ,
77 SUBPARTITION subp5 ),
78 PARTITION `p30-299` VALUES LESS THAN (300)
79 (SUBPARTITION subp6 ,
80 SUBPARTITION subp7 ,
81 SUBPARTITION subp8 ),
82 PARTITION `p300-2999` VALUES LESS THAN (3000)
83 (SUBPARTITION subp9 ,
84 SUBPARTITION subp10 ,
85 SUBPARTITION subp11 ),
86 PARTITION `p3000-299999` VALUES LESS THAN (300000)
87 (SUBPARTITION subp12 ,
88 SUBPARTITION subp13 ,
89 SUBPARTITION subp14 ));
90 CREATE
91 PROCEDURE p1 ()
92 BEGIN
93 DECLARE c11,c21 int;
94 DECLARE c12,c22 varchar(64);
95 DECLARE cur1 CURSOR FOR SELECT * FROM t1 PARTITION (pneg,`p0-29`,`p30-299`,`p300-2999`,`p3000-299999`) ORDER BY a,b;
96 DECLARE cur2 CURSOR FOR SELECT * FROM t1 ORDER BY a,b;
97 DECLARE CONTINUE HANDLER FOR NOT FOUND SET @done = 1;
98 OPEN cur1;
99 OPEN cur2;
100 read_loop: LOOP
101 FETCH cur1 INTO c11,c12;
102 FETCH cur2 INTO c21,c22;
103 IF !((c11=c21) AND (c12=c22)) THEN
104 SELECT c11,c12,c21,c22;
105 LEAVE read_loop;
106 END IF;
107 IF @done THEN
108 LEAVE read_loop;
109 END IF;
110 END LOOP;
111 CLOSE cur1;
112 CLOSE cur2;
113 END//
114 Warnings:
115 Warning 1287 '!' is deprecated and will be removed in a future release. Please use NOT instead
116 CREATE PROCEDURE p2 ()
117 BEGIN
118 UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd' WHERE a BETWEEN 3 AND 10;
119 END//
120 CREATE PROCEDURE p3 ()
121 BEGIN
122 DELETE FROM t1 PARTITION (`p0-29`) WHERE b= 'p0-29-upd';
123 END//
124 CREATE TRIGGER tr1 AFTER INSERT ON t2
125 FOR EACH ROW BEGIN
126 UPDATE t1 PARTITION (`p30-299`) SET b='p30-299-upd-trigger' WHERE a BETWEEN 30 AND 40;
127 END//
128 CALL p1;
129 CALL p2;
130 SELECT * FROM t1 PARTITION (`p0-29`) WHERE a BETWEEN 3 AND 10 ORDER BY a,b ;
131 a b
132 3 p0-29-upd
133 4 p0-29-upd
134 SELECT * FROM t1 WHERE a BETWEEN 3 AND 10 ORDER BY a,b ;
135 a b
136 3 p0-29-upd
137 4 p0-29-upd
138 CALL p3;
139 SELECT * FROM t1 PARTITION (`p0-29`) WHERE a BETWEEN 3 AND 10 ORDER BY a,b ;
140 a b
141 CALL p3;
142 INSERT INTO t2 PARTITION (`p0-29`, subp3) VALUES (4, '(p0-29-)subp3');
143 SELECT * FROM t2 PARTITION (`p0-29`) WHERE a BETWEEN 3 AND 10 ORDER BY a,b ;
144 a b
145 4 (p0-29-)subp3
146 SELECT * FROM t1 PARTITION (`p30-299`) WHERE a BETWEEN 30 AND 40 ORDER BY a,b ;
147 a b
148 31 p30-299-upd-trigger
149 32 p30-299-upd-trigger
150 33 p30-299-upd-trigger
151 34 p30-299-upd-trigger
152 DELETE FROM t1 PARTITION (pneg, `p30-299`);
153 SELECT * FROM t1 ORDER BY a,b;
154 a b
155 1 (p0-29-)subp3
156 2 (p0-29-)subp3
157 21 (p0-29-)subp5
158 22 (p0-29-)subp5
159 23 (p0-29-)subp5
160 24 (p0-29-)subp5
161 301 (p300-2999-)subp8
162 302 (p300-2999-)subp8
163 303 (p300-2999-)subp8
164 304 (p300-2999-)subp8
165 3001 (p3000-299999-)subp12
166 3002 (p3000-299999-)subp12
167 3003 (p3000-299999-)subp12
168 3004 (p3000-299999-)subp12
169 299996 (p3000-299999-)subp14
170 299997 (p3000-299999-)subp14
171 299998 (p3000-299999-)subp14
172 299999 (p3000-299999-)subp14
173 ########## Empty table t1 #########
174 DELETE FROM t1;
175 CALL p1;
176 CALL p2;
177 CALL p3;
178 DROP PROCEDURE p1;
179 DROP PROCEDURE p2;
180 DROP PROCEDURE p3;
181 DROP TRIGGER tr1;
182 DROP TABLE t1;
183 DROP TABLE t2;