oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4

5
#owner: yanmu.ztl
6
#owner group: rs
7
#description: test whether show create tablegroup stmt
8

9
#connect (conn_sys,$OBMYSQL_MS0,root@sys,,*NO-ONE*,$OBMYSQL_PORT);
10
#connection conn_sys;
11

12
# case 1: partition options
13
# case 1.1: 非分区tablegroup
14
let $index = 0;
15
--disable_warnings
16
eval drop tablegroup if exists show_create_tg_$index;
17
--enable_warnings
18
eval create tablegroup show_create_tg_$index;
19
eval show create tablegroup show_create_tg_$index;
20
--disable_warnings
21
eval drop tablegroup if exists show_create_tg_$index;
22
--enable_warnings
23

24
# case 1.2: 一级分区tablegroup
25

26
inc $index;
27
--disable_warnings
28
eval drop tablegroup if exists show_create_tg_$index;
29
--enable_warnings
30
eval create tablegroup show_create_tg_$index partition by hash partitions 2;
31
eval show create tablegroup show_create_tg_$index;
32
--disable_warnings
33
eval drop tablegroup if exists show_create_tg_$index;
34
--enable_warnings
35

36
inc $index;
37
--disable_warnings
38
eval drop tablegroup if exists show_create_tg_$index;
39
--enable_warnings
40
eval create tablegroup show_create_tg_$index partition by key 2 partitions 2;
41
eval show create tablegroup show_create_tg_$index;
42
--disable_warnings
43
eval drop tablegroup if exists show_create_tg_$index;
44
--enable_warnings
45

46
inc $index;
47
--disable_warnings
48
eval drop tablegroup if exists show_create_tg_$index;
49
--enable_warnings
50
eval create tablegroup show_create_tg_$index partition by range (partition p0 values less than (100), partition p1 values less than (200), partition p2 values less than (MAXVALUE));
51
eval show create tablegroup show_create_tg_$index;
52
--disable_warnings
53
eval drop tablegroup if exists show_create_tg_$index;
54
--enable_warnings
55

56
inc $index;
57
--disable_warnings
58
eval drop tablegroup if exists show_create_tg_$index;
59
--enable_warnings
60
eval create tablegroup show_create_tg_$index partition by range columns 2 (partition p0 values less than (1, '100'), partition p1 values less than (2, '200'), partition p2 values less than (MAXVALUE, MAXVALUE));
61
eval show create tablegroup show_create_tg_$index;
62
--disable_warnings
63
eval drop tablegroup if exists show_create_tg_$index;
64
--enable_warnings
65

66
inc $index;
67
--disable_warnings
68
eval drop tablegroup if exists show_create_tg_$index;
69
--enable_warnings
70
eval create tablegroup show_create_tg_$index partition by list (partition p0 values in (0, 1), partition p1 values in (2, 3), partition p3 values in (DEFAULT));
71
eval show create tablegroup show_create_tg_$index;
72
--disable_warnings
73
eval drop tablegroup if exists show_create_tg_$index;
74
--enable_warnings
75

76
inc $index;
77
--disable_warnings
78
eval drop tablegroup if exists show_create_tg_$index;
79
--enable_warnings
80
eval create tablegroup show_create_tg_$index partition by list columns 2 (partition p0 values in (('00', 0), ('01', 1)), partition p1 values in (('02', 2), ('03', 3)), partition p3 values in (DEFAULT));
81
eval show create tablegroup show_create_tg_$index;
82
--disable_warnings
83
eval drop tablegroup if exists show_create_tg_$index;
84
--enable_warnings
85

86
# case 1.2: 二级分区tablegroup
87

88
# 一级hash
89
inc $index;
90
--disable_warnings
91
eval drop tablegroup if exists show_create_tg_$index;
92
--enable_warnings
93
eval create tablegroup show_create_tg_$index partition by hash subpartition by range subpartition template (subpartition p0 values less than (100), subpartition p1 values less than (200), subpartition p2 values less than (MAXVALUE)) partitions 2;
94
eval show create tablegroup show_create_tg_$index;
95
--disable_warnings
96
eval drop tablegroup if exists show_create_tg_$index;
97
--enable_warnings
98

99
inc $index;
100
--disable_warnings
101
eval drop tablegroup if exists show_create_tg_$index;
102
--enable_warnings
103
eval create tablegroup show_create_tg_$index partition by hash subpartition by range columns 2 subpartition template (subpartition p0 values less than (1, '100'), subpartition p1 values less than (2, '200'), subpartition p2 values less than (MAXVALUE, MAXVALUE)) partitions 2;
104
eval show create tablegroup show_create_tg_$index;
105
--disable_warnings
106
eval drop tablegroup if exists show_create_tg_$index;
107
--enable_warnings
108

