Debian Codegraph @

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

raw
1 #
2 # Basic CREATE TABLE .. PARTITION statements
3 #
4
5 --source include/have_partition.inc
6 --source ../have_engine.inc
7
8 --disable_warnings
9 DROP TABLE IF EXISTS t1;
10 --enable_warnings
11
12 let $create_definition = a $int_col;
13 let $partition_options = PARTITION BY HASH(a) PARTITIONS 2;
14 --source ../create_table.inc
15 if ($mysql_errname)
16 {
17 --let $my_last_stmt = $create_statement
18 --let $functionality = Partitions
19 --source ../unexpected_result.inc
20 }
21 if (!$mysql_errname)
22 {
23 INSERT INTO t1 (a) VALUES (1),(2),(3),(2);
24 # We are only interested in partition information
25 --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 #
26 EXPLAIN PARTITIONS SELECT a FROM t1;
27 --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 #
28 EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a=2;
29
30 DROP TABLE t1;
31 }
32
33 let $create_definition = a $char_col;
34 let $partition_options = PARTITION BY KEY(a) PARTITIONS 2;
35 --source ../create_table.inc
36 if ($mysql_errname)
37 {
38 --let $my_last_stmt = $create_statement
39 --let $functionality = Partitions or CHAR types
40 --source ../unexpected_result.inc
41 }
42 if (!$mysql_errname)
43 {
44 INSERT INTO t1 (a) VALUES ('a'),('b'),('c');
45 --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 #
46 EXPLAIN PARTITIONS SELECT a FROM t1;
47 --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 #
48 EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a = 'b';
49 DROP TABLE t1;
50 }
51
52 --let $continue = 1
53 --source ../have_default_index.inc
54 if ($have_default_index)
55 {
56 let $create_definition = a $int_indexed_col, $default_index (a);
57 let $partition_options = PARTITION BY KEY(a) PARTITIONS 2;
58 --source ../create_table.inc
59 if ($mysql_errname)
60 {
61 --let $my_last_stmt = $create_statement
62 --let $functionality = Partitions or indexes
63 --source ../unexpected_result.inc
64 }
65 if (!$mysql_errname)
66 {
67 --source ../mask_engine.inc
68 --replace_column 6 # 7 # 10 # 11 #
69 SHOW INDEX IN t1;
70 INSERT INTO t1 (a) VALUES (1),(2),(3),(5);
71 --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 #
72 EXPLAIN PARTITIONS SELECT a FROM t1;
73 --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 #
74 EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a IN (1,3);
75 DROP TABLE t1;
76 }
77 }
78
79 let $create_definition = a $int_indexed_col PRIMARY KEY;
80 let $partition_options = PARTITION BY KEY() PARTITIONS 2;
81 --source ../create_table.inc
82 if ($mysql_errname)
83 {
84 --let $my_last_stmt = $create_statement
85 --let $functionality = PK
86 --source ../unexpected_result.inc
87 }
88 if (!$mysql_errname)
89 {
90 --replace_column 6 # 7 # 10 # 11 #
91 SHOW INDEX IN t1;
92
93 INSERT INTO t1 (a) VALUES (1),(200),(3),(2);
94 --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 #
95 EXPLAIN PARTITIONS SELECT a FROM t1;
96 --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 #
97 EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a=2;
98 DROP TABLE t1;
99 }
100
101
102 let $create_definition = a $int_col;
103 let $partition_options = PARTITION BY RANGE(a) (
104 PARTITION p0 VALUES LESS THAN (10),
105 PARTITION p1 VALUES LESS THAN (1000)
106 );
107 --source ../create_table.inc
108 if ($mysql_errname)
109 {
110 --let $my_last_stmt = $create_statement
111 --let $functionality = Partitions
112 --source ../unexpected_result.inc
113 }
114 if (!$mysql_errname)
115 {
116 INSERT INTO t1 (a) VALUES (1),(2),(400);
117 --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 #
118 EXPLAIN PARTITIONS SELECT a FROM t1;
119 --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 #
120 EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a = 2;
121
122 --let $error_codes = ER_NO_PARTITION_FOR_GIVEN_VALUE
123 INSERT INTO t1 (a) VALUES (10000);
124 --source ../check_errors.inc
125 DROP TABLE t1;
126
127 let $create_definition = a $int_col;
128 let $partition_options = PARTITION BY LIST(a) (
129 PARTITION abc VALUES IN (1,2,3),
130 PARTITION def VALUES IN (100,101,102)
131 );
132 --source ../create_table.inc
133
134 INSERT INTO t1 (a) VALUES (1),(101),(1);
135 --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 #
136 EXPLAIN PARTITIONS SELECT a FROM t1;
137 --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 #
138 EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a = 100;
139 --let $error_codes = ER_NO_PARTITION_FOR_GIVEN_VALUE
140 INSERT INTO t1 (a) VALUES (50);
141 --source ../check_errors.inc
142 DROP TABLE t1;
143 }
144
145 let $create_definition = a $int_col, b $int_col;
146 let partition_options = PARTITION BY LIST(a) SUBPARTITION by HASH(b) (
147 PARTITION abc VALUES IN (1,2,3),
148 PARTITION def VALUES IN (100,101,102)
149 );
150 --source ../create_table.inc
151 if ($mysql_errname)
152 {
153 --let $my_last_stmt = $create_statement
154 --let $functionality = Partitions or subpartitions
155 --source ../unexpected_result.inc
156 }
157 if (!$mysql_errname)
158 {
159 --replace_column 6 # 7 # 10 # 11 #
160 SHOW INDEX IN t1;
161
162 INSERT INTO t1 (a,b) VALUES (1,1),(101,2),(1,3);
163 --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 #
164 EXPLAIN PARTITIONS SELECT a FROM t1;
165 --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 #
166 EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a = 100;
167
168 --sorted_result
169 SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, PARTITION_METHOD, SUBPARTITION_METHOD
170 FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 't1';
171 # This is just to hit some more handler functions while we are here
172 --disable_result_log
173 SELECT * FROM INFORMATION_SCHEMA.PARTITIONS;
174 --enable_result_log
175
176 DROP TABLE t1;
177 }
178 --source ../cleanup_engine.inc
179