Debian Codegraph @

mysql-test/suite/innodb/t/multi_value_basic.test

raw
1 --source include/no_valgrind_without_big.inc
2 --source include/have_innodb_max_16k.inc
3
4 --source include/count_sessions.inc
5
6 # Bug 30709525 - We need more entries in the multi value index
7 # to hit the case where the internal InnoDB row_merge_buf_t size
8 # is exceeded twice (currently 2MB, cca 150k values in json arrays)
9 CREATE TABLE t1 (id INT UNSIGNED NOT NULL, arr JSON NOT NULL, PRIMARY KEY (id));
10
11 let $record = 0;
12 let $total_records = 15000;
13
14 --disable_query_log
15 while ($record<$total_records)
16 {
17 eval INSERT INTO t1(id, arr) VALUES($record, '[0,1,2,3,4,5,6,7,8,9]');
18 inc $record;
19 }
20 --enable_query_log
21 ALTER TABLE t1 ADD INDEX test_categories ((CAST(arr AS UNSIGNED ARRAY)));
22 DROP TABLE t1;
23
24 CREATE TABLE t1 (id INT NOT NULL KEY AUTO_INCREMENT, f1 JSON);
25 CREATE INDEX i1 ON t1((CAST(f1->"$[*]" AS UNSIGNED ARRAY)));
26 INSERT INTO t1(f1) VALUES(CAST('[1333,1321]' AS JSON));
27 SELECT * FROM t1;
28 DROP TABLE t1;
29
30
31
32 CREATE TABLE t1 (id INT NOT NULL KEY AUTO_INCREMENT, f1 JSON);
33 CREATE INDEX i1 ON t1((CAST(f1->"$[*]" AS CHAR(10) ARRAY)));
34 INSERT INTO t1(f1) VALUES(CAST('["13.33","13.21"]' AS JSON));
35 SELECT * FROM t1;
36 DROP TABLE t1;
37
38
39
40 CREATE TABLE t1 (
41 j4 json ,
42 KEY mv_idx_binary ((( CAST(j4->'$[*]' AS BINARY(10) ARRAY))), (json_depth(j4)), (json_valid(j4))) USING BTREE);
43
44 INSERT INTO t1 VALUES ('["foobar"]');
45 SELECT * FROM t1 WHERE "foobar" MEMBER OF (j4->'$[*]');
46 DELETE FROM t1;
47
48 DROP TABLE t1;
49
50
51
52 CREATE TABLE t1 (id INT NOT NULL KEY AUTO_INCREMENT, f1 JSON);
53 CREATE INDEX i1 ON t1((CAST(f1->"$[*]" AS DECIMAL(4,2) ARRAY)));
54 INSERT INTO t1(f1) VALUES(CAST('["13.33","13.21"]' AS JSON));
55 UPDATE t1 SET f1 = NULL;
56 SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
57 SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
58 SELECT * FROM t1 WHERE f1 IS NULL;
59 UPDATE t1 SET f1 = CAST('["13.33","13.21"]' AS JSON);
60 SELECT * FROM t1 WHERE f1 IS NULL;
61 SELECT * FROM t1 WHERE 12.34 MEMBER OF (f1->'$[*]');
62 SELECT * FROM t1 WHERE 56.78 MEMBER OF (f1->'$[*]');
63 SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
64 SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
65 UPDATE t1 SET f1 = CAST('["12.34","56.78"]' AS JSON);
66 CHECK TABLE t1;
67 SELECT * FROM t1 WHERE 12.34 MEMBER OF (f1->'$[*]');
68 SELECT * FROM t1 WHERE 56.78 MEMBER OF (f1->'$[*]');
69 SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
70 SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
71 UPDATE t1 SET f1 = CAST('["43.21","98.76", "76.54"]' AS JSON);
72 CHECK TABLE t1;
73 SELECT * FROM t1 WHERE 12.34 MEMBER OF (f1->'$[*]');
74 SELECT * FROM t1 WHERE 56.78 MEMBER OF (f1->'$[*]');
75 SELECT * FROM t1 WHERE 43.21 MEMBER OF (f1->'$[*]');
76 SELECT * FROM t1 WHERE 98.76 MEMBER OF (f1->'$[*]');
77 SELECT * FROM t1 WHERE 76.54 MEMBER OF (f1->'$[*]');
78 DROP TABLE t1;
79
80
81 CREATE TEMPORARY TABLE IF NOT EXISTS t1 (
82 j JSON DEFAULT (CAST('["HelloWorld"]' AS JSON)),
83 KEY mv_idx_binary (( CAST(j->'$[*]' AS CHAR(10) ARRAY) )) );
84
85 INSERT INTO t1 VALUES() ;
86 SELECT * FROM t1;
87 UPDATE t1 SET j = CAST('["HelloMySQL"]' AS JSON);
88 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
89 DELETE FROM t1;
90
91 DROP TABLE t1;
92
93
94
95 CREATE TABLE t1(j JSON, KEY i1((CAST(j AS UNSIGNED ARRAY))));
96 INSERT INTO t1 VALUES('[1,2,3]'), ('[]'), (NULL);
97 SELECT * FROM t1;
98 UPDATE t1 SET j = '[]' WHERE j IS NULL;
99 SELECT * FROM t1;
100 DELETE FROM t1;
101 INSERT INTO t1 VALUES('[1,2,3]'), ('[]'), (NULL);
102 UPDATE t1 SET j = NULL;
103 SELECT * FROM t1;
104 DELETE FROM t1;
105 CHECK TABLE t1;
106 DROP TABLE t1;
107
108
109
110 CREATE TABLE t1 (
111 j JSON DEFAULT (CAST('["HelloWorld"]' AS JSON)),
112 KEY mv_idx_binary ((CAST(j->'$[*]' AS BINARY(10) ARRAY))));
113 INSERT INTO t1 VALUES();
114 ALTER TABLE t1 DROP INDEX mv_idx_binary ;
115 CREATE INDEX mv_idx_binary ON t1 ((CAST( j -> '$[*]' AS BINARY(10) ARRAY))) VISIBLE;
116 SELECT * FROM t1;
117 DROP TABLE t1;
118
119
120
121 CREATE TABLE IF NOT EXISTS t1 (
122 j2 JSON DEFAULT (CAST('[9,8,0,1]' AS JSON)),
123 j3 JSON DEFAULT (CAST('["foobar"]' AS JSON)),
124 KEY mv_idx_unsigned (( CAST(j2->'$[*]' AS UNSIGNED ARRAY) )) ,
125 KEY mv_idx_char (( CAST(j3->'$[*]' AS CHAR(10) ARRAY) ))
126 );
127 INSERT INTO t1(j3) VALUES(CAST('[ "foobar", "HelloWorld", "MySQL", "1234" ]' AS JSON));
128 INSERT INTO t1(j3) VALUES(CAST('[ "Hello", "World", "InnoDB", "56" ]' AS JSON));
129 SELECT * FROM t1;
130 DELETE FROM t1;
131 CHECK TABLE t1;
132 DROP TABLE t1;
133
134
135
136 CREATE TABLE t1 (j JSON, key i1((cast(j->"$.id" AS CHAR(10) ARRAY))), key i2((cast(j->"$.id" AS UNSIGNED ARRAY))));
137 INSERT INTO t1 VALUES('{"id":1}');
138 INSERT INTO t1 VALUES('{"group":2}');
139 INSERT INTO t1 VALUES(NULL);
140 SELECT * FROM t1;
141
142 DROP TABLE t1;
143
144
145
146 CREATE TABLE t1(j JSON);
147
148 INSERT INTO t1 VALUES('[]'), ('null');
149
150 # The empty array '[]' is fine, but 'null' is not an array and can't be indexed
151 --error ER_INVALID_JSON_VALUE_FOR_FUNC_INDEX
152 CREATE INDEX mv_idx_items_of_array ON t1 ((CAST(j->'$' AS UNSIGNED ARRAY)));
153
154 DELETE FROM t1;
155
156 # The following example is difficult in several ways:
157 # The rows with '[]' produce no index records
158 # The row '[6,2]' produces 2 index records in opposite order
159 # The row '[5]' should get properly indexed
160 INSERT INTO t1 VALUES ('[]'), ('[6,2]'), ('[]'), ('[1,4]'), ('[5]');
161
162 CREATE INDEX mv_idx_items_of_array ON t1 ((CAST(j->'$' AS UNSIGNED ARRAY)));
163
164 EXPLAIN SELECT * FROM t1 WHERE 5 MEMBER OF (j->'$');
165 SELECT * FROM t1 FORCE INDEX(mv_idx_items_of_array) WHERE 5 MEMBER OF (j->'$');
166
167 DROP TABLE t1;
168
169
170
171 CREATE TABLE IF NOT EXISTS t1 (
172 i INT DEFAULT 100,
173 j JSON DEFAULT (CAST('["HelloWorld"]' AS JSON)),
174 KEY mv_idx_binary (( CAST(j->'$[*]' AS BINARY(10) ARRAY) )) );
175
176 INSERT INTO t1 VALUES();
177
178 SELECT * FROM t1;
179
180 connect (conn1,localhost,root,,);
181 START TRANSACTION;
182 DELETE FROM t1;
183
184 connection default;
185 SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
186
187 connection conn1;
188 COMMIT;
189 disconnect conn1;
190
191 connection default;
192
193 SELECT * FROM t1;
194
195 DROP TABLE t1;
196
197
198
199 # Test a SELECT should read the consistent result with concurrent UPDATE
200 CREATE TABLE t1 (f1 JSON, UNIQUE KEY i1((CAST(f1->"$[*]" AS CHAR(10) ARRAY))));
201 INSERT INTO t1(f1) VALUES(CAST('["abc", "abc "]' AS JSON));
202
203 connect (conn1,localhost,root,,);
204 START TRANSACTION;
205
206 SELECT * FROM t1 WHERE "abc" MEMBER OF (f1->"$[*]");
207
208 connection default;
209 UPDATE t1 SET f1 = CAST('["def"]' AS JSON);
210
211 connection conn1;
212 SELECT * FROM t1 WHERE "def" MEMBER OF (f1->"$[*]");
213 ROLLBACK;
214
215 connection default;
216 disconnect conn1;
217
218 DROP TABLE t1;
219
220
221
222 # Test insert the same value with the deleted one, the purge
223 # can handle it properly
224
225 CREATE TABLE t1 (id INT NOT NULL KEY AUTO_INCREMENT, f1 JSON);
226 CREATE INDEX i1 ON t1((CAST(f1->"$[*]" AS UNSIGNED ARRAY)));
227
228 INSERT INTO t1 VALUES(0, NULL);
229
230 DELETE FROM t1;
231
232 INSERT INTO t1 VALUES(1, NULL);
233
234 SELECT * FROM t1;
235
236 DELETE FROM t1;
237
238 INSERT INTO t1 VALUES(2, CAST('[10]' AS JSON));
239
240 DELETE FROM t1;
241
242 INSERT INTO t1 VALUES(3, CAST('[10]' AS JSON));
243
244 --source include/wait_innodb_all_purged.inc
245
246 CHECK TABLE t1;
247
248 SELECT * FROM t1;
249
250 DROP TABLE t1;
251
252
253 # Test update the same record several times, the purge
254 # can handle it properly
255
256 CREATE TABLE t1 (id INT NOT NULL KEY AUTO_INCREMENT, f1 JSON);
257 CREATE INDEX i1 ON t1((CAST(f1->"$[*]" AS DECIMAL(4,2) ARRAY)));
258 INSERT INTO t1(f1) VALUES(CAST('["13.33","13.21"]' AS JSON));
259 UPDATE t1 SET f1 = NULL;
260 UPDATE t1 SET f1 = CAST('["13.33","13.21"]' AS JSON);
261
262 --source include/wait_innodb_all_purged.inc
263
264 CHECK TABLE t1;
265 SELECT * FROM t1 WHERE f1 IS NULL;
266 SELECT * FROM t1 WHERE 12.34 MEMBER OF (f1->'$[*]');
267 SELECT * FROM t1 WHERE 56.78 MEMBER OF (f1->'$[*]');
268 SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
269 SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
270
271 UPDATE t1 SET f1 = CAST('["12.34","56.78"]' AS JSON);
272 UPDATE t1 SET f1 = CAST('["43.21","98.76", "76.54"]' AS JSON);
273
274 --source include/wait_innodb_all_purged.inc
275
276 CHECK TABLE t1;
277 SELECT * FROM t1 WHERE 12.34 MEMBER OF (f1->'$[*]');
278 SELECT * FROM t1 WHERE 56.78 MEMBER OF (f1->'$[*]');
279 SELECT * FROM t1 WHERE 43.21 MEMBER OF (f1->'$[*]');
280 SELECT * FROM t1 WHERE 98.76 MEMBER OF (f1->'$[*]');
281 SELECT * FROM t1 WHERE 76.54 MEMBER OF (f1->'$[*]');
282 DROP TABLE t1;
283
284
285
286 # Test if any previous DELETE / UPDATE failed, the multi-value
287 # position of them will always be reset for next DELETE & UPDATE
288
289 CREATE TABLE `t1` (
290 `id` int(11) NOT NULL AUTO_INCREMENT,
291 `j1` json DEFAULT (cast(_utf8mb4'[5,-1,0]' as json)),
292 `j2` json DEFAULT (cast(_utf8mb4'[9,8,0,1]' as json)),
293 `j3` json DEFAULT (cast(_utf8mb4'["foobar"]' as json)),
294 `j4` json DEFAULT (cast(_utf8mb4'["HelloWorld"]' as json)),
295 `j5` json DEFAULT (json_array(now())),
296 `j7` json DEFAULT (json_array(cast(now() as time))),
297 `extra` json DEFAULT NULL,
298 PRIMARY KEY (`id`),
299 UNIQUE KEY `mv_idx_binary` ((json_length(`j3`)),(cast(json_extract(`j4`,_utf8mb4'$[*]') as binary(10) array)),(json_depth(`j2`))),
300 KEY `mv_idx_signed` ((cast(json_extract(`j1`,_utf8mb4'$[*]') as signed array))),
301 KEY `mv_idx_unsigned` ((cast(json_extract(`j2`,_utf8mb4'$[*]') as unsigned array))),
302 KEY `mv_idx_char` ((cast(json_extract(`j3`,_utf8mb4'$[*]') as char(10) array))),
303 KEY `mv_idx_time` ((cast(json_extract(`j7`,_utf8mb4'$[*]') as time array)))
304 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
305
306 INSERT INTO t1 ( j4 ) VALUES( JSON_OBJECT( 'key1' , CAST( '2019-11-27 03:43:53' AS DATETIME)) );
307 INSERT INTO t1 VALUES();
308 DELETE FROM t1 WHERE ( JSON_OVERLAPS( JSON_OBJECT( 'key1' , CAST(null AS JSON)) , j1 -> '$[*]' ) ) XOR ( JSON_CONTAINS( j5 -> '$[*]' , JSON_OBJECT('key1' , CAST(CONCAT( '[ 1, 2, 3, ' , null, ', 5 ]' ) AS JSON)) ) );
309
310 --error ER_DUP_ENTRY
311 UPDATE t1 SET j4 = '["foobar", "HelloWorld", "MySQL", "1234" ]' WHERE NOT ('');
312 UPDATE t1 SET j7 = JSON_OBJECT( 'key1' , JSON_OBJECT()) WHERE JSON_OBJECT('key1' , CAST( '[]' AS JSON)) MEMBER OF ( j1 -> '$[*]' );
313 DELETE FROM t1;
314
315 CHECK TABLE t1;
316 DROP TABLE t1;
317
318
319
320 # Test DML mixed with DDL
321
322 CREATE TABLE IF NOT EXISTS t1 (
323 j2 JSON DEFAULT (CAST('[9,8,0,1]' AS JSON)),
324 j8 JSON DEFAULT (JSON_ARRAY(3.1456,6.022,-1)),
325 KEY mv_idx_unsigned (( CAST(j2->'$[*]' AS UNSIGNED ARRAY) )) ,
326 KEY mv_idx_decimal (( CAST(j8->'$[*]' AS DECIMAL(10,4) ARRAY) )) );
327
328 ALTER TABLE t1 DROP INDEX mv_idx_decimal;
329 INSERT INTO t1 VALUES();
330 DELETE FROM t1;
331 ALTER TABLE t1 DROP INDEX mv_idx_unsigned;
332 SELECT * FROM t1;
333
334 --source include/wait_innodb_all_purged.inc
335 # Sleep a while to make sure purge will finish the work
336 sleep 3;
337
338 DROP TABLE t1;
339
340
341 # Test the UPDATE scenario when the update doesn't touch one multi-value
342 # data, but the value still need to be calculated and remembered.
343
344 CREATE TABLE IF NOT EXISTS t1 ( id int AUTO_INCREMENT PRIMARY KEY, j1 JSON DEFAULT (CAST('[5,-1,0]' AS JSON)), j2 JSON DEFAULT (CAST('[9,8,0,1]' AS JSON)), j3 JSON DEFAULT (CAST('["foobar"]' AS JSON)), j4 JSON DEFAULT (CAST('["HelloWorld"]' AS JSON)), j5 JSON DEFAULT (JSON_ARRAY(NOW())), j6 JSON DEFAULT (JSON_ARRAY(DATE(NOW()), DATE(NOW()), DATE(NOW()))), j7 JSON DEFAULT (JSON_ARRAY(TIME(NOW()))), j8 JSON DEFAULT (JSON_ARRAY(3.1456,6.022,-1)), KEY mv_idx_signed (( CAST(j1->'$[*]' AS SIGNED ARRAY) )) , KEY mv_idx_unsigned (( CAST(j2->'$[*]' AS UNSIGNED ARRAY) )) , KEY mv_idx_char (( CAST(j3->'$[*]' AS CHAR(10) ARRAY) )) , KEY mv_idx_binary (( CAST(j4->'$[*]' AS BINARY(10) ARRAY) )) , KEY mv_idx_datetime (( CAST(j5->'$[*]' AS DATETIME ARRAY) )) , KEY mv_idx_date (( CAST(j6->'$[*]' AS DATE ARRAY) )) , KEY mv_idx_time (( CAST(j7->'$[*]' AS TIME ARRAY) )) , KEY mv_idx_decimal (( CAST(j8->'$[*]' AS DECIMAL(10,4) ARRAY) )) ) ;
345 SET autocommit=0 ;
346 SET SESSION sql_mode ='' ;
347 DELETE FROM t1 WHERE 0 ;
348 INSERT INTO t1 SELECT * FROM t1 GROUP BY j2 ;
349 INSERT INTO t1 ( j4 ) VALUES( JSON_OBJECT( 'key1' , CAST( '2007-08-18' AS DATE)) ) ;
350 INSERT INTO t1 VALUES() ;
351 # Disable the result log in case of result mismatch for 'Extra' when
352 # running with binlog-format=mixed
353 --disable_result_log
354 EXPLAIN DELETE FROM t1 ;
355 --enable_result_log
356 ALTER TABLE t1 RENAME KEY mv_idx_signed to new_idx_1 ;
357 ALTER TABLE t1 RENAME KEY new_idx_1 to mv_idx_signed ;
358 UPDATE t1 SET j5 = JSON_OBJECT( 'key1' , true) WHERE ( 1 ) AND ( 0 ) XOR ( '' ) ;
359 ALTER TABLE t1 DROP INDEX mv_idx_datetime ;
360 ALTER TABLE t1 ADD COLUMN extra JSON ;
361 UPDATE t1 SET extra = j1 ;
362 ALTER TABLE t1 DROP INDEX mv_idx_date ;
363 INSERT INTO t1 VALUES() ;
364 DELETE FROM t1 ;
365 EXPLAIN UPDATE t1 IGNORE KEY FOR JOIN ( mv_idx_decimal ) SET j4 = JSON_OBJECT( 'key1' , CAST( '12:58:44' AS TIME)) WHERE ( 1 ) OR ( '' ) ;
366 INSERT INTO t1 SELECT * FROM t1 GROUP BY j2 ORDER BY j6 DESC ;
367 UPDATE t1 USE INDEX FOR ORDER BY ( mv_idx_char , mv_idx_unsigned , mv_idx_binary ) SET j5 = JSON_ARRAY(CAST(-69.56 AS DECIMAL (10,3)), CAST(PI() AS DECIMAL(10,5)), 1947.95) WHERE 1 ;
368 INSERT INTO t1 ( j2 ) VALUES( JSON_OBJECT( 'key1' , 'to' ) ) ;
369 ALTER TABLE t1 DROP INDEX mv_idx_decimal ;
370 EXPLAIN SELECT * FROM t1 WHERE 0 ORDER BY j6 ;
371 INSERT INTO t1 VALUES() ;
372 UPDATE t1 SET j1 = CAST( '[ -1 , 10, 7, 6, -999 ]' AS JSON ) WHERE 0 ;
373 DELETE FROM t1 WHERE NOT ( '' ) ;
374 ALTER TABLE t1 RENAME KEY mv_idx_binary to new_idx_1 ;
375 ALTER TABLE t1 RENAME KEY new_idx_1 to mv_idx_binary ;
376 EXPLAIN INSERT INTO t1 ( j3 ) VALUES( JSON_OBJECT( 'key1' , CAST(null AS JSON)) ) ;
377 DELETE FROM t1 ;
378 CREATE INDEX mv_idx_decimal ON t1 (( CAST( j8 -> '$[*]' AS DECIMAL(10,4) ARRAY) )) VISIBLE ;
379 INSERT INTO t1 VALUES() ;
380 INSERT INTO t1 ( j4 ) VALUES( JSON_OBJECT() ) ;
381 ALTER TABLE t1 RENAME KEY mv_idx_binary to new_idx_1 ;
382 ALTER TABLE t1 RENAME KEY new_idx_1 to mv_idx_binary ;
383 EXPLAIN DELETE FROM t1 WHERE '' ;
384 DELETE FROM t1 WHERE '2034-10-29 21:05:14' MEMBER OF ( j8 -> '$[*]' ) ;
385 INSERT INTO t1 VALUES() ;
386 UPDATE t1 SET j5 = '[7, 5, 1, -56, -45]' WHERE 0 ;
387 ALTER TABLE t1 DROP INDEX mv_idx_time ;
388 ALTER TABLE t1 DROP INDEX mv_idx_binary ;
389 ALTER TABLE t1 ADD INDEX mv_idx_date ( ( JSON_DEPTH( j3 ) ) ,( CAST( j6 -> '$.key1[*]' AS DATE ARRAY) ), ( JSON_LENGTH( j7 ) ) ) VISIBLE ;
390 INSERT INTO t1 ( j4 ) VALUES( JSON_OBJECT( 'key1' , CAST( '1978-09-05 12:08:06' AS DATETIME)) ) ;
391 ALTER TABLE t1 ADD INDEX mv_idx_time USING HASH ( ( JSON_LENGTH( j5 ) ) , ( JSON_DEPTH( j1 ) ) ,( CAST( j7 -> '$.key1' AS TIME ARRAY) )) INVISIBLE ;
392 INSERT INTO t1 VALUES() ;
393 INSERT INTO t1 VALUES() ;
394 UPDATE t1 SET j3 = JSON_OBJECT( 'key1' , 3 ) WHERE ( '' ) XOR ( 1 ) OR ( 0 ) ;
395 ROLLBACK;
396 UPDATE t1 SET j3 = JSON_OBJECT( 'key1' , 3 ) WHERE ( '' ) XOR ( 1 ) OR ( 0 ) ;
397 DROP TABLE t1;
398
399 SET autocommit = default;
400 SET SESSION sql_mode = default;
401
402
403
404 # Test re-build table will work properly, because all multi-value columns
405 # are also re-built correctly according to table definition.
406 SET autocommit=0;
407 SET SESSION sql_mode ='';
408
409 CREATE TABLE IF NOT EXISTS t1 ( id int AUTO_INCREMENT PRIMARY KEY, j1 JSON DEFAULT (CAST( '[5,-1,0]' AS JSON)), j2 JSON DEFAULT (CAST( '[9,8,0,1]' AS JSON)), j3 JSON DEFAULT (CAST( '["foobar"]' AS JSON)), j4 JSON DEFAULT (CAST( '["HelloWorld"]' AS JSON)), j5 JSON DEFAULT (JSON_ARRAY(NOW())), j6 JSON DEFAULT (JSON_ARRAY(DATE(NOW()), DATE(NOW()), DATE(NOW()))), j7 JSON DEFAULT (JSON_ARRAY(TIME(NOW()))), j8 JSON DEFAULT (JSON_ARRAY(3.1456,6.022,-1)), KEY mv_idx_signed (( CAST(j1-> '$[*]' AS SIGNED ARRAY) )) , KEY mv_idx_unsigned (( CAST(j2-> '$[*]' AS UNSIGNED ARRAY) )) , KEY mv_idx_char (( CAST(j3-> '$[*]' AS CHAR(10) ARRAY) )) , KEY mv_idx_binary (( CAST(j4-> '$[*]' AS BINARY(10) ARRAY) )) , KEY mv_idx_datetime (( CAST(j5-> '$[*]' AS DATETIME ARRAY) )) , KEY mv_idx_date (( CAST(j6-> '$[*]' AS DATE ARRAY) )) , KEY mv_idx_time (( CAST(j7-> '$[*]' AS TIME ARRAY) )) , KEY mv_idx_decimal (( CAST(j8-> '$[*]' AS DECIMAL(10,4) ARRAY) )) );
410
411 SELECT * FROM t1 WHERE NOT ( JSON_ARRAY(TIME(NOW()), CAST( '00:00:00' AS TIME)) MEMBER OF ( j2 -> '$[*]' ) ) GROUP BY j4 LIMIT 84;
412 INSERT INTO t1 SELECT * FROM t1 FORCE KEY FOR JOIN ( mv_idx_decimal , mv_idx_char );
413 SELECT * FROM t1 IGNORE INDEX FOR ORDER BY ( mv_idx_date , mv_idx_binary ) WHERE ( 0 ) XOR ( 1 ) GROUP BY j5 ORDER BY j3;
414 ALTER TABLE t1 DROP INDEX mv_idx_time;
415 INSERT INTO t1 VALUES();
416 CHECK TABLE t1;
417 SELECT * FROM t1 WHERE 0;
418 DELETE FROM t1;
419 CHECK TABLE t1;
420 INSERT INTO t1 SELECT * FROM t1 GROUP BY j3 ORDER BY j7;
421 INSERT INTO t1 VALUES();
422 ALTER TABLE t1 ADD COLUMN extra JSON;
423 UPDATE t1 SET extra = j5;
424 ALTER TABLE t1 DROP INDEX mv_idx_datetime;
425 ALTER TABLE t1 DROP COLUMN j5;
426 ALTER TABLE t1 CHANGE COLUMN extra j5 JSON;
427 DELETE FROM t1 WHERE '';
428 INSERT INTO t1 ( j1 ) VALUES( JSON_OBJECT( 'key1' , CAST( '00:52:03' AS TIME)) );
429 ALTER TABLE t1 ADD COLUMN extra JSON;
430 UPDATE t1 SET extra = j5;
431 ALTER TABLE t1 DROP INDEX mv_idx_date;
432 SELECT id, j1, j2, j3, j4 FROM t1;
433 SELECT COUNT(*) FROM t1;
434 ALTER TABLE t1 DROP COLUMN j5;
435 CHECK TABLE t1;
436 SELECT id, j1, j2, j3, j4 FROM t1 WHERE "foobar" MEMBER OF (j3->'$[*]');
437 SELECT id, j1, j2, j3, j4 FROM t1 WHERE "HelloWorld" MEMBER OF (j4->'$[*]');
438 DROP TABLE t1;
439
440 SET autocommit = default;
441 SET SESSION sql_mode = default;
442
443
444 # Test if the multi-value field is not of the first index field.
445 SET SESSION sql_mode = '';
446
447 CREATE TABLE `t1` (
448 `id` int(11) NOT NULL AUTO_INCREMENT,
449 `j1` json DEFAULT (cast(_utf8mb4'[5,-1,0]' as json)),
450 `j2` json DEFAULT (cast(_utf8mb4'[9,8,0,1]' as json)),
451 `j3` json DEFAULT (cast(_utf8mb4'["foobar"]' as json)),
452 `j4` json DEFAULT (cast(_utf8mb4'["HelloWorld"]' as json)),
453 `j5` json DEFAULT (json_array(now())),
454 `j6` json DEFAULT (json_array(cast(now() as date),cast(now() as date),cast(now() as date))),
455 `j7` json DEFAULT (json_array(cast(now() as time))),
456 `j8` json DEFAULT (json_array(3.1456,6.022,-(1))),
457 `extra` json DEFAULT NULL,
458 PRIMARY KEY (`id`),
459 UNIQUE KEY `mv_idx_signed` ((json_length(`j5`)),(cast(json_extract(`j1`,_utf8mb4'$.key1[*]') as signed array)),(json_depth(`j3`))) USING BTREE,
460 KEY `mv_idx_char` ((cast(json_extract(`j3`,_utf8mb4'$.key2') as char(10) array)),(json_length(`j7`)),(((strcmp(_utf8mb4' ',_utf8mb4'NwKf2G3hn6eKy8TCMVTfh0xgGZfg68dchTUUxQtg9xfvCdePZCAxIZEvUETuWATcdjuOLZx') <> (_utf8mb4'6q' like _utf8mb4'_world_')) and (_utf8mb4'1994-01-16 22:25:11' not between _utf8mb4'2033-04-12 09:41:42' and _utf8mb4'1991-05-20 06:44:18')))),
461 KEY `mv_idx_unsigned` ((json_length(`j3`)),(json_valid(`j4`)),(cast(json_extract(`j2`,_utf8mb4'$[*]') as unsigned array))) USING BTREE /*!80000 INVISIBLE */,
462 KEY `mv_idx_decimal` ((cast(json_extract(`j8`,_utf8mb4'$[*]') as decimal(10, 4) array))) USING BTREE
463 ) ENGINE=InnoDB AUTO_INCREMENT=1283 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
464
465 ALTER TABLE t1 ADD UNIQUE INDEX mv_idx_date USING BTREE ( ( JSON_DEPTH( j3 ) ) ,( CAST( j6 -> '$[*]' AS DATE ARRAY) ), ( JSON_LENGTH( j1 ) ) ) VISIBLE;
466
467 UPDATE t1 SET j2 = JSON_OBJECT( 'key1' , 1772955722 ) WHERE ( JSON_OVERLAPS( j7 -> '$[*]' , '[ ]' ) ) XOR ( 1 );
468
469 INSERT INTO t1 SELECT * FROM t1 WHERE JSON_CONTAINS( JSON_ARRAY(-10,100,65,JSON_ARRAY(-101,25,12,0)) , j6 -> '$[*]' ) LIMIT 55;
470
471 INSERT INTO t1 SELECT * FROM t1 GROUP BY j8;
472
473 DELETE FROM t1 WHERE ( 0 ) XOR ( 1 ) AND ( JSON_CONTAINS( '[45, 61, 54, 0]' , j3 -> '$[*]' ) );
474
475 INSERT INTO t1 VALUES();
476
477 UPDATE t1 SET j3 = '["foobar", "HelloWorld", "MySQL", "1234" ]' WHERE JSON_OVERLAPS( JSON_ARRAY(DATE(NOW()), CAST( '2018-01-01' AS DATE), CAST( '0000-00-00' AS DATE)) , j7 -> '$[*]' );
478
479 DELETE FROM t1;
480
481 SELECT * FROM t1 LIMIT 73;
482
483 INSERT INTO t1 VALUES();
484 --error 1176
485 INSERT INTO t1 SELECT * FROM t1 USE KEY FOR GROUP BY ( mv_idx_unsigned , mv_idx_signed ) GROUP BY j3 ORDER BY j1;
486 --error 1176
487 SELECT * FROM t1 IGNORE INDEX ( mv_idx_datetime , mv_idx_char ) WHERE JSON_CONTAINS( j5 -> '$[*]' , JSON_OBJECT( 'key1' , CAST( '1981-06-09' AS DATE)) );
488
489 SELECT * FROM t1 WHERE JSON_OVERLAPS( j4 -> '$[*]' , JSON_ARRAY(CAST(-69.56 AS DECIMAL (10,3)), CAST(PI() AS DECIMAL(10,5)), 1947.95) ) GROUP BY j8;
490
491 DELETE FROM t1;
492
493 CHECK TABLE t1;
494
495 DROP TABLE t1;
496
497 SET sql_mode = default;
498
499
500
501 # Test rollback
502
503 CREATE TABLE t1(j json, KEY ((CAST(j->'$' AS UNSIGNED ARRAY))));
504
505 --error ER_INVALID_JSON_VALUE_FOR_FUNC_INDEX
506 INSERT INTO t1 VALUES ('[]'), ('null');
507 SELECT * FROM t1;
508
509 DROP TABLE t1;
510
511
512 CREATE TABLE t1 (id INT NOT NULL KEY AUTO_INCREMENT, f1 JSON);
513 CREATE INDEX i1 ON t1((CAST(f1->"$[*]" AS DECIMAL(4,2) ARRAY)));
514 CREATE INDEX i2 ON t1((CAST(f1->"$[*]" AS CHAR(10) ARRAY)));
515
516 START TRANSACTION;
517 INSERT INTO t1(f1) VALUES(CAST('["13.33","13.21"]' AS JSON));
518 INSERT INTO t1(f1) VALUES(NULL);
519 SELECT * FROM t1;
520 SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
521 ROLLBACK;
522 CHECK TABLE t1;
523 SELECT * FROM t1;
524 SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
525
526
527 INSERT INTO t1(f1) VALUES(CAST('["13.33","13.21"]' AS JSON));
528 START TRANSACTION;
529 UPDATE t1 SET f1 = CAST('["12.34", "56.78"]' AS JSON);
530 SELECT * FROM t1 WHERE 12.34 MEMBER OF (f1->'$[*]');
531 ROLLBACK;
532 CHECK TABLE t1;
533 SELECT * FROM t1;
534 SELECT * FROM t1 WHERE 12.34 MEMBER OF (f1->'$[*]');
535 SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
536 START TRANSACTION;
537 UPDATE t1 SET f1 = NULL;
538 ROLLBACK;
539 CHECK TABLE t1;
540 SELECT * FROM t1;
541 SELECT * FROM t1 WHERE 12.34 MEMBER OF (f1->'$[*]');
542 SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
543 UPDATE t1 SET f1 = NULL;
544 START TRANSACTION;
545 UPDATE t1 SET f1 = CAST('["12.34", "56.78"]' AS JSON);
546 SELECT * FROM t1 WHERE 12.34 MEMBER OF (f1->'$[*]');
547 SELECT * FROM t1 WHERE 56.78 MEMBER OF (f1->'$[*]');
548 ROLLBACK;
549 CHECK TABLE t1;
550 SELECT * FROM t1;
551
552 UPDATE t1 SET f1 = CAST('["13.33", "13.21"]' AS JSON);
553 START TRANSACTION;
554 DELETE FROM t1;
555 SELECT * FROM t1;
556 SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
557 SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
558 ROLLBACK;
559 SELECT * FROM t1;
560 SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
561 SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
562 CHECK TABLE t1;
563
564 DROP TABLE t1;
565
566
567 CREATE TABLE IF NOT EXISTS t1 ( j1 JSON DEFAULT (CAST('[5,-1,0]' AS JSON)), j2 JSON DEFAULT (CAST('[9,8,0,1]' AS JSON)), j3 JSON DEFAULT (CAST('["foobar"]' AS JSON)), j5 JSON DEFAULT (JSON_ARRAY(NOW())), j6 JSON DEFAULT (JSON_ARRAY(DATE(NOW()), DATE(NOW()), DATE(NOW()))), j7 JSON DEFAULT (JSON_ARRAY(TIME(NOW()))), j8 JSON DEFAULT (JSON_ARRAY(3.1456,6.022,-1)), KEY mv_idx_signed (( CAST(j1->'$[*]' AS SIGNED ARRAY) )) , KEY mv_idx_unsigned (( CAST(j2->'$[*]' AS UNSIGNED ARRAY) )) , KEY mv_idx_char (( CAST(j3->'$[*]' AS CHAR(10) ARRAY) )) , KEY mv_idx_datetime (( CAST(j5->'$[*]' AS DATETIME ARRAY) )) , KEY mv_idx_date (( CAST(j6->'$[*]' AS DATE ARRAY) )) , KEY mv_idx_time (( CAST(j7->'$[*]' AS TIME ARRAY) )) , KEY mv_idx_decimal (( CAST(j8->'$[*]' AS DECIMAL(10,4) ARRAY) )));
568
569 INSERT INTO t1( j1 ) VALUES( CAST( '[ 0, -1, 9, -4 ]' AS JSON ) );
570 SET autocommit = 0;
571 INSERT INTO t1 VALUES();
572 ROLLBACK;
573 SELECT j1, j2, j3 FROM t1;
574 CHECK TABLE t1;
575 DROP TABLE t1;
576
577 SET autocommit = default;
578
579
580 # Test for the scenario that record on clustered index would be inserted
581 # by modify, and the operation has to be rolled back.
582 CREATE TABLE IF NOT EXISTS t1 (
583 i INT NOT NULL PRIMARY KEY,
584 j JSON DEFAULT (CAST('["HelloWorld"]' AS JSON)),
585 KEY mv_idx_binary (( CAST(j->'$[*]' AS BINARY(10) ARRAY) )) );
586
587 INSERT INTO t1 VALUES(1, CAST('["HelloWorld", "Hello"]' AS JSON));
588 INSERT INTO t1 VALUES(2, CAST('["HelloMySQL", "Hello"]' AS JSON));
589
590 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
591 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
592
593 DELETE FROM t1;
594
595 START TRANSACTION;
596 INSERT INTO t1 VALUES(1, CAST('["HelloMySQL", "Hello"]' AS JSON));
597 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
598 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
599 ROLLBACK;
600
601 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
602 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
603
604 CHECK TABLE t1;
605
606 DROP TABLE t1;
607
608
609
610 --echo # Test the UPDATE which only updates part of the multi-value field,
611 --echo # to make sure the partial delete + insert works
612
613 # Test update on multi-value field only
614 CREATE TABLE IF NOT EXISTS t1 (
615 i INT NOT NULL PRIMARY KEY,
616 j JSON DEFAULT (CAST('["HelloWorld"]' AS JSON)),
617 KEY mv_idx_binary (( CAST(j->'$[*]' AS BINARY(12) ARRAY) )) );
618
619 INSERT INTO t1 VALUES(1, CAST('["HelloWorld", "Hello"]' AS JSON));
620 INSERT INTO t1 VALUES(2, CAST('["HelloMySQL", "Hello"]' AS JSON));
621
622 UPDATE t1 SET j = CAST('["Helloworld", "Hello"]' AS JSON);
623
624 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
625 SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
626 SELECT * FROM t1 WHERE "Helloworld" MEMBER OF (j->'$[*]');
627 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
628
629 UPDATE t1 SET j = CAST('["Helloworld", "Hello", "World", "MySQL"]' AS JSON);
630
631 SELECT * FROM t1 WHERE "MySQL" MEMBER OF (j->'$[*]');
632 SELECT * FROM t1 WHERE "World" MEMBER OF (j->'$[*]');
633 SELECT * FROM t1 WHERE "Helloworld" MEMBER OF (j->'$[*]');
634 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
635
636 INSERT INTO t1 VALUES(3, CAST('["HelloInnoDB", "Hello", "MySQL", "hello"]' AS JSON));
637
638 SELECT * FROM t1 WHERE "MySQL" MEMBER OF (j->'$[*]');
639 SELECT * FROM t1 WHERE "World" MEMBER OF (j->'$[*]');
640 SELECT * FROM t1 WHERE "Helloworld" MEMBER OF (j->'$[*]');
641 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
642 SELECT * FROM t1 WHERE "HelloInnoDB" MEMBER OF (j->'$[*]');
643
644 UPDATE t1 SET j = CAST('["HelloInnoDB", "Hello", "mysql", "HELLO"]' AS JSON) WHERE "hello" MEMBER OF (j->'$[*]');
645
646 SELECT * FROM t1 WHERE "MySQL" MEMBER OF (j->'$[*]');
647 SELECT * FROM t1 WHERE "mysql" MEMBER OF (j->'$[*]');
648 SELECT * FROM t1 WHERE "World" MEMBER OF (j->'$[*]');
649 SELECT * FROM t1 WHERE "Helloworld" MEMBER OF (j->'$[*]');
650 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
651 SELECT * FROM t1 WHERE "HELLO" MEMBER OF (j->'$[*]');
652 SELECT * FROM t1 WHERE "HelloInnoDB" MEMBER OF (j->'$[*]');
653
654 UPDATE t1 SET j = CAST('["HelloInnoDB", "HELLO"]' AS JSON) WHERE "HELLO" MEMBER OF (j->'$[*]');
655
656 SELECT * FROM t1 WHERE "MySQL" MEMBER OF (j->'$[*]');
657 SELECT * FROM t1 WHERE "mysql" MEMBER OF (j->'$[*]');
658 SELECT * FROM t1 WHERE "World" MEMBER OF (j->'$[*]');
659 SELECT * FROM t1 WHERE "Helloworld" MEMBER OF (j->'$[*]');
660 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
661 SELECT * FROM t1 WHERE "HELLO" MEMBER OF (j->'$[*]');
662 SELECT * FROM t1 WHERE "HelloInnoDB" MEMBER OF (j->'$[*]');
663
664 DELETE FROM t1;
665
666 INSERT INTO t1 VALUES(3, CAST('["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X"]' AS JSON));
667
668 UPDATE t1 SET j = CAST('["a", "B", "C", "d", "E", "F", "g", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X"]' AS JSON);
669
670 SELECT * FROM t1 WHERE "a" MEMBER OF (j->'$[*]');
671 SELECT * FROM t1 WHERE "A" MEMBER OF (j->'$[*]');
672 SELECT * FROM t1 WHERE "d" MEMBER OF (j->'$[*]');
673 SELECT * FROM t1 WHERE "D" MEMBER OF (j->'$[*]');
674 SELECT * FROM t1 WHERE "g" MEMBER OF (j->'$[*]');
675 SELECT * FROM t1 WHERE "H" MEMBER OF (j->'$[*]');
676
677 # wait for purge to process the update undo record.
678 --source include/wait_innodb_all_purged.inc
679
680 CHECK TABLE t1;
681
682 TRUNCATE TABLE t1;
683
684 SELECT * FROM t1;
685
686
687 # Test rollback on multi-value field update only
688 INSERT INTO t1 VALUES(1, CAST('["HelloWorld", "Hello"]' AS JSON));
689 INSERT INTO t1 VALUES(2, CAST('["HelloMySQL", "Hello"]' AS JSON));
690
691 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
692 SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
693 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
694
695 START TRANSACTION;
696 UPDATE t1 SET j = CAST('["Helloworld", "Hello"]' AS JSON);
697 ROLLBACK;
698
699 CHECK TABLE t1;
700
701 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
702 SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
703 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
704 SELECT * FROM t1 WHERE "Helloworld" MEMBER OF (j->'$[*]');
705
706 START TRANSACTION;
707 DELETE FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
708 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
709 UPDATE t1 SET j = CAST('["HelloMySQL", "Hello", "hello", "mysql"]' AS JSON);
710 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
711 SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
712 SELECT * FROM t1 WHERE "mysql" MEMBER OF (j->'$[*]');
713 ROLLBACK;
714
715 CHECK TABLE t1;
716
717 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
718 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
719 SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
720 SELECT * FROM t1 WHERE "mysql" MEMBER OF (j->'$[*]');
721
722 DELETE FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
723 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
724 UPDATE t1 SET j = CAST('["HelloMySQL", "Hello", "hello", "mysql", "MySQL"]' AS JSON);
725
726 START TRANSACTION;
727 UPDATE t1 SET j = CAST('["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]' AS JSON);
728 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
729 SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
730 SELECT * FROM t1 WHERE "mysql" MEMBER OF (j->'$[*]');
731 SELECT * FROM t1 WHERE "mySQL" MEMBER OF (j->'$[*]');
732 SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
733 UPDATE t1 SET j = CAST('["HelloMySQL", "Hello"]' AS JSON);
734 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
735 SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
736 SELECT * FROM t1 WHERE "mysql" MEMBER OF (j->'$[*]');
737 SELECT * FROM t1 WHERE "mySQL" MEMBER OF (j->'$[*]');
738 SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
739 ROLLBACK;
740
741 CHECK TABLE t1;
742
743 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
744 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
745 SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
746 SELECT * FROM t1 WHERE "mysql" MEMBER OF (j->'$[*]');
747
748
749 TRUNCATE TABLE t1;
750
751 CHECK TABLE t1;
752
753 SELECT * FROM t1;
754
755
756 # Test update primary key will also lead to multi-value index update
757 INSERT INTO t1 VALUES(1, CAST('["HelloWorld", "Hello"]' AS JSON));
758 INSERT INTO t1 VALUES(2, CAST('["HelloMySQL", "Hello"]' AS JSON));
759
760 UPDATE t1 SET i = 3 WHERE i = 2;
761 UPDATE t1 SET i = 4, j = CAST('["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]' AS JSON) WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
762 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
763 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
764 SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
765 SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
766
767 --echo # Force a multi-table UPDATE, to test:
768 --echo # Bug #98691: Updating a multi-table view has problem if one sub-table is constant
769
770 CREATE VIEW vv AS SELECT t1.* FROM t1 STRAIGHT_JOIN (SELECT 1) AS dt;
771 EXPLAIN UPDATE vv SET i = 3 WHERE i = 4;
772 UPDATE vv SET i = 3 WHERE i = 4;
773 UPDATE vv SET i = 4 WHERE i = 3;
774 # Not testing DELETE, as DELETE from multi-table view isn't allowed.
775 DROP VIEW vv;
776
777 START TRANSACTION;
778 UPDATE t1 SET i = 5 WHERE i = 4;
779 ROLLBACK;
780
781 CHECK TABLE t1;
782
783 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
784 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
785 SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
786 SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
787
788 START TRANSACTION;
789 UPDATE t1 SET i = 6, j = CAST('["HelloMySQL", "mySQL", "MYSQL"]' AS JSON) WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
790 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
791 SELECT * FROM t1 WHERE "mySQL" MEMBER OF (j->'$[*]');
792 SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
793 ROLLBACK;
794
795 CHECK TABLE t1;
796
797 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
798 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
799 SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
800 SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
801
802 DROP TABLE t1;
803
804
805 # Test update on the multi-value index with normal fields
806 CREATE TABLE t1 (
807 id INT NOT NULL PRIMARY KEY,
808 a INT,
809 b INT,
810 j JSON DEFAULT (CAST('["HelloWorld"]' AS JSON)),
811 KEY mv_idx_binary(a, (( CAST(j->'$[*]' AS BINARY(12) ARRAY) )), b));
812
813 INSERT INTO t1 VALUES(1, 1, 1, CAST('["HelloWorld", "Hello"]' AS JSON));
814
815 UPDATE t1 SET b = 2;
816 SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
817 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
818
819 UPDATE t1 SET a = 2;
820 SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
821 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
822
823 UPDATE t1 SET b = 3, j = CAST('["HelloMySQL", "mySQL", "MYSQL"]' AS JSON) WHERE "HelloWorld" MEMBER OF (j->'$[*]');
824
825 SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
826 SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
827
828 UPDATE t1 SET a = 3, j = CAST('["HelloMySQL", "mySQL", "MYSQL", "hello"]' AS JSON) WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
829
830 SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
831 SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
832 SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
833
834 CHECK TABLE t1;
835
836 TRUNCATE TABLE t1;
837
838 INSERT INTO t1 VALUES(1, 1, 1, CAST('["HelloWorld", "Hello"]' AS JSON));
839
840 START TRANSACTION;
841 UPDATE t1 SET b = 2;
842 SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
843 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
844
845 UPDATE t1 SET a = 2;
846 SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
847 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
848 ROLLBACK;
849
850 CHECK TABLE t1;
851
852 SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
853 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
854
855 START TRANSACTION;
856 UPDATE t1 SET b = 3, j = CAST('["HelloMySQL", "mySQL", "MYSQL"]' AS JSON) WHERE "HelloWorld" MEMBER OF (j->'$[*]');
857 SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
858
859 UPDATE t1 SET a = 3, j = CAST('["HelloMySQL", "mySQL", "MYSQL", "hello"]' AS JSON) WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
860 SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
861 SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
862 SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
863 ROLLBACK;
864
865 CHECK TABLE t1;
866
867 SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
868 SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
869
870 SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
871 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
872
873 DROP TABLE t1;
874
875
876 # Test developed when working on Bug#31479282 - server crashed when adding empty string to column with json index.
877 CREATE TABLE t1 (
878 id int NOT NULL,
879 j JSON DEFAULT NULL,
880 PRIMARY KEY (id),
881 KEY j_key123 ((CAST(json_extract(j,_utf8mb4'$') AS CHAR(64) ARRAY)))
882 );
883
884 INSERT INTO t1 values (1000, CONCAT('[""]'));
885
886 DROP TABLE t1;
887
888 # Basic test cases for partitioned table with multi-value columns
889 CREATE TABLE t1 (i INT, j JSON, KEY mv_idx_int ((CAST(j->'$[*]' AS SIGNED ARRAY))) ) PARTITION BY KEY(i) PARTITIONS 2;
890 INSERT INTO t1 VALUES(10,'[4, 5, 6]'), (1, '[1, 2, 3]'), (5, '[3, 4, 5]'), (8, '[4, 14, 24]');
891
892 SELECT * FROM t1 PARTITION (p0) WHERE 4 MEMBER OF (j->'$[*]');
893 SELECT * FROM t1 PARTITION (p1) WHERE 4 MEMBER OF (j->'$[*]');
894
895 SELECT * FROM t1 WHERE 4 MEMBER OF (j->'$[*]');
896
897 SELECT * FROM t1 PARTITION (p0) WHERE 1 MEMBER OF (j->'$[*]');
898 SELECT * FROM t1 PARTITION (p1) WHERE 1 MEMBER OF (j->'$[*]');
899
900 SELECT * FROM t1 WHERE 1 MEMBER OF (j->'$[*]');
901
902 DROP TABLE t1;
903
904 --echo #
905 --echo # Bug#33334928: SELECT COUNT(*) using multi-valued index
906 --echo # reports wrong number of rows.
907 --echo #
908
909 CREATE TABLE t (
910 id int NOT NULL,
911 doc_id int DEFAULT NULL,
912 doc json DEFAULT NULL,
913 PRIMARY KEY (id),
914 KEY mv_id (id,(cast(json_extract(doc,'$.val') as unsigned array))),
915 KEY mv_doc_id (doc_id,(cast(json_extract(doc,'$.val') as unsigned array)))
916 );
917 INSERT INTO t values (1, 2, '{"val":[1,2,3,4,5]}'), (2, 1, '{"val":[2,3,4,5]}'), (3, 2, '{"val":[2,3,4,5]}'), (4, NULL, '{"val":[3,4,5]}'), (5, 2, '{"val":[4,5]}');
918
919 ANALYZE TABLE t;
920
921 EXPLAIN SELECT COUNT(*) FROM t WHERE id = '1';
922 SELECT COUNT(*) FROM t WHERE id = '1';
923
924 EXPLAIN SELECT COUNT(*) FROM t FORCE INDEX (mv_id) WHERE id = '1';
925 SELECT COUNT(*) FROM t FORCE INDEX (mv_id) WHERE id = '1';
926
927 EXPLAIN SELECT COUNT(*) FROM t FORCE INDEX (mv_doc_id) WHERE doc_id = '2';
928 SELECT COUNT(*) FROM t FORCE INDEX (mv_doc_id) WHERE doc_id = '2';
929
930 EXPLAIN SELECT COUNT(*) FROM t FORCE INDEX (mv_doc_id) WHERE doc_id = '2' OR doc_id IS NULL;
931 SELECT COUNT(*) FROM t FORCE INDEX (mv_doc_id) WHERE doc_id = '2' OR doc_id IS NULL;
932
933 EXPLAIN SELECT * FROM t WHERE id = '1';
934 SELECT * FROM t WHERE id = '1';
935
936 EXPLAIN SELECT * FROM t FORCE INDEX (mv_id) WHERE id = '1';
937 SELECT * FROM t FORCE INDEX (mv_id) WHERE id = '1';
938
939 EXPLAIN SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id = '2';
940 SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id = '2';
941
942 EXPLAIN SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id = '2' OR doc_id IS NULL;
943 # Hypergraph ignores FORCE INDEX, so we need --sorted_result in case
944 # it does not use the index.
945 --sorted_result
946 SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id = '2' OR doc_id IS NULL;
947
948 EXPLAIN SELECT * FROM t WHERE doc_id is NOT NULL;
949 SELECT * FROM t WHERE doc_id is NOT NULL;
950
951 EXPLAIN SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id is NOT NULL;
952 --sorted_result
953 SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id is NOT NULL;
954
955 EXPLAIN SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id <> 0;
956 --sorted_result
957 SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id <> 0;
958
959 EXPLAIN SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id > 0;
960 --sorted_result
961 SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id > 0;
962
963 EXPLAIN SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id < 2 OR doc_id IS NULL;
964 --sorted_result
965 SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id < 2 OR doc_id IS NULL;
966
967 EXPLAIN SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id >= 1 AND doc_id < 2;
968 SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id >= 1 AND doc_id < 2;
969
970 DROP TABLE t;
971
972 --echo #
973 --echo # Bug #32725063 CRASH WITH MULTI-VALUED INDEXES IN
974 --echo # COMBINATION WITH AT LEAST GENERATED FK
975
976 CREATE TABLE t1 (id BIGINT NOT NULL AUTO_INCREMENT, data JSON,PRIMARY KEY (id));
977 ALTER TABLE t1 ADD INDEX ix_t1_idArray ((CAST(data->'$.idArray' AS UNSIGNED ARRAY))),
978 ADD COLUMN parent_id BIGINT GENERATED ALWAYS AS (data->'$.parentID') STORED,
979 ADD INDEX ix_t1_parent (parent_id),
980 ADD CONSTRAINT fk_t1_parent FOREIGN KEY (parent_id) REFERENCES t1 (id) ON DELETE CASCADE;
981 INSERT INTO t1 (data) VALUES ('{\"idArray\":[1]}');
982 INSERT INTO t1 (data) VALUES ('{\"parentID\":1,\"idArray\":[1]}');
983 DELETE FROM t1 WHERE id = 1;
984 DROP TABLE t1;
985
986 # Test case where parent and child tables are seperate
987 CREATE TABLE t1 (id BIGINT NOT NULL , PRIMARY KEY (id));
988 CREATE TABLE t2(data JSON,child_id bigint GENERATED ALWAYS AS (data->'$.parentID') STORED,key(child_id));
989 ALTER TABLE t2 ADD INDEX ix_t1_idArray ((CAST(data->'$.idArray' AS UNSIGNED ARRAY)));
990 ALTER TABLE t2 ADD CONSTRAINT fk_t1_parent FOREIGN KEY (child_id) REFERENCES t1 (id) ON DELETE CASCADE;
991 INSERT INTO t1 VALUES (1);
992 INSERT INTO t2 (data) VALUES ('{\"parentID\":1,\"idArray\":[1]}');
993 DELETE FROM t1 WHERE id = 1;
994 DROP TABLE t2,t1;
995
996 --source include/wait_until_count_sessions.inc