Debian Codegraph @

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

raw
1 --source include/xplugin_preamble.inc
2 --source include/xplugin_create_user.inc
3 CREATE DATABASE `ytest`;
4
5 ## Test starts here
6 --let $xtest_file= $MYSQL_TMP_DIR/update_table.xpl
7 --write_file $xtest_file
8 -->quiet
9 -->sql
10 drop schema if exists xtest;
11 create schema xtest;
12 create table xtest.xtable_ (id int primary key, name varchar(40), price decimal(5,2));
13 insert into xtest.xtable_ values (1, 'banana', 1.20);
14 insert into xtest.xtable_ values (2, 'apple', 0.25);
15 insert into xtest.xtable_ values (3, 'tomato', 1.80);
16 insert into xtest.xtable_ values (4, 'mango', 3.14);
17 insert into xtest.xtable_ values (5, 'orange', 0.90);
18
19
20 drop table if exists xtest.xtable;
21 create table xtest.xtable as select * from xtest.xtable_;
22 -->endsql
23
24 -->echo Rel: Update a valid row
25 Mysqlx.Crud.Update {
26 collection {
27 name: "xtable"
28 schema: "xtest"
29 }
30 data_model: TABLE
31 criteria {
32 type: OPERATOR
33 operator {
34 name: "=="
35 param {
36 type: IDENT
37 identifier {
38 name: "name"
39 }
40 }
41 param {
42 type: LITERAL
43 literal {
44 type: V_OCTETS
45 v_octets {value:"banana"}
46 }
47 }
48 }
49 }
50 operation {
51 source {
52 name: "name"
53 }
54 operation: SET
55 value {
56 type: LITERAL
57 literal {
58 type: V_STRING
59 v_string {
60 value: "lemon"
61 }
62 }
63 }
64 }
65 }
66 -->recvresult
67
68
69 -->sql
70 select * from xtest.xtable;
71 drop table if exists xtest.xtable;
72 create table xtest.xtable as select * from xtest.xtable_;
73 -->endsql
74
75 -->echo Rel: Update non-existing row
76 Mysqlx.Crud.Update {
77 collection {
78 name: "xtable"
79 schema: "xtest"
80 }
81 data_model: TABLE
82 criteria {
83 type: OPERATOR
84 operator {
85 name: "=="
86 param {
87 type: IDENT
88 identifier {
89 name: "name"
90 }
91 }
92 param {
93 type: LITERAL
94 literal {
95 type: V_OCTETS
96 v_octets {value:"lemon"}
97 }
98 }
99 }
100 }
101 operation {
102 source {
103 name: "name"
104 }
105 operation: SET
106 value {
107 type: LITERAL
108 literal {
109 type: V_STRING
110 v_string {
111 value: "lemon"
112 }
113 }
114 }
115 }
116 }
117 -->recvresult
118
119
120 -->sql
121 select * from xtest.xtable;
122 drop table if exists xtest.xtable;
123 create table xtest.xtable as select * from xtest.xtable_;
124 -->endsql
125
126 -->echo Rel: Update multiple rows
127 Mysqlx.Crud.Update {
128 collection {
129 name: "xtable"
130 schema: "xtest"
131 }
132 data_model: TABLE
133 criteria {
134 type: OPERATOR
135 operator {
136 name: ">"
137 param {
138 type: IDENT
139 identifier {
140 name: "price"
141 }
142 }
143 param {
144 type: LITERAL
145 literal {
146 type: V_DOUBLE
147 v_double: 1.0
148 }
149 }
150 }
151 }
152 operation {
153 source {
154 name: "name"
155 }
156 operation: SET
157 value {
158 type: LITERAL
159 literal {
160 type: V_STRING
161 v_string {
162 value: "lemon"
163 }
164 }
165 }
166 }
167 }
168 -->recvresult
169
170
171 -->sql
172 select * from xtest.xtable;
173 drop table if exists xtest.xtable;
174 create table xtest.xtable as select * from xtest.xtable_;
175 -->endsql
176
177 -->echo Rel: Update all rows (no criteria)
178 Mysqlx.Crud.Update {
179 collection {
180 name: "xtable"
181 schema: "xtest"
182 }
183 data_model: TABLE
184 operation {
185 source {
186 name: "name"
187 }
188 operation: SET
189 value {
190 type: LITERAL
191 literal {
192 type: V_STRING
193 v_string {
194 value: "lemon"
195 }
196 }
197 }
198 }
199 }
200 -->recvresult
201
202
203 -->sql
204 select * from xtest.xtable;
205 drop table if exists xtest.xtable;
206 create table xtest.xtable as select * from xtest.xtable_;
207 -->endsql
208
209 -->echo Rel: Update two first rows in desc order
210 Mysqlx.Crud.Update {
211 collection {
212 name: "xtable"
213 schema: "xtest"
214 }
215 data_model: TABLE
216 criteria {
217 type: OPERATOR
218 operator {
219 name: ">"
220 param {
221 type: IDENT
222 identifier {
223 name: "price"
224 }
225 }
226 param {
227 type: LITERAL
228 literal {
229 type: V_DOUBLE
230 v_double: 1.0
231 }
232 }
233 }
234 }
235 limit {
236 row_count: 2
237 }
238 order {
239 expr {
240 type: IDENT
241 identifier {
242 name: "price"
243 }
244 }
245 direction: DESC
246 }
247 operation {
248 source {
249 name: "name"
250 }
251 operation: SET
252 value {
253 type: LITERAL
254 literal {
255 type: V_STRING
256 v_string {
257 value: "lemon"
258 }
259 }
260 }
261 }
262 }
263 -->recvresult
264
265
266 -->sql
267 select * from xtest.xtable;
268 drop table if exists xtest.xtable;
269 create table xtest.xtable as select * from xtest.xtable_;
270 -->endsql
271
272 -->echo Rel: Update non-existing column
273 Mysqlx.Crud.Update {
274 collection {
275 name: "xtable"
276 schema: "xtest"
277 }
278 data_model: TABLE
279 operation {
280 source {
281 name: "noname"
282 }
283 operation: SET
284 value {
285 type: LITERAL
286 literal {
287 type: V_STRING
288 v_string {
289 value: "lemon"
290 }
291 }
292 }
293 }
294 }
295 -->expecterror 1054
296 -->recvresult
297
298
299 -->sql
300 select * from xtest.xtable;
301 drop table if exists xtest.xtable;
302 create table xtest.xtable as select * from xtest.xtable_;
303 -->endsql
304
305 -->echo Rel: Update on a table with expression on invalid columns
306 Mysqlx.Crud.Update {
307 collection {
308 name: "xtable"
309 schema: "xtest"
310 }
311 data_model: TABLE
312 criteria {
313 type: OPERATOR
314 operator {
315 name: ">"
316 param {
317 type: IDENT
318 identifier {
319 name: "noprice"
320 }
321 }
322 param {
323 type: LITERAL
324 literal {
325 type: V_DOUBLE
326 v_double: 1.0
327 }
328 }
329 }
330 }
331 operation {
332 source {
333 name: "name"
334 }
335 operation: SET
336 value {
337 type: LITERAL
338 literal {
339 type: V_STRING
340 v_string {
341 value: "lemon"
342 }
343 }
344 }
345 }
346 }
347 -->expecterror 1054
348 -->recvresult
349
350
351 -->sql
352 select * from xtest.xtable;
353 drop table if exists xtest.xtable;
354 create table xtest.xtable as select * from xtest.xtable_;
355 -->endsql
356
357 -->echo Rel: Update with empty operations type
358 Mysqlx.Crud.Update {
359 collection {
360 name: "xtable"
361 schema: "xtest"
362 }
363 data_model: TABLE
364 }
365 -->expecterror 5050
366 -->recvresult
367
368
369 -->sql
370 select * from xtest.xtable;
371 drop table if exists xtest.xtable;
372 create table xtest.xtable as select * from xtest.xtable_;
373 -->endsql
374
375 -->echo Rel: Update source column with table
376 Mysqlx.Crud.Update {
377 collection {
378 name: "xtable"
379 schema: "xtest"
380 }
381 data_model: TABLE
382 operation {
383 source {
384 name: ""
385 }
386 operation: SET
387 value {
388 type: LITERAL
389 literal {
390 type: V_STRING
391 v_string {
392 value: "lemon"
393 }
394 }
395 }
396 }
397 }
398 -->expecterror 5052
399 -->recvresult
400
401
402 -->sql
403 select * from xtest.xtable;
404 drop table if exists xtest.xtable;
405 create table xtest.xtable as select * from xtest.xtable_;
406 -->endsql
407
408 -->echo Rel: Update source column with table
409 Mysqlx.Crud.Update {
410 collection {
411 name: "xtable"
412 schema: "xtest"
413 }
414 data_model: TABLE
415 operation {
416 source {
417 name: "name"
418 table_name: "xtable"
419 }
420 operation: SET
421 value {
422 type: LITERAL
423 literal {
424 type: V_STRING
425 v_string {
426 value: "lemon"
427 }
428 }
429 }
430 }
431 }
432 -->expecterror 5052
433 -->recvresult
434
435
436 -->sql
437 select * from xtest.xtable;
438 drop table if exists xtest.xtable;
439 create table xtest.xtable as select * from xtest.xtable_;
440 -->endsql
441
442 -->echo Rel: Update source column with table and schema
443 Mysqlx.Crud.Update {
444 collection {
445 name: "xtable"
446 schema: "xtest"
447 }
448 data_model: TABLE
449 operation {
450 source {
451 name: "name"
452 table_name: "xtable"
453 schema_name: "xschema"
454 }
455 operation: SET
456 value {
457 type: LITERAL
458 literal {
459 type: V_STRING
460 v_string {
461 value: "lemon"
462 }
463 }
464 }
465 }
466 }
467 -->expecterror 5052
468 -->recvresult
469
470
471 -->sql
472 select * from xtest.xtable;
473 drop table if exists xtest.xtable;
474 create table xtest.xtable as select * from xtest.xtable_;
475 -->endsql
476
477 -->echo Rel: Update on an invalid schema
478 Mysqlx.Crud.Update {
479 collection {
480 name: "xtable"
481 schema: "ytest"
482 }
483 data_model: TABLE
484 operation {
485 source {
486 name: "name"
487 }
488 operation: SET
489 value {
490 type: LITERAL
491 literal {
492 type: V_STRING
493 v_string {
494 value: "lemon"
495 }
496 }
497 }
498 }
499 }
500 -->expecterror 1146
501 -->recvresult
502
503
504 -->sql
505 select * from xtest.xtable;
506 drop table if exists xtest.xtable;
507 create table xtest.xtable as select * from xtest.xtable_;
508 -->endsql
509
510 -->echo Rel: Update on an invalid collection
511 Mysqlx.Crud.Update {
512 collection {
513 name: "ytable"
514 schema: "xtest"
515 }
516 data_model: TABLE
517 operation {
518 source {
519 name: "name"
520 }
521 operation: SET
522 value {
523 type: LITERAL
524 literal {
525 type: V_STRING
526 v_string {
527 value: "lemon"
528 }
529 }
530 }
531 }
532 }
533
534 -->expecterror 1146
535 -->recvresult
536
537 -->sql
538 select * from xtest.xtable;
539 -->endsql
540
541
542 -->echo Rel: Update one field twice
543 Mysqlx.Crud.Update {
544 collection {
545 name: "xtable"
546 schema: "xtest"
547 }
548 data_model: TABLE
549 operation {
550 source {
551 name: "name"
552 }
553 operation: SET
554 value {
555 type: LITERAL
556 literal {
557 type: V_STRING
558 v_string {
559 value: "carrot"
560 }
561 }
562 }
563 }
564 operation {
565 source {
566 name: "name"
567 }
568 operation: SET
569 value {
570 type: LITERAL
571 literal {
572 type: V_STRING
573 v_string {
574 value: "lemon"
575 }
576 }
577 }
578 }
579 }
580 -->recvresult
581
582 -->sql
583 select * from xtest.xtable;
584 -->endsql
585
586 EOF
587
588 --exec $MYSQLXTEST -ux_root --password='' --file=$xtest_file 2>&1
589
590 ## Cleanup
591 drop schema ytest;
592 --source ../include/xplugin_cleanup.inc