Debian Codegraph @

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

raw
1 ## Test of general functionality of prepare statement crud insert message
2
3 ## Preamble
4 --source include/xplugin_preamble.inc
5 --source include/xplugin_create_user.inc
6 ## Test starts here
7
8 --write_file $MYSQL_TMP_DIR/prep_stmt_insert.xpl
9 -->import assert_variable.macro
10 -->import assert_select.macro
11 -->quiet
12
13 -->macro Assert_is_exist %PATTERN%
14 callmacro Assert_select
15 count
16 1
17 SELECT count(*) as count FROM xtest.xcol WHERE doc->>'$.pupil' LIKE %PATTERN%;
18 -->endmacro
19
20 -->stmtadmin create_collection {"schema":"xtest","name":"xcol"}
21 -->recvresult
22
23 -->echo
24 -->echo ## 1. Prepare malformed insert stmt (no schema)
25 -->echo # a. Try to execute
26 -->echo # b. Try to deallocate
27 -->echo ## 2. Prepare correct insert stmt (no placeholders)
28 -->echo # a. Execute (no params)
29 -->echo # b. Deallocate
30 -->echo ## 3. Prepare correct stmt (one placeholder)
31 -->echo # a. Try to execute (no params)
32 -->echo # b. Execute with param
33 -->echo # c. Execute with params (one extra to ignore)
34 -->echo # d. Deallocate
35 -->echo ## 4. Prepare correct stmt (two placeholders)
36 -->echo # a. Execute with params
37 -->echo # b. Deallocate
38 -->echo ## 5. Prepare correct stmt (two placeholders in expr)
39 -->echo # a. Execute with params
40 -->echo # b. Deallocate
41 -->echo ## 6. Prepare correct stmt (placeholder on _id)
42 -->echo # a. Execute with param
43 -->echo # b. Deallocate
44 -->echo ## 7. Prepare correct stmt with upsert (placeholder on "type" field)
45 -->echo # a. Execute with param "dog" (insert - "dog")
46 -->echo # b. Execute with param "cat" (update from "dog" to "cat")
47 -->echo # c. Deallocate
48
49
50 -->title =Stmt 1: Prepare malformed insert stmt (no schema)
51
52 Mysqlx.Prepare.Prepare {
53 stmt_id: 1
54 stmt: {
55 type: INSERT
56 insert: {
57 collection { name: "xcol" schema: "nonexistent" }
58 data_model: DOCUMENT
59 row {
60 field {
61 type: LITERAL literal {
62 type: V_STRING v_string {
63 value: '{"pupil":"Alice", "pet":"cat"}'
64 }
65 }
66 }
67 }
68 }
69 }
70 }
71 -->recverror ER_BAD_DB_ERROR
72
73 -->callmacro Assert_status_variable Mysqlx_prep_prepare 1
74
75 -->title =Stmt 1.a. Try to execute
76
77 Mysqlx.Prepare.Execute {
78 stmt_id: 1
79 }
80 -->expecterror ER_X_BAD_STATEMENT_ID
81 -->recvresult
82
83 -->callmacro Assert_status_variable Mysqlx_prep_execute 1
84
85 -->title =Stmt 1.b. Try to deallocate
86
87 Mysqlx.Prepare.Deallocate {
88 stmt_id: 1
89 }
90 -->recverror ER_X_BAD_STATEMENT_ID
91
92 -->callmacro Assert_status_variable Mysqlx_prep_deallocate 1
93
94
95
96 -->title =Stmt 2: Prepare correct insert stmt (no placeholders)
97
98 Mysqlx.Prepare.Prepare {
99 stmt_id: 2
100 stmt: {
101 type: INSERT
102 insert: {
103 collection { name: "xcol" schema: "xtest" }
104 data_model: DOCUMENT
105 row {
106 field {
107 type: LITERAL literal {
108 type: V_STRING v_string {
109 value: '{"pupil":"Alice", "pet":"cat"}'
110 }
111 }
112 }
113 }
114 }
115 }
116 }
117 -->recvok
118
119 -->callmacro Assert_status_variable Mysqlx_prep_prepare 2
120
121
122 -->title =Stmt 2.a. Execute (no params)
123
124 Mysqlx.Prepare.Execute {
125 stmt_id: 2
126 }
127 -->recvresult
128
129 -->callmacro Assert_is_exist 'Alice%'
130
131 -->callmacro Assert_status_variable Mysqlx_prep_execute 2
132
133 -->title =Stmt 2.b.: Deallocate
134
135 Mysqlx.Prepare.Deallocate {
136 stmt_id: 2
137 }
138 -->recvok
139
140
141 -->title =Stmt 3: Prepare correct stmt (one placeholder)
142
143 Mysqlx.Prepare.Prepare {
144 stmt_id: 3
145 stmt: {
146 type: INSERT
147 insert: {
148 collection { name: "xcol" schema: "xtest" }
149 data_model: DOCUMENT
150 row {
151 field {
152 type: PLACEHOLDER position: 0
153 }
154 }
155 }
156 }
157 }
158 -->recvok
159
160 -->callmacro Assert_status_variable Mysqlx_prep_prepare 3
161
162
163 -->title =Stmt 3.a. Try to execute (no params)
164
165 Mysqlx.Prepare.Execute {
166 stmt_id: 3
167 }
168 -->expecterror ER_X_PREPARED_EXECUTE_ARGUMENT_CONSISTENCY
169 -->recvresult
170
171 -->callmacro Assert_status_variable Mysqlx_prep_execute 3
172
173
174 -->title =Stmt 3.b. Execute with param
175
176 Mysqlx.Prepare.Execute {
177 stmt_id: 3
178 args {
179 type: SCALAR scalar {
180 type: V_STRING v_string {
181 value: '{"pupil":"Bob", "pet":"dog"}'
182 }
183 }
184 }
185 }
186 -->recvresult
187
188 -->callmacro Assert_is_exist 'Bob%'
189
190 -->callmacro Assert_status_variable Mysqlx_prep_execute 4
191
192
193 -->title =Stmt 3.c. Execute with params (one extra to ignore)
194
195 Mysqlx.Prepare.Execute {
196 stmt_id: 3
197 args {
198 type: SCALAR scalar {
199 type: V_STRING v_string {
200 value: '{"pupil":"Christina", "pet":"hamster"}'
201 }
202 }
203 }
204 args {
205 type: SCALAR scalar {
206 type: V_STRING v_string {
207 value: '{"pupil":"Charlie"}'
208 }
209 }
210 }
211 }
212 -->recvresult
213
214 -->callmacro Assert_is_exist 'Ch%'
215
216 -->callmacro Assert_status_variable Mysqlx_prep_execute 5
217
218
219 -->title =Stmt 3.d. Deallocate
220
221 Mysqlx.Prepare.Deallocate {
222 stmt_id: 3
223 }
224 -->recvok
225
226 -->callmacro Assert_status_variable Mysqlx_prep_deallocate 3
227
228
229
230 -->title =Stmt 4: Prepare correct stmt (two placeholders)
231
232 Mysqlx.Prepare.Prepare {
233 stmt_id: 4
234 stmt: {
235 type: INSERT
236 insert: {
237 collection { name: "xcol" schema: "xtest" }
238 data_model: DOCUMENT
239 row {
240 field {
241 type: PLACEHOLDER position: 0
242 }
243 }
244 row {
245 field {
246 type: PLACEHOLDER position: 1
247 }
248 }
249 }
250 }
251 }
252 -->recvok
253
254 -->callmacro Assert_status_variable Mysqlx_prep_prepare 4
255
256
257 -->title =Stmt 4.a. Execute with params
258
259 Mysqlx.Prepare.Execute {
260 stmt_id: 4
261 args {
262 type: SCALAR scalar {
263 type: V_STRING v_string {
264 value: '{"pupil":"David", "pet":"parot"}'
265 }
266 }
267 }
268 args {
269 type: SCALAR scalar {
270 type: V_STRING v_string {
271 value: '{"_id":"abc123", "pupil":"Eva", "pet":"fish"}'
272 }
273 }
274 }
275 }
276 -->recvresult
277
278 -->callmacro Assert_is_exist 'David%'
279 -->callmacro Assert_is_exist 'Eva%'
280
281 -->callmacro Assert_status_variable Mysqlx_prep_execute 6
282
283
284 -->title =Stmt 4.b. Deallocate
285
286 Mysqlx.Prepare.Deallocate {
287 stmt_id: 4
288 }
289 -->recvok
290
291 -->callmacro Assert_status_variable Mysqlx_prep_deallocate 4
292
293
294
295 -->title =Stmt 5: Prepare correct stmt (two placeholders in expr)
296
297 Mysqlx.Prepare.Prepare {
298 stmt_id: 5
299 stmt: {
300 type: INSERT
301 insert: {
302 collection { name: "xcol" schema: "xtest" }
303 data_model: DOCUMENT
304 row {
305 field {
306 type: OBJECT object {
307 fld {
308 key: "pupil"
309 value {
310 type: PLACEHOLDER position: 0
311 }
312 }
313 fld {
314 key: "pet"
315 value {
316 type: PLACEHOLDER position: 1
317 }
318 }
319 }
320 }
321 }
322 }
323 }
324 }
325 -->recvok
326
327 -->callmacro Assert_status_variable Mysqlx_prep_prepare 5
328
329
330 -->title =Stmt 5.a. Execute with params
331
332 Mysqlx.Prepare.Execute {
333 stmt_id: 5
334 args {
335 type: SCALAR scalar {
336 type: V_STRING v_string {
337 value: "Felix"
338 }
339 }
340 }
341 args {
342 type: SCALAR scalar {
343 type: V_STRING v_string {
344 value: "lizard"
345 }
346 }
347 }
348 }
349 -->recvmessage Mysqlx.Notice.Frame {type:3 scope:LOCAL payload:"param: ROWS_AFFECTED value {v_unsigned_int: 1}"}
350 -->recvmessage Mysqlx.Notice.Frame {type:3 scope:LOCAL payload:"param: GENERATED_DOCUMENT_IDS"}
351 -->recvmessage Mysqlx.Sql.StmtExecuteOk {}
352
353
354 -->callmacro Assert_is_exist 'Felix%'
355
356 -->callmacro Assert_status_variable Mysqlx_prep_execute 7
357
358
359 -->title =Stmt 5.b. Deallocate
360
361 Mysqlx.Prepare.Deallocate {
362 stmt_id: 5
363 }
364 -->recvok
365
366 -->callmacro Assert_status_variable Mysqlx_prep_deallocate 5
367
368 -->title =Stmt 6: Prepare correct stmt (placeholder on _id)
369
370 Mysqlx.Prepare.Prepare {
371 stmt_id: 6
372 stmt: {
373 type: INSERT
374 insert: {
375 collection { name: "xcol" schema: "xtest" }
376 data_model: DOCUMENT
377 row {
378 field {
379 type: OBJECT object {
380 fld {
381 key: "_id"
382 value {
383 type: PLACEHOLDER position: 0
384 }
385 }
386 fld {
387 key: "pet"
388 value {
389 type: LITERAL literal {type: V_STRING v_string{ value: "dog"}}
390 }
391 }
392 }
393 }
394 }
395 }
396 }
397 }
398 -->recvok
399
400 -->callmacro Assert_status_variable Mysqlx_prep_prepare 6
401
402
403 -->title =Stmt 6.a. Execute with params
404
405 Mysqlx.Prepare.Execute {
406 stmt_id: 6
407 args {
408 type: SCALAR scalar {
409 type: V_STRING v_string {
410 value: "Felix"
411 }
412 }
413 }
414 }
415 -->recvmessage Mysqlx.Notice.Frame {type:3 scope:LOCAL payload:"param: ROWS_AFFECTED value {v_unsigned_int: 1}"}
416 -->recvmessage Mysqlx.Sql.StmtExecuteOk {}
417
418
419 -->callmacro Assert_is_exist 'Felix%'
420
421 -->callmacro Assert_status_variable Mysqlx_prep_execute 8
422
423
424 -->title =Stmt 6.b. Deallocate
425
426 Mysqlx.Prepare.Deallocate {
427 stmt_id: 6
428 }
429 -->recvok
430
431 -->callmacro Assert_status_variable Mysqlx_prep_deallocate 6
432
433 -->title =Stmt 7: Prepare correct stmt with upsert (placeholder on "type" field)
434
435 Mysqlx.Prepare.Prepare {
436 stmt_id: 7
437 stmt: {
438 type: INSERT
439 insert: {
440 collection { name: "xcol" schema: "xtest" }
441 data_model: DOCUMENT
442 upsert: true
443 row {
444 field {
445 type: OBJECT object {
446 fld {
447 key: "type"
448 value {
449 type: PLACEHOLDER position: 0
450 }
451 }
452 fld {
453 key: "_id"
454 value {
455 type: LITERAL literal {type: V_STRING v_string{ value: "animal"}}
456 }
457 }
458 }
459 }
460 }
461 }
462 }
463 }
464 -->recvok
465
466 -->callmacro Assert_status_variable Mysqlx_prep_prepare 7
467
468
469 -->title =Stmt 7.a. Execute with param "dog" (insert - "dog")
470
471 Mysqlx.Prepare.Execute {
472 stmt_id: 7
473 args {
474 type: SCALAR scalar {
475 type: V_STRING v_string {
476 value: "Dog"
477 }
478 }
479 }
480 }
481 -->recvmessage Mysqlx.Notice.Frame {type:3 scope:LOCAL payload:"param: ROWS_AFFECTED value {v_unsigned_int: 1}"}
482 -->recvmessage Mysqlx.Sql.StmtExecuteOk {}
483
484
485 -->callmacro Assert_status_variable Mysqlx_prep_execute 9
486
487 -->title =Stmt 7.b. Execute with param "cat" (update from "dog" to "cat")
488
489 Mysqlx.Prepare.Execute {
490 stmt_id: 7
491 args {
492 type: SCALAR scalar {
493 type: V_STRING v_string {
494 value: "Cat"
495 }
496 }
497 }
498 }
499 -->recvmessage Mysqlx.Notice.Frame {type:3 scope:LOCAL payload:"param: ROWS_AFFECTED value {}"}
500 -->recvmessage Mysqlx.Sql.StmtExecuteOk {}
501
502 -->callmacro Assert_status_variable Mysqlx_prep_execute 10
503
504
505 -->title =Stmt 7.c. Deallocate
506
507 Mysqlx.Prepare.Deallocate {
508 stmt_id: 7
509 }
510 -->recvok
511
512 -->callmacro Assert_status_variable Mysqlx_prep_deallocate 7
513
514 EOF
515
516
517 CREATE SCHEMA IF NOT EXISTS xtest;
518
519
520 --replace_regex /([0-9a-f]{4})[0-9a-f]{8}([0-9a-f]{16})/\1XXXXXXXX\2/
521 exec $MYSQLXTEST
522 -ux_root --password='' --schema=xtest
523 --file=$MYSQL_TMP_DIR/prep_stmt_insert.xpl 2>&1;
524
525
526
527
528 ## Cleanup
529 --source ../include/xplugin_cleanup.inc