Debian Codegraph @

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

raw
1 SET @start_global_value = @@global.default_storage_engine;
2 SELECT @start_global_value;
3 @start_global_value
4 InnoDB
5 SET @@global.default_storage_engine = myisam;
6 RUN DROP DATABASE IF EXISTS xplugintest
7
8 0 rows affected
9 RUN CREATE DATABASE xplugintest
10
11 1 rows affected
12 RUN USE xplugintest
13
14 0 rows affected
15 RUN CREATE TABLE `table1` (
16 `doc` VARCHAR(100) DEFAULT NULL,
17 `_id` INT AUTO_INCREMENT PRIMARY KEY
18 ) ENGINE=memory
19
20 0 rows affected
21
22 command ok
23 Insert multiple rows into a Document column : myisam table
24 send Mysqlx.Crud.Insert {
25 collection {
26 name: "table2"
27 schema: "xplugintest"
28 }
29 data_model: DOCUMENT
30 row {
31 field {
32 type: LITERAL
33 literal {
34 type: V_STRING
35 v_string {
36 value: "\n{\n \"_id\": \"1\",\n \"name\": \"Omar Bras\", \"id\": \"1\"\n}"
37 }
38 }
39 }
40 }
41 row {
42 field {
43 type: LITERAL
44 literal {
45 type: V_STRING
46 v_string {
47 value: "\n{\n \"_id\": \"2\",\n \"name\": \"Omar Mex\", \"id\": \"2\"\n}"
48 }
49 }
50 }
51 }
52 }
53
54
55 2 rows affected
56 Records: 2 Duplicates: 0 Warnings: 0
57 Insert into memory table
58 send Mysqlx.Crud.Insert {
59 collection {
60 name: "table1"
61 schema: "xplugintest"
62 }
63 data_model: TABLE
64 projection {
65 name: "_id"
66 }
67 projection {
68 name: "doc"
69 }
70 row {
71 field {
72 type: LITERAL
73 literal {
74 type: V_SINT
75 v_signed_int: 10
76 }
77 }
78 field {
79 type: LITERAL
80 literal {
81 type: V_STRING
82 v_string {
83 value: "Omar Bras"
84 }
85 }
86 }
87 }
88 row {
89 field {
90 type: LITERAL
91 literal {
92 type: V_SINT
93 v_signed_int: 11
94 }
95 }
96 field {
97 type: LITERAL
98 literal {
99 type: V_STRING
100 v_string {
101 value: "Omar Mex"
102 }
103 }
104 }
105 }
106 }
107
108
109 2 rows affected
110 last insert id: 11
111 Records: 2 Duplicates: 0 Warnings: 0
112 RUN SELECT * FROM xplugintest.table2
113 doc _id _json_schema
114 {"id": "1", "_id": "1", "name": "Omar Bras"} 1 {"type": "object"}
115 {"id": "2", "_id": "2", "name": "Omar Mex"} 2 {"type": "object"}
116 0 rows affected
117 RUN SELECT * FROM xplugintest.table1
118 doc _id
119 Omar Bras 10
120 Omar Mex 11
121 0 rows affected
122 "Index with unique(true), index column NOT NULL (true)":myisam table
123
124 command ok
125 RUN SHOW CREATE TABLE xplugintest.table2
126 Table Create Table
127 table2 CREATE TABLE `table2` (
128 `doc` json DEFAULT NULL,
129 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
130 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
131 `$ix_t50_r_2BAA686C5604372BA51E965E6346198F5072C3D7` text GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$.name'))) VIRTUAL NOT NULL,
132 PRIMARY KEY (`_id`),
133 UNIQUE KEY `name_index` (`$ix_t50_r_2BAA686C5604372BA51E965E6346198F5072C3D7`(50)),
134 CONSTRAINT `$val_strict_8A6DECD6D7FCE389DA6206C4247042D2D76E08AD` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
135 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
136 0 rows affected
137 RUN SHOW CREATE TABLE xplugintest.table1
138 Table Create Table
139 table1 CREATE TABLE `table1` (
140 `doc` varchar(100) DEFAULT NULL,
141 `_id` int NOT NULL AUTO_INCREMENT,
142 PRIMARY KEY (`_id`)
143 ) ENGINE=MEMORY AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
144 0 rows affected
145 RUN SELECT * FROM xplugintest.table2
146 doc _id _json_schema $ix_t50_r_2BAA686C5604372BA51E965E6346198F5072C3D7
147 {"id": "1", "_id": "1", "name": "Omar Bras"} 1 {"type": "object"} Omar Bras
148 {"id": "2", "_id": "2", "name": "Omar Mex"} 2 {"type": "object"} Omar Mex
149 0 rows affected
150 RUN SELECT * FROM xplugintest.table1
151 doc _id
152 Omar Bras 10
153 Omar Mex 11
154 0 rows affected
155 Update - myisam table
156 send Mysqlx.Crud.Update {
157 collection {
158 name: "table2"
159 schema: "xplugintest"
160 }
161 data_model: DOCUMENT
162 criteria {
163 type: OPERATOR
164 operator {
165 name: "=="
166 param {
167 type: IDENT
168 identifier {
169 name: "_id"
170 }
171 }
172 param {
173 type: LITERAL
174 literal {
175 type: V_SINT
176 v_signed_int: 2
177 }
178 }
179 }
180 }
181 operation {
182 source {
183 document_path {
184 type: MEMBER
185 value: "name"
186 }
187 }
188 operation: ITEM_SET
189 value {
190 type: LITERAL
191 literal {
192 type: V_STRING
193 v_string {
194 value: "\"Omar Argentina\""
195 }
196 }
197 }
198 }
199 }
200
201
202 1 rows affected
203 Rows matched: 1 Changed: 1 Warnings: 0
204 Update value - memory table
205 send Mysqlx.Crud.Update {
206 collection {
207 name: "table1"
208 schema: "xplugintest"
209 }
210 data_model: TABLE
211 criteria {
212 type: OPERATOR
213 operator {
214 name: "=="
215 param {
216 type: IDENT
217 identifier {
218 name: "_id"
219 }
220 }
221 param {
222 type: LITERAL
223 literal {
224 type: V_SINT
225 v_signed_int: 10
226 }
227 }
228 }
229 }
230 operation {
231 source {
232 name: "doc"
233 }
234 operation: SET
235 value {
236 type: LITERAL
237 literal {
238 type: V_STRING
239 v_string {
240 value: "Omar Argentina"
241 }
242 }
243 }
244 }
245 }
246
247
248 1 rows affected
249 Rows matched: 1 Changed: 1 Warnings: 0
250 RUN SHOW CREATE TABLE xplugintest.table2
251 Table Create Table
252 table2 CREATE TABLE `table2` (
253 `doc` json DEFAULT NULL,
254 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
255 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
256 `$ix_t50_r_2BAA686C5604372BA51E965E6346198F5072C3D7` text GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$.name'))) VIRTUAL NOT NULL,
257 PRIMARY KEY (`_id`),
258 UNIQUE KEY `name_index` (`$ix_t50_r_2BAA686C5604372BA51E965E6346198F5072C3D7`(50)),
259 CONSTRAINT `$val_strict_8A6DECD6D7FCE389DA6206C4247042D2D76E08AD` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
260 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
261 0 rows affected
262 RUN SHOW CREATE TABLE xplugintest.table1
263 Table Create Table
264 table1 CREATE TABLE `table1` (
265 `doc` varchar(100) DEFAULT NULL,
266 `_id` int NOT NULL AUTO_INCREMENT,
267 PRIMARY KEY (`_id`)
268 ) ENGINE=MEMORY AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
269 0 rows affected
270 RUN SELECT JSON_EXTRACT(doc,'$.name') FROM xplugintest.table2
271 JSON_EXTRACT(doc,'$.name')
272 "Omar Bras"
273 "\"Omar Argentina\""
274 0 rows affected
275 RUN SELECT * FROM xplugintest.table2
276 doc _id _json_schema $ix_t50_r_2BAA686C5604372BA51E965E6346198F5072C3D7
277 {"id": "1", "_id": "1", "name": "Omar Bras"} 1 {"type": "object"} Omar Bras
278 {"id": "2", "_id": "2", "name": "\"Omar Argentina\""} 2 {"type": "object"} "Omar Argentina"
279 0 rows affected
280 RUN SELECT * FROM xplugintest.table1
281 doc _id
282 Omar Argentina 10
283 Omar Mex 11
284 0 rows affected
285 Delete with == operator : myisam table
286 send Mysqlx.Crud.Delete {
287 collection {
288 name: "table2"
289 schema: "xplugintest"
290 }
291 data_model: DOCUMENT
292 criteria {
293 type: OPERATOR
294 operator {
295 name: "=="
296 param {
297 type: IDENT
298 identifier {
299 name: "_id"
300 }
301 }
302 param {
303 type: LITERAL
304 literal {
305 type: V_SINT
306 v_signed_int: 2
307 }
308 }
309 }
310 }
311 }
312
313
314 1 rows affected
315 Delete with == operator : memory table
316 send Mysqlx.Crud.Delete {
317 collection {
318 name: "table1"
319 schema: "xplugintest"
320 }
321 data_model: TABLE
322 criteria {
323 type: OPERATOR
324 operator {
325 name: "=="
326 param {
327 type: IDENT
328 identifier {
329 name: "_id"
330 }
331 }
332 param {
333 type: LITERAL
334 literal {
335 type: V_SINT
336 v_signed_int: 11
337 }
338 }
339 }
340 }
341 }
342
343
344 1 rows affected
345 RUN SELECT JSON_EXTRACT(doc,'$.name') FROM xplugintest.table2
346 JSON_EXTRACT(doc,'$.name')
347 "Omar Bras"
348 0 rows affected
349 RUN SELECT * FROM xplugintest.table2
350 doc _id _json_schema $ix_t50_r_2BAA686C5604372BA51E965E6346198F5072C3D7
351 {"id": "1", "_id": "1", "name": "Omar Bras"} 1 {"type": "object"} Omar Bras
352 0 rows affected
353 RUN SELECT * FROM xplugintest.table1
354 doc _id
355 Omar Argentina 10
356 0 rows affected
357 insert : myisam table
358 send Mysqlx.Crud.Insert {
359 collection {
360 name: "table2"
361 schema: "xplugintest"
362 }
363 data_model: DOCUMENT
364 row {
365 field {
366 type: LITERAL
367 literal {
368 type: V_STRING
369 v_string {
370 value: "\n{\n \"_id\": \"3\",\n \"name\": \"Omar Peru\", \"id\": \"3\"\n}"
371 }
372 }
373 }
374 }
375 }
376
377
378 1 rows affected
379 insert : memory table
380 send Mysqlx.Crud.Insert {
381 collection {
382 name: "table1"
383 schema: "xplugintest"
384 }
385 data_model: TABLE
386 projection {
387 name: "_id"
388 }
389 projection {
390 name: "doc"
391 }
392 row {
393 field {
394 type: LITERAL
395 literal {
396 type: V_SINT
397 v_signed_int: 12
398 }
399 }
400 field {
401 type: LITERAL
402 literal {
403 type: V_STRING
404 v_string {
405 value: "Omar Peru"
406 }
407 }
408 }
409 }
410 }
411
412
413 1 rows affected
414 last insert id: 12
415 # Insert fail due to non unique _id doc member: myisam table
416 send Mysqlx.Crud.Insert {
417 collection {
418 name: "table2"
419 schema: "xplugintest"
420 }
421 data_model: DOCUMENT
422 row {
423 field {
424 type: LITERAL
425 literal {
426 type: V_STRING
427 v_string {
428 value: "\n{\n \"_id\": \"1\",\n \"name\": \"Omar Colombia\", \"id\": \"3\"\n}"
429 }
430 }
431 }
432 }
433 }
434
435 Got expected error: Document contains a field value that is not unique but required to be (code 5116)
436 # Insert fail due to non unique _id doc member: memory table
437 send Mysqlx.Crud.Insert {
438 collection {
439 name: "table1"
440 schema: "xplugintest"
441 }
442 data_model: TABLE
443 projection {
444 name: "_id"
445 }
446 projection {
447 name: "doc"
448 }
449 row {
450 field {
451 type: LITERAL
452 literal {
453 type: V_SINT
454 v_signed_int: 10
455 }
456 }
457 field {
458 type: LITERAL
459 literal {
460 type: V_STRING
461 v_string {
462 value: "Omar Colombia"
463 }
464 }
465 }
466 }
467 }
468
469 Got expected error: Duplicate entry '10' for key 'table1.PRIMARY' (code 1062)
470 RUN SELECT * FROM xplugintest.table2
471 doc _id _json_schema $ix_t50_r_2BAA686C5604372BA51E965E6346198F5072C3D7
472 {"id": "1", "_id": "1", "name": "Omar Bras"} 1 {"type": "object"} Omar Bras
473 {"id": "3", "_id": "3", "name": "Omar Peru"} 3 {"type": "object"} Omar Peru
474 0 rows affected
475 RUN SELECT * FROM xplugintest.table1
476 doc _id
477 Omar Argentina 10
478 Omar Peru 12
479 0 rows affected
480 Mysqlx.Ok {
481 msg: "bye!"
482 }
483 ok
484 DROP DATABASE IF EXISTS xplugintest;
485 SET @@global.default_storage_engine = @start_global_value;
486 SELECT @@global.default_storage_engine;
487 @@global.default_storage_engine
488 InnoDB