Debian Codegraph @

mysql-test/suite/x/r/admin_create_index_array.result

raw
1 CREATE SCHEMA xtest DEFAULT CHARSET 'utf8mb4';
2
3 Create collection 'data'
4 ========================
5
6 command ok
7
8 Insert documents into collection 'data'
9 =======================================
10
11 1 rows affected
12 Got expected error (code 0)
13
14 1 rows affected
15 Got expected error (code 0)
16
17 1 rows affected
18 Got expected error (code 0)
19
20 1 rows affected
21 Got expected error (code 0)
22 ## 1. Create and drop array index ($.path)
23
24 Create array index 'integer_idx' on field 'integer'
25 ===================================================
26
27 command ok
28 Got expected error (code 0)
29 Verify ["SHOW index FROM xtest.data WHERE Key_name = 'integer_idx'" returns in "Key_name" column, following value integer_idx]
30
31 Drop array index 'integer_idx'
32 ==============================
33
34 command ok
35
36 Create array index 'text_idx' on field 'text'
37 =============================================
38
39 command ok
40 Got expected error (code 0)
41 Verify ["SHOW index FROM xtest.data WHERE Key_name = 'text_idx'" returns in "Key_name" column, following value text_idx]
42
43 Drop array index 'text_idx'
44 ===========================
45
46 command ok
47
48 Create array index 'text_default_type_idx' on field 'text'
49 ==========================================================
50
51 command ok
52 Got expected error (code 0)
53 Verify ["SHOW index FROM xtest.data WHERE Key_name = 'text_default_type_idx'" returns in "Key_name" column, following value text_default_type_idx]
54
55 Drop array index 'text_default_type_idx'
56 ========================================
57
58 command ok
59
60 Create array index 'time_idx' on field 'time'
61 =============================================
62
63 command ok
64 Got expected error (code 0)
65 Verify ["SHOW index FROM xtest.data WHERE Key_name = 'time_idx'" returns in "Key_name" column, following value time_idx]
66
67 Drop array index 'time_idx'
68 ===========================
69
70 command ok
71
72 Create array index 'date_idx' on field 'date'
73 =============================================
74
75 command ok
76 Got expected error (code 0)
77 Verify ["SHOW index FROM xtest.data WHERE Key_name = 'date_idx'" returns in "Key_name" column, following value date_idx]
78
79 Drop array index 'date_idx'
80 ===========================
81
82 command ok
83
84 Create array index 'datetime_idx' on field 'datetime'
85 =====================================================
86
87 command ok
88 Got expected error (code 0)
89 Verify ["SHOW index FROM xtest.data WHERE Key_name = 'datetime_idx'" returns in "Key_name" column, following value datetime_idx]
90
91 Drop array index 'datetime_idx'
92 ===============================
93
94 command ok
95
96 Create array index 'float_idx' on field 'float'
97 ===============================================
98
99 command ok
100 Got expected error (code 0)
101 Verify ["SHOW index FROM xtest.data WHERE Key_name = 'float_idx'" returns in "Key_name" column, following value float_idx]
102
103 Drop array index 'float_idx'
104 ============================
105
106 command ok
107 ## 1. Create and drop array index ($.path[*])
108
109 Create array index 'integer_arr_idx' on field 'integer[*]'
110 ==========================================================
111
112 command ok
113 Got expected error (code 0)
114 Verify ["SHOW index FROM xtest.data WHERE Key_name = 'integer_arr_idx'" returns in "Key_name" column, following value integer_arr_idx]
115
116 Drop array index 'integer_arr_idx'
117 ==================================
118
119 command ok
120
121 Create array index 'text_arr_idx' on field 'text[*]'
122 ====================================================
123
124 command ok
125 Got expected error (code 0)
126 Verify ["SHOW index FROM xtest.data WHERE Key_name = 'text_arr_idx'" returns in "Key_name" column, following value text_arr_idx]
127
128 Drop array index 'text_arr_idx'
129 ===============================
130
131 command ok
132
133 Create array index 'text_arr_default_type_idx' on field 'text[*]'
134 =================================================================
135
136 command ok
137 Got expected error (code 0)
138 Verify ["SHOW index FROM xtest.data WHERE Key_name = 'text_arr_default_type_idx'" returns in "Key_name" column, following value text_arr_default_type_idx]
139
140 Drop array index 'text_arr_default_type_idx'
141 ============================================
142
143 command ok
144
145 Create array index 'time_arr_idx' on field 'time[*]'
146 ====================================================
147
148 command ok
149 Got expected error (code 0)
150 Verify ["SHOW index FROM xtest.data WHERE Key_name = 'time_arr_idx'" returns in "Key_name" column, following value time_arr_idx]
151
152 Drop array index 'time_arr_idx'
153 ===============================
154
155 command ok
156
157 Create array index 'date_arr_idx' on field 'date[*]'
158 ====================================================
159
160 command ok
161 Got expected error (code 0)
162 Verify ["SHOW index FROM xtest.data WHERE Key_name = 'date_arr_idx'" returns in "Key_name" column, following value date_arr_idx]
163
164 Drop array index 'date_arr_idx'
165 ===============================
166
167 command ok
168
169 Create array index 'datetime_arr_idx' on field 'datetime[*]'
170 ============================================================
171
172 command ok
173 Got expected error (code 0)
174 Verify ["SHOW index FROM xtest.data WHERE Key_name = 'datetime_arr_idx'" returns in "Key_name" column, following value datetime_arr_idx]
175
176 Drop array index 'datetime_arr_idx'
177 ===================================
178
179 command ok
180
181 Create array index 'float_arr_idx' on field 'float[*]'
182 ======================================================
183
184 command ok
185 Got expected error (code 0)
186 Verify ["SHOW index FROM xtest.data WHERE Key_name = 'float_arr_idx'" returns in "Key_name" column, following value float_arr_idx]
187
188 Drop array index 'float_arr_idx'
189 ================================
190
191 command ok
192
193 Create array index 'object_idx' on field 'object[*].number'
194 ===========================================================
195
196 command ok
197 Got expected error (code 0)
198 Verify ["SHOW index FROM xtest.data WHERE Key_name = 'object_idx'" returns in "Key_name" column, following value object_idx]
199
200 Drop array index 'object_idx'
201 =============================
202
203 command ok
204 ## 2. Try to create 'multi-field' array index (array-array)
205 Got expected error: This version of MySQL doesn't yet support 'more than one multi-valued key part per index' (code 1235)
206 ## 2. Create and drop 'multi-field' array index (array-scalar)
207
208 command ok
209 Verify ["SHOW index FROM xtest.data WHERE Key_name = 'scalar_text_idx'" returns in "Key_name" column, following value scalar_text_idx]
210
211 Drop array index 'scalar_text_idx'
212 ==================================
213
214 command ok
215 ## 3. Create and drop 'unique' array index
216
217 command ok
218 Verify ["SHOW index FROM xtest.data WHERE Key_name = 'unique_idx'" returns in "Key_name" column, following value unique_idx]
219
220 Drop array index 'unique_idx'
221 =============================
222
223 command ok
224 Got expected error: Duplicate entry '[3.' for key 'data.fail_idx' (code 1062)
225 ## 3. Create 'required' array index
226 Got expected error: Unsupported argument specification for '$.integer' (code 5017)
227 ## 4. Verify usage array index
228 ## 4.1 with JSON_CONTAINS
229
230 command ok
231 Verify ["EXPLAIN SELECT * FROM xtest.data USE INDEX(`integer_idx`) WHERE json_contains(doc->'$.integer', cast('[11]' as json))" returns in "possible_keys" column, following value integer_idx]
232 ## 4. Verify usage array index
233 ## 4.2 with JSON_OVERLAPS
234 Verify ["EXPLAIN SELECT * FROM xtest.data USE INDEX(`integer_idx`) WHERE json_overlaps(doc->'$.integer', cast('[11]' as json))" returns in "possible_keys" column, following value integer_idx]
235 ## 5. null value is not supported in indexed arrays
236
237 Create collection 'data1'
238 =========================
239
240 command ok
241
242 1 rows affected
243 Got expected error (code 0)
244 Got expected error: Invalid JSON value for CAST for functional index 'integer_idx'. (code 3903)
245 Got expected error: Invalid JSON value for CAST for functional index 'text_idx'. (code 3903)
246 Got expected error: Invalid JSON value for CAST for functional index 'text_default_type_idx'. (code 3903)
247 Got expected error: Invalid JSON value for CAST for functional index 'time_idx'. (code 3903)
248 Got expected error: Invalid JSON value for CAST for functional index 'date_idx'. (code 3903)
249 Got expected error: Invalid JSON value for CAST for functional index 'datetime_idx'. (code 3903)
250 Got expected error: Invalid JSON value for CAST for functional index 'float_idx'. (code 3903)
251
252 Create collection 'data2'
253 =========================
254
255 command ok
256
257 1 rows affected
258 Got expected error (code 0)
259 Got expected error: Invalid JSON value for CAST for functional index 'integer_idx'. (code 3903)
260 Got expected error: Invalid JSON value for CAST for functional index 'text_idx'. (code 3903)
261 Got expected error: Invalid JSON value for CAST for functional index 'text_default_type_idx'. (code 3903)
262 Got expected error: Invalid JSON value for CAST for functional index 'time_idx'. (code 3903)
263 Got expected error: Invalid JSON value for CAST for functional index 'date_idx'. (code 3903)
264 Got expected error: Invalid JSON value for CAST for functional index 'datetime_idx'. (code 3903)
265 Got expected error: Invalid JSON value for CAST for functional index 'float_idx'. (code 3903)
266 ## 6. Create different types of index on an empty array
267
268 Create collection 'data3'
269 =========================
270
271 command ok
272
273 1 rows affected
274 Got expected error (code 0)
275
276 Create array index 'empty_signed_idx' on field 'empty'
277 ======================================================
278
279 command ok
280 Got expected error (code 0)
281 Verify ["SHOW index FROM xtest.data3 WHERE Key_name = 'empty_signed_idx'" returns in "Key_name" column, following value empty_signed_idx]
282
283 Drop array index 'empty_signed_idx'
284 ===================================
285
286 command ok
287
288 Create array index 'empty_unsigned_idx' on field 'empty'
289 ========================================================
290
291 command ok
292 Got expected error (code 0)
293 Verify ["SHOW index FROM xtest.data3 WHERE Key_name = 'empty_unsigned_idx'" returns in "Key_name" column, following value empty_unsigned_idx]
294
295 Drop array index 'empty_unsigned_idx'
296 =====================================
297
298 command ok
299
300 Create array index 'empty_char_idx' on field 'empty'
301 ====================================================
302
303 command ok
304 Got expected error (code 0)
305 Verify ["SHOW index FROM xtest.data3 WHERE Key_name = 'empty_char_idx'" returns in "Key_name" column, following value empty_char_idx]
306
307 Drop array index 'empty_char_idx'
308 =================================
309
310 command ok
311
312 Create array index 'empty_date_idx' on field 'empty'
313 ====================================================
314
315 command ok
316 Got expected error (code 0)
317 Verify ["SHOW index FROM xtest.data3 WHERE Key_name = 'empty_date_idx'" returns in "Key_name" column, following value empty_date_idx]
318
319 Drop array index 'empty_date_idx'
320 =================================
321
322 command ok
323
324 Create array index 'empty_time_idx' on field 'empty'
325 ====================================================
326
327 command ok
328 Got expected error (code 0)
329 Verify ["SHOW index FROM xtest.data3 WHERE Key_name = 'empty_time_idx'" returns in "Key_name" column, following value empty_time_idx]
330
331 Drop array index 'empty_time_idx'
332 =================================
333
334 command ok
335
336 Create array index 'empty_datetime_idx' on field 'empty'
337 ========================================================
338
339 command ok
340 Got expected error (code 0)
341 Verify ["SHOW index FROM xtest.data3 WHERE Key_name = 'empty_datetime_idx'" returns in "Key_name" column, following value empty_datetime_idx]
342
343 Drop array index 'empty_datetime_idx'
344 =====================================
345
346 command ok
347
348 Create array index 'empty_decimal_idx' on field 'empty'
349 =======================================================
350
351 command ok
352 Got expected error (code 0)
353 Verify ["SHOW index FROM xtest.data3 WHERE Key_name = 'empty_decimal_idx'" returns in "Key_name" column, following value empty_decimal_idx]
354
355 Drop array index 'empty_decimal_idx'
356 ====================================
357
358 command ok
359
360 Create array index 'empty_binary_idx' on field 'empty'
361 ======================================================
362
363 command ok
364 Got expected error (code 0)
365 Verify ["SHOW index FROM xtest.data3 WHERE Key_name = 'empty_binary_idx'" returns in "Key_name" column, following value empty_binary_idx]
366
367 Drop array index 'empty_binary_idx'
368 ===================================
369
370 command ok
371 ## 7. Create index on a field having mixed data types
372
373 Create collection 'data4'
374 =========================
375
376 command ok
377
378 1 rows affected
379 Got expected error (code 0)
380 Got expected error: Invalid JSON value for CAST for functional index 'mixed_signed_idx'. (code 3903)
381 Got expected error: Value is out of range for functional index 'mixed_unsigned_idx' at row 1 (code 3752)
382
383 command ok
384 Got expected error (code 0)
385 Got expected error: Data truncated for functional index 'mixed_date_idx' at row 1 (code 3751)
386 Got expected error: Data truncated for functional index 'mixed_time_idx' at row 1 (code 3751)
387 Got expected error: Data truncated for functional index 'mixed_datetime_idx' at row 1 (code 3751)
388 Got expected error: Invalid JSON value for CAST for functional index 'mixed_decimal_idx'. (code 3903)
389
390 command ok
391 Got expected error (code 0)
392 ## 8. Create index on a field and insert document with that field missing
393
394 Create collection 'data5'
395 =========================
396
397 command ok
398
399 1 rows affected
400 Got expected error (code 0)
401
402 command ok
403 Got expected error (code 0)
404 Verify ["SHOW index FROM xtest.data5 WHERE Key_name = 'emp_id_idx'" returns in "Key_name" column, following value emp_id_idx]
405
406 1 rows affected
407 Got expected error (code 0)
408 ## 9. Try to create index with type as JSON/TEXT[(N)] [charset_info]/CHAR[(N)] [charset_info]
409
410 Create collection 'data6'
411 =========================
412
413 command ok
414 Got expected error: Invalid or unsupported type specification for array index 'JSON' (code 5017)
415 Got expected error: Invalid or unsupported type specification for array index 'text' (code 5017)
416 Got expected error: Invalid or unsupported type specification for array index 'text(64)' (code 5017)
417 Got expected error: Invalid or unsupported type specification for array index 'text(64) charset latin1 collate latin1_bin' (code 5017)
418 Got expected error: Invalid or unsupported type specification for array index 'text(64) charset latin1' (code 5017)
419 Got expected error: Invalid or unsupported type specification for array index 'text(64) collate latin1_bin' (code 5017)
420 Got expected error: Invalid or unsupported type specification for array index 'char(64) charset latin1' (code 5017)
421 Got expected error: Invalid or unsupported type specification for array index 'char(64) collate latin1_bin' (code 5017)
422 Got expected error: Invalid or unsupported type specification for array index 'char(64) charset latin1 collate latin1_bin' (code 5017)
423 ## 10. Increasing admin commands counters
424
425 Create collection 'data8'
426 =========================
427
428 command ok
429 Verify [Status variable "Mysqlx_stmt_create_collection_index" needs to have a value of 61]
430
431 command ok
432 Got expected error (code 0)
433 Verify [Status variable "Mysqlx_stmt_create_collection_index" needs to have a value of 62]
434 Verify [Status variable "Mysqlx_stmt_drop_collection_index" needs to have a value of 25]
435
436 command ok
437 Verify [Status variable "Mysqlx_stmt_drop_collection_index" needs to have a value of 26]
438 Mysqlx.Ok {
439 msg: "bye!"
440 }
441 ok