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