Debian Codegraph @

mysql-test/suite/parts/r/engine_defined_part_attributes.result

raw
1 #
2 # MDEV-5271 Support engine-defined attributes per partition
3 #
4 # partitioned tables
5 CREATE TABLE `t1` (
6 `id` INT
7 ) ENGINE=InnoDB ENCRYPTED="YES" PARTITION BY RANGE(id) (
8 PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="NO" ENCRYPTION_KEY_ID=1,
9 PARTITION pt2 VALUES LESS THAN MAXVALUE ENCRYPTED="DEFAULT" ENCRYPTION_KEY_ID=1
10 );
11 SHOW CREATE TABLE `t1`;
12 Table Create Table
13 t1 CREATE TABLE `t1` (
14 `id` int(11) DEFAULT NULL
15 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `ENCRYPTED`='YES'
16 PARTITION BY RANGE (`id`)
17 (PARTITION `pt1` VALUES LESS THAN (100) ENGINE = InnoDB ENCRYPTED = 'NO' ENCRYPTION_KEY_ID = 1,
18 PARTITION `pt2` VALUES LESS THAN MAXVALUE ENGINE = InnoDB ENCRYPTED = 'DEFAULT' ENCRYPTION_KEY_ID = 1)
19 INSERT INTO t1 VALUES (1), (2), (3);
20 DELETE FROM t1 WHERE id = 1;
21 UPDATE t1 SET id = 4 WHERE id = 3;
22 SELECT * FROM t1 WHERE id IN (2, 3);
23 id
24 2
25 DROP TABLE `t1`;
26 CREATE TABLE `t2` (
27 `id` INT
28 ) ENGINE=InnoDB ENCRYPTED="YES" ENCRYPTION_KEY_ID=2 PARTITION BY RANGE(id) (
29 PARTITION pt1 VALUES LESS THAN (100),
30 PARTITION pt2 VALUES LESS THAN MAXVALUE
31 );
32 ERROR HY000: Can't create table `test`.`t2` (errno: 140 "Wrong create options")
33 CREATE TABLE `t3` (
34 `id` INT
35 ) ENGINE=InnoDB ENCRYPTED="NO" PARTITION BY RANGE(id) (
36 PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="YES" ENCRYPTION_KEY_ID=2,
37 PARTITION pt2 VALUES LESS THAN MAXVALUE
38 );
39 ERROR HY000: Can't create table `test`.`t3` (errno: 140 "Wrong create options")
40 CREATE TABLE `t4` (
41 `id` INT
42 ) ENGINE=InnoDB ENCRYPTED="NO";
43 SHOW CREATE TABLE `t4`;
44 Table Create Table
45 t4 CREATE TABLE `t4` (
46 `id` int(11) DEFAULT NULL
47 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `ENCRYPTED`='NO'
48 ALTER TABLE `t4` PARTITION BY RANGE(id) (
49 PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="NO",
50 PARTITION pt2 VALUES LESS THAN MAXVALUE ENCRYPTED="DEFAULT"
51 );
52 SHOW CREATE TABLE `t4`;
53 Table Create Table
54 t4 CREATE TABLE `t4` (
55 `id` int(11) DEFAULT NULL
56 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `ENCRYPTED`='NO'
57 PARTITION BY RANGE (`id`)
58 (PARTITION `pt1` VALUES LESS THAN (100) ENGINE = InnoDB ENCRYPTED = 'NO',
59 PARTITION `pt2` VALUES LESS THAN MAXVALUE ENGINE = InnoDB ENCRYPTED = 'DEFAULT')
60 ALTER TABLE `t4` PARTITION BY RANGE(id) (
61 PARTITION pt1 VALUES LESS THAN (100),
62 PARTITION pt2 VALUES LESS THAN MAXVALUE
63 );
64 SHOW CREATE TABLE `t4`;
65 Table Create Table
66 t4 CREATE TABLE `t4` (
67 `id` int(11) DEFAULT NULL
68 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `ENCRYPTED`='NO'
69 PARTITION BY RANGE (`id`)
70 (PARTITION `pt1` VALUES LESS THAN (100) ENGINE = InnoDB,
71 PARTITION `pt2` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
72 ALTER TABLE `t4` PARTITION BY RANGE(id) (
73 PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="YES" ENCRYPTION_KEY_ID=2,
74 PARTITION pt2 VALUES LESS THAN MAXVALUE ENCRYPTED="DEFAULT"
75 );
76 ERROR HY000: Can't create table `test`.`t4` (errno: 140 "Wrong create options")
77 DROP TABLE `t4`;
78 # subpartitioned tables
79 CREATE TABLE `t5` (
80 `id` INT
81 ) ENGINE=InnoDB ENCRYPTED="NO" PARTITION BY RANGE(id)
82 SUBPARTITION BY HASH(id)
83 SUBPARTITIONS 2 (
84 PARTITION pt1 VALUES LESS THAN (100),
85 PARTITION pt2 VALUES LESS THAN MAXVALUE
86 );
87 SHOW CREATE TABLE `t5`;
88 Table Create Table
89 t5 CREATE TABLE `t5` (
90 `id` int(11) DEFAULT NULL
91 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `ENCRYPTED`='NO'
92 PARTITION BY RANGE (`id`)
93 SUBPARTITION BY HASH (`id`)
94 SUBPARTITIONS 2
95 (PARTITION `pt1` VALUES LESS THAN (100) ENGINE = InnoDB,
96 PARTITION `pt2` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
97 DROP TABLE `t5`;
98 CREATE TABLE `t6` (
99 `id` INT
100 ) ENGINE=InnoDB PARTITION BY RANGE(id)
101 SUBPARTITION BY HASH(id)
102 SUBPARTITIONS 2 (
103 PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="YES",
104 PARTITION pt2 VALUES LESS THAN MAXVALUE
105 );
106 ERROR HY000: Can't create table `test`.`t6` (errno: 140 "Wrong create options")
107 CREATE TABLE `t7` (
108 id INT
109 ) ENGINE=InnoDB PARTITION BY RANGE(id)
110 SUBPARTITION BY HASH(id) (
111 PARTITION pt1 VALUES LESS THAN (100)(
112 SUBPARTITION spt1 ENCRYPTED="NO",
113 SUBPARTITION spt2
114 ),
115 PARTITION pt2 VALUES LESS THAN MAXVALUE (
116 SUBPARTITION spt3,
117 SUBPARTITION spt4
118 )
119 );
120 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ENCRYPTED="NO",
121 SUBPARTITION spt2
122 ),
123 PARTITION pt2 VALUES LESS THAN MAXVALUE ...' at line 6
124 CREATE TABLE `t8` (
125 id INT
126 ) ENGINE=InnoDB ENCRYPTED="NO" PARTITION BY RANGE(id)
127 SUBPARTITION BY HASH(id) (
128 PARTITION pt1 VALUES LESS THAN (100) (
129 SUBPARTITION spt1,
130 SUBPARTITION spt2
131 ),
132 PARTITION pt2 VALUES LESS THAN MAXVALUE (
133 SUBPARTITION spt3,
134 SUBPARTITION spt4
135 )
136 );
137 SHOW CREATE TABLE `t8`;
138 Table Create Table
139 t8 CREATE TABLE `t8` (
140 `id` int(11) DEFAULT NULL
141 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `ENCRYPTED`='NO'
142 PARTITION BY RANGE (`id`)
143 SUBPARTITION BY HASH (`id`)
144 (PARTITION `pt1` VALUES LESS THAN (100)
145 (SUBPARTITION `spt1` ENGINE = InnoDB,
146 SUBPARTITION `spt2` ENGINE = InnoDB),
147 PARTITION `pt2` VALUES LESS THAN MAXVALUE
148 (SUBPARTITION `spt3` ENGINE = InnoDB,
149 SUBPARTITION `spt4` ENGINE = InnoDB))
150 DROP TABLE `t8`;
151 CREATE TABLE `t9` (
152 id INT
153 ) ENGINE=InnoDB PARTITION BY RANGE(id)
154 SUBPARTITION BY HASH(id) (
155 PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="NO" (
156 SUBPARTITION spt1,
157 SUBPARTITION spt2
158 ),
159 PARTITION pt2 VALUES LESS THAN MAXVALUE (
160 SUBPARTITION spt3,
161 SUBPARTITION spt4
162 )
163 );
164 SHOW CREATE TABLE `t9`;
165 Table Create Table
166 t9 CREATE TABLE `t9` (
167 `id` int(11) DEFAULT NULL
168 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
169 PARTITION BY RANGE (`id`)
170 SUBPARTITION BY HASH (`id`)
171 (PARTITION `pt1` VALUES LESS THAN (100)
172 (SUBPARTITION `spt1` ENGINE = InnoDB,
173 SUBPARTITION `spt2` ENGINE = InnoDB),
174 PARTITION `pt2` VALUES LESS THAN MAXVALUE
175 (SUBPARTITION `spt3` ENGINE = InnoDB,
176 SUBPARTITION `spt4` ENGINE = InnoDB))
177 DROP TABLE `t9`;
178 CREATE TABLE `t10` (
179 id INT
180 ) ENGINE=InnoDB PARTITION BY RANGE(id)
181 SUBPARTITION BY HASH(id) (
182 PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="YES" (
183 SUBPARTITION spt1,
184 SUBPARTITION spt2
185 ),
186 PARTITION pt2 VALUES LESS THAN MAXVALUE (
187 SUBPARTITION spt3,
188 SUBPARTITION spt4
189 )
190 );
191 ERROR HY000: Can't create table `test`.`t10` (errno: 140 "Wrong create options")
192 CREATE TABLE `t11` (
193 id INT
194 ) ENGINE=InnoDB ENCRYPTED="YES" PARTITION BY RANGE(id)
195 SUBPARTITION BY HASH(id) (
196 PARTITION pt1 VALUES LESS THAN (100) ENCRYPTED="NO" (
197 SUBPARTITION spt1,
198 SUBPARTITION spt2
199 ),
200 PARTITION pt2 VALUES LESS THAN MAXVALUE ENCRYPTED="NO" (
201 SUBPARTITION spt3,
202 SUBPARTITION spt4
203 )
204 );
205 SHOW CREATE TABLE `t11`;
206 Table Create Table
207 t11 CREATE TABLE `t11` (
208 `id` int(11) DEFAULT NULL
209 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `ENCRYPTED`='YES'
210 PARTITION BY RANGE (`id`)
211 SUBPARTITION BY HASH (`id`)
212 (PARTITION `pt1` VALUES LESS THAN (100)
213 (SUBPARTITION `spt1` ENGINE = InnoDB,
214 SUBPARTITION `spt2` ENGINE = InnoDB),
215 PARTITION `pt2` VALUES LESS THAN MAXVALUE
216 (SUBPARTITION `spt3` ENGINE = InnoDB,
217 SUBPARTITION `spt4` ENGINE = InnoDB))
218 DROP TABLE `t11`;
219 #
220 # MDEV-27605 ALTER .. ADD PARTITION uses wrong partition-level option values
221 #
222 # restart: --innodb-sys-tablespaces
223 CREATE TABLE `t12` (
224 id INT
225 ) ENGINE=InnoDB PARTITION BY HASH(id)
226 (
227 pt1 PAGE_COMPRESSED=0
228 );
229 SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t12%';
230 name flag
231 test/t12#P#pt1 21
232 ALTER TABLE `t12` ADD PARTITION (
233 PARTITION pt2 PAGE_COMPRESSED=1
234 );
235 SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t12%';
236 name flag
237 test/t12#P#pt1 21
238 test/t12#P#pt2 1610612789
239 ALTER TABLE `t12` ADD PARTITION (
240 PARTITION pt3 PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=3
241 );
242 SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t12%';
243 name flag
244 test/t12#P#pt1 21
245 test/t12#P#pt2 1610612789
246 test/t12#P#pt3 805306421
247 DROP TABLE `t12`;
248 CREATE TABLE `t13` (
249 `id` INT
250 ) ENGINE=InnoDB PAGE_COMPRESSED=1 PARTITION BY RANGE(id) (
251 PARTITION pt1 VALUES LESS THAN (100) PAGE_COMPRESSED=0,
252 PARTITION pt2 VALUES LESS THAN (200) PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=3,
253 PARTITION pt3 VALUES LESS THAN MAXVALUE
254 );
255 SHOW CREATE TABLE `t13`;
256 Table Create Table
257 t13 CREATE TABLE `t13` (
258 `id` int(11) DEFAULT NULL
259 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `PAGE_COMPRESSED`=1
260 PARTITION BY RANGE (`id`)
261 (PARTITION `pt1` VALUES LESS THAN (100) ENGINE = InnoDB PAGE_COMPRESSED = 0,
262 PARTITION `pt2` VALUES LESS THAN (200) ENGINE = InnoDB PAGE_COMPRESSED = 1 PAGE_COMPRESSION_LEVEL = 3,
263 PARTITION `pt3` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
264 SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t13%';
265 name flag
266 test/t13#P#pt1 21
267 test/t13#P#pt2 805306421
268 test/t13#P#pt3 1610612789
269 ALTER TABLE `t13` PARTITION BY RANGE(id) (
270 PARTITION pt1 VALUES LESS THAN (100) PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=3,
271 PARTITION pt2 VALUES LESS THAN (200),
272 PARTITION pt3 VALUES LESS THAN MAXVALUE PAGE_COMPRESSED=0
273 );
274 SHOW CREATE TABLE `t13`;
275 Table Create Table
276 t13 CREATE TABLE `t13` (
277 `id` int(11) DEFAULT NULL
278 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `PAGE_COMPRESSED`=1
279 PARTITION BY RANGE (`id`)
280 (PARTITION `pt1` VALUES LESS THAN (100) ENGINE = InnoDB PAGE_COMPRESSED = 1 PAGE_COMPRESSION_LEVEL = 3,
281 PARTITION `pt2` VALUES LESS THAN (200) ENGINE = InnoDB,
282 PARTITION `pt3` VALUES LESS THAN MAXVALUE ENGINE = InnoDB PAGE_COMPRESSED = 0)
283 SELECT name, flag FROM information_schema.innodb_sys_tablespaces WHERE name like 'test/t13%';
284 name flag
285 test/t13#P#pt1 805306421
286 test/t13#P#pt2 1610612789
287 test/t13#P#pt3 21
288 DROP TABLE `t13`;