Debian Codegraph @

mysql-test/suite/storage_engine/parts/create_table.result

raw
1 DROP TABLE IF EXISTS t1;
2 CREATE TABLE t1 (a <INT_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY HASH(a) PARTITIONS 2;
3 INSERT INTO t1 (a) VALUES (1),(2),(3),(2);
4 EXPLAIN PARTITIONS SELECT a FROM t1;
5 id select_type table partitions type possible_keys key key_len ref rows Extra
6 1 SIMPLE t1 p0,p1 # # # # # # #
7 EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a=2;
8 id select_type table partitions type possible_keys key key_len ref rows Extra
9 1 SIMPLE t1 p0 # # # # # # #
10 DROP TABLE t1;
11 CREATE TABLE t1 (a <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY KEY(a) PARTITIONS 2;
12 INSERT INTO t1 (a) VALUES ('a'),('b'),('c');
13 EXPLAIN PARTITIONS SELECT a FROM t1;
14 id select_type table partitions type possible_keys key key_len ref rows Extra
15 1 SIMPLE t1 p0,p1 # # # # # # #
16 EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a = 'b';
17 id select_type table partitions type possible_keys key key_len ref rows Extra
18 1 SIMPLE t1 p1 # # # # # # #
19 DROP TABLE t1;
20 CREATE TABLE t1 (a <INT_COLUMN>, <CUSTOM_INDEX> (a)) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY KEY(a) PARTITIONS 2;
21 SHOW INDEX IN t1;
22 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
23 t1 1 a 1 a # # NULL NULL # #
24 INSERT INTO t1 (a) VALUES (1),(2),(3),(5);
25 EXPLAIN PARTITIONS SELECT a FROM t1;
26 id select_type table partitions type possible_keys key key_len ref rows Extra
27 1 SIMPLE t1 p0,p1 # # # # # # #
28 EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a IN (1,3);
29 id select_type table partitions type possible_keys key key_len ref rows Extra
30 1 SIMPLE t1 p0 # # # # # # #
31 DROP TABLE t1;
32 CREATE TABLE t1 (a <INT_COLUMN> PRIMARY KEY) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY KEY() PARTITIONS 2;
33 SHOW INDEX IN t1;
34 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
35 t1 0 PRIMARY 1 a # # NULL NULL # #
36 INSERT INTO t1 (a) VALUES (1),(200),(3),(2);
37 EXPLAIN PARTITIONS SELECT a FROM t1;
38 id select_type table partitions type possible_keys key key_len ref rows Extra
39 1 SIMPLE t1 p0,p1 # # # # # # #
40 EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a=2;
41 id select_type table partitions type possible_keys key key_len ref rows Extra
42 1 SIMPLE t1 p1 # # # # # # #
43 DROP TABLE t1;
44 CREATE TABLE t1 (a <INT_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY RANGE(a) (
45 PARTITION p0 VALUES LESS THAN (10),
46 PARTITION p1 VALUES LESS THAN (1000)
47 );
48 INSERT INTO t1 (a) VALUES (1),(2),(400);
49 EXPLAIN PARTITIONS SELECT a FROM t1;
50 id select_type table partitions type possible_keys key key_len ref rows Extra
51 1 SIMPLE t1 p0,p1 # # # # # # #
52 EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a = 2;
53 id select_type table partitions type possible_keys key key_len ref rows Extra
54 1 SIMPLE t1 p0 # # # # # # #
55 INSERT INTO t1 (a) VALUES (10000);
56 ERROR HY000: Table has no partition for value 10000
57 DROP TABLE t1;
58 CREATE TABLE t1 (a <INT_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY LIST(a) (
59 PARTITION abc VALUES IN (1,2,3),
60 PARTITION def VALUES IN (100,101,102)
61 );
62 INSERT INTO t1 (a) VALUES (1),(101),(1);
63 EXPLAIN PARTITIONS SELECT a FROM t1;
64 id select_type table partitions type possible_keys key key_len ref rows Extra
65 1 SIMPLE t1 abc,def # # # # # # #
66 EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a = 100;
67 id select_type table partitions type possible_keys key key_len ref rows Extra
68 1 SIMPLE NULL NULL # # # # # # #
69 INSERT INTO t1 (a) VALUES (50);
70 ERROR HY000: Table has no partition for value 50
71 DROP TABLE t1;
72 CREATE TABLE t1 (a <INT_COLUMN>, b <INT_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY LIST(a) SUBPARTITION by HASH(b) (
73 PARTITION abc VALUES IN (1,2,3),
74 PARTITION def VALUES IN (100,101,102)
75 );
76 SHOW INDEX IN t1;
77 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
78 INSERT INTO t1 (a,b) VALUES (1,1),(101,2),(1,3);
79 EXPLAIN PARTITIONS SELECT a FROM t1;
80 id select_type table partitions type possible_keys key key_len ref rows Extra
81 1 SIMPLE t1 abc_abcsp0,def_defsp0 # # # # # # #
82 EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a = 100;
83 id select_type table partitions type possible_keys key key_len ref rows Extra
84 1 SIMPLE NULL NULL # # # # # # #
85 SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, PARTITION_METHOD, SUBPARTITION_METHOD
86 FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 't1';
87 TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_METHOD SUBPARTITION_METHOD
88 test t1 abc abcsp0 LIST HASH
89 test t1 def defsp0 LIST HASH
90 SELECT * FROM INFORMATION_SCHEMA.PARTITIONS;
91 DROP TABLE t1;