Debian Codegraph @

mysql-test/suite/x/t/cursor_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
14 -->echo
15 -->echo ## 1. Verify prep-stat status variables that are set to zero
16 -->echo ## 2. Execute cursor open
17 -->echo # a. success for open with one parameter
18 -->echo # b. success for open with two parameters (more than needed)
19 -->echo # c. fails when no parameter was supplied, try to fetch using failed cursor_id
20 -->echo # d. fails when using invalid cursor ID, try to fetch using failed cursor_id
21 -->echo ## 3. Close cursor
22 -->echo # a. success when using ID that was already opened
23 -->echo # b. fails when using ID that was already deallocated
24 -->echo # c. fails when using ID that was never opened
25 -->echo ## 4. Mixed tests
26 -->echo # a. open(curID1), close(curID1), open(curID1) - reuse already closes ID
27 -->echo # b. open(curID1), open(curID1) - open implicit closes previous cursor
28 -->echo # c. open(curID1, stmtID1), open(curID2, stmtID1) - curID2 closes curID1 because they use same stmtID
29 -->echo # d. prep(stmtID1), open(curID1, stmtID1), deallocate(stmtID1), failed_fetch(curID1) - closure of prepared-statement, closes the cursor
30 -->echo ## 5. Assert status variables for new session, they should be at zero
31
32
33 -->title = 1. Verify cursor status variables
34
35 -->macro Assert_status_variables_required_by_the_test
36 callmacro Assert_status_variable Mysqlx_prep_prepare 0;
37 callmacro Assert_status_variable Mysqlx_cursor_open 0;
38 callmacro Assert_status_variable Mysqlx_cursor_close 0;
39 callmacro Assert_status_variable Mysqlx_cursor_fetch 0;
40 -->endmacro
41
42 -->callmacro Assert_status_variables_required_by_the_test
43
44 -->title = 2.a. Open cursor with valid stmt
45
46 Mysqlx.Prepare.Prepare {
47 stmt_id: 2
48 stmt: {
49 type: FIND
50 find: {
51 collection { name: "xtable" schema: "xtest" }
52 data_model: TABLE
53 projection {
54 source {
55 type: FUNC_CALL function_call {
56 name {
57 name: "count"
58 }
59 param {
60 type: LITERAL literal {
61 type: V_OCTETS v_octets {
62 value: "*"
63 }
64 }
65 }
66 }
67 }
68 }
69 criteria {
70 type: OPERATOR operator {
71 name: "like"
72 param {
73 type: IDENT identifier {
74 name: 'phrase'
75 }
76 }
77 param {
78 type: PLACEHOLDER position: 0
79 }
80 }
81 }
82 }
83 }
84 }
85 -->recvok
86
87 -->callmacro Assert_status_variable Mysqlx_prep_prepare 1
88
89 Mysqlx.Cursor.Open {
90 cursor_id: 100
91 stmt {
92 type: PREPARE_EXECUTE
93 prepare_execute {
94 stmt_id: 2
95 args {
96 type: SCALAR scalar {
97 type: V_STRING v_string {
98 value: "%"
99 }
100 }
101 }
102 }
103 }
104 }
105 -->recvresult
106
107 -->callmacro Assert_status_variable Mysqlx_cursor_open 1
108
109
110 -->title = 2.b. Open cursor with valid stmt (args more than needed)
111
112 Mysqlx.Cursor.Open {
113 cursor_id: 101
114 stmt {
115 type: PREPARE_EXECUTE
116 prepare_execute {
117 stmt_id: 2
118 args {
119 type: SCALAR scalar {
120 type: V_STRING v_string {
121 value: "Alice%"
122 }
123 }
124 }
125 args {
126 type: SCALAR scalar {
127 type: V_STRING v_string {
128 value: "123"
129 }
130 }
131 }
132 }
133 }
134 }
135 -->recvresult
136
137 show status like 'Mysqlx_cursor_open';
138 -->callmacro Assert_status_variable Mysqlx_cursor_open 2
139
140
141 -->title = 2.c. Fails open cursor with valid stmt (missing arg)
142 Mysqlx.Cursor.Open {
143 cursor_id: 102
144 stmt {
145 type: PREPARE_EXECUTE
146 prepare_execute {
147 stmt_id: 2
148 }
149 }
150 }
151 -->expecterror ER_X_PREPARED_EXECUTE_ARGUMENT_CONSISTENCY
152 -->recvresult
153
154 Mysqlx.Cursor.Fetch {
155 cursor_id: 102
156 }
157 -->expecterror ER_X_BAD_CURSOR_ID
158 -->recvresult
159
160 -->callmacro Assert_status_variable Mysqlx_cursor_open 3
161 -->callmacro Assert_status_variable Mysqlx_cursor_fetch 1
162
163
164 -->title = 2.d. Fails open cursor with invalid stmt
165
166 Mysqlx.Cursor.Open {
167 cursor_id: 103
168 stmt {
169 type: PREPARE_EXECUTE
170 prepare_execute {
171 stmt_id: 200
172 }
173 }
174 }
175 -->expecterror ER_X_BAD_STATEMENT_ID
176 -->recvresult
177
178 Mysqlx.Cursor.Fetch {
179 cursor_id: 103
180 }
181 -->expecterror ER_X_BAD_CURSOR_ID
182 -->recvresult
183
184 -->callmacro Assert_status_variable Mysqlx_cursor_open 4
185
186
187 -->title = 3.a. Close already opened cursor
188
189 Mysqlx.Prepare.Prepare {
190 stmt_id: 1
191 stmt: {
192 type: STMT
193 stmt_execute: {
194 stmt: "select 1"
195 }
196 }
197 }
198 -->recvok
199
200 Mysqlx.Cursor.Open {
201 cursor_id: 100
202 stmt {
203 type: PREPARE_EXECUTE
204 prepare_execute {
205 stmt_id: 1
206 }
207 }
208 }
209 -->recvresult
210
211 Mysqlx.Cursor.Close {
212 cursor_id: 100
213 }
214 -->recvok
215
216 -->callmacro Assert_status_variable Mysqlx_cursor_close 1
217
218 -->title = 3.b. Fails close cursor (bad id) / try to close second time
219
220 Mysqlx.Cursor.Close {
221 cursor_id: 100
222 }
223 -->recverror ER_X_BAD_CURSOR_ID
224
225 -->callmacro Assert_status_variable Mysqlx_cursor_close 2
226
227
228 -->title = 3.c. Fails close cursor (bad id/never used)
229
230 Mysqlx.Cursor.Close {
231 cursor_id: 10000
232 }
233 -->recverror ER_X_BAD_CURSOR_ID
234
235 -->callmacro Assert_status_variable Mysqlx_cursor_close 3
236
237 -->title = 4.a. Reuse deallocated cursor ID
238
239 Mysqlx.Prepare.Prepare {
240 stmt_id: 3
241 stmt: {
242 type: STMT
243 stmt_execute: {
244 stmt: "select 3"
245 }
246 }
247 }
248 -->recvok
249
250 Mysqlx.Prepare.Prepare {
251 stmt_id: 4
252 stmt: {
253 type: STMT
254 stmt_execute: {
255 stmt: "select 4"
256 }
257 }
258 }
259 -->recvok
260
261 Mysqlx.Cursor.Open {
262 cursor_id: 104
263 stmt {
264 type: PREPARE_EXECUTE
265 prepare_execute {
266 stmt_id: 3
267 }
268 }
269 }
270 -->recvresult
271
272 Mysqlx.Cursor.Close {
273 cursor_id: 104
274 }
275 -->recvok
276
277 Mysqlx.Cursor.Open {
278 cursor_id: 104
279 stmt {
280 type: PREPARE_EXECUTE
281 prepare_execute {
282 stmt_id: 4
283 }
284 }
285 }
286 -->recvresult
287
288
289 -->title = 4.b. Reuse ID without direct deallocation
290
291 Mysqlx.Prepare.Prepare {
292 stmt_id: 5
293 stmt: {
294 type: STMT
295 stmt_execute: {
296 stmt: "select 5 as col1"
297 }
298 }
299 }
300 -->recvok
301
302 Mysqlx.Cursor.Open {
303 cursor_id: 105
304 stmt {
305 type: PREPARE_EXECUTE
306 prepare_execute {
307 stmt_id: 5
308 }
309 }
310 }
311 -->recvresult
312
313 Mysqlx.Cursor.Open {
314 cursor_id: 105
315 stmt {
316 type: PREPARE_EXECUTE
317 prepare_execute {
318 stmt_id: 5
319 }
320 }
321 }
322 -->recvresult
323
324
325 -->title = 4.c. Same stmt can't be opened by cursor twice
326
327 Mysqlx.Prepare.Prepare {
328 stmt_id: 6
329 stmt: {
330 type: STMT
331 stmt_execute: {
332 stmt: "select 6 as col1"
333 }
334 }
335 }
336 -->recvok
337
338 Mysqlx.Cursor.Open {
339 cursor_id: 106
340 stmt {
341 type: PREPARE_EXECUTE
342 prepare_execute {
343 stmt_id: 6
344 }
345 }
346 }
347 -->recvresult
348
349
350 Mysqlx.Cursor.Open {
351 cursor_id: 107
352 stmt {
353 type: PREPARE_EXECUTE
354 prepare_execute {
355 stmt_id: 6
356 }
357 }
358 }
359 -->recvresult
360
361 Mysqlx.Cursor.Fetch {
362 cursor_id: 107
363 }
364 -->recvresult
365
366
367 Mysqlx.Cursor.Fetch {
368 cursor_id: 106
369 }
370 -->expecterror ER_X_BAD_CURSOR_ID
371 -->recvresult
372
373 -->callmacro Assert_status_variable Mysqlx_cursor_fetch 4
374
375 -->title = 4.d. Closure of prepared-statement, closes the cursor
376
377 Mysqlx.Prepare.Prepare {
378 stmt_id: 7
379 stmt: {
380 type: STMT
381 stmt_execute: {
382 stmt: "select 7 as col1"
383 }
384 }
385 }
386 -->recvok
387
388 Mysqlx.Cursor.Open {
389 cursor_id: 107
390 stmt {
391 type: PREPARE_EXECUTE
392 prepare_execute {
393 stmt_id: 7
394 }
395 }
396 }
397 -->recvresult
398
399 Mysqlx.Prepare.Deallocate {
400 stmt_id: 7
401 }
402
403 Mysqlx.Cursor.Fetch {
404 cursor_id: 107
405 }
406 -->expecterror ER_X_BAD_CURSOR_ID
407 -->recvresult
408
409
410 -->title = 5. Assert status variables for new session, they should be at zero
411 -->newsession s1 x_root
412 -->callmacro Assert_status_variables_required_by_the_test
413
414 EOF
415
416
417 CREATE SCHEMA IF NOT EXISTS xtest;
418 USE xtest;
419 CREATE TABLE xtable (phrase VARCHAR(30));
420 INSERT INTO xtable (phrase) VALUES ('Alice has a cat');
421 INSERT INTO xtable (phrase) VALUES ('Bob has a dog');
422
423 exec $MYSQLXTEST
424 -ux_root --password=''
425 --file=$MYSQL_TMP_DIR/prep_stmt_sundries.xpl 2>&1;
426
427 # Ensure that all connections are closed, after that we can proceed
428 # with Prepared_stmt_count status variables assertion
429 #
430 let $wait_for_status_variable = 'Mysqlx_connections_closed',
431 'Mysqlx_sessions';
432 let $wait_for_status_value = 2,0;
433 source ../include/wait_for_status_variables.inc;
434
435 --echo
436 --echo ## Assert global variables
437 --let $assert_text= Global plugin variable Mysqlx_cursor_open must match number of send Mysqlx.Cursor.Open messages
438 --let $assert_cond= [SHOW STATUS LIKE "Mysqlx_cursor_open", Value, 1] = 12
439 --source include/assert.inc
440
441 --let $assert_text= Global plugin variable Mysqlx_cursor_close must match number of send Mysqlx.Cursor.Close messages
442 --let $assert_cond= [SHOW STATUS LIKE "Mysqlx_cursor_close", Value, 1] = 4
443 --source include/assert.inc
444
445 --let $assert_text= Global plugin variable Mysqlx_cursor_fetch must match number of send Mysqlx.Cursor.Fetch messages
446 --let $assert_cond= [SHOW STATUS LIKE "Mysqlx_cursor_fetch", Value, 1] = 5
447 --source include/assert.inc
448
449 --let $assert_text= Global system status Prepared_stmt_count must be set to zero, all prep-stmt must be deallocated.
450 --let $assert_cond= [SHOW STATUS LIKE "Prepared_stmt_count", Value, 1] = 0
451 --source include/assert.inc
452
453 ## Cleanup
454 --source ../include/xplugin_cleanup.inc