Debian Codegraph @

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

raw
1 ## Test of user defined function mysqlx_generate_document_id
2
3 ## Preamble
4 --source include/xplugin_preamble.inc
5 --source include/xplugin_create_user.inc
6 ## Test starts here
7
8
9 --echo
10 --echo ## 1.a. Verify that basic usage of `mysqlx_generate_document_id`
11 --echo ## works correctly using classic protocol.
12 --echo ## 1.b. Usage of `mysqlx_generate_document_id` with
13 --echo ## invalid arguments using classic protocol.
14 --echo ## 2.a. Handling of NULL values in DO (classic)
15 --echo ## 2.b. Handling of NULL values in SELECT (classic)
16 --echo ## 2.c. Handling of FUNCTION returning NULL values in DO (classic)
17 --echo ## 2.d. Handling of FUNCTION returning NULL values in SELECT (classic)
18 --echo ## 3.a. Verify that basic usage of `mysqlx_generate_document_id`
19 --echo ## works correctly using x protocol.
20 --echo ## 3.b. Usage of `mysqlx_generate_document_id` with
21 --echo ## invalid arguments using X protocol.
22 --echo ## 4.a. Handling of NULL values in DO (x)
23 --echo ## 4.b. Handling of NULL values in SELECT (x)
24 --echo ## 4.c. Handling of FUNCTION returning NULL values in DO (x)
25 --echo ## 4.d. Handling of FUNCTION returning NULL values in SELECT (x)
26
27
28 --write_file $MYSQL_TMP_DIR/udf_mysqlx_generate_document_id.xpl
29
30 -->echo
31 -->echo #
32 -->echo ## Ad 3.a. Verify that basic usage of `mysqlx_generate_document_id`
33 -->echo ## works correctly using x protocol.
34
35 SELECT mysqlx_generate_document_id() FROM xtest.util;
36
37 SELECT mysqlx_generate_document_id(16) FROM xtest.util;
38
39 SELECT mysqlx_generate_document_id(16, 8) FROM xtest.util;
40
41 SELECT mysqlx_generate_document_id(16, 8, 0) FROM xtest.util;
42
43 SELECT mysqlx_generate_document_id(16, 8, 1) FROM xtest.util;
44
45 set auto_increment_offset = 16;
46 set auto_increment_increment = 8;
47
48 CALL xtest.my_procedure(16, 8);
49
50 SELECT mysqlx_generate_document_id(@@auto_increment_offset, @@auto_increment_increment) FROM xtest.util;
51
52 DO mysqlx_generate_document_id();
53
54 -->echo
55 -->echo #
56 -->echo ## Ad 3.b. Usage of `mysqlx_generate_document_id` with
57 -->echo ## invalid arguments using X protocol.
58
59 -->stmtsql CALL mysqlx_generate_document_id();
60 -->expecterror ER_NO_DB_ERROR
61 -->recvresult
62
63 use xtest;
64
65 -->stmtsql CALL mysqlx_generate_document_id();
66 -->expecterror ER_SP_DOES_NOT_EXIST
67 -->recvresult
68
69
70 -->stmtsql DO mysqlx_generate_document_id('abc');
71 -->expecterror ER_CANT_INITIALIZE_UDF
72 -->recvresult
73
74 -->stmtsql DO mysqlx_generate_document_id(NULL);
75 -->expecterror ER_CANT_INITIALIZE_UDF
76 -->recvresult
77
78 -->stmtsql DO mysqlx_generate_document_id(123.45);
79 -->expecterror ER_CANT_INITIALIZE_UDF
80 -->recvresult
81
82 -->stmtsql DO mysqlx_generate_document_id(1, 'abc');
83 -->expecterror ER_CANT_INITIALIZE_UDF
84 -->recvresult
85
86 -->stmtsql DO mysqlx_generate_document_id(1, NULL);
87 -->expecterror ER_CANT_INITIALIZE_UDF
88 -->recvresult
89
90 -->stmtsql DO mysqlx_generate_document_id(1, 123.45);
91 -->expecterror ER_CANT_INITIALIZE_UDF
92 -->recvresult
93
94 -->stmtsql DO mysqlx_generate_document_id(1, 1, 'abc');
95 -->expecterror ER_CANT_INITIALIZE_UDF
96 -->recvresult
97
98 -->stmtsql DO mysqlx_generate_document_id(1, 1, NULL);
99 -->expecterror ER_CANT_INITIALIZE_UDF
100 -->recvresult
101
102 -->stmtsql DO mysqlx_generate_document_id(1, 1, 123.45);
103 -->expecterror ER_CANT_INITIALIZE_UDF
104 -->recvresult
105
106 -->stmtsql DO mysqlx_generate_document_id(1, 1, 1, 1);
107 -->expecterror ER_CANT_INITIALIZE_UDF
108 -->recvresult
109
110 -->stmtsql DO mysqlx_generate_document_id(1, 1, 1, 1, 1);
111 -->expecterror ER_CANT_INITIALIZE_UDF
112 -->recvresult
113
114
115 -->echo
116 -->echo #
117 -->echo ## Ad 4.a. Handling of NULL values in DO
118
119 -->stmtsql DO mysqlx_generate_document_id(NULL, 1, 1);
120 -->expecterror ER_CANT_INITIALIZE_UDF
121 -->recvresult
122
123 -->stmtsql DO mysqlx_generate_document_id(1, NULL, 1);
124 -->expecterror ER_CANT_INITIALIZE_UDF
125 -->recvresult
126
127 -->stmtsql DO mysqlx_generate_document_id(1, 1, NULL);
128 -->expecterror ER_CANT_INITIALIZE_UDF
129 -->recvresult
130
131
132 -->echo
133 -->echo #
134 -->echo ## Ad 4.b. Handling of NULL values in SELECT
135
136 -->stmtsql SELECT mysqlx_generate_document_id(NULL,1,1);
137 -->expecterror ER_CANT_INITIALIZE_UDF
138 -->recvresult
139
140 -->stmtsql SELECT mysqlx_generate_document_id(1,NULL,1);
141 -->expecterror ER_CANT_INITIALIZE_UDF
142 -->recvresult
143
144 -->stmtsql SELECT mysqlx_generate_document_id(1,1,NULL);
145 -->expecterror ER_CANT_INITIALIZE_UDF
146 -->recvresult
147
148
149 -->echo
150 -->echo #
151 -->echo ## Ad 4.c. Handling of FUNCTION returning NULL values in DO
152
153 -->stmtsql DO mysqlx_generate_document_id(IF(1=2,1,NULL), 1, 0);
154 -->recvresult
155
156 -->stmtsql DO mysqlx_generate_document_id(1, IF(1=2,1,NULL), 0);
157 -->recvresult
158
159 -->stmtsql DO mysqlx_generate_document_id(1, 1, IF(1=2,1,NULL));
160 -->recvresult
161
162
163 -->echo
164 -->echo #
165 -->echo ## Ad 4.d. Handling of FUNCTION returning NULL values in SELECT
166
167 -->stmtsql SELECT mysqlx_generate_document_id(IF(1=2,1,NULL),1,0);
168 -->recvresult
169
170 -->stmtsql SELECT mysqlx_generate_document_id(1,IF(1=2,1,NULL),0);
171 -->recvresult
172
173 -->stmtsql SELECT mysqlx_generate_document_id(1,1,IF(1=2,1,NULL));
174 -->recvresult
175 EOF
176
177
178 --let DOC_ID_TIME_MASK= /([0-9a-f]{4})[0-9a-f]{8}([0-9a-f]{16})/\1XXXXXXXX\2/
179
180 CREATE SCHEMA xtest;
181 USE xtest;
182 CREATE TABLE util(f INT);
183
184 INSERT INTO util(f) VALUES(1),(2),(3),(4),(5);
185
186 --echo
187 --echo #
188 --echo ## Ad 1.a. Verify that basic usage of `mysqlx_generate_document_id`
189 --echo ## works correctly using classic protocol.
190
191 --replace_regex $DOC_ID_TIME_MASK
192 SELECT mysqlx_generate_document_id() FROM util;
193
194 --replace_regex $DOC_ID_TIME_MASK
195 SELECT mysqlx_generate_document_id(16) FROM util;
196
197 --replace_regex $DOC_ID_TIME_MASK
198 SELECT mysqlx_generate_document_id(16, 8) FROM util;
199
200 --replace_regex $DOC_ID_TIME_MASK
201 SELECT mysqlx_generate_document_id(16, 8, 0) FROM util;
202
203 --replace_regex $DOC_ID_TIME_MASK
204 SELECT mysqlx_generate_document_id(16, 8, 1) FROM util;
205
206 set auto_increment_offset = 16;
207 set auto_increment_increment = 8;
208
209 --replace_regex $DOC_ID_TIME_MASK
210 SELECT mysqlx_generate_document_id(@@auto_increment_offset, @@auto_increment_increment) FROM util;
211
212 DELIMITER ||;
213 CREATE PROCEDURE my_procedure(IN offset INT, IN increment INT)
214 BEGIN
215 SELECT mysqlx_generate_document_id(offset, increment) AS document_id;
216 END ||
217 DELIMITER ;||
218
219 --replace_regex $DOC_ID_TIME_MASK
220 CALL my_procedure(16, 8);
221
222 --echo
223 --echo #
224 --echo ## Ad 1.b. Usage of `mysqlx_generate_document_id` with
225 --echo ## invalid arguments using classic protocol.
226
227 DO mysqlx_generate_document_id();
228
229 # The functions accepts from 1-3 arguments, and all
230 # should be INTs.
231 --error 1305
232 CALL mysqlx_generate_document_id();
233
234 --error 1123
235 DO mysqlx_generate_document_id('abc');
236
237 --error 1123
238 DO mysqlx_generate_document_id(NULL);
239
240 --error 1123
241 DO mysqlx_generate_document_id(123.45);
242
243 --error 1123
244 DO mysqlx_generate_document_id(1, 'abc');
245
246 --error 1123
247 DO mysqlx_generate_document_id(1, NULL);
248
249 --error 1123
250 DO mysqlx_generate_document_id(1, 123.45);
251
252 --error 1123
253 DO mysqlx_generate_document_id(1, 1, 'abc');
254
255 --error 1123
256 DO mysqlx_generate_document_id(1, 1, NULL);
257
258 --error 1123
259 DO mysqlx_generate_document_id(1, 1, 123.45);
260
261 --error 1123
262 DO mysqlx_generate_document_id(1, 1, 1, 1);
263
264 --error 1123
265 DO mysqlx_generate_document_id(1, 1, 1, 1, 1);
266
267 --echo
268 --echo #
269 --echo ## Ad 2.a. Handling of NULL values in DO
270
271 --error 1123
272 DO mysqlx_generate_document_id(NULL, 1, 1);
273
274 --error 1123
275 DO mysqlx_generate_document_id(1, NULL, 1);
276
277 --error 1123
278 DO mysqlx_generate_document_id(1, 1, NULL);
279
280
281 --echo
282 --echo #
283 --echo ## Ad 2.b. Handling of NULL values in SELECT
284
285 --error 1123
286 SELECT mysqlx_generate_document_id(NULL,1,1);
287
288 --error 1123
289 SELECT mysqlx_generate_document_id(1,NULL,1);
290
291 --error 1123
292 SELECT mysqlx_generate_document_id(1,1,NULL);
293
294
295 --echo
296 --echo #
297 --echo ## Ad 2.c. Handling of FUNCTION returning NULL values in DO
298
299 --replace_regex $DOC_ID_TIME_MASK
300 DO mysqlx_generate_document_id(IF(1=2,1,NULL), 1, 0);
301
302 --replace_regex $DOC_ID_TIME_MASK
303 DO mysqlx_generate_document_id(1, IF(1=2,1,NULL), 0);
304
305 --replace_regex $DOC_ID_TIME_MASK
306 DO mysqlx_generate_document_id(1, 1, IF(1=2,1,NULL));
307
308
309 --echo
310 --echo #
311 --echo ## Ad 2.d. Handling of FUNCTION returning NULL values in SELECT
312
313 --replace_regex $DOC_ID_TIME_MASK
314 SELECT mysqlx_generate_document_id(IF(1=2,1,NULL),1,0);
315
316 --replace_regex $DOC_ID_TIME_MASK
317 SELECT mysqlx_generate_document_id(1,IF(1=2,1,NULL),0);
318
319 --replace_regex $DOC_ID_TIME_MASK
320 SELECT mysqlx_generate_document_id(1,1,IF(1=2,1,NULL));
321
322
323 --replace_regex $DOC_ID_TIME_MASK
324 exec $MYSQLXTEST
325 -ux_root --password=''
326 --file=$MYSQL_TMP_DIR/udf_mysqlx_generate_document_id.xpl 2>&1;
327
328 SET @doc = '{"a":1}';
329 SELECT JSON_INSERT(@doc, '$.a', CONVERT(mysqlx_generate_document_id(1,1,JSON_CONTAINS_PATH(@doc, 'one', '$.a')) USING utf8mb4)) as doc;
330 --replace_regex $DOC_ID_TIME_MASK
331 SELECT JSON_INSERT(@doc, '$.b', CONVERT(mysqlx_generate_document_id(1,1,JSON_CONTAINS_PATH(@doc, 'one', '$.b')) USING utf8mb4)) as doc;
332
333
334 ## Cleanup
335 --source ../include/xplugin_cleanup.inc
336 set auto_increment_increment = DEFAULT;
337 set auto_increment_offset = DEFAULT;