Debian Codegraph @

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

raw
1 CREATE TABLE t1 (id INT UNSIGNED NOT NULL, arr JSON NOT NULL, PRIMARY KEY (id));
2 ALTER TABLE t1 ADD INDEX test_categories ((CAST(arr AS UNSIGNED ARRAY)));
3 DROP TABLE t1;
4 CREATE TABLE t1 (id INT NOT NULL KEY AUTO_INCREMENT, f1 JSON);
5 CREATE INDEX i1 ON t1((CAST(f1->"$[*]" AS UNSIGNED ARRAY)));
6 INSERT INTO t1(f1) VALUES(CAST('[1333,1321]' AS JSON));
7 SELECT * FROM t1;
8 id f1
9 1 [1333, 1321]
10 DROP TABLE t1;
11 CREATE TABLE t1 (id INT NOT NULL KEY AUTO_INCREMENT, f1 JSON);
12 CREATE INDEX i1 ON t1((CAST(f1->"$[*]" AS CHAR(10) ARRAY)));
13 INSERT INTO t1(f1) VALUES(CAST('["13.33","13.21"]' AS JSON));
14 SELECT * FROM t1;
15 id f1
16 1 ["13.33", "13.21"]
17 DROP TABLE t1;
18 CREATE TABLE t1 (
19 j4 json ,
20 KEY mv_idx_binary ((( CAST(j4->'$[*]' AS BINARY(10) ARRAY))), (json_depth(j4)), (json_valid(j4))) USING BTREE);
21 INSERT INTO t1 VALUES ('["foobar"]');
22 SELECT * FROM t1 WHERE "foobar" MEMBER OF (j4->'$[*]');
23 j4
24 ["foobar"]
25 DELETE FROM t1;
26 DROP TABLE t1;
27 CREATE TABLE t1 (id INT NOT NULL KEY AUTO_INCREMENT, f1 JSON);
28 CREATE INDEX i1 ON t1((CAST(f1->"$[*]" AS DECIMAL(4,2) ARRAY)));
29 INSERT INTO t1(f1) VALUES(CAST('["13.33","13.21"]' AS JSON));
30 UPDATE t1 SET f1 = NULL;
31 SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
32 id f1
33 SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
34 id f1
35 SELECT * FROM t1 WHERE f1 IS NULL;
36 id f1
37 1 NULL
38 UPDATE t1 SET f1 = CAST('["13.33","13.21"]' AS JSON);
39 SELECT * FROM t1 WHERE f1 IS NULL;
40 id f1
41 SELECT * FROM t1 WHERE 12.34 MEMBER OF (f1->'$[*]');
42 id f1
43 SELECT * FROM t1 WHERE 56.78 MEMBER OF (f1->'$[*]');
44 id f1
45 SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
46 id f1
47 1 ["13.33", "13.21"]
48 SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
49 id f1
50 1 ["13.33", "13.21"]
51 UPDATE t1 SET f1 = CAST('["12.34","56.78"]' AS JSON);
52 CHECK TABLE t1;
53 Table Op Msg_type Msg_text
54 test.t1 check status OK
55 SELECT * FROM t1 WHERE 12.34 MEMBER OF (f1->'$[*]');
56 id f1
57 1 ["12.34", "56.78"]
58 SELECT * FROM t1 WHERE 56.78 MEMBER OF (f1->'$[*]');
59 id f1
60 1 ["12.34", "56.78"]
61 SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
62 id f1
63 SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
64 id f1
65 UPDATE t1 SET f1 = CAST('["43.21","98.76", "76.54"]' AS JSON);
66 CHECK TABLE t1;
67 Table Op Msg_type Msg_text
68 test.t1 check status OK
69 SELECT * FROM t1 WHERE 12.34 MEMBER OF (f1->'$[*]');
70 id f1
71 SELECT * FROM t1 WHERE 56.78 MEMBER OF (f1->'$[*]');
72 id f1
73 SELECT * FROM t1 WHERE 43.21 MEMBER OF (f1->'$[*]');
74 id f1
75 1 ["43.21", "98.76", "76.54"]
76 SELECT * FROM t1 WHERE 98.76 MEMBER OF (f1->'$[*]');
77 id f1
78 1 ["43.21", "98.76", "76.54"]
79 SELECT * FROM t1 WHERE 76.54 MEMBER OF (f1->'$[*]');
80 id f1
81 1 ["43.21", "98.76", "76.54"]
82 DROP TABLE t1;
83 CREATE TEMPORARY TABLE IF NOT EXISTS t1 (
84 j JSON DEFAULT (CAST('["HelloWorld"]' AS JSON)),
85 KEY mv_idx_binary (( CAST(j->'$[*]' AS CHAR(10) ARRAY) )) );
86 INSERT INTO t1 VALUES() ;
87 SELECT * FROM t1;
88 j
89 ["HelloWorld"]
90 UPDATE t1 SET j = CAST('["HelloMySQL"]' AS JSON);
91 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
92 j
93 ["HelloMySQL"]
94 DELETE FROM t1;
95 DROP TABLE t1;
96 CREATE TABLE t1(j JSON, KEY i1((CAST(j AS UNSIGNED ARRAY))));
97 INSERT INTO t1 VALUES('[1,2,3]'), ('[]'), (NULL);
98 SELECT * FROM t1;
99 j
100 [1, 2, 3]
101 []
102 NULL
103 UPDATE t1 SET j = '[]' WHERE j IS NULL;
104 SELECT * FROM t1;
105 j
106 [1, 2, 3]
107 []
108 []
109 DELETE FROM t1;
110 INSERT INTO t1 VALUES('[1,2,3]'), ('[]'), (NULL);
111 UPDATE t1 SET j = NULL;
112 SELECT * FROM t1;
113 j
114 NULL
115 NULL
116 NULL
117 DELETE FROM t1;
118 CHECK TABLE t1;
119 Table Op Msg_type Msg_text
120 test.t1 check status OK
121 DROP TABLE t1;
122 CREATE TABLE t1 (
123 j JSON DEFAULT (CAST('["HelloWorld"]' AS JSON)),
124 KEY mv_idx_binary ((CAST(j->'$[*]' AS BINARY(10) ARRAY))));
125 INSERT INTO t1 VALUES();
126 ALTER TABLE t1 DROP INDEX mv_idx_binary ;
127 CREATE INDEX mv_idx_binary ON t1 ((CAST( j -> '$[*]' AS BINARY(10) ARRAY))) VISIBLE;
128 SELECT * FROM t1;
129 j
130 ["HelloWorld"]
131 DROP TABLE t1;
132 CREATE TABLE IF NOT EXISTS t1 (
133 j2 JSON DEFAULT (CAST('[9,8,0,1]' AS JSON)),
134 j3 JSON DEFAULT (CAST('["foobar"]' AS JSON)),
135 KEY mv_idx_unsigned (( CAST(j2->'$[*]' AS UNSIGNED ARRAY) )) ,
136 KEY mv_idx_char (( CAST(j3->'$[*]' AS CHAR(10) ARRAY) ))
137 );
138 INSERT INTO t1(j3) VALUES(CAST('[ "foobar", "HelloWorld", "MySQL", "1234" ]' AS JSON));
139 INSERT INTO t1(j3) VALUES(CAST('[ "Hello", "World", "InnoDB", "56" ]' AS JSON));
140 SELECT * FROM t1;
141 j2 j3
142 [9, 8, 0, 1] ["foobar", "HelloWorld", "MySQL", "1234"]
143 [9, 8, 0, 1] ["Hello", "World", "InnoDB", "56"]
144 DELETE FROM t1;
145 CHECK TABLE t1;
146 Table Op Msg_type Msg_text
147 test.t1 check status OK
148 DROP TABLE t1;
149 CREATE TABLE t1 (j JSON, key i1((cast(j->"$.id" AS CHAR(10) ARRAY))), key i2((cast(j->"$.id" AS UNSIGNED ARRAY))));
150 INSERT INTO t1 VALUES('{"id":1}');
151 INSERT INTO t1 VALUES('{"group":2}');
152 INSERT INTO t1 VALUES(NULL);
153 SELECT * FROM t1;
154 j
155 {"id": 1}
156 {"group": 2}
157 NULL
158 DROP TABLE t1;
159 CREATE TABLE t1(j JSON);
160 INSERT INTO t1 VALUES('[]'), ('null');
161 CREATE INDEX mv_idx_items_of_array ON t1 ((CAST(j->'$' AS UNSIGNED ARRAY)));
162 ERROR 22018: Invalid JSON value for CAST for functional index 'mv_idx_items_of_array'.
163 DELETE FROM t1;
164 INSERT INTO t1 VALUES ('[]'), ('[6,2]'), ('[]'), ('[1,4]'), ('[5]');
165 CREATE INDEX mv_idx_items_of_array ON t1 ((CAST(j->'$' AS UNSIGNED ARRAY)));
166 EXPLAIN SELECT * FROM t1 WHERE 5 MEMBER OF (j->'$');
167 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
168 1 SIMPLE t1 NULL ref mv_idx_items_of_array mv_idx_items_of_array 9 const 1 100.00 Using where
169 Warnings:
170 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where json'5' member of (cast(json_extract(`j`,_utf8mb4'$') as unsigned array))
171 SELECT * FROM t1 FORCE INDEX(mv_idx_items_of_array) WHERE 5 MEMBER OF (j->'$');
172 j
173 [5]
174 DROP TABLE t1;
175 CREATE TABLE IF NOT EXISTS t1 (
176 i INT DEFAULT 100,
177 j JSON DEFAULT (CAST('["HelloWorld"]' AS JSON)),
178 KEY mv_idx_binary (( CAST(j->'$[*]' AS BINARY(10) ARRAY) )) );
179 INSERT INTO t1 VALUES();
180 SELECT * FROM t1;
181 i j
182 100 ["HelloWorld"]
183 START TRANSACTION;
184 DELETE FROM t1;
185 SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
186 i j
187 100 ["HelloWorld"]
188 COMMIT;
189 SELECT * FROM t1;
190 i j
191 DROP TABLE t1;
192 CREATE TABLE t1 (f1 JSON, UNIQUE KEY i1((CAST(f1->"$[*]" AS CHAR(10) ARRAY))));
193 INSERT INTO t1(f1) VALUES(CAST('["abc", "abc "]' AS JSON));
194 START TRANSACTION;
195 SELECT * FROM t1 WHERE "abc" MEMBER OF (f1->"$[*]");
196 f1
197 ["abc", "abc "]
198 UPDATE t1 SET f1 = CAST('["def"]' AS JSON);
199 SELECT * FROM t1 WHERE "def" MEMBER OF (f1->"$[*]");
200 f1
201 ROLLBACK;
202 DROP TABLE t1;
203 CREATE TABLE t1 (id INT NOT NULL KEY AUTO_INCREMENT, f1 JSON);
204 CREATE INDEX i1 ON t1((CAST(f1->"$[*]" AS UNSIGNED ARRAY)));
205 INSERT INTO t1 VALUES(0, NULL);
206 DELETE FROM t1;
207 INSERT INTO t1 VALUES(1, NULL);
208 SELECT * FROM t1;
209 id f1
210 1 NULL
211 DELETE FROM t1;
212 INSERT INTO t1 VALUES(2, CAST('[10]' AS JSON));
213 DELETE FROM t1;
214 INSERT INTO t1 VALUES(3, CAST('[10]' AS JSON));
215 CHECK TABLE t1;
216 Table Op Msg_type Msg_text
217 test.t1 check status OK
218 SELECT * FROM t1;
219 id f1
220 3 [10]
221 DROP TABLE t1;
222 CREATE TABLE t1 (id INT NOT NULL KEY AUTO_INCREMENT, f1 JSON);
223 CREATE INDEX i1 ON t1((CAST(f1->"$[*]" AS DECIMAL(4,2) ARRAY)));
224 INSERT INTO t1(f1) VALUES(CAST('["13.33","13.21"]' AS JSON));
225 UPDATE t1 SET f1 = NULL;
226 UPDATE t1 SET f1 = CAST('["13.33","13.21"]' AS JSON);
227 CHECK TABLE t1;
228 Table Op Msg_type Msg_text
229 test.t1 check status OK
230 SELECT * FROM t1 WHERE f1 IS NULL;
231 id f1
232 SELECT * FROM t1 WHERE 12.34 MEMBER OF (f1->'$[*]');
233 id f1
234 SELECT * FROM t1 WHERE 56.78 MEMBER OF (f1->'$[*]');
235 id f1
236 SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
237 id f1
238 1 ["13.33", "13.21"]
239 SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
240 id f1
241 1 ["13.33", "13.21"]
242 UPDATE t1 SET f1 = CAST('["12.34","56.78"]' AS JSON);
243 UPDATE t1 SET f1 = CAST('["43.21","98.76", "76.54"]' AS JSON);
244 CHECK TABLE t1;
245 Table Op Msg_type Msg_text
246 test.t1 check status OK
247 SELECT * FROM t1 WHERE 12.34 MEMBER OF (f1->'$[*]');
248 id f1
249 SELECT * FROM t1 WHERE 56.78 MEMBER OF (f1->'$[*]');
250 id f1
251 SELECT * FROM t1 WHERE 43.21 MEMBER OF (f1->'$[*]');
252 id f1
253 1 ["43.21", "98.76", "76.54"]
254 SELECT * FROM t1 WHERE 98.76 MEMBER OF (f1->'$[*]');
255 id f1
256 1 ["43.21", "98.76", "76.54"]
257 SELECT * FROM t1 WHERE 76.54 MEMBER OF (f1->'$[*]');
258 id f1
259 1 ["43.21", "98.76", "76.54"]
260 DROP TABLE t1;
261 CREATE TABLE `t1` (
262 `id` int(11) NOT NULL AUTO_INCREMENT,
263 `j1` json DEFAULT (cast(_utf8mb4'[5,-1,0]' as json)),
264 `j2` json DEFAULT (cast(_utf8mb4'[9,8,0,1]' as json)),
265 `j3` json DEFAULT (cast(_utf8mb4'["foobar"]' as json)),
266 `j4` json DEFAULT (cast(_utf8mb4'["HelloWorld"]' as json)),
267 `j5` json DEFAULT (json_array(now())),
268 `j7` json DEFAULT (json_array(cast(now() as time))),
269 `extra` json DEFAULT NULL,
270 PRIMARY KEY (`id`),
271 UNIQUE KEY `mv_idx_binary` ((json_length(`j3`)),(cast(json_extract(`j4`,_utf8mb4'$[*]') as binary(10) array)),(json_depth(`j2`))),
272 KEY `mv_idx_signed` ((cast(json_extract(`j1`,_utf8mb4'$[*]') as signed array))),
273 KEY `mv_idx_unsigned` ((cast(json_extract(`j2`,_utf8mb4'$[*]') as unsigned array))),
274 KEY `mv_idx_char` ((cast(json_extract(`j3`,_utf8mb4'$[*]') as char(10) array))),
275 KEY `mv_idx_time` ((cast(json_extract(`j7`,_utf8mb4'$[*]') as time array)))
276 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
277 Warnings:
278 Warning 1681 Integer display width is deprecated and will be removed in a future release.
279 INSERT INTO t1 ( j4 ) VALUES( JSON_OBJECT( 'key1' , CAST( '2019-11-27 03:43:53' AS DATETIME)) );
280 INSERT INTO t1 VALUES();
281 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)) ) );
282 UPDATE t1 SET j4 = '["foobar", "HelloWorld", "MySQL", "1234" ]' WHERE NOT ('');
283 ERROR 23000: Duplicate entry '1-["base64:t-2' for key 't1.mv_idx_binary'
284 UPDATE t1 SET j7 = JSON_OBJECT( 'key1' , JSON_OBJECT()) WHERE JSON_OBJECT('key1' , CAST( '[]' AS JSON)) MEMBER OF ( j1 -> '$[*]' );
285 DELETE FROM t1;
286 CHECK TABLE t1;
287 Table Op Msg_type Msg_text
288 test.t1 check status OK
289 DROP TABLE t1;
290 CREATE TABLE IF NOT EXISTS t1 (
291 j2 JSON DEFAULT (CAST('[9,8,0,1]' AS JSON)),
292 j8 JSON DEFAULT (JSON_ARRAY(3.1456,6.022,-1)),
293 KEY mv_idx_unsigned (( CAST(j2->'$[*]' AS UNSIGNED ARRAY) )) ,
294 KEY mv_idx_decimal (( CAST(j8->'$[*]' AS DECIMAL(10,4) ARRAY) )) );
295 ALTER TABLE t1 DROP INDEX mv_idx_decimal;
296 INSERT INTO t1 VALUES();
297 DELETE FROM t1;
298 ALTER TABLE t1 DROP INDEX mv_idx_unsigned;
299 SELECT * FROM t1;
300 j2 j8
301 DROP TABLE t1;
302 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) )) ) ;
303 SET autocommit=0 ;
304 SET SESSION sql_mode ='' ;
305 DELETE FROM t1 WHERE 0 ;
306 INSERT INTO t1 SELECT * FROM t1 GROUP BY j2 ;
307 INSERT INTO t1 ( j4 ) VALUES( JSON_OBJECT( 'key1' , CAST( '2007-08-18' AS DATE)) ) ;
308 INSERT INTO t1 VALUES() ;
309 EXPLAIN DELETE FROM t1 ;
310 ALTER TABLE t1 RENAME KEY mv_idx_signed to new_idx_1 ;
311 ALTER TABLE t1 RENAME KEY new_idx_1 to mv_idx_signed ;
312 UPDATE t1 SET j5 = JSON_OBJECT( 'key1' , true) WHERE ( 1 ) AND ( 0 ) XOR ( '' ) ;
313 ALTER TABLE t1 DROP INDEX mv_idx_datetime ;
314 ALTER TABLE t1 ADD COLUMN extra JSON ;
315 UPDATE t1 SET extra = j1 ;
316 ALTER TABLE t1 DROP INDEX mv_idx_date ;
317 INSERT INTO t1 VALUES() ;
318 DELETE FROM t1 ;
319 EXPLAIN UPDATE t1 IGNORE KEY FOR JOIN ( mv_idx_decimal ) SET j4 = JSON_OBJECT( 'key1' , CAST( '12:58:44' AS TIME)) WHERE ( 1 ) OR ( '' ) ;
320 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
321 1 UPDATE t1 NULL index NULL PRIMARY 4 NULL 1 100.00 NULL
322 Warnings:
323 Note 1003 update `test`.`t1` IGNORE INDEX FOR JOIN (`mv_idx_decimal`) set `test`.`t1`.`j4` = json_object('key1',cast('12:58:44' as time))
324 INSERT INTO t1 SELECT * FROM t1 GROUP BY j2 ORDER BY j6 DESC ;
325 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 ;
326 INSERT INTO t1 ( j2 ) VALUES( JSON_OBJECT( 'key1' , 'to' ) ) ;
327 ALTER TABLE t1 DROP INDEX mv_idx_decimal ;
328 EXPLAIN SELECT * FROM t1 WHERE 0 ORDER BY j6 ;
329 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
330 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
331 Warnings:
332 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`j1` AS `j1`,`test`.`t1`.`j2` AS `j2`,`test`.`t1`.`j3` AS `j3`,`test`.`t1`.`j4` AS `j4`,`test`.`t1`.`j5` AS `j5`,`test`.`t1`.`j6` AS `j6`,`test`.`t1`.`j7` AS `j7`,`test`.`t1`.`j8` AS `j8`,`test`.`t1`.`extra` AS `extra` from `test`.`t1` where false order by `test`.`t1`.`j6`
333 INSERT INTO t1 VALUES() ;
334 UPDATE t1 SET j1 = CAST( '[ -1 , 10, 7, 6, -999 ]' AS JSON ) WHERE 0 ;
335 DELETE FROM t1 WHERE NOT ( '' ) ;
336 ALTER TABLE t1 RENAME KEY mv_idx_binary to new_idx_1 ;
337 ALTER TABLE t1 RENAME KEY new_idx_1 to mv_idx_binary ;
338 EXPLAIN INSERT INTO t1 ( j3 ) VALUES( JSON_OBJECT( 'key1' , CAST(null AS JSON)) ) ;
339 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
340 1 INSERT t1 NULL ALL NULL NULL NULL NULL NULL NULL NULL
341 Warnings:
342 Note 1003 insert into `test`.`t1` (`test`.`t1`.`j3`) values (json_object('key1',cast(NULL as json)))
343 DELETE FROM t1 ;
344 CREATE INDEX mv_idx_decimal ON t1 (( CAST( j8 -> '$[*]' AS DECIMAL(10,4) ARRAY) )) VISIBLE ;
345 INSERT INTO t1 VALUES() ;
346 INSERT INTO t1 ( j4 ) VALUES( JSON_OBJECT() ) ;
347 ALTER TABLE t1 RENAME KEY mv_idx_binary to new_idx_1 ;
348 ALTER TABLE t1 RENAME KEY new_idx_1 to mv_idx_binary ;
349 EXPLAIN DELETE FROM t1 WHERE '' ;
350 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
351 1 DELETE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
352 Warnings:
353 Note 1003 delete from `test`.`t1` where false
354 DELETE FROM t1 WHERE '2034-10-29 21:05:14' MEMBER OF ( j8 -> '$[*]' ) ;
355 INSERT INTO t1 VALUES() ;
356 UPDATE t1 SET j5 = '[7, 5, 1, -56, -45]' WHERE 0 ;
357 ALTER TABLE t1 DROP INDEX mv_idx_time ;
358 ALTER TABLE t1 DROP INDEX mv_idx_binary ;
359 ALTER TABLE t1 ADD INDEX mv_idx_date ( ( JSON_DEPTH( j3 ) ) ,( CAST( j6 -> '$.key1[*]' AS DATE ARRAY) ), ( JSON_LENGTH( j7 ) ) ) VISIBLE ;
360 INSERT INTO t1 ( j4 ) VALUES( JSON_OBJECT( 'key1' , CAST( '1978-09-05 12:08:06' AS DATETIME)) ) ;
361 ALTER TABLE t1 ADD INDEX mv_idx_time USING HASH ( ( JSON_LENGTH( j5 ) ) , ( JSON_DEPTH( j1 ) ) ,( CAST( j7 -> '$.key1' AS TIME ARRAY) )) INVISIBLE ;
362 Warnings:
363 Note 3502 This storage engine does not support the HASH index algorithm, storage engine default was used instead.
364 INSERT INTO t1 VALUES() ;
365 INSERT INTO t1 VALUES() ;
366 UPDATE t1 SET j3 = JSON_OBJECT( 'key1' , 3 ) WHERE ( '' ) XOR ( 1 ) OR ( 0 ) ;
367 ROLLBACK;
368 UPDATE t1 SET j3 = JSON_OBJECT( 'key1' , 3 ) WHERE ( '' ) XOR ( 1 ) OR ( 0 ) ;
369 DROP TABLE t1;
370 SET autocommit = default;
371 SET SESSION sql_mode = default;
372 SET autocommit=0;
373 SET SESSION sql_mode ='';
374 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) )) );
375 SELECT * FROM t1 WHERE NOT ( JSON_ARRAY(TIME(NOW()), CAST( '00:00:00' AS TIME)) MEMBER OF ( j2 -> '$[*]' ) ) GROUP BY j4 LIMIT 84;
376 id j1 j2 j3 j4 j5 j6 j7 j8
377 INSERT INTO t1 SELECT * FROM t1 FORCE KEY FOR JOIN ( mv_idx_decimal , mv_idx_char );
378 SELECT * FROM t1 IGNORE INDEX FOR ORDER BY ( mv_idx_date , mv_idx_binary ) WHERE ( 0 ) XOR ( 1 ) GROUP BY j5 ORDER BY j3;
379 id j1 j2 j3 j4 j5 j6 j7 j8
380 ALTER TABLE t1 DROP INDEX mv_idx_time;
381 INSERT INTO t1 VALUES();
382 CHECK TABLE t1;
383 Table Op Msg_type Msg_text
384 test.t1 check status OK
385 SELECT * FROM t1 WHERE 0;
386 id j1 j2 j3 j4 j5 j6 j7 j8
387 DELETE FROM t1;
388 CHECK TABLE t1;
389 Table Op Msg_type Msg_text
390 test.t1 check status OK
391 INSERT INTO t1 SELECT * FROM t1 GROUP BY j3 ORDER BY j7;
392 INSERT INTO t1 VALUES();
393 ALTER TABLE t1 ADD COLUMN extra JSON;
394 UPDATE t1 SET extra = j5;
395 ALTER TABLE t1 DROP INDEX mv_idx_datetime;
396 ALTER TABLE t1 DROP COLUMN j5;
397 ALTER TABLE t1 CHANGE COLUMN extra j5 JSON;
398 DELETE FROM t1 WHERE '';
399 INSERT INTO t1 ( j1 ) VALUES( JSON_OBJECT( 'key1' , CAST( '00:52:03' AS TIME)) );
400 ALTER TABLE t1 ADD COLUMN extra JSON;
401 UPDATE t1 SET extra = j5;
402 ALTER TABLE t1 DROP INDEX mv_idx_date;
403 SELECT id, j1, j2, j3, j4 FROM t1;
404 id j1 j2 j3 j4
405 2 [5, -1, 0] [9, 8, 0, 1] ["foobar"] ["HelloWorld"]
406 3 {"key1": "00:52:03.000000"} [9, 8, 0, 1] ["foobar"] ["HelloWorld"]
407 SELECT COUNT(*) FROM t1;
408 COUNT(*)
409 2
410 ALTER TABLE t1 DROP COLUMN j5;
411 CHECK TABLE t1;
412 Table Op Msg_type Msg_text
413 test.t1 check status OK
414 SELECT id, j1, j2, j3, j4 FROM t1 WHERE "foobar" MEMBER OF (j3->'$[*]');
415 id j1 j2 j3 j4
416 2 [5, -1, 0] [9, 8, 0, 1] ["foobar"] ["HelloWorld"]
417 3 {"key1": "00:52:03.000000"} [9, 8, 0, 1] ["foobar"] ["HelloWorld"]
418 SELECT id, j1, j2, j3, j4 FROM t1 WHERE "HelloWorld" MEMBER OF (j4->'$[*]');
419 id j1 j2 j3 j4
420 2 [5, -1, 0] [9, 8, 0, 1] ["foobar"] ["HelloWorld"]
421 3 {"key1": "00:52:03.000000"} [9, 8, 0, 1] ["foobar"] ["HelloWorld"]
422 DROP TABLE t1;
423 SET autocommit = default;
424 SET SESSION sql_mode = default;
425 SET SESSION sql_mode = '';
426 CREATE TABLE `t1` (
427 `id` int(11) NOT NULL AUTO_INCREMENT,
428 `j1` json DEFAULT (cast(_utf8mb4'[5,-1,0]' as json)),
429 `j2` json DEFAULT (cast(_utf8mb4'[9,8,0,1]' as json)),
430 `j3` json DEFAULT (cast(_utf8mb4'["foobar"]' as json)),
431 `j4` json DEFAULT (cast(_utf8mb4'["HelloWorld"]' as json)),
432 `j5` json DEFAULT (json_array(now())),
433 `j6` json DEFAULT (json_array(cast(now() as date),cast(now() as date),cast(now() as date))),
434 `j7` json DEFAULT (json_array(cast(now() as time))),
435 `j8` json DEFAULT (json_array(3.1456,6.022,-(1))),
436 `extra` json DEFAULT NULL,
437 PRIMARY KEY (`id`),
438 UNIQUE KEY `mv_idx_signed` ((json_length(`j5`)),(cast(json_extract(`j1`,_utf8mb4'$.key1[*]') as signed array)),(json_depth(`j3`))) USING BTREE,
439 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')))),
440 KEY `mv_idx_unsigned` ((json_length(`j3`)),(json_valid(`j4`)),(cast(json_extract(`j2`,_utf8mb4'$[*]') as unsigned array))) USING BTREE /*!80000 INVISIBLE */,
441 KEY `mv_idx_decimal` ((cast(json_extract(`j8`,_utf8mb4'$[*]') as decimal(10, 4) array))) USING BTREE
442 ) ENGINE=InnoDB AUTO_INCREMENT=1283 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
443 Warnings:
444 Warning 1681 Integer display width is deprecated and will be removed in a future release.
445 ALTER TABLE t1 ADD UNIQUE INDEX mv_idx_date USING BTREE ( ( JSON_DEPTH( j3 ) ) ,( CAST( j6 -> '$[*]' AS DATE ARRAY) ), ( JSON_LENGTH( j1 ) ) ) VISIBLE;
446 UPDATE t1 SET j2 = JSON_OBJECT( 'key1' , 1772955722 ) WHERE ( JSON_OVERLAPS( j7 -> '$[*]' , '[ ]' ) ) XOR ( 1 );
447 INSERT INTO t1 SELECT * FROM t1 WHERE JSON_CONTAINS( JSON_ARRAY(-10,100,65,JSON_ARRAY(-101,25,12,0)) , j6 -> '$[*]' ) LIMIT 55;
448 INSERT INTO t1 SELECT * FROM t1 GROUP BY j8;
449 DELETE FROM t1 WHERE ( 0 ) XOR ( 1 ) AND ( JSON_CONTAINS( '[45, 61, 54, 0]' , j3 -> '$[*]' ) );
450 INSERT INTO t1 VALUES();
451 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 -> '$[*]' );
452 DELETE FROM t1;
453 SELECT * FROM t1 LIMIT 73;
454 id j1 j2 j3 j4 j5 j6 j7 j8 extra
455 INSERT INTO t1 VALUES();
456 INSERT INTO t1 SELECT * FROM t1 USE KEY FOR GROUP BY ( mv_idx_unsigned , mv_idx_signed ) GROUP BY j3 ORDER BY j1;
457 ERROR 42000: Key 'mv_idx_unsigned' doesn't exist in table 't1'
458 SELECT * FROM t1 IGNORE INDEX ( mv_idx_datetime , mv_idx_char ) WHERE JSON_CONTAINS( j5 -> '$[*]' , JSON_OBJECT( 'key1' , CAST( '1981-06-09' AS DATE)) );
459 ERROR 42000: Key 'mv_idx_datetime' doesn't exist in table 't1'
460 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;
461 id j1 j2 j3 j4 j5 j6 j7 j8 extra
462 DELETE FROM t1;
463 CHECK TABLE t1;
464 Table Op Msg_type Msg_text
465 test.t1 check status OK
466 DROP TABLE t1;
467 SET sql_mode = default;
468 CREATE TABLE t1(j json, KEY ((CAST(j->'$' AS UNSIGNED ARRAY))));
469 INSERT INTO t1 VALUES ('[]'), ('null');
470 ERROR 22018: Invalid JSON value for CAST for functional index 'functional_index'.
471 SELECT * FROM t1;
472 j
473 DROP TABLE t1;
474 CREATE TABLE t1 (id INT NOT NULL KEY AUTO_INCREMENT, f1 JSON);
475 CREATE INDEX i1 ON t1((CAST(f1->"$[*]" AS DECIMAL(4,2) ARRAY)));
476 CREATE INDEX i2 ON t1((CAST(f1->"$[*]" AS CHAR(10) ARRAY)));
477 START TRANSACTION;
478 INSERT INTO t1(f1) VALUES(CAST('["13.33","13.21"]' AS JSON));
479 INSERT INTO t1(f1) VALUES(NULL);
480 SELECT * FROM t1;
481 id f1
482 1 ["13.33", "13.21"]
483 2 NULL
484 SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
485 id f1
486 1 ["13.33", "13.21"]
487 ROLLBACK;
488 CHECK TABLE t1;
489 Table Op Msg_type Msg_text
490 test.t1 check status OK
491 SELECT * FROM t1;
492 id f1
493 SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
494 id f1
495 INSERT INTO t1(f1) VALUES(CAST('["13.33","13.21"]' AS JSON));
496 START TRANSACTION;
497 UPDATE t1 SET f1 = CAST('["12.34", "56.78"]' AS JSON);
498 SELECT * FROM t1 WHERE 12.34 MEMBER OF (f1->'$[*]');
499 id f1
500 3 ["12.34", "56.78"]
501 ROLLBACK;
502 CHECK TABLE t1;
503 Table Op Msg_type Msg_text
504 test.t1 check status OK
505 SELECT * FROM t1;
506 id f1
507 3 ["13.33", "13.21"]
508 SELECT * FROM t1 WHERE 12.34 MEMBER OF (f1->'$[*]');
509 id f1
510 SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
511 id f1
512 3 ["13.33", "13.21"]
513 START TRANSACTION;
514 UPDATE t1 SET f1 = NULL;
515 ROLLBACK;
516 CHECK TABLE t1;
517 Table Op Msg_type Msg_text
518 test.t1 check status OK
519 SELECT * FROM t1;
520 id f1
521 3 ["13.33", "13.21"]
522 SELECT * FROM t1 WHERE 12.34 MEMBER OF (f1->'$[*]');
523 id f1
524 SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
525 id f1
526 3 ["13.33", "13.21"]
527 UPDATE t1 SET f1 = NULL;
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 id f1
532 3 ["12.34", "56.78"]
533 SELECT * FROM t1 WHERE 56.78 MEMBER OF (f1->'$[*]');
534 id f1
535 3 ["12.34", "56.78"]
536 ROLLBACK;
537 CHECK TABLE t1;
538 Table Op Msg_type Msg_text
539 test.t1 check status OK
540 SELECT * FROM t1;
541 id f1
542 3 NULL
543 UPDATE t1 SET f1 = CAST('["13.33", "13.21"]' AS JSON);
544 START TRANSACTION;
545 DELETE FROM t1;
546 SELECT * FROM t1;
547 id f1
548 SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
549 id f1
550 SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
551 id f1
552 ROLLBACK;
553 SELECT * FROM t1;
554 id f1
555 3 ["13.33", "13.21"]
556 SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
557 id f1
558 3 ["13.33", "13.21"]
559 SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
560 id f1
561 3 ["13.33", "13.21"]
562 CHECK TABLE t1;
563 Table Op Msg_type Msg_text
564 test.t1 check status OK
565 DROP TABLE t1;
566 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) )));
567 INSERT INTO t1( j1 ) VALUES( CAST( '[ 0, -1, 9, -4 ]' AS JSON ) );
568 SET autocommit = 0;
569 INSERT INTO t1 VALUES();
570 ROLLBACK;
571 SELECT j1, j2, j3 FROM t1;
572 j1 j2 j3
573 [0, -1, 9, -4] [9, 8, 0, 1] ["foobar"]
574 CHECK TABLE t1;
575 Table Op Msg_type Msg_text
576 test.t1 check status OK
577 DROP TABLE t1;
578 SET autocommit = default;
579 CREATE TABLE IF NOT EXISTS t1 (
580 i INT NOT NULL PRIMARY KEY,
581 j JSON DEFAULT (CAST('["HelloWorld"]' AS JSON)),
582 KEY mv_idx_binary (( CAST(j->'$[*]' AS BINARY(10) ARRAY) )) );
583 INSERT INTO t1 VALUES(1, CAST('["HelloWorld", "Hello"]' AS JSON));
584 INSERT INTO t1 VALUES(2, CAST('["HelloMySQL", "Hello"]' AS JSON));
585 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
586 i j
587 2 ["HelloMySQL", "Hello"]
588 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
589 i j
590 1 ["HelloWorld", "Hello"]
591 2 ["HelloMySQL", "Hello"]
592 DELETE FROM t1;
593 START TRANSACTION;
594 INSERT INTO t1 VALUES(1, CAST('["HelloMySQL", "Hello"]' AS JSON));
595 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
596 i j
597 1 ["HelloMySQL", "Hello"]
598 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
599 i j
600 1 ["HelloMySQL", "Hello"]
601 ROLLBACK;
602 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
603 i j
604 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
605 i j
606 CHECK TABLE t1;
607 Table Op Msg_type Msg_text
608 test.t1 check status OK
609 DROP TABLE t1;
610 # Test the UPDATE which only updates part of the multi-value field,
611 # to make sure the partial delete + insert works
612 CREATE TABLE IF NOT EXISTS t1 (
613 i INT NOT NULL PRIMARY KEY,
614 j JSON DEFAULT (CAST('["HelloWorld"]' AS JSON)),
615 KEY mv_idx_binary (( CAST(j->'$[*]' AS BINARY(12) ARRAY) )) );
616 INSERT INTO t1 VALUES(1, CAST('["HelloWorld", "Hello"]' AS JSON));
617 INSERT INTO t1 VALUES(2, CAST('["HelloMySQL", "Hello"]' AS JSON));
618 UPDATE t1 SET j = CAST('["Helloworld", "Hello"]' AS JSON);
619 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
620 i j
621 SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
622 i j
623 SELECT * FROM t1 WHERE "Helloworld" MEMBER OF (j->'$[*]');
624 i j
625 1 ["Helloworld", "Hello"]
626 2 ["Helloworld", "Hello"]
627 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
628 i j
629 1 ["Helloworld", "Hello"]
630 2 ["Helloworld", "Hello"]
631 UPDATE t1 SET j = CAST('["Helloworld", "Hello", "World", "MySQL"]' AS JSON);
632 SELECT * FROM t1 WHERE "MySQL" MEMBER OF (j->'$[*]');
633 i j
634 1 ["Helloworld", "Hello", "World", "MySQL"]
635 2 ["Helloworld", "Hello", "World", "MySQL"]
636 SELECT * FROM t1 WHERE "World" MEMBER OF (j->'$[*]');
637 i j
638 1 ["Helloworld", "Hello", "World", "MySQL"]
639 2 ["Helloworld", "Hello", "World", "MySQL"]
640 SELECT * FROM t1 WHERE "Helloworld" MEMBER OF (j->'$[*]');
641 i j
642 1 ["Helloworld", "Hello", "World", "MySQL"]
643 2 ["Helloworld", "Hello", "World", "MySQL"]
644 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
645 i j
646 1 ["Helloworld", "Hello", "World", "MySQL"]
647 2 ["Helloworld", "Hello", "World", "MySQL"]
648 INSERT INTO t1 VALUES(3, CAST('["HelloInnoDB", "Hello", "MySQL", "hello"]' AS JSON));
649 SELECT * FROM t1 WHERE "MySQL" MEMBER OF (j->'$[*]');
650 i j
651 1 ["Helloworld", "Hello", "World", "MySQL"]
652 2 ["Helloworld", "Hello", "World", "MySQL"]
653 3 ["HelloInnoDB", "Hello", "MySQL", "hello"]
654 SELECT * FROM t1 WHERE "World" MEMBER OF (j->'$[*]');
655 i j
656 1 ["Helloworld", "Hello", "World", "MySQL"]
657 2 ["Helloworld", "Hello", "World", "MySQL"]
658 SELECT * FROM t1 WHERE "Helloworld" MEMBER OF (j->'$[*]');
659 i j
660 1 ["Helloworld", "Hello", "World", "MySQL"]
661 2 ["Helloworld", "Hello", "World", "MySQL"]
662 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
663 i j
664 1 ["Helloworld", "Hello", "World", "MySQL"]
665 2 ["Helloworld", "Hello", "World", "MySQL"]
666 3 ["HelloInnoDB", "Hello", "MySQL", "hello"]
667 SELECT * FROM t1 WHERE "HelloInnoDB" MEMBER OF (j->'$[*]');
668 i j
669 3 ["HelloInnoDB", "Hello", "MySQL", "hello"]
670 UPDATE t1 SET j = CAST('["HelloInnoDB", "Hello", "mysql", "HELLO"]' AS JSON) WHERE "hello" MEMBER OF (j->'$[*]');
671 SELECT * FROM t1 WHERE "MySQL" MEMBER OF (j->'$[*]');
672 i j
673 1 ["Helloworld", "Hello", "World", "MySQL"]
674 2 ["Helloworld", "Hello", "World", "MySQL"]
675 SELECT * FROM t1 WHERE "mysql" MEMBER OF (j->'$[*]');
676 i j
677 3 ["HelloInnoDB", "Hello", "mysql", "HELLO"]
678 SELECT * FROM t1 WHERE "World" MEMBER OF (j->'$[*]');
679 i j
680 1 ["Helloworld", "Hello", "World", "MySQL"]
681 2 ["Helloworld", "Hello", "World", "MySQL"]
682 SELECT * FROM t1 WHERE "Helloworld" MEMBER OF (j->'$[*]');
683 i j
684 1 ["Helloworld", "Hello", "World", "MySQL"]
685 2 ["Helloworld", "Hello", "World", "MySQL"]
686 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
687 i j
688 1 ["Helloworld", "Hello", "World", "MySQL"]
689 2 ["Helloworld", "Hello", "World", "MySQL"]
690 3 ["HelloInnoDB", "Hello", "mysql", "HELLO"]
691 SELECT * FROM t1 WHERE "HELLO" MEMBER OF (j->'$[*]');
692 i j
693 3 ["HelloInnoDB", "Hello", "mysql", "HELLO"]
694 SELECT * FROM t1 WHERE "HelloInnoDB" MEMBER OF (j->'$[*]');
695 i j
696 3 ["HelloInnoDB", "Hello", "mysql", "HELLO"]
697 UPDATE t1 SET j = CAST('["HelloInnoDB", "HELLO"]' AS JSON) WHERE "HELLO" MEMBER OF (j->'$[*]');
698 SELECT * FROM t1 WHERE "MySQL" MEMBER OF (j->'$[*]');
699 i j
700 1 ["Helloworld", "Hello", "World", "MySQL"]
701 2 ["Helloworld", "Hello", "World", "MySQL"]
702 SELECT * FROM t1 WHERE "mysql" MEMBER OF (j->'$[*]');
703 i j
704 SELECT * FROM t1 WHERE "World" MEMBER OF (j->'$[*]');
705 i j
706 1 ["Helloworld", "Hello", "World", "MySQL"]
707 2 ["Helloworld", "Hello", "World", "MySQL"]
708 SELECT * FROM t1 WHERE "Helloworld" MEMBER OF (j->'$[*]');
709 i j
710 1 ["Helloworld", "Hello", "World", "MySQL"]
711 2 ["Helloworld", "Hello", "World", "MySQL"]
712 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
713 i j
714 1 ["Helloworld", "Hello", "World", "MySQL"]
715 2 ["Helloworld", "Hello", "World", "MySQL"]
716 SELECT * FROM t1 WHERE "HELLO" MEMBER OF (j->'$[*]');
717 i j
718 3 ["HelloInnoDB", "HELLO"]
719 SELECT * FROM t1 WHERE "HelloInnoDB" MEMBER OF (j->'$[*]');
720 i j
721 3 ["HelloInnoDB", "HELLO"]
722 DELETE FROM t1;
723 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));
724 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);
725 SELECT * FROM t1 WHERE "a" MEMBER OF (j->'$[*]');
726 i j
727 3 ["a", "B", "C", "d", "E", "F", "g", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X"]
728 SELECT * FROM t1 WHERE "A" MEMBER OF (j->'$[*]');
729 i j
730 SELECT * FROM t1 WHERE "d" MEMBER OF (j->'$[*]');
731 i j
732 3 ["a", "B", "C", "d", "E", "F", "g", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X"]
733 SELECT * FROM t1 WHERE "D" MEMBER OF (j->'$[*]');
734 i j
735 SELECT * FROM t1 WHERE "g" MEMBER OF (j->'$[*]');
736 i j
737 3 ["a", "B", "C", "d", "E", "F", "g", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X"]
738 SELECT * FROM t1 WHERE "H" MEMBER OF (j->'$[*]');
739 i j
740 3 ["a", "B", "C", "d", "E", "F", "g", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X"]
741 CHECK TABLE t1;
742 Table Op Msg_type Msg_text
743 test.t1 check status OK
744 TRUNCATE TABLE t1;
745 SELECT * FROM t1;
746 i j
747 INSERT INTO t1 VALUES(1, CAST('["HelloWorld", "Hello"]' AS JSON));
748 INSERT INTO t1 VALUES(2, CAST('["HelloMySQL", "Hello"]' AS JSON));
749 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
750 i j
751 2 ["HelloMySQL", "Hello"]
752 SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
753 i j
754 1 ["HelloWorld", "Hello"]
755 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
756 i j
757 1 ["HelloWorld", "Hello"]
758 2 ["HelloMySQL", "Hello"]
759 START TRANSACTION;
760 UPDATE t1 SET j = CAST('["Helloworld", "Hello"]' AS JSON);
761 ROLLBACK;
762 CHECK TABLE t1;
763 Table Op Msg_type Msg_text
764 test.t1 check status OK
765 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
766 i j
767 2 ["HelloMySQL", "Hello"]
768 SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
769 i j
770 1 ["HelloWorld", "Hello"]
771 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
772 i j
773 1 ["HelloWorld", "Hello"]
774 2 ["HelloMySQL", "Hello"]
775 SELECT * FROM t1 WHERE "Helloworld" MEMBER OF (j->'$[*]');
776 i j
777 START TRANSACTION;
778 DELETE FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
779 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
780 i j
781 2 ["HelloMySQL", "Hello"]
782 UPDATE t1 SET j = CAST('["HelloMySQL", "Hello", "hello", "mysql"]' AS JSON);
783 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
784 i j
785 2 ["HelloMySQL", "Hello", "hello", "mysql"]
786 SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
787 i j
788 2 ["HelloMySQL", "Hello", "hello", "mysql"]
789 SELECT * FROM t1 WHERE "mysql" MEMBER OF (j->'$[*]');
790 i j
791 2 ["HelloMySQL", "Hello", "hello", "mysql"]
792 ROLLBACK;
793 CHECK TABLE t1;
794 Table Op Msg_type Msg_text
795 test.t1 check status OK
796 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
797 i j
798 2 ["HelloMySQL", "Hello"]
799 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
800 i j
801 1 ["HelloWorld", "Hello"]
802 2 ["HelloMySQL", "Hello"]
803 SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
804 i j
805 SELECT * FROM t1 WHERE "mysql" MEMBER OF (j->'$[*]');
806 i j
807 DELETE FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
808 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
809 i j
810 2 ["HelloMySQL", "Hello"]
811 UPDATE t1 SET j = CAST('["HelloMySQL", "Hello", "hello", "mysql", "MySQL"]' AS JSON);
812 START TRANSACTION;
813 UPDATE t1 SET j = CAST('["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]' AS JSON);
814 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
815 i j
816 2 ["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
817 SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
818 i j
819 2 ["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
820 SELECT * FROM t1 WHERE "mysql" MEMBER OF (j->'$[*]');
821 i j
822 SELECT * FROM t1 WHERE "mySQL" MEMBER OF (j->'$[*]');
823 i j
824 2 ["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
825 SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
826 i j
827 2 ["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
828 UPDATE t1 SET j = CAST('["HelloMySQL", "Hello"]' AS JSON);
829 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
830 i j
831 2 ["HelloMySQL", "Hello"]
832 SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
833 i j
834 SELECT * FROM t1 WHERE "mysql" MEMBER OF (j->'$[*]');
835 i j
836 SELECT * FROM t1 WHERE "mySQL" MEMBER OF (j->'$[*]');
837 i j
838 SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
839 i j
840 ROLLBACK;
841 CHECK TABLE t1;
842 Table Op Msg_type Msg_text
843 test.t1 check status OK
844 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
845 i j
846 2 ["HelloMySQL", "Hello", "hello", "mysql", "MySQL"]
847 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
848 i j
849 2 ["HelloMySQL", "Hello", "hello", "mysql", "MySQL"]
850 SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
851 i j
852 2 ["HelloMySQL", "Hello", "hello", "mysql", "MySQL"]
853 SELECT * FROM t1 WHERE "mysql" MEMBER OF (j->'$[*]');
854 i j
855 2 ["HelloMySQL", "Hello", "hello", "mysql", "MySQL"]
856 TRUNCATE TABLE t1;
857 CHECK TABLE t1;
858 Table Op Msg_type Msg_text
859 test.t1 check status OK
860 SELECT * FROM t1;
861 i j
862 INSERT INTO t1 VALUES(1, CAST('["HelloWorld", "Hello"]' AS JSON));
863 INSERT INTO t1 VALUES(2, CAST('["HelloMySQL", "Hello"]' AS JSON));
864 UPDATE t1 SET i = 3 WHERE i = 2;
865 UPDATE t1 SET i = 4, j = CAST('["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]' AS JSON) WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
866 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
867 i j
868 4 ["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
869 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
870 i j
871 1 ["HelloWorld", "Hello"]
872 4 ["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
873 SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
874 i j
875 4 ["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
876 SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
877 i j
878 4 ["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
879 # Force a multi-table UPDATE, to test:
880 # Bug #98691: Updating a multi-table view has problem if one sub-table is constant
881 CREATE VIEW vv AS SELECT t1.* FROM t1 STRAIGHT_JOIN (SELECT 1) AS dt;
882 EXPLAIN UPDATE vv SET i = 3 WHERE i = 4;
883 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
884 1 UPDATE t1 NULL const PRIMARY PRIMARY 4 const 1 100.00 NULL
885 1 PRIMARY <derived3> NULL system NULL NULL NULL NULL 1 100.00 NULL
886 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
887 Warnings:
888 Note 1003 update (`test`.`t1` straight_join (/* select#3 */ select 1 AS `1`) `dt`) set `test`.`t1`.`i` = 3 where true
889 UPDATE vv SET i = 3 WHERE i = 4;
890 UPDATE vv SET i = 4 WHERE i = 3;
891 DROP VIEW vv;
892 START TRANSACTION;
893 UPDATE t1 SET i = 5 WHERE i = 4;
894 ROLLBACK;
895 CHECK TABLE t1;
896 Table Op Msg_type Msg_text
897 test.t1 check status OK
898 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
899 i j
900 4 ["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
901 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
902 i j
903 1 ["HelloWorld", "Hello"]
904 4 ["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
905 SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
906 i j
907 4 ["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
908 SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
909 i j
910 4 ["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
911 START TRANSACTION;
912 UPDATE t1 SET i = 6, j = CAST('["HelloMySQL", "mySQL", "MYSQL"]' AS JSON) WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
913 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
914 i j
915 6 ["HelloMySQL", "mySQL", "MYSQL"]
916 SELECT * FROM t1 WHERE "mySQL" MEMBER OF (j->'$[*]');
917 i j
918 6 ["HelloMySQL", "mySQL", "MYSQL"]
919 SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
920 i j
921 6 ["HelloMySQL", "mySQL", "MYSQL"]
922 ROLLBACK;
923 CHECK TABLE t1;
924 Table Op Msg_type Msg_text
925 test.t1 check status OK
926 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
927 i j
928 4 ["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
929 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
930 i j
931 1 ["HelloWorld", "Hello"]
932 4 ["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
933 SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
934 i j
935 4 ["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
936 SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
937 i j
938 4 ["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
939 DROP TABLE t1;
940 CREATE TABLE t1 (
941 id INT NOT NULL PRIMARY KEY,
942 a INT,
943 b INT,
944 j JSON DEFAULT (CAST('["HelloWorld"]' AS JSON)),
945 KEY mv_idx_binary(a, (( CAST(j->'$[*]' AS BINARY(12) ARRAY) )), b));
946 INSERT INTO t1 VALUES(1, 1, 1, CAST('["HelloWorld", "Hello"]' AS JSON));
947 UPDATE t1 SET b = 2;
948 SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
949 id a b j
950 1 1 2 ["HelloWorld", "Hello"]
951 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
952 id a b j
953 1 1 2 ["HelloWorld", "Hello"]
954 UPDATE t1 SET a = 2;
955 SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
956 id a b j
957 1 2 2 ["HelloWorld", "Hello"]
958 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
959 id a b j
960 1 2 2 ["HelloWorld", "Hello"]
961 UPDATE t1 SET b = 3, j = CAST('["HelloMySQL", "mySQL", "MYSQL"]' AS JSON) WHERE "HelloWorld" MEMBER OF (j->'$[*]');
962 SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
963 id a b j
964 SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
965 id a b j
966 1 2 3 ["HelloMySQL", "mySQL", "MYSQL"]
967 UPDATE t1 SET a = 3, j = CAST('["HelloMySQL", "mySQL", "MYSQL", "hello"]' AS JSON) WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
968 SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
969 id a b j
970 SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
971 id a b j
972 1 3 3 ["HelloMySQL", "mySQL", "MYSQL", "hello"]
973 SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
974 id a b j
975 1 3 3 ["HelloMySQL", "mySQL", "MYSQL", "hello"]
976 CHECK TABLE t1;
977 Table Op Msg_type Msg_text
978 test.t1 check status OK
979 TRUNCATE TABLE t1;
980 INSERT INTO t1 VALUES(1, 1, 1, CAST('["HelloWorld", "Hello"]' AS JSON));
981 START TRANSACTION;
982 UPDATE t1 SET b = 2;
983 SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
984 id a b j
985 1 1 2 ["HelloWorld", "Hello"]
986 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
987 id a b j
988 1 1 2 ["HelloWorld", "Hello"]
989 UPDATE t1 SET a = 2;
990 SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
991 id a b j
992 1 2 2 ["HelloWorld", "Hello"]
993 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
994 id a b j
995 1 2 2 ["HelloWorld", "Hello"]
996 ROLLBACK;
997 CHECK TABLE t1;
998 Table Op Msg_type Msg_text
999 test.t1 check status OK
1000 SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
1001 id a b j
1002 1 1 1 ["HelloWorld", "Hello"]
1003 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
1004 id a b j
1005 1 1 1 ["HelloWorld", "Hello"]
1006 START TRANSACTION;
1007 UPDATE t1 SET b = 3, j = CAST('["HelloMySQL", "mySQL", "MYSQL"]' AS JSON) WHERE "HelloWorld" MEMBER OF (j->'$[*]');
1008 SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
1009 id a b j
1010 1 1 3 ["HelloMySQL", "mySQL", "MYSQL"]
1011 UPDATE t1 SET a = 3, j = CAST('["HelloMySQL", "mySQL", "MYSQL", "hello"]' AS JSON) WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
1012 SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
1013 id a b j
1014 SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
1015 id a b j
1016 1 3 3 ["HelloMySQL", "mySQL", "MYSQL", "hello"]
1017 SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
1018 id a b j
1019 1 3 3 ["HelloMySQL", "mySQL", "MYSQL", "hello"]
1020 ROLLBACK;
1021 CHECK TABLE t1;
1022 Table Op Msg_type Msg_text
1023 test.t1 check status OK
1024 SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
1025 id a b j
1026 SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
1027 id a b j
1028 SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
1029 id a b j
1030 1 1 1 ["HelloWorld", "Hello"]
1031 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
1032 id a b j
1033 1 1 1 ["HelloWorld", "Hello"]
1034 DROP TABLE t1;
1035 CREATE TABLE t1 (
1036 id int NOT NULL,
1037 j JSON DEFAULT NULL,
1038 PRIMARY KEY (id),
1039 KEY j_key123 ((CAST(json_extract(j,_utf8mb4'$') AS CHAR(64) ARRAY)))
1040 );
1041 INSERT INTO t1 values (1000, CONCAT('[""]'));
1042 DROP TABLE t1;
1043 CREATE TABLE t1 (i INT, j JSON, KEY mv_idx_int ((CAST(j->'$[*]' AS SIGNED ARRAY))) ) PARTITION BY KEY(i) PARTITIONS 2;
1044 INSERT INTO t1 VALUES(10,'[4, 5, 6]'), (1, '[1, 2, 3]'), (5, '[3, 4, 5]'), (8, '[4, 14, 24]');
1045 SELECT * FROM t1 PARTITION (p0) WHERE 4 MEMBER OF (j->'$[*]');
1046 i j
1047 5 [3, 4, 5]
1048 SELECT * FROM t1 PARTITION (p1) WHERE 4 MEMBER OF (j->'$[*]');
1049 i j
1050 10 [4, 5, 6]
1051 8 [4, 14, 24]
1052 SELECT * FROM t1 WHERE 4 MEMBER OF (j->'$[*]');
1053 i j
1054 5 [3, 4, 5]
1055 10 [4, 5, 6]
1056 8 [4, 14, 24]
1057 SELECT * FROM t1 PARTITION (p0) WHERE 1 MEMBER OF (j->'$[*]');
1058 i j
1059 1 [1, 2, 3]
1060 SELECT * FROM t1 PARTITION (p1) WHERE 1 MEMBER OF (j->'$[*]');
1061 i j
1062 SELECT * FROM t1 WHERE 1 MEMBER OF (j->'$[*]');
1063 i j
1064 1 [1, 2, 3]
1065 DROP TABLE t1;
1066 #
1067 # Bug#33334928: SELECT COUNT(*) using multi-valued index
1068 # reports wrong number of rows.
1069 #
1070 CREATE TABLE t (
1071 id int NOT NULL,
1072 doc_id int DEFAULT NULL,
1073 doc json DEFAULT NULL,
1074 PRIMARY KEY (id),
1075 KEY mv_id (id,(cast(json_extract(doc,'$.val') as unsigned array))),
1076 KEY mv_doc_id (doc_id,(cast(json_extract(doc,'$.val') as unsigned array)))
1077 );
1078 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]}');
1079 ANALYZE TABLE t;
1080 Table Op Msg_type Msg_text
1081 test.t analyze status OK
1082 EXPLAIN SELECT COUNT(*) FROM t WHERE id = '1';
1083 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1084 1 SIMPLE t NULL const PRIMARY,mv_id PRIMARY 4 const 1 100.00 Using index
1085 Warnings:
1086 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t` where true
1087 SELECT COUNT(*) FROM t WHERE id = '1';
1088 COUNT(*)
1089 1
1090 EXPLAIN SELECT COUNT(*) FROM t FORCE INDEX (mv_id) WHERE id = '1';
1091 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1092 1 SIMPLE t NULL ref mv_id mv_id 4 const 5 100.00 NULL
1093 Warnings:
1094 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t` FORCE INDEX (`mv_id`) where (`test`.`t`.`id` = 1)
1095 SELECT COUNT(*) FROM t FORCE INDEX (mv_id) WHERE id = '1';
1096 COUNT(*)
1097 1
1098 EXPLAIN SELECT COUNT(*) FROM t FORCE INDEX (mv_doc_id) WHERE doc_id = '2';
1099 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1100 1 SIMPLE t NULL ref mv_doc_id mv_doc_id 5 const 11 100.00 NULL
1101 Warnings:
1102 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t` FORCE INDEX (`mv_doc_id`) where (`test`.`t`.`doc_id` = 2)
1103 SELECT COUNT(*) FROM t FORCE INDEX (mv_doc_id) WHERE doc_id = '2';
1104 COUNT(*)
1105 3
1106 EXPLAIN SELECT COUNT(*) FROM t FORCE INDEX (mv_doc_id) WHERE doc_id = '2' OR doc_id IS NULL;
1107 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1108 1 SIMPLE t NULL ref_or_null mv_doc_id mv_doc_id 5 const 14 100.00 Using where
1109 Warnings:
1110 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t` FORCE INDEX (`mv_doc_id`) where ((`test`.`t`.`doc_id` = 2) or (`test`.`t`.`doc_id` is null))
1111 SELECT COUNT(*) FROM t FORCE INDEX (mv_doc_id) WHERE doc_id = '2' OR doc_id IS NULL;
1112 COUNT(*)
1113 4
1114 EXPLAIN SELECT * FROM t WHERE id = '1';
1115 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1116 1 SIMPLE t NULL const PRIMARY,mv_id PRIMARY 4 const 1 100.00 NULL
1117 Warnings:
1118 Note 1003 /* select#1 */ select '1' AS `id`,'2' AS `doc_id`,'{"val": [1, 2, 3, 4, 5]}' AS `doc` from `test`.`t` where true
1119 SELECT * FROM t WHERE id = '1';
1120 id doc_id doc
1121 1 2 {"val": [1, 2, 3, 4, 5]}
1122 EXPLAIN SELECT * FROM t FORCE INDEX (mv_id) WHERE id = '1';
1123 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1124 1 SIMPLE t NULL ref mv_id mv_id 4 const 1 100.00 NULL
1125 Warnings:
1126 Note 1003 /* select#1 */ select `test`.`t`.`id` AS `id`,`test`.`t`.`doc_id` AS `doc_id`,`test`.`t`.`doc` AS `doc` from `test`.`t` FORCE INDEX (`mv_id`) where (`test`.`t`.`id` = 1)
1127 SELECT * FROM t FORCE INDEX (mv_id) WHERE id = '1';
1128 id doc_id doc
1129 1 2 {"val": [1, 2, 3, 4, 5]}
1130 EXPLAIN SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id = '2';
1131 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1132 1 SIMPLE t NULL ref mv_doc_id mv_doc_id 5 const 1 100.00 NULL
1133 Warnings:
1134 Note 1003 /* select#1 */ select `test`.`t`.`id` AS `id`,`test`.`t`.`doc_id` AS `doc_id`,`test`.`t`.`doc` AS `doc` from `test`.`t` FORCE INDEX (`mv_doc_id`) where (`test`.`t`.`doc_id` = 2)
1135 SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id = '2';
1136 id doc_id doc
1137 1 2 {"val": [1, 2, 3, 4, 5]}
1138 3 2 {"val": [2, 3, 4, 5]}
1139 5 2 {"val": [4, 5]}
1140 EXPLAIN SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id = '2' OR doc_id IS NULL;
1141 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1142 1 SIMPLE t NULL ref_or_null mv_doc_id mv_doc_id 5 const 2 100.00 Using where
1143 Warnings:
1144 Note 1003 /* select#1 */ select `test`.`t`.`id` AS `id`,`test`.`t`.`doc_id` AS `doc_id`,`test`.`t`.`doc` AS `doc` from `test`.`t` FORCE INDEX (`mv_doc_id`) where ((`test`.`t`.`doc_id` = 2) or (`test`.`t`.`doc_id` is null))
1145 SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id = '2' OR doc_id IS NULL;
1146 id doc_id doc
1147 1 2 {"val": [1, 2, 3, 4, 5]}
1148 3 2 {"val": [2, 3, 4, 5]}
1149 4 NULL {"val": [3, 4, 5]}
1150 5 2 {"val": [4, 5]}
1151 EXPLAIN SELECT * FROM t WHERE doc_id is NOT NULL;
1152 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1153 1 SIMPLE t NULL ALL mv_doc_id NULL NULL NULL 5 100.00 Using where
1154 Warnings:
1155 Note 1003 /* select#1 */ select `test`.`t`.`id` AS `id`,`test`.`t`.`doc_id` AS `doc_id`,`test`.`t`.`doc` AS `doc` from `test`.`t` where (`test`.`t`.`doc_id` is not null)
1156 SELECT * FROM t WHERE doc_id is NOT NULL;
1157 id doc_id doc
1158 1 2 {"val": [1, 2, 3, 4, 5]}
1159 2 1 {"val": [2, 3, 4, 5]}
1160 3 2 {"val": [2, 3, 4, 5]}
1161 5 2 {"val": [4, 5]}
1162 EXPLAIN SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id is NOT NULL;
1163 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1164 1 SIMPLE t NULL range mv_doc_id mv_doc_id 5 NULL 15 100.00 Using where
1165 Warnings:
1166 Note 1003 /* select#1 */ select `test`.`t`.`id` AS `id`,`test`.`t`.`doc_id` AS `doc_id`,`test`.`t`.`doc` AS `doc` from `test`.`t` FORCE INDEX (`mv_doc_id`) where (`test`.`t`.`doc_id` is not null)
1167 SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id is NOT NULL;
1168 id doc_id doc
1169 1 2 {"val": [1, 2, 3, 4, 5]}
1170 2 1 {"val": [2, 3, 4, 5]}
1171 3 2 {"val": [2, 3, 4, 5]}
1172 5 2 {"val": [4, 5]}
1173 EXPLAIN SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id <> 0;
1174 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1175 1 SIMPLE t NULL range mv_doc_id mv_doc_id 5 NULL 2 100.00 Using where
1176 Warnings:
1177 Note 1003 /* select#1 */ select `test`.`t`.`id` AS `id`,`test`.`t`.`doc_id` AS `doc_id`,`test`.`t`.`doc` AS `doc` from `test`.`t` FORCE INDEX (`mv_doc_id`) where (`test`.`t`.`doc_id` <> 0)
1178 SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id <> 0;
1179 id doc_id doc
1180 1 2 {"val": [1, 2, 3, 4, 5]}
1181 2 1 {"val": [2, 3, 4, 5]}
1182 3 2 {"val": [2, 3, 4, 5]}
1183 5 2 {"val": [4, 5]}
1184 EXPLAIN SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id > 0;
1185 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1186 1 SIMPLE t NULL range mv_doc_id mv_doc_id 5 NULL 1 100.00 Using where
1187 Warnings:
1188 Note 1003 /* select#1 */ select `test`.`t`.`id` AS `id`,`test`.`t`.`doc_id` AS `doc_id`,`test`.`t`.`doc` AS `doc` from `test`.`t` FORCE INDEX (`mv_doc_id`) where (`test`.`t`.`doc_id` > 0)
1189 SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id > 0;
1190 id doc_id doc
1191 1 2 {"val": [1, 2, 3, 4, 5]}
1192 2 1 {"val": [2, 3, 4, 5]}
1193 3 2 {"val": [2, 3, 4, 5]}
1194 5 2 {"val": [4, 5]}
1195 EXPLAIN SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id < 2 OR doc_id IS NULL;
1196 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1197 1 SIMPLE t NULL range mv_doc_id mv_doc_id 5 NULL 1 100.00 Using where
1198 Warnings:
1199 Note 1003 /* select#1 */ select `test`.`t`.`id` AS `id`,`test`.`t`.`doc_id` AS `doc_id`,`test`.`t`.`doc` AS `doc` from `test`.`t` FORCE INDEX (`mv_doc_id`) where ((`test`.`t`.`doc_id` < 2) or (`test`.`t`.`doc_id` is null))
1200 SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id < 2 OR doc_id IS NULL;
1201 id doc_id doc
1202 2 1 {"val": [2, 3, 4, 5]}
1203 4 NULL {"val": [3, 4, 5]}
1204 EXPLAIN SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id >= 1 AND doc_id < 2;
1205 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1206 1 SIMPLE t NULL range mv_doc_id mv_doc_id 5 NULL 1 100.00 Using where
1207 Warnings:
1208 Note 1003 /* select#1 */ select `test`.`t`.`id` AS `id`,`test`.`t`.`doc_id` AS `doc_id`,`test`.`t`.`doc` AS `doc` from `test`.`t` FORCE INDEX (`mv_doc_id`) where ((`test`.`t`.`doc_id` >= 1) and (`test`.`t`.`doc_id` < 2))
1209 SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id >= 1 AND doc_id < 2;
1210 id doc_id doc
1211 2 1 {"val": [2, 3, 4, 5]}
1212 DROP TABLE t;
1213 #
1214 # Bug #32725063 CRASH WITH MULTI-VALUED INDEXES IN
1215 # COMBINATION WITH AT LEAST GENERATED FK
1216 CREATE TABLE t1 (id BIGINT NOT NULL AUTO_INCREMENT, data JSON,PRIMARY KEY (id));
1217 ALTER TABLE t1 ADD INDEX ix_t1_idArray ((CAST(data->'$.idArray' AS UNSIGNED ARRAY))),
1218 ADD COLUMN parent_id BIGINT GENERATED ALWAYS AS (data->'$.parentID') STORED,
1219 ADD INDEX ix_t1_parent (parent_id),
1220 ADD CONSTRAINT fk_t1_parent FOREIGN KEY (parent_id) REFERENCES t1 (id) ON DELETE CASCADE;
1221 INSERT INTO t1 (data) VALUES ('{\"idArray\":[1]}');
1222 INSERT INTO t1 (data) VALUES ('{\"parentID\":1,\"idArray\":[1]}');
1223 DELETE FROM t1 WHERE id = 1;
1224 DROP TABLE t1;
1225 CREATE TABLE t1 (id BIGINT NOT NULL , PRIMARY KEY (id));
1226 CREATE TABLE t2(data JSON,child_id bigint GENERATED ALWAYS AS (data->'$.parentID') STORED,key(child_id));
1227 ALTER TABLE t2 ADD INDEX ix_t1_idArray ((CAST(data->'$.idArray' AS UNSIGNED ARRAY)));
1228 ALTER TABLE t2 ADD CONSTRAINT fk_t1_parent FOREIGN KEY (child_id) REFERENCES t1 (id) ON DELETE CASCADE;
1229 INSERT INTO t1 VALUES (1);
1230 INSERT INTO t2 (data) VALUES ('{\"parentID\":1,\"idArray\":[1]}');
1231 DELETE FROM t1 WHERE id = 1;
1232 DROP TABLE t2,t1;