Debian Codegraph @

mysql-test/suite/innodb/r/partition.result

raw
1 #
2 # Test that INFORMATION_SCHEMA.INNODB_COLUMNS shows all the columns
3 # in a non-partitioned table
4 #
5 CREATE TABLE t_nopart (a int PRIMARY KEY, b int, c varchar(64));
6 SELECT lower(t.name), lower(c.name)
7 FROM INFORMATION_SCHEMA.INNODB_COLUMNS c
8 JOIN INFORMATION_SCHEMA.INNODB_TABLES t
9 WHERE t.table_id = c.table_id and t.name like '%t_nopart%'
10 ORDER BY t.name, c.name;
11 lower(t.name) lower(c.name)
12 test/t_nopart a
13 test/t_nopart b
14 test/t_nopart c
15 DROP TABLE t_nopart;
16 #
17 # Test that INFORMATION_SCHEMA.INNODB_COLUMNS shows columns for
18 # all the partitions in a partitioned table
19 #
20 CREATE TABLE t_part
21 (a int,
22 b int,
23 c varchar(64),
24 PRIMARY KEY (a),
25 KEY (b),
26 KEY (c,b))
27 ENGINE = InnoDB
28 PARTITION BY HASH (a) PARTITIONS 5;
29 SELECT lower(t.name), lower(c.name)
30 FROM INFORMATION_SCHEMA.INNODB_COLUMNS c
31 JOIN INFORMATION_SCHEMA.INNODB_TABLES t
32 WHERE t.table_id = c.table_id and t.name like '%t_part%'
33 ORDER BY t.name, c.name;
34 lower(t.name) lower(c.name)
35 test/t_part#p#p0 a
36 test/t_part#p#p0 b
37 test/t_part#p#p0 c
38 test/t_part#p#p1 a
39 test/t_part#p#p1 b
40 test/t_part#p#p1 c
41 test/t_part#p#p2 a
42 test/t_part#p#p2 b
43 test/t_part#p#p2 c
44 test/t_part#p#p3 a
45 test/t_part#p#p3 b
46 test/t_part#p#p3 c
47 test/t_part#p#p4 a
48 test/t_part#p#p4 b
49 test/t_part#p#p4 c
50 DROP TABLE t_part;
51 #
52 # Test that INFORMATION_SCHEMA.INNODB_COLUMNS shows columns for all
53 # the subpartitions of a table
54 #
55 CREATE TABLE t_subpart (a INT, b INT)
56 PARTITION BY RANGE(b)
57 SUBPARTITION BY HASH(b)
58 SUBPARTITIONS 2 (
59 PARTITION p0 VALUES LESS THAN (1000),
60 PARTITION p1 VALUES LESS THAN (2000),
61 PARTITION p2 VALUES LESS THAN MAXVALUE
62 );
63 SELECT lower(t.name), lower(c.name)
64 FROM INFORMATION_SCHEMA.INNODB_COLUMNS c
65 JOIN INFORMATION_SCHEMA.INNODB_TABLES t
66 WHERE t.table_id = c.table_id and t.name like '%t_subpart%'
67 ORDER BY t.name, c.name;
68 lower(t.name) lower(c.name)
69 test/t_subpart#p#p0#sp#p0sp0 a
70 test/t_subpart#p#p0#sp#p0sp0 b
71 test/t_subpart#p#p0#sp#p0sp1 a
72 test/t_subpart#p#p0#sp#p0sp1 b
73 test/t_subpart#p#p1#sp#p1sp0 a
74 test/t_subpart#p#p1#sp#p1sp0 b
75 test/t_subpart#p#p1#sp#p1sp1 a
76 test/t_subpart#p#p1#sp#p1sp1 b
77 test/t_subpart#p#p2#sp#p2sp0 a
78 test/t_subpart#p#p2#sp#p2sp0 b
79 test/t_subpart#p#p2#sp#p2sp1 a
80 test/t_subpart#p#p2#sp#p2sp1 b
81 DROP TABLE t_subpart;
82 #
83 # Test of optimizer estimates
84 #
85 CREATE TABLE t0 (a int PRIMARY KEY, b int, c varchar(64));
86 CREATE TABLE t1
87 (a int,
88 b int,
89 c varchar(64),
90 PRIMARY KEY (a),
91 KEY (b),
92 KEY (c,b))
93 ENGINE = InnoDB
94 PARTITION BY HASH (a) PARTITIONS 5;
95 # Set up the rows so that the following query would match
96 # 100 PK values but only 10 'b' values. But 90 of the matching
97 # PKs will be in the smallest partition.
98 # Since the fix for bug#1364811 will ha_partition only check the biggest
99 # partitions, but ha_innopart will check all partitions when estimating
100 # numbers of records in range. Resulting in ha_partition will
101 # assume there are only 10 matching PK values and choose to do search
102 # by PK but ha_innopart will search by secondary index 'b' since it
103 # will prefer to read 10 records from the secondary index over reading
104 # 100 records from the PRIMARY index.
105 # SELECT * FROM t1 WHERE a BETWEEN 1 AND 450 AND b BETWEEN 1 AND 10;
106 # Using t0 to prevent any issues with purge in t1.
107 INSERT INTO t0 VALUES (1,0,"common"),(2,0,"common"),(3,0,"common"),
108 (4,0,"common"),(5,0,"common"), (6,0,"common"),(7,0,"common"),(8,0,"common"),
109 (9,0,"common"),(10,0,"common");
110 INSERT INTO t0 SELECT a + 10, b, c FROM t0;
111 INSERT INTO t0 SELECT a + 20, b, c FROM t0;
112 INSERT INTO t0 SELECT a + 40, b, c FROM t0;
113 INSERT INTO t0 SELECT a + 80, b, c FROM t0;
114 INSERT INTO t0 SELECT a + 160, b, c FROM t0;
115 INSERT INTO t0 SELECT a + 320, b, c FROM t0;
116 INSERT INTO t0 SELECT a + 640, b, c FROM t0;
117 DELETE FROM t0 WHERE a BETWEEN 13 AND 450 AND (a % 5) <> 0;
118 DELETE FROM t0 WHERE a > 450 AND (a % 5) = 0 LIMIT 250;
119 UPDATE t0 SET b = a, c = "MATCH!" WHERE a <= 10;
120 INSERT INTO t1 SELECT * FROM t0;
121 DROP TABLE t0;
122 ANALYZE TABLE t1;
123 Table Op Msg_type Msg_text
124 test.t1 analyze status OK
125 EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 1 AND 450 AND b BETWEEN 1 AND 10;
126 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
127 1 SIMPLE t1 p0,p1,p2,p3,p4 range PRIMARY,b,c b 9 NULL 10 13.09 Using index condition
128 Warnings:
129 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`a` between 1 and 450) and (`test`.`t1`.`b` between 1 and 10))
130 SELECT * FROM performance_schema.session_status
131 WHERE VARIABLE_NAME LIKE 'dummy';
132 VARIABLE_NAME VARIABLE_VALUE
133 FLUSH STATUS;
134 SELECT * FROM t1 WHERE a BETWEEN 1 AND 450 AND b BETWEEN 1 AND 10;
135 a b c
136 1 1 MATCH!
137 10 10 MATCH!
138 2 2 MATCH!
139 3 3 MATCH!
140 4 4 MATCH!
141 5 5 MATCH!
142 6 6 MATCH!
143 7 7 MATCH!
144 8 8 MATCH!
145 9 9 MATCH!
146 VARIABLE_NAME VARIABLE_VALUE
147 Handler_commit 1
148 Handler_external_lock 3
149 Handler_read_key 5
150 Handler_read_next 10
151 DROP TABLE t1;
152 # Test of index_merge and delete
153 CREATE TABLE `t1` (
154 `a` int(11) NOT NULL,
155 `b` int(11) NOT NULL,
156 KEY `a` (`a`),
157 KEY `b` (`b`)
158 )
159 /*!50100 PARTITION BY HASH (a)
160 PARTITIONS 3 */;
161 Warnings:
162 Warning 1681 Integer display width is deprecated and will be removed in a future release.
163 Warning 1681 Integer display width is deprecated and will be removed in a future release.
164 INSERT INTO t1 VALUES (0,0),(0,0),(1,0),(1,0),(2,0),(2,0),(0,0);
165 INSERT INTO t1 SELECT t1.a,t1.b FROM t1, t1 t2;
166 INSERT INTO t1 VALUES (1,1),(2,2);
167 ANALYZE TABLE t1;
168 Table Op Msg_type Msg_text
169 test.t1 analyze status OK
170 EXPLAIN SELECT * FROM t1 WHERE a = 4 OR b > 0;
171 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
172 1 SIMPLE t1 p0,p1,p2 index_merge a,b a,b 4,4 NULL # 100.00 Using sort_union(a,b); Using where
173 Warnings:
174 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 4) or (`test`.`t1`.`b` > 0))
175 EXPLAIN DELETE FROM t1 WHERE a = 4 OR b > 0;
176 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
177 1 DELETE t1 p0,p1,p2 index_merge a,b a,b 4,4 NULL # 100.00 Using sort_union(a,b); Using where
178 Warnings:
179 Note 1003 delete from `test`.`t1` where ((`test`.`t1`.`a` = 4) or (`test`.`t1`.`b` > 0))
180 SELECT * FROM t1 WHERE a = 4 OR b > 0;
181 a b
182 1 1
183 2 2
184 SELECT COUNT(*) FROM t1;
185 COUNT(*)
186 58
187 DELETE FROM t1 WHERE a = 4 OR b > 0;
188 SELECT COUNT(*) FROM t1;
189 COUNT(*)
190 56
191 SELECT * FROM t1 WHERE a = 4 OR b > 0;
192 a b
193 DROP TABLE t1;
194 # more testing of index_merge and delete
195 CREATE TABLE t1
196 (a int not null,
197 b int not null,
198 INDEX(a),
199 INDEX(b))
200 engine=InnoDB
201 PARTITION BY KEY(a,b) PARTITIONS 5;
202 INSERT INTO t1 VALUES (0,0),(1,1), (2,2), (3,3), (4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
203 INSERT INTO t1 SELECT a + 10, b + 10 FROM t1;
204 INSERT INTO t1 SELECT a + 20, b + 20 FROM t1;
205 INSERT INTO t1 SELECT a + 40, b + 40 FROM t1;
206 INSERT INTO t1 SELECT a + 80, b + 80 FROM t1;
207 INSERT INTO t1 SELECT a + 160, b + 160 FROM t1;
208 ANALYZE TABLE t1;
209 Table Op Msg_type Msg_text
210 test.t1 analyze status OK
211 EXPLAIN SELECT * FROM t1 WHERE a = 4 OR b < 3;
212 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
213 1 SIMPLE t1 p0,p1,p2,p3,p4 index_merge a,b a,b 4,4 NULL # 100.00 Using sort_union(a,b); Using where
214 Warnings:
215 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 4) or (`test`.`t1`.`b` < 3))
216 SELECT * FROM t1 WHERE a = 4 OR b < 3;
217 a b
218 0 0
219 1 1
220 2 2
221 4 4
222 EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 0 AND 20 OR b BETWEEN 10 AND 20;
223 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
224 1 SIMPLE t1 p0,p1,p2,p3,p4 index_merge a,b a,b 4,4 NULL # 100.00 Using sort_union(a,b); Using where
225 Warnings:
226 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` between 0 and 20) or (`test`.`t1`.`b` between 10 and 20))
227 SELECT * FROM t1 WHERE a BETWEEN 0 AND 20 OR b BETWEEN 10 AND 20;
228 a b
229 0 0
230 1 1
231 10 10
232 11 11
233 12 12
234 13 13
235 14 14
236 15 15
237 16 16
238 17 17
239 18 18
240 19 19
241 2 2
242 20 20
243 3 3
244 4 4
245 5 5
246 6 6
247 7 7
248 8 8
249 9 9
250 EXPLAIN DELETE FROM t1 WHERE a = 4 OR b < 3;
251 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
252 1 DELETE t1 p0,p1,p2,p3,p4 index_merge a,b a,b 4,4 NULL # 100.00 Using sort_union(a,b); Using where
253 Warnings:
254 Note 1003 delete from `test`.`t1` where ((`test`.`t1`.`a` = 4) or (`test`.`t1`.`b` < 3))
255 DELETE FROM t1 WHERE a = 4 OR b < 3;
256 EXPLAIN DELETE FROM t1 WHERE a BETWEEN 0 AND 20 OR b BETWEEN 10 AND 20;
257 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
258 1 DELETE t1 p0,p1,p2,p3,p4 index_merge a,b a,b 4,4 NULL # 100.00 Using sort_union(a,b); Using where
259 Warnings:
260 Note 1003 delete from `test`.`t1` where ((`test`.`t1`.`a` between 0 and 20) or (`test`.`t1`.`b` between 10 and 20))
261 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
262 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
263 DELETE FROM t1 WHERE a BETWEEN 0 AND 20 OR b BETWEEN 10 AND 20;
264 DROP TABLE t1;
265 #
266 # Bug#20584754: CAN'T FIND RECORD IN 'TABLE100_KEY_PK_PARTS_2_DATETIME_3'
267 #
268 CREATE TABLE `t1` (
269 `c1` tinyint unsigned DEFAULT NULL,
270 `c2` char(1) DEFAULT NULL,
271 `pk` tinyint NOT NULL,
272 PRIMARY KEY (`pk`),
273 KEY `c1` (`c1`),
274 KEY `c2` (`c2`)
275 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
276 /*!50100 PARTITION BY HASH (pk)
277 PARTITIONS 2 */;
278 # Create the dataset in a separate table, so it can be loaded
279 # directly without update/delete which may give unstable EXPLAIN
280 # due to background pruning.
281 CREATE TABLE t0
282 (a tinyint not null auto_increment primary key,
283 b char(1) DEFAULT 'a',
284 c tinyint DEFAULT 1);
285 INSERT INTO t0 (a) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
286 INSERT INTO t0 (a) SELECT NULL FROM t0, t0 t0_2 LIMIT 50;
287 UPDATE t0 SET c = NULL WHERE a IN (2,3,6,13,18);
288 INSERT INTO t1 SELECT c,b,a FROM t0;
289 DROP TABLE t0;
290 ANALYZE TABLE t1;
291 Table Op Msg_type Msg_text
292 test.t1 analyze status OK
293 EXPLAIN SELECT
294 pk
295 FROM `t1`
296 WHERE `c1` IS NULL OR `c2` = '0';
297 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
298 1 SIMPLE t1 p0,p1 index_merge c1,c2 c1,c2 2,2 NULL # 100.00 Using union(c1,c2); Using where
299 Warnings:
300 Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` where ((`test`.`t1`.`c1` is null) or (`test`.`t1`.`c2` = '0'))
301 SELECT
302 pk
303 FROM `t1`
304 WHERE `c1` IS NULL OR `c2` = '0';
305 pk
306 13
307 18
308 2
309 3
310 6
311 DROP TABLE t1;
312 #
313 # Bug#20516390: PARTITIONED TABLE, TBLSPACE MOVE, ALTER, ASSERT
314 # !(M_FILE_PER_TABLE && M_USE_SHAR
315 #
316 CREATE TABLE t1
317 (a int not null,
318 d int not null,
319 b varchar(198) not null,
320 c varchar(177),
321 index(d),
322 index(a),
323 PRIMARY KEY (b(10), a, d),
324 INDEX (c(95), b(95)),
325 INDEX (b(5), c(10), a))
326 ENGINE=InnoDB stats_persistent=DEFAULT
327 PARTITION BY LINEAR HASH(d) PARTITIONS 2;
328 ALTER TABLE t1 DROP PRIMARY KEY, ALGORITHM=DEFAULT;
329 DROP TABLE t1;
330 #
331 # Bug #20593808: CRASH WITH PARTITIONED TABLES
332 # MULTITABLE DELETE
333 #
334 Test DML with index scan on partitioned table
335 #1. test delete with join using PK index scan on partitioned table
336 CREATE TABLE t1 (
337 col_1_int int,
338 col_2_text text)
339 ENGINE=InnoDB;
340 SHOW CREATE TABLE t1;
341 Table Create Table
342 t1 CREATE TABLE `t1` (
343 `col_1_int` int DEFAULT NULL,
344 `col_2_text` text
345 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
346 #insert two records in t1
347 INSERT INTO t1 VALUES (5, 'row to delete');
348 INSERT INTO t1 VALUES (3, 'row to keep');
349 SELECT * FROM t1 ORDER BY col_1_int;
350 col_1_int col_2_text
351 3 row to keep
352 5 row to delete
353 CREATE TABLE t2 (
354 col_1_int int,
355 col_2_int int,
356 col_3_text text,
357 PRIMARY KEY(col_1_int, col_3_text(100)),
358 KEY idx1 (col_2_int, col_1_int))
359 ENGINE=InnoDB
360 PARTITION BY KEY(col_1_int) partitions 5;
361 SHOW CREATE TABLE t2;
362 Table Create Table
363 t2 CREATE TABLE `t2` (
364 `col_1_int` int NOT NULL,
365 `col_2_int` int DEFAULT NULL,
366 `col_3_text` text NOT NULL,
367 PRIMARY KEY (`col_1_int`,`col_3_text`(100)),
368 KEY `idx1` (`col_2_int`,`col_1_int`)
369 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
370 /*!50100 PARTITION BY KEY (col_1_int)
371 PARTITIONS 5 */
372 #insert five records in t2
373 INSERT INTO t2 VALUES (91, 11, 'bigger value for no-op');
374 INSERT INTO t2 VALUES ( 4, 25, 'smaller value to trigger delete');
375 INSERT INTO t2 VALUES (35, 33, 'bigger value for no-op addlen');
376 INSERT INTO t2 VALUES (55, 42, 'bigger value for no-op addlen + addlen + addlen');
377 INSERT INTO t2 VALUES (82, 54, 'bigger value for no-op addlen + addlen');
378 SELECT * FROM t2 ORDER BY col_1_int;
379 col_1_int col_2_int col_3_text
380 4 25 smaller value to trigger delete
381 35 33 bigger value for no-op addlen
382 55 42 bigger value for no-op addlen + addlen + addlen
383 82 54 bigger value for no-op addlen + addlen
384 91 11 bigger value for no-op
385 # test delete with join and index scan
386 DELETE t1 FROM t2, t1 WHERE t2.col_1_int <= t1.col_1_int;
387 expect one record
388 SELECT * FROM t1;
389 col_1_int col_2_text
390 3 row to keep
391 DROP TABLE t1;
392 DROP TABLE t2;
393 #2. test delete with join using secondary index scan on partitioned table
394 CREATE TABLE t1 (
395 col_1_int int,
396 col_2_text text)
397 ENGINE=InnoDB;
398 SHOW CREATE TABLE t1;
399 Table Create Table
400 t1 CREATE TABLE `t1` (
401 `col_1_int` int DEFAULT NULL,
402 `col_2_text` text
403 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
404 #insert two records in t1
405 INSERT INTO t1 VALUES (5, 'row to delete');
406 INSERT INTO t1 VALUES (3, 'row to keep');
407 SELECT * FROM t1 ORDER BY col_1_int;
408 col_1_int col_2_text
409 3 row to keep
410 5 row to delete
411 CREATE TABLE t2 (
412 col_1_int int,
413 col_2_int int,
414 col_3_text text,
415 PRIMARY KEY(col_2_int, col_1_int),
416 KEY idx1(col_1_int, col_3_text(100)))
417 ENGINE=InnoDB
418 PARTITION BY KEY(col_2_int) partitions 5;
419 SHOW CREATE TABLE t2;
420 Table Create Table
421 t2 CREATE TABLE `t2` (
422 `col_1_int` int NOT NULL,
423 `col_2_int` int NOT NULL,
424 `col_3_text` text,
425 PRIMARY KEY (`col_2_int`,`col_1_int`),
426 KEY `idx1` (`col_1_int`,`col_3_text`(100))
427 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
428 /*!50100 PARTITION BY KEY (col_2_int)
429 PARTITIONS 5 */
430 #insert five records in t2
431 INSERT INTO t2 VALUES (91, 11, 'bigger value for no-op');
432 INSERT INTO t2 VALUES ( 4, 25, 'smaller value to trigger delete');
433 INSERT INTO t2 VALUES (35, 33, 'bigger value for no-op addlen');
434 INSERT INTO t2 VALUES (55, 42, 'bigger value for no-op addlen + addlen + addlen');
435 INSERT INTO t2 VALUES (82, 54, 'bigger value for no-op addlen + addlen');
436 SELECT * FROM t2 ORDER BY col_1_int;
437 col_1_int col_2_int col_3_text
438 4 25 smaller value to trigger delete
439 35 33 bigger value for no-op addlen
440 55 42 bigger value for no-op addlen + addlen + addlen
441 82 54 bigger value for no-op addlen + addlen
442 91 11 bigger value for no-op
443 # test delete with join and index scan
444 DELETE t1 FROM t2, t1 WHERE t2.col_1_int <= t1.col_1_int;
445 expect one record
446 SELECT * FROM t1;
447 col_1_int col_2_text
448 3 row to keep
449 DROP TABLE t1;
450 DROP TABLE t2;
451 #
452 # Bug #20949314: PARTITION_HELPER::PH_RND_INIT(BOOL): ASSERTION
453 #
454 CREATE TABLE t1 (c1 INT);
455 INSERT INTO t1 VALUES (3), (4), (5), (1), (2);
456 CREATE TABLE t2 PARTITION BY KEY (c1) PARTITIONS 3
457 AS
458 SELECT * FROM t1;
459 DELETE A FROM t1 AS A WHERE A.c1 IN
460 (SELECT c1 FROM t2 AS B WHERE B.c1 = A.c1);
461 SELECT * from t1;
462 c1
463 DROP TABLE t1;
464 DROP TABLE t2;
465 #
466 # Bug#21211524: CRASH IN ACTUAL_KEY_PARTS WITH PARTITIONED TABLES
467 #
468 CREATE TABLE t1(
469 col1 SET('') CHARACTER SET UTF16 COLLATE UTF16_SPANISH_CI,
470 col2 INT,
471 PRIMARY KEY (col2, col1),
472 UNIQUE KEY (col1, col2))
473 ENGINE=INNODB
474 PARTITION BY KEY (col1);
475 SELECT * from t1 WHERE col1 BETWEEN 0x92 AND "";
476 col1 col2
477 DROP TABLE t1;
478 #
479 # Bug#21794110: ASSERTION `MP->COUNT > 0 && MY_THREAD_EQUAL
480 #
481 CREATE TABLE t1(col1 INT PRIMARY KEY, col2 CHAR(10))
482 ENGINE=INNODB
483 PARTITION BY HASH(col1) PARTITIONS 2;
484 INSERT INTO t1 VALUES(0, 'string1');
485 ALTER TABLE t1 CHANGE col1 col1 INT UNSIGNED AUTO_INCREMENT;
486 SHOW CREATE TABLE t1;
487 Table Create Table
488 t1 CREATE TABLE `t1` (
489 `col1` int unsigned NOT NULL AUTO_INCREMENT,
490 `col2` char(10) DEFAULT NULL,
491 PRIMARY KEY (`col1`)
492 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
493 /*!50100 PARTITION BY HASH (`col1`)
494 PARTITIONS 2 */
495 DROP TABLE t1;
496 #
497 # Bug#21620577: ASSERTION H->ACTIVE_INDEX == 64
498 #
499 CREATE TABLE t1(col1 YEAR not null, col2 int, KEY(col1), KEY(col1, col2))
500 ENGINE=INNODB
501 PARTITION BY KEY(col1) PARTITIONS 2;
502 INSERT INTO t1 VALUES('2015', 10);
503 SELECT /*+ bka()*/ a.col2 from t1 AS a
504 RIGHT JOIN t1 AS b on a.col1<=>null
505 RIGHT JOIN t1 AS c on 1;
506 col2
507 NULL
508 DROP TABLE t1;
509 #
510 # Bug#21754608: HANDLE_FATAL_SIGNAL PARTITION_HELPER::PH_READ_RANGE_FIRST
511 #
512 CREATE TABLE t1(col1 int PRIMARY KEY, col2 int, col3 int)
513 ENGINE=INNODB
514 PARTITION BY LIST(col1) PARTITIONS 2
515 (PARTITION p0 VALUES IN (11,12),
516 PARTITION p1 VALUES IN (0,1));
517 INSERT INTO t1 values(1, 10, 100);
518 SELECT * from t1 WHERE col1 BETWEEN '2000-01-01 01:02:00' AND '2000-01-01 01:03:00';
519 col1 col2 col3
520 DROP TABLE t1;
521 #
522 # Bug#21755994 ALTER INPLACE CRASHES ON A INNODB TABLE WITH PARTITION ON SEPARATE TABLESPACE
523 #
524 # Note that we do not support partitions in shared tablespaces
525 # (at least not until WL12034).
526 CREATE TABLE t1 ( a INT NOT NULL, PRIMARY KEY (a))
527 ENGINE=InnoDB
528 PARTITION BY RANGE (a) PARTITIONS 3
529 ( PARTITION P1 VALUES LESS THAN (2),
530 PARTITION P2 VALUES LESS THAN (4) TABLESPACE `innodb_system`,
531 PARTITION P3 VALUES LESS THAN (6));
532 ERROR HY000: InnoDB : A partitioned table is not allowed in a shared tablespace.
533 CREATE TABLE t1 ( a INT NOT NULL, PRIMARY KEY (a))
534 ENGINE=InnoDB
535 PARTITION BY RANGE (a) PARTITIONS 3
536 ( PARTITION P1 VALUES LESS THAN (2),
537 PARTITION P2 VALUES LESS THAN (4) TABLESPACE `innodb_file_per_table`,
538 PARTITION P3 VALUES LESS THAN (6));
539 ALTER TABLE t1 ADD COLUMN f int;
540 DROP TABLE t1;
541 #
542 # Bug#21957001 INNODB: FAILING ASSERTION: 0 IN FILE HA_INNOPART.CC LINE 3526
543 #
544 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Trying to access missing tablespace .*");
545 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Missing .ibd file for table `test`\.`t1` .* ");
546 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Cannot calculate statistics for table .*");
547 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Operating system error number 2 in a file operation.");
548 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* The error means the system cannot find the path specified.");
549 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Cannot open datafile for read-only:");
550 CREATE TABLE t1(c1 INT,c2 CHAR(1)) PARTITION BY KEY(c1) PARTITIONS 4;
551 ALTER TABLE t1 DISCARD PARTITION p3 TABLESPACE;
552 ANALYZE TABLE t1;
553 Table Op Msg_type Msg_text
554 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p3` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
555 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p3` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
556 test.t1 analyze status Operation failed
557 SELECT TABLE_ROWS FROM information_schema.tables where table_name = 't1';
558 TABLE_ROWS
559 0
560 ALTER TABLE t1 DISCARD PARTITION p1 TABLESPACE;
561 Warnings:
562 Warning 1814 InnoDB: Tablespace has been discarded for table 't1'
563 ANALYZE TABLE t1;
564 Table Op Msg_type Msg_text
565 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p1` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
566 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p3` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
567 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p1` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
568 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p3` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
569 test.t1 analyze status Operation failed
570 SELECT TABLE_ROWS FROM information_schema.tables where table_name = 't1';
571 TABLE_ROWS
572 0
573 ALTER TABLE t1 DISCARD PARTITION p2 TABLESPACE;
574 Warnings:
575 Warning 1814 InnoDB: Tablespace has been discarded for table 't1'
576 Warning 1814 InnoDB: Tablespace has been discarded for table 't1'
577 ANALYZE TABLE t1;
578 Table Op Msg_type Msg_text
579 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p1` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
580 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p2` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
581 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p3` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
582 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p1` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
583 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p2` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
584 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p3` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
585 test.t1 analyze status Operation failed
586 SELECT TABLE_ROWS FROM information_schema.tables where table_name = 't1';
587 TABLE_ROWS
588 0
589 ALTER TABLE t1 DISCARD PARTITION p0 TABLESPACE;
590 Warnings:
591 Warning 1814 InnoDB: Tablespace has been discarded for table 't1'
592 Warning 1814 InnoDB: Tablespace has been discarded for table 't1'
593 Warning 1814 InnoDB: Tablespace has been discarded for table 't1'
594 ANALYZE TABLE t1;
595 Table Op Msg_type Msg_text
596 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p0` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
597 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p1` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
598 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p2` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
599 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p3` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
600 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p0` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
601 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p1` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
602 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p2` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
603 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p3` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
604 test.t1 analyze status Operation failed
605 SELECT TABLE_ROWS FROM information_schema.tables where table_name = 't1';
606 TABLE_ROWS
607 0
608 DROP TABLE t1;
609 CREATE TABLE t1(c1 INT,c2 CHAR(1)) PARTITION BY KEY(c1) PARTITIONS 4;
610 ALTER TABLE t1 DISCARD TABLESPACE;
611 ANALYZE TABLE t1;
612 Table Op Msg_type Msg_text
613 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p0` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
614 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p1` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
615 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p2` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
616 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p3` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
617 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p0` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
618 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p1` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
619 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p2` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
620 test.t1 analyze Warning InnoDB: Trying to get the free space for partition `test`.`t1` /* Partition `p3` */ but its tablespace has been discarded or the .ibd file is missing. Setting the free space of the partition to zero.
621 test.t1 analyze status Operation failed
622 SELECT TABLE_ROWS FROM information_schema.tables where table_name = 't1';
623 TABLE_ROWS
624 0
625 DROP TABLE t1;
626 #
627 # Bug#21864838: SIG11 IN FIELD_BLOB::CMP| AT SQL/FIELD.CC
628 #
629 CREATE TABLE t1 (col1 INT PRIMARY KEY, col2 VARCHAR(1), col3 BLOB AS (REPEAT(col2, 500)) VIRTUAL, KEY(col3(100)))
630 ENGINE=INNODB
631 PARTITION BY HASH(col1) PARTITIONS 2;
632 CREATE TABLE t2 (col1 INT PRIMARY KEY, col2 VARCHAR(1), col3 BLOB AS (REPEAT(col2, 500)) VIRTUAL, KEY(col3(100)))
633 ENGINE=INNODB
634 PARTITION BY HASH(col1) PARTITIONS 2;
635 INSERT INTO t1 (col1, col2) VALUES(1, 'd'), (2, 'c'), (3, 'b'), (4, 'a');
636 INSERT INTO t2 (col1, col2) VALUES(1, 'd'), (2, 'c'), (3, 'b'), (4, 'a');
637 SELECT t1.col1 FROM t1, t2 where t1.col1 = 3 AND t1.col3 = t2.col3 ORDER BY t2.col1 DESC;
638 col1
639 3
640 DROP TABLE t1;
641 DROP TABLE t2;
642 #
643 # Bug #21881155: UNINTIALIZED READ IN KEY_REC_CMP CAUSE CRASH LATER
644 #
645 CREATE TABLE t1 (col1 int, col2 BLOB AS ('a') VIRTUAL, col3 INT,
646 PRIMARY KEY(col1), KEY (col3, col2(1), col1))
647 ENGINE=INNODB
648 PARTITION BY KEY (col1) PARTITIONS 2;
649 INSERT INTO t1(col1) values (1),(2);
650 SELECT 1 FROM t1 WHERE col2 > 'a' GROUP BY col3;
651 1
652 DROP TABLE t1;
653 # Bug#22444530 - GCOLS + PARTITIONED TABLE, CRASH IN
654 #
655 set sql_mode='';
656 create table t (
657 a int not null,
658 b int generated always as (1) virtual not null,
659 c int generated always as (1) virtual not null,
660 key (c)
661 ) engine=innodb partition by key (a) partitions 2;
662 insert into t(a) values(1);
663 select b from t group by c;
664 b
665 1
666 drop table t;
667 create table t (
668 a int not null,
669 b blob generated always as ("a") virtual not null,
670 c int generated always as (1) virtual not null,
671 key (c)
672 ) engine=innodb partition by key (a) partitions 2;
673 insert into t(a) values(1);
674 select b from t group by c;
675 b
676 a
677 drop table t;
678 #
679 # Bug#23037025 COL+PARTITION SIG11 IN ROW_SEL_STORE_MYSQL_REC
680 #
681 set sql_mode='';
682 CREATE TABLE t1 (
683 col1 INT,
684 col2 INT AS (col1) VIRTUAL NOT NULL,
685 col3 INT AS (1) VIRTUAL,
686 KEY (col3)
687 ) ENGINE=INNODB PARTITION BY KEY (col1) PARTITIONS 2;
688 CREATE TABLE t2 (col1 INTEGER);
689 CREATE TABLE t3 (col1 INTEGER);
690 CREATE TRIGGER t2_trig1 BEFORE INSERT ON t2 FOR EACH ROW
691 INSERT INTO t3 SELECT col2 FROM t1 GROUP BY col3;
692 INSERT IGNORE INTO t1(col1) values(NULL);
693 INSERT INTO t2 VALUES(1);
694 ERROR 23000: Column 'col2' cannot be null
695 DROP TRIGGER t2_trig1;
696 DROP TABLE t3;
697 DROP TABLE t2;
698 DROP TABLE t1;
699 #
700 # Bug#23194259 ASSERTION FAILURE:TRX0TRX.H:1416:SRV_READ_ONLY_MODE
701 #
702 CREATE TABLE t1(col1 INT AS(1), KEY(col1))
703 ENGINE=INNODB
704 PARTITION BY KEY(col1) PARTITIONS 2;
705 SELECT * FROM t1 WHERE sha(uuid_short());
706 col1
707 DROP TABLE t1;