Debian Codegraph @

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

raw
1 CREATE SCHEMA IF NOT EXISTS xtest;
2 USE xtest;
3 CREATE PROCEDURE recreate_tables()
4 BEGIN
5 CREATE TABLE IF NOT EXISTS xtable (phrase VARCHAR(30), prio INTEGER);
6 TRUNCATE TABLE xtable;
7 INSERT INTO xtable (phrase, prio) VALUES ('Alice has a cat', 0);
8 INSERT INTO xtable (phrase, prio) VALUES ('Bob has a dog 1', 1);
9 INSERT INTO xtable (phrase, prio) VALUES ('Peppa has a teddy', 0);
10 INSERT INTO xtable (phrase, prio) VALUES ('Bob has a dog 2', 2);
11 INSERT INTO xtable (phrase, prio) VALUES ('Row to delete', 3);
12 CREATE TABLE IF NOT EXISTS xtable_with_pk (p_id INTEGER AUTO_INCREMENT PRIMARY KEY, description varchar(40));
13 TRUNCATE TABLE xtable_with_pk;
14 INSERT INTO xtable_with_pk (description) VALUES ('MyRow');
15 END //
16 CREATE PROCEDURE one_resultsets()
17 BEGIN
18 SELECT phrase FROM xtable limit 3;
19 END //
20 CREATE PROCEDURE two_resultsets()
21 BEGIN
22 SELECT phrase FROM xtable limit 3;
23 SELECT prio FROM xtable limit 2;
24 END //
25 CREATE PROCEDURE two_resultsets_with_errors()
26 BEGIN
27 SELECT 1/prio as col1 FROM xtable;
28 SELECT 1/prio as col1 FROM xtable;
29 END //
30 CALL recreate_tables();
31
32
33 ## I. Validate non dynamic SQL statement where Cursor.Fetch is not needed
34 #
35 # 1. Assert empty resultset, dont get any rows at Cursor.Open.
36 # 2. Assert resultset that has less rows than requested by Cursor.Open.
37
38 ## II. Validate non dynamic SQL statement where Cursor.Fetch is needed to get all data
39 #
40 # 1. Assert empty resultset, still try to get at last one row at open
41 # 2. Assert resultset that has same amount of rows as requested
42 # 3. Assert resultset that has rows grater by one than requested
43 # 4. Assert resultset that has two times the amount of rows as requested
44 # 5. Assert resultset by fetching the resultset one by one row
45 # 6. Assert resultset by fetching the resultset and warnings generated by it
46
47 ## III. Validate dynamic SQL statement
48 #
49 # 1. Assert that cursor open, fetches whole resultset
50 # a. fetch_rows in Cursor.Open is not set
51 # b. fetch_rows in Cursor.Open is set (value doesn't matter).
52 # 2. Assert that cursor open, fetches multiple resultsets
53 # 3. Assert that cursor open, fetches multiple resultsets with warnings
54
55 ## IV. Validate notices
56 #
57 # 1. Insert stmt
58 # a. Table with primary key (expect notice GENERATED_INSERT_ID)
59 # b. Table without primary key (must not be generate GENERATED_INSERT_ID)
60 # 2. Delete stmt
61 # 3. Update stmt
62
63
64 #
65 # I.1
66 [PREPARE - SELECT * FROM xtable LIMIT 0]
67 RUN recvok
68 [EXECUTE]
69 [ASSERT Mysqlx.Resultset.ColumnMetaData]
70 [ASSERT Mysqlx.Resultset.ColumnMetaData]
71 [ASSERT Mysqlx.Resultset.FetchDone]
72 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
73 [ASSERT Mysqlx.Sql.StmtExecuteOk]
74
75 #
76 # I.2
77 [PREPARE - SELECT * FROM xtable]
78 RUN recvok
79 [EXECUTE]
80 [ASSERT Mysqlx.Resultset.ColumnMetaData]
81 [ASSERT Mysqlx.Resultset.ColumnMetaData]
82 [ASSERT Mysqlx.Resultset.Row]
83 [ASSERT Mysqlx.Resultset.Row]
84 [ASSERT Mysqlx.Resultset.Row]
85 [ASSERT Mysqlx.Resultset.Row]
86 [ASSERT Mysqlx.Resultset.Row]
87 [ASSERT Mysqlx.Resultset.FetchDone]
88 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
89 [ASSERT Mysqlx.Sql.StmtExecuteOk]
90
91 #
92 # II.1
93 [PREPARE - SELECT * FROM xtable LIMIT 0]
94 RUN recvok
95 [EXECUTE]
96 [ASSERT Mysqlx.Resultset.ColumnMetaData]
97 [ASSERT Mysqlx.Resultset.ColumnMetaData]
98 [ASSERT Mysqlx.Resultset.FetchSuspended]
99 [ASSERT Mysqlx.Sql.StmtExecuteOk]
100 [FETCH]
101 [ASSERT Mysqlx.Resultset.FetchDone]
102 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
103 [ASSERT Mysqlx.Sql.StmtExecuteOk]
104
105 #
106 # II.2
107 [PREPARE - SELECT * FROM xtable LIMIT 5]
108 RUN recvok
109 [EXECUTE]
110 [ASSERT Mysqlx.Resultset.ColumnMetaData]
111 [ASSERT Mysqlx.Resultset.ColumnMetaData]
112 [ASSERT Mysqlx.Resultset.Row]
113 [ASSERT Mysqlx.Resultset.Row]
114 [ASSERT Mysqlx.Resultset.Row]
115 [ASSERT Mysqlx.Resultset.Row]
116 [ASSERT Mysqlx.Resultset.Row]
117 [ASSERT Mysqlx.Resultset.FetchSuspended]
118 [ASSERT Mysqlx.Sql.StmtExecuteOk]
119 [FETCH]
120 [ASSERT Mysqlx.Resultset.FetchDone]
121 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
122 [ASSERT Mysqlx.Sql.StmtExecuteOk]
123
124 #
125 # II.3
126 [PREPARE - SELECT * FROM xtable LIMIT 5]
127 RUN recvok
128 [EXECUTE]
129 [ASSERT Mysqlx.Resultset.ColumnMetaData]
130 [ASSERT Mysqlx.Resultset.ColumnMetaData]
131 [ASSERT Mysqlx.Resultset.Row]
132 [ASSERT Mysqlx.Resultset.Row]
133 [ASSERT Mysqlx.Resultset.Row]
134 [ASSERT Mysqlx.Resultset.Row]
135 [ASSERT Mysqlx.Resultset.FetchSuspended]
136 [ASSERT Mysqlx.Sql.StmtExecuteOk]
137 [FETCH]
138 [ASSERT Mysqlx.Resultset.Row]
139 [ASSERT Mysqlx.Resultset.FetchDone]
140 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
141 [ASSERT Mysqlx.Sql.StmtExecuteOk]
142
143 #
144 # II.4
145 [PREPARE - SELECT * FROM xtable LIMIT 4]
146 RUN recvok
147 [EXECUTE]
148 [ASSERT Mysqlx.Resultset.ColumnMetaData]
149 [ASSERT Mysqlx.Resultset.ColumnMetaData]
150 [ASSERT Mysqlx.Resultset.Row]
151 [ASSERT Mysqlx.Resultset.Row]
152 [ASSERT Mysqlx.Resultset.FetchSuspended]
153 [ASSERT Mysqlx.Sql.StmtExecuteOk]
154 [FETCH]
155 [ASSERT Mysqlx.Resultset.Row]
156 [ASSERT Mysqlx.Resultset.Row]
157 [ASSERT Mysqlx.Resultset.FetchDone]
158 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
159 [ASSERT Mysqlx.Sql.StmtExecuteOk]
160
161 #
162 # II.5
163 [PREPARE - SELECT * FROM xtable LIMIT 3]
164 RUN recvok
165 [EXECUTE]
166 [ASSERT Mysqlx.Resultset.ColumnMetaData]
167 [ASSERT Mysqlx.Resultset.ColumnMetaData]
168 [ASSERT Mysqlx.Resultset.Row]
169 [ASSERT Mysqlx.Resultset.FetchSuspended]
170 [ASSERT Mysqlx.Sql.StmtExecuteOk]
171 [FETCH]
172 [ASSERT Mysqlx.Resultset.Row]
173 [ASSERT Mysqlx.Resultset.FetchSuspended]
174 [ASSERT Mysqlx.Sql.StmtExecuteOk]
175 [FETCH]
176 [ASSERT Mysqlx.Resultset.Row]
177 [ASSERT Mysqlx.Resultset.FetchSuspended]
178 [ASSERT Mysqlx.Sql.StmtExecuteOk]
179 [FETCH]
180 [ASSERT Mysqlx.Resultset.FetchDone]
181 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
182 [ASSERT Mysqlx.Sql.StmtExecuteOk]
183
184 #
185 # II.6
186 [PREPARE - SELECT 1/prio as col1 FROM xtable]
187 RUN recvok
188 [EXECUTE]
189 [ASSERT Mysqlx.Resultset.ColumnMetaData]
190 [ASSERT Mysqlx.Resultset.FetchSuspended]
191 [ASSERT notice warning - WARNING, 1365]
192 [ASSERT notice warning - WARNING, 1365]
193 [ASSERT Mysqlx.Sql.StmtExecuteOk]
194 [FETCH]
195 [ASSERT Mysqlx.Resultset.Row]
196 [ASSERT Mysqlx.Resultset.Row]
197 [ASSERT Mysqlx.Resultset.FetchSuspended]
198 [ASSERT Mysqlx.Sql.StmtExecuteOk]
199 [FETCH]
200 [ASSERT Mysqlx.Resultset.Row]
201 [ASSERT Mysqlx.Resultset.Row]
202 [ASSERT Mysqlx.Resultset.Row]
203 [ASSERT Mysqlx.Resultset.FetchDone]
204 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
205 [ASSERT Mysqlx.Sql.StmtExecuteOk]
206
207 #
208 # III.1.a
209 [PREPARE - CALL one_resultsets()]
210 RUN recvok
211 [EXECUTE]
212 [ASSERT Mysqlx.Resultset.ColumnMetaData]
213 [ASSERT Mysqlx.Resultset.Row]
214 [ASSERT Mysqlx.Resultset.Row]
215 [ASSERT Mysqlx.Resultset.Row]
216 [ASSERT Mysqlx.Resultset.FetchDoneMoreResultsets]
217 [ASSERT Mysqlx.Resultset.FetchDone]
218 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
219 [ASSERT Mysqlx.Sql.StmtExecuteOk]
220
221 #
222 # III.1.b
223 [PREPARE - CALL one_resultsets()]
224 RUN recvok
225 [EXECUTE]
226 [ASSERT Mysqlx.Resultset.ColumnMetaData]
227 [ASSERT Mysqlx.Resultset.Row]
228 [ASSERT Mysqlx.Resultset.Row]
229 [ASSERT Mysqlx.Resultset.Row]
230 [ASSERT Mysqlx.Resultset.FetchDoneMoreResultsets]
231 [ASSERT Mysqlx.Resultset.FetchDone]
232 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
233 [ASSERT Mysqlx.Sql.StmtExecuteOk]
234
235 #
236 # III.2
237 [PREPARE - CALL two_resultsets()]
238 RUN recvok
239 [EXECUTE]
240 [ASSERT Mysqlx.Resultset.ColumnMetaData]
241 [ASSERT Mysqlx.Resultset.Row]
242 [ASSERT Mysqlx.Resultset.Row]
243 [ASSERT Mysqlx.Resultset.Row]
244 [ASSERT Mysqlx.Resultset.FetchDoneMoreResultsets]
245 [ASSERT Mysqlx.Resultset.ColumnMetaData]
246 [ASSERT Mysqlx.Resultset.Row]
247 [ASSERT Mysqlx.Resultset.Row]
248 [ASSERT Mysqlx.Resultset.FetchDoneMoreResultsets]
249 [ASSERT Mysqlx.Resultset.FetchDone]
250 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
251 [ASSERT Mysqlx.Sql.StmtExecuteOk]
252
253 #
254 # III.3
255 [PREPARE - CALL two_resultsets_with_errors]
256 RUN recvok
257 [EXECUTE]
258 [ASSERT Mysqlx.Resultset.ColumnMetaData]
259 [ASSERT Mysqlx.Resultset.Row]
260 [ASSERT Mysqlx.Resultset.Row]
261 [ASSERT Mysqlx.Resultset.Row]
262 [ASSERT Mysqlx.Resultset.Row]
263 [ASSERT Mysqlx.Resultset.Row]
264 [ASSERT Mysqlx.Resultset.FetchDoneMoreResultsets]
265 [ASSERT Mysqlx.Resultset.ColumnMetaData]
266 [ASSERT Mysqlx.Resultset.Row]
267 [ASSERT Mysqlx.Resultset.Row]
268 [ASSERT Mysqlx.Resultset.Row]
269 [ASSERT Mysqlx.Resultset.Row]
270 [ASSERT Mysqlx.Resultset.Row]
271 [ASSERT Mysqlx.Resultset.FetchDoneMoreResultsets]
272 [ASSERT Mysqlx.Resultset.FetchDone]
273 [ASSERT notice warning - WARNING, 1365]
274 [ASSERT notice warning - WARNING, 1365]
275 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
276 [ASSERT Mysqlx.Sql.StmtExecuteOk]
277
278 #
279 # IV.1.a
280 [PREPARE - INSERT INTO xtest.xtable_with_pk(description) VALUE('MyCustomRow')]
281 RUN recvok
282 [EXECUTE]
283 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:1]
284 [ASSERT notice session state change - GENERATED_INSERT_ID, v_unsigned_int: 2]
285 [ASSERT Mysqlx.Sql.StmtExecuteOk]
286
287 #
288 # IV.1.b
289 [PREPARE - INSERT INTO xtest.xtable(phrase, prio) VALUE('MyCustomRow', 100)]
290 RUN recvok
291 [EXECUTE]
292 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:1]
293 [ASSERT Mysqlx.Sql.StmtExecuteOk]
294
295 #
296 # IV.2
297 [PREPARE - DELETE FROM xtest.xtable WHERE prio=100]
298 RUN recvok
299 [EXECUTE]
300 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:1]
301 [ASSERT Mysqlx.Sql.StmtExecuteOk]
302
303 #
304 # IV.3
305 [PREPARE - UPDATE xtest.xtable SET phrase='New']
306 RUN recvok
307 [EXECUTE]
308 [ASSERT notice session state change - PRODUCED_MESSAGE, type: V_STRING]
309 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:5]
310 [ASSERT Mysqlx.Sql.StmtExecuteOk]
311 Mysqlx.Ok {
312 msg: "bye!"
313 }
314 ok