Debian Codegraph @

mysql-test/suite/x/t/crud_update_table_json.test

raw
1 ## Upadting json values in non-document table
2
3 ## Preamble
4 --source include/xplugin_preamble.inc
5 --source include/xplugin_create_user.inc
6
7 ## Test starts here
8 --write_file $MYSQL_TMP_DIR/crud_update_table_json.xpl
9 ## Test data
10 -->sql
11 DROP SCHEMA IF EXISTS xtest;
12 CREATE SCHEMA xtest;
13 CREATE TABLE xtest.xtable (xfield JSON);
14 -->endsql
15
16 ### SET
17 -->sql
18 TRUNCATE TABLE xtest.xtable;
19 INSERT INTO xtest.xtable VALUES('{"first":1.0, "second": "two"}');
20 SELECT * FROM xtest.xtable;
21 -->endsql
22
23 Mysqlx.Crud.Update {
24 collection { name: "xtable" schema: "xtest"}
25 data_model: TABLE
26 operation {
27 source {
28 name: 'xfield'
29 }
30 operation: SET
31 value: {
32 type: LITERAL literal {type: V_OCTETS v_octets {value:'{"first":1.1, "second": "two.1"}'}}
33 }
34 }
35 }
36
37 #-- Mysqlx.Sql.StmtExecuteOk
38 -->recvresult
39
40 -->sql
41 SELECT * FROM xtest.xtable;
42 -->endsql
43
44
45 ### ITEM_SET
46 -->sql
47 TRUNCATE TABLE xtest.xtable;
48 INSERT INTO xtest.xtable VALUES('{"first":1.0, "second": "two"}');
49 SELECT * FROM xtest.xtable;
50 -->endsql
51
52 Mysqlx.Crud.Update {
53 collection { name: "xtable" schema: "xtest"}
54 data_model: TABLE
55 operation {
56 source {
57 name: 'xfield'
58 document_path {type: MEMBER value: 'first'}
59 }
60 operation: ITEM_SET
61 value: {
62 type: LITERAL literal {type: V_DOUBLE v_double: 1.1}
63 }
64 }
65 operation {
66 source {
67 name: 'xfield'
68 document_path {type: MEMBER value: 'second'}
69 }
70 operation: ITEM_SET
71 value: {
72 type: LITERAL literal {type: V_OCTETS v_octets {value:"two.1"}}
73 }
74 }
75 operation {
76 source {
77 name: 'xfield'
78 document_path {type: MEMBER value: 'third'}
79 }
80 operation: ITEM_SET
81 value: {
82 type: LITERAL literal {type: V_DOUBLE v_double: 3.1}
83 }
84 }
85 }
86
87 #-- Mysqlx.Sql.StmtExecuteOk
88 -->recvresult
89
90 -->sql
91 SELECT * FROM xtest.xtable;
92 -->endsql
93
94
95 ### ITEM_REMOVE
96 -->sql
97 TRUNCATE TABLE xtest.xtable;
98 INSERT INTO xtest.xtable VALUES('{"first":1.0, "second": "two"}');
99 SELECT * FROM xtest.xtable;
100 -->endsql
101
102 Mysqlx.Crud.Update {
103 collection { name: "xtable" schema: "xtest"}
104 data_model: TABLE
105 operation {
106 source {
107 name: 'xfield'
108 document_path {type: MEMBER value: 'first'}
109 }
110 operation: ITEM_REMOVE
111 }
112 }
113
114 #-- Mysqlx.Sql.StmtExecuteOk
115 -->recvresult
116
117 -->sql
118 SELECT * FROM xtest.xtable;
119 -->endsql
120
121
122 ### ITEM_REPLACE
123 -->sql
124 TRUNCATE TABLE xtest.xtable;
125 INSERT INTO xtest.xtable VALUES('{"first":1.0, "second": "two"}');
126 SELECT * FROM xtest.xtable;
127 -->endsql
128
129 Mysqlx.Crud.Update {
130 collection { name: "xtable" schema: "xtest"}
131 data_model: TABLE
132 operation {
133 source {
134 name: 'xfield'
135 document_path {type: MEMBER value: 'first'}
136 }
137 operation: ITEM_REPLACE
138 value: {
139 type: LITERAL literal {type: V_DOUBLE v_double: 1.1}
140 }
141 }
142 operation {
143 source {
144 name: 'xfield'
145 document_path {type: MEMBER value: 'third'}
146 }
147 operation: ITEM_REPLACE
148 value: {
149 type: LITERAL literal {type: V_OCTETS v_octets {value:"three.1"}}
150 }
151 }
152 }
153
154 #-- Mysqlx.Sql.StmtExecuteOk
155 -->recvresult
156
157 -->sql
158 SELECT * FROM xtest.xtable;
159 -->endsql
160
161
162 ### ITEM_MERGE
163 -->sql
164 TRUNCATE TABLE xtest.xtable;
165 INSERT INTO xtest.xtable VALUES('{"first":1.0, "second": "two"}');
166 SELECT * FROM xtest.xtable;
167 -->endsql
168
169 Mysqlx.Crud.Update {
170 collection { name: "xtable" schema: "xtest"}
171 data_model: TABLE
172 operation {
173 source {
174 name: 'xfield'
175 }
176 operation: ITEM_MERGE
177 value: {
178 type: LITERAL literal {type: V_OCTETS v_octets {value:'{"third":3.0, "fourth": "four"}'}}
179 }
180 }
181 }
182
183 #-- Mysqlx.Sql.StmtExecuteOk
184 -->recvresult
185
186 -->sql
187 SELECT * FROM xtest.xtable;
188 -->endsql
189
190
191 ### ARRAY_INSERT
192 -->sql
193 TRUNCATE TABLE xtest.xtable;
194 INSERT INTO xtest.xtable VALUES('{"first":1.0, "second": ["two"]}');
195 SELECT * FROM xtest.xtable;
196 -->endsql
197
198 Mysqlx.Crud.Update {
199 collection { name: "xtable" schema: "xtest"}
200 data_model: TABLE
201 operation {
202 source {
203 name: 'xfield'
204 document_path {type: MEMBER value: 'second'}
205 document_path {type: ARRAY_INDEX index: 0}
206 }
207 operation: ARRAY_INSERT
208 value: {
209 type: LITERAL literal {type: V_OCTETS v_octets {value:'two.1'}}
210 }
211 }
212 }
213
214 #-- Mysqlx.Sql.StmtExecuteOk
215 -->recvresult
216
217 -->sql
218 SELECT * FROM xtest.xtable;
219 -->endsql
220
221
222 ### ARRAY_APPEND
223 -->sql
224 TRUNCATE TABLE xtest.xtable;
225 INSERT INTO xtest.xtable VALUES('{"first":1.0, "second": "two"}');
226 SELECT * FROM xtest.xtable;
227 -->endsql
228
229 Mysqlx.Crud.Update {
230 collection { name: "xtable" schema: "xtest"}
231 data_model: TABLE
232 operation {
233 source {
234 name: 'xfield'
235 document_path {type: MEMBER value: 'second'}
236 }
237 operation: ARRAY_APPEND
238 value: {
239 type: LITERAL literal {type: V_OCTETS v_octets {value:'two.1'}}
240 }
241 }
242 }
243
244 #-- Mysqlx.Sql.StmtExecuteOk
245 -->recvresult
246
247 -->sql
248 SELECT * FROM xtest.xtable;
249 -->endsql
250
251
252 ### ERRORS
253 -->sql
254 TRUNCATE TABLE xtest.xtable;
255 INSERT INTO xtest.xtable VALUES('{"first":1.0, "second": "two"}');
256 SELECT * FROM xtest.xtable;
257 -->endsql
258
259 ### SET errors
260
261 Mysqlx.Crud.Update {
262 collection { name: "xtable" schema: "xtest"}
263 data_model: TABLE
264 operation {
265 source {
266 name: 'xfield'
267 document_path {type: MEMBER value: 'second'}
268 }
269 operation: SET
270 value: {
271 type: LITERAL literal {type: V_OCTETS v_octets {value:'{"first":1.1}'}}
272 }
273 }
274 }
275
276 -->expecterror 5052
277 -->recvresult
278
279 Mysqlx.Crud.Update {
280 collection { name: "xtable" schema: "xtest"}
281 data_model: TABLE
282 operation {
283 source {
284 name: ''
285 }
286 operation: SET
287 value: {
288 type: LITERAL literal {type: V_OCTETS v_octets {value:'{"first":1.1}'}}
289 }
290 }
291 }
292
293 -->expecterror 5052
294 -->recvresult
295
296
297 ### ITEM_SET error
298
299 Mysqlx.Crud.Update {
300 collection { name: "xtable" schema: "xtest"}
301 data_model: TABLE
302 operation {
303 source {
304 name: 'xfield'
305 }
306 operation: ITEM_SET
307 value: {
308 type: LITERAL literal {type: V_DOUBLE v_double: 1.1}
309 }
310 }
311 }
312
313 -->expecterror 5053
314 -->recvresult
315
316
317 ### ITEM_REMOVE error
318
319 Mysqlx.Crud.Update {
320 collection { name: "xtable" schema: "xtest"}
321 data_model: TABLE
322 operation {
323 source {
324 name: 'xfield'
325 }
326 operation: ITEM_REMOVE
327 }
328 }
329
330 -->expecterror 5053
331 -->recvresult
332
333
334 ### ITEM_REPLACE error
335
336 Mysqlx.Crud.Update {
337 collection { name: "xtable" schema: "xtest"}
338 data_model: TABLE
339 operation {
340 source {
341 name: 'xfield'
342 }
343 operation: ITEM_REPLACE
344 value: {
345 type: LITERAL literal {type: V_DOUBLE v_double: 1.1}
346 }
347 }
348 }
349
350 -->expecterror 5053
351 -->recvresult
352
353
354 ### ARRAY_INSERT error
355
356 Mysqlx.Crud.Update {
357 collection { name: "xtable" schema: "xtest"}
358 data_model: TABLE
359 operation {
360 source {
361 name: 'xfield'
362 }
363 operation: ARRAY_INSERT
364 value: {
365 type: LITERAL literal {type: V_DOUBLE v_double: 1.1}
366 }
367 }
368 }
369
370 -->expecterror 5053
371 -->recvresult
372
373
374 ### ARRAY_APPEND error
375
376 Mysqlx.Crud.Update {
377 collection { name: "xtable" schema: "xtest"}
378 data_model: TABLE
379 operation {
380 source {
381 name: 'xfield'
382 }
383 operation: ARRAY_APPEND
384 value: {
385 type: LITERAL literal {type: V_DOUBLE v_double: 1.1}
386 }
387 }
388 }
389
390 -->expecterror 5053
391 -->recvresult
392
393 ## Cleanup
394 -->sql
395 DROP SCHEMA IF EXISTS xtest;
396 -->endsql
397 EOF
398
399 --exec $MYSQLXTEST -ux_root --password='' --file=$MYSQL_TMP_DIR/crud_update_table_json.xpl 2>&1
400
401 ## Cleanup
402 --source ../include/xplugin_cleanup.inc