Debian Codegraph @

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

raw
1 ## Test of general functionality of prepare statement
2
3 ## Preamble
4 --source include/no_ps_protocol.inc
5 --source include/xplugin_preamble.inc
6 --source include/xplugin_create_user.inc
7 ## Test starts here
8
9 --write_file $MYSQL_TMP_DIR/prep_stmt_sundries.xpl
10 -->import assert_variable.macro
11 -->quiet
12
13 -->echo
14 -->echo ## 1. Verify prep-stat status variables that are set to zero
15 -->echo ## 2. Prepare statement
16 -->echo # a. fails in case when invalid data are supplied
17 -->echo # b. success for valid data with one placeholder
18 -->echo ## 3. Execute prepared statement
19 -->echo # a. success for execute with one parameter
20 -->echo # b. success for execute with two parameters (more than needed)
21 -->echo # c. fails when no parameter was supplied
22 -->echo # d. fails when parameter of invalid type was supplied
23 -->echo # e. fails when using invalid ID for prepared-statmenent
24 -->echo ## 4. Deallocate prepared statement
25 -->echo # a. success when using ID that was prepared
26 -->echo # b. fails when using ID that was already deallocated
27 -->echo # c. fails when using ID that was never prepared
28 -->echo ## 5. Mixed tests
29 -->echo # a. prepare-deallocate, reuse the same ID in next prepare
30 -->echo # b. prepare two times same statement, show that previous stmt was deallocated
31 -->echo # c. prepare-execute-deallocate confirm that next execute with deallocated-id is going to fail
32 -->echo # d. prepare-execute stmt that returns NULL
33 -->echo ## 6. Assert status variables for new session, they should be at zero
34 -->echo ## 7. Assert that prep-statment id is local in context of session
35 -->echo ## 8. Assert that prep-execute-statment id is local in context of session
36
37 -->title = 1. Prep-stmt variables are set to zero
38
39 -->macro Assert_status_variables_required_by_the_test
40 callmacro Assert_status_variable Mysqlx_prep_prepare 0;
41 callmacro Assert_status_variable Mysqlx_prep_execute 0;
42 callmacro Assert_status_variable Mysqlx_prep_deallocate 0;
43 -->endmacro
44
45 -->callmacro Assert_status_variables_required_by_the_test
46 -->callmacro Assert_status_variable Prepared_stmt_count 0
47 -->callmacro Assert_status_variable Com_stmt_prepare 0
48 -->callmacro Assert_status_variable Com_stmt_execute 0
49 -->callmacro Assert_status_variable Com_stmt_fetch 0
50 -->callmacro Assert_status_variable Com_stmt_close 0
51
52 -->title = 2.a. Malformed stmt for prepare
53
54 Mysqlx.Prepare.Prepare {
55 stmt_id: 102
56 stmt: {
57 type: FIND
58 find: {
59 collection { name: "xtable" schema: "nonexistent" }
60 data_model: TABLE
61 projection {
62 source {
63 type: LITERAL literal {
64 type: V_OCTETS v_octets {
65 value: "1"
66 }
67 }
68 }
69 }
70 }
71 }
72 }
73 -->recverror ER_BAD_DB_ERROR
74
75 -->callmacro Assert_status_variable Mysqlx_prep_prepare 1
76 -->callmacro Assert_status_variable Prepared_stmt_count 0
77
78
79 -->title = 2.b. Correct stmt
80
81 Mysqlx.Prepare.Prepare {
82 stmt_id: 1
83 stmt: {
84 type: FIND
85 find: {
86 collection { name: "xtable" schema: "xtest" }
87 data_model: TABLE
88 projection {
89 source {
90 type: FUNC_CALL function_call {
91 name {
92 name: "count"
93 }
94 param {
95 type: LITERAL literal {
96 type: V_OCTETS v_octets {
97 value: "*"
98 }
99 }
100 }
101 }
102 }
103 }
104 criteria {
105 type: OPERATOR operator {
106 name: "like"
107 param {
108 type: IDENT identifier {
109 name: 'phrase'
110 }
111 }
112 param {
113 type: PLACEHOLDER position: 0
114 }
115 }
116 }
117 }
118 }
119 }
120 -->recvok
121
122 -->callmacro Assert_status_variable Mysqlx_prep_prepare 2
123 -->callmacro Assert_status_variable Prepared_stmt_count 1
124
125
126 -->title = 3.a. Correct stmt execute
127
128 Mysqlx.Prepare.Execute {
129 stmt_id: 1
130 args {
131 type: SCALAR scalar {
132 type: V_STRING v_string {
133 value: "%"
134 }
135 }
136 }
137 }
138 -->recvresult
139
140 -->callmacro Assert_status_variable Mysqlx_prep_execute 1
141
142
143 -->title = 3.b. Correct stmt execute (more than one expected args)
144
145 Mysqlx.Prepare.Execute {
146 stmt_id: 1
147 args {
148 type: SCALAR scalar {
149 type: V_STRING v_string {
150 value: "Alice%"
151 }
152 }
153 }
154 args {
155 type: SCALAR scalar {
156 type: V_STRING v_string {
157 value: "123"
158 }
159 }
160 }
161 }
162 -->recvresult
163
164 -->callmacro Assert_status_variable Mysqlx_prep_execute 2
165
166
167 -->title = 3.c. Wrong stmt execute (no arg)
168 Mysqlx.Prepare.Execute {
169 stmt_id: 1
170 }
171 -->expecterror ER_X_PREPARED_EXECUTE_ARGUMENT_CONSISTENCY
172 -->recvresult
173
174 -->callmacro Assert_status_variable Mysqlx_prep_execute 3
175
176 -->title = 3.d. Correct stmt executed, still argument type is invalid (only scalar is supported)
177 Mysqlx.Prepare.Execute {
178 stmt_id: 1
179 args {
180 type: ARRAY
181 array {
182 }
183 }
184 }
185 -->expecterror ER_X_PREPARED_EXECUTE_ARGUMENT_NOT_SUPPORTED
186 -->recvresult
187
188 -->callmacro Assert_status_variable Mysqlx_prep_execute 4
189
190
191 -->title = 3.e. Wrong stmt execute (wrong id)
192
193 Mysqlx.Prepare.Execute {
194 stmt_id: 42
195 args {
196 type: SCALAR scalar {
197 type: V_STRING v_string {
198 value: "Bob%"
199 }
200 }
201 }
202 }
203 -->expecterror ER_X_BAD_STATEMENT_ID
204 -->recvresult
205
206 -->callmacro Assert_status_variable Mysqlx_prep_execute 5
207
208
209 -->title = 4.a. Correct stmt close
210
211 Mysqlx.Prepare.Deallocate {
212 stmt_id: 1
213 }
214 -->recvok
215
216 -->callmacro Assert_status_variable Mysqlx_prep_deallocate 1
217 -->callmacro Assert_status_variable Prepared_stmt_count 0
218
219 -->title = 4.b. Wrong stmt close (bad id) / try to close second time
220
221 Mysqlx.Prepare.Deallocate {
222 stmt_id: 1
223 }
224 -->recverror ER_X_BAD_STATEMENT_ID
225
226 -->callmacro Assert_status_variable Mysqlx_prep_deallocate 2
227
228
229 -->title = 4.c. Wrong stmt close (bad id)
230
231 Mysqlx.Prepare.Deallocate {
232 stmt_id: 77
233 }
234 -->recverror ER_X_BAD_STATEMENT_ID
235
236 -->callmacro Assert_status_variable Mysqlx_prep_deallocate 3
237
238 -->title = 5.a. Reuse deallocated ID
239
240 Mysqlx.Prepare.Prepare {
241 stmt_id: 2
242 stmt: {
243 type: STMT
244 stmt_execute: {
245 stmt: "select 1"
246 }
247 }
248 }
249 -->recvok
250
251 -->callmacro Assert_status_variable Prepared_stmt_count 1
252
253 Mysqlx.Prepare.Deallocate {
254 stmt_id: 2
255 }
256 -->recvok
257
258 Mysqlx.Prepare.Prepare {
259 stmt_id: 2
260 stmt: {
261 type: STMT
262 stmt_execute: {
263 stmt: "select 2"
264 }
265 }
266 }
267 -->recvok
268
269 -->callmacro Assert_status_variable Prepared_stmt_count 1
270
271 Mysqlx.Prepare.Execute {
272 stmt_id: 2
273 }
274 -->recvtovar %DATA%
275 -->assert_eq 2 %DATA%
276
277 Mysqlx.Prepare.Deallocate {
278 stmt_id: 2
279 }
280 -->recvok
281
282 -->callmacro Assert_status_variable Prepared_stmt_count 0
283
284 -->title = 5.b. Reuse ID which deallocates previous one
285 Mysqlx.Prepare.Prepare {
286 stmt_id: 3
287 stmt: {
288 type: STMT
289 stmt_execute: {
290 stmt: "select 1"
291 }
292 }
293 }
294 -->recvok
295 -->callmacro Assert_status_variable Prepared_stmt_count 1
296
297 Mysqlx.Prepare.Prepare {
298 stmt_id: 3
299 stmt: {
300 type: STMT
301 stmt_execute: {
302 stmt: "select 3"
303 }
304 }
305 }
306 -->recvok
307
308 -->callmacro Assert_status_variable Prepared_stmt_count 1
309
310 Mysqlx.Prepare.Execute {
311 stmt_id: 3
312 }
313 -->recvtovar %DATA1%
314 -->assert_eq 3 %DATA1%
315
316 Mysqlx.Prepare.Deallocate {
317 stmt_id: 3
318 }
319 -->recvok
320
321 -->title = 5.c. prepare-execute-deallocate confirm that next execute with deallocated-id is going to fail
322
323 Mysqlx.Prepare.Execute {
324 stmt_id: 3
325 }
326 -->recverror ER_X_BAD_STATEMENT_ID
327
328
329 -->callmacro Assert_status_variable Prepared_stmt_count 0
330
331 -->title = 5.d. prepare-execute stmt that returns NULL
332
333 Mysqlx.Prepare.Prepare {
334 stmt_id: 4
335 stmt {
336 type: STMT
337 stmt_execute {
338 stmt: "SELECT NULL"
339 }
340 }
341 }
342 -->recvok
343
344 Mysqlx.Prepare.Execute {
345 stmt_id: 4
346 }
347 -->recvresult
348
349 Mysqlx.Prepare.Deallocate {
350 stmt_id: 4
351 }
352 -->recvok
353
354 -->callmacro Assert_status_variable Prepared_stmt_count 0
355
356 ## Test send six Mysqlx.Prepare.Deallocate messages, still one of them failed.
357 -->callmacro Assert_status_variable Com_stmt_close 6
358 ## Test send six Mysqlx.Prepare.Prepare messages, all requests were successful.
359 -->callmacro Assert_status_variable Com_stmt_prepare 7
360 ## Test send six Mysqlx.Prepare.Execute messages, still two of them failed.
361 -->callmacro Assert_status_variable Com_stmt_execute 5
362 ## Test didn't send a Mysqlx.Prepare.Fetch message.
363 -->callmacro Assert_status_variable Com_stmt_fetch 0
364
365 -->title = 6. Assert status variables for new session, they should be at zero
366 -->newsession s1 x_root
367 -->callmacro Assert_status_variables_required_by_the_test
368
369 -->title = 7. Assert that prep-statment id is local in context of session
370 Mysqlx.Prepare.Prepare {
371 stmt_id: 1
372 stmt: {
373 type: STMT
374 stmt_execute: {
375 stmt: "select 1"
376 }
377 }
378 }
379 -->recvok
380 -->closesession
381 -->newsession s2 x_root
382
383 Mysqlx.Prepare.Execute {
384 stmt_id: 1
385 }
386 -->expecterror ER_X_BAD_STATEMENT_ID
387 -->recvresult
388
389 Mysqlx.Prepare.Prepare {
390 stmt_id: 1
391 stmt: {
392 type: STMT
393 stmt_execute: {
394 stmt: "select 1"
395 }
396 }
397 }
398 -->recvok
399
400 -->title = 8. Assert that prep-execute-statment id is local in context of session
401 Mysqlx.Prepare.Prepare {
402 stmt_id: 1
403 stmt: {
404 type: STMT
405 stmt_execute: {
406 stmt: "select 1"
407 }
408 }
409 }
410 -->recvok
411 Mysqlx.Prepare.Execute {
412 stmt_id: 1
413 }
414 -->recvresult
415 -->closesession
416 -->newsession s2 x_root
417
418 Mysqlx.Prepare.Execute {
419 stmt_id: 1
420 }
421 -->expecterror ER_X_BAD_STATEMENT_ID
422 -->recvresult
423
424 Mysqlx.Prepare.Prepare {
425 stmt_id: 1
426 stmt: {
427 type: STMT
428 stmt_execute: {
429 stmt: "select 1"
430 }
431 }
432 }
433 -->recvok
434 EOF
435
436 # This test verifies global status variables
437 # which can't be reset by SQL.
438 # A restart is going to force, all status variables
439 # to be set to zero.
440 --source include/restart_mysqld.inc
441
442 CREATE SCHEMA IF NOT EXISTS xtest;
443 USE xtest;
444 CREATE TABLE xtable (phrase VARCHAR(30));
445 INSERT INTO xtable (phrase) VALUES ('Alice has a cat');
446 INSERT INTO xtable (phrase) VALUES ('Bob has a dog');
447
448
449 exec $MYSQLXTEST
450 -ux_root --password='' --schema=xtest
451 --file=$MYSQL_TMP_DIR/prep_stmt_sundries.xpl 2>&1;
452
453 # Ensure that all connections are closed, after that we can proceed
454 # with Prepared_stmt_count status variables assertion
455 #
456 let $wait_for_status_variable = 'Mysqlx_connections_closed',
457 'Mysqlx_sessions';
458 let $wait_for_status_value = 4, 0;
459 source ../include/wait_for_status_variables.inc;
460
461 --echo
462 --echo ## Assert global X Plugin status variables
463 --let $assert_text= Global plugin status Mysqlx_prep_prepare must match number of send Mysqlx.Prepare.Prepare messages
464 --let $assert_cond= [SHOW GLOBAL STATUS LIKE "Mysqlx_prep_prepare", Value, 1] = 11
465 --source include/assert.inc
466
467 --let $assert_text= Global plugin status Mysqlx_prep_execute must match number of send Mysqlx.Prepare.Execute messages
468 --let $assert_cond= [SHOW GLOBAL STATUS LIKE "Mysqlx_prep_execute", Value, 1] = 12
469 --source include/assert.inc
470
471 --let $assert_text= Global plugin status Mysqlx_prep_deallocate must match number of send Mysqlx.Prepare.Deallocate messages
472 --let $assert_cond= [SHOW GLOBAL STATUS LIKE "Mysqlx_prep_deallocate", Value, 1] = 7
473 --source include/assert.inc
474
475
476 --echo
477 --echo ## Assert global server status variables
478 --let $assert_text= Global system status Prepared_stmt_count must be set to zero, all prep-stmt must be deallocated.
479 --let $assert_cond= [SHOW GLOBAL STATUS LIKE "Prepared_stmt_count", Value, 1] = 0
480 --source include/assert.inc
481
482 --let $assert_text= Global system status Com_stmt_prepare must be set to zero, all prep-stmt must be deallocated.
483 --let $assert_cond= [SHOW GLOBAL STATUS LIKE "Com_stmt_prepare", Value, 1] = 11
484 --source include/assert.inc
485
486 --let $assert_text= Global system status Com_stmt_execute must be set to zero, all prep-stmt must be deallocated.
487 --let $assert_cond= [SHOW GLOBAL STATUS LIKE "Com_stmt_execute", Value, 1] = 6
488 --source include/assert.inc
489
490 --let $assert_text= Global system status Com_stmt_fetch must be set to zero, all prep-stmt must be deallocated.
491 --let $assert_cond= [SHOW GLOBAL STATUS LIKE "Com_stmt_fetch", Value, 1] = 0
492 --source include/assert.inc
493
494 --let $assert_text= Global system status Com_stmt_close must be set to zero, all prep-stmt must be deallocated.
495 --let $assert_cond= [SHOW GLOBAL STATUS LIKE "Com_stmt_close", Value, 1] = 7
496 --source include/assert.inc
497
498 ## Cleanup
499 --source ../include/xplugin_cleanup.inc