Debian Codegraph @

mysql-test/suite/innodb/r/multi_value_debug.result

raw
1 CREATE TABLE t1 (id INT NOT NULL KEY AUTO_INCREMENT, f1 JSON);
2 CREATE INDEX i1 ON t1((CAST(f1->"$[*]" AS DECIMAL(4,2) ARRAY)));
3 CREATE INDEX i2 ON t1((CAST(f1->"$[*]" AS CHAR(10) ARRAY)));
4 set debug = '+d, row_ins_sec_index_entry_lock_wait';
5 INSERT INTO t1(f1) VALUES(CAST('["11.11", "22.22", "33.33", "44.44", "55.55", "66.66", "77.77", "88.88", "99.99"]' AS JSON));
6 SELECT * FROM t1;
7 id f1
8 1 ["11.11", "22.22", "33.33", "44.44", "55.55", "66.66", "77.77", "88.88", "99.99"]
9 SELECT * FROM t1 WHERE 11.11 MEMBER OF (f1->'$[*]');
10 id f1
11 1 ["11.11", "22.22", "33.33", "44.44", "55.55", "66.66", "77.77", "88.88", "99.99"]
12 SELECT * FROM t1 WHERE 22.22 MEMBER OF (f1->'$[*]');
13 id f1
14 1 ["11.11", "22.22", "33.33", "44.44", "55.55", "66.66", "77.77", "88.88", "99.99"]
15 SELECT * FROM t1 WHERE 33.33 MEMBER OF (f1->'$[*]');
16 id f1
17 1 ["11.11", "22.22", "33.33", "44.44", "55.55", "66.66", "77.77", "88.88", "99.99"]
18 SELECT * FROM t1 WHERE 44.44 MEMBER OF (f1->'$[*]');
19 id f1
20 1 ["11.11", "22.22", "33.33", "44.44", "55.55", "66.66", "77.77", "88.88", "99.99"]
21 SELECT * FROM t1 WHERE 55.55 MEMBER OF (f1->'$[*]');
22 id f1
23 1 ["11.11", "22.22", "33.33", "44.44", "55.55", "66.66", "77.77", "88.88", "99.99"]
24 SELECT * FROM t1 WHERE 66.66 MEMBER OF (f1->'$[*]');
25 id f1
26 1 ["11.11", "22.22", "33.33", "44.44", "55.55", "66.66", "77.77", "88.88", "99.99"]
27 SELECT * FROM t1 WHERE 77.77 MEMBER OF (f1->'$[*]');
28 id f1
29 1 ["11.11", "22.22", "33.33", "44.44", "55.55", "66.66", "77.77", "88.88", "99.99"]
30 SELECT * FROM t1 WHERE 88.88 MEMBER OF (f1->'$[*]');
31 id f1
32 1 ["11.11", "22.22", "33.33", "44.44", "55.55", "66.66", "77.77", "88.88", "99.99"]
33 SELECT * FROM t1 WHERE 99.99 MEMBER OF (f1->'$[*]');
34 id f1
35 1 ["11.11", "22.22", "33.33", "44.44", "55.55", "66.66", "77.77", "88.88", "99.99"]
36 TRUNCATE TABLE t1;
37 INSERT INTO t1(f1) VALUES(CAST('["13.33","13.21"]' AS JSON));
38 INSERT INTO t1(f1) VALUES(CAST('["15.67","18.55"]' AS JSON));
39 SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
40 id f1
41 1 ["13.33", "13.21"]
42 SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
43 id f1
44 1 ["13.33", "13.21"]
45 SELECT * FROM t1 WHERE 15.67 MEMBER OF (f1->'$[*]');
46 id f1
47 2 ["15.67", "18.55"]
48 SELECT * FROM t1 WHERE 18.55 MEMBER OF (f1->'$[*]');
49 id f1
50 2 ["15.67", "18.55"]
51 SELECT * FROM t1 WHERE "13.33" MEMBER OF (f1->'$[*]');
52 id f1
53 1 ["13.33", "13.21"]
54 SELECT * FROM t1 WHERE "13.21" MEMBER OF (f1->'$[*]');
55 id f1
56 1 ["13.33", "13.21"]
57 SELECT * FROM t1 WHERE "15.67" MEMBER OF (f1->'$[*]');
58 id f1
59 2 ["15.67", "18.55"]
60 SELECT * FROM t1 WHERE "18.55" MEMBER OF (f1->'$[*]');
61 id f1
62 2 ["15.67", "18.55"]
63 set debug = '+d, row_upd_sec_index_entry_lock_wait';
64 DELETE FROM t1;
65 SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
66 id f1
67 SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
68 id f1
69 SELECT * FROM t1 WHERE 15.67 MEMBER OF (f1->'$[*]');
70 id f1
71 SELECT * FROM t1 WHERE 18.55 MEMBER OF (f1->'$[*]');
72 id f1
73 SELECT * FROM t1 WHERE "13.33" MEMBER OF (f1->'$[*]');
74 id f1
75 SELECT * FROM t1 WHERE "13.21" MEMBER OF (f1->'$[*]');
76 id f1
77 SELECT * FROM t1 WHERE "15.67" MEMBER OF (f1->'$[*]');
78 id f1
79 SELECT * FROM t1 WHERE "18.55" MEMBER OF (f1->'$[*]');
80 id f1
81 INSERT INTO t1(f1) VALUES(CAST('["13.33","13.21","13.45"]' AS JSON));
82 INSERT INTO t1(f1) VALUES(CAST('["15.67","18.55","19.32"]' AS JSON));
83 set debug = '-d, row_upd_sec_index_entry_lock_wait';
84 set debug = '+d, delete_one_multi_sec_index_entry_lock_wait';
85 UPDATE t1 SET f1 = CAST('["98.76","87.65","76.54"]' AS JSON) WHERE 13.33 MEMBER OF (f1->'$[*]');
86 SELECT * FROM t1;
87 id f1
88 3 ["98.76", "87.65", "76.54"]
89 4 ["15.67", "18.55", "19.32"]
90 SELECT * FROM t1 WHERE 98.76 MEMBER OF (f1->'$[*]');
91 id f1
92 3 ["98.76", "87.65", "76.54"]
93 SELECT * FROM t1 WHERE 87.65 MEMBER OF (f1->'$[*]');
94 id f1
95 3 ["98.76", "87.65", "76.54"]
96 SELECT * FROM t1 WHERE 76.54 MEMBER OF (f1->'$[*]');
97 id f1
98 3 ["98.76", "87.65", "76.54"]
99 SELECT * FROM t1 WHERE 15.67 MEMBER OF (f1->'$[*]');
100 id f1
101 4 ["15.67", "18.55", "19.32"]
102 SELECT * FROM t1 WHERE 18.55 MEMBER OF (f1->'$[*]');
103 id f1
104 4 ["15.67", "18.55", "19.32"]
105 SELECT * FROM t1 WHERE 19.32 MEMBER OF (f1->'$[*]');
106 id f1
107 4 ["15.67", "18.55", "19.32"]
108 SELECT * FROM t1 WHERE 13.45 MEMBER OF (f1->'$[*]');
109 id f1
110 SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
111 id f1
112 SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
113 id f1
114 SELECT * FROM t1 WHERE "98.76" MEMBER OF (f1->'$[*]');
115 id f1
116 3 ["98.76", "87.65", "76.54"]
117 SELECT * FROM t1 WHERE "87.65" MEMBER OF (f1->'$[*]');
118 id f1
119 3 ["98.76", "87.65", "76.54"]
120 SELECT * FROM t1 WHERE "76.54" MEMBER OF (f1->'$[*]');
121 id f1
122 3 ["98.76", "87.65", "76.54"]
123 SELECT * FROM t1 WHERE "15.67" MEMBER OF (f1->'$[*]');
124 id f1
125 4 ["15.67", "18.55", "19.32"]
126 SELECT * FROM t1 WHERE "18.55" MEMBER OF (f1->'$[*]');
127 id f1
128 4 ["15.67", "18.55", "19.32"]
129 SELECT * FROM t1 WHERE "19.32" MEMBER OF (f1->'$[*]');
130 id f1
131 4 ["15.67", "18.55", "19.32"]
132 SELECT * FROM t1 WHERE "13.45" MEMBER OF (f1->'$[*]');
133 id f1
134 SELECT * FROM t1 WHERE "13.21" MEMBER OF (f1->'$[*]');
135 id f1
136 SELECT * FROM t1 WHERE "13.33" MEMBER OF (f1->'$[*]');
137 id f1
138 UPDATE t1 SET f1 = CAST('["11.11","22.22","33.33"]' AS JSON) WHERE "18.55" MEMBER OF (f1->'$[*]');
139 SELECT * FROM t1;
140 id f1
141 3 ["98.76", "87.65", "76.54"]
142 4 ["11.11", "22.22", "33.33"]
143 SELECT * FROM t1 WHERE 98.76 MEMBER OF (f1->'$[*]');
144 id f1
145 3 ["98.76", "87.65", "76.54"]
146 SELECT * FROM t1 WHERE 87.65 MEMBER OF (f1->'$[*]');
147 id f1
148 3 ["98.76", "87.65", "76.54"]
149 SELECT * FROM t1 WHERE 76.54 MEMBER OF (f1->'$[*]');
150 id f1
151 3 ["98.76", "87.65", "76.54"]
152 SELECT * FROM t1 WHERE 11.11 MEMBER OF (f1->'$[*]');
153 id f1
154 4 ["11.11", "22.22", "33.33"]
155 SELECT * FROM t1 WHERE 22.22 MEMBER OF (f1->'$[*]');
156 id f1
157 4 ["11.11", "22.22", "33.33"]
158 SELECT * FROM t1 WHERE 33.33 MEMBER OF (f1->'$[*]');
159 id f1
160 4 ["11.11", "22.22", "33.33"]
161 SELECT * FROM t1 WHERE 19.32 MEMBER OF (f1->'$[*]');
162 id f1
163 SELECT * FROM t1 WHERE 15.67 MEMBER OF (f1->'$[*]');
164 id f1
165 SELECT * FROM t1 WHERE 18.55 MEMBER OF (f1->'$[*]');
166 id f1
167 SELECT * FROM t1 WHERE "98.76" MEMBER OF (f1->'$[*]');
168 id f1
169 3 ["98.76", "87.65", "76.54"]
170 SELECT * FROM t1 WHERE "87.65" MEMBER OF (f1->'$[*]');
171 id f1
172 3 ["98.76", "87.65", "76.54"]
173 SELECT * FROM t1 WHERE "76.54" MEMBER OF (f1->'$[*]');
174 id f1
175 3 ["98.76", "87.65", "76.54"]
176 SELECT * FROM t1 WHERE "11.11" MEMBER OF (f1->'$[*]');
177 id f1
178 4 ["11.11", "22.22", "33.33"]
179 SELECT * FROM t1 WHERE "22.22" MEMBER OF (f1->'$[*]');
180 id f1
181 4 ["11.11", "22.22", "33.33"]
182 SELECT * FROM t1 WHERE "33.33" MEMBER OF (f1->'$[*]');
183 id f1
184 4 ["11.11", "22.22", "33.33"]
185 SELECT * FROM t1 WHERE "19.32" MEMBER OF (f1->'$[*]');
186 id f1
187 SELECT * FROM t1 WHERE "15.67" MEMBER OF (f1->'$[*]');
188 id f1
189 SELECT * FROM t1 WHERE "18.55" MEMBER OF (f1->'$[*]');
190 id f1
191 CHECK TABLE t1;
192 Table Op Msg_type Msg_text
193 test.t1 check status OK
194 DELETE FROM t1;
195 INSERT INTO t1(f1) VALUES(CAST('["13.33","13.21","13.45"]' AS JSON));
196 INSERT INTO t1(f1) VALUES(CAST('["15.67","18.55","19.32"]' AS JSON));
197 set debug = '-d, delete_one_multi_sec_index_entry_lock_wait';
198 set debug = '+d, row_ins_sec_index_entry_lock_wait';
199 UPDATE t1 SET f1 = CAST('["98.76","87.65","76.54"]' AS JSON) WHERE 13.33 MEMBER OF (f1->'$[*]');
200 SELECT * FROM t1;
201 id f1
202 5 ["98.76", "87.65", "76.54"]
203 6 ["15.67", "18.55", "19.32"]
204 SELECT * FROM t1 WHERE 98.76 MEMBER OF (f1->'$[*]');
205 id f1
206 5 ["98.76", "87.65", "76.54"]
207 SELECT * FROM t1 WHERE 87.65 MEMBER OF (f1->'$[*]');
208 id f1
209 5 ["98.76", "87.65", "76.54"]
210 SELECT * FROM t1 WHERE 76.54 MEMBER OF (f1->'$[*]');
211 id f1
212 5 ["98.76", "87.65", "76.54"]
213 SELECT * FROM t1 WHERE 15.67 MEMBER OF (f1->'$[*]');
214 id f1
215 6 ["15.67", "18.55", "19.32"]
216 SELECT * FROM t1 WHERE 18.55 MEMBER OF (f1->'$[*]');
217 id f1
218 6 ["15.67", "18.55", "19.32"]
219 SELECT * FROM t1 WHERE 19.32 MEMBER OF (f1->'$[*]');
220 id f1
221 6 ["15.67", "18.55", "19.32"]
222 SELECT * FROM t1 WHERE 13.45 MEMBER OF (f1->'$[*]');
223 id f1
224 SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
225 id f1
226 SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
227 id f1
228 SELECT * FROM t1 WHERE "98.76" MEMBER OF (f1->'$[*]');
229 id f1
230 5 ["98.76", "87.65", "76.54"]
231 SELECT * FROM t1 WHERE "87.65" MEMBER OF (f1->'$[*]');
232 id f1
233 5 ["98.76", "87.65", "76.54"]
234 SELECT * FROM t1 WHERE "76.54" MEMBER OF (f1->'$[*]');
235 id f1
236 5 ["98.76", "87.65", "76.54"]
237 SELECT * FROM t1 WHERE "15.67" MEMBER OF (f1->'$[*]');
238 id f1
239 6 ["15.67", "18.55", "19.32"]
240 SELECT * FROM t1 WHERE "18.55" MEMBER OF (f1->'$[*]');
241 id f1
242 6 ["15.67", "18.55", "19.32"]
243 SELECT * FROM t1 WHERE "19.32" MEMBER OF (f1->'$[*]');
244 id f1
245 6 ["15.67", "18.55", "19.32"]
246 SELECT * FROM t1 WHERE "13.45" MEMBER OF (f1->'$[*]');
247 id f1
248 SELECT * FROM t1 WHERE "13.21" MEMBER OF (f1->'$[*]');
249 id f1
250 SELECT * FROM t1 WHERE "13.33" MEMBER OF (f1->'$[*]');
251 id f1
252 UPDATE t1 SET f1 = CAST('["11.11","22.22","33.33"]' AS JSON) WHERE "18.55" MEMBER OF (f1->'$[*]');
253 SELECT * FROM t1;
254 id f1
255 5 ["98.76", "87.65", "76.54"]
256 6 ["11.11", "22.22", "33.33"]
257 SELECT * FROM t1 WHERE 98.76 MEMBER OF (f1->'$[*]');
258 id f1
259 5 ["98.76", "87.65", "76.54"]
260 SELECT * FROM t1 WHERE 87.65 MEMBER OF (f1->'$[*]');
261 id f1
262 5 ["98.76", "87.65", "76.54"]
263 SELECT * FROM t1 WHERE 76.54 MEMBER OF (f1->'$[*]');
264 id f1
265 5 ["98.76", "87.65", "76.54"]
266 SELECT * FROM t1 WHERE 11.11 MEMBER OF (f1->'$[*]');
267 id f1
268 6 ["11.11", "22.22", "33.33"]
269 SELECT * FROM t1 WHERE 22.22 MEMBER OF (f1->'$[*]');
270 id f1
271 6 ["11.11", "22.22", "33.33"]
272 SELECT * FROM t1 WHERE 33.33 MEMBER OF (f1->'$[*]');
273 id f1
274 6 ["11.11", "22.22", "33.33"]
275 SELECT * FROM t1 WHERE 19.32 MEMBER OF (f1->'$[*]');
276 id f1
277 SELECT * FROM t1 WHERE 15.67 MEMBER OF (f1->'$[*]');
278 id f1
279 SELECT * FROM t1 WHERE 18.55 MEMBER OF (f1->'$[*]');
280 id f1
281 SELECT * FROM t1 WHERE "98.76" MEMBER OF (f1->'$[*]');
282 id f1
283 5 ["98.76", "87.65", "76.54"]
284 SELECT * FROM t1 WHERE "87.65" MEMBER OF (f1->'$[*]');
285 id f1
286 5 ["98.76", "87.65", "76.54"]
287 SELECT * FROM t1 WHERE "76.54" MEMBER OF (f1->'$[*]');
288 id f1
289 5 ["98.76", "87.65", "76.54"]
290 SELECT * FROM t1 WHERE "11.11" MEMBER OF (f1->'$[*]');
291 id f1
292 6 ["11.11", "22.22", "33.33"]
293 SELECT * FROM t1 WHERE "22.22" MEMBER OF (f1->'$[*]');
294 id f1
295 6 ["11.11", "22.22", "33.33"]
296 SELECT * FROM t1 WHERE "33.33" MEMBER OF (f1->'$[*]');
297 id f1
298 6 ["11.11", "22.22", "33.33"]
299 SELECT * FROM t1 WHERE "19.32" MEMBER OF (f1->'$[*]');
300 id f1
301 SELECT * FROM t1 WHERE "15.67" MEMBER OF (f1->'$[*]');
302 id f1
303 SELECT * FROM t1 WHERE "18.55" MEMBER OF (f1->'$[*]');
304 id f1
305 set debug = '-d, row_ins_sec_index_entry_lock_wait';
306 CHECK TABLE t1;
307 Table Op Msg_type Msg_text
308 test.t1 check status OK
309 DELETE FROM t1;
310 DROP TABLE t1;
311 CREATE TABLE t1(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, f1 JSON);
312 INSERT INTO t1(f1) VALUES (CAST('[1,3]' AS JSON));
313 INSERT INTO t1(f1) VALUES (CAST('[1,3,9,8]' AS JSON));
314 INSERT INTO t1(f1) VALUES (CAST('[1,3,9,8,10,11,20,30]' AS JSON));
315 SET DEBUG='+d, ddl_add_multi_value';
316 CREATE INDEX idx1 ON t1((CAST(f1->"$[*]" AS UNSIGNED ARRAY)));
317 SELECT * FROM t1 WHERE 9 MEMBER OF (f1->'$[*]');
318 id f1
319 2 [1, 3, 9, 8]
320 3 [1, 3, 9, 8, 10, 11, 20, 30]
321 SELECT * FROM t1 WHERE 11 MEMBER OF (f1->'$[*]');
322 id f1
323 3 [1, 3, 9, 8, 10, 11, 20, 30]
324 SELECT * FROM t1 WHERE 30 MEMBER OF (f1->'$[*]');
325 id f1
326 3 [1, 3, 9, 8, 10, 11, 20, 30]
327 SET DEBUG='-d, ddl_add_multi_value';
328 CHECK TABLE t1;
329 Table Op Msg_type Msg_text
330 test.t1 check status OK
331 DROP TABLE t1;
332 CREATE TABLE t1(j JSON);
333 INSERT INTO t1 VALUES ('["test"]'), ('[]'), ('null');
334 SET DEBUG_SYNC='innodb_commit_inplace_alter_table_enter SIGNAL rolling_back WAIT_FOR drop_created';
335 CREATE INDEX mv_idx_date ON t1 ((CAST(j->'$' AS UNSIGNED ARRAY)));;
336 SET DEBUG_SYNC='now WAIT_FOR rolling_back';
337 SET DEBUG_SYNC='row_search_for_mysql_before_return SIGNAL scanning WAIT_FOR continue_scan';
338 SELECT * FROM t1;;
339 SET DEBUG_SYNC='now WAIT_FOR scanning';
340 SET DEBUG_SYNC='now SIGNAL drop_created';
341 ERROR 22018: Invalid JSON value for CAST for functional index 'mv_idx_date'.
342 SET DEBUG_SYNC='now SIGNAL continue_scan';
343 j
344 ["test"]
345 []
346 null
347 INSERT INTO t1 VALUES ('["Hello"]');
348 CHECK TABLE t1;
349 Table Op Msg_type Msg_text
350 test.t1 check status OK
351 DROP TABLE t1;
352 CREATE TABLE t1 (
353 i INT NOT NULL PRIMARY KEY,
354 j JSON DEFAULT (CAST('["HelloWorld"]' AS JSON)),
355 KEY mv_idx_binary (( CAST(j->'$[*]' AS BINARY(10) ARRAY) )) );
356 INSERT INTO t1 VALUES(1, CAST('["HelloWorld", "Hello"]' AS JSON));
357 INSERT INTO t1 VALUES(2, CAST('["HelloMySQL", "Hello"]' AS JSON));
358 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
359 i j
360 2 ["HelloMySQL", "Hello"]
361 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
362 i j
363 1 ["HelloWorld", "Hello"]
364 2 ["HelloMySQL", "Hello"]
365 SET GLOBAL innodb_purge_stop_now = ON;
366 DELETE FROM t1;
367 START TRANSACTION;
368 INSERT INTO t1 VALUES(1, CAST('["HelloMySQL", "Hello"]' AS JSON));
369 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
370 i j
371 1 ["HelloMySQL", "Hello"]
372 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
373 i j
374 1 ["HelloMySQL", "Hello"]
375 ROLLBACK;
376 SET GLOBAL innodb_purge_run_now=ON;
377 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
378 i j
379 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
380 i j
381 CHECK TABLE t1;
382 Table Op Msg_type Msg_text
383 test.t1 check status OK
384 DROP TABLE t1;
385 CREATE TABLE t1 (id INT NOT NULL KEY AUTO_INCREMENT, f1 JSON, f2 JSON, f3 JSON, f4 JSON , f5 JSON, f6 JSON,
386 KEY mv_idx_binary (( CAST(f1->'$[*]' AS CHAR(10) ARRAY) )),
387 KEY mv_idx_binary1 (( CAST(f2->'$[*]' AS CHAR(10) ARRAY) )),
388 KEY mv_idx_binary2 (( CAST(f3->'$[*]' AS CHAR(10) ARRAY) )),
389 KEY mv_idx_binary3 (( CAST(f4->'$[*]' AS CHAR(10) ARRAY) )),
390 KEY mv_idx_binary4 (( CAST(f5->'$[*]' AS CHAR(10) ARRAY) )),
391 KEY mv_idx_binary5 (( CAST(f6->'$[*]' AS CHAR(10) ARRAY) )));
392 INSERT INTO t1 VALUES(1, CAST('["HelloWorld", "Hello"]' AS JSON), CAST('["HelloWorld", "Hello"]' AS JSON), CAST('["HelloWorld", "Hello"]' AS JSON), CAST('["HelloWorld", "Hello"]' AS JSON), CAST('["HelloWorld", "Hello"]' AS JSON), CAST('["HelloWorld", "Hello"]' AS JSON));
393 SET GLOBAL innodb_purge_stop_now = ON;
394 DELETE FROM t1;
395 INSERT INTO t1 VALUES(1, CAST('["Helloworl", "hell"]' AS JSON), CAST('["Helloworl", "hell"]' AS JSON), CAST('["helloworl", "hell"]' AS JSON), CAST('["helloworl", "hell"]' AS JSON), CAST('["helloworl", "well"]' AS JSON), CAST('["helloworl", "hell"]' AS JSON));
396 CHECK TABLE t1;
397 Table Op Msg_type Msg_text
398 test.t1 check status OK
399 DELETE FROM t1;
400 CHECK TABLE t1;
401 Table Op Msg_type Msg_text
402 test.t1 check status OK
403 DROP TABLE t1;
404 SET GLOBAL innodb_purge_run_now=ON;
405 CREATE TABLE IF NOT EXISTS t1 (
406 i INT NOT NULL PRIMARY KEY,
407 j JSON DEFAULT (CAST('["100"]' AS JSON)),
408 UNIQUE KEY mv_idx_binary (( CAST(j->'$[*]' AS UNSIGNED ARRAY) )) );
409 INSERT INTO t1 VALUES(1, CAST('["90"]' AS JSON));
410 START TRANSACTION;
411 INSERT INTO t1 VALUES(2, CAST('["100"]' AS JSON));
412 START TRANSACTION;
413 SET DEBUG_SYNC='lock_rec_convert_impl_to_expl SIGNAL converting WAIT_FOR go';
414 INSERT INTO t1 VALUES(3, CAST('["100"]' AS JSON));;
415 SET DEBUG_SYNC='now WAIT_FOR converting';
416 COMMIT;
417 SET DEBUG_SYNC='now SIGNAL go';
418 ERROR 23000: Duplicate entry '[100]' for key 't1.mv_idx_binary'
419 START TRANSACTION;
420 SET DEBUG_SYNC='row_update_for_mysql_error SIGNAL del_fail WAIT_FOR delete_go';
421 DELETE FROM t1;;
422 SET DEBUG_SYNC='now WAIT_FOR del_fail';
423 INSERT INTO t1 VALUES(4, CAST('["100"]' AS JSON));
424 ERROR 23000: Duplicate entry '[100]' for key 't1.mv_idx_binary'
425 SET DEBUG_SYNC='now SIGNAL delete_go';
426 COMMIT;
427 COMMIT;
428 CHECK TABLE t1;
429 Table Op Msg_type Msg_text
430 test.t1 check status OK
431 SELECT * FROM t1;
432 i j
433 DROP TABLE t1;
434 CREATE TABLE t1 (a INT, b INT, f JSON,
435 KEY mv_idx_binary ((( CAST(f->'$[*]' AS BINARY(10) ARRAY ))))
436 ) ENGINE=InnoDB;
437 INSERT INTO t1 (a,b,f) VALUES(1, 1, '["foobar"]');
438 CREATE TABLE t0 (a INT) ENGINE=InnoDB;
439 BEGIN;
440 SELECT * FROM t0;
441 a
442 UPDATE t1 SET f = NULL;
443 ALTER TABLE t1 DROP INDEX mv_idx_binary, ALGORITHM=INPLACE;
444 COMMIT;
445 UPDATE t1 SET a = 1;
446 set global innodb_purge_run_now=ON;
447 CHECK TABLE t1;
448 Table Op Msg_type Msg_text
449 test.t1 check status OK
450 SELECT * FROM t1;
451 a b f
452 1 1 NULL
453 DROP TABLE t0, t1;
454 CREATE TABLE t1(a INT PRIMARY KEY, b INT, f1 JSON) ENGINE=InnoDB;
455 CREATE INDEX i1 ON t1((CAST(f1->"$[*]" AS CHAR(10) ARRAY)));
456 INSERT INTO t1 VALUES(1, 1, CAST('["1111", "2222"]' AS JSON));
457 INSERT INTO t1 VALUES(2, 2, CAST('["11111", "2222", "22222", "3333"]' AS JSON));
458 INSERT INTO t1 VALUES(3, 3, CAST('["1111", "2222", "22222", "3333", "444"]' AS JSON));
459 INSERT INTO t1 VALUES(4, 4, CAST('["22222", "3333", "444", "5555"]' AS JSON));
460 SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL before_apply WAIT_FOR apply';
461 OPTIMIZE TABLE t1;;
462 connection con1
463 SET DEBUG_SYNC='now WAIT_FOR before_apply';
464 INSERT INTO t1 VALUES(5, 5, CAST('["2222", "5555"]' AS JSON));
465 DELETE FROM t1 WHERE "444" MEMBER OF (f1->"$[*]");
466 INSERT INTO t1 VALUES(6, 6, CAST('["1111", "444"]' AS JSON));
467 UPDATE t1 SET f1 = CAST('["11111", "2222"]' AS JSON) WHERE a = 1;
468 UPDATE t1 SET f1 = CAST('["11111", "22222", "33333", "44444"]' AS JSON) WHERE a = 3;
469 UPDATE t1 SET f1 = CAST('["2222", "5555", "6666"]' AS JSON) WHERE a = 5;
470 SET DEBUG_SYNC='now SIGNAL apply';
471 connection default
472 /* reap */ OPTIMIZE TABLE t1;
473 Table Op Msg_type Msg_text
474 test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
475 test.t1 optimize status OK
476 CHECK TABLE t1;
477 Table Op Msg_type Msg_text
478 test.t1 check status OK
479 SELECT * FROM t1;
480 a b f1
481 1 1 ["11111", "2222"]
482 2 2 ["11111", "2222", "22222", "3333"]
483 5 5 ["2222", "5555", "6666"]
484 6 6 ["1111", "444"]
485 SELECT * FROM t1 WHERE "1111" MEMBER OF (f1->"$[*]");
486 a b f1
487 6 6 ["1111", "444"]
488 SELECT * FROM t1 WHERE "5555" MEMBER OF (f1->"$[*]");
489 a b f1
490 5 5 ["2222", "5555", "6666"]
491 SELECT * FROM t1 WHERE "444" MEMBER OF (f1->"$[*]");
492 a b f1
493 6 6 ["1111", "444"]
494 SELECT * FROM t1 WHERE "11111" MEMBER OF (f1->"$[*]");
495 a b f1
496 1 1 ["11111", "2222"]
497 2 2 ["11111", "2222", "22222", "3333"]
498 SELECT * FROM t1 WHERE "22222" MEMBER OF (f1->"$[*]");
499 a b f1
500 2 2 ["11111", "2222", "22222", "3333"]
501 SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL before_apply_1 WAIT_FOR apply_1';
502 OPTIMIZE TABLE t1;;
503 SET DEBUG_SYNC='now WAIT_FOR before_apply_1';
504 INSERT INTO t1 VALUES(7, 7, CAST('["6666", "1111"]' AS JSON));
505 DELETE FROM t1 WHERE "22222" MEMBER OF (f1->"$[*]");
506 INSERT INTO t1 VALUES(8, 8, CAST('["1111", "3333", "444", "6666"]' AS JSON));
507 UPDATE t1 SET f1 = CAST('["5555", "2222", "6666", "777"]' AS JSON) WHERE a = 5;
508 UPDATE t1 SET f1 = CAST('["666", "777", "6666", "7777"]' AS JSON) WHERE a = 6;
509 SET DEBUG_SYNC='now SIGNAL apply_1';
510 connection default
511 /* reap */ OPTIMIZE TABLE t1;
512 Table Op Msg_type Msg_text
513 test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
514 test.t1 optimize status OK
515 CHECK TABLE t1;
516 Table Op Msg_type Msg_text
517 test.t1 check status OK
518 SELECT * FROM t1;
519 a b f1
520 1 1 ["11111", "2222"]
521 5 5 ["5555", "2222", "6666", "777"]
522 6 6 ["666", "777", "6666", "7777"]
523 7 7 ["6666", "1111"]
524 8 8 ["1111", "3333", "444", "6666"]
525 SELECT * FROM t1 WHERE "1111" MEMBER OF (f1->"$[*]");
526 a b f1
527 7 7 ["6666", "1111"]
528 8 8 ["1111", "3333", "444", "6666"]
529 SELECT * FROM t1 WHERE "5555" MEMBER OF (f1->"$[*]");
530 a b f1
531 5 5 ["5555", "2222", "6666", "777"]
532 SELECT * FROM t1 WHERE "444" MEMBER OF (f1->"$[*]");
533 a b f1
534 8 8 ["1111", "3333", "444", "6666"]
535 SELECT * FROM t1 WHERE "11111" MEMBER OF (f1->"$[*]");
536 a b f1
537 1 1 ["11111", "2222"]
538 SELECT * FROM t1 WHERE "22222" MEMBER OF (f1->"$[*]");
539 a b f1
540 SELECT * FROM t1 WHERE "666" MEMBER OF (f1->"$[*]");
541 a b f1
542 6 6 ["666", "777", "6666", "7777"]
543 SELECT * FROM t1 WHERE "6666" MEMBER OF (f1->"$[*]");
544 a b f1
545 5 5 ["5555", "2222", "6666", "777"]
546 6 6 ["666", "777", "6666", "7777"]
547 7 7 ["6666", "1111"]
548 8 8 ["1111", "3333", "444", "6666"]
549 SELECT * FROM t1 WHERE "777" MEMBER OF (f1->"$[*]");
550 a b f1
551 5 5 ["5555", "2222", "6666", "777"]
552 6 6 ["666", "777", "6666", "7777"]
553 SELECT * FROM t1 WHERE "7777" MEMBER OF (f1->"$[*]");
554 a b f1
555 6 6 ["666", "777", "6666", "7777"]
556 SELECT * FROM t1 WHERE "3333" MEMBER OF (f1->"$[*]");
557 a b f1
558 8 8 ["1111", "3333", "444", "6666"]
559 DROP TABLE t1;
560 CREATE TABLE `t1` (
561 `id` int(11) NOT NULL AUTO_INCREMENT,
562 `j1` JSON DEFAULT (CAST(_utf8mb4'[5,-1,0]' AS JSON)),
563 `j2` JSON DEFAULT (CAST(_utf8mb4'[9,8,0,1]' AS JSON)),
564 `j3` JSON DEFAULT (CAST(_utf8mb4'["foobar"]' AS JSON)),
565 `j4` JSON DEFAULT (CAST(_utf8mb4'["HelloWorld"]' AS JSON)),
566 `j5` JSON DEFAULT (CAST(_utf8mb4'["HelloMySQL"]' AS JSON)),
567 PRIMARY KEY (`id`),
568 KEY `mv_idx_binary` ((JSON_LENGTH(`j3`)), (CAST(JSON_EXTRACT(`j4`,_utf8mb4'$[*]') AS BINARY(10) ARRAY)), (JSON_DEPTH(`j2`)))
569 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
570 Warnings:
571 Warning 1681 Integer display width is deprecated and will be removed in a future release.
572 INSERT INTO t1 VALUES(0, CAST(_utf8mb4'[5,-1,0]' AS JSON), CAST(_utf8mb4'[9,8,0,1]' AS JSON), CAST(_utf8mb4'["foobar"]' AS JSON), CAST(_utf8mb4'["HelloWorld"]' AS JSON), CAST(_utf8mb4'["HelloMySQL"]' AS JSON));
573 INSERT INTO t1 VALUES(0, CAST(_utf8mb4'[5,-1,0]' AS JSON), CAST(_utf8mb4'[9,8,0,1]' AS JSON), CAST(_utf8mb4'["foobar"]' AS JSON), CAST(_utf8mb4'["HelloWorld"]' AS JSON), '[]');
574 SELECT * FROM t1;
575 id j1 j2 j3 j4 j5
576 1 [5, -1, 0] [9, 8, 0, 1] ["foobar"] ["HelloWorld"] ["HelloMySQL"]
577 2 [5, -1, 0] [9, 8, 0, 1] ["foobar"] ["HelloWorld"] []
578 SET DEBUG_SYNC='innodb_commit_inplace_alter_table_enter SIGNAL rollback WAIT_FOR try_drop';
579 CREATE INDEX mv_idx_date ON t1 ((CAST(j5->'$' AS UNSIGNED ARRAY)));;
580 SET DEBUG_SYNC='now WAIT_FOR rollback';
581 SET DEBUG_SYNC='row_search_for_mysql_before_return SIGNAL scanning WAIT_FOR continue';
582 SELECT * FROM t1;;
583 SET DEBUG_SYNC='now WAIT_FOR scanning';
584 SET DEBUG_SYNC='now SIGNAL try_drop';
585 ERROR 22018: Invalid JSON value for CAST for functional index 'mv_idx_date'.
586 SET DEBUG_SYNC='now SIGNAL continue';
587 id j1 j2 j3 j4 j5
588 1 [5, -1, 0] [9, 8, 0, 1] ["foobar"] ["HelloWorld"] ["HelloMySQL"]
589 2 [5, -1, 0] [9, 8, 0, 1] ["foobar"] ["HelloWorld"] []
590 SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL inplace_done WAIT_FOR upgrade_lock';
591 ALTER TABLE t1 DROP INDEX mv_idx_binary;;
592 SET DEBUG_SYNC='now WAIT_FOR inplace_done';
593 SET DEBUG_SYNC='innodb_row_upd_clust_step_enter SIGNAL upgrade_lock';
594 DELETE FROM t1;;
595 SELECT * FROM t1;
596 id j1 j2 j3 j4 j5
597 DROP TABLE t1;
598 #
599 # Bug#31474182 THE MULTI-VALUED INDEX RETURNS ERROR WHEN --DEBUG OPTION SPECIFIED.
600 #
601 set debug = 'd:t';
602 CREATE TABLE t1 (f1 JSON, id VARCHAR(64) AS (f1->"$[0]") STORED NOT NULL PRIMARY KEY);
603 CREATE INDEX i1 ON t1((CAST(f1->"$[*]" AS UNSIGNED ARRAY)));
604 ANALYZE TABLE t1;
605 Table Op Msg_type Msg_text
606 test.t1 analyze status OK
607 SELECT * FROM t1 WHERE JSON_OVERLAPS(f1->'$[*]', '[4,3,7]') ORDER BY id;
608 f1 id
609 DROP TABLE t1;
610 set debug = default;