Debian Codegraph @

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

raw
1 call mtr.add_suppression("Error parsing message of type 12: args");
2 call mtr.add_suppression("Error handling message: args");
3 RUN CREATE DATABASE xplugintest
4
5 1 rows affected
6 RUN USE xplugintest
7
8 0 rows affected
9
10 command ok
11 Create and drop indexes of different types on single fields
12 Mysqlx.Sql.StmtExecuteOk {
13 }
14
15 RUN ANALYZE TABLE xplugintest.table1
16 Table Op Msg_type Msg_text
17 xplugintest.table1 analyze status OK
18 0 rows affected
19 RUN SHOW INDEX FROM xplugintest.table1
20 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
21 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
22 table1 0 ix 1 $ix_it_r_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
23 0 rows affected
24 RUN SHOW CREATE TABLE xplugintest.table1
25 Table Create Table
26 table1 CREATE TABLE `table1` (
27 `doc` json DEFAULT NULL,
28 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
29 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
30 `$ix_it_r_193639E0029FB71DA5916BDF64B75D481FD4CF70` tinyint GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
31 PRIMARY KEY (`_id`),
32 UNIQUE KEY `ix` (`$ix_it_r_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
33 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
34 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
35 0 rows affected
36 Mysqlx.Sql.StmtExecuteOk {
37 }
38
39 RUN SHOW INDEX FROM xplugintest.table1
40 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
41 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
42 0 rows affected
43 Mysqlx.Sql.StmtExecuteOk {
44 }
45
46 RUN ANALYZE TABLE xplugintest.table1
47 Table Op Msg_type Msg_text
48 xplugintest.table1 analyze status OK
49 0 rows affected
50 RUN SHOW INDEX FROM xplugintest.table1
51 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
52 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
53 table1 0 ix 1 $ix_it_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
54 0 rows affected
55 RUN SHOW CREATE TABLE xplugintest.table1
56 Table Create Table
57 table1 CREATE TABLE `table1` (
58 `doc` json DEFAULT NULL,
59 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
60 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
61 `$ix_it_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70` tinyint unsigned GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
62 PRIMARY KEY (`_id`),
63 UNIQUE KEY `ix` (`$ix_it_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
64 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
65 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
66 0 rows affected
67 Mysqlx.Sql.StmtExecuteOk {
68 }
69
70 RUN SHOW INDEX FROM xplugintest.table1
71 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
72 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
73 0 rows affected
74 Mysqlx.Sql.StmtExecuteOk {
75 }
76
77 RUN ANALYZE TABLE xplugintest.table1
78 Table Op Msg_type Msg_text
79 xplugintest.table1 analyze status OK
80 0 rows affected
81 RUN SHOW INDEX FROM xplugintest.table1
82 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
83 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
84 table1 0 ix 1 $ix_it2_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
85 0 rows affected
86 RUN SHOW CREATE TABLE xplugintest.table1
87 Table Create Table
88 table1 CREATE TABLE `table1` (
89 `doc` json DEFAULT NULL,
90 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
91 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
92 `$ix_it2_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70` tinyint unsigned GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
93 PRIMARY KEY (`_id`),
94 UNIQUE KEY `ix` (`$ix_it2_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
95 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
96 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
97 0 rows affected
98 Mysqlx.Sql.StmtExecuteOk {
99 }
100
101 RUN SHOW INDEX FROM xplugintest.table1
102 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
103 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
104 0 rows affected
105 Mysqlx.Sql.StmtExecuteOk {
106 }
107
108 RUN ANALYZE TABLE xplugintest.table1
109 Table Op Msg_type Msg_text
110 xplugintest.table1 analyze status OK
111 0 rows affected
112 RUN SHOW INDEX FROM xplugintest.table1
113 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
114 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
115 table1 0 ix 1 $ix_is_r_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
116 0 rows affected
117 RUN SHOW CREATE TABLE xplugintest.table1
118 Table Create Table
119 table1 CREATE TABLE `table1` (
120 `doc` json DEFAULT NULL,
121 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
122 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
123 `$ix_is_r_193639E0029FB71DA5916BDF64B75D481FD4CF70` smallint GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
124 PRIMARY KEY (`_id`),
125 UNIQUE KEY `ix` (`$ix_is_r_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
126 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
127 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
128 0 rows affected
129 Mysqlx.Sql.StmtExecuteOk {
130 }
131
132 RUN SHOW INDEX FROM xplugintest.table1
133 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
134 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
135 0 rows affected
136 Mysqlx.Sql.StmtExecuteOk {
137 }
138
139 RUN ANALYZE TABLE xplugintest.table1
140 Table Op Msg_type Msg_text
141 xplugintest.table1 analyze status OK
142 0 rows affected
143 RUN SHOW INDEX FROM xplugintest.table1
144 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
145 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
146 table1 0 ix 1 $ix_is_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
147 0 rows affected
148 RUN SHOW CREATE TABLE xplugintest.table1
149 Table Create Table
150 table1 CREATE TABLE `table1` (
151 `doc` json DEFAULT NULL,
152 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
153 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
154 `$ix_is_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70` smallint unsigned GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
155 PRIMARY KEY (`_id`),
156 UNIQUE KEY `ix` (`$ix_is_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
157 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
158 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
159 0 rows affected
160 Mysqlx.Sql.StmtExecuteOk {
161 }
162
163 RUN SHOW INDEX FROM xplugintest.table1
164 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
165 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
166 0 rows affected
167 Mysqlx.Sql.StmtExecuteOk {
168 }
169
170 RUN ANALYZE TABLE xplugintest.table1
171 Table Op Msg_type Msg_text
172 xplugintest.table1 analyze status OK
173 0 rows affected
174 RUN SHOW INDEX FROM xplugintest.table1
175 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
176 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
177 table1 0 ix 1 $ix_is4_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
178 0 rows affected
179 RUN SHOW CREATE TABLE xplugintest.table1
180 Table Create Table
181 table1 CREATE TABLE `table1` (
182 `doc` json DEFAULT NULL,
183 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
184 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
185 `$ix_is4_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70` smallint unsigned GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
186 PRIMARY KEY (`_id`),
187 UNIQUE KEY `ix` (`$ix_is4_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
188 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
189 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
190 0 rows affected
191 RUN INSERT INTO xplugintest.table1(doc) VALUES('{"col":"12345","_id":1}')
192
193 1 rows affected
194 RUN SELECT * FROM xplugintest.table1
195 doc _id _json_schema $ix_is4_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70
196 {"_id": 1, "col": "12345"} 1 {"type": "object"} 12345
197 0 rows affected
198 RUN DELETE FROM xplugintest.table1
199
200 1 rows affected
201 Mysqlx.Sql.StmtExecuteOk {
202 }
203
204 RUN SHOW INDEX FROM xplugintest.table1
205 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
206 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
207 0 rows affected
208 Mysqlx.Sql.StmtExecuteOk {
209 }
210
211 RUN ANALYZE TABLE xplugintest.table1
212 Table Op Msg_type Msg_text
213 xplugintest.table1 analyze status OK
214 0 rows affected
215 RUN SHOW INDEX FROM xplugintest.table1
216 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
217 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
218 table1 0 ix 1 $ix_im_r_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
219 0 rows affected
220 RUN SHOW CREATE TABLE xplugintest.table1
221 Table Create Table
222 table1 CREATE TABLE `table1` (
223 `doc` json DEFAULT NULL,
224 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
225 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
226 `$ix_im_r_193639E0029FB71DA5916BDF64B75D481FD4CF70` mediumint GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
227 PRIMARY KEY (`_id`),
228 UNIQUE KEY `ix` (`$ix_im_r_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
229 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
230 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
231 0 rows affected
232 Mysqlx.Sql.StmtExecuteOk {
233 }
234
235 RUN SHOW INDEX FROM xplugintest.table1
236 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
237 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
238 0 rows affected
239 Mysqlx.Sql.StmtExecuteOk {
240 }
241
242 RUN ANALYZE TABLE xplugintest.table1
243 Table Op Msg_type Msg_text
244 xplugintest.table1 analyze status OK
245 0 rows affected
246 RUN SHOW INDEX FROM xplugintest.table1
247 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
248 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
249 table1 0 ix 1 $ix_im_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
250 0 rows affected
251 RUN SHOW CREATE TABLE xplugintest.table1
252 Table Create Table
253 table1 CREATE TABLE `table1` (
254 `doc` json DEFAULT NULL,
255 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
256 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
257 `$ix_im_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70` mediumint unsigned GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
258 PRIMARY KEY (`_id`),
259 UNIQUE KEY `ix` (`$ix_im_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
260 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
261 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
262 0 rows affected
263 Mysqlx.Sql.StmtExecuteOk {
264 }
265
266 RUN SHOW INDEX FROM xplugintest.table1
267 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
268 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
269 0 rows affected
270 Mysqlx.Sql.StmtExecuteOk {
271 }
272
273 RUN ANALYZE TABLE xplugintest.table1
274 Table Op Msg_type Msg_text
275 xplugintest.table1 analyze status OK
276 0 rows affected
277 RUN SHOW INDEX FROM xplugintest.table1
278 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
279 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
280 table1 0 ix 1 $ix_im7_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
281 0 rows affected
282 RUN SHOW CREATE TABLE xplugintest.table1
283 Table Create Table
284 table1 CREATE TABLE `table1` (
285 `doc` json DEFAULT NULL,
286 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
287 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
288 `$ix_im7_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70` mediumint unsigned GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
289 PRIMARY KEY (`_id`),
290 UNIQUE KEY `ix` (`$ix_im7_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
291 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
292 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
293 0 rows affected
294 RUN INSERT INTO xplugintest.table1(doc) VALUES('{"col":"12345678","_id":1}')
295
296 1 rows affected
297 RUN SELECT * FROM xplugintest.table1
298 doc _id _json_schema $ix_im7_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70
299 {"_id": 1, "col": "12345678"} 1 {"type": "object"} 12345678
300 0 rows affected
301 RUN DELETE FROM xplugintest.table1
302
303 1 rows affected
304 Mysqlx.Sql.StmtExecuteOk {
305 }
306
307 RUN SHOW INDEX FROM xplugintest.table1
308 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
309 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
310 0 rows affected
311 Mysqlx.Sql.StmtExecuteOk {
312 }
313
314 RUN ANALYZE TABLE xplugintest.table1
315 Table Op Msg_type Msg_text
316 xplugintest.table1 analyze status OK
317 0 rows affected
318 RUN SHOW INDEX FROM xplugintest.table1
319 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
320 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
321 table1 0 ix 1 $ix_i_r_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
322 0 rows affected
323 RUN SHOW CREATE TABLE xplugintest.table1
324 Table Create Table
325 table1 CREATE TABLE `table1` (
326 `doc` json DEFAULT NULL,
327 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
328 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
329 `$ix_i_r_193639E0029FB71DA5916BDF64B75D481FD4CF70` int GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
330 PRIMARY KEY (`_id`),
331 UNIQUE KEY `ix` (`$ix_i_r_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
332 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
333 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
334 0 rows affected
335 Mysqlx.Sql.StmtExecuteOk {
336 }
337
338 RUN SHOW INDEX FROM xplugintest.table1
339 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
340 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
341 0 rows affected
342 Mysqlx.Sql.StmtExecuteOk {
343 }
344
345 RUN ANALYZE TABLE xplugintest.table1
346 Table Op Msg_type Msg_text
347 xplugintest.table1 analyze status OK
348 0 rows affected
349 RUN SHOW INDEX FROM xplugintest.table1
350 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
351 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
352 table1 0 ix 1 $ix_i_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
353 0 rows affected
354 RUN SHOW CREATE TABLE xplugintest.table1
355 Table Create Table
356 table1 CREATE TABLE `table1` (
357 `doc` json DEFAULT NULL,
358 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
359 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
360 `$ix_i_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70` int unsigned GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
361 PRIMARY KEY (`_id`),
362 UNIQUE KEY `ix` (`$ix_i_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
363 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
364 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
365 0 rows affected
366 Mysqlx.Sql.StmtExecuteOk {
367 }
368
369 RUN SHOW INDEX FROM xplugintest.table1
370 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
371 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
372 0 rows affected
373 Mysqlx.Sql.StmtExecuteOk {
374 }
375
376 RUN ANALYZE TABLE xplugintest.table1
377 Table Op Msg_type Msg_text
378 xplugintest.table1 analyze status OK
379 0 rows affected
380 RUN SHOW INDEX FROM xplugintest.table1
381 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
382 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
383 table1 0 ix 1 $ix_i6_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
384 0 rows affected
385 RUN SHOW CREATE TABLE xplugintest.table1
386 Table Create Table
387 table1 CREATE TABLE `table1` (
388 `doc` json DEFAULT NULL,
389 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
390 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
391 `$ix_i6_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70` int unsigned GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
392 PRIMARY KEY (`_id`),
393 UNIQUE KEY `ix` (`$ix_i6_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
394 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
395 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
396 0 rows affected
397 RUN INSERT INTO xplugintest.table1(doc) VALUES('{"col":"1234567","_id":1}')
398
399 1 rows affected
400 RUN SELECT * FROM xplugintest.table1
401 doc _id _json_schema $ix_i6_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70
402 {"_id": 1, "col": "1234567"} 1 {"type": "object"} 1234567
403 0 rows affected
404 RUN DELETE FROM xplugintest.table1
405
406 1 rows affected
407 Mysqlx.Sql.StmtExecuteOk {
408 }
409
410 RUN SHOW INDEX FROM xplugintest.table1
411 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
412 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
413 0 rows affected
414 Mysqlx.Sql.StmtExecuteOk {
415 }
416
417 RUN ANALYZE TABLE xplugintest.table1
418 Table Op Msg_type Msg_text
419 xplugintest.table1 analyze status OK
420 0 rows affected
421 RUN SHOW INDEX FROM xplugintest.table1
422 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
423 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
424 table1 0 ix 1 $ix_ib_r_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
425 0 rows affected
426 RUN SHOW CREATE TABLE xplugintest.table1
427 Table Create Table
428 table1 CREATE TABLE `table1` (
429 `doc` json DEFAULT NULL,
430 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
431 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
432 `$ix_ib_r_193639E0029FB71DA5916BDF64B75D481FD4CF70` bigint GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
433 PRIMARY KEY (`_id`),
434 UNIQUE KEY `ix` (`$ix_ib_r_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
435 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
436 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
437 0 rows affected
438 Mysqlx.Sql.StmtExecuteOk {
439 }
440
441 RUN SHOW INDEX FROM xplugintest.table1
442 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
443 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
444 0 rows affected
445 Mysqlx.Sql.StmtExecuteOk {
446 }
447
448 RUN ANALYZE TABLE xplugintest.table1
449 Table Op Msg_type Msg_text
450 xplugintest.table1 analyze status OK
451 0 rows affected
452 RUN SHOW INDEX FROM xplugintest.table1
453 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
454 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
455 table1 0 ix 1 $ix_ib_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
456 0 rows affected
457 RUN SHOW CREATE TABLE xplugintest.table1
458 Table Create Table
459 table1 CREATE TABLE `table1` (
460 `doc` json DEFAULT NULL,
461 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
462 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
463 `$ix_ib_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70` bigint unsigned GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
464 PRIMARY KEY (`_id`),
465 UNIQUE KEY `ix` (`$ix_ib_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
466 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
467 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
468 0 rows affected
469 Mysqlx.Sql.StmtExecuteOk {
470 }
471
472 RUN SHOW INDEX FROM xplugintest.table1
473 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
474 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
475 0 rows affected
476 Mysqlx.Sql.StmtExecuteOk {
477 }
478
479 RUN ANALYZE TABLE xplugintest.table1
480 Table Op Msg_type Msg_text
481 xplugintest.table1 analyze status OK
482 0 rows affected
483 RUN SHOW INDEX FROM xplugintest.table1
484 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
485 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
486 table1 0 ix 1 $ix_ib13_r_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
487 0 rows affected
488 RUN SHOW CREATE TABLE xplugintest.table1
489 Table Create Table
490 table1 CREATE TABLE `table1` (
491 `doc` json DEFAULT NULL,
492 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
493 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
494 `$ix_ib13_r_193639E0029FB71DA5916BDF64B75D481FD4CF70` bigint GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
495 PRIMARY KEY (`_id`),
496 UNIQUE KEY `ix` (`$ix_ib13_r_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
497 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
498 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
499 0 rows affected
500 RUN INSERT INTO xplugintest.table1(doc) VALUES('{"col":"123456789012345","_id":1}')
501
502 1 rows affected
503 RUN SELECT * FROM xplugintest.table1
504 doc _id _json_schema $ix_ib13_r_193639E0029FB71DA5916BDF64B75D481FD4CF70
505 {"_id": 1, "col": "123456789012345"} 1 {"type": "object"} 123456789012345
506 0 rows affected
507 RUN DELETE FROM xplugintest.table1
508
509 1 rows affected
510 Mysqlx.Sql.StmtExecuteOk {
511 }
512
513 RUN SHOW INDEX FROM xplugintest.table1
514 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
515 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
516 0 rows affected
517 Mysqlx.Sql.StmtExecuteOk {
518 }
519
520 RUN ANALYZE TABLE xplugintest.table1
521 Table Op Msg_type Msg_text
522 xplugintest.table1 analyze status OK
523 0 rows affected
524 RUN SHOW INDEX FROM xplugintest.table1
525 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
526 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
527 table1 0 ix 1 $ix_ib22_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
528 0 rows affected
529 RUN SHOW CREATE TABLE xplugintest.table1
530 Table Create Table
531 table1 CREATE TABLE `table1` (
532 `doc` json DEFAULT NULL,
533 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
534 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
535 `$ix_ib22_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70` bigint unsigned GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
536 PRIMARY KEY (`_id`),
537 UNIQUE KEY `ix` (`$ix_ib22_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
538 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
539 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
540 0 rows affected
541 Mysqlx.Sql.StmtExecuteOk {
542 }
543
544 RUN SHOW INDEX FROM xplugintest.table1
545 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
546 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
547 0 rows affected
548 Mysqlx.Sql.StmtExecuteOk {
549 }
550
551 RUN ANALYZE TABLE xplugintest.table1
552 Table Op Msg_type Msg_text
553 xplugintest.table1 analyze status OK
554 0 rows affected
555 RUN SHOW INDEX FROM xplugintest.table1
556 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
557 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
558 table1 0 ix 1 $ix_fr_r_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
559 0 rows affected
560 RUN SHOW CREATE TABLE xplugintest.table1
561 Table Create Table
562 table1 CREATE TABLE `table1` (
563 `doc` json DEFAULT NULL,
564 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
565 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
566 `$ix_fr_r_193639E0029FB71DA5916BDF64B75D481FD4CF70` double GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
567 PRIMARY KEY (`_id`),
568 UNIQUE KEY `ix` (`$ix_fr_r_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
569 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
570 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
571 0 rows affected
572 Mysqlx.Sql.StmtExecuteOk {
573 }
574
575 RUN SHOW INDEX FROM xplugintest.table1
576 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
577 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
578 0 rows affected
579 Mysqlx.Sql.StmtExecuteOk {
580 }
581
582 RUN ANALYZE TABLE xplugintest.table1
583 Table Op Msg_type Msg_text
584 xplugintest.table1 analyze status OK
585 0 rows affected
586 RUN SHOW INDEX FROM xplugintest.table1
587 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
588 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
589 table1 0 ix 1 $ix_fr_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
590 0 rows affected
591 RUN SHOW CREATE TABLE xplugintest.table1
592 Table Create Table
593 table1 CREATE TABLE `table1` (
594 `doc` json DEFAULT NULL,
595 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
596 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
597 `$ix_fr_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70` double unsigned GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
598 PRIMARY KEY (`_id`),
599 UNIQUE KEY `ix` (`$ix_fr_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
600 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
601 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
602 0 rows affected
603 Mysqlx.Sql.StmtExecuteOk {
604 }
605
606 RUN SHOW INDEX FROM xplugintest.table1
607 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
608 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
609 0 rows affected
610 Mysqlx.Sql.StmtExecuteOk {
611 }
612
613 RUN ANALYZE TABLE xplugintest.table1
614 Table Op Msg_type Msg_text
615 xplugintest.table1 analyze status OK
616 0 rows affected
617 RUN SHOW INDEX FROM xplugintest.table1
618 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
619 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
620 table1 0 ix 1 $ix_f_r_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
621 0 rows affected
622 RUN SHOW CREATE TABLE xplugintest.table1
623 Table Create Table
624 table1 CREATE TABLE `table1` (
625 `doc` json DEFAULT NULL,
626 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
627 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
628 `$ix_f_r_193639E0029FB71DA5916BDF64B75D481FD4CF70` float GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
629 PRIMARY KEY (`_id`),
630 UNIQUE KEY `ix` (`$ix_f_r_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
631 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
632 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
633 0 rows affected
634 Mysqlx.Sql.StmtExecuteOk {
635 }
636
637 RUN SHOW INDEX FROM xplugintest.table1
638 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
639 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
640 0 rows affected
641 Mysqlx.Sql.StmtExecuteOk {
642 }
643
644 RUN ANALYZE TABLE xplugintest.table1
645 Table Op Msg_type Msg_text
646 xplugintest.table1 analyze status OK
647 0 rows affected
648 RUN SHOW INDEX FROM xplugintest.table1
649 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
650 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
651 table1 0 ix 1 $ix_f_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
652 0 rows affected
653 RUN SHOW CREATE TABLE xplugintest.table1
654 Table Create Table
655 table1 CREATE TABLE `table1` (
656 `doc` json DEFAULT NULL,
657 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
658 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
659 `$ix_f_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70` float unsigned GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
660 PRIMARY KEY (`_id`),
661 UNIQUE KEY `ix` (`$ix_f_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
662 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
663 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
664 0 rows affected
665 Mysqlx.Sql.StmtExecuteOk {
666 }
667
668 RUN SHOW INDEX FROM xplugintest.table1
669 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
670 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
671 0 rows affected
672 Mysqlx.Sql.StmtExecuteOk {
673 }
674
675 RUN ANALYZE TABLE xplugintest.table1
676 Table Op Msg_type Msg_text
677 xplugintest.table1 analyze status OK
678 0 rows affected
679 RUN SHOW INDEX FROM xplugintest.table1
680 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
681 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
682 table1 0 ix 1 $ix_fd_r_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
683 0 rows affected
684 RUN SHOW CREATE TABLE xplugintest.table1
685 Table Create Table
686 table1 CREATE TABLE `table1` (
687 `doc` json DEFAULT NULL,
688 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
689 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
690 `$ix_fd_r_193639E0029FB71DA5916BDF64B75D481FD4CF70` double GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
691 PRIMARY KEY (`_id`),
692 UNIQUE KEY `ix` (`$ix_fd_r_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
693 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
694 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
695 0 rows affected
696 Mysqlx.Sql.StmtExecuteOk {
697 }
698
699 RUN SHOW INDEX FROM xplugintest.table1
700 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
701 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
702 0 rows affected
703 Mysqlx.Sql.StmtExecuteOk {
704 }
705
706 RUN ANALYZE TABLE xplugintest.table1
707 Table Op Msg_type Msg_text
708 xplugintest.table1 analyze status OK
709 0 rows affected
710 RUN SHOW INDEX FROM xplugintest.table1
711 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
712 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
713 table1 0 ix 1 $ix_fd_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
714 0 rows affected
715 RUN SHOW CREATE TABLE xplugintest.table1
716 Table Create Table
717 table1 CREATE TABLE `table1` (
718 `doc` json DEFAULT NULL,
719 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
720 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
721 `$ix_fd_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70` double unsigned GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
722 PRIMARY KEY (`_id`),
723 UNIQUE KEY `ix` (`$ix_fd_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
724 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
725 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
726 0 rows affected
727 Mysqlx.Sql.StmtExecuteOk {
728 }
729
730 RUN SHOW INDEX FROM xplugintest.table1
731 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
732 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
733 0 rows affected
734 Mysqlx.Sql.StmtExecuteOk {
735 }
736
737 RUN ANALYZE TABLE xplugintest.table1
738 Table Op Msg_type Msg_text
739 xplugintest.table1 analyze status OK
740 0 rows affected
741 RUN SHOW INDEX FROM xplugintest.table1
742 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
743 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
744 table1 0 ix 1 $ix_fd7_4_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
745 0 rows affected
746 RUN SHOW CREATE TABLE xplugintest.table1
747 Table Create Table
748 table1 CREATE TABLE `table1` (
749 `doc` json DEFAULT NULL,
750 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
751 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
752 `$ix_fd7_4_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70` double(7,4) unsigned GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
753 PRIMARY KEY (`_id`),
754 UNIQUE KEY `ix` (`$ix_fd7_4_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
755 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
756 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
757 0 rows affected
758 RUN INSERT INTO xplugintest.table1(doc) VALUES('{"col":"123.1234","_id":1}')
759
760 1 rows affected
761 RUN SELECT * FROM xplugintest.table1
762 doc _id _json_schema $ix_fd7_4_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70
763 {"_id": 1, "col": "123.1234"} 1 {"type": "object"} 123.1234
764 0 rows affected
765 RUN DELETE FROM xplugintest.table1
766
767 1 rows affected
768 Mysqlx.Sql.StmtExecuteOk {
769 }
770
771 RUN SHOW INDEX FROM xplugintest.table1
772 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
773 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
774 0 rows affected
775 Mysqlx.Sql.StmtExecuteOk {
776 }
777
778 RUN ANALYZE TABLE xplugintest.table1
779 Table Op Msg_type Msg_text
780 xplugintest.table1 analyze status OK
781 0 rows affected
782 RUN SHOW INDEX FROM xplugintest.table1
783 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
784 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
785 table1 0 ix 1 $ix_xd_r_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
786 0 rows affected
787 RUN SHOW CREATE TABLE xplugintest.table1
788 Table Create Table
789 table1 CREATE TABLE `table1` (
790 `doc` json DEFAULT NULL,
791 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
792 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
793 `$ix_xd_r_193639E0029FB71DA5916BDF64B75D481FD4CF70` decimal(10,0) GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
794 PRIMARY KEY (`_id`),
795 UNIQUE KEY `ix` (`$ix_xd_r_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
796 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
797 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
798 0 rows affected
799 Mysqlx.Sql.StmtExecuteOk {
800 }
801
802 RUN SHOW INDEX FROM xplugintest.table1
803 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
804 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
805 0 rows affected
806 Mysqlx.Sql.StmtExecuteOk {
807 }
808
809 RUN ANALYZE TABLE xplugintest.table1
810 Table Op Msg_type Msg_text
811 xplugintest.table1 analyze status OK
812 0 rows affected
813 RUN SHOW INDEX FROM xplugintest.table1
814 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
815 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
816 table1 0 ix 1 $ix_xd10_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
817 0 rows affected
818 RUN SHOW CREATE TABLE xplugintest.table1
819 Table Create Table
820 table1 CREATE TABLE `table1` (
821 `doc` json DEFAULT NULL,
822 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
823 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
824 `$ix_xd10_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70` decimal(10,0) unsigned GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
825 PRIMARY KEY (`_id`),
826 UNIQUE KEY `ix` (`$ix_xd10_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
827 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
828 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
829 0 rows affected
830 RUN INSERT INTO xplugintest.table1(doc) VALUES('{"col":"123456.123","_id":1}')
831
832 1 rows affected
833 RUN SELECT * FROM xplugintest.table1
834 doc _id _json_schema $ix_xd10_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70
835 {"_id": 1, "col": "123456.123"} 1 {"type": "object"} 123456
836 0 rows affected
837 RUN DELETE FROM xplugintest.table1
838
839 1 rows affected
840 Mysqlx.Sql.StmtExecuteOk {
841 }
842
843 RUN SHOW INDEX FROM xplugintest.table1
844 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
845 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
846 0 rows affected
847 Mysqlx.Sql.StmtExecuteOk {
848 }
849
850 RUN ANALYZE TABLE xplugintest.table1
851 Table Op Msg_type Msg_text
852 xplugintest.table1 analyze status OK
853 0 rows affected
854 RUN SHOW INDEX FROM xplugintest.table1
855 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
856 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
857 table1 0 ix 1 $ix_xd8_2_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
858 0 rows affected
859 RUN SHOW CREATE TABLE xplugintest.table1
860 Table Create Table
861 table1 CREATE TABLE `table1` (
862 `doc` json DEFAULT NULL,
863 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
864 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
865 `$ix_xd8_2_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70` decimal(8,2) unsigned GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
866 PRIMARY KEY (`_id`),
867 UNIQUE KEY `ix` (`$ix_xd8_2_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
868 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
869 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
870 0 rows affected
871 RUN INSERT INTO xplugintest.table1(doc) VALUES('{"col":"123456.123","_id":1}')
872
873 1 rows affected
874 RUN SELECT * FROM xplugintest.table1
875 doc _id _json_schema $ix_xd8_2_ur_193639E0029FB71DA5916BDF64B75D481FD4CF70
876 {"_id": 1, "col": "123456.123"} 1 {"type": "object"} 123456.12
877 0 rows affected
878 RUN DELETE FROM xplugintest.table1
879
880 1 rows affected
881 Mysqlx.Sql.StmtExecuteOk {
882 }
883
884 RUN SHOW INDEX FROM xplugintest.table1
885 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
886 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
887 0 rows affected
888 Mysqlx.Sql.StmtExecuteOk {
889 }
890
891 RUN ANALYZE TABLE xplugintest.table1
892 Table Op Msg_type Msg_text
893 xplugintest.table1 analyze status OK
894 0 rows affected
895 RUN SHOW INDEX FROM xplugintest.table1
896 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
897 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
898 table1 0 ix 1 $ix_xn_r_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
899 0 rows affected
900 RUN SHOW CREATE TABLE xplugintest.table1
901 Table Create Table
902 table1 CREATE TABLE `table1` (
903 `doc` json DEFAULT NULL,
904 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
905 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
906 `$ix_xn_r_193639E0029FB71DA5916BDF64B75D481FD4CF70` decimal(10,0) GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
907 PRIMARY KEY (`_id`),
908 UNIQUE KEY `ix` (`$ix_xn_r_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
909 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
910 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
911 0 rows affected
912 Mysqlx.Sql.StmtExecuteOk {
913 }
914
915 RUN SHOW INDEX FROM xplugintest.table1
916 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
917 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
918 0 rows affected
919 Mysqlx.Sql.StmtExecuteOk {
920 }
921
922 RUN ANALYZE TABLE xplugintest.table1
923 Table Op Msg_type Msg_text
924 xplugintest.table1 analyze status OK
925 0 rows affected
926 RUN SHOW INDEX FROM xplugintest.table1
927 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
928 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
929 table1 0 ix 1 $ix_xn10_2_r_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
930 0 rows affected
931 RUN SHOW CREATE TABLE xplugintest.table1
932 Table Create Table
933 table1 CREATE TABLE `table1` (
934 `doc` json DEFAULT NULL,
935 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
936 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
937 `$ix_xn10_2_r_193639E0029FB71DA5916BDF64B75D481FD4CF70` decimal(10,2) GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
938 PRIMARY KEY (`_id`),
939 UNIQUE KEY `ix` (`$ix_xn10_2_r_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
940 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
941 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
942 0 rows affected
943 RUN INSERT INTO xplugintest.table1(doc) VALUES('{"col":"12345.123456","_id":1}')
944
945 1 rows affected
946 RUN SELECT * FROM xplugintest.table1
947 doc _id _json_schema $ix_xn10_2_r_193639E0029FB71DA5916BDF64B75D481FD4CF70
948 {"_id": 1, "col": "12345.123456"} 1 {"type": "object"} 12345.12
949 0 rows affected
950 RUN DELETE FROM xplugintest.table1
951
952 1 rows affected
953 Mysqlx.Sql.StmtExecuteOk {
954 }
955
956 RUN SHOW INDEX FROM xplugintest.table1
957 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
958 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
959 0 rows affected
960 Mysqlx.Sql.StmtExecuteOk {
961 }
962
963 RUN ANALYZE TABLE xplugintest.table1
964 Table Op Msg_type Msg_text
965 xplugintest.table1 analyze status OK
966 0 rows affected
967 RUN SHOW INDEX FROM xplugintest.table1
968 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
969 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
970 table1 0 ix 1 $ix_xn5_r_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
971 0 rows affected
972 RUN SHOW CREATE TABLE xplugintest.table1
973 Table Create Table
974 table1 CREATE TABLE `table1` (
975 `doc` json DEFAULT NULL,
976 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
977 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
978 `$ix_xn5_r_193639E0029FB71DA5916BDF64B75D481FD4CF70` decimal(5,0) GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.col')) VIRTUAL NOT NULL,
979 PRIMARY KEY (`_id`),
980 UNIQUE KEY `ix` (`$ix_xn5_r_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
981 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
982 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
983 0 rows affected
984 RUN INSERT INTO xplugintest.table1(doc) VALUES('{"col":"12345.123456","_id":1}')
985
986 1 rows affected
987 RUN SELECT * FROM xplugintest.table1
988 doc _id _json_schema $ix_xn5_r_193639E0029FB71DA5916BDF64B75D481FD4CF70
989 {"_id": 1, "col": "12345.123456"} 1 {"type": "object"} 12345
990 0 rows affected
991 RUN DELETE FROM xplugintest.table1
992
993 1 rows affected
994 Mysqlx.Sql.StmtExecuteOk {
995 }
996
997 RUN SHOW INDEX FROM xplugintest.table1
998 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
999 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1000 0 rows affected
1001 Mysqlx.Sql.StmtExecuteOk {
1002 }
1003
1004 RUN ANALYZE TABLE xplugintest.table1
1005 Table Op Msg_type Msg_text
1006 xplugintest.table1 analyze status OK
1007 0 rows affected
1008 RUN SHOW INDEX FROM xplugintest.table1
1009 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1010 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1011 table1 0 ix 1 $ix_d_r_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
1012 0 rows affected
1013 RUN SHOW CREATE TABLE xplugintest.table1
1014 Table Create Table
1015 table1 CREATE TABLE `table1` (
1016 `doc` json DEFAULT NULL,
1017 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
1018 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
1019 `$ix_d_r_193639E0029FB71DA5916BDF64B75D481FD4CF70` date GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$.col'))) VIRTUAL NOT NULL,
1020 PRIMARY KEY (`_id`),
1021 UNIQUE KEY `ix` (`$ix_d_r_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
1022 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
1023 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1024 0 rows affected
1025 Mysqlx.Sql.StmtExecuteOk {
1026 }
1027
1028 RUN SHOW INDEX FROM xplugintest.table1
1029 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1030 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1031 0 rows affected
1032 Mysqlx.Sql.StmtExecuteOk {
1033 }
1034
1035 RUN ANALYZE TABLE xplugintest.table1
1036 Table Op Msg_type Msg_text
1037 xplugintest.table1 analyze status OK
1038 0 rows affected
1039 RUN SHOW INDEX FROM xplugintest.table1
1040 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1041 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1042 table1 0 ix 1 $ix_dt_r_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
1043 0 rows affected
1044 RUN SHOW CREATE TABLE xplugintest.table1
1045 Table Create Table
1046 table1 CREATE TABLE `table1` (
1047 `doc` json DEFAULT NULL,
1048 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
1049 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
1050 `$ix_dt_r_193639E0029FB71DA5916BDF64B75D481FD4CF70` time GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$.col'))) VIRTUAL NOT NULL,
1051 PRIMARY KEY (`_id`),
1052 UNIQUE KEY `ix` (`$ix_dt_r_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
1053 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
1054 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1055 0 rows affected
1056 Mysqlx.Sql.StmtExecuteOk {
1057 }
1058
1059 RUN SHOW INDEX FROM xplugintest.table1
1060 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1061 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1062 0 rows affected
1063 Mysqlx.Sql.StmtExecuteOk {
1064 }
1065
1066 RUN ANALYZE TABLE xplugintest.table1
1067 Table Op Msg_type Msg_text
1068 xplugintest.table1 analyze status OK
1069 0 rows affected
1070 RUN SHOW INDEX FROM xplugintest.table1
1071 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1072 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1073 table1 0 ix 1 $ix_dt2_r_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
1074 0 rows affected
1075 RUN SHOW CREATE TABLE xplugintest.table1
1076 Table Create Table
1077 table1 CREATE TABLE `table1` (
1078 `doc` json DEFAULT NULL,
1079 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
1080 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
1081 `$ix_dt2_r_193639E0029FB71DA5916BDF64B75D481FD4CF70` time(2) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$.col'))) VIRTUAL NOT NULL,
1082 PRIMARY KEY (`_id`),
1083 UNIQUE KEY `ix` (`$ix_dt2_r_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
1084 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
1085 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1086 0 rows affected
1087 RUN INSERT INTO xplugintest.table1(doc) VALUES('{"col":"12:13:44","_id":1}')
1088
1089 1 rows affected
1090 RUN SELECT * FROM xplugintest.table1
1091 doc _id _json_schema $ix_dt2_r_193639E0029FB71DA5916BDF64B75D481FD4CF70
1092 {"_id": 1, "col": "12:13:44"} 1 {"type": "object"} 12:13:44
1093 0 rows affected
1094 RUN DELETE FROM xplugintest.table1
1095
1096 1 rows affected
1097 Mysqlx.Sql.StmtExecuteOk {
1098 }
1099
1100 RUN SHOW INDEX FROM xplugintest.table1
1101 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1102 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1103 0 rows affected
1104 Mysqlx.Sql.StmtExecuteOk {
1105 }
1106
1107 RUN ANALYZE TABLE xplugintest.table1
1108 Table Op Msg_type Msg_text
1109 xplugintest.table1 analyze status OK
1110 0 rows affected
1111 RUN SHOW INDEX FROM xplugintest.table1
1112 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1113 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1114 table1 0 ix 1 $ix_ds_r_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
1115 0 rows affected
1116 RUN SHOW CREATE TABLE xplugintest.table1
1117 Table Create Table
1118 table1 CREATE TABLE `table1` (
1119 `doc` json DEFAULT NULL,
1120 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
1121 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
1122 `$ix_ds_r_193639E0029FB71DA5916BDF64B75D481FD4CF70` timestamp GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$.col'))) VIRTUAL NOT NULL,
1123 PRIMARY KEY (`_id`),
1124 UNIQUE KEY `ix` (`$ix_ds_r_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
1125 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
1126 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1127 0 rows affected
1128 Mysqlx.Sql.StmtExecuteOk {
1129 }
1130
1131 RUN SHOW INDEX FROM xplugintest.table1
1132 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1133 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1134 0 rows affected
1135 Mysqlx.Sql.StmtExecuteOk {
1136 }
1137
1138 RUN ANALYZE TABLE xplugintest.table1
1139 Table Op Msg_type Msg_text
1140 xplugintest.table1 analyze status OK
1141 0 rows affected
1142 RUN SHOW INDEX FROM xplugintest.table1
1143 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1144 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1145 table1 0 ix 1 $ix_ds6_r_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
1146 0 rows affected
1147 RUN SHOW CREATE TABLE xplugintest.table1
1148 Table Create Table
1149 table1 CREATE TABLE `table1` (
1150 `doc` json DEFAULT NULL,
1151 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
1152 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
1153 `$ix_ds6_r_193639E0029FB71DA5916BDF64B75D481FD4CF70` timestamp(6) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$.col'))) VIRTUAL NOT NULL,
1154 PRIMARY KEY (`_id`),
1155 UNIQUE KEY `ix` (`$ix_ds6_r_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
1156 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
1157 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1158 0 rows affected
1159 RUN INSERT INTO xplugintest.table1(doc) VALUES('{"col":"2038-01-19 03:14:07.123456","_id":1}')
1160
1161 1 rows affected
1162 RUN SELECT * FROM xplugintest.table1
1163 doc _id _json_schema $ix_ds6_r_193639E0029FB71DA5916BDF64B75D481FD4CF70
1164 {"_id": 1, "col": "2038-01-19 03:14:07.123456"} 1 {"type": "object"} 2038-01-19 03:14:07.123456
1165 0 rows affected
1166 RUN DELETE FROM xplugintest.table1
1167
1168 1 rows affected
1169 Mysqlx.Sql.StmtExecuteOk {
1170 }
1171
1172 RUN SHOW INDEX FROM xplugintest.table1
1173 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1174 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1175 0 rows affected
1176 Mysqlx.Sql.StmtExecuteOk {
1177 }
1178
1179 RUN ANALYZE TABLE xplugintest.table1
1180 Table Op Msg_type Msg_text
1181 xplugintest.table1 analyze status OK
1182 0 rows affected
1183 RUN SHOW INDEX FROM xplugintest.table1
1184 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1185 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1186 table1 0 ix 1 $ix_dd_r_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
1187 0 rows affected
1188 RUN SHOW CREATE TABLE xplugintest.table1
1189 Table Create Table
1190 table1 CREATE TABLE `table1` (
1191 `doc` json DEFAULT NULL,
1192 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
1193 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
1194 `$ix_dd_r_193639E0029FB71DA5916BDF64B75D481FD4CF70` datetime GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$.col'))) VIRTUAL NOT NULL,
1195 PRIMARY KEY (`_id`),
1196 UNIQUE KEY `ix` (`$ix_dd_r_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
1197 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
1198 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1199 0 rows affected
1200 Mysqlx.Sql.StmtExecuteOk {
1201 }
1202
1203 RUN SHOW INDEX FROM xplugintest.table1
1204 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1205 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1206 0 rows affected
1207 Mysqlx.Sql.StmtExecuteOk {
1208 }
1209
1210 RUN ANALYZE TABLE xplugintest.table1
1211 Table Op Msg_type Msg_text
1212 xplugintest.table1 analyze status OK
1213 0 rows affected
1214 RUN SHOW INDEX FROM xplugintest.table1
1215 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1216 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1217 table1 0 ix 1 $ix_dd6_r_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
1218 0 rows affected
1219 RUN SHOW CREATE TABLE xplugintest.table1
1220 Table Create Table
1221 table1 CREATE TABLE `table1` (
1222 `doc` json DEFAULT NULL,
1223 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
1224 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
1225 `$ix_dd6_r_193639E0029FB71DA5916BDF64B75D481FD4CF70` datetime(6) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$.col'))) VIRTUAL NOT NULL,
1226 PRIMARY KEY (`_id`),
1227 UNIQUE KEY `ix` (`$ix_dd6_r_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
1228 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
1229 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1230 0 rows affected
1231 Mysqlx.Sql.StmtExecuteOk {
1232 }
1233
1234 RUN SHOW INDEX FROM xplugintest.table1
1235 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1236 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1237 0 rows affected
1238 Mysqlx.Sql.StmtExecuteOk {
1239 }
1240
1241 RUN ANALYZE TABLE xplugintest.table1
1242 Table Op Msg_type Msg_text
1243 xplugintest.table1 analyze status OK
1244 0 rows affected
1245 RUN SHOW INDEX FROM xplugintest.table1
1246 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1247 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1248 table1 0 ix 1 $ix_dy_r_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
1249 0 rows affected
1250 RUN SHOW CREATE TABLE xplugintest.table1
1251 Table Create Table
1252 table1 CREATE TABLE `table1` (
1253 `doc` json DEFAULT NULL,
1254 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
1255 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
1256 `$ix_dy_r_193639E0029FB71DA5916BDF64B75D481FD4CF70` year GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$.col'))) VIRTUAL NOT NULL,
1257 PRIMARY KEY (`_id`),
1258 UNIQUE KEY `ix` (`$ix_dy_r_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
1259 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
1260 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1261 0 rows affected
1262 Mysqlx.Sql.StmtExecuteOk {
1263 }
1264
1265 RUN SHOW INDEX FROM xplugintest.table1
1266 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1267 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1268 0 rows affected
1269 Mysqlx.Error {
1270 severity: ERROR
1271 code: 5017
1272 msg: "Invalid or unsupported type specification \'VARCHAR(15)\'"
1273 sql_state: "HY000"
1274 }
1275
1276 RUN ANALYZE TABLE xplugintest.table1
1277 Table Op Msg_type Msg_text
1278 xplugintest.table1 analyze status OK
1279 0 rows affected
1280 RUN SHOW INDEX FROM xplugintest.table1
1281 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1282 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1283 0 rows affected
1284 RUN SHOW CREATE TABLE xplugintest.table1
1285 Table Create Table
1286 table1 CREATE TABLE `table1` (
1287 `doc` json DEFAULT NULL,
1288 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
1289 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
1290 PRIMARY KEY (`_id`),
1291 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
1292 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1293 0 rows affected
1294 RUN INSERT INTO xplugintest.table1(doc) VALUES('{"col":"QUITE LOOOONG STRING","_id":1}')
1295
1296 1 rows affected
1297 RUN SELECT * FROM xplugintest.table1
1298 doc _id _json_schema
1299 {"_id": 1, "col": "QUITE LOOOONG STRING"} 1 {"type": "object"}
1300 0 rows affected
1301 RUN DELETE FROM xplugintest.table1
1302
1303 1 rows affected
1304 Mysqlx.Error {
1305 severity: ERROR
1306 code: 1091
1307 msg: "Can\'t DROP \'ix\'; check that column/key exists"
1308 sql_state: "42000"
1309 }
1310
1311 RUN SHOW INDEX FROM xplugintest.table1
1312 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1313 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1314 0 rows affected
1315 Mysqlx.Sql.StmtExecuteOk {
1316 }
1317
1318 RUN ANALYZE TABLE xplugintest.table1
1319 Table Op Msg_type Msg_text
1320 xplugintest.table1 analyze status OK
1321 0 rows affected
1322 RUN SHOW INDEX FROM xplugintest.table1
1323 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1324 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1325 table1 0 ix 1 $ix_c25_r_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
1326 0 rows affected
1327 RUN SHOW CREATE TABLE xplugintest.table1
1328 Table Create Table
1329 table1 CREATE TABLE `table1` (
1330 `doc` json DEFAULT NULL,
1331 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
1332 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
1333 `$ix_c25_r_193639E0029FB71DA5916BDF64B75D481FD4CF70` char(25) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$.col'))) VIRTUAL NOT NULL,
1334 PRIMARY KEY (`_id`),
1335 UNIQUE KEY `ix` (`$ix_c25_r_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
1336 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
1337 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1338 0 rows affected
1339 RUN INSERT INTO xplugintest.table1(doc) VALUES('{"col":"LONG LONG STRING, LONGER THAN INDEX SIZE","_id":1}')
1340 While executing INSERT INTO xplugintest.table1(doc) VALUES('{"col":"LONG LONG STRING, LONGER THAN INDEX SIZE","_id":1}'):
1341 Got expected error: Data too long for column '$ix_c25_r_193639E0029FB71DA5916BDF64B75D481FD4CF70' at row 1 (code 1406)
1342 RUN SELECT * FROM xplugintest.table1
1343 doc _id _json_schema $ix_c25_r_193639E0029FB71DA5916BDF64B75D481FD4CF70
1344 0 rows affected
1345 RUN DELETE FROM xplugintest.table1
1346
1347 0 rows affected
1348 Mysqlx.Sql.StmtExecuteOk {
1349 }
1350
1351 RUN SHOW INDEX FROM xplugintest.table1
1352 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1353 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1354 0 rows affected
1355 Mysqlx.Sql.StmtExecuteOk {
1356 }
1357
1358 RUN ANALYZE TABLE xplugintest.table1
1359 Table Op Msg_type Msg_text
1360 xplugintest.table1 analyze status OK
1361 0 rows affected
1362 RUN SHOW INDEX FROM xplugintest.table1
1363 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1364 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1365 table1 0 ix 1 $ix_t_r_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 null null BTREE YES null
1366 0 rows affected
1367 RUN SHOW CREATE TABLE xplugintest.table1
1368 Table Create Table
1369 table1 CREATE TABLE `table1` (
1370 `doc` json DEFAULT NULL,
1371 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
1372 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
1373 `$ix_t_r_193639E0029FB71DA5916BDF64B75D481FD4CF70` bit(1) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$.col'))) VIRTUAL NOT NULL,
1374 PRIMARY KEY (`_id`),
1375 UNIQUE KEY `ix` (`$ix_t_r_193639E0029FB71DA5916BDF64B75D481FD4CF70`),
1376 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
1377 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1378 0 rows affected
1379 Mysqlx.Sql.StmtExecuteOk {
1380 }
1381
1382 RUN SHOW INDEX FROM xplugintest.table1
1383 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1384 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1385 0 rows affected
1386 Mysqlx.Sql.StmtExecuteOk {
1387 }
1388
1389 RUN ANALYZE TABLE xplugintest.table1
1390 Table Op Msg_type Msg_text
1391 xplugintest.table1 analyze status OK
1392 0 rows affected
1393 RUN SHOW INDEX FROM xplugintest.table1
1394 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1395 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1396 table1 0 ix 1 $ix_bt50_r_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 50 null BTREE YES null
1397 0 rows affected
1398 RUN SHOW CREATE TABLE xplugintest.table1
1399 Table Create Table
1400 table1 CREATE TABLE `table1` (
1401 `doc` json DEFAULT NULL,
1402 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
1403 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
1404 `$ix_bt50_r_193639E0029FB71DA5916BDF64B75D481FD4CF70` tinyblob GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$.col'))) VIRTUAL NOT NULL,
1405 PRIMARY KEY (`_id`),
1406 UNIQUE KEY `ix` (`$ix_bt50_r_193639E0029FB71DA5916BDF64B75D481FD4CF70`(50)),
1407 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
1408 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1409 0 rows affected
1410 RUN INSERT INTO xplugintest.table1(doc) VALUES('{"col":"BLOB BLOB BLOB BLOB BLOB VALUE LONG VALUE 0123456789 BLOB01234567890123456789","_id":1}')
1411
1412 1 rows affected
1413 RUN SELECT * FROM xplugintest.table1
1414 doc _id _json_schema $ix_bt50_r_193639E0029FB71DA5916BDF64B75D481FD4CF70
1415 {"_id": 1, "col": "BLOB BLOB BLOB BLOB BLOB VALUE LONG VALUE 0123456789 BLOB01234567890123456789"} 1 {"type": "object"} BLOB BLOB BLOB BLOB BLOB VALUE LONG VALUE 0123456789 BLOB01234567890123456789
1416 0 rows affected
1417 RUN DELETE FROM xplugintest.table1
1418
1419 1 rows affected
1420 Mysqlx.Sql.StmtExecuteOk {
1421 }
1422
1423 RUN SHOW INDEX FROM xplugintest.table1
1424 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1425 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1426 0 rows affected
1427 Mysqlx.Sql.StmtExecuteOk {
1428 }
1429
1430 RUN ANALYZE TABLE xplugintest.table1
1431 Table Op Msg_type Msg_text
1432 xplugintest.table1 analyze status OK
1433 0 rows affected
1434 RUN SHOW INDEX FROM xplugintest.table1
1435 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1436 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1437 table1 0 ix 1 $ix_t30_r_193639E0029FB71DA5916BDF64B75D481FD4CF70 A 0 30 null BTREE YES null
1438 0 rows affected
1439 RUN SHOW CREATE TABLE xplugintest.table1
1440 Table Create Table
1441 table1 CREATE TABLE `table1` (
1442 `doc` json DEFAULT NULL,
1443 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
1444 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
1445 `$ix_t30_r_193639E0029FB71DA5916BDF64B75D481FD4CF70` text GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$.col'))) VIRTUAL NOT NULL,
1446 PRIMARY KEY (`_id`),
1447 UNIQUE KEY `ix` (`$ix_t30_r_193639E0029FB71DA5916BDF64B75D481FD4CF70`(30)),
1448 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
1449 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1450 0 rows affected
1451 RUN INSERT INTO xplugintest.table1(doc) VALUES('{"col":"TEXT LONGER THAN INDEX SIZE WHICH EQUALS 30 1234567890","_id":1}')
1452
1453 1 rows affected
1454 RUN SELECT * FROM xplugintest.table1
1455 doc _id _json_schema $ix_t30_r_193639E0029FB71DA5916BDF64B75D481FD4CF70
1456 {"_id": 1, "col": "TEXT LONGER THAN INDEX SIZE WHICH EQUALS 30 1234567890"} 1 {"type": "object"} TEXT LONGER THAN INDEX SIZE WHICH EQUALS 30 1234567890
1457 0 rows affected
1458 RUN DELETE FROM xplugintest.table1
1459
1460 1 rows affected
1461 Mysqlx.Sql.StmtExecuteOk {
1462 }
1463
1464 RUN SHOW INDEX FROM xplugintest.table1
1465 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1466 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1467 0 rows affected
1468 Create index on two fields
1469 Mysqlx.Sql.StmtExecuteOk {
1470 }
1471
1472 RUN ANALYZE TABLE xplugintest.table1
1473 Table Op Msg_type Msg_text
1474 xplugintest.table1 analyze status OK
1475 0 rows affected
1476 RUN SHOW INDEX FROM xplugintest.table1
1477 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1478 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1479 table1 0 two_field_index 1 $ix_t10_r_0AD3DA5237EEE3DFC9BAFF8E7071211F65E9713C A 0 10 null BTREE YES null
1480 table1 0 two_field_index 2 $ix_i_r_B8701A5C0B839D9E3336ED0BD89272C2666E1C74 A 0 null null BTREE YES null
1481 0 rows affected
1482 Mysqlx.Sql.StmtExecuteOk {
1483 }
1484
1485 RUN SHOW INDEX FROM xplugintest.table1
1486 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1487 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1488 0 rows affected
1489 Create two indexes using the same field
1490 Mysqlx.Sql.StmtExecuteOk {
1491 }
1492
1493 Mysqlx.Sql.StmtExecuteOk {
1494 }
1495
1496 RUN ANALYZE TABLE xplugintest.table1
1497 Table Op Msg_type Msg_text
1498 xplugintest.table1 analyze status OK
1499 0 rows affected
1500 RUN SHOW INDEX FROM xplugintest.table1
1501 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1502 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1503 table1 0 index1 1 $ix_i_r_391E85E134E06EE3BB933293E26546D764D922BD A 0 null null BTREE YES null
1504 table1 0 index1 2 $ix_i_r_EF67772F9C09859F0E6CC3208EDA759461EA79E2 A 0 null null BTREE YES null
1505 table1 0 index2 1 $ix_i_r_EF67772F9C09859F0E6CC3208EDA759461EA79E2 A 0 null null BTREE YES null
1506 0 rows affected
1507 Drop the first of the indexes, it should keep the shared column
1508 Mysqlx.Sql.StmtExecuteOk {
1509 }
1510
1511 RUN SHOW INDEX FROM xplugintest.table1
1512 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1513 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1514 table1 0 index2 1 $ix_i_r_EF67772F9C09859F0E6CC3208EDA759461EA79E2 A 0 null null BTREE YES null
1515 0 rows affected
1516 Drop the second of the indexes, the column should be gone
1517 Mysqlx.Sql.StmtExecuteOk {
1518 }
1519
1520 RUN SHOW INDEX FROM xplugintest.table1
1521 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1522 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1523 0 rows affected
1524 Error scenarios when dropping the index
1525 Mysqlx.Error {
1526 severity: ERROR
1527 code: 5113
1528 msg: "Invalid collection name: nonexisting.table1"
1529 sql_state: "HY000"
1530 }
1531
1532 Mysqlx.Error {
1533 severity: ERROR
1534 code: 5113
1535 msg: "Invalid collection name: xplugintest.nonexisting"
1536 sql_state: "HY000"
1537 }
1538
1539 Mysqlx.Error {
1540 severity: ERROR
1541 code: 1091
1542 msg: "Can\'t DROP \'nonexisting\'; check that column/key exists"
1543 sql_state: "42000"
1544 }
1545
1546 Mysqlx.Error {
1547 severity: ERROR
1548 code: 5017
1549 msg: "Invalid or unsupported type specification \'TINYBLOB(10)\'"
1550 sql_state: "HY000"
1551 }
1552
1553 RUN SHOW INDEX FROM xplugintest.table1
1554 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1555 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1556 0 rows affected
1557 RUN ALTER TABLE xplugintest.table1 ADD SUBJECT text GENERATED ALWAYS AS (json_extract( doc, '$.subject' )) STORED
1558
1559 0 rows affected
1560 Records: 0 Duplicates: 0 Warnings: 0
1561 RUN ALTER TABLE xplugintest.table1 ADD book text
1562
1563 0 rows affected
1564 Records: 0 Duplicates: 0 Warnings: 0
1565 RUN ALTER TABLE xplugintest.table1 ADD INDEX `fulltext_idx_1` (subject(100))
1566
1567 0 rows affected
1568 Records: 0 Duplicates: 0 Warnings: 0
1569 RUN ALTER TABLE xplugintest.table1 ADD INDEX `fulltext_idx_2` (subject(100))
1570
1571 0 rows affected
1572 Records: 0 Duplicates: 0 Warnings: 1
1573 RUN ALTER TABLE xplugintest.table1 ADD INDEX `fulltext_idx_3` (book(100))
1574
1575 0 rows affected
1576 Records: 0 Duplicates: 0 Warnings: 0
1577 RUN ANALYZE TABLE xplugintest.table1
1578 Table Op Msg_type Msg_text
1579 xplugintest.table1 analyze status OK
1580 0 rows affected
1581 RUN SHOW CREATE TABLE xplugintest.table1
1582 Table Create Table
1583 table1 CREATE TABLE `table1` (
1584 `doc` json DEFAULT NULL,
1585 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
1586 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
1587 `SUBJECT` text GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.subject')) STORED,
1588 `book` text,
1589 PRIMARY KEY (`_id`),
1590 KEY `fulltext_idx_1` (`SUBJECT`(100)),
1591 KEY `fulltext_idx_2` (`SUBJECT`(100)),
1592 KEY `fulltext_idx_3` (`book`(100)),
1593 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
1594 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1595 0 rows affected
1596 RUN SHOW INDEX FROM xplugintest.table1
1597 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1598 table1 0 PRIMARY 1 _id A 0 null null BTREE YES null
1599 table1 1 fulltext_idx_1 1 SUBJECT A 0 100 null YES BTREE YES null
1600 table1 1 fulltext_idx_2 1 SUBJECT A 0 100 null YES BTREE YES null
1601 table1 1 fulltext_idx_3 1 book A 0 100 null YES BTREE YES null
1602 0 rows affected
1603 drop primary index
1604 Mysqlx.Sql.StmtExecuteOk {
1605 }
1606
1607 RUN SHOW CREATE TABLE xplugintest.table1
1608 Table Create Table
1609 table1 CREATE TABLE `table1` (
1610 `doc` json DEFAULT NULL,
1611 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
1612 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
1613 `SUBJECT` text GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.subject')) STORED,
1614 `book` text,
1615 KEY `fulltext_idx_1` (`SUBJECT`(100)),
1616 KEY `fulltext_idx_2` (`SUBJECT`(100)),
1617 KEY `fulltext_idx_3` (`book`(100)),
1618 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
1619 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1620 0 rows affected
1621 Mysqlx.Sql.StmtExecuteOk {
1622 }
1623
1624 Mysqlx.Sql.StmtExecuteOk {
1625 }
1626
1627 Mysqlx.Sql.StmtExecuteOk {
1628 }
1629
1630 RUN SHOW CREATE TABLE xplugintest.table1
1631 Table Create Table
1632 table1 CREATE TABLE `table1` (
1633 `doc` json DEFAULT NULL,
1634 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
1635 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
1636 `SUBJECT` text GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.subject')) STORED,
1637 `book` text,
1638 CONSTRAINT `$val_strict_F722D77E7114627C511CE41D56D327CB09C4F9E5` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
1639 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1640 0 rows affected
1641 RUN ALTER TABLE xplugintest.table1 DROP COLUMN subject
1642
1643 0 rows affected
1644 Records: 0 Duplicates: 0 Warnings: 0
1645 RUN ANALYZE TABLE xplugintest.table1
1646 Table Op Msg_type Msg_text
1647 xplugintest.table1 analyze status OK
1648 0 rows affected
1649 RUN SHOW INDEX FROM xplugintest.table1
1650 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1651 0 rows affected
1652 RUN CREATE TABLE xplugintest.table2 (
1653 ID TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
1654 Name VARCHAR(255) NOT NULL,
1655 LastName VARCHAR(255) NOT NULL,
1656 UNIQUE `idx1` (Name),
1657 UNIQUE `idx2` (LastName)
1658 )
1659
1660 0 rows affected
1661 drop index from data_model=TABLE
1662 Mysqlx.Sql.StmtExecuteOk {
1663 }
1664
1665 Error can not drop primary index when column defined as auto_increment
1666 Got expected error: Incorrect table definition; there can be only one auto column and it must be defined as a key (code 1075)
1667 RUN SHOW CREATE TABLE xplugintest.table2
1668 Table Create Table
1669 table2 CREATE TABLE `table2` (
1670 `ID` tinyint NOT NULL AUTO_INCREMENT,
1671 `Name` varchar(255) NOT NULL,
1672 `LastName` varchar(255) NOT NULL,
1673 PRIMARY KEY (`ID`),
1674 UNIQUE KEY `idx2` (`LastName`)
1675 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1676 0 rows affected
1677 drop index using namespace=sql
1678 send Mysqlx.Sql.StmtExecute {
1679 stmt: "ALTER TABLE xplugintest.table2 DROP INDEX `idx2`"
1680 namespace: "sql"
1681 }
1682
1683
1684 0 rows affected
1685 Records: 0 Duplicates: 0 Warnings: 0
1686 RUN ANALYZE TABLE xplugintest.table2
1687 Table Op Msg_type Msg_text
1688 xplugintest.table2 analyze status OK
1689 0 rows affected
1690 RUN SHOW INDEX FROM xplugintest.table2
1691 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
1692 table2 0 PRIMARY 1 ID A 0 null null BTREE YES null
1693 0 rows affected
1694 RUN DROP DATABASE IF EXISTS xplugintest
1695
1696 2 rows affected
1697 Mysqlx.Ok {
1698 msg: "bye!"
1699 }
1700 ok