Debian Codegraph @

mysql-test/suite/x/r/flow_resultset_prepexecute_crud_table.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', 1);
8 INSERT INTO xtable (phrase, prio) VALUES ('Bob has a dog 1', 2);
9 INSERT INTO xtable (phrase, prio) VALUES ('Peppa has a teddy', 1);
10 INSERT INTO xtable (phrase, prio) VALUES ('Bob has a dog 2', 3);
11 INSERT INTO xtable (phrase, prio) VALUES ('Row to delete', 4);
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 CALL recreate_tables();
17
18
19 ## Test setup
20
21
22 ## I. Validate simple resultsets for CRUD messages
23 #
24 # 1. Assert resultset for CRUD-delete message
25 # 2. Assert resultset for CRUD-update message
26 # 3. Assert resultset for CRUD-create message (table with auto_inc column)
27 # 4. Assert resultset for CRUD-create message (table without auto_inc column)
28 # 5. Assert resultset for CRUD-read message
29
30 ## II. Validate simple resultset with warnings
31 #
32 # 1. Assert one resultsets for select queries
33
34 #
35 # I.1
36 send Mysqlx.Prepare.Prepare {
37 stmt_id: 1
38 stmt {
39 type: DELETE
40 delete {
41 collection {
42 name: "xtable"
43 }
44 data_model: TABLE
45 criteria {
46 type: OPERATOR
47 operator {
48 name: "=="
49 param {
50 type: IDENT
51 identifier {
52 name: "prio"
53 }
54 }
55 param {
56 type: LITERAL
57 literal {
58 type: V_SINT
59 v_signed_int: 3
60 }
61 }
62 }
63 }
64 }
65 }
66 }
67
68 RUN recvok
69 send Mysqlx.Prepare.Execute {
70 stmt_id: 1
71 }
72
73 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:1]
74 [ASSERT Mysqlx.Sql.StmtExecuteOk]
75
76 #
77 # I.2
78 send Mysqlx.Prepare.Prepare {
79 stmt_id: 2
80 stmt {
81 type: UPDATE
82 update {
83 collection {
84 name: "xtable"
85 }
86 data_model: TABLE
87 operation {
88 source {
89 name: "prio"
90 }
91 operation: SET
92 value {
93 type: OPERATOR
94 operator {
95 name: "-"
96 param {
97 type: IDENT
98 identifier {
99 name: "prio"
100 }
101 }
102 param {
103 type: LITERAL
104 literal {
105 type: V_SINT
106 v_signed_int: 1
107 }
108 }
109 }
110 }
111 }
112 }
113 }
114 }
115
116 RUN recvok
117 send Mysqlx.Prepare.Execute {
118 stmt_id: 2
119 }
120
121 [ASSERT notice session state change - PRODUCED_MESSAGE, type: V_STRING]
122 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:4]
123 [ASSERT Mysqlx.Sql.StmtExecuteOk]
124
125 #
126 # I.3
127 send Mysqlx.Prepare.Prepare {
128 stmt_id: 3
129 stmt {
130 type: INSERT
131 insert {
132 collection {
133 name: "xtable"
134 }
135 data_model: TABLE
136 row {
137 field {
138 type: LITERAL
139 literal {
140 type: V_STRING
141 v_string {
142 value: "sixth"
143 }
144 }
145 }
146 field {
147 type: LITERAL
148 literal {
149 type: V_SINT
150 v_signed_int: 3
151 }
152 }
153 }
154 upsert: false
155 }
156 }
157 }
158
159 RUN recvok
160 send Mysqlx.Prepare.Execute {
161 stmt_id: 3
162 }
163
164 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:1]
165 [ASSERT Mysqlx.Sql.StmtExecuteOk]
166
167 #
168 # I.4
169 send Mysqlx.Prepare.Prepare {
170 stmt_id: 4
171 stmt {
172 type: INSERT
173 insert {
174 collection {
175 name: "xtable_with_pk"
176 }
177 data_model: TABLE
178 projection {
179 name: "description"
180 }
181 row {
182 field {
183 type: LITERAL
184 literal {
185 type: V_STRING
186 v_string {
187 value: "test"
188 }
189 }
190 }
191 }
192 upsert: false
193 }
194 }
195 }
196
197 RUN recvok
198 send Mysqlx.Prepare.Execute {
199 stmt_id: 4
200 }
201
202 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:1]
203 [ASSERT notice session state change - GENERATED_INSERT_ID, v_unsigned_int: 2]
204 [ASSERT Mysqlx.Sql.StmtExecuteOk]
205
206 #
207 # I.5
208 send Mysqlx.Prepare.Prepare {
209 stmt_id: 5
210 stmt {
211 type: FIND
212 find {
213 collection {
214 name: "xtable"
215 }
216 data_model: TABLE
217 projection {
218 source {
219 type: IDENT
220 identifier {
221 name: "prio"
222 }
223 }
224 alias: "prio"
225 }
226 }
227 }
228 }
229
230 RUN recvok
231 send Mysqlx.Prepare.Execute {
232 stmt_id: 5
233 }
234
235 [ASSERT Mysqlx.Resultset.ColumnMetaData]
236 [ASSERT Mysqlx.Resultset.Row]
237 [ASSERT Mysqlx.Resultset.Row]
238 [ASSERT Mysqlx.Resultset.Row]
239 [ASSERT Mysqlx.Resultset.Row]
240 [ASSERT Mysqlx.Resultset.Row]
241 [ASSERT Mysqlx.Resultset.FetchDone]
242 [ASSERT Mysqlx.Sql.StmtExecuteOk]
243
244 #
245 # II.1
246 send Mysqlx.Prepare.Prepare {
247 stmt_id: 6
248 stmt {
249 type: FIND
250 find {
251 collection {
252 name: "xtable"
253 }
254 data_model: TABLE
255 projection {
256 source {
257 type: OPERATOR
258 operator {
259 name: "/"
260 param {
261 type: LITERAL
262 literal {
263 type: V_SINT
264 v_signed_int: 1
265 }
266 }
267 param {
268 type: IDENT
269 identifier {
270 name: "prio"
271 }
272 }
273 }
274 }
275 alias: "name"
276 }
277 }
278 }
279 }
280
281 RUN recvok
282 send Mysqlx.Prepare.Execute {
283 stmt_id: 6
284 }
285
286 [ASSERT Mysqlx.Resultset.ColumnMetaData]
287 [ASSERT Mysqlx.Resultset.Row]
288 [ASSERT Mysqlx.Resultset.Row]
289 [ASSERT Mysqlx.Resultset.Row]
290 [ASSERT Mysqlx.Resultset.Row]
291 [ASSERT Mysqlx.Resultset.Row]
292 [ASSERT Mysqlx.Resultset.FetchDone]
293 [ASSERT notice warning - WARNING, 1365]
294 [ASSERT notice warning - WARNING, 1365]
295 [ASSERT Mysqlx.Sql.StmtExecuteOk]
296 Mysqlx.Ok {
297 msg: "bye!"
298 }
299 ok