109
inc $index;
110
--disable_warnings
111
eval drop tablegroup if exists show_create_tg_$index;
112
--enable_warnings
113
eval create tablegroup show_create_tg_$index partition by hash subpartition by list subpartition template (subpartition p0 values in (0, 1), subpartition p1 values in (2, 3), subpartition p3 values in (DEFAULT)) partitions 2;
114
eval show create tablegroup show_create_tg_$index;
115
--disable_warnings
116
eval drop tablegroup if exists show_create_tg_$index;
117
--enable_warnings
118

119
inc $index;
120
--disable_warnings
121
eval drop tablegroup if exists show_create_tg_$index;
122
--enable_warnings
123
eval create tablegroup show_create_tg_$index partition by hash subpartition by list columns 2 subpartition template (subpartition p0 values in (('00', 0), ('01', 1)), subpartition p1 values in (('02', 2), ('03', 3)), subpartition p3 values in (DEFAULT)) partitions 2;
124
eval show create tablegroup show_create_tg_$index;
125
--disable_warnings
126
eval drop tablegroup if exists show_create_tg_$index;
127
--enable_warnings
128

129
# 一级key
130
inc $index;
131
--disable_warnings
132
eval drop tablegroup if exists show_create_tg_$index;
133
--enable_warnings
134
eval create tablegroup show_create_tg_$index partition by key 2 subpartition by range subpartition template (subpartition p0 values less than (100), subpartition p1 values less than (200), subpartition p2 values less than (MAXVALUE)) partitions 2;
135
eval show create tablegroup show_create_tg_$index;
136
--disable_warnings
137
eval drop tablegroup if exists show_create_tg_$index;
138
--enable_warnings
139

140
inc $index;
141
--disable_warnings
142
eval drop tablegroup if exists show_create_tg_$index;
143
--enable_warnings
144
eval create tablegroup show_create_tg_$index partition by key 2 subpartition by range columns 2 subpartition template (subpartition p0 values less than (1, '100'), subpartition p1 values less than (2, '200'), subpartition p2 values less than (MAXVALUE, MAXVALUE)) partitions 2;
145
eval show create tablegroup show_create_tg_$index;
146
--disable_warnings
147
eval drop tablegroup if exists show_create_tg_$index;
148
--enable_warnings
149

150
inc $index;
151
--disable_warnings
152
eval drop tablegroup if exists show_create_tg_$index;
153
--enable_warnings
154
eval create tablegroup show_create_tg_$index partition by key 2 subpartition by list subpartition template (subpartition p0 values in (0, 1), subpartition p1 values in (2, 3), subpartition p3 values in (DEFAULT)) partitions 2;
155
eval show create tablegroup show_create_tg_$index;
156
--disable_warnings
157
eval drop tablegroup if exists show_create_tg_$index;
158
--enable_warnings
159

160
inc $index;
161
--disable_warnings
162
eval drop tablegroup if exists show_create_tg_$index;
163
--enable_warnings
164
eval create tablegroup show_create_tg_$index partition by key 2 subpartition by list columns 2 subpartition template (subpartition p0 values in (('00', 0), ('01', 1)), subpartition p1 values in (('02', 2), ('03', 3)), subpartition p3 values in (DEFAULT)) partitions 2;
165
eval show create tablegroup show_create_tg_$index;
166
--disable_warnings
167
eval drop tablegroup if exists show_create_tg_$index;
168
--enable_warnings
169

170
# 一级range
171
inc $index;
172
--disable_warnings
173
eval drop tablegroup if exists show_create_tg_$index;
174
--enable_warnings
175
eval create tablegroup show_create_tg_$index partition by range subpartition by hash subpartitions 2 (partition p0 values less than (100), partition p1 values less than (200), partition p2 values less than (MAXVALUE));
176
eval show create tablegroup show_create_tg_$index;
177
--disable_warnings
178
eval drop tablegroup if exists show_create_tg_$index;
179
--enable_warnings
180

181
inc $index;
182
--disable_warnings
183
eval drop tablegroup if exists show_create_tg_$index;
184
--enable_warnings
185
eval create tablegroup show_create_tg_$index partition by range subpartition by key 2 subpartitions 2 (partition p0 values less than (100), partition p1 values less than (200), partition p2 values less than (MAXVALUE));
186
eval show create tablegroup show_create_tg_$index;
187
--disable_warnings
188
eval drop tablegroup if exists show_create_tg_$index;
189
--enable_warnings
190

191
inc $index;
192
--disable_warnings
193
eval drop tablegroup if exists show_create_tg_$index;
194
--enable_warnings
195
eval create tablegroup show_create_tg_$index partition by range subpartition by list subpartition template (subpartition p0 values in (0, 1), subpartition p1 values in (2, 3), subpartition p3 values in (DEFAULT)) (partition p0 values less than (100), partition p1 values less than (200), partition p2 values less than (MAXVALUE));
196
eval show create tablegroup show_create_tg_$index;
197
--disable_warnings
198
eval drop tablegroup if exists show_create_tg_$index;
199
--enable_warnings
200

201
inc $index;
202
--disable_warnings
203
eval drop tablegroup if exists show_create_tg_$index;
204
--enable_warnings
205
eval create tablegroup show_create_tg_$index partition by range subpartition by list columns 2 subpartition template (subpartition p0 values in (('00', 0), ('01', 1)), subpartition p1 values in (('02', 2), ('03', 3)), subpartition p3 values in (DEFAULT)) (partition p0 values less than (100), partition p1 values less than (200), partition p2 values less than (MAXVALUE));
206
eval show create tablegroup show_create_tg_$index;
207
--disable_warnings
208
eval drop tablegroup if exists show_create_tg_$index;
209
--enable_warnings
210

211
# 一级range columns
212
inc $index;
213
--disable_warnings
214
eval drop tablegroup if exists show_create_tg_$index;
215
--enable_warnings
216
eval create tablegroup show_create_tg_$index partition by range columns 2 subpartition by hash subpartitions 2 (partition p0 values less than (1, '100'), partition p1 values less than (2, '200'), partition p2 values less than (MAXVALUE, MAXVALUE));
217
eval show create tablegroup show_create_tg_$index;
218
--disable_warnings
219
eval drop tablegroup if exists show_create_tg_$index;
220
--enable_warnings
221

222
inc $index;
223
--disable_warnings
224
eval drop tablegroup if exists show_create_tg_$index;
225
--enable_warnings
226
eval create tablegroup show_create_tg_$index partition by range columns 2 subpartition by key 2 subpartitions 2 (partition p0 values less than (1, '100'), partition p1 values less than (2, '200'), partition p2 values less than (MAXVALUE, MAXVALUE));
227
eval show create tablegroup show_create_tg_$index;
228
--disable_warnings
229
eval drop tablegroup if exists show_create_tg_$index;
230
--enable_warnings
231

232
inc $index;
233
--disable_warnings
234
eval drop tablegroup if exists show_create_tg_$index;
235
--enable_warnings
236
eval create tablegroup show_create_tg_$index partition by range columns 2 subpartition by list subpartition template (subpartition p0 values in (0, 1), subpartition p1 values in (2, 3), subpartition p3 values in (DEFAULT)) (partition p0 values less than (1, '100'), partition p1 values less than (2, '200'), partition p2 values less than (MAXVALUE, MAXVALUE));
237
eval show create tablegroup show_create_tg_$index;
238
--disable_warnings
239
eval drop tablegroup if exists show_create_tg_$index;
240
--enable_warnings
241

242
inc $index;
243
--disable_warnings
244
eval drop tablegroup if exists show_create_tg_$index;
245
--enable_warnings
246
eval create tablegroup show_create_tg_$index partition by range columns 2 subpartition by list columns 2 subpartition template (subpartition p0 values in (('00', 0), ('01', 1)), subpartition p1 values in (('02', 2), ('03', 3)), subpartition p3 values in (DEFAULT)) (partition p0 values less than (1, '100'), partition p1 values less than (2, '200'), partition p2 values less than (MAXVALUE, MAXVALUE));
247
eval show create tablegroup show_create_tg_$index;
248
--disable_warnings
249
eval drop tablegroup if exists show_create_tg_$index;
250
--enable_warnings
251

252
# 一级list
253
inc $index;
254
--disable_warnings
255
eval drop tablegroup if exists show_create_tg_$index;
256
--enable_warnings
257
eval create tablegroup show_create_tg_$index partition by list subpartition by hash subpartitions 2 (partition p0 values in (0, 1), partition p1 values in (2, 3), partition p3 values in (DEFAULT));
258
eval show create tablegroup show_create_tg_$index;
259
--disable_warnings
260
eval drop tablegroup if exists show_create_tg_$index;
261
--enable_warnings
262

263
inc $index;
264
--disable_warnings
265
eval drop tablegroup if exists show_create_tg_$index;
266
--enable_warnings
267
eval create tablegroup show_create_tg_$index partition by list subpartition by key 2 subpartitions 2 (partition p0 values in (0, 1), partition p1 values in (2, 3), partition p3 values in (DEFAULT));
268
eval show create tablegroup show_create_tg_$index;
269
--disable_warnings
270
eval drop tablegroup if exists show_create_tg_$index;
271
--enable_warnings
272

273
inc $index;
274
--disable_warnings
275
eval drop tablegroup if exists show_create_tg_$index;
276
--enable_warnings
277
eval create tablegroup show_create_tg_$index partition by list subpartition by range subpartition template (subpartition p0 values less than (100), subpartition p1 values less than (200), subpartition p2 values less than (MAXVALUE)) (partition p0 values in (0, 1), partition p1 values in (2, 3), partition p3 values in (DEFAULT));
278
eval show create tablegroup show_create_tg_$index;
279
--disable_warnings
280
eval drop tablegroup if exists show_create_tg_$index;
281
--enable_warnings
282

283
inc $index;
284
--disable_warnings
285
eval drop tablegroup if exists show_create_tg_$index;
286
--enable_warnings
287
eval create tablegroup show_create_tg_$index partition by list subpartition by range columns 2 subpartition template (subpartition p0 values less than (1, '100'), subpartition p1 values less than (2, '200'), subpartition p2 values less than (MAXVALUE, MAXVALUE)) (partition p0 values in (0, 1), partition p1 values in (2, 3), partition p3 values in (DEFAULT));
288
--disable_warnings
289
eval drop tablegroup if exists show_create_tg_$index;
290
--enable_warnings
291

292
# 一级list columns
293
inc $index;
294
--disable_warnings
295
eval drop tablegroup if exists show_create_tg_$index;
296
--enable_warnings
297
eval create tablegroup show_create_tg_$index partition by list columns 2 subpartition by hash subpartitions 2 (partition p0 values in (('00', 0), ('01', 1)), partition p1 values in (('02', 2), ('03', 3)), partition p3 values in (DEFAULT));
298
eval show create tablegroup show_create_tg_$index;
299
--disable_warnings
300
eval drop tablegroup if exists show_create_tg_$index;
301
--enable_warnings
302

303
inc $index;
304
--disable_warnings
305
eval drop tablegroup if exists show_create_tg_$index;
306
--enable_warnings
307
eval create tablegroup show_create_tg_$index partition by list columns 2 subpartition by key 2 subpartitions 2 (partition p0 values in (('00', 0), ('01', 1)), partition p1 values in (('02', 2), ('03', 3)), partition p3 values in (DEFAULT));
308
eval show create tablegroup show_create_tg_$index;
309
--disable_warnings
310
eval drop tablegroup if exists show_create_tg_$index;
311
--enable_warnings
312

313
inc $index;
314
--disable_warnings
315
eval drop tablegroup if exists show_create_tg_$index;
316
--enable_warnings
317
eval create tablegroup show_create_tg_$index partition by list columns 2 subpartition by range subpartition template (subpartition p0 values less than (100), subpartition p1 values less than (200), subpartition p2 values less than (MAXVALUE)) (partition p0 values in (('00', 0), ('01', 1)), partition p1 values in (('02', 2), ('03', 3)), partition p3 values in (DEFAULT));
318
eval show create tablegroup show_create_tg_$index;
319
--disable_warnings
320
eval drop tablegroup if exists show_create_tg_$index;
321
--enable_warnings
322

323
inc $index;
324
--disable_warnings
325
eval drop tablegroup if exists show_create_tg_$index;
326
--enable_warnings
327
eval create tablegroup show_create_tg_$index partition by list columns 2 subpartition by range columns 2 subpartition template (subpartition p0 values less than (1, '100'), subpartition p1 values less than (2, '200'), subpartition p2 values less than (MAXVALUE, MAXVALUE)) (partition p0 values in (('00', 0), ('01', 1)), partition p1 values in (('02', 2), ('03', 3)), partition p3 values in (DEFAULT));
328
eval show create tablegroup show_create_tg_$index;
329
--disable_warnings
330
eval drop tablegroup if exists show_create_tg_$index;
331
--enable_warnings
332