Debian Codegraph @

mysql-test/suite/s3/partition.result

raw
1 # Test for COALESCE PARTITION, ALTER TABLE and ADD PARTITIONS
2 # for tables with HASH partitions
3 CREATE TABLE t1 (
4 c1 INT DEFAULT NULL
5 ) ENGINE=Aria
6 PARTITION BY HASH (c1)
7 PARTITIONS 3;
8 INSERT INTO t1 VALUE (1), (2), (101), (102), (201), (202);
9 ALTER TABLE t1 ENGINE=S3;
10 SELECT count(*) FROM t1;
11 count(*)
12 6
13 SHOW TABLES;
14 Tables_in_s3
15 t1
16 ALTER TABLE t1 COALESCE PARTITION 2;
17 ERROR 42000: Table 't1' uses an extension that doesn't exist in this MariaDB version
18 SHOW WARNINGS;
19 Level Code Message
20 Error 1112 Table 't1' uses an extension that doesn't exist in this MariaDB version
21 ALTER TABLE t1 ADD PARTITION PARTITIONS 6;
22 SELECT count(*) FROM t1;
23 count(*)
24 6
25 ALTER TABLE t1 ADD COLUMN c INT;
26 SELECT count(*) FROM t1;
27 count(*)
28 6
29 DROP TABLE t1;
30 # Test for simple change engine to S3
31 CREATE TABLE t1 (
32 c1 int DEFAULT NULL,
33 c2 int DEFAULT NULL
34 ) ENGINE=Aria
35 PARTITION BY RANGE (c1)
36 SUBPARTITION BY HASH(c2)
37 SUBPARTITIONS 2
38 (PARTITION p0 VALUES LESS THAN (100),
39 PARTITION p1 VALUES LESS THAN (200),
40 PARTITION p3 VALUES LESS THAN (300));
41 INSERT INTO t1 VALUE (1,1), (2,2), (101,101), (102,102), (201,201), (202,202);
42 ALTER TABLE t1 ENGINE=S3;
43 SELECT count(*) FROM t1;
44 count(*)
45 6
46 # Test for rename table
47 RENAME TABLE t1 TO t2;
48 SELECT count(*) FROM t2;
49 count(*)
50 6
51 # Test for TRUNCATE, ANALYZE, CHECK, REBUILD, OPTIMIZE, REPAIR,
52 # ADD, DROP, REORGANIZE partition
53 ALTER TABLE t2 TRUNCATE PARTITION p3;
54 ERROR HY000: Table 't2' is read only
55 ALTER TABLE t2 ANALYZE PARTITION p3;
56 Table Op Msg_type Msg_text
57 s3.t2 analyze status Table 's3.t2' is read only
58 SELECT count(*) FROM t2;
59 count(*)
60 6
61 ALTER TABLE t2 CHECK PARTITION p3;
62 Table Op Msg_type Msg_text
63 s3.t2 check status OK
64 SELECT count(*) FROM t2;
65 count(*)
66 6
67 ALTER TABLE t2 REBUILD PARTITION p0, p1;
68 ERROR 42000: Table 't2' uses an extension that doesn't exist in this MariaDB version
69 ALTER TABLE t2 OPTIMIZE PARTITION p0, p1;
70 Table Op Msg_type Msg_text
71 s3.t2 optimize Error Table 't2' is read only
72 s3.t2 optimize status Operation failed
73 SELECT count(*) FROM t2;
74 count(*)
75 6
76 ALTER TABLE t2 REPAIR PARTITION p0, p1;
77 Table Op Msg_type Msg_text
78 s3.t2 repair Error Table 't2' is read only
79 s3.t2 repair status Operation failed
80 SELECT count(*) FROM t2;
81 count(*)
82 6
83 ALTER TABLE t2 ADD PARTITION (PARTITION p4 VALUES LESS THAN (400));
84 SHOW CREATE TABLE t2;
85 Table Create Table
86 t2 CREATE TABLE `t2` (
87 `c1` int(11) DEFAULT NULL,
88 `c2` int(11) DEFAULT NULL
89 ) ENGINE=S3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
90 PARTITION BY RANGE (`c1`)
91 SUBPARTITION BY HASH (`c2`)
92 SUBPARTITIONS 2
93 (PARTITION `p0` VALUES LESS THAN (100) ENGINE = S3,
94 PARTITION `p1` VALUES LESS THAN (200) ENGINE = S3,
95 PARTITION `p3` VALUES LESS THAN (300) ENGINE = S3,
96 PARTITION `p4` VALUES LESS THAN (400) ENGINE = S3)
97 ALTER TABLE t2
98 REORGANIZE PARTITION p4 INTO (
99 PARTITION n0 VALUES LESS THAN (500),
100 PARTITION n1 VALUES LESS THAN (600)
101 );
102 ERROR 42000: Table 't2' uses an extension that doesn't exist in this MariaDB version
103 ALTER TABLE t2 DROP PARTITION p3;
104 SHOW CREATE TABLE t2;
105 Table Create Table
106 t2 CREATE TABLE `t2` (
107 `c1` int(11) DEFAULT NULL,
108 `c2` int(11) DEFAULT NULL
109 ) ENGINE=S3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
110 PARTITION BY RANGE (`c1`)
111 SUBPARTITION BY HASH (`c2`)
112 SUBPARTITIONS 2
113 (PARTITION `p0` VALUES LESS THAN (100) ENGINE = S3,
114 PARTITION `p1` VALUES LESS THAN (200) ENGINE = S3,
115 PARTITION `p4` VALUES LESS THAN (400) ENGINE = S3)
116 SELECT count(*) from t2;
117 count(*)
118 4
119 # Test for ALTER TABLE
120 ALTER TABLE t2 ADD COLUMN c INT;
121 SELECT count(*) FROM t2;
122 count(*)
123 4
124 ALTER TABLE t2 DROP COLUMN c;
125 SELECT count(*) FROM t2;
126 count(*)
127 4
128 # Test for REMOVE PARTITIONING
129 ALTER TABLE t2 REMOVE PARTITIONING;
130 SHOW CREATE TABLE t2;
131 Table Create Table
132 t2 CREATE TABLE `t2` (
133 `c1` int(11) DEFAULT NULL,
134 `c2` int(11) DEFAULT NULL
135 ) ENGINE=S3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1
136 SELECT count(*) FROM t2;
137 count(*)
138 4
139 DROP TABLE t2;
140 # Test for EXCHANGE PARTITION
141 CREATE TABLE t1 (
142 c1 int DEFAULT NULL
143 ) ENGINE=Aria
144 PARTITION BY RANGE (c1)
145 (PARTITION p0 VALUES LESS THAN (100),
146 PARTITION p1 VALUES LESS THAN (200));
147 INSERT INTO t1 VALUE (1), (2), (101), (102);
148 ALTER TABLE t1 ENGINE=S3;
149 CREATE TABLE t_part (
150 c1 int DEFAULT NULL
151 ) ENGINE=Aria;
152 INSERT INTO t_part VALUE (120), (130), (140);
153 ALTER TABLE t_part ENGINE=S3;
154 ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t_part;
155 SELECT count(*) FROM t_part;
156 count(*)
157 2
158 SELECT count(*) FROM t1;
159 count(*)
160 5
161 DROP TABLE t1;
162 DROP TABLE t_part;