Debian Codegraph @

mysql-test/suite/x/r/multiple_resultsets.result

raw
1 SET @@sql_mode = "";
2
3
4 ## I. Validate direct SP calls that generate multiple-results and uses out-params
5 #
6 # 1. Assert that result-file doesn't contain out-params inside the resultsets.
7 # 2. Assert that out-params set their assigned user variables
8
9 ## II. Validate SQL prepared SP calls that generate multiple-results and uses out-params
10 #
11 # 1. Assert that result-file doesn't contain out-params inside the resultsets.
12 # 2. Assert that out-params set their assigned user variables
13
14 ## III. Validate Mysqlx prepared SP calls that generate multiple-results and uses out-params
15 #
16 # 1. Assert that result-file contain out-params inside the resultsets.
17 # 2. Assert that out-params don't set their assigned user variables
18
19
20 #
21 # I.
22 #
23 ## Setup tests: tables and stored procedures
24 [Execute `empty`()]
25
26 0 rows affected
27 [Execute one_results_set()]
28 c1t1 c2t1
29 1 ONE
30 2 TWO
31 3 THREE
32
33 0 rows affected
34 [Execute three_results_sets()]
35 c1t1 c2t1
36 1 ONE
37 2 TWO
38 3 THREE
39 c1t2
40 2006-07-03 00:00:00
41 2013-06-15 00:00:00
42 2012-11-24 00:00:00
43 1979-11-24 00:00:00
44 c1t1 c2t1
45 1 ONE
46 2 TWO
47 3 THREE
48
49 0 rows affected
50 [Reseting test variables]
51 RUN SET @res=-1
52 RUN SET @min_val=-1
53 RUN SET @max_val=-1
54 RUN SET @in_val=5
55 [Execute out_param_no_select(@in_val,@res)]
56
57 0 rows affected
58 Verify [User variable "res" needs to have a value of 10]
59 [Execute out_params_with_select(@min_val,@max_val)]
60 c1t1 c2t1
61 1 ONE
62 2 TWO
63 3 THREE
64
65 0 rows affected
66 Verify [User variable "min_val" needs to have a value of 1979-11-24 00:00:00]
67 Verify [User variable "max_val" needs to have a value of 2013-06-15 00:00:00]
68 [Execute update_no_select()]
69
70 2 rows affected
71 [Execute update_and_select()]
72 c1t1 c2t1
73 1 ONE
74 5 TWO
75 6 THREE
76
77 0 rows affected
78 [Execute update_and_out_param(@max_val)]
79
80 1 rows affected
81 Verify [User variable "max_val" needs to have a value of 6]
82 [Execute update_and_out_param_and_select(@max_val)]
83 AVG(c1t1)
84 6.6667
85
86 0 rows affected
87 Verify [User variable "max_val" needs to have a value of 12]
88 [Execute insert_no_select()]
89
90 3 rows affected
91 [Execute insert_and_select()]
92 c1t1 c2t1
93 3 ONE
94 5 TWO
95 12 THREE
96 11 s11
97 12 s12
98 13 s13
99 111 s111
100 112 s112
101 13 s113
102
103 0 rows affected
104 [Execute insert_and_out_param(@max_val)]
105
106 1 rows affected
107 Verify [User variable "max_val" needs to have a value of 9999]
108 [Execute insert_and_out_param_and_select(@max_val)]
109 MIN(c1t1)
110 3
111
112 0 rows affected
113 Verify [User variable "max_val" needs to have a value of 19999]
114 [Execute error()]
115 Got expected error: Table 'xtest.tab_not_existing' doesn't exist (code 1146)
116 [Execute select_and_error()]
117 c1t1 c2t1
118 3 ONE
119 5 TWO
120 12 THREE
121 11 s11
122 12 s12
123 13 s13
124 111 s111
125 112 s112
126 13 s113
127 9999 BIG ONE
128 19999 BIGER ONE
129 Got expected error: Table 'xtest.tab_not_existing' doesn't exist (code 1146)
130 [Reseting test variables]
131 RUN SET @res=-1
132 RUN SET @min_val=-1
133 RUN SET @max_val=-1
134 RUN SET @in_val=5
135 [Execute out_param_and_error(@min_val)]
136 Got expected error: Table 'xtest.tab_not_existing' doesn't exist (code 1146)
137 Verify [User variable "min_val" needs to have a value of -1]
138 [Execute select_out_param_and_error(@min_val)]
139 COUNT(c1t1)
140 11
141 Got expected error: Table 'xtest.tab_not_existing' doesn't exist (code 1146)
142 Verify [User variable "min_val" needs to have a value of -1]
143 [Execute with_warning()]
144
145 0 rows affected
146 Warnings generated:
147 WARNING | 1264 | Out of range value for column 'tiny' at row 1
148 Got expected warning: 1264
149 [Execute select_and_warning()]
150 c1t2
151 2006-07-03 00:00:00
152 2013-06-15 00:00:00
153 2012-11-24 00:00:00
154 1979-11-24 00:00:00
155
156 0 rows affected
157 Warnings generated:
158 WARNING | 1264 | Out of range value for column 'tiny' at row 1
159 Got expected warning: 1264
160 [Execute out_param_and_warning(@vavg)]
161
162 1 rows affected
163 Warnings generated:
164 WARNING | 1264 | Out of range value for column 'tiny' at row 1
165 Got expected warning: 1264
166 RUN SELECT @vavg
167 @vavg
168 2753.636474609375
169 0 rows affected
170 [Execute select_out_param_and_warning(@min_val)]
171 COUNT(c1t1)
172 11
173
174 0 rows affected
175 Warnings generated:
176 WARNING | 1265 | Data truncated for column 'c' at row 1
177 Got expected warning: 1265
178 Verify [User variable "min_val" needs to have a value of 3]
179 Mysqlx.Ok {
180 msg: "bye!"
181 }
182 ok
183
184 #
185 # II.
186 DROP SCHEMA IF EXISTS xtest;
187 #
188 ## Setup tests: tables and stored procedures
189 [Prepare and execute `empty`()]
190
191 0 rows affected
192 [Prepare and execute one_results_set()]
193 c1t1 c2t1
194 1 ONE
195 2 TWO
196 3 THREE
197
198 0 rows affected
199 [Prepare and execute three_results_sets()]
200 c1t1 c2t1
201 1 ONE
202 2 TWO
203 3 THREE
204 c1t2
205 2006-07-03 00:00:00
206 2013-06-15 00:00:00
207 2012-11-24 00:00:00
208 1979-11-24 00:00:00
209 c1t1 c2t1
210 1 ONE
211 2 TWO
212 3 THREE
213
214 0 rows affected
215 [Reseting test variables]
216 RUN SET @res=-1
217 RUN SET @min_val=-1
218 RUN SET @max_val=-1
219 RUN SET @in_val=5
220 [Prepare and execute out_param_no_select(@in_val,?)]
221
222 0 rows affected
223 Verify [User variable "res" needs to have a value of 10]
224 [Prepare and execute out_params_with_select(?,?)]
225 c1t1 c2t1
226 1 ONE
227 2 TWO
228 3 THREE
229
230 0 rows affected
231 Verify [User variable "min_val" needs to have a value of 1979-11-24 00:00:00]
232 Verify [User variable "max_val" needs to have a value of 2013-06-15 00:00:00]
233 [Prepare and execute update_no_select()]
234
235 2 rows affected
236 [Prepare and execute update_and_select()]
237 c1t1 c2t1
238 1 ONE
239 5 TWO
240 6 THREE
241
242 0 rows affected
243 [Prepare and execute update_and_out_param(?)]
244
245 1 rows affected
246 Verify [User variable "max_val" needs to have a value of 6]
247 [Prepare and execute update_and_out_param_and_select(?)]
248 AVG(c1t1)
249 6.6667
250
251 0 rows affected
252 Verify [User variable "max_val" needs to have a value of 12]
253 [Prepare and execute insert_no_select()]
254
255 3 rows affected
256 [Prepare and execute insert_and_select()]
257 c1t1 c2t1
258 3 ONE
259 5 TWO
260 12 THREE
261 11 s11
262 12 s12
263 13 s13
264 111 s111
265 112 s112
266 13 s113
267
268 0 rows affected
269 [Prepare and execute insert_and_out_param(?)]
270
271 1 rows affected
272 Verify [User variable "max_val" needs to have a value of 9999]
273 [Prepare and execute insert_and_out_param_and_select(?)]
274 MIN(c1t1)
275 3
276
277 0 rows affected
278 Verify [User variable "max_val" needs to have a value of 19999]
279 [Prepare and execute error()]
280 Got expected error: Table 'xtest.tab_not_existing' doesn't exist (code 1146)
281 [Prepare and execute select_and_error()]
282 c1t1 c2t1
283 3 ONE
284 5 TWO
285 12 THREE
286 11 s11
287 12 s12
288 13 s13
289 111 s111
290 112 s112
291 13 s113
292 9999 BIG ONE
293 19999 BIGER ONE
294 Got expected error: Table 'xtest.tab_not_existing' doesn't exist (code 1146)
295 [Reseting test variables]
296 RUN SET @res=-1
297 RUN SET @min_val=-1
298 RUN SET @max_val=-1
299 RUN SET @in_val=5
300 [Prepare and execute out_param_and_error(?)]
301 Got expected error: Table 'xtest.tab_not_existing' doesn't exist (code 1146)
302 Verify [User variable "min_val" needs to have a value of -1]
303 [Prepare and execute select_out_param_and_error(?)]
304 COUNT(c1t1)
305 11
306 Got expected error: Table 'xtest.tab_not_existing' doesn't exist (code 1146)
307 Verify [User variable "min_val" needs to have a value of -1]
308 [Prepare and execute with_warning()]
309
310 0 rows affected
311 Warnings generated:
312 WARNING | 1264 | Out of range value for column 'tiny' at row 1
313 Got expected warning: 1264
314 [Prepare and execute select_and_warning()]
315 c1t2
316 2006-07-03 00:00:00
317 2013-06-15 00:00:00
318 2012-11-24 00:00:00
319 1979-11-24 00:00:00
320
321 0 rows affected
322 Warnings generated:
323 WARNING | 1264 | Out of range value for column 'tiny' at row 1
324 Got expected warning: 1264
325 [Prepare and execute out_param_and_warning(?)]
326
327 1 rows affected
328 Warnings generated:
329 WARNING | 1264 | Out of range value for column 'tiny' at row 1
330 Got expected warning: 1264
331 RUN SELECT @vavg
332 @vavg
333 2753.636474609375
334 0 rows affected
335 [Prepare and execute select_out_param_and_warning(@min_val)]
336 COUNT(c1t1)
337 11
338
339 0 rows affected
340 Warnings generated:
341 WARNING | 1265 | Data truncated for column 'c' at row 1
342 Got expected warning: 1265
343 Verify [User variable "min_val" needs to have a value of 3]
344 Mysqlx.Ok {
345 msg: "bye!"
346 }
347 ok
348
349 #
350 # III.
351 DROP SCHEMA IF EXISTS xtest;
352 #
353 ## Setup tests: tables and stored procedures
354 [Prepare and execute `empty`()]
355
356 0 rows affected
357 [Prepare and execute one_results_set()]
358 c1t1 c2t1
359 1 ONE
360 2 TWO
361 3 THREE
362
363 0 rows affected
364 [Prepare and execute three_results_sets()]
365 c1t1 c2t1
366 1 ONE
367 2 TWO
368 3 THREE
369 c1t2
370 2006-07-03 00:00:00
371 2013-06-15 00:00:00
372 2012-11-24 00:00:00
373 1979-11-24 00:00:00
374 c1t1 c2t1
375 1 ONE
376 2 TWO
377 3 THREE
378
379 0 rows affected
380 [Reseting test variables]
381 RUN SET @res=-1
382 RUN SET @min_val=-1
383 RUN SET @max_val=-1
384 RUN SET @in_val=5
385 [Prepare and execute out_param_no_select(@in_val,?)]
386
387 res
388 10
389 0 rows affected
390 Verify [User variable "res" needs to have a value of -1]
391 [Prepare and execute out_params_with_select(?,?)]
392 c1t1 c2t1
393 1 ONE
394 2 TWO
395 3 THREE
396 min_val max_val
397 1979-11-24 00:00:00 2013-06-15 00:00:00
398 0 rows affected
399 Verify [User variable "min_val" needs to have a value of -1]
400 Verify [User variable "max_val" needs to have a value of -1]
401 [Prepare and execute update_no_select()]
402
403 2 rows affected
404 [Prepare and execute update_and_select()]
405 c1t1 c2t1
406 1 ONE
407 5 TWO
408 6 THREE
409
410 0 rows affected
411 [Prepare and execute update_and_out_param(?)]
412
413 max_val
414 6
415 1 rows affected
416 Verify [User variable "max_val" needs to have a value of -1]
417 [Prepare and execute update_and_out_param_and_select(?)]
418 AVG(c1t1)
419 6.6667
420 max_val
421 12
422 0 rows affected
423 Verify [User variable "max_val" needs to have a value of -1]
424 [Prepare and execute insert_no_select()]
425
426 3 rows affected
427 [Prepare and execute insert_and_select()]
428 c1t1 c2t1
429 3 ONE
430 5 TWO
431 12 THREE
432 11 s11
433 12 s12
434 13 s13
435 111 s111
436 112 s112
437 13 s113
438
439 0 rows affected
440 [Prepare and execute insert_and_out_param(?)]
441
442 max_val
443 9999
444 1 rows affected
445 Verify [User variable "max_val" needs to have a value of -1]
446 [Prepare and execute insert_and_out_param_and_select(?)]
447 MIN(c1t1)
448 3
449 max_val
450 19999
451 0 rows affected
452 Verify [User variable "max_val" needs to have a value of -1]
453 [Prepare and execute error()]
454 Got expected error: Table 'xtest.tab_not_existing' doesn't exist (code 1146)
455 [Prepare and execute select_and_error()]
456 c1t1 c2t1
457 3 ONE
458 5 TWO
459 12 THREE
460 11 s11
461 12 s12
462 13 s13
463 111 s111
464 112 s112
465 13 s113
466 9999 BIG ONE
467 19999 BIGER ONE
468 Got expected error: Table 'xtest.tab_not_existing' doesn't exist (code 1146)
469 [Reseting test variables]
470 RUN SET @res=-1
471 RUN SET @min_val=-1
472 RUN SET @max_val=-1
473 RUN SET @in_val=5
474 [Prepare and execute out_param_and_error(?)]
475 Got expected error: Table 'xtest.tab_not_existing' doesn't exist (code 1146)
476 Verify [User variable "min_val" needs to have a value of -1]
477 [Prepare and execute select_out_param_and_error(?)]
478 COUNT(c1t1)
479 11
480 Got expected error: Table 'xtest.tab_not_existing' doesn't exist (code 1146)
481 Verify [User variable "min_val" needs to have a value of -1]
482 [Prepare and execute with_warning()]
483
484 0 rows affected
485 Warnings generated:
486 WARNING | 1264 | Out of range value for column 'tiny' at row 1
487 Got expected warning: 1264
488 [Prepare and execute select_and_warning()]
489 c1t2
490 2006-07-03 00:00:00
491 2013-06-15 00:00:00
492 2012-11-24 00:00:00
493 1979-11-24 00:00:00
494
495 0 rows affected
496 Warnings generated:
497 WARNING | 1264 | Out of range value for column 'tiny' at row 1
498 Got expected warning: 1264
499 [Prepare and execute out_param_and_warning(?)]
500
501 vavg
502 2753.64
503 1 rows affected
504 Warnings generated:
505 WARNING | 1264 | Out of range value for column 'tiny' at row 1
506 Got expected warning: 1264
507 RUN SELECT @vavg
508 @vavg
509 null
510 0 rows affected
511 [Prepare and execute select_out_param_and_warning(@min_val)]
512 COUNT(c1t1)
513 11
514
515 0 rows affected
516 Warnings generated:
517 WARNING | 1265 | Data truncated for column 'c' at row 1
518 Got expected warning: 1265
519 Verify [User variable "min_val" needs to have a value of 3]
520 Mysqlx.Ok {
521 msg: "bye!"
522 }
523 ok
524 DROP SCHEMA IF EXISTS xtest;