Debian Codegraph @

mysql-test/suite/x/include/test_flow_resultset_cursors.inc

raw
1
2 --write_file $MYSQL_TMP_DIR/resultset.xpl
3 -->import assert_messages.macro
4 -->import assert_notices.macro
5
6 -->varlet %ID% 1
7 -->macro Prepare_and_cursor_open_wo_res %OTHER_OPTIONS% %SQL%
8 -->varinc %ID% 1
9 -->noquery_result
10 -->quiet
11 -->echo [PREPARE - %SQL%]
12 Mysqlx.Prepare.Prepare {
13 stmt_id: %ID%
14 stmt: {
15 type: STMT
16 stmt_execute: {
17 stmt: "%SQL%"
18 }
19 }
20 }
21 -->recvok
22 -->echo [EXECUTE]
23 Mysqlx.Cursor.Open {
24 cursor_id: %ID%
25 stmt {
26 type: PREPARE_EXECUTE
27 prepare_execute {
28 stmt_id: %ID%
29 }
30 }
31 %OTHER_OPTIONS%
32 }
33 -->query_result
34 -->noquiet
35 -->endmacro
36
37
38 -->macro Cursor_fetch_wo_res %OTHER_OPTIONS%
39 -->noquery_result
40 -->quiet
41 -->echo [FETCH]
42 Mysqlx.Cursor.Fetch {
43 cursor_id: %ID%
44 %OTHER_OPTIONS%
45 }
46 -->query_result
47 -->noquiet
48 -->endmacro
49
50 ##
51 ## Purpose of this test is:
52 #
53 # * validating that resultsets are in align to the UML flow:
54 #
55 # ...
56 # loop has more resultsets or not at end of fetch
57 # group resultset
58 # loop has more columns
59 # server --> client: ColumnMetaData
60 # end
61 # loop has more rows
62 # server --> client: Row
63 # end
64 # end
65 # alt has more resultsets
66 # server --> client: FetchDoneMoreResultsets
67 # end
68 # end
69 # loop has more OUT-paramsets or not at end of fetch
70 # server --> client: FetchDoneMoreOutParams
71 # group resultset
72 # loop has more columns
73 # server --> client: ColumnMetaData
74 # end
75 # loop has more rows
76 # server --> client: Row
77 # end
78 # end
79 # end
80 # alt at end of all resultsets
81 # server --> client: FetchDone
82 # else cursor is opened
83 # server --> client: FetchSuspended
84 # end
85 # ...
86 #
87 # * expecting following notices for SQL prepared-statement:
88 #
89 # |Notice\Stmt |Select |Insert |Update |Delete |
90 # |-----------------------|-------|-------|-------|-------|
91 # |ROWS_AFFECTED |X(0) |X |X |X |
92 # |PRODUCED_MESSAGE | | |X | |
93 # |GENERATED_INSERT_ID | |X(PK) | | |
94 # |GENERATED_DOCUMENT_IDS | | | | |
95 #
96 # * cover fetching responses of static SQL and dynamic SQL:
97 #
98 # Resultsets generated by dynamic SQL (for example procedures) can't
99 # be buffered by cursors and are executed right away.
100 # Because of this issue, Mysqlx.Cursor.Open may return a resultset instead
101 # of Mysqlx.Ok.
102 #
103 -->echo
104 -->echo
105 -->echo ## I. Validate non dynamic SQL statement where Cursor.Fetch is not needed
106 -->echo #
107 -->echo # 1. Assert empty resultset, dont get any rows at Cursor.Open.
108 -->echo # 2. Assert resultset that has less rows than requested by Cursor.Open.
109 -->echo
110 -->echo ## II. Validate non dynamic SQL statement where Cursor.Fetch is needed to get all data
111 -->echo #
112 -->echo # 1. Assert empty resultset, still try to get at last one row at open
113 -->echo # 2. Assert resultset that has same amount of rows as requested
114 -->echo # 3. Assert resultset that has rows grater by one than requested
115 -->echo # 4. Assert resultset that has two times the amount of rows as requested
116 -->echo # 5. Assert resultset by fetching the resultset one by one row
117 -->echo # 6. Assert resultset by fetching the resultset and warnings generated by it
118 -->echo
119 -->echo ## III. Validate dynamic SQL statement
120 -->echo #
121 -->echo # 1. Assert that cursor open, fetches whole resultset
122 -->echo # a. fetch_rows in Cursor.Open is not set
123 -->echo # b. fetch_rows in Cursor.Open is set (value doesn't matter).
124 -->echo # 2. Assert that cursor open, fetches multiple resultsets
125 -->echo # 3. Assert that cursor open, fetches multiple resultsets with warnings
126 -->echo
127 -->echo ## IV. Validate notices
128 -->echo #
129 -->echo # 1. Insert stmt
130 -->echo # a. Table with primary key (expect notice GENERATED_INSERT_ID)
131 -->echo # b. Table without primary key (must not be generate GENERATED_INSERT_ID)
132 -->echo # 2. Delete stmt
133 -->echo # 3. Update stmt
134 -->echo
135
136
137 -->echo
138 -->echo #
139 -->echo # I.1
140 callmacro Prepare_and_cursor_open_wo_res
141 fetch_rows: 1
142 SELECT * FROM xtable LIMIT 0;
143 callmacro Assert_metadata BYTES "phrase";
144 callmacro Assert_metadata SINT "prio";
145 callmacro Assert_message Mysqlx.Resultset.FetchDone;
146 callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0;
147 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
148
149
150 -->echo
151 -->echo #
152 -->echo # I.2
153 callmacro Prepare_and_cursor_open_wo_res
154 fetch_rows: 1000
155 SELECT * FROM xtable;
156 callmacro Assert_metadata BYTES "phrase";
157 callmacro Assert_metadata SINT "prio";
158 callmacro Assert_rows 5;
159 callmacro Assert_message Mysqlx.Resultset.FetchDone;
160 callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0;
161 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
162
163
164 -->echo
165 -->echo #
166 -->echo # II.1
167 callmacro Prepare_and_cursor_open_wo_res
168 SELECT * FROM xtable LIMIT 0;
169 callmacro Assert_metadata BYTES "phrase";
170 callmacro Assert_metadata SINT "prio";
171 callmacro Assert_message Mysqlx.Resultset.FetchSuspended;
172 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
173
174 callmacro Cursor_fetch_wo_res fetch_rows: 1000;
175 callmacro Assert_message Mysqlx.Resultset.FetchDone;
176 callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0;
177 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
178
179
180 -->echo
181 -->echo #
182 -->echo # II.2
183 callmacro Prepare_and_cursor_open_wo_res
184 fetch_rows: 5
185 SELECT * FROM xtable LIMIT 5;
186 callmacro Assert_metadata BYTES "phrase";
187 callmacro Assert_metadata SINT "prio";
188 callmacro Assert_rows 5;
189 callmacro Assert_message Mysqlx.Resultset.FetchSuspended;
190 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
191
192 callmacro Cursor_fetch_wo_res fetch_rows: 1000;
193 callmacro Assert_message Mysqlx.Resultset.FetchDone;
194 callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0;
195 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
196
197
198 -->echo
199 -->echo #
200 -->echo # II.3
201 callmacro Prepare_and_cursor_open_wo_res
202 fetch_rows: 4
203 SELECT * FROM xtable LIMIT 5;
204 callmacro Assert_metadata BYTES "phrase";
205 callmacro Assert_metadata SINT "prio";
206 callmacro Assert_rows 4;
207 callmacro Assert_message Mysqlx.Resultset.FetchSuspended;
208 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
209
210 callmacro Cursor_fetch_wo_res fetch_rows: 1000;
211 callmacro Assert_rows 1;
212 callmacro Assert_message Mysqlx.Resultset.FetchDone;
213 callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0;
214 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
215
216
217 -->echo
218 -->echo #
219 -->echo # II.4
220 callmacro Prepare_and_cursor_open_wo_res
221 fetch_rows: 2
222 SELECT * FROM xtable LIMIT 4;
223 callmacro Assert_metadata BYTES "phrase";
224 callmacro Assert_metadata SINT "prio";
225 callmacro Assert_rows 2;
226 callmacro Assert_message Mysqlx.Resultset.FetchSuspended;
227 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
228
229 callmacro Cursor_fetch_wo_res fetch_rows: 1000;
230 callmacro Assert_rows 2;
231 callmacro Assert_message Mysqlx.Resultset.FetchDone;
232 callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0;
233 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
234
235
236 -->echo
237 -->echo #
238 -->echo # II.5
239 callmacro Prepare_and_cursor_open_wo_res
240 fetch_rows: 1
241 SELECT * FROM xtable LIMIT 3;
242 callmacro Assert_metadata BYTES "phrase";
243 callmacro Assert_metadata SINT "prio";
244 callmacro Assert_rows 1;
245 callmacro Assert_message Mysqlx.Resultset.FetchSuspended;
246 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
247
248 callmacro Cursor_fetch_wo_res fetch_rows: 1;
249 callmacro Assert_rows 1;
250 callmacro Assert_message Mysqlx.Resultset.FetchSuspended;
251 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
252
253 callmacro Cursor_fetch_wo_res fetch_rows: 1;
254 callmacro Assert_rows 1;
255 callmacro Assert_message Mysqlx.Resultset.FetchSuspended;
256 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
257
258 callmacro Cursor_fetch_wo_res fetch_rows: 1;
259 callmacro Assert_message Mysqlx.Resultset.FetchDone;
260 callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0;
261 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
262
263
264 -->echo
265 -->echo #
266 -->echo # II.6
267 callmacro Prepare_and_cursor_open_wo_res
268
269 SELECT 1/prio as col1 FROM xtable;
270 -->callmacro Assert_metadata DECIMAL "col1"
271 callmacro Assert_message Mysqlx.Resultset.FetchSuspended;
272 ## ER_DIVISION_BY_ZERO == 1365
273 callmacro Assert_notice_warning WARNING 1365;
274 callmacro Assert_notice_warning WARNING 1365;
275 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
276
277 callmacro Cursor_fetch_wo_res fetch_rows: 2;
278 callmacro Assert_rows 2;
279 callmacro Assert_message Mysqlx.Resultset.FetchSuspended;
280 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
281
282 callmacro Cursor_fetch_wo_res fetch_rows: 4;
283 callmacro Assert_rows 3;
284 callmacro Assert_message Mysqlx.Resultset.FetchDone;
285 callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0;
286 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
287
288
289 -->echo
290 -->echo #
291 -->echo # III.1.a
292 callmacro Prepare_and_cursor_open_wo_res
293
294 CALL one_resultsets();
295 callmacro Assert_metadata BYTES "phrase";
296 callmacro Assert_rows 3;
297 callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets;
298 callmacro Assert_message Mysqlx.Resultset.FetchDone;
299 callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0;
300 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
301
302
303 -->echo
304 -->echo #
305 -->echo # III.1.b
306 callmacro Prepare_and_cursor_open_wo_res
307 fetch_rows: 1
308 CALL one_resultsets();
309 callmacro Assert_metadata BYTES "phrase";
310 callmacro Assert_rows 3;
311 callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets;
312 callmacro Assert_message Mysqlx.Resultset.FetchDone;
313 callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0;
314 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
315
316
317 -->echo
318 -->echo #
319 -->echo # III.2
320 callmacro Prepare_and_cursor_open_wo_res
321
322 CALL two_resultsets();
323 callmacro Assert_metadata BYTES "phrase";
324 callmacro Assert_rows 3;
325 callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets;
326 callmacro Assert_metadata SINT "prio";
327 callmacro Assert_rows 2;
328 callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets;
329 callmacro Assert_message Mysqlx.Resultset.FetchDone;
330 callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0;
331 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
332
333
334 -->echo
335 -->echo #
336 -->echo # III.3
337 callmacro Prepare_and_cursor_open_wo_res
338
339 CALL two_resultsets_with_errors;
340 -->callmacro Assert_metadata DECIMAL "col1"
341 -->callmacro Assert_rows 5
342 -->callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets
343 -->callmacro Assert_metadata DECIMAL "col1"
344 -->callmacro Assert_rows 5
345 -->callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets
346 -->callmacro Assert_message Mysqlx.Resultset.FetchDone
347 ## ER_DIVISION_BY_ZERO == 1365
348 -->callmacro Assert_notice_warning WARNING 1365
349 -->callmacro Assert_notice_warning WARNING 1365
350 -->callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0
351 -->callmacro Assert_message Mysqlx.Sql.StmtExecuteOk
352
353
354 -->echo
355 -->echo #
356 -->echo # IV.1.a
357 callmacro Prepare_and_cursor_open_wo_res
358
359 INSERT INTO xtest.xtable_with_pk(description) VALUE('MyCustomRow');
360 callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:1;
361 callmacro Assert_notice_session_state GENERATED_INSERT_ID v_unsigned_int: 2;
362 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
363
364
365 -->echo
366 -->echo #
367 -->echo # IV.1.b
368 callmacro Prepare_and_cursor_open_wo_res
369
370 INSERT INTO xtest.xtable(phrase, prio) VALUE('MyCustomRow', 100);
371 callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:1;
372 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
373
374
375 -->echo
376 -->echo #
377 -->echo # IV.2
378 callmacro Prepare_and_cursor_open_wo_res
379
380 DELETE FROM xtest.xtable WHERE prio=100;
381 callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:1;
382 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
383
384
385 -->echo
386 -->echo #
387 -->echo # IV.3
388 callmacro Prepare_and_cursor_open_wo_res
389
390 UPDATE xtest.xtable SET phrase='New';
391 callmacro Assert_notice_session_state PRODUCED_MESSAGE type: V_STRING;
392 callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:5;
393 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
394
395 EOF
396
397
398 CREATE SCHEMA IF NOT EXISTS xtest;
399 USE xtest;
400
401 DELIMITER //;
402
403 # Data are similar to sample_tables.inc,
404 # still prio in decremented by one
405 CREATE PROCEDURE recreate_tables()
406 BEGIN
407 CREATE TABLE IF NOT EXISTS xtable (phrase VARCHAR(30), prio INTEGER);
408 TRUNCATE TABLE xtable;
409 INSERT INTO xtable (phrase, prio) VALUES ('Alice has a cat', 0);
410 INSERT INTO xtable (phrase, prio) VALUES ('Bob has a dog 1', 1);
411 INSERT INTO xtable (phrase, prio) VALUES ('Peppa has a teddy', 0);
412 INSERT INTO xtable (phrase, prio) VALUES ('Bob has a dog 2', 2);
413 INSERT INTO xtable (phrase, prio) VALUES ('Row to delete', 3);
414
415 CREATE TABLE IF NOT EXISTS xtable_with_pk (p_id INTEGER AUTO_INCREMENT PRIMARY KEY, description varchar(40));
416 TRUNCATE TABLE xtable_with_pk;
417 INSERT INTO xtable_with_pk (description) VALUES ('MyRow');
418 END //
419
420 CREATE PROCEDURE one_resultsets()
421 BEGIN
422 SELECT phrase FROM xtable limit 3;
423 END //
424
425 CREATE PROCEDURE two_resultsets()
426 BEGIN
427 SELECT phrase FROM xtable limit 3;
428 SELECT prio FROM xtable limit 2;
429 END //
430
431 CREATE PROCEDURE two_resultsets_with_errors()
432 BEGIN
433 SELECT 1/prio as col1 FROM xtable;
434 SELECT 1/prio as col1 FROM xtable;
435 END //
436
437 DELIMITER ;//
438
439