Debian Codegraph @

mysql-test/suite/x/t/admin_create_index_array.test

raw
1 ## manipulate of indexes on aray fields in document
2
3 ## 1. Create and drop array index
4 ## 2. Create and drop 'multi-field' array index
5 ## 3. Create and drop 'unique' array index
6 ## 3. Create 'required' array index
7 ## 4. Verify usage array index
8 ## 4.1 with JSON_CONTAINS
9 ## 4.2 with JSON_OVERLAPS
10 ## 5. null value is not supported in indexed arrays
11
12 --source include/xplugin_preamble.inc
13 --source include/xplugin_create_user.inc
14
15 ## Test starts here
16 --let $xtest_file= $MYSQL_TMP_DIR/admin_create_index_array.xpl
17 --write_file $xtest_file
18 -->import assert_variable.macro
19 -->import assert_status_variable.macro
20 -->import assert_select.macro
21 -->quiet
22
23 #MACROS
24
25 -->macro Assert_use_of_index %COLL% %NAME% %COND%
26 callmacro Assert_select
27 possible_keys
28 %NAME%
29 EXPLAIN SELECT * FROM xtest.%COLL% USE INDEX(`%NAME%`) WHERE %COND%;
30 -->endmacro
31
32 -->macro Insert %COLL% %VALUE% %ERROR%
33 Mysqlx.Crud.Insert {
34 collection {
35 name: "%COLL%"
36 schema: "xtest"
37 }
38 data_model: DOCUMENT
39 row {
40 field {
41 type: LITERAL
42 literal {
43 type: V_STRING
44 v_string {
45 value: %VALUE%
46 }
47 }
48 }
49 }
50 }
51 -->expecterror %ERROR%
52 -->recvresult
53 -->endmacro
54
55
56 -->macro Assert_index_exist %COLL% %NAME%
57 callmacro Assert_select
58 Key_name
59 %NAME%
60 SHOW index FROM xtest.%COLL% WHERE Key_name = '%NAME%';
61 -->endmacro
62
63
64 -->macro Create_array_index %COLL% %NAME% %PATH% %TYPE% %ERROR%
65 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"%COLL%", "name":"%NAME%", "unique":false, "fields":{"field":"$.%PATH%", %TYPE% "array":true}}
66 -->expecterror %ERROR%
67 -->recvresult
68 -->endmacro
69
70
71 -->macro Test_array_index %COLL% %NAME% %PATH% %TYPE%
72 -->title =Create array index '%NAME%' on field '%PATH%'
73 -->callmacro Create_array_index %COLL% %NAME% %PATH% %TYPE% ER_SUCCESS
74 -->callmacro Assert_index_exist %COLL% %NAME%
75 -->title =Drop array index '%NAME%'
76 -->stmtadmin drop_collection_index {"schema":"xtest", "collection":"%COLL%", "name":"%NAME%"}
77 -->recvresult
78 -->endmacro
79
80 -->macro Create_collection %COLL%
81 -->title =Create collection '%COLL%'
82 -->stmtadmin create_collection {"schema":"xtest", "name":"%COLL%"}
83 -->recvresult
84 -->endmacro
85
86 #TESTS
87
88 -->callmacro Create_collection data
89
90 -->title =Insert documents into collection 'data'
91 -->callmacro Insert data '{"_id":"001"}' ER_SUCCESS
92 -->callmacro Insert data '{"_id":"002", "scalar":1, "integer":10, "text":"Alice", "time":"10:36:00.000000", "date":"2015-05-27", "datetime":"1976-02-25T01:12:34", "float":3.14, "object":{"number":1}}' ER_SUCCESS
93 -->callmacro Insert data '{"_id":"003", "scalar":2, "integer":[11], "text":["Bob"], "time":["11:16:00.000000"], "date":["2016-04-17"], "datetime":["1978-01-25T02:02:44"], "float":[4.15], "object":[{"number":2}]}' ER_SUCCESS
94 -->callmacro Insert data '{"_id":"004", "scalar":3, "integer":[12, 13], "text":["Alice", "Bob"], "time":["10:36:00.000000", "11:16:00.000000"], "date":["2015-05-27", "2016-04-17"], "datetime":["1976-02-25T01:12:34", "1978-01-25T02:02:44"], "float":[3.14, 4.15], "object":[{"number":3}, {"number":4}]}' ER_SUCCESS
95
96 -->echo ## 1. Create and drop array index ($.path)
97 -->callmacro Test_array_index data integer_idx integer "type":"signed",
98 -->callmacro Test_array_index data text_idx text "type":"char(64)",
99 -->callmacro Test_array_index data text_default_type_idx text
100 -->callmacro Test_array_index data time_idx time "type":"time",
101 -->callmacro Test_array_index data date_idx date "type":"date",
102 -->callmacro Test_array_index data datetime_idx datetime "type":"datetime",
103 -->callmacro Test_array_index data float_idx float "type":"decimal(10,3)",
104
105 -->echo ## 1. Create and drop array index ($.path[*])
106 -->callmacro Test_array_index data integer_arr_idx integer[*] "type":"signed",
107 -->callmacro Test_array_index data text_arr_idx text[*] "type":"char(64)",
108 -->callmacro Test_array_index data text_arr_default_type_idx text[*]
109 -->callmacro Test_array_index data time_arr_idx time[*] "type":"time",
110 -->callmacro Test_array_index data date_arr_idx date[*] "type":"date",
111 -->callmacro Test_array_index data datetime_arr_idx datetime[*] "type":"datetime",
112 -->callmacro Test_array_index data float_arr_idx float[*] "type":"decimal(10,3)",
113 -->callmacro Test_array_index data object_idx object[*].number "type":"unsigned",
114
115
116 -->echo ## 2. Try to create 'multi-field' array index (array-array)
117 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"data", "name":"integer_text_idx", "unique":false, "fields":[{"field":"$.integer", "type":"SIGNED", "array":true}, {"field":"$.text", "type":"CHAR(10)", "array":true}]}
118 -->expecterror ER_NOT_SUPPORTED_YET
119 -->recvresult
120
121
122 -->echo ## 2. Create and drop 'multi-field' array index (array-scalar)
123 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"data", "name":"scalar_text_idx", "unique":false, "fields":[{"field":"$.scalar", "type":"INT"}, {"field":"$.text", "type":"CHAR(64)", "array":true}]}
124 -->recvresult
125 -->callmacro Assert_index_exist data scalar_text_idx
126 -->title =Drop array index 'scalar_text_idx'
127 -->stmtadmin drop_collection_index {"schema":"xtest", "collection":"data", "name":"scalar_text_idx"}
128 -->recvresult
129
130
131 -->echo ## 3. Create and drop 'unique' array index
132 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"data", "name":"unique_idx", "unique":true, "fields":{"field":"$.integer", "type":"SIGNED", "array":true}}
133 -->recvresult
134 -->callmacro Assert_index_exist data unique_idx
135 -->title =Drop array index 'unique_idx'
136 -->stmtadmin drop_collection_index {"schema":"xtest", "collection":"data", "name":"unique_idx"}
137 -->recvresult
138
139 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"data", "name":"fail_idx", "unique":true, "fields":{"field":"$.float", "type":"DECIMAL(6,2)", "array":true}}
140 -->expecterror ER_DUP_ENTRY
141 -->recvresult
142
143
144 -->echo ## 3. Create 'required' array index
145 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"data", "name":"fail_idx", "unique":false, "fields":{"field":"$.integer", "type":"SIGNED", "array":true, "required":true}}
146 -->expecterror ER_X_CMD_ARGUMENT_VALUE
147 -->recvresult
148
149
150 -->echo ## 4. Verify usage array index
151 -->echo ## 4.1 with JSON_CONTAINS
152 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"data", "name":"integer_idx", "unique":false, "fields":{"field":"$.integer", "type":"SIGNED", "array":true}}
153 -->recvresult
154 -->callmacro Assert_use_of_index data integer_idx json_contains(doc->'$.integer', cast('[11]' as json))
155
156
157 -->echo ## 4. Verify usage array index
158 -->echo ## 4.2 with JSON_OVERLAPS
159 -->callmacro Assert_use_of_index data integer_idx json_overlaps(doc->'$.integer', cast('[11]' as json))
160
161
162 -->echo ## 5. null value is not supported in indexed arrays
163 -->callmacro Create_collection data1
164 -->callmacro Insert data1 '{"_id":"005", "integer":null, "text":null, "time":null, "date":null, "datetime":null, "float":null, "object":null}' ER_SUCCESS
165 -->callmacro Create_array_index data1 integer_idx integer "type":"signed", ER_INVALID_JSON_VALUE_FOR_FUNC_INDEX
166 -->callmacro Create_array_index data1 text_idx text "type":"char(64)", ER_INVALID_JSON_VALUE_FOR_FUNC_INDEX
167 -->callmacro Create_array_index data1 text_default_type_idx text ER_INVALID_JSON_VALUE_FOR_FUNC_INDEX
168 -->callmacro Create_array_index data1 time_idx time "type":"time", ER_INVALID_JSON_VALUE_FOR_FUNC_INDEX
169 -->callmacro Create_array_index data1 date_idx date "type":"date", ER_INVALID_JSON_VALUE_FOR_FUNC_INDEX
170 -->callmacro Create_array_index data1 datetime_idx datetime "type":"datetime", ER_INVALID_JSON_VALUE_FOR_FUNC_INDEX
171 -->callmacro Create_array_index data1 float_idx float "type":"decimal(10,3)", ER_INVALID_JSON_VALUE_FOR_FUNC_INDEX
172
173 -->callmacro Create_collection data2
174 -->callmacro Insert data2 '{"_id":"006", "integer":[1, null], "text":["Alice", null], "time":["10:36:00.000000", null], "date":["2015-05-27", null], "datetime":["1976-02-25T01:12:34", null], "float":[3.14, null]}' ER_SUCCESS
175 -->callmacro Create_array_index data2 integer_idx integer "type":"signed", ER_INVALID_JSON_VALUE_FOR_FUNC_INDEX
176 -->callmacro Create_array_index data2 text_idx text "type":"char(64)", ER_INVALID_JSON_VALUE_FOR_FUNC_INDEX
177 -->callmacro Create_array_index data2 text_default_type_idx text ER_INVALID_JSON_VALUE_FOR_FUNC_INDEX
178 -->callmacro Create_array_index data2 time_idx time "type":"time", ER_INVALID_JSON_VALUE_FOR_FUNC_INDEX
179 -->callmacro Create_array_index data2 date_idx date "type":"date", ER_INVALID_JSON_VALUE_FOR_FUNC_INDEX
180 -->callmacro Create_array_index data2 datetime_idx datetime "type":"datetime", ER_INVALID_JSON_VALUE_FOR_FUNC_INDEX
181 -->callmacro Create_array_index data2 float_idx float "type":"decimal(10,3)", ER_INVALID_JSON_VALUE_FOR_FUNC_INDEX
182
183
184 -->echo ## 6. Create different types of index on an empty array
185 -->callmacro Create_collection data3
186 -->callmacro Insert data3 '{"_id":"007", "empty":[]}' ER_SUCCESS
187 -->callmacro Test_array_index data3 empty_signed_idx empty "type":"SIGNED",
188 -->callmacro Test_array_index data3 empty_unsigned_idx empty "type":"UNSIGNED",
189 -->callmacro Test_array_index data3 empty_char_idx empty "type":"char(64)",
190 -->callmacro Test_array_index data3 empty_date_idx empty "type":"DATE",
191 -->callmacro Test_array_index data3 empty_time_idx empty "type":"TIME",
192 -->callmacro Test_array_index data3 empty_datetime_idx empty "type":"DATETIME",
193 -->callmacro Test_array_index data3 empty_decimal_idx empty "type":"DECIMAL(10,4)",
194 -->callmacro Test_array_index data3 empty_binary_idx empty "type":"BINARY(64)",
195
196
197 -->echo ## 7. Create index on a field having mixed data types
198 -->callmacro Create_collection data4
199 -->callmacro Insert data4 '{"_id":"008", "mixed":[1, -100, "foobar", "2018-12-05", "09:56:11", "2018-12-05 09:58:19", "3.1412", "1010101011"]}' ER_SUCCESS
200 -->callmacro Create_array_index data4 mixed_signed_idx mixed "type":"SIGNED", ER_INVALID_JSON_VALUE_FOR_FUNC_INDEX
201 -->callmacro Create_array_index data4 mixed_unsigned_idx mixed "type":"UNSIGNED", ER_WARN_DATA_OUT_OF_RANGE_FUNCTIONAL_INDEX
202 -->callmacro Create_array_index data4 mixed_char_idx mixed "type":"char(64)", ER_SUCCESS
203 -->callmacro Create_array_index data4 mixed_date_idx mixed "type":"DATE", ER_WARN_DATA_TRUNCATED_FUNCTIONAL_INDEX
204 -->callmacro Create_array_index data4 mixed_time_idx mixed "type":"TIME", ER_WARN_DATA_TRUNCATED_FUNCTIONAL_INDEX
205 -->callmacro Create_array_index data4 mixed_datetime_idx mixed "type":"DATETIME", ER_WARN_DATA_TRUNCATED_FUNCTIONAL_INDEX
206 -->callmacro Create_array_index data4 mixed_decimal_idx mixed "type":"DECIMAL(10,4)", ER_INVALID_JSON_VALUE_FOR_FUNC_INDEX
207 -->callmacro Create_array_index data4 mixed_binary_idx mixed "type":"BINARY(64)", ER_SUCCESS
208
209
210 -->echo ## 8. Create index on a field and insert document with that field missing
211 -->callmacro Create_collection data5
212 -->callmacro Insert data5 '{"_id":"001", "emp_id":[100, 101, 102, 103 ], "emp_age":[24,25,26,27]}' ER_SUCCESS
213 -->callmacro Create_array_index data5 emp_id_idx mixed "type":"UNSIGNED", ER_SUCCESS
214 -->callmacro Assert_index_exist data5 emp_id_idx
215 -->callmacro Insert data5 '{"_id":"002", "emp_age":[28,29,30,31]}' ER_SUCCESS
216
217
218 -->echo ## 9. Try to create index with type as JSON/TEXT[(N)] [charset_info]/CHAR[(N)] [charset_info]
219 -->callmacro Create_collection data6
220 -->callmacro Create_array_index data6 json_idx any "type":"JSON", ER_X_CMD_ARGUMENT_VALUE
221 -->callmacro Create_array_index data6 text_idx any "type":"text", ER_X_CMD_ARGUMENT_VALUE
222 -->callmacro Create_array_index data6 text_idx any "type":"text(64)", ER_X_CMD_ARGUMENT_VALUE
223 -->callmacro Create_array_index data6 text_idx any "type":"text(64) charset latin1 collate latin1_bin", ER_X_CMD_ARGUMENT_VALUE
224 -->callmacro Create_array_index data6 text_idx any "type":"text(64) charset latin1", ER_X_CMD_ARGUMENT_VALUE
225 -->callmacro Create_array_index data6 text_idx any "type":"text(64) collate latin1_bin", ER_X_CMD_ARGUMENT_VALUE
226 -->callmacro Create_array_index data6 text_idx any "type":"char(64) charset latin1", ER_X_CMD_ARGUMENT_VALUE
227 -->callmacro Create_array_index data6 text_idx any "type":"char(64) collate latin1_bin", ER_X_CMD_ARGUMENT_VALUE
228 -->callmacro Create_array_index data6 text_idx any "type":"char(64) charset latin1 collate latin1_bin", ER_X_CMD_ARGUMENT_VALUE
229
230
231 -->echo ## 10. Increasing admin commands counters
232 -->callmacro Create_collection data8
233
234 -->callmacro Assert_status_variable Mysqlx_stmt_create_collection_index 61
235 -->callmacro Create_array_index data8 idx any "type":"signed", ER_SUCCESS
236 -->callmacro Assert_status_variable Mysqlx_stmt_create_collection_index 62
237
238 -->callmacro Assert_status_variable Mysqlx_stmt_drop_collection_index 25
239 -->stmtadmin drop_collection_index {"schema":"xtest", "collection":"data8", "name":"idx"}
240 -->recvresult
241 -->callmacro Assert_status_variable Mysqlx_stmt_drop_collection_index 26
242
243 EOF
244
245
246 CREATE SCHEMA xtest DEFAULT CHARSET 'utf8mb4';
247
248 --exec $MYSQLXTEST -ux_root --password='' --file=$xtest_file 2>&1
249
250 ## Cleanup
251 --source ../include/xplugin_cleanup.inc