Debian Codegraph @

mysql-test/suite/json/r/array_index.result

raw
1 CREATE TABLE t1 (f1 json);
2 create index i1 on t1((cast(f1 as unsigned array)));
3 show create table t1;
4 Table Create Table
5 t1 CREATE TABLE `t1` (
6 `f1` json DEFAULT NULL,
7 KEY `i1` ((cast(`f1` as unsigned array)))
8 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
9 insert into t1(f1) values
10 (cast(1 as json)),
11 (cast('[2,3,4]' as json)),
12 (cast('[3,4,3,4,2,2,2]' as json)),
13 (cast('[5,5,6,6]' as json));
14 select * from t1;
15 f1
16 1
17 [2, 3, 4]
18 [3, 4, 3, 4, 2, 2, 2]
19 [5, 5, 6, 6]
20 alter table t1 add index mv_idx2((cast(f1 as signed array)));
21 show create table t1;
22 Table Create Table
23 t1 CREATE TABLE `t1` (
24 `f1` json DEFAULT NULL,
25 KEY `i1` ((cast(`f1` as unsigned array))),
26 KEY `mv_idx2` ((cast(`f1` as signed array)))
27 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
28 alter table t1 drop index i1;
29 show create table t1;
30 Table Create Table
31 t1 CREATE TABLE `t1` (
32 `f1` json DEFAULT NULL,
33 KEY `mv_idx2` ((cast(`f1` as signed array)))
34 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
35 drop index mv_idx2 on t1;
36 show create table t1;
37 Table Create Table
38 t1 CREATE TABLE `t1` (
39 `f1` json DEFAULT NULL
40 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
41 drop table t1;
42 CREATE TABLE t1 (f1 json);
43 insert into t1(f1) values (cast("null" as json));
44 create index i1 on t1((cast(f1 as unsigned array)));
45 ERROR 22018: Invalid JSON value for CAST for functional index 'i1'.
46 delete from t1;
47 insert into t1(f1) values ('1111111111111111111111');
48 create index i1 on t1((cast(f1 as unsigned array)));
49 ERROR 22003: Out of range JSON value for CAST for functional index 'i1'.
50 delete from t1;
51 create index i1 on t1((cast(f1 as unsigned array)));
52 insert into t1(f1) values (cast("null" as json));
53 ERROR 22018: Invalid JSON value for CAST for functional index 'i1'.
54 insert into t1(f1) values ('1111111111111111111111');
55 ERROR 22003: Out of range JSON value for CAST for functional index 'i1'.
56 drop table t1;
57 create table t1 (f1 json, key mvi((cast(f1 as unsigned array))));
58 show create table t1;
59 Table Create Table
60 t1 CREATE TABLE `t1` (
61 `f1` json DEFAULT NULL,
62 KEY `mvi` ((cast(`f1` as unsigned array)))
63 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
64 drop table t1;
65 select cast('[1,2,3]' as unsigned array);
66 ERROR 42000: This version of MySQL doesn't yet support 'Use of CAST( .. AS .. ARRAY) outside of functional index in CREATE(non-SELECT)/ALTER TABLE or in general expressions'
67 create table t1 as select cast('[1,2,3]' as unsigned array);
68 ERROR 42000: This version of MySQL doesn't yet support 'Use of CAST( .. AS .. ARRAY) outside of functional index in CREATE(non-SELECT)/ALTER TABLE or in general expressions'
69 create table t(j json, gc json as (cast(j->'$[*]' as unsigned array)));
70 ERROR 42000: This version of MySQL doesn't yet support 'Use of CAST( .. AS .. ARRAY) outside of functional index in CREATE(non-SELECT)/ALTER TABLE or in general expressions'
71 create table t(j json, gc json as
72 ((concat(cast(j->'$[*]' as unsigned array),"x"))));
73 ERROR 42000: This version of MySQL doesn't yet support 'Use of CAST( .. AS .. ARRAY) outside of functional index in CREATE(non-SELECT)/ALTER TABLE or in general expressions'
74 create table t1(j json, key i1((cast(j->"$" as json array))));
75 ERROR 42000: This version of MySQL doesn't yet support 'CAST-ing data to array of JSON'
76 create table t1(j json, key i1((cast(j->"$" as char array))));
77 ERROR 42000: This version of MySQL doesn't yet support 'CAST-ing data to array of char/binary BLOBs'
78 create table t1(j json, key i1((cast(j->"$" as binary array))));
79 ERROR 42000: This version of MySQL doesn't yet support 'CAST-ing data to array of char/binary BLOBs'
80 create table t1(j json, key i1((cast(j->"$" as float array))));
81 ERROR 42000: This version of MySQL doesn't yet support 'CAST-ing data to array of FLOAT'
82 create table t1(j json, key i1((cast(j->"$" as double array))));
83 ERROR 42000: This version of MySQL doesn't yet support 'CAST-ing data to array of DOUBLE'
84 CREATE TABLE t1 (f1 json, key mvi((cast(f1 as unsigned array)))) engine=myisam;
85 ERROR 42000: The storage engine for the table doesn't support multi-valued indexes
86 CREATE TABLE t1 (f1 json, key mvi((cast(f1 as unsigned array)) asc));
87 ERROR HY000: Incorrect usage of multi-valued index and explicit index order
88 CREATE TABLE t1 (f1 json, key mvi((cast(cast(f1 as unsigned array) as unsigned array))));
89 ERROR 42000: This version of MySQL doesn't yet support 'Use of CAST( .. AS .. ARRAY) outside of functional index in CREATE(non-SELECT)/ALTER TABLE or in general expressions'
90 CREATE TABLE t(x INT, KEY k ((1 AND CAST(JSON_ARRAY(x) AS UNSIGNED ARRAY))));
91 ERROR 42000: This version of MySQL doesn't yet support 'Use of CAST( .. AS .. ARRAY) outside of functional index in CREATE(non-SELECT)/ALTER TABLE or in general expressions'
92 create table col(doc json,
93 key i1((cast(doc->'$.text' as char(10) array)),
94 (cast(doc->'$.integer' as signed array))));
95 ERROR 42000: This version of MySQL doesn't yet support 'more than one multi-valued key part per index'
96 create table t1(j json, key i1((cast(j as char(10) array))));
97 insert into t1 values('{"asd":1}');
98 ERROR 42000: This version of MySQL doesn't yet support 'CAST-ing JSON OBJECT type to array'
99 insert into t1 values('true');
100 ERROR 42000: This version of MySQL doesn't yet support 'CAST-ing JSON BOOLEAN type to array'
101 drop table t1;
102 create table t1 (j json, key mv_idx_char ((cast(j as char(16384) array))));
103 ERROR 42000: This version of MySQL doesn't yet support 'CAST-ing data to array of char/binary BLOBs'
104 create table t1 (j json, key mv_idx_char ((cast(j as char(512) array))));
105 show create table t1;
106 Table Create Table
107 t1 CREATE TABLE `t1` (
108 `j` json DEFAULT NULL,
109 KEY `mv_idx_char` ((cast(`j` as char(512) array)))
110 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
111 drop table t1;
112 create function f() returns int deterministic return cast(json_array(1,2) as
113 unsigned array);
114 ERROR HY000: Incorrect usage of CAST( .. AS .. ARRAY) and stored routines
115 create table t(x int, key k ((cast(json_array(x) as decimal(10,3) array))));
116 drop table t;
117 create table t(j json, key k ((cast(j as unsigned array))));
118 insert into t values ('[]');
119 drop table t;
120 select 1 member of ('[1,2,3]');
121 1 member of ('[1,2,3]')
122 1
123 select 1 member of ('1');
124 1 member of ('1')
125 1
126 select 1 member ('1');
127 1 member ('1')
128 1
129 select cast(1 as json) member of(json_array(1,2,3));
130 cast(1 as json) member of(json_array(1,2,3))
131 1
132 select cast(4 as json) member of(json_array(1,2,3));
133 cast(4 as json) member of(json_array(1,2,3))
134 0
135 select cast(1 as json) member of(json_array(2,NULL,1));
136 cast(1 as json) member of(json_array(2,NULL,1))
137 1
138 select cast(4 as json) member of(json_array(1,2,NULL));
139 cast(4 as json) member of(json_array(1,2,NULL))
140 0
141 set @A_VAR=json_array(1,2,3);
142 select cast(1 as json) member of(cast(@A_VAR as json));
143 cast(1 as json) member of(cast(@A_VAR as json))
144 1
145 select cast(4 as json) member of(cast(@A_VAR as json));
146 cast(4 as json) member of(cast(@A_VAR as json))
147 0
148 set @A_VAR=json_array(2,cast('{"A":1, "B":2}' as json),3);
149 select cast('{"A":1, "B":2}' as json) member of(cast(@A_VAR as json));
150 cast('{"A":1, "B":2}' as json) member of(cast(@A_VAR as json))
151 1
152 select cast('{"B":2, "A":1}' as json) member of(cast(@A_VAR as json));
153 cast('{"B":2, "A":1}' as json) member of(cast(@A_VAR as json))
154 1
155 select cast('{"B":2, "A":2}' as json) member of(cast(@A_VAR as json));
156 cast('{"B":2, "A":2}' as json) member of(cast(@A_VAR as json))
157 0
158 set @A_VAR=json_array(2,cast('{"A":1, "B":2}' as json), NULL);
159 select cast('{"B":2, "A":1}' as json) member of(cast(@A_VAR as json));
160 cast('{"B":2, "A":1}' as json) member of(cast(@A_VAR as json))
161 1
162 create table t1 (f1 json, key i1 ((cast(f1->"$.a" as unsigned array))));
163 insert into t1(f1) values (cast('{}' as json));
164 drop table t1;
165 create table t (j json default (cast(json_object() as unsigned array)));
166 ERROR 42000: This version of MySQL doesn't yet support 'Use of CAST( .. AS .. ARRAY) outside of functional index in CREATE(non-SELECT)/ALTER TABLE or in general expressions'
167 create table t(f char(1));
168 insert into t values
169 ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('A'),('B'),('C'),('D'),('E'),('F');
170 create table t1(doc json,key i1((cast(doc->'$' as unsigned array))));
171 set session group_concat_max_len = 1000000;
172 Check limit on max total mv keys length
173 Should succeed
174 insert into t1 select concat('[',group_concat(val),']') from (
175 select conv(concat(t1.f, t2.f, t3.f), 16, 10) val
176 from t t1, t t2, t t3
177 having val < 1605) tt;
178 insert into t1 select concat('[',group_concat(val),']') from (
179 select conv(concat(t1.f, t2.f, t3.f), 16, 10) val
180 from t t1, t t2, t t3
181 having val < 1606) tt;
182 ERROR HY000: Exceeded max number of values per record for multi-valued index 'i1' by 1 value(s).
183 create table t2(doc json,key i1((cast(doc->'$' as char(10) array))));
184 insert into t2 select concat('[',group_concat(val),']') from (
185 select conv(concat(t1.f, t2.f, t3.f), 16, 10) dec_val,
186 concat('"',
187 repeat(t1.f,3),
188 repeat(t2.f,3),
189 repeat(t3.f,2),
190 '"') val
191 from t t1, t t2, t t3
192 having dec_val < 1600) tt;
193 ERROR HY000: Exceeded max total length of values per record for multi-valued index 'i1' by 7446 bytes.
194 insert into t2 select concat('[',group_concat(val),']') from (
195 select conv(concat(t1.f, t2.f, t3.f, t4.f), 16, 10) dec_val,
196 concat('"',
197 repeat(t1.f,3),
198 repeat(t2.f,3),
199 repeat(t3.f,2),
200 repeat(t4.f,2),
201 '"') val
202 from t t1, t t2, t t3, t t4) tt;
203 ERROR HY000: Exceeded max number of values per record for multi-valued index 'i1' by 60184 value(s).
204 analyze table t1;
205 Table Op Msg_type Msg_text
206 test.t1 analyze status OK
207 explain select * from t1 ignore key(i1) where json_contains(doc->"$","[1,2,3]");
208 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
209 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
210 Warnings:
211 Note 1003 /* select#1 */ select `test`.`t1`.`doc` AS `doc` from `test`.`t1` IGNORE INDEX (`i1`) where json_contains(json_extract(`test`.`t1`.`doc`,'$'),<cache>('[1,2,3]'))
212 explain select * from t1 where json_contains(doc->"$","[1,2,3]");
213 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
214 1 SIMPLE t1 NULL range i1 i1 9 NULL 3 100.00 Using where
215 Warnings:
216 Note 1003 /* select#1 */ select `test`.`t1`.`doc` AS `doc` from `test`.`t1` where json_contains(cast(json_extract(`doc`,_utf8mb4'$') as unsigned array),json'[1, 2, 3]')
217 explain select * from t1 where json_overlaps(doc->"$","[1,2,3]");
218 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
219 1 SIMPLE t1 NULL range i1 i1 9 NULL 3 100.00 Using where
220 Warnings:
221 Note 1003 /* select#1 */ select `test`.`t1`.`doc` AS `doc` from `test`.`t1` where json_overlaps(cast(json_extract(`doc`,_utf8mb4'$') as unsigned array),json'[1, 2, 3]')
222 explain select * from t1 where json_overlaps("[1,2,3]", doc->"$");
223 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
224 1 SIMPLE t1 NULL range i1 i1 9 NULL 3 100.00 Using where
225 Warnings:
226 Note 1003 /* select#1 */ select `test`.`t1`.`doc` AS `doc` from `test`.`t1` where json_overlaps(json'[1, 2, 3]',cast(json_extract(`doc`,_utf8mb4'$') as unsigned array))
227 drop table t2, t1, t;
228 create table t2(j json, key k ((cast(j->'$[*]' as char(10) array))));
229 insert into t2 values (json_array('abc')), (json_array('ABC'));
230 analyze table t2;
231 Table Op Msg_type Msg_text
232 test.t2 analyze status OK
233 select * from t2 where "abc" member of (j->'$[*]');
234 j
235 ["abc"]
236 explain select * from t2 where "abc" member of (j->'$[*]');
237 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
238 1 SIMPLE t2 NULL ref k k 43 const 1 100.00 Using where
239 Warnings:
240 Note 1003 /* select#1 */ select `test`.`t2`.`j` AS `j` from `test`.`t2` where json'"abc"' member of (cast(json_extract(`j`,_utf8mb4'$[*]') as char(10) array))
241 explain select * from t2 ignore key(k) where "abc" member of (j->'$[*]');
242 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
243 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
244 Warnings:
245 Note 1003 /* select#1 */ select `test`.`t2`.`j` AS `j` from `test`.`t2` IGNORE INDEX (`k`) where <cache>('abc') member of (json_extract(`test`.`t2`.`j`,'$[*]'))
246 insert into t2 values('[]');
247 drop table t2;
248 create table t1(j json, key i1((cast(j->"$.id" as char(10) array))), key i2((cast(j->"$.id" as unsigned array))));
249 insert into t1 values('{"id":1}');
250 select * from t1 where 1 member of (j->"$.id");
251 j
252 {"id": 1}
253 explain select * from t1 where 1 member of (j->"$.id");
254 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
255 1 SIMPLE t1 NULL ref i2 i2 9 const 1 100.00 Using where
256 Warnings:
257 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where json'1' member of (cast(json_extract(`j`,_utf8mb4'$.id') as unsigned array))
258 select * from t1 where '1' member of (j->"$.id");
259 j
260 {"id": 1}
261 explain select * from t1 where '1' member of (j->"$.id");
262 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
263 1 SIMPLE t1 NULL ref i1 i1 43 const 1 100.00 Using where
264 Warnings:
265 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where json'"1"' member of (cast(json_extract(`j`,_utf8mb4'$.id') as char(10) array))
266 drop table t1;
267 create table t(j json, key k ((cast(json_array(j) as unsigned array))));
268 insert into t values ('1');
269 select * from t where 1 member of (json_array(j));
270 j
271 1
272 explain select * from t where 1 member of (json_array(j));
273 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
274 1 SIMPLE t NULL ref k k 9 const 1 100.00 Using where
275 Warnings:
276 Note 1003 /* select#1 */ select `test`.`t`.`j` AS `j` from `test`.`t` where json'1' member of (cast(json_array(`j`) as unsigned array))
277 drop table t;
278 create table t1(x varchar(10), key k ((cast(concat(x,x) as unsigned array))));
279 insert into t1 values ('1');
280 select * from t1 where 11 member of (concat(x,x));
281 x
282 1
283 explain select * from t1 where 11 member of (concat(x,x));
284 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
285 1 SIMPLE t1 NULL ref k k 9 const 1 100.00 Using where
286 Warnings:
287 Note 1003 /* select#1 */ select `test`.`t1`.`x` AS `x` from `test`.`t1` where json'11' member of (cast(concat(`x`,`x`) as unsigned array))
288 drop table t1;
289 Test for error on array/object insertion into a scalar index
290 create table t1(j json, key i1((cast(json_array(j) as unsigned array))));
291 insert into t1 values ('{"a":1}');
292 ERROR HY000: Cannot store an array or an object in a scalar key part of the index 'i1'.
293 insert into t1 values ('[1,2,3]');
294 ERROR HY000: Cannot store an array or an object in a scalar key part of the index 'i1'.
295 drop table t1;
296 Same as above, but error is thrown from inside of InnoDB
297 create table t(j json);
298 insert into t values ('{"a":1}');
299 create index idx on t((cast(json_array(j) as unsigned array)));
300 ERROR HY000: Cannot store an array or an object in a scalar key part of the index 'idx'.
301 drop table t;
302 create table t1(j json,
303 sp_f json as (ifnull(j->'$.data','[]')) virtual,
304 key sp_i((cast(sp_f as unsigned array))));
305 insert into t1(j) values
306 ('{"data":[1,2,3]}'),
307 ('{"other_data":[4,5,6]}'),
308 ('"no data"');
309 analyze table t1;
310 Table Op Msg_type Msg_text
311 test.t1 analyze status OK
312 select * from t1 where 2 member of(sp_f);
313 j sp_f
314 {"data": [1, 2, 3]} [1, 2, 3]
315 explain select * from t1 where 2 member of(sp_f);
316 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
317 1 SIMPLE t1 NULL ref sp_i sp_i 9 const 1 100.00 Using where
318 Warnings:
319 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j`,`test`.`t1`.`sp_f` AS `sp_f` from `test`.`t1` where json'2' member of (cast(`sp_f` as unsigned array))
320 drop table t1;
321 create table t(vc varchar(10), key ((cast(vc->'$' as unsigned array))));
322 insert into t values ('');
323 ERROR 22032: Invalid JSON text in argument 1 to function json_extract: "The document is empty." at position 0.
324 drop table t;
325 create table t(j json, key i1((cast(j->'$' as unsigned))));
326 insert into t values('1');
327 select * from t where json_contains(j->'$', '1');
328 j
329 1
330 explain select * from t where json_contains(j->'$', '1');
331 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
332 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 Using where
333 Warnings:
334 Note 1003 /* select#1 */ select `test`.`t`.`j` AS `j` from `test`.`t` where json_contains(json_extract(`test`.`t`.`j`,'$'),<cache>('1'))
335 drop table t;
336 create table t(j json, key i1((cast(j->'$' as unsigned array))));
337 insert into t values('1');
338 select * from t where json_contains(j->'$', '1');
339 j
340 1
341 explain select * from t where json_contains(j->'$', '1');
342 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
343 1 SIMPLE t NULL range i1 i1 9 NULL 1 100.00 Using where
344 Warnings:
345 Note 1003 /* select#1 */ select `test`.`t`.`j` AS `j` from `test`.`t` where json_contains(cast(json_extract(`j`,_utf8mb4'$') as unsigned array),json'[1]')
346 select * from t where json_contains(j->'$', '{"a":1}');
347 j
348 select * from t where j->'$'= 1;
349 j
350 1
351 explain select * from t where j->'$'= 1;
352 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
353 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 Using where
354 Warnings:
355 Note 1003 /* select#1 */ select `test`.`t`.`j` AS `j` from `test`.`t` where (json_extract(`test`.`t`.`j`,'$') = 1)
356 select * from t where j->'$'>= 1;
357 j
358 1
359 explain select * from t where j->'$'>= 1;
360 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
361 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 Using where
362 Warnings:
363 Note 1003 /* select#1 */ select `test`.`t`.`j` AS `j` from `test`.`t` where (json_extract(`test`.`t`.`j`,'$') >= 1)
364 drop table t;
365 CREATE TABLE t1 (id INT AUTO_INCREMENT KEY, f1 JSON, KEY i1((CAST(f1->"$[*]" AS CHAR(10) ARRAY))));
366 INSERT INTO t1(f1) VALUES(JSON_ARRAY(_utf8mb4 0xE284AB, _utf8mb4 0xc385));
367 SELECT * FROM t1 WHERE _utf8mb4 0xE284AB MEMBER OF (f1->"$[*]");
368 id f1
369 1 ["â„«", "Ã…"]
370 SELECT * FROM t1 WHERE _utf8mb4 0xc385 MEMBER OF (f1->"$[*]");
371 id f1
372 1 ["â„«", "Ã…"]
373 DELETE FROM t1;
374 INSERT INTO t1(f1) VALUES(JSON_ARRAY( _utf8mb4 0xc385, _utf8mb4 0xE284AB));
375 SELECT * FROM t1 WHERE _utf8mb4 0xE284AB MEMBER OF (f1->"$[*]");
376 id f1
377 2 ["Ã…", "â„«"]
378 SELECT * FROM t1 WHERE _utf8mb4 0xc385 MEMBER OF (f1->"$[*]");
379 id f1
380 2 ["Ã…", "â„«"]
381 DELETE FROM t1;
382 INSERT INTO t1(f1) VALUES(JSON_ARRAY(_utf8mb4 0xE284AB));
383 SELECT * FROM t1 WHERE _utf8mb4 0xE284AB MEMBER OF (f1->"$[*]");
384 id f1
385 3 ["â„«"]
386 Empty result is expected
387 SELECT * FROM t1 WHERE _utf8mb4 0xc385 MEMBER OF (f1->"$[*]");
388 id f1
389 DELETE FROM t1;
390 INSERT INTO t1(f1) VALUES(JSON_ARRAY( _utf8mb4 0xc385));
391 Empty result is expected
392 SELECT * FROM t1 WHERE _utf8mb4 0xE284AB MEMBER OF (f1->"$[*]");
393 id f1
394 SELECT * FROM t1 WHERE _utf8mb4 0xc385 MEMBER OF (f1->"$[*]");
395 id f1
396 4 ["Ã…"]
397 DROP TABLE t1;
398 CREATE TABLE t1 (id INT AUTO_INCREMENT KEY, f1 JSON, UNIQUE KEY i1((CAST(f1->"$[*]" AS CHAR(10) ARRAY))));
399 INSERT INTO t1(f1) VALUES(JSON_ARRAY(_utf8mb4 0xE284AB, _utf8mb4 0xc385));
400 SELECT * FROM t1 WHERE _utf8mb4 0xE284AB MEMBER OF (f1->"$[*]");
401 id f1
402 1 ["â„«", "Ã…"]
403 SELECT * FROM t1 WHERE _utf8mb4 0xc385 MEMBER OF (f1->"$[*]");
404 id f1
405 1 ["â„«", "Ã…"]
406 DELETE FROM t1;
407 INSERT INTO t1(f1) VALUES(JSON_ARRAY( _utf8mb4 0xc385, _utf8mb4 0xE284AB));
408 SELECT * FROM t1 WHERE _utf8mb4 0xE284AB MEMBER OF (f1->"$[*]");
409 id f1
410 2 ["Ã…", "â„«"]
411 SELECT * FROM t1 WHERE _utf8mb4 0xc385 MEMBER OF (f1->"$[*]");
412 id f1
413 2 ["Ã…", "â„«"]
414 DELETE FROM t1;
415 INSERT INTO t1(f1) VALUES(JSON_ARRAY(_utf8mb4 0xE284AB));
416 SELECT * FROM t1 WHERE _utf8mb4 0xE284AB MEMBER OF (f1->"$[*]");
417 id f1
418 3 ["â„«"]
419 Empty result is expected
420 SELECT * FROM t1 WHERE _utf8mb4 0xc385 MEMBER OF (f1->"$[*]");
421 id f1
422 DELETE FROM t1;
423 INSERT INTO t1(f1) VALUES(JSON_ARRAY( _utf8mb4 0xc385));
424 Empty result is expected
425 SELECT * FROM t1 WHERE _utf8mb4 0xE284AB MEMBER OF (f1->"$[*]");
426 id f1
427 SELECT * FROM t1 WHERE _utf8mb4 0xc385 MEMBER OF (f1->"$[*]");
428 id f1
429 4 ["Ã…"]
430 DROP TABLE t1;
431 Test CAST( .. AS UNSIGNED ARRAY)
432 create table t1 (id int not null key auto_increment, f1 json);
433 create index i1 on t1((cast(f1->"$[*]" as unsigned array)));
434 show create table t1;
435 Table Create Table
436 t1 CREATE TABLE `t1` (
437 `id` int NOT NULL AUTO_INCREMENT,
438 `f1` json DEFAULT NULL,
439 PRIMARY KEY (`id`),
440 KEY `i1` ((cast(json_extract(`f1`,_utf8mb4'$[*]') as unsigned array)))
441 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
442 insert into t1(f1) values
443 (cast('[1,3]' as json)), (cast(2 as json)), (cast('[3,3,4,4,7]' as json)),
444 (cast('[5,7]' as json)),
445 (cast('[8,4,3,5]' as json)), (cast('[5,6,7]' as json)),
446 (cast('[9,2,7]' as json)), (cast('[1,3]' as json)),
447 (cast('[3,3,4,4,7]' as json)), (cast(4 as json)), (cast('[8,4,3,5]' as json)),
448 (cast('[9,2,7]' as json)), (cast('[9,2,7]' as json)),
449 (cast('[1,3]' as json)),
450 (cast('[3,3,4,4,7]' as json)), (cast(4 as json)),
451 (cast(7 as json)), (cast('[8,4,3,5]' as json)), (cast('[9,2,7]' as json)),
452 ('[98,99]');
453 analyze table t1;
454 Table Op Msg_type Msg_text
455 test.t1 analyze status OK
456 insert into t1(f1) values (cast("[-2]" as json));
457 ERROR 22003: Value is out of range for functional index 'i1' at row 1
458 insert into t1(f1) values (cast('[1,-3]' as json));
459 ERROR 22003: Value is out of range for functional index 'i1' at row 1
460 select * from t1;
461 id f1
462 1 [1, 3]
463 2 2
464 3 [3, 3, 4, 4, 7]
465 4 [5, 7]
466 5 [8, 4, 3, 5]
467 6 [5, 6, 7]
468 7 [9, 2, 7]
469 8 [1, 3]
470 9 [3, 3, 4, 4, 7]
471 10 4
472 11 [8, 4, 3, 5]
473 12 [9, 2, 7]
474 13 [9, 2, 7]
475 14 [1, 3]
476 15 [3, 3, 4, 4, 7]
477 16 4
478 17 7
479 18 [8, 4, 3, 5]
480 19 [9, 2, 7]
481 20 [98, 99]
482 select * from t1 where 5 member of (f1->"$[*]");
483 id f1
484 4 [5, 7]
485 5 [8, 4, 3, 5]
486 6 [5, 6, 7]
487 11 [8, 4, 3, 5]
488 18 [8, 4, 3, 5]
489 explain select * from t1 where 5 member of (f1->"$[*]");
490 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
491 1 SIMPLE t1 NULL ref i1 i1 9 const 5 100.00 Using where
492 Warnings:
493 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` where json'5' member of (cast(json_extract(`f1`,_utf8mb4'$[*]') as unsigned array))
494 select * from t1 where f1->"$[0]" member of ('[1,3,9]');
495 id f1
496 1 [1, 3]
497 3 [3, 3, 4, 4, 7]
498 7 [9, 2, 7]
499 8 [1, 3]
500 9 [3, 3, 4, 4, 7]
501 12 [9, 2, 7]
502 13 [9, 2, 7]
503 14 [1, 3]
504 15 [3, 3, 4, 4, 7]
505 19 [9, 2, 7]
506 explain select * from t1 where f1->"$[0]" member of ('[1,3,9]');
507 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
508 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 100.00 Using where
509 Warnings:
510 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` where json_extract(`test`.`t1`.`f1`,'$[0]') member of (<cache>('[1,3,9]'))
511 select * from t1 force index(i1) where json_contains(f1->"$[*]", "[4,3]");
512 id f1
513 3 [3, 3, 4, 4, 7]
514 5 [8, 4, 3, 5]
515 9 [3, 3, 4, 4, 7]
516 11 [8, 4, 3, 5]
517 15 [3, 3, 4, 4, 7]
518 18 [8, 4, 3, 5]
519 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[4,3]");
520 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
521 1 SIMPLE t1 NULL range i1 i1 9 NULL 2 100.00 Using where
522 Warnings:
523 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` FORCE INDEX (`i1`) where json_contains(cast(json_extract(`f1`,_utf8mb4'$[*]') as unsigned array),json'[4, 3]')
524 select * from t1 force index(i1) where json_contains(f1->"$[*]", "[5,7]");
525 id f1
526 4 [5, 7]
527 6 [5, 6, 7]
528 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[5,7]");
529 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
530 1 SIMPLE t1 NULL range i1 i1 9 NULL 2 100.00 Using where
531 Warnings:
532 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` FORCE INDEX (`i1`) where json_contains(cast(json_extract(`f1`,_utf8mb4'$[*]') as unsigned array),json'[5, 7]')
533 select * from t1 force index(i1) where json_contains(f1->"$[*]", "[7]");
534 id f1
535 3 [3, 3, 4, 4, 7]
536 4 [5, 7]
537 6 [5, 6, 7]
538 7 [9, 2, 7]
539 9 [3, 3, 4, 4, 7]
540 12 [9, 2, 7]
541 13 [9, 2, 7]
542 15 [3, 3, 4, 4, 7]
543 19 [9, 2, 7]
544 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[7]");
545 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
546 1 SIMPLE t1 NULL range i1 i1 9 NULL 1 100.00 Using where
547 Warnings:
548 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` FORCE INDEX (`i1`) where json_contains(cast(json_extract(`f1`,_utf8mb4'$[*]') as unsigned array),json'[7]')
549 set @save_opt=@@optimizer_switch;
550 set @@optimizer_switch="mrr=off";
551 select * from t1 force index(i1) where json_contains(f1->"$[*]", "[4,3]");
552 id f1
553 3 [3, 3, 4, 4, 7]
554 5 [8, 4, 3, 5]
555 9 [3, 3, 4, 4, 7]
556 11 [8, 4, 3, 5]
557 15 [3, 3, 4, 4, 7]
558 18 [8, 4, 3, 5]
559 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[4,3]");
560 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
561 1 SIMPLE t1 NULL range i1 i1 9 NULL 2 100.00 Using where
562 Warnings:
563 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` FORCE INDEX (`i1`) where json_contains(cast(json_extract(`f1`,_utf8mb4'$[*]') as unsigned array),json'[4, 3]')
564 select * from t1 force index(i1) where json_contains(f1->"$[*]", "[5,7]");
565 id f1
566 4 [5, 7]
567 6 [5, 6, 7]
568 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[5,7]");
569 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
570 1 SIMPLE t1 NULL range i1 i1 9 NULL 2 100.00 Using where
571 Warnings:
572 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` FORCE INDEX (`i1`) where json_contains(cast(json_extract(`f1`,_utf8mb4'$[*]') as unsigned array),json'[5, 7]')
573 select * from t1 force index(i1) where json_contains(f1->"$[*]", "[7]");
574 id f1
575 3 [3, 3, 4, 4, 7]
576 4 [5, 7]
577 6 [5, 6, 7]
578 7 [9, 2, 7]
579 9 [3, 3, 4, 4, 7]
580 12 [9, 2, 7]
581 13 [9, 2, 7]
582 15 [3, 3, 4, 4, 7]
583 19 [9, 2, 7]
584 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[7]");
585 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
586 1 SIMPLE t1 NULL range i1 i1 9 NULL 1 100.00 Using where
587 Warnings:
588 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` FORCE INDEX (`i1`) where json_contains(cast(json_extract(`f1`,_utf8mb4'$[*]') as unsigned array),json'[7]')
589 select * from t1 force index(i1) where 99 member of (f1->"$[*]");
590 id f1
591 20 [98, 99]
592 update t1 set f1=cast('[100,99]' as json) where 99 member of (f1->"$[*]");
593 select * from t1 force index(i1) where json_contains(f1->"$[*]", "[100]");
594 id f1
595 20 [100, 99]
596 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[100]");
597 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
598 1 SIMPLE t1 NULL range i1 i1 9 NULL 1 100.00 Using where
599 Warnings:
600 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` FORCE INDEX (`i1`) where json_contains(cast(json_extract(`f1`,_utf8mb4'$[*]') as unsigned array),json'[100]')
601 set @@optimizer_switch=@save_opt;
602 # Check that index applicability conditions aren't met as expected
603 explain select * from t1 force index(i1) where json_contains("[1,2,3,4]", "[4,3]");
604 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
605 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 100.00 NULL
606 Warnings:
607 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` FORCE INDEX (`i1`)
608 explain select * from t1 force index(i1) where
609 json_contains(concat("[1,2",",3,4]"), "[4,3]");
610 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
611 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 100.00 NULL
612 Warnings:
613 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` FORCE INDEX (`i1`)
614 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", f1->"$[*]");
615 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
616 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 100.00 Using where
617 Warnings:
618 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` FORCE INDEX (`i1`) where json_contains(json_extract(`test`.`t1`.`f1`,'$[*]'),json_extract(`test`.`t1`.`f1`,'$[*]'))
619 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[4,");
620 ERROR 22032: Invalid JSON text in argument 2 to function json_contains: "Invalid value." at position 3.
621 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", '{"a":4}');
622 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
623 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 100.00 Using where
624 Warnings:
625 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` FORCE INDEX (`i1`) where json_contains(json_extract(`test`.`t1`.`f1`,'$[*]'),<cache>('{"a":4}'))
626 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", '[4,"a"]');
627 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
628 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 100.00 Using where
629 Warnings:
630 Warning 3903 Invalid JSON value for CAST for functional index 'i1'.
631 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` FORCE INDEX (`i1`) where json_contains(json_extract(`test`.`t1`.`f1`,'$[*]'),<cache>('[4,"a"]'))
632 delete from t1;
633 drop table t1;
634 Test CAST( .. AS SIGNED ARRAY)
635 create table t1 (id int not null key auto_increment, f1 json);
636 create index i1 on t1((cast(f1->"$" as signed array)));
637 show create table t1;
638 Table Create Table
639 t1 CREATE TABLE `t1` (
640 `id` int NOT NULL AUTO_INCREMENT,
641 `f1` json DEFAULT NULL,
642 PRIMARY KEY (`id`),
643 KEY `i1` ((cast(json_extract(`f1`,_utf8mb4'$') as signed array)))
644 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
645 insert into t1(f1) values
646 (cast('[1,3]' as json)),
647 (cast(2 as json)),
648 (cast('[3,3,4,4,7]' as json)),
649 (cast(4 as json)),
650 (cast('[5,7]' as json)),
651 (cast(6 as json)),
652 (cast(7 as json)),
653 (cast('[8,5]' as json)),
654 (cast('[9,2]' as json));
655 insert into t1(f1) values
656 (cast('[1,-3]' as json)),
657 (cast(-2 as json)), ('[98,99]');
658 analyze table t1;
659 Table Op Msg_type Msg_text
660 test.t1 analyze status OK
661 select * from t1;
662 id f1
663 1 [1, 3]
664 2 2
665 3 [3, 3, 4, 4, 7]
666 4 4
667 5 [5, 7]
668 6 6
669 7 7
670 8 [8, 5]
671 9 [9, 2]
672 10 [1, -3]
673 11 -2
674 12 [98, 99]
675 should return record ## 5,8
676 select * from t1 where 5 member of (f1->"$");
677 id f1
678 5 [5, 7]
679 8 [8, 5]
680 explain select * from t1 where 5 member of (f1->"$");
681 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
682 1 SIMPLE t1 NULL ref i1 i1 9 const 2 100.00 Using where
683 Warnings:
684 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` where json'5' member of (cast(json_extract(`f1`,_utf8mb4'$') as signed array))
685 select * from t1 force index(i1) where 99 member of (f1->"$[*]");
686 id f1
687 12 [98, 99]
688 update t1 set f1=cast('[100,99]' as json) where 99 member of (f1->"$[*]");
689 select * from t1 force index(i1) where 100 member of (f1->"$[*]");
690 id f1
691 12 [100, 99]
692 explain select * from t1 force index(i1) where 100 member of (f1->"$[*]");
693 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
694 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 12 100.00 Using where
695 Warnings:
696 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` FORCE INDEX (`i1`) where <cache>(100) member of (json_extract(`test`.`t1`.`f1`,'$[*]'))
697 delete from t1;
698 drop table t1;
699 Test CAST( .. AS CHAR(X) ARRAY)
700 create table t1 (f1 json);
701 create index i1 on t1((cast(f1->"$[*]" as char(10) array)));
702 show create table t1;
703 Table Create Table
704 t1 CREATE TABLE `t1` (
705 `f1` json DEFAULT NULL,
706 KEY `i1` ((cast(json_extract(`f1`,_utf8mb4'$[*]') as char(10) array)))
707 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
708 insert into t1(f1) values(cast('"fgh"' as json));
709 insert into t1(f1) values(cast('["asd","qwe"]' as json));
710 insert into t1(f1) values(cast('["qe"]' as json));
711 insert into t1(f1) values(cast('["gfd","qwe"]' as json));
712 insert into t1(f1) values(cast('["ew","sdf"]' as json));
713 insert into t1(f1) values(cast('["xcv","cvb"]' as json));
714 insert into t1(f1) values(cast('["adf"]' as json));
715 insert into t1(f1) values(cast('["asd","sdfqwe"]' as json));
716 insert into t1(f1) values(cast('["ew","sdf"]' as json));
717 insert into t1(f1) values(cast('["xcv","cvb"]' as json));
718 insert into t1(f1) values(cast('["adf"]' as json));
719 insert into t1(f1) values(cast('["asd","sdfqwe"]' as json));
720 insert into t1(f1) values(cast('["bnm","sdfqwe"]' as json));
721 analyze table t1;
722 Table Op Msg_type Msg_text
723 test.t1 analyze status OK
724 select * from t1 where "qwe" member of (f1->"$[*]");
725 f1
726 ["asd", "qwe"]
727 ["gfd", "qwe"]
728 explain select * from t1 where "qwe" member of (f1->"$[*]");
729 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
730 1 SIMPLE t1 NULL ref i1 i1 43 const 2 100.00 Using where
731 Warnings:
732 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where json'"qwe"' member of (cast(json_extract(`f1`,_utf8mb4'$[*]') as char(10) array))
733 select * from t1 force index(i1) where "bnm" member of (f1->"$[*]");
734 f1
735 ["bnm", "sdfqwe"]
736 update t1 set f1=cast('["bvc", "hgfd"]' as json) where "bnm" member of (f1->"$[*]");
737 select * from t1 force index(i1) where "bvc" member of (f1->"$[*]");
738 f1
739 ["bvc", "hgfd"]
740 explain select * from t1 force index(i1) where "bvc" member of (f1->"$[*]");
741 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
742 1 SIMPLE t1 NULL ref i1 i1 43 const 1 100.00 Using where
743 Warnings:
744 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1` from `test`.`t1` FORCE INDEX (`i1`) where json'"bvc"' member of (cast(json_extract(`f1`,_utf8mb4'$[*]') as char(10) array))
745 delete from t1;
746 drop table t1;
747 Test CAST( .. AS BINARY(X) ARRAY)
748 create table t1 (f1 json);
749 create index i1 on t1((cast(f1->"$[*]" as binary(10) array)));
750 show create table t1;
751 Table Create Table
752 t1 CREATE TABLE `t1` (
753 `f1` json DEFAULT NULL,
754 KEY `i1` ((cast(json_extract(`f1`,_utf8mb4'$[*]') as binary(10) array)))
755 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
756 insert into t1(f1) values(cast('"fgh"' as json));
757 insert into t1(f1) values(cast('["asd","qwe"]' as json));
758 insert into t1(f1) values(cast('["qe"]' as json));
759 insert into t1(f1) values(cast('["gfd","qwe"]' as json));
760 insert into t1(f1) values(cast('["ew","sdf"]' as json));
761 insert into t1(f1) values(cast('["xcv","cvb"]' as json));
762 insert into t1(f1) values(cast('["adf"]' as json));
763 insert into t1(f1) values(cast('["asd","sdfqwe"]' as json));
764 insert into t1(f1) values(cast('["ew","sdf"]' as json));
765 insert into t1(f1) values(cast('["xcv","cvb"]' as json));
766 insert into t1(f1) values(cast('["adf"]' as json));
767 insert into t1(f1) values(cast('["asd","sdfqwe"]' as json));
768 insert into t1(f1) values(cast('["bnm","sdfqwe"]' as json));
769 analyze table t1;
770 Table Op Msg_type Msg_text
771 test.t1 analyze status OK
772 select * from t1 where "qwe" member of (f1->"$[*]");
773 f1
774 ["asd", "qwe"]
775 ["gfd", "qwe"]
776 explain select * from t1 where "qwe" member of (f1->"$[*]");
777 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
778 1 SIMPLE t1 NULL ref i1 i1 13 const 2 100.00 Using where
779 Warnings:
780 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where json'"base64:type15:cXdl"' member of (cast(json_extract(`f1`,_utf8mb4'$[*]') as binary(10) array))
781 select * from t1 force index(i1) where "bnm" member of (f1->"$[*]");
782 f1
783 ["bnm", "sdfqwe"]
784 update t1 set f1=cast('["bvc", "hgfd"]' as json) where "bnm" member of (f1->"$[*]");
785 select * from t1 force index(i1) where "bvc" member of (f1->"$[*]");
786 f1
787 ["bvc", "hgfd"]
788 explain select * from t1 force index(i1) where "bvc" member of (f1->"$[*]");
789 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
790 1 SIMPLE t1 NULL ref i1 i1 13 const 1 100.00 Using where
791 Warnings:
792 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1` from `test`.`t1` FORCE INDEX (`i1`) where json'"base64:type15:YnZj"' member of (cast(json_extract(`f1`,_utf8mb4'$[*]') as binary(10) array))
793 delete from t1;
794 drop table t1;
795 Test CAST( .. AS DECIMAL ARRAY)
796 create table t1 (id int not null key auto_increment, f1 json);
797 create index i1 on t1((cast(f1->"$" as decimal(10,4) array)));
798 show create table t1;
799 Table Create Table
800 t1 CREATE TABLE `t1` (
801 `id` int NOT NULL AUTO_INCREMENT,
802 `f1` json DEFAULT NULL,
803 PRIMARY KEY (`id`),
804 KEY `i1` ((cast(json_extract(`f1`,_utf8mb4'$') as decimal(10, 4) array)))
805 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
806 insert into t1(f1) values(cast(cast(0.0 as decimal) as json));
807 insert into t1(f1) values(cast(cast(1 as decimal) as json));
808 insert into t1(f1) values(cast(cast(-3 as decimal) as json));
809 insert into t1(f1) values(cast(cast(0.0 as decimal) as json));
810 insert into t1(f1) values(cast(cast(1 as decimal) as json));
811 insert into t1(f1) values(cast(cast(-3 as decimal) as json));
812 insert into t1(f1) values(json_array(
813 cast(1.33 as decimal(10,3)),
814 cast(5 as decimal(10,3)),
815 cast(1.33 as decimal(10,1))));
816 insert into t1(f1) values(json_array(
817 cast(-1.33 as decimal(10,3)),
818 cast(5 as decimal),
819 cast(1.33 as decimal(10,4))));
820 insert into t1(f1) values(json_array(
821 cast(98 as decimal(10,3)),
822 cast(99 as decimal)));
823 analyze table t1;
824 Table Op Msg_type Msg_text
825 test.t1 analyze status OK
826 select * from t1;
827 id f1
828 1 0
829 2 1
830 3 -3
831 4 0
832 5 1
833 6 -3
834 7 [1.330, 5.000, 1.3]
835 8 [-1.330, 5, 1.3300]
836 9 [98.000, 99]
837 select * from t1 where 1.33 member of (f1->"$");
838 id f1
839 7 [1.330, 5.000, 1.3]
840 8 [-1.330, 5, 1.3300]
841 explain select * from t1 where 1.33 member of (f1->"$");
842 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
843 1 SIMPLE t1 NULL ref i1 i1 6 const 2 100.00 Using where
844 Warnings:
845 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` where json'1.3300' member of (cast(json_extract(`f1`,_utf8mb4'$') as decimal(10, 4) array))
846 select * from t1 force index(i1) where 99 member of (f1->"$[*]");
847 id f1
848 9 [98.000, 99]
849 update t1 set f1=cast('[100,99]' as json) where 99 member of (f1->"$[*]");
850 select * from t1 force index(i1) where 100 member of (f1->"$[*]");
851 id f1
852 9 [100, 99]
853 explain select * from t1 force index(i1) where 100 member of (f1->"$[*]");
854 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
855 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 9 100.00 Using where
856 Warnings:
857 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` FORCE INDEX (`i1`) where <cache>(100) member of (json_extract(`test`.`t1`.`f1`,'$[*]'))
858 delete from t1;
859 drop table t1;
860 #Test CAST( .. AS YEAR ARRAY)
861 create table t1 (id int not null key auto_increment, f1 json);
862 create index i1 on t1((cast(f1->"$" as YEAR array)));
863 ERROR 42000: This version of MySQL doesn't yet support 'CAST-ing data to array of YEAR'
864 show create table t1;
865 Table Create Table
866 t1 CREATE TABLE `t1` (
867 `id` int NOT NULL AUTO_INCREMENT,
868 `f1` json DEFAULT NULL,
869 PRIMARY KEY (`id`)
870 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
871 DROP TABLE t1;
872 create table t1 (id int not null key auto_increment, f1 json, key i1 ((cast(f1->"$" as YEAR array)));
873 ERROR 42000: This version of MySQL doesn't yet support 'CAST-ing data to array of YEAR'
874 Test CAST( .. AS DATE ARRAY)
875 create table t1 (id int not null key auto_increment, f1 json);
876 create index i1 on t1((cast(f1->"$" as date array)));
877 show create table t1;
878 Table Create Table
879 t1 CREATE TABLE `t1` (
880 `id` int NOT NULL AUTO_INCREMENT,
881 `f1` json DEFAULT NULL,
882 PRIMARY KEY (`id`),
883 KEY `i1` ((cast(json_extract(`f1`,_utf8mb4'$') as date array)))
884 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
885 insert into t1(f1) values(cast(cast('01-01-01' as date) as json));
886 insert into t1(f1) values(cast(cast('01-02-03' as date) as json));
887 insert into t1(f1) values(cast(cast('22.11.17' as date) as json));
888 Warnings:
889 Warning 4095 Delimiter '.' in position 2 in datetime value '22.11.17' at row 1 is deprecated. Prefer the standard '-'.
890 insert into t1(f1) values(json_array(
891 cast('01-02-03' as date),
892 cast('03-03-03' as date),
893 cast('01.01.17' as date)));
894 Warnings:
895 Warning 4095 Delimiter '.' in position 2 in datetime value '01.01.17' at row 1 is deprecated. Prefer the standard '-'.
896 insert into t1(f1) values(json_array(
897 cast('03-03-03' as date),
898 cast('05-05-05' as date),
899 cast('01-02-03' as date)));
900 insert into t1(f1) values(json_array(
901 cast('01-01-12' as date),
902 cast('01-01-13' as date)));
903 insert into t1(f1) values('["01-02-03","22.11.17"]');
904 Warnings:
905 Warning 4095 Delimiter '.' in position 2 in datetime value '22.11.17' at row 1 is deprecated. Prefer the standard '-'.
906 analyze table t1;
907 Table Op Msg_type Msg_text
908 test.t1 analyze status OK
909 select * from t1 where cast('01-02-03' as date) member of (f1->"$");
910 id f1
911 2 "2001-02-03"
912 4 ["2001-02-03", "2003-03-03", "2001-01-17"]
913 5 ["2003-03-03", "2005-05-05", "2001-02-03"]
914 7 ["01-02-03", "22.11.17"]
915 Warnings:
916 Warning 4095 Delimiter '.' in position 2 in datetime value '22.11.17' at row 4 is deprecated. Prefer the standard '-'.
917 explain select * from t1 where cast('01-02-03' as date) member of (f1->"$");
918 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
919 1 SIMPLE t1 NULL ref i1 i1 4 const 4 100.00 Using where
920 Warnings:
921 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` where json'"2001-02-03"' member of (cast(json_extract(`f1`,_utf8mb4'$') as date array))
922 select * from t1 force index(i1) where cast('01-01-12' as date) member of (f1->"$");
923 id f1
924 6 ["2001-01-12", "2001-01-13"]
925 update t1 set f1=
926 json_array(cast('01-01-14' as date), cast('01-01-12' as date)) where
927 cast('01-01-12' as date) member of (f1->"$");
928 select * from t1 force index(i1) where
929 cast('01-01-14' as date) member of (f1->"$");
930 id f1
931 6 ["2001-01-14", "2001-01-12"]
932 explain select * from t1 force index(i1) where
933 cast('01-01-14' as date) member of (f1->"$");
934 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
935 1 SIMPLE t1 NULL ref i1 i1 4 const 1 100.00 Using where
936 Warnings:
937 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` FORCE INDEX (`i1`) where json'"2001-01-14"' member of (cast(json_extract(`f1`,_utf8mb4'$') as date array))
938 delete from t1;
939 Warnings:
940 Warning 4095 Delimiter '.' in position 2 in datetime value '22.11.17' at row 1 is deprecated. Prefer the standard '-'.
941 Warning 4095 Delimiter '.' in position 2 in datetime value '22.11.17' at row 1 is deprecated. Prefer the standard '-'.
942 drop table t1;
943 Test CAST( .. AS TIME ARRAY)
944 create table t1 (id int not null key auto_increment, f1 json);
945 create index i1 on t1((cast(f1->"$" as time(6) array)));
946 show create table t1;
947 Table Create Table
948 t1 CREATE TABLE `t1` (
949 `id` int NOT NULL AUTO_INCREMENT,
950 `f1` json DEFAULT NULL,
951 PRIMARY KEY (`id`),
952 KEY `i1` ((cast(json_extract(`f1`,_utf8mb4'$') as time(6) array)))
953 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
954 insert into t1(f1) values(cast(cast('01:01:01' as time) as json));
955 select * from t1;
956 id f1
957 1 "01:01:01.000000"
958 drop table t1;
959 create table t1 (id int not null key auto_increment, f1 json);
960 create index i1 on t1((cast(f1->"$" as time array)));
961 show create table t1;
962 Table Create Table
963 t1 CREATE TABLE `t1` (
964 `id` int NOT NULL AUTO_INCREMENT,
965 `f1` json DEFAULT NULL,
966 PRIMARY KEY (`id`),
967 KEY `i1` ((cast(json_extract(`f1`,_utf8mb4'$') as time array)))
968 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
969 insert into t1(f1) values(cast(cast('01:01:01' as time) as json));
970 insert into t1(f1) values(cast(cast('01:02:03' as time) as json));
971 insert into t1(f1) values(cast(cast('22:11:17' as time) as json));
972 insert into t1(f1) values(json_array(
973 cast('01:02:03' as time),
974 cast('03:03:03' as time),
975 cast('01:01:17' as time)));
976 insert into t1(f1) values(json_array(
977 cast('03:03:03' as time),
978 cast('05:05:05' as time),
979 cast('01:02:03' as time)));
980 insert into t1(f1) values(json_array(
981 cast('01:12:54' as time),
982 cast('01:12:55' as time)));
983 insert into t1(f1) values('["01:02:03","22:11:17"]');
984 analyze table t1;
985 Table Op Msg_type Msg_text
986 test.t1 analyze status OK
987 select * from t1 where cast('01:02:03' as time) member of (f1->"$");
988 id f1
989 2 "01:02:03.000000"
990 4 ["01:02:03.000000", "03:03:03.000000", "01:01:17.000000"]
991 5 ["03:03:03.000000", "05:05:05.000000", "01:02:03.000000"]
992 7 ["01:02:03", "22:11:17"]
993 explain select * from t1 where cast('01:02:03' as time) member of (f1->"$");
994 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
995 1 SIMPLE t1 NULL ref i1 i1 4 const 4 100.00 Using where
996 Warnings:
997 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` where json'"01:02:03.000000"' member of (cast(json_extract(`f1`,_utf8mb4'$') as time array))
998 select * from t1 force index(i1) where
999 cast('01:12:54' as time) member of (f1->"$");
1000 id f1
1001 6 ["01:12:54.000000", "01:12:55.000000"]
1002 update t1 set f1=
1003 json_array(cast('01:12:56' as time), cast('01:12:55' as time)) where
1004 cast('01:12:54' as time) member of (f1->"$");
1005 select * from t1 force index(i1) where
1006 cast('01:12:56' as time) member of (f1->"$");
1007 id f1
1008 6 ["01:12:56.000000", "01:12:55.000000"]
1009 explain select * from t1 force index(i1) where
1010 cast('01:12:56' as time) member of (f1->"$");
1011 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1012 1 SIMPLE t1 NULL ref i1 i1 4 const 1 100.00 Using where
1013 Warnings:
1014 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` FORCE INDEX (`i1`) where json'"01:12:56.000000"' member of (cast(json_extract(`f1`,_utf8mb4'$') as time array))
1015 delete from t1;
1016 drop table t1;
1017 Test CAST( .. AS DATETIME ARRAY)
1018 create table t1 (id int not null key auto_increment, f1 json);
1019 create index i1 on t1((cast(f1->"$" as datetime(6) array)));
1020 show create table t1;
1021 Table Create Table
1022 t1 CREATE TABLE `t1` (
1023 `id` int NOT NULL AUTO_INCREMENT,
1024 `f1` json DEFAULT NULL,
1025 PRIMARY KEY (`id`),
1026 KEY `i1` ((cast(json_extract(`f1`,_utf8mb4'$') as datetime(6) array)))
1027 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1028 insert into t1(f1) values(cast(cast('01-01-01 01:01:01' as datetime) as json));
1029 select * from t1;
1030 id f1
1031 1 "2001-01-01 01:01:01.000000"
1032 drop table t1;
1033 create table t1 (id int not null key auto_increment, f1 json);
1034 create index i1 on t1((cast(f1->"$" as datetime array)));
1035 show create table t1;
1036 Table Create Table
1037 t1 CREATE TABLE `t1` (
1038 `id` int NOT NULL AUTO_INCREMENT,
1039 `f1` json DEFAULT NULL,
1040 PRIMARY KEY (`id`),
1041 KEY `i1` ((cast(json_extract(`f1`,_utf8mb4'$') as datetime array)))
1042 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1043 insert into t1(f1) values(cast(cast('01-01-01 01:01:01' as datetime) as json));
1044 insert into t1(f1) values(cast(cast('01-01-01 01:02:03' as datetime) as json));
1045 insert into t1(f1) values(cast(cast('01-01-01 22:11:17' as datetime) as json));
1046 insert into t1(f1) values(json_array(
1047 cast('01-01-01 01:02:03' as datetime),
1048 cast('01-01-01 03:03:03' as datetime),
1049 cast('01-01-01 01:01:17' as datetime)));
1050 insert into t1(f1) values(json_array(
1051 cast('01-01-01 03:03:03' as datetime),
1052 cast('01-01-01 05:05:05' as datetime),
1053 cast('01-01-01 01:02:03' as datetime)));
1054 insert into t1(f1) values(json_array(
1055 cast('01-01-01 01:12:54' as datetime),
1056 cast('01-01-01 01:12:55' as datetime)));
1057 insert into t1(f1) values('["01-01-01 01:02:03","01-01-01 22:11:17"]');
1058 analyze table t1;
1059 Table Op Msg_type Msg_text
1060 test.t1 analyze status OK
1061 select * from t1 where cast('01-01-01 01:02:03' as datetime) member of (f1->"$");
1062 id f1
1063 2 "2001-01-01 01:02:03.000000"
1064 4 ["2001-01-01 01:02:03.000000", "2001-01-01 03:03:03.000000", "2001-01-01 01:01:17.000000"]
1065 5 ["2001-01-01 03:03:03.000000", "2001-01-01 05:05:05.000000", "2001-01-01 01:02:03.000000"]
1066 7 ["01-01-01 01:02:03", "01-01-01 22:11:17"]
1067 explain select * from t1 where cast('01-01-01 01:02:03' as datetime) member
1068 of (f1->"$");
1069 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1070 1 SIMPLE t1 NULL ref i1 i1 6 const 4 100.00 Using where
1071 Warnings:
1072 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` where json'"2001-01-01 01:02:03.000000"' member of (cast(json_extract(`f1`,_utf8mb4'$') as datetime array))
1073 select * from t1 force index(i1) where
1074 cast('01-01-01 01:12:54' as datetime) member of (f1->"$");
1075 id f1
1076 6 ["2001-01-01 01:12:54.000000", "2001-01-01 01:12:55.000000"]
1077 update t1 set f1=
1078 json_array(cast('01-01-01 01:12:56' as datetime),
1079 cast('01-01-01 01:12:55' as datetime)) where
1080 cast('01-01-01 01:12:54' as datetime) member of (f1->"$");
1081 select * from t1 force index(i1) where
1082 cast('01-01-01 01:12:56' as datetime) member of (f1->"$");
1083 id f1
1084 6 ["2001-01-01 01:12:56.000000", "2001-01-01 01:12:55.000000"]
1085 explain select * from t1 force index(i1) where
1086 cast('01-01-01 01:12:56' as datetime) member of (f1->"$");
1087 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1088 1 SIMPLE t1 NULL ref i1 i1 6 const 1 100.00 Using where
1089 Warnings:
1090 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` FORCE INDEX (`i1`) where json'"2001-01-01 01:12:56.000000"' member of (cast(json_extract(`f1`,_utf8mb4'$') as datetime array))
1091 delete from t1;
1092 drop table t1;
1093 select json_overlaps(cast('[1,2,3]' as json), cast('[3,4,5]' as json));
1094 json_overlaps(cast('[1,2,3]' as json), cast('[3,4,5]' as json))
1095 1
1096 select json_overlaps(cast('[1,2,3]' as json), cast('[4,4,5]' as json));
1097 json_overlaps(cast('[1,2,3]' as json), cast('[4,4,5]' as json))
1098 0
1099 select json_overlaps(cast('[1,2,3]' as json), cast('[4,5]' as json));
1100 json_overlaps(cast('[1,2,3]' as json), cast('[4,5]' as json))
1101 0
1102 select json_overlaps(cast('[1,2]' as json), cast('[3,4,5]' as json));
1103 json_overlaps(cast('[1,2]' as json), cast('[3,4,5]' as json))
1104 0
1105 select json_overlaps(cast('[1,2]' as json), cast('[2,4,5]' as json));
1106 json_overlaps(cast('[1,2]' as json), cast('[2,4,5]' as json))
1107 1
1108 select json_overlaps(cast('1' as json), cast('[3,4,5]' as json));
1109 json_overlaps(cast('1' as json), cast('[3,4,5]' as json))
1110 0
1111 select json_overlaps(cast('[3,4,5]' as json), cast('1' as json));
1112 json_overlaps(cast('[3,4,5]' as json), cast('1' as json))
1113 0
1114 select json_overlaps(cast('[3,4,{"a":5}]' as json), cast('{"a":5}' as json));
1115 json_overlaps(cast('[3,4,{"a":5}]' as json), cast('{"a":5}' as json))
1116 1
1117 select json_overlaps(cast('{"a":1, "b":2}' as json), cast('{"a":1,"c":3}' as json));
1118 json_overlaps(cast('{"a":1, "b":2}' as json), cast('{"a":1,"c":3}' as json))
1119 1
1120 select json_overlaps(cast('{"a":1, "b":2}' as json), cast('{"a":2,"c":3}' as json));
1121 json_overlaps(cast('{"a":1, "b":2}' as json), cast('{"a":2,"c":3}' as json))
1122 0
1123 select json_overlaps(cast('{"a":1, "b":null}' as json), cast('{"a":2,"c":3}' as json));
1124 json_overlaps(cast('{"a":1, "b":null}' as json), cast('{"a":2,"c":3}' as json))
1125 0
1126 select json_overlaps(cast('{"a":1, "b":2}' as json), cast('{"a":null, "c":3}' as json));
1127 json_overlaps(cast('{"a":1, "b":2}' as json), cast('{"a":null, "c":3}' as json))
1128 0
1129 select json_overlaps('null','[null]');
1130 json_overlaps('null','[null]')
1131 1
1132 select json_overlaps('1234',NULL);
1133 json_overlaps('1234',NULL)
1134 NULL
1135 select json_overlaps('null',NULL);
1136 json_overlaps('null',NULL)
1137 NULL
1138 select json_overlaps('asdasd',NULL);
1139 ERROR 22032: Invalid JSON text in argument 1 to function json_overlaps: "Invalid value." at position 0.
1140 select json_overlaps('[{"a":1}]', '{"a":1}') as c1,
1141 json_overlaps('[{"a":1}]', '[{"a":1}]') as c2;
1142 c1 c2
1143 1 1
1144 select json_overlaps('[{}]', '{}') as c1;
1145 c1
1146 1
1147 select json_overlaps('{}', '[{}]') as c1;
1148 c1
1149 1
1150 select json_overlaps('[{}]', '{"a":1, "b":2}') as c1;
1151 c1
1152 0
1153 select json_overlaps('[{}]', '1') as c1;
1154 c1
1155 0
1156 select json_overlaps("1","1") as c1;
1157 c1
1158 1
1159 select json_overlaps("true","false") as c1;
1160 c1
1161 0
1162 select json_overlaps("null","null") as c1;
1163 c1
1164 1
1165 select json_overlaps("123",'{"asd":123}') as c1;
1166 c1
1167 0
1168 create table t(j json, key ((cast(j->'$[*]' as unsigned array))));
1169 select * from t where json_overlaps('[]', j->'$[*]');
1170 j
1171 select * from t where json_contains(j->'$[*]', '[]');
1172 j
1173 drop table t;
1174 create table t1 (id int not null key auto_increment, f1 json);
1175 create index i1 on t1((cast(f1->"$[*]" as signed array)));
1176 show create table t1;
1177 Table Create Table
1178 t1 CREATE TABLE `t1` (
1179 `id` int NOT NULL AUTO_INCREMENT,
1180 `f1` json DEFAULT NULL,
1181 PRIMARY KEY (`id`),
1182 KEY `i1` ((cast(json_extract(`f1`,_utf8mb4'$[*]') as signed array)))
1183 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1184 insert into t1(f1) values
1185 (cast('[1,3]' as json)),
1186 (cast(2 as json)),
1187 (cast('[3,3,4,4,7]' as json)),
1188 (cast(4 as json)),
1189 (cast('[5,7]' as json)),
1190 (cast(6 as json)),
1191 (cast(7 as json)),
1192 (cast('[8,4,3,5]' as json)),
1193 (cast('[9,2,7]' as json)),
1194 (cast('null' as json));
1195 analyze table t1;
1196 Table Op Msg_type Msg_text
1197 test.t1 analyze status OK
1198 select * from t1 where json_overlaps(cast('[4,6]' as json), f1->'$[*]');
1199 id f1
1200 3 [3, 3, 4, 4, 7]
1201 8 [8, 4, 3, 5]
1202 explain select * from t1 where json_overlaps(cast('[4,6]' as json), f1->'$[*]');
1203 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1204 1 SIMPLE t1 NULL range i1 i1 9 NULL 3 100.00 Using where
1205 Warnings:
1206 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` where json_overlaps(json'[4, 6]',cast(json_extract(`f1`,_utf8mb4'$[*]') as signed array))
1207 select * from t1 where json_overlaps(f1->'$[*]', cast('[2,5]' as json));
1208 id f1
1209 5 [5, 7]
1210 8 [8, 4, 3, 5]
1211 9 [9, 2, 7]
1212 explain select * from t1 where json_overlaps(f1->'$[*]', cast('[2,5]' as json));
1213 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1214 1 SIMPLE t1 NULL range i1 i1 9 NULL 3 100.00 Using where
1215 Warnings:
1216 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` where json_overlaps(cast(json_extract(`f1`,_utf8mb4'$[*]') as signed array),json'[2, 5]')
1217 drop table t1;
1218 create table t1 (f1 json, id varchar(255) as (f1->"$[0]") stored not null primary key );
1219 create index i1 on t1((cast(f1->"$[*]" as unsigned array)));
1220 show create table t1;
1221 Table Create Table
1222 t1 CREATE TABLE `t1` (
1223 `f1` json DEFAULT NULL,
1224 `id` varchar(255) GENERATED ALWAYS AS (json_extract(`f1`,_utf8mb4'$[0]')) STORED NOT NULL,
1225 PRIMARY KEY (`id`),
1226 KEY `i1` ((cast(json_extract(`f1`,_utf8mb4'$[*]') as unsigned array)))
1227 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1228 insert into t1(f1) values
1229 (cast('[1,3]' as json)), (cast(2 as json)), (cast('[3,3,4,4,7]' as json)),
1230 (cast(4 as json)), (cast('[100,5,7]' as json)), (cast(6 as json)),
1231 (cast(7 as json)), (cast('[8,4,3,5]' as json)), (cast('[5,6,7]' as json)),
1232 (cast('[9,2,7]' as json));
1233 insert into t1(f1) values
1234 (cast('[11,3]' as json)), (cast(12 as json)), (cast('[13,3,4,4,7]' as json)),
1235 (cast(14 as json)), (cast('[0,5,7]' as json)), (cast(16 as json)),
1236 (cast(17 as json)), (cast('[18,4,3,5]' as json)), (cast('[15,6,7]' as json)),
1237 (cast('[19,2,7]' as json));
1238 insert into t1(f1) values
1239 (cast('[21,3]' as json)), (cast(22 as json)), (cast('[23,3,4,4,7]' as json)),
1240 (cast(24 as json)), (cast('[10,5,7]' as json)), (cast(26 as json)),
1241 (cast(27 as json)), (cast('[28,4,3,5]' as json)), (cast('[25,6,7]' as json)),
1242 (cast('[29,2,7]' as json));
1243 insert into t1(f1) values
1244 (cast('[31,3]' as json)), (cast(32 as json)), (cast('[33,3,4,4,7]' as json)),
1245 (cast(34 as json)), (cast('[20,5,7]' as json)), (cast(36 as json)),
1246 (cast(37 as json)), (cast('[38,4,3,5]' as json)), (cast('[35,6,7]' as json)),
1247 (cast('[39,2,7]' as json));
1248 insert into t1(f1) values
1249 (cast('[41,3]' as json)), (cast(42 as json)), (cast('[43,3,4,4,7]' as json)),
1250 (cast(44 as json)), (cast('[30,5,7]' as json)), (cast(46 as json)),
1251 (cast(47 as json)), (cast('[48,4,3,5]' as json)), (cast('[45,6,7]' as json)),
1252 (cast('[49,2,7]' as json));
1253 insert into t1(f1) values
1254 (cast('[51,3]' as json)), (cast(52 as json)), (cast('[53,3,4,4,7]' as json)),
1255 (cast(54 as json)), (cast('[40,5,7]' as json)), (cast(56 as json)),
1256 (cast(57 as json)), (cast('[58,4,3,5]' as json)), (cast('[55,6,7]' as json)),
1257 (cast('[59,2,7]' as json));
1258 analyze table t1;
1259 Table Op Msg_type Msg_text
1260 test.t1 analyze status OK
1261 set @save_sbs= @@sort_buffer_size;
1262 set @@sort_buffer_size=32768;
1263 select * from t1 force index(i1)
1264 where json_overlaps(f1->'$[*]', '[4,3,7]') order by id;
1265 f1 id
1266 [0, 5, 7] 0
1267 [1, 3] 1
1268 [10, 5, 7] 10
1269 [100, 5, 7] 100
1270 [11, 3] 11
1271 [13, 3, 4, 4, 7] 13
1272 [15, 6, 7] 15
1273 [18, 4, 3, 5] 18
1274 [19, 2, 7] 19
1275 [20, 5, 7] 20
1276 [21, 3] 21
1277 [23, 3, 4, 4, 7] 23
1278 [25, 6, 7] 25
1279 [28, 4, 3, 5] 28
1280 [29, 2, 7] 29
1281 [3, 3, 4, 4, 7] 3
1282 [30, 5, 7] 30
1283 [31, 3] 31
1284 [33, 3, 4, 4, 7] 33
1285 [35, 6, 7] 35
1286 [38, 4, 3, 5] 38
1287 [39, 2, 7] 39
1288 [40, 5, 7] 40
1289 [41, 3] 41
1290 [43, 3, 4, 4, 7] 43
1291 [45, 6, 7] 45
1292 [48, 4, 3, 5] 48
1293 [49, 2, 7] 49
1294 [5, 6, 7] 5
1295 [51, 3] 51
1296 [53, 3, 4, 4, 7] 53
1297 [55, 6, 7] 55
1298 [58, 4, 3, 5] 58
1299 [59, 2, 7] 59
1300 [8, 4, 3, 5] 8
1301 [9, 2, 7] 9
1302 explain select * from t1 force index(i1)
1303 where json_overlaps(f1->'$[*]', '[4,3,7]') order by id;
1304 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1305 1 SIMPLE t1 NULL range i1 i1 9 NULL 54 100.00 Using where; Using filesort
1306 Warnings:
1307 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`id` AS `id` from `test`.`t1` FORCE INDEX (`i1`) where json_overlaps(cast(json_extract(`f1`,_utf8mb4'$[*]') as unsigned array),json'[4, 3, 7]') order by `test`.`t1`.`id`
1308 prepare stmt from "select * from t1 force index(i1)
1309 where json_overlaps(f1->'$[*]', '[4,3,7]') order by id";
1310 execute stmt;
1311 f1 id
1312 [0, 5, 7] 0
1313 [1, 3] 1
1314 [10, 5, 7] 10
1315 [100, 5, 7] 100
1316 [11, 3] 11
1317 [13, 3, 4, 4, 7] 13
1318 [15, 6, 7] 15
1319 [18, 4, 3, 5] 18
1320 [19, 2, 7] 19
1321 [20, 5, 7] 20
1322 [21, 3] 21
1323 [23, 3, 4, 4, 7] 23
1324 [25, 6, 7] 25
1325 [28, 4, 3, 5] 28
1326 [29, 2, 7] 29
1327 [3, 3, 4, 4, 7] 3
1328 [30, 5, 7] 30
1329 [31, 3] 31
1330 [33, 3, 4, 4, 7] 33
1331 [35, 6, 7] 35
1332 [38, 4, 3, 5] 38
1333 [39, 2, 7] 39
1334 [40, 5, 7] 40
1335 [41, 3] 41
1336 [43, 3, 4, 4, 7] 43
1337 [45, 6, 7] 45
1338 [48, 4, 3, 5] 48
1339 [49, 2, 7] 49
1340 [5, 6, 7] 5
1341 [51, 3] 51
1342 [53, 3, 4, 4, 7] 53
1343 [55, 6, 7] 55
1344 [58, 4, 3, 5] 58
1345 [59, 2, 7] 59
1346 [8, 4, 3, 5] 8
1347 [9, 2, 7] 9
1348 deallocate prepare stmt;
1349 select count(*) from t1 force index(i1)
1350 where json_overlaps(f1->'$[*]', '[4,3,7]') order by id;
1351 count(*)
1352 36
1353 explain select count(*) from t1 force index(i1)
1354 where json_overlaps(f1->'$[*]', '[4,3,7]') order by id;
1355 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1356 1 SIMPLE t1 NULL range i1 i1 9 NULL 54 100.00 Using where
1357 Warnings:
1358 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` FORCE INDEX (`i1`) where json_overlaps(cast(json_extract(`f1`,_utf8mb4'$[*]') as unsigned array),json'[4, 3, 7]')
1359 prepare stmt from "select count(*) from t1 force index(i1)
1360 where json_overlaps(f1->'$[*]', '[4,3,7]') order by id";
1361 execute stmt;
1362 count(*)
1363 36
1364 deallocate prepare stmt;
1365 select /*+ NO_BNL(t1,tt) JOIN_ORDER(t1,tt) */ * from t1 join t1 as tt force index(i1)
1366 where json_overlaps(tt.f1->'$[*]', '[4,3,7]') and t1.id in (1,2) order by t1.id,tt.id;
1367 f1 id f1 id
1368 [1, 3] 1 [0, 5, 7] 0
1369 [1, 3] 1 [1, 3] 1
1370 [1, 3] 1 [10, 5, 7] 10
1371 [1, 3] 1 [100, 5, 7] 100
1372 [1, 3] 1 [11, 3] 11
1373 [1, 3] 1 [13, 3, 4, 4, 7] 13
1374 [1, 3] 1 [15, 6, 7] 15
1375 [1, 3] 1 [18, 4, 3, 5] 18
1376 [1, 3] 1 [19, 2, 7] 19
1377 [1, 3] 1 [20, 5, 7] 20
1378 [1, 3] 1 [21, 3] 21
1379 [1, 3] 1 [23, 3, 4, 4, 7] 23
1380 [1, 3] 1 [25, 6, 7] 25
1381 [1, 3] 1 [28, 4, 3, 5] 28
1382 [1, 3] 1 [29, 2, 7] 29
1383 [1, 3] 1 [3, 3, 4, 4, 7] 3
1384 [1, 3] 1 [30, 5, 7] 30
1385 [1, 3] 1 [31, 3] 31
1386 [1, 3] 1 [33, 3, 4, 4, 7] 33
1387 [1, 3] 1 [35, 6, 7] 35
1388 [1, 3] 1 [38, 4, 3, 5] 38
1389 [1, 3] 1 [39, 2, 7] 39
1390 [1, 3] 1 [40, 5, 7] 40
1391 [1, 3] 1 [41, 3] 41
1392 [1, 3] 1 [43, 3, 4, 4, 7] 43
1393 [1, 3] 1 [45, 6, 7] 45
1394 [1, 3] 1 [48, 4, 3, 5] 48
1395 [1, 3] 1 [49, 2, 7] 49
1396 [1, 3] 1 [5, 6, 7] 5
1397 [1, 3] 1 [51, 3] 51
1398 [1, 3] 1 [53, 3, 4, 4, 7] 53
1399 [1, 3] 1 [55, 6, 7] 55
1400 [1, 3] 1 [58, 4, 3, 5] 58
1401 [1, 3] 1 [59, 2, 7] 59
1402 [1, 3] 1 [8, 4, 3, 5] 8
1403 [1, 3] 1 [9, 2, 7] 9
1404 2 2 [0, 5, 7] 0
1405 2 2 [1, 3] 1
1406 2 2 [10, 5, 7] 10
1407 2 2 [100, 5, 7] 100
1408 2 2 [11, 3] 11
1409 2 2 [13, 3, 4, 4, 7] 13
1410 2 2 [15, 6, 7] 15
1411 2 2 [18, 4, 3, 5] 18
1412 2 2 [19, 2, 7] 19
1413 2 2 [20, 5, 7] 20
1414 2 2 [21, 3] 21
1415 2 2 [23, 3, 4, 4, 7] 23
1416 2 2 [25, 6, 7] 25
1417 2 2 [28, 4, 3, 5] 28
1418 2 2 [29, 2, 7] 29
1419 2 2 [3, 3, 4, 4, 7] 3
1420 2 2 [30, 5, 7] 30
1421 2 2 [31, 3] 31
1422 2 2 [33, 3, 4, 4, 7] 33
1423 2 2 [35, 6, 7] 35
1424 2 2 [38, 4, 3, 5] 38
1425 2 2 [39, 2, 7] 39
1426 2 2 [40, 5, 7] 40
1427 2 2 [41, 3] 41
1428 2 2 [43, 3, 4, 4, 7] 43
1429 2 2 [45, 6, 7] 45
1430 2 2 [48, 4, 3, 5] 48
1431 2 2 [49, 2, 7] 49
1432 2 2 [5, 6, 7] 5
1433 2 2 [51, 3] 51
1434 2 2 [53, 3, 4, 4, 7] 53
1435 2 2 [55, 6, 7] 55
1436 2 2 [58, 4, 3, 5] 58
1437 2 2 [59, 2, 7] 59
1438 2 2 [8, 4, 3, 5] 8
1439 2 2 [9, 2, 7] 9
1440 explain select /*+ NO_BNL(t1,tt) JOIN_ORDER(t1,tt) */ * from t1 join t1 as tt force index(i1)
1441 where json_overlaps(tt.f1->'$[*]', '[4,3,7]') and t1.id in (1,2) order by t1.id,tt.id;
1442 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1443 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 60 20.00 Using where; Using temporary; Using filesort
1444 1 SIMPLE tt NULL range i1 i1 9 NULL 54 100.00 Using where
1445 Warnings:
1446 Warning 1739 Cannot use range access on index 'PRIMARY' due to type or collation conversion on field 'id'
1447 Note 1003 /* select#1 */ select /*+ JOIN_ORDER(@`select#1` `t1`,`tt`) NO_BNL(`t1`@`select#1`) NO_BNL(`tt`@`select#1`) */ `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`id` AS `id`,`test`.`tt`.`f1` AS `f1`,`test`.`tt`.`id` AS `id` from `test`.`t1` join `test`.`t1` `tt` FORCE INDEX (`i1`) where (json_overlaps(cast(json_extract(`f1`,_utf8mb4'$[*]') as unsigned array),json'[4, 3, 7]') and (`test`.`t1`.`id` in (1,2))) order by `test`.`t1`.`id`,`test`.`tt`.`id`
1448 prepare stmt from "select /*+ NO_BNL(t1,tt) JOIN_ORDER(t1,tt) */ * from t1 join t1 as tt force index(i1)
1449 where json_overlaps(tt.f1->'$[*]', '[4,3,7]') and t1.id in (1,2) order by t1.id,tt.id";
1450 execute stmt;
1451 f1 id f1 id
1452 [1, 3] 1 [0, 5, 7] 0
1453 [1, 3] 1 [1, 3] 1
1454 [1, 3] 1 [10, 5, 7] 10
1455 [1, 3] 1 [100, 5, 7] 100
1456 [1, 3] 1 [11, 3] 11
1457 [1, 3] 1 [13, 3, 4, 4, 7] 13
1458 [1, 3] 1 [15, 6, 7] 15
1459 [1, 3] 1 [18, 4, 3, 5] 18
1460 [1, 3] 1 [19, 2, 7] 19
1461 [1, 3] 1 [20, 5, 7] 20
1462 [1, 3] 1 [21, 3] 21
1463 [1, 3] 1 [23, 3, 4, 4, 7] 23
1464 [1, 3] 1 [25, 6, 7] 25
1465 [1, 3] 1 [28, 4, 3, 5] 28
1466 [1, 3] 1 [29, 2, 7] 29
1467 [1, 3] 1 [3, 3, 4, 4, 7] 3
1468 [1, 3] 1 [30, 5, 7] 30
1469 [1, 3] 1 [31, 3] 31
1470 [1, 3] 1 [33, 3, 4, 4, 7] 33
1471 [1, 3] 1 [35, 6, 7] 35
1472 [1, 3] 1 [38, 4, 3, 5] 38
1473 [1, 3] 1 [39, 2, 7] 39
1474 [1, 3] 1 [40, 5, 7] 40
1475 [1, 3] 1 [41, 3] 41
1476 [1, 3] 1 [43, 3, 4, 4, 7] 43
1477 [1, 3] 1 [45, 6, 7] 45
1478 [1, 3] 1 [48, 4, 3, 5] 48
1479 [1, 3] 1 [49, 2, 7] 49
1480 [1, 3] 1 [5, 6, 7] 5
1481 [1, 3] 1 [51, 3] 51
1482 [1, 3] 1 [53, 3, 4, 4, 7] 53
1483 [1, 3] 1 [55, 6, 7] 55
1484 [1, 3] 1 [58, 4, 3, 5] 58
1485 [1, 3] 1 [59, 2, 7] 59
1486 [1, 3] 1 [8, 4, 3, 5] 8
1487 [1, 3] 1 [9, 2, 7] 9
1488 2 2 [0, 5, 7] 0
1489 2 2 [1, 3] 1
1490 2 2 [10, 5, 7] 10
1491 2 2 [100, 5, 7] 100
1492 2 2 [11, 3] 11
1493 2 2 [13, 3, 4, 4, 7] 13
1494 2 2 [15, 6, 7] 15
1495 2 2 [18, 4, 3, 5] 18
1496 2 2 [19, 2, 7] 19
1497 2 2 [20, 5, 7] 20
1498 2 2 [21, 3] 21
1499 2 2 [23, 3, 4, 4, 7] 23
1500 2 2 [25, 6, 7] 25
1501 2 2 [28, 4, 3, 5] 28
1502 2 2 [29, 2, 7] 29
1503 2 2 [3, 3, 4, 4, 7] 3
1504 2 2 [30, 5, 7] 30
1505 2 2 [31, 3] 31
1506 2 2 [33, 3, 4, 4, 7] 33
1507 2 2 [35, 6, 7] 35
1508 2 2 [38, 4, 3, 5] 38
1509 2 2 [39, 2, 7] 39
1510 2 2 [40, 5, 7] 40
1511 2 2 [41, 3] 41
1512 2 2 [43, 3, 4, 4, 7] 43
1513 2 2 [45, 6, 7] 45
1514 2 2 [48, 4, 3, 5] 48
1515 2 2 [49, 2, 7] 49
1516 2 2 [5, 6, 7] 5
1517 2 2 [51, 3] 51
1518 2 2 [53, 3, 4, 4, 7] 53
1519 2 2 [55, 6, 7] 55
1520 2 2 [58, 4, 3, 5] 58
1521 2 2 [59, 2, 7] 59
1522 2 2 [8, 4, 3, 5] 8
1523 2 2 [9, 2, 7] 9
1524 deallocate prepare stmt;
1525 select /*+ NO_BNL(t1,tt) JOIN_ORDER(t1,tt) */ count(*) from t1 join t1 as tt force index(i1)
1526 where json_overlaps(tt.f1->'$[*]', '[4,3,7]') and t1.id in (1,2) order by t1.id,tt.id;
1527 count(*)
1528 72
1529 explain select /*+ NO_BNL(t1,tt) JOIN_ORDER(t1,tt) */ count(*) from t1 join t1 as tt force index(i1)
1530 where json_overlaps(tt.f1->'$[*]', '[4,3,7]') and t1.id in (1,2) order by t1.id,tt.id;
1531 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1532 1 SIMPLE t1 NULL index PRIMARY PRIMARY 1022 NULL 60 20.00 Using where; Using index
1533 1 SIMPLE tt NULL range i1 i1 9 NULL 54 100.00 Using where
1534 Warnings:
1535 Warning 1739 Cannot use range access on index 'PRIMARY' due to type or collation conversion on field 'id'
1536 Note 1003 /* select#1 */ select /*+ JOIN_ORDER(@`select#1` `t1`,`tt`) NO_BNL(`t1`@`select#1`) NO_BNL(`tt`@`select#1`) */ count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` `tt` FORCE INDEX (`i1`) where (json_overlaps(cast(json_extract(`f1`,_utf8mb4'$[*]') as unsigned array),json'[4, 3, 7]') and (`test`.`t1`.`id` in (1,2)))
1537 prepare stmt from "select /*+ NO_BNL(t1,tt) JOIN_ORDER(t1,tt) */ count(*) from t1 join t1 as tt force index(i1)
1538 where json_overlaps(tt.f1->'$[*]', '[4,3,7]') and t1.id in (1,2) order by t1.id,tt.id";
1539 execute stmt;
1540 count(*)
1541 72
1542 deallocate prepare stmt;
1543 set @@sort_buffer_size= @save_sbs;
1544 set @save_opt_sw= @@optimizer_switch;
1545 select * from t1 force index(i1)
1546 where json_overlaps(f1->"$[*]", "[4,3,7]") order by id;
1547 f1 id
1548 [0, 5, 7] 0
1549 [1, 3] 1
1550 [10, 5, 7] 10
1551 [100, 5, 7] 100
1552 [11, 3] 11
1553 [13, 3, 4, 4, 7] 13
1554 [15, 6, 7] 15
1555 [18, 4, 3, 5] 18
1556 [19, 2, 7] 19
1557 [20, 5, 7] 20
1558 [21, 3] 21
1559 [23, 3, 4, 4, 7] 23
1560 [25, 6, 7] 25
1561 [28, 4, 3, 5] 28
1562 [29, 2, 7] 29
1563 [3, 3, 4, 4, 7] 3
1564 [30, 5, 7] 30
1565 [31, 3] 31
1566 [33, 3, 4, 4, 7] 33
1567 [35, 6, 7] 35
1568 [38, 4, 3, 5] 38
1569 [39, 2, 7] 39
1570 [40, 5, 7] 40
1571 [41, 3] 41
1572 [43, 3, 4, 4, 7] 43
1573 [45, 6, 7] 45
1574 [48, 4, 3, 5] 48
1575 [49, 2, 7] 49
1576 [5, 6, 7] 5
1577 [51, 3] 51
1578 [53, 3, 4, 4, 7] 53
1579 [55, 6, 7] 55
1580 [58, 4, 3, 5] 58
1581 [59, 2, 7] 59
1582 [8, 4, 3, 5] 8
1583 [9, 2, 7] 9
1584 explain select * from t1 force index(i1)
1585 where json_overlaps(f1->"$[*]", "[4,3,7]") order by id;
1586 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1587 1 SIMPLE t1 NULL range i1 i1 9 NULL 54 100.00 Using where; Using filesort
1588 Warnings:
1589 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`id` AS `id` from `test`.`t1` FORCE INDEX (`i1`) where json_overlaps(cast(json_extract(`f1`,_utf8mb4'$[*]') as unsigned array),json'[4, 3, 7]') order by `test`.`t1`.`id`
1590 select count(*) from t1 force index(i1)
1591 where json_overlaps(f1->"$[*]", "[4,3,7]") order by id;
1592 count(*)
1593 36
1594 explain select count(*) from t1 force index(i1)
1595 where json_overlaps(f1->"$[*]", "[4,3,7]") order by id;
1596 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1597 1 SIMPLE t1 NULL range i1 i1 9 NULL 54 100.00 Using where
1598 Warnings:
1599 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` FORCE INDEX (`i1`) where json_overlaps(cast(json_extract(`f1`,_utf8mb4'$[*]') as unsigned array),json'[4, 3, 7]')
1600 drop table t1;
1601 create table t1 (f1 json,
1602 id varchar(255) as (f1->"$[0]") stored not null primary key );
1603 create index i1 on t1((cast(f1->"$[*]" as unsigned array)));
1604 show create table t1;
1605 Table Create Table
1606 t1 CREATE TABLE `t1` (
1607 `f1` json DEFAULT NULL,
1608 `id` varchar(255) GENERATED ALWAYS AS (json_extract(`f1`,_utf8mb4'$[0]')) STORED NOT NULL,
1609 PRIMARY KEY (`id`),
1610 KEY `i1` ((cast(json_extract(`f1`,_utf8mb4'$[*]') as unsigned array)))
1611 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1612 insert into t1(f1) values
1613 (cast('[1,3]' as json)), (cast(2 as json)), (cast('[3,3,4,4,7]' as json)),
1614 (cast(4 as json)), (cast('[0,5,7]' as json)), (cast(6 as json)),
1615 (cast(7 as json)), (cast('[8,4,3,5]' as json)), (cast('[5,6,7]' as json)),
1616 (cast('[9,2,7]' as json));
1617 analyze table t1;
1618 Table Op Msg_type Msg_text
1619 test.t1 analyze status OK
1620 set @@optimizer_switch="mrr_cost_based=off";
1621 select * from t1 force index(i1)
1622 where json_overlaps(f1->"$[*]", "[4,3,7]") order by id;
1623 f1 id
1624 [0, 5, 7] 0
1625 [1, 3] 1
1626 [3, 3, 4, 4, 7] 3
1627 [5, 6, 7] 5
1628 [8, 4, 3, 5] 8
1629 [9, 2, 7] 9
1630 explain select * from t1 force index(i1)
1631 where json_overlaps(f1->"$[*]", "[4,3,7]") order by id;
1632 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1633 1 SIMPLE t1 NULL range i1 i1 9 NULL 9 100.00 Using where; Using MRR; Using filesort
1634 Warnings:
1635 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`id` AS `id` from `test`.`t1` FORCE INDEX (`i1`) where json_overlaps(cast(json_extract(`f1`,_utf8mb4'$[*]') as unsigned array),json'[4, 3, 7]') order by `test`.`t1`.`id`
1636 drop table t1;
1637 create table t(j json, key k ((cast(j->'$[*]' as unsigned array))));
1638 insert into t values('[2,2,3]');
1639 select * from t where 1 member of (j);
1640 j
1641 explain select * from t where 1 member of (j);
1642 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1643 1 SIMPLE t NULL ALL NULL NULL NULL NULL 1 100.00 Using where
1644 Warnings:
1645 Note 1003 /* select#1 */ select `test`.`t`.`j` AS `j` from `test`.`t` where <cache>(1) member of (`test`.`t`.`j`)
1646 drop table t;
1647 create table t1 (j JSON, KEY mv_idx ((CAST(j->'$[*]' AS DATETIME ARRAY))));
1648 insert into t1(j) values("[ 1 ]") ;
1649 ERROR 01000: Data truncated for functional index 'mv_idx' at row 1
1650 drop table t1;
1651 create table t2 (j json default (cast('[9,-1]' as json)),
1652 key mv_idx ((cast(j->'$[*]' as unsigned array))));
1653 insert into t2 values ();
1654 ERROR 22003: Value is out of range for functional index 'mv_idx' at row 1
1655 drop table t2;
1656 create table t1 (j JSON DEFAULT( '["foobar"]' ));
1657 insert into t1 values ();
1658 alter table t1 add index mv_idx((CAST(j->'$[0]' AS UNSIGNED ARRAY)));
1659 ERROR 22018: Invalid JSON value for CAST for functional index 'mv_idx'.
1660 drop table t1;
1661 create table t1 (j JSON, KEY mv_idx ((cast(j->'$[*]' AS BINARY(10) ARRAY))));
1662 insert into t1(j) values('["asdffggasdasdasdasd"]');
1663 ERROR 22001: Data too long for functional index 'mv_idx'.
1664 drop table t1;
1665 CREATE TABLE t1 (j JSON DEFAULT (JSON_ARRAY(PI())),
1666 KEY mv_idx((CAST(j->'$[0]' AS DECIMAL(10,4) ARRAY))));
1667 INSERT INTO t1 VALUES();
1668 Warnings:
1669 Note 3751 Data truncated for functional index 'mv_idx' at row 1
1670 DROP TABLE t1;
1671 create table t1 (j JSON, KEY Mv_idx ((cast(j->'$[*]' AS BINARY(10) ARRAY))) ) ;
1672 insert into t1(j) values('["asdffggasdasdasdasd"]');
1673 ERROR 22001: Data too long for functional index 'Mv_idx'.
1674 create table t2 (j JSON, KEY Mv_idx ((cast(j->'$[*]' AS CHAR(10) ARRAY))) ) ;
1675 insert into t2(j) values('["asdffggasdasdasdasd"]');
1676 ERROR 22001: Data too long for functional index 'Mv_idx'.
1677 drop table t1,t2;
1678 create table t1(j json);
1679 insert into t1 values (json_array(1,2,3));
1680 insert into t1 values (json_array(1,2,3, null));
1681 select * from t1 where json_overlaps(j->'$[*]', '[2,3,4]');
1682 j
1683 [1, 2, 3]
1684 [1, 2, 3, null]
1685 select * from t1 where json_overlaps(j->'$[*]', '[2,3,4, null]');
1686 j
1687 [1, 2, 3]
1688 [1, 2, 3, null]
1689 Lookups of single SON null value can't use index, only table scan
1690 select * from t1 where json_overlaps(j->'$[*]', 'null');
1691 j
1692 [1, 2, 3, null]
1693 explain select * from t1 where json_overlaps(j->'$[*]', 'null');
1694 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1695 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
1696 Warnings:
1697 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where json_overlaps(json_extract(`test`.`t1`.`j`,'$[*]'),<cache>('null'))
1698 select * from t1 where json_overlaps(j->'$[*]', '[null]');
1699 j
1700 [1, 2, 3, null]
1701 explain select * from t1 where json_overlaps(j->'$[*]', '[null]');
1702 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1703 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
1704 Warnings:
1705 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where json_overlaps(json_extract(`test`.`t1`.`j`,'$[*]'),<cache>('[null]'))
1706 select * from t1 where cast('null' as json) member of(j->'$[*]');
1707 j
1708 [1, 2, 3, null]
1709 explain select * from t1 where cast('null' as json) member of(j->'$[*]');
1710 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1711 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
1712 Warnings:
1713 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where <cache>(cast('null' as json)) member of (json_extract(`test`.`t1`.`j`,'$[*]'))
1714 select * from t1 where cast('[null]' as json) member of(j->'$[*]');
1715 j
1716 explain select * from t1 where cast('[null]' as json) member of(j->'$[*]');
1717 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1718 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
1719 Warnings:
1720 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where <cache>(cast('[null]' as json)) member of (json_extract(`test`.`t1`.`j`,'$[*]'))
1721 alter table t1 add key k ((cast(j->'$[*]' as unsigned array)));
1722 ERROR 22018: Invalid JSON value for CAST for functional index 'k'.
1723 delete from t1 where cast('null' as json) member of(j->'$[*]');
1724 alter table t1 add key k ((cast(j->'$[*]' as unsigned array)));
1725 select * from t1 where json_overlaps(j->'$[*]', '[2,3,4, null]');
1726 j
1727 [1, 2, 3]
1728 explain select * from t1 where json_overlaps(j->'$[*]', '[2,3,4, null]');
1729 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1730 1 SIMPLE t1 NULL range k k 9 NULL 3 100.00 Using where; Using MRR
1731 Warnings:
1732 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where json_overlaps(cast(json_extract(`j`,_utf8mb4'$[*]') as unsigned array),json'[2, 3, 4, null]')
1733 select * from t1 where json_overlaps(j->'$[*]', '[2,3,4, "asd"]');
1734 j
1735 [1, 2, 3]
1736 Warnings:
1737 Warning 3903 Invalid JSON value for CAST for functional index 'k'.
1738 explain select * from t1 where json_overlaps(j->'$[*]', '[2,3,4, "asd"]');
1739 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1740 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
1741 Warnings:
1742 Warning 3903 Invalid JSON value for CAST for functional index 'k'.
1743 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where json_overlaps(json_extract(`test`.`t1`.`j`,'$[*]'),<cache>('[2,3,4, "asd"]'))
1744 explain select * from t1 where json_overlaps(j->'$[*]', '{"a":1}');
1745 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1746 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
1747 Warnings:
1748 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where json_overlaps(json_extract(`test`.`t1`.`j`,'$[*]'),<cache>('{"a":1}'))
1749 explain select * from t1 where json_contains(j->'$[*]', '{"a":1}');
1750 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1751 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
1752 Warnings:
1753 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where json_contains(json_extract(`test`.`t1`.`j`,'$[*]'),<cache>('{"a":1}'))
1754 drop table t1;
1755 create table if not exists t1 (
1756 j2 json default (cast('[9,8,0,1]' as json)),
1757 j3 json default (cast('["foobar"]' as json)),
1758 key mv_idx_unsigned (( cast(j2->'$[*]' as unsigned array) ))
1759 );
1760 insert into t1(j3) values(cast('[ 1,2,3,4 ]' as json));
1761 insert into t1(j2, j3) select j2, j3 from t1;
1762 insert into t1(j2, j3) select j2, j3 from t1;
1763 insert into t1(j2, j3) select j2, j3 from t1;
1764 insert into t1(j2, j3) select j2, j3 from t1;
1765 analyze table t1;
1766 Table Op Msg_type Msg_text
1767 test.t1 analyze status OK
1768 explain select count(*) from t1 force index(mv_idx_unsigned);
1769 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1770 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 16 100.00 NULL
1771 Warnings:
1772 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` FORCE INDEX (`mv_idx_unsigned`)
1773 drop table t1;
1774 create table if not exists t1 (
1775 j7 json default (json_array(time('11:11:11'))),
1776 j8 json default (json_array(-1,-2,9)),
1777 key mv_idx_time (( cast(j7->'$[*]' as time array))));
1778 insert into t1() values() ;
1779 select * from t1 where cast('11:11:11' as time) member of (j7->'$[*]');
1780 j7 j8
1781 ["11:11:11.000000"] [-1, -2, 9]
1782 alter table t1 add column ttt int, algorithm = copy;
1783 select * from t1 where cast('11:11:11' as time) member of (j7->'$[*]');
1784 j7 j8 ttt
1785 ["11:11:11.000000"] [-1, -2, 9] NULL
1786 explain select * from t1 where cast('11:11:11' as time) member of (j7->'$[*]');
1787 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1788 1 SIMPLE t1 NULL ref mv_idx_time mv_idx_time 4 const 1 100.00 Using where
1789 Warnings:
1790 Note 1003 /* select#1 */ select `test`.`t1`.`j7` AS `j7`,`test`.`t1`.`j8` AS `j8`,`test`.`t1`.`ttt` AS `ttt` from `test`.`t1` where json'"11:11:11.000000"' member of (cast(json_extract(`j7`,_utf8mb4'$[*]') as time array))
1791 drop table t1;
1792 set @save_mode= @@sql_mode;
1793 set sql_mode='';
1794 create table t1 (j json, key mv_idx_time ((cast(j->'$[*]' as time array))));
1795 insert into t1 values(json_array(cast('2018-01-03' as date),
1796 cast( '2018-01-01' as date)));
1797 Warnings:
1798 Warning 3751 Data truncated for functional index 'mv_idx_time' at row 1
1799 Warning 3751 Data truncated for functional index 'mv_idx_time' at row 1
1800 drop table t1;
1801 create table t(j json, key ((cast(j->'$[*]' as time array))));
1802 insert into t values (json_array(timestamp'2001-01-01 00:00:00',
1803 timestamp'2002-02-02 00:00:02',
1804 timestamp'2003-03-03 00:00:00'));
1805 Warnings:
1806 Note 3751 Data truncated for functional index 'functional_index' at row 1
1807 Note 3751 Data truncated for functional index 'functional_index' at row 1
1808 Note 3751 Data truncated for functional index 'functional_index' at row 1
1809 drop table t;
1810 set @@sql_mode= @save_mode;
1811 #
1812 # Bug#28752637: SIG11 IN JOIN::UPDATE_DEPEND_MAP() AT SQL/SQL_OPTIMIZER.CC
1813 #
1814 CREATE TABLE t1 (
1815 j3 JSON, KEY mv_idx_char (( CAST(j3->'$[*]' AS CHAR(10) ARRAY) )));
1816 INSERT INTO t1 SELECT * FROM t1 WHERE null MEMBER OF ( j3 -> '$[*]' ) ;
1817 Shouldn't use ref access
1818 EXPLAIN SELECT * FROM t1 WHERE null MEMBER OF ( j3 -> '$[*]' ) ;
1819 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1820 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
1821 Warnings:
1822 Note 1003 /* select#1 */ select `test`.`t1`.`j3` AS `j3` from `test`.`t1` where NULL member of (json_extract(`test`.`t1`.`j3`,'$[*]'))
1823 DROP TABLE t1;
1824 #
1825 # Bug#28876519: ASSERT FAILURE:ROW0INS.CC:266:UPDATE->N_FIELDS == 0
1826 #
1827 CREATE TABLE t1 (f1 JSON, UNIQUE KEY i1((CAST(f1->"$[*]" AS CHAR(10) ARRAY))));
1828 INSERT INTO t1(f1) VALUES(CAST('["abc", "abc "]' AS JSON));
1829 SELECT * FROM t1 WHERE "abc" MEMBER OF (f1->"$[*]");
1830 f1
1831 ["abc", "abc "]
1832 EXPLAIN SELECT * FROM t1 WHERE "abc" MEMBER OF (f1->"$[*]");
1833 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1834 1 SIMPLE t1 NULL const i1 i1 43 const 1 100.00 NULL
1835 Warnings:
1836 Note 1003 /* select#1 */ select '["abc", "abc "]' AS `f1` from `test`.`t1` where true
1837 SELECT * FROM t1 WHERE "abc " MEMBER OF (f1->"$[*]");
1838 f1
1839 ["abc", "abc "]
1840 EXPLAIN SELECT * FROM t1 WHERE "abc " MEMBER OF (f1->"$[*]");
1841 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1842 1 SIMPLE t1 NULL const i1 i1 43 const 1 100.00 NULL
1843 Warnings:
1844 Note 1003 /* select#1 */ select '["abc", "abc "]' AS `f1` from `test`.`t1` where true
1845 DELETE FROM t1;
1846 INSERT INTO t1(f1) VALUES(CAST('["abc"]' AS JSON));
1847 INSERT INTO t1(f1) VALUES(CAST('["abc "]' AS JSON));
1848 DROP TABLE t1;
1849 CREATE TABLE t1 (f1 JSON, KEY i1((CAST(f1->"$[*]" AS CHAR(10) ARRAY))));
1850 INSERT INTO t1(f1) VALUES(CAST('["abc"]' AS JSON));
1851 INSERT INTO t1(f1) VALUES(CAST('["abc "]' AS JSON));
1852 SELECT * FROM t1 WHERE "abc" MEMBER OF (f1->"$[*]");
1853 f1
1854 ["abc"]
1855 SELECT * FROM t1 WHERE "abc " MEMBER OF (f1->"$[*]");
1856 f1
1857 ["abc "]
1858 DROP TABLE t1;
1859 CREATE TABLE t1 (f1 JSON, KEY i1((CAST(f1->"$[*]" AS BINARY(10) ARRAY))));
1860 INSERT INTO t1(f1) VALUES(CAST('["abc"]' AS JSON));
1861 INSERT INTO t1(f1) VALUES(CAST('["abc "]' AS JSON));
1862 SELECT * FROM t1 WHERE "abc" MEMBER OF (f1->"$[*]");
1863 f1
1864 ["abc"]
1865 SELECT * FROM t1 WHERE "abc " MEMBER OF (f1->"$[*]");
1866 f1
1867 ["abc "]
1868 DROP TABLE t1;
1869 CREATE TABLE t1 (f1 JSON,
1870 KEY i1((CAST(f1->"$[*]" AS CHAR(10) CHARACTER SET UTF32 ARRAY))));
1871 ERROR 42000: This version of MySQL doesn't yet support 'specifying charset for multi-valued index'
1872 SET NAMES latin1;
1873 CREATE TABLE t1 (f1 JSON, KEY i1((CAST(f1->"$[*]" AS CHAR(10) ARRAY))));
1874 INSERT INTO t1(f1) VALUES(CAST('["abc"]' AS JSON));
1875 INSERT INTO t1(f1) VALUES(CAST('["abc "]' AS JSON));
1876 SELECT * FROM t1 WHERE "abc" MEMBER OF (f1->"$[*]");
1877 f1
1878 ["abc"]
1879 SELECT * FROM t1 WHERE "abc " MEMBER OF (f1->"$[*]");
1880 f1
1881 ["abc "]
1882 DROP TABLE t1;
1883 SET NAMES utf8mb4;
1884 #
1885 # Bug #28893289: DUPLICATE RESULT OF JSON_OVERLAPS() WITH UNIQUE MV INDEX
1886 #
1887 CREATE TABLE t1 (j JSON, UNIQUE KEY mv_idx ((CAST(j->'$[*]' AS SIGNED ARRAY))));
1888 INSERT INTO t1(j) VALUES ('[2,2,2]');
1889 SELECT * FROM t1 WHERE JSON_OVERLAPS(CAST('[2,2]' AS JSON), j->'$[*]');
1890 j
1891 [2, 2, 2]
1892 EXPLAIN SELECT * FROM t1 WHERE JSON_OVERLAPS(CAST('[2,2]' AS JSON), j->'$[*]');
1893 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1894 1 SIMPLE t1 NULL range mv_idx mv_idx 9 NULL 1 100.00 Using where; Using MRR
1895 Warnings:
1896 Note 1003 /* select#1 */ select `test`.`t1`.`j` AS `j` from `test`.`t1` where json_overlaps(json'[2, 2]',cast(json_extract(`j`,_utf8mb4'$[*]') as signed array))
1897 DROP TABLE t1;
1898 #
1899 # Bug#28929529: SERVER CRASH WITH MEMBER OF() FUNCTION
1900 #
1901 CREATE TABLE t1 ( col_int INT) ;
1902 INSERT INTO t1 VALUES (1);
1903 SELECT col_int FROM t1 WHERE col_int MEMBER OF('[98989,67976]');
1904 col_int
1905 EXPLAIN SELECT col_int FROM t1 WHERE col_int MEMBER OF('[98989,67976]');
1906 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1907 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
1908 Warnings:
1909 Note 1003 /* select#1 */ select `test`.`t1`.`col_int` AS `col_int` from `test`.`t1` where `test`.`t1`.`col_int` member of (<cache>('[98989,67976]'))
1910 DROP TABLE t1;
1911 #
1912 # Bug#28935260: SIG11 IN HANDLER::HA_EXTRA() AT SQL/HANDLER.CC
1913 #
1914 CREATE TABLE t1 (
1915 col_key VARCHAR(1) DEFAULT NULL,
1916 col_jsn json DEFAULT NULL,
1917 KEY idx_col_key (col_key),
1918 KEY mv_idx ((CAST(col_jsn->'$[*]' AS UNSIGNED ARRAY))));
1919 INSERT INTO t1 VALUES ('i','[0]'), ('E','[0]'), ('l','[0]');
1920 SELECT col_key FROM t1 WHERE
1921 (JSON_OVERLAPS(CAST(9444 AS JSON),col_jsn->'$[*]')) OR col_key = 't';
1922 col_key
1923 EXPLAIN SELECT col_key FROM t1 WHERE
1924 (JSON_OVERLAPS(CAST(9444 AS JSON),col_jsn->'$[*]')) OR col_key = 't';
1925 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1926 1 SIMPLE t1 NULL index_merge idx_col_key,mv_idx mv_idx,idx_col_key 9,7 NULL 2 100.00 Using sort_union(mv_idx,idx_col_key); Using where
1927 Warnings:
1928 Note 1003 /* select#1 */ select `test`.`t1`.`col_key` AS `col_key` from `test`.`t1` where (json_overlaps(json'[9444]',cast(json_extract(`col_jsn`,_utf8mb4'$[*]') as unsigned array)) or (`test`.`t1`.`col_key` = 't'))
1929 DROP TABLE t1;
1930 #
1931 # Bug#28960833: SIG11 IN FILESORT::MAKE_SORTORDER() AT SQL/FILESORT.CC
1932 #
1933 CREATE TABLE C1 (
1934 pk int(11) NOT NULL AUTO_INCREMENT KEY,
1935 col_int int(11) DEFAULT NULL) ;
1936 Warnings:
1937 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1938 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1939 INSERT INTO C1 VALUES (1,2);
1940 SELECT SUM( DISTINCT table1.col_int ) AS field1,
1941 MAX( table1.col_int ) AS field3
1942 FROM C1 AS table1 LEFT JOIN C1 AS table2 ON 1
1943 WHERE CAST(RAND()*100 AS JSON) MEMBER OF( 'sTfW' )
1944 GROUP BY table1.pk ORDER BY field1, field3 ;
1945 ERROR 22032: Invalid JSON text in argument 1 to function member of: "Invalid value." at position 0.
1946 DROP TABLE C1;
1947 #
1948 # Bug#28959908: REPEATED EXECUTION OF SELECT RETURNS DIFFERENT RESULT
1949 #
1950 CREATE TABLE t1 (
1951 col_int_key int(11) DEFAULT NULL,
1952 col_jsonn json DEFAULT NULL,
1953 KEY mv_idx ((cast(col_jsonn->'$[*]' as char(40) array)))
1954 );
1955 Warnings:
1956 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1957 INSERT INTO t1 VALUES (NULL,'[1]'), (4,'[1]'), (1,'[2]');
1958 CREATE TABLE t2(col_int int(11));
1959 Warnings:
1960 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1961 INSERT INTO t2 VALUES (1), (2), (3), (11), (12);
1962 ANALYZE TABLE t1,t2;
1963 Table Op Msg_type Msg_text
1964 test.t1 analyze status OK
1965 test.t2 analyze status OK
1966 EXPLAIN SELECT t1.col_int_key AS field1, t2.col_int AS field2 FROM t2 LEFT
1967 JOIN t1 ON 1 WHERE (CAST("1" AS JSON) MEMBER OF( t1.col_jsonn->'$[*]'));
1968 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1969 1 SIMPLE t1 NULL ref mv_idx mv_idx 163 const 2 100.00 Using where
1970 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 100.00 Using join buffer (hash join)
1971 Warnings:
1972 Note 1003 /* select#1 */ select `test`.`t1`.`col_int_key` AS `field1`,`test`.`t2`.`col_int` AS `field2` from `test`.`t2` join `test`.`t1` where json'"1"' member of (cast(json_extract(`col_jsonn`,_utf8mb4'$[*]') as char(40) array))
1973 SELECT t1.col_int_key AS field1, t2.col_int AS field2 FROM t2 LEFT
1974 JOIN t1 ON 1 WHERE (CAST("1" AS JSON) MEMBER OF( t1.col_jsonn->'$[*]'));
1975 field1 field2
1976 4 1
1977 4 11
1978 4 12
1979 4 2
1980 4 3
1981 NULL 1
1982 NULL 11
1983 NULL 12
1984 NULL 2
1985 NULL 3
1986 DROP TABLE t1,t2;
1987 #
1988 # Bug#29111067: SIG6 IN HANDLER::HA_EXTRA() AT SQL/HANDLER.CC
1989 #
1990 CREATE TABLE c1 (
1991 col_int int(11) DEFAULT NULL,
1992 col_jsonn json DEFAULT NULL,
1993 KEY mv_idx_char
1994 ((cast(json_extract(`col_jsonn`,_utf8mb4'$[2][*]') as char(10) array))),
1995 KEY mv_idx_time
1996 ((cast(json_extract(`col_jsonn`,_utf8mb4'$[6][*]') as time array)))
1997 );
1998 Warnings:
1999 Warning 1681 Integer display width is deprecated and will be removed in a future release.
2000 INSERT INTO c1 VALUES (266852963,'[[1835802859, 1212466474, 1958770846],
2001 [140577309, 37575131, 1480381571, 1610621995], ["6PivylM", "D4MJJ1N", "M0KX",
2002 "0ELw", "sIgqeN", "HHOzYx8vo", "u7htig76J0", "SVd7N"], [1, 0, 0, 1],
2003 ["2027-03-28 09:30:48", "1981-12-19 07:23:59"], ["1992-02-21"], ["04:08:11",
2004 "03:14:43", "16:15:47", "08:35:02", "01:54:19", "12:23:50", "23:19:54",
2005 "03:19:05", "06:20:09"]]'),(827337874,'[[-289358475], [1365589563,
2006 1136730961, 1355533008, 623792249, 1492351200], ["quU8k89", "b", "5",
2007 "qUCBav5wm", "4qw", "48esnGIpJz", "OHFde8", "MvWemE3ON", "kfWRfhif"], [0],
2008 ["2029-08-21 11:07:49", "2010-12-30 13:23:27", "1982-08-13 20:37:26"],
2009 ["2003-08-19", "1996-06-18", "2010-12-29", "2023-02-26", "1994-03-09"],
2010 ["23:05:01"]]'),(1335355510,'[[-565849481, -24516610, -2120954195,
2011 1161359986, -1413651589], [], ["G", "G", "G", "2h", "AjfPYjosm", "gq", "qwX",
2012 "iap94E", "S", "BHLb3XfcD", "kyJSC"], [1, 1, 1, 1, 1, 1],
2013 ["2021-11-20 06:14:11"], ["1998-01-19", "2025-01-28", "2036-12-07",
2014 "1992-01-10",
2015 "1992-02-13", "2000-10-31", "1976-10-16", "1988-01-15"], ["07:24:55",
2016 "18:26:39", "01:03:31", "10:01:50", "10:32:40"]]'),(717723030,'[[721357711,
2017 443763172, 1346794776, 1228470359, 810183154, 1544293472, 182301524],
2018 [1274610824, 1044052482, 1788179665, 1649506330, 969248599, 1368415902,
2019 15095787], ["65h", "zCOtWhY", "vtIr", "9", "b7vckqds", "3RGQx", "jW1",
2020 "r0CqU"], [1, 0, 1, 1, 1], ["2010-02-23 02:29:37", "2011-07-17 16:44:14",
2021 "1987-08-17 03:47:04", "2012-05-24 09:09:40"], ["1997-01-12", "1992-10-26",
2022 "1975-05-29", "1988-05-02", "1984-07-24", "2001-06-21"], ["01:16:30",
2023 "12:10:12", "16:52:37", "11:15:48"]]'),(-1455798357,'[[1160950873,
2024 -1845559138, -591619735], [1947296343, 1928779367, 2046082843, 1714804343,
2025 1903096605, 230458028], ["8j8RNBGsci", "QWU2x", "b4a7", "uIcTSlagrr",
2026 "rpCiT2w"], [0, 1, 1], ["1971-10-25 17:55:12", "2021-04-07 00:09:33",
2027 "2015-06-11 05:08:07", "1981-08-25 16:50:38", "2035-10-19 05:23:43"],
2028 ["2016-03-08", "2034-05-14", "2026-11-18", "1988-12-08", "2027-03-26",
2029 "2003-05-09"], ["01:45:07", "08:13:35",
2030 "22:51:45"]]'),(-205841342,'[[-286383407, -2132945193, -1440807096,
2031 712069046], [329306555, 927040824, 397088016, 951650479, 1320369164,
2032 2135904858], ["d0", "lG2", "uY", "6K9"], [1, 0, 0, 0, 1, 1],
2033 ["1980-03-14 17:42:17", "2009-05-12 20:16:27", "1991-10-09 13:20:26",
2034 "1973-08-03 01:28:46", "2027-02-28 14:26:25", "2014-07-24 05:46:40",
2035 "2035-04-30 15:17:48"], [], ["06:32:03"]]');
2036 analyze table c1;
2037 Table Op Msg_type Msg_text
2038 test.c1 analyze status OK
2039 SELECT col_int FROM c1 WHERE
2040 (JSON_CONTAINS( col_jsonn-> '$[2][*]' , JSON_ARRAY( '2030-11-16' ) ) ) OR
2041 JSON_OVERLAPS(JSON_ARRAY('12:38:26', '17:42:23'), col_jsonn->'$[6][*]');
2042 col_int
2043 EXPLAIN SELECT col_int FROM c1 WHERE
2044 (JSON_CONTAINS( col_jsonn-> '$[2][*]' , JSON_ARRAY( '2030-11-16' ) ) ) OR
2045 JSON_OVERLAPS(JSON_ARRAY('12:38:26', '17:42:23'), col_jsonn->'$[6][*]');
2046 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2047 1 SIMPLE c1 NULL index_merge mv_idx_char,mv_idx_time mv_idx_char,mv_idx_time 43,4 NULL 3 100.00 Using sort_union(mv_idx_char,mv_idx_time); Using where
2048 Warnings:
2049 Note 1003 /* select#1 */ select `test`.`c1`.`col_int` AS `col_int` from `test`.`c1` where (json_contains(cast(json_extract(`col_jsonn`,_utf8mb4'$[2][*]') as char(10) array),json'["2030-11-16"]') or json_overlaps(json'["12:38:26.000000", "17:42:23.000000"]',cast(json_extract(`col_jsonn`,_utf8mb4'$[6][*]') as time array)))
2050 DROP TABLE c1;
2051 #
2052 # Bug#29114081: ERROR WHILE READING TABLE
2053 #
2054 CREATE TABLE a (pk integer auto_increment key, col_varchar_key varchar(1));
2055 CREATE TABLE c (pk integer auto_increment key, col_varchar varchar(1) ,
2056 col_varchar_key varchar(1));
2057 ALTER TABLE a ADD COLUMN col_jsonn JSON;
2058 ALTER TABLE a
2059 ADD INDEX mv_idx_binary ((CAST(col_jsonn->'$[*]' AS BINARY(10) ARRAY)));
2060 ALTER TABLE c ADD COLUMN col_jsonn JSON;
2061 ALTER TABLE c
2062 ADD INDEX mv_idx_binary ((CAST(col_jsonn->'$[*]' AS BINARY(10) ARRAY)));
2063 insert into c values (DEFAULT,'8','q','[0,0,1,1,0,1]'),
2064 (DEFAULT,'T','N','[1,0,0,0,0,1,0]'), (DEFAULT,'H','p','[0,0,1]'),
2065 (DEFAULT,'Z','t','[1]'), (DEFAULT,'T','v','[0]'), (DEFAULT,'m','N','[1,1,0]'),
2066 (DEFAULT,'4','o','[1,1,0,1]'), (DEFAULT,'1','5','[1,0,1,0,0,0]');
2067 analyze table c;
2068 Table Op Msg_type Msg_text
2069 test.c analyze status OK
2070 SELECT alias1 . pk AS field1 , alias2 . pk AS field3
2071 FROM ( c AS alias1 LEFT JOIN c AS alias2
2072 ON ( alias1 . col_varchar = alias2 . col_varchar_key ) )
2073 WHERE (
2074 EXISTS ( ( SELECT sq2_alias2 . col_varchar_key AS sq2_field1
2075 FROM a AS sq2_alias2
2076 WHERE JSON_OVERLAPS(CAST('TOoqLsu' AS JSON),sq2_alias2.col_jsonn->'$[*]'))))
2077 AND ( alias1 . pk = alias2 . pk AND alias1 . pk = 8 );
2078 ERROR 22032: Invalid JSON text in argument 1 to function cast_as_json: "Invalid value." at position 0.
2079 SELECT alias1 . pk AS field1 , alias2 . pk AS field3
2080 FROM ( c AS alias1 LEFT JOIN c AS alias2
2081 ON ( alias1 . col_varchar = alias2 . col_varchar_key ) )
2082 WHERE (
2083 EXISTS ( ( SELECT sq2_alias2 . col_varchar_key AS sq2_field1
2084 FROM a AS sq2_alias2
2085 WHERE JSON_OVERLAPS(CAST('"TOoqLsu"' AS JSON),sq2_alias2.col_jsonn->'$[*]'))))
2086 AND ( alias1 . pk = alias2 . pk AND alias1 . pk = 8 );
2087 field1 field3
2088 EXPLAIN SELECT alias1 . pk AS field1 , alias2 . pk AS field3
2089 FROM ( c AS alias1 LEFT JOIN c AS alias2
2090 ON ( alias1 . col_varchar = alias2 . col_varchar_key ) )
2091 WHERE (
2092 EXISTS ( ( SELECT sq2_alias2 . col_varchar_key AS sq2_field1
2093 FROM a AS sq2_alias2
2094 WHERE JSON_OVERLAPS(CAST('"TOoqLsu"' AS JSON),sq2_alias2.col_jsonn->'$[*]'))))
2095 AND ( alias1 . pk = alias2 . pk AND alias1 . pk = 8 );
2096 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2097 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2098 Warnings:
2099 Note 1003 /* select#1 */ select '8' AS `field1`,'8' AS `field3` from `test`.`c` `alias1` join `test`.`c` `alias2` semi join (`test`.`a` `sq2_alias2`) where (json_overlaps(json'["base64:type15:VE9vcUxzdQ=="]',cast(json_extract(`col_jsonn`,_utf8mb4'$[*]') as binary(10) array)) and multiple equal(8) and multiple equal('1'))
2100 DROP TABLE a,c;
2101 #
2102 # Bug#29303026: TOO MANY RESULTS FROM UNIQUE MULTI-VALUE INDEX
2103 #
2104 create table t (j json, unique key ((cast(j->'$[*]' as char(10) array))));
2105 insert into t values ('[1,"1"]');
2106 select * from t where json_overlaps('["1",1]', j->'$[*]');
2107 j
2108 [1, "1"]
2109 explain select * from t where json_overlaps('["1",1]', j->'$[*]');
2110 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2111 1 SIMPLE t NULL range functional_index functional_index 43 NULL 1 100.00 Using where; Using MRR
2112 Warnings:
2113 Note 1003 /* select#1 */ select `test`.`t`.`j` AS `j` from `test`.`t` where json_overlaps(json'["1", "1"]',cast(json_extract(`j`,_utf8mb4'$[*]') as char(10) array))
2114 drop table t;
2115 #
2116 # Bug#29582655: ASSERTION FAILURE: `!THD->IS_ERROR()'
2117 #
2118 CREATE TABLE t1 (pk INT primary key, col_jsonn JSON, INDEX
2119 mv_idx((CAST(col_jsonn->'$[0][*]' AS BINARY(10) ARRAY))));
2120 CREATE TABLE t2 (pk INT primary key, col_jsonn JSON);
2121 SELECT JSON_OVERLAPS( '0' , t1. col_jsonn-> '$[0][*]' ) FROM t1 LEFT JOIN t2
2122 ON t1.pk = JSON_OVERLAPS( 'OJNRaUr' , JSON_ARRAY( 'Q' , 'qX' ) ) WHERE t2 .
2123 pk = 1;
2124 ERROR 22032: Invalid JSON text in argument 1 to function json_overlaps: "Invalid value." at position 0.
2125 DROP TABLE t1,t2;
2126 #
2127 # Bug#29752056: COLLECTION.FIND() FAILS FOR DATE TYPE WHEN IN IS USED
2128 #
2129 CREATE TABLE t1 (doc JSON,_id VARBINARY(32)
2130 GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) STORED PRIMARY
2131 KEY);
2132 ALTER TABLE t1 ADD INDEX dateArrayIndex
2133 ((CAST(JSON_EXTRACT(doc,'$.dateField') AS DATE ARRAY)));
2134 ALTER TABLE t1 ADD INDEX datetimeArrayIndex
2135 ((CAST(JSON_EXTRACT(doc,'$.datetimeField') AS DATETIME ARRAY)));
2136 ALTER TABLE t1 ADD INDEX timeArrayIndex
2137 ((CAST(JSON_EXTRACT(doc,'$.timeField') AS TIME ARRAY)));
2138 INSERT INTO t1 (doc) VALUES
2139 ('{"dateField":["2019-1-1","2019-2-1","2019-3-1"],
2140 "datetimeField":["2019-12-29 23:59:59","2019-12-30 23:59:59",
2141 "2019-12-31 23:59:59"],
2142 "timeField":["10:10","11:10","12:10"],
2143 "_id": "00005cd4075c0000000000000087"}'),
2144 ('{"dateField":["2019-1-2","2019-2-2","2019-3-2"],
2145 "datetimeField":["2018-12-29 23:59:59","2018-12-30 23:59:59",
2146 "2018-12-31 23:59:59"],
2147 "timeField":["10:11","11:11","12:11"],
2148 "_id": "00005cd4075c0000000000000088"}'),
2149 ('{"dateField":["2019-1-3","2019-2-3","2019-3-3"],
2150 "datetimeField":["2017-12-29 23:59:59","2017-12-30 23:59:59",
2151 "2017-12-31 23:59:59"],
2152 "timeField":["10:12","11:12","12:12"],
2153 "_id": "00005cd4075c0000000000000089"}');
2154 ANALYZE TABLE t1;
2155 Table Op Msg_type Msg_text
2156 test.t1 analyze status OK
2157 # DATE
2158 SELECT doc->'$.dateField' FROM t1 WHERE
2159 JSON_CONTAINS(doc->'$.dateField',JSON_QUOTE('2019-2-1'));
2160 doc->'$.dateField'
2161 ["2019-1-1", "2019-2-1", "2019-3-1"]
2162 SELECT doc->'$.dateField' FROM t1 IGNORE KEY(dateArrayIndex) WHERE
2163 JSON_CONTAINS(doc->'$.dateField',JSON_QUOTE('2019-2-1'));
2164 doc->'$.dateField'
2165 ["2019-1-1", "2019-2-1", "2019-3-1"]
2166 # Empty result as $.dateField is string and index isn't used
2167 SELECT doc->'$.dateField' FROM t1 IGNORE KEY(dateArrayIndex) WHERE
2168 JSON_CONTAINS(doc->'$.dateField', CAST(CAST('2019-2-1' AS DATE) AS JSON));
2169 doc->'$.dateField'
2170 EXPLAIN SELECT doc->'$.dateField' FROM t1 WHERE
2171 JSON_CONTAINS(doc->'$.dateField',JSON_QUOTE('2019-2-1'));
2172 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2173 1 SIMPLE t1 NULL range dateArrayIndex dateArrayIndex 4 NULL 1 100.00 Using where; Using MRR
2174 Warnings:
2175 Note 1003 /* select#1 */ select json_extract(`test`.`t1`.`doc`,'$.dateField') AS `doc->'$.dateField'` from `test`.`t1` where json_contains(cast(json_extract(`doc`,_utf8mb4'$.dateField') as date array),json'["2019-02-01"]')
2176 SELECT doc->'$.dateField' FROM t1 WHERE
2177 JSON_CONTAINS(doc->'$.dateField',JSON_ARRAY('2019-2-1','2019-3-1'));
2178 doc->'$.dateField'
2179 ["2019-1-1", "2019-2-1", "2019-3-1"]
2180 EXPLAIN SELECT doc->'$.dateField' FROM t1 WHERE
2181 JSON_CONTAINS(doc->'$.dateField',JSON_ARRAY('2019-2-1','2019-3-1'));
2182 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2183 1 SIMPLE t1 NULL range dateArrayIndex dateArrayIndex 4 NULL 2 100.00 Using where; Using MRR
2184 Warnings:
2185 Note 1003 /* select#1 */ select json_extract(`test`.`t1`.`doc`,'$.dateField') AS `doc->'$.dateField'` from `test`.`t1` where json_contains(cast(json_extract(`doc`,_utf8mb4'$.dateField') as date array),json'["2019-02-01", "2019-03-01"]')
2186 SELECT doc->'$.dateField' FROM t1 WHERE
2187 '2019-2-1' MEMBER OF (doc->'$.dateField');
2188 doc->'$.dateField'
2189 ["2019-1-1", "2019-2-1", "2019-3-1"]
2190 EXPLAIN SELECT doc->'$.dateField' FROM t1 WHERE
2191 '2019-2-1' MEMBER OF (doc->'$.dateField');
2192 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2193 1 SIMPLE t1 NULL ref dateArrayIndex dateArrayIndex 4 const 1 100.00 Using where
2194 Warnings:
2195 Note 1003 /* select#1 */ select json_extract(`test`.`t1`.`doc`,'$.dateField') AS `doc->'$.dateField'` from `test`.`t1` where json'"2019-02-01"' member of (cast(json_extract(`doc`,_utf8mb4'$.dateField') as date array))
2196 SELECT doc->'$.dateField' FROM t1 WHERE
2197 DATE'2019-2-1' MEMBER OF (doc->'$.dateField');
2198 doc->'$.dateField'
2199 ["2019-1-1", "2019-2-1", "2019-3-1"]
2200 EXPLAIN SELECT doc->'$.dateField' FROM t1 WHERE
2201 DATE'2019-2-1' MEMBER OF (doc->'$.dateField');
2202 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2203 1 SIMPLE t1 NULL ref dateArrayIndex dateArrayIndex 4 const 1 100.00 Using where
2204 Warnings:
2205 Note 1003 /* select#1 */ select json_extract(`test`.`t1`.`doc`,'$.dateField') AS `doc->'$.dateField'` from `test`.`t1` where json'"2019-02-01"' member of (cast(json_extract(`doc`,_utf8mb4'$.dateField') as date array))
2206 SELECT doc->'$.dateField' FROM t1 WHERE
2207 'asd2019' MEMBER OF (doc->'$.dateField');
2208 doc->'$.dateField'
2209 Warnings:
2210 Warning 3751 Data truncated for functional index 'dateArrayIndex' at row 1
2211 EXPLAIN SELECT doc->'$.dateField' FROM t1 WHERE
2212 'asd2019' MEMBER OF (doc->'$.dateField');
2213 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2214 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
2215 Warnings:
2216 Warning 3751 Data truncated for functional index 'dateArrayIndex' at row 1
2217 Note 1003 /* select#1 */ select json_extract(`test`.`t1`.`doc`,'$.dateField') AS `doc->'$.dateField'` from `test`.`t1` where <cache>('asd2019') member of (json_extract(`test`.`t1`.`doc`,'$.dateField'))
2218 # DATETIME
2219 SELECT doc->'$.datetimeField' FROM t1 WHERE
2220 JSON_CONTAINS(doc->'$.datetimeField',JSON_QUOTE('2017-12-29 23:59:59'));
2221 doc->'$.datetimeField'
2222 ["2017-12-29 23:59:59", "2017-12-30 23:59:59", "2017-12-31 23:59:59"]
2223 SELECT doc->'$.datetimeField' FROM t1 IGNORE KEY(datetimeArrayIndex) WHERE
2224 JSON_CONTAINS(doc->'$.datetimeField',JSON_QUOTE('2017-12-29 23:59:59'));
2225 doc->'$.datetimeField'
2226 ["2017-12-29 23:59:59", "2017-12-30 23:59:59", "2017-12-31 23:59:59"]
2227 # Empty result as $.datetimeField is string and index isn't used
2228 SELECT doc->'$.datetimeField' FROM t1 IGNORE KEY(datetimeArrayIndex) WHERE
2229 JSON_CONTAINS(doc->'$.datetimeField',
2230 CAST(CAST('2017-12-29 23:59:59' AS DATETIME) AS JSON));
2231 doc->'$.datetimeField'
2232 EXPLAIN SELECT doc->'$.datetimeField' FROM t1 WHERE
2233 JSON_CONTAINS(doc->'$.datetimeField',JSON_QUOTE('2017-12-29 23:59:59'));
2234 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2235 1 SIMPLE t1 NULL range datetimeArrayIndex datetimeArrayIndex 6 NULL 1 100.00 Using where; Using MRR
2236 Warnings:
2237 Note 1003 /* select#1 */ select json_extract(`test`.`t1`.`doc`,'$.datetimeField') AS `doc->'$.datetimeField'` from `test`.`t1` where json_contains(cast(json_extract(`doc`,_utf8mb4'$.datetimeField') as datetime array),json'["2017-12-29 23:59:59.000000"]')
2238 SELECT doc->'$.datetimeField' FROM t1 WHERE
2239 JSON_CONTAINS(doc->'$.datetimeField',
2240 JSON_ARRAY('2017-12-29 23:59:59','2017-12-30 23:59:59'));
2241 doc->'$.datetimeField'
2242 ["2017-12-29 23:59:59", "2017-12-30 23:59:59", "2017-12-31 23:59:59"]
2243 EXPLAIN SELECT doc->'$.datetimeField' FROM t1 WHERE
2244 JSON_CONTAINS(doc->'$.datetimeField',
2245 JSON_ARRAY('2017-12-29 23:59:59','2017-12-30 23:59:59'));
2246 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2247 1 SIMPLE t1 NULL range datetimeArrayIndex datetimeArrayIndex 6 NULL 2 100.00 Using where; Using MRR
2248 Warnings:
2249 Note 1003 /* select#1 */ select json_extract(`test`.`t1`.`doc`,'$.datetimeField') AS `doc->'$.datetimeField'` from `test`.`t1` where json_contains(cast(json_extract(`doc`,_utf8mb4'$.datetimeField') as datetime array),json'["2017-12-29 23:59:59.000000", "2017-12-30 23:59:59.000000"]')
2250 SELECT doc->'$.datetimeField' FROM t1 WHERE
2251 '2017-12-29 23:59:59' MEMBER OF (doc->'$.datetimeField');
2252 doc->'$.datetimeField'
2253 ["2017-12-29 23:59:59", "2017-12-30 23:59:59", "2017-12-31 23:59:59"]
2254 EXPLAIN SELECT doc->'$.datetimeField' FROM t1 WHERE
2255 '2017-12-29 23:59:59' MEMBER OF (doc->'$.datetimeField');
2256 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2257 1 SIMPLE t1 NULL ref datetimeArrayIndex datetimeArrayIndex 6 const 1 100.00 Using where
2258 Warnings:
2259 Note 1003 /* select#1 */ select json_extract(`test`.`t1`.`doc`,'$.datetimeField') AS `doc->'$.datetimeField'` from `test`.`t1` where json'"2017-12-29 23:59:59.000000"' member of (cast(json_extract(`doc`,_utf8mb4'$.datetimeField') as datetime array))
2260 SELECT doc->'$.datetimeField' FROM t1 WHERE
2261 TIMESTAMP'2017-12-29 23:59:59' MEMBER OF (doc->'$.datetimeField');
2262 doc->'$.datetimeField'
2263 ["2017-12-29 23:59:59", "2017-12-30 23:59:59", "2017-12-31 23:59:59"]
2264 EXPLAIN SELECT doc->'$.datetimeField' FROM t1 WHERE
2265 TIMESTAMP'2017-12-29 23:59:59' MEMBER OF (doc->'$.datetimeField');
2266 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2267 1 SIMPLE t1 NULL ref datetimeArrayIndex datetimeArrayIndex 6 const 1 100.00 Using where
2268 Warnings:
2269 Note 1003 /* select#1 */ select json_extract(`test`.`t1`.`doc`,'$.datetimeField') AS `doc->'$.datetimeField'` from `test`.`t1` where json'"2017-12-29 23:59:59.000000"' member of (cast(json_extract(`doc`,_utf8mb4'$.datetimeField') as datetime array))
2270 # TIME
2271 SELECT doc->'$.timeField' FROM t1 WHERE
2272 JSON_CONTAINS(doc->'$.timeField',JSON_QUOTE('11:11'));
2273 doc->'$.timeField'
2274 ["10:11", "11:11", "12:11"]
2275 SELECT doc->'$.timeField' FROM t1 IGNORE KEY(timeArrayIndex) WHERE
2276 JSON_CONTAINS(doc->'$.timeField',JSON_QUOTE('11:11'));
2277 doc->'$.timeField'
2278 ["10:11", "11:11", "12:11"]
2279 # Empty result as $.timeField is string and index isn't used
2280 SELECT doc->'$.timeField' FROM t1 IGNORE KEY(timeArrayIndex) WHERE
2281 JSON_CONTAINS(doc->'$.timeField', CAST(CAST('11:11' AS TIME) AS JSON));
2282 doc->'$.timeField'
2283 EXPLAIN SELECT doc->'$.timeField' FROM t1 WHERE
2284 JSON_CONTAINS(doc->'$.timeField',JSON_QUOTE('11:11'));
2285 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2286 1 SIMPLE t1 NULL range timeArrayIndex timeArrayIndex 4 NULL 1 100.00 Using where; Using MRR
2287 Warnings:
2288 Note 1003 /* select#1 */ select json_extract(`test`.`t1`.`doc`,'$.timeField') AS `doc->'$.timeField'` from `test`.`t1` where json_contains(cast(json_extract(`doc`,_utf8mb4'$.timeField') as time array),json'["11:11:00.000000"]')
2289 SELECT doc->'$.timeField' FROM t1 WHERE
2290 JSON_CONTAINS(doc->'$.timeField',JSON_ARRAY('11:11','12:11'));
2291 doc->'$.timeField'
2292 ["10:11", "11:11", "12:11"]
2293 EXPLAIN SELECT doc->'$.timeField' FROM t1 WHERE
2294 JSON_CONTAINS(doc->'$.timeField',JSON_ARRAY('11:11','12:11'));
2295 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2296 1 SIMPLE t1 NULL range timeArrayIndex timeArrayIndex 4 NULL 2 100.00 Using where; Using MRR
2297 Warnings:
2298 Note 1003 /* select#1 */ select json_extract(`test`.`t1`.`doc`,'$.timeField') AS `doc->'$.timeField'` from `test`.`t1` where json_contains(cast(json_extract(`doc`,_utf8mb4'$.timeField') as time array),json'["11:11:00.000000", "12:11:00.000000"]')
2299 SELECT doc->'$.timeField' FROM t1 WHERE
2300 '11:11' MEMBER OF (doc->'$.timeField');
2301 doc->'$.timeField'
2302 ["10:11", "11:11", "12:11"]
2303 EXPLAIN SELECT doc->'$.timeField' FROM t1 WHERE
2304 '11:11' MEMBER OF (doc->'$.timeField');
2305 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2306 1 SIMPLE t1 NULL ref timeArrayIndex timeArrayIndex 4 const 1 100.00 Using where
2307 Warnings:
2308 Note 1003 /* select#1 */ select json_extract(`test`.`t1`.`doc`,'$.timeField') AS `doc->'$.timeField'` from `test`.`t1` where json'"11:11:00.000000"' member of (cast(json_extract(`doc`,_utf8mb4'$.timeField') as time array))
2309 SELECT doc->'$.timeField' FROM t1 WHERE
2310 TIME'11:11' MEMBER OF (doc->'$.timeField');
2311 doc->'$.timeField'
2312 ["10:11", "11:11", "12:11"]
2313 EXPLAIN SELECT doc->'$.timeField' FROM t1 WHERE
2314 TIME'11:11' MEMBER OF (doc->'$.timeField');
2315 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2316 1 SIMPLE t1 NULL ref timeArrayIndex timeArrayIndex 4 const 1 100.00 Using where
2317 Warnings:
2318 Note 1003 /* select#1 */ select json_extract(`test`.`t1`.`doc`,'$.timeField') AS `doc->'$.timeField'` from `test`.`t1` where json'"11:11:00.000000"' member of (cast(json_extract(`doc`,_utf8mb4'$.timeField') as time array))
2319 DROP TABLE t1;
2320 CREATE TABLE t(i INT, j JSON);
2321 INSERT INTO t VALUES
2322 (1,'"2019-1-1"'),(2,'"2019-01-01"'),(3,CAST(DATE'2019-1-1' AS JSON));
2323 # Untyped comparison
2324 SELECT * FROM t WHERE j->'$' = '2019-1-1';
2325 i j
2326 1 "2019-1-1"
2327 SELECT * FROM t WHERE j->'$' = DATE'2019-01-01';
2328 i j
2329 3 "2019-01-01"
2330 SELECT * FROM t WHERE j->'$' = '2019-01-1';
2331 i j
2332 CREATE INDEX idx ON t((CAST(j->'$' AS DATE)));
2333 # Typed comparison using index's type
2334 SELECT * FROM t WHERE j->'$' = '2019-1-1';
2335 i j
2336 1 "2019-1-1"
2337 2 "2019-01-01"
2338 3 "2019-01-01"
2339 SELECT * FROM t WHERE j->'$' = DATE'2019-01-01';
2340 i j
2341 1 "2019-1-1"
2342 2 "2019-01-01"
2343 3 "2019-01-01"
2344 SELECT * FROM t WHERE j->'$' = '2019-01-1';
2345 i j
2346 1 "2019-1-1"
2347 2 "2019-01-01"
2348 3 "2019-01-01"
2349 ALTER TABLE t DROP INDEX idx;
2350 DELETE FROM t;
2351 INSERT INTO t VALUES
2352 (1,'"1:1"'),(2,'"01:1"'),(3,CAST(TIME'01:1' AS JSON));
2353 SELECT * FROM t WHERE j->'$' = '1:1';
2354 i j
2355 1 "1:1"
2356 SELECT * FROM t WHERE j->'$' = TIME'1:1';
2357 i j
2358 3 "01:01:00.000000"
2359 SELECT * FROM t WHERE j->'$' = '1:01';
2360 i j
2361 CREATE INDEX idx ON t((CAST(j->'$' AS TIME)));
2362 # Typed comparison using index's type
2363 SELECT * FROM t WHERE j->'$' = '1:1';
2364 i j
2365 1 "1:1"
2366 2 "01:1"
2367 3 "01:01:00.000000"
2368 SELECT * FROM t WHERE j->'$' = TIME'1:1';
2369 i j
2370 1 "1:1"
2371 2 "01:1"
2372 3 "01:01:00.000000"
2373 SELECT * FROM t WHERE j->'$' = '1:01';
2374 i j
2375 1 "1:1"
2376 2 "01:1"
2377 3 "01:01:00.000000"
2378 DROP TABLE t;
2379 #
2380 # Bug#30227756: JSON.JSON_PERFSCHEMA FAILS ON WINDOWS
2381 #
2382 CREATE TABLE t1 (
2383 j JSON DEFAULT (CAST('[9,8,0,1]' AS JSON)),
2384 KEY mv_idx ((CAST(j->'$[*]' AS UNSIGNED ARRAY))));
2385 INSERT INTO t1 VALUES();
2386 DROP TABLE t1;
2387 SELECT * FROM performance_schema.memory_summary_global_by_event_name
2388 WHERE event_name = 'memory/sql/JSON' AND
2389 (count_alloc <> count_free OR
2390 sum_number_of_bytes_alloc <> sum_number_of_bytes_free);
2391 EVENT_NAME COUNT_ALLOC COUNT_FREE SUM_NUMBER_OF_BYTES_ALLOC SUM_NUMBER_OF_BYTES_FREE LOW_COUNT_USED CURRENT_COUNT_USED HIGH_COUNT_USED LOW_NUMBER_OF_BYTES_USED CURRENT_NUMBER_OF_BYTES_USED HIGH_NUMBER_OF_BYTES_USED
2392 #
2393 # Bug#31301101 ASSERTION ".CONV_FIELD->HAS_CHARSET"
2394 #
2395 CREATE TABLE t1(j char(1), KEY idxbn ((CAST('' AS CHAR(1) ARRAY))));
2396 ALTER TABLE t1 CONVERT TO CHARACTER SET latin1;
2397 DROP TABLE t1;
2398 #
2399 # Bug#30838807: MULTI-VALUED INDEX ON JSON COLUMN IS NOT USED
2400 #
2401 CREATE TABLE t(vc VARCHAR(10), j JSON DEFAULT (vc),
2402 KEY j_idx ((CAST(j AS UNSIGNED ARRAY))),
2403 KEY vc_idx ((CAST(vc AS UNSIGNED ARRAY))));
2404 INSERT INTO t(vc) VALUES ('[1,2,3]'), ('[2,3,4]'), ('[3,4,5]'), ('[4,5,6]');
2405 ANALYZE TABLE t;
2406 Table Op Msg_type Msg_text
2407 test.t analyze status OK
2408 # Multi-valued index on a JSON column.
2409 EXPLAIN FORMAT=TREE SELECT * FROM t WHERE JSON_OVERLAPS('[2,3]', j);
2410 EXPLAIN
2411 -> Filter: json_overlaps(json'[2, 3]',cast(j as unsigned array)) (cost=2.07 rows=5)
2412 -> Index range scan on t using j_idx over (3 MEMBER OF (j)) OR (2 MEMBER OF (j)) (cost=2.07 rows=5)
2413
2414 SELECT * FROM t WHERE JSON_OVERLAPS('[2,3]', j);
2415 vc j
2416 [1,2,3] [1, 2, 3]
2417 [2,3,4] [2, 3, 4]
2418 [3,4,5] [3, 4, 5]
2419 EXPLAIN FORMAT=TREE SELECT * FROM t WHERE JSON_CONTAINS(j, '[2,3]');
2420 EXPLAIN
2421 -> Filter: json_contains(cast(j as unsigned array),json'[2, 3]') (cost=2.07 rows=5)
2422 -> Index range scan on t using j_idx over (3 MEMBER OF (j)) OR (2 MEMBER OF (j)) (cost=2.07 rows=5)
2423
2424 SELECT * FROM t WHERE JSON_CONTAINS(j, '[2,3]');
2425 vc j
2426 [1,2,3] [1, 2, 3]
2427 [2,3,4] [2, 3, 4]
2428 EXPLAIN FORMAT=TREE SELECT * FROM t WHERE 3 MEMBER OF (j);
2429 EXPLAIN
2430 -> Filter: json'3' member of (cast(j as unsigned array)) (cost=0.8 rows=3)
2431 -> Index lookup on t using j_idx (cast(j as unsigned array)=json'3') (cost=0.8 rows=3)
2432
2433 SELECT * FROM t WHERE 3 MEMBER OF (j);
2434 vc j
2435 [1,2,3] [1, 2, 3]
2436 [2,3,4] [2, 3, 4]
2437 [3,4,5] [3, 4, 5]
2438 # Multi-valued index on a VARCHAR column.
2439 EXPLAIN FORMAT=TREE SELECT * FROM t WHERE JSON_OVERLAPS('[2,3]', vc);
2440 EXPLAIN
2441 -> Filter: json_overlaps(json'[2, 3]',cast(vc as unsigned array)) (cost=2.07 rows=5)
2442 -> Index range scan on t using vc_idx over (3 MEMBER OF (vc)) OR (2 MEMBER OF (vc)) (cost=2.07 rows=5)
2443
2444 SELECT * FROM t WHERE JSON_OVERLAPS('[2,3]', vc);
2445 vc j
2446 [1,2,3] [1, 2, 3]
2447 [2,3,4] [2, 3, 4]
2448 [3,4,5] [3, 4, 5]
2449 EXPLAIN FORMAT=TREE SELECT * FROM t WHERE JSON_CONTAINS(vc, '[2,3]');
2450 EXPLAIN
2451 -> Filter: json_contains(cast(vc as unsigned array),json'[2, 3]') (cost=2.07 rows=5)
2452 -> Index range scan on t using vc_idx over (3 MEMBER OF (vc)) OR (2 MEMBER OF (vc)) (cost=2.07 rows=5)
2453
2454 SELECT * FROM t WHERE JSON_CONTAINS(vc, '[2,3]');
2455 vc j
2456 [1,2,3] [1, 2, 3]
2457 [2,3,4] [2, 3, 4]
2458 EXPLAIN FORMAT=TREE SELECT * FROM t WHERE 3 MEMBER OF (vc);
2459 EXPLAIN
2460 -> Filter: json'3' member of (cast(vc as unsigned array)) (cost=0.8 rows=3)
2461 -> Index lookup on t using vc_idx (cast(vc as unsigned array)=json'3') (cost=0.8 rows=3)
2462
2463 SELECT * FROM t WHERE 3 MEMBER OF (vc);
2464 vc j
2465 [1,2,3] [1, 2, 3]
2466 [2,3,4] [2, 3, 4]
2467 [3,4,5] [3, 4, 5]
2468 DROP TABLE t;
2469 # Bug#33275457: Fix multi-valued index
2470 CREATE TABLE t1 (
2471 f1 VARCHAR(50) NOT NULL PRIMARY KEY,
2472 f2 JSON NOT NULL,
2473 INDEX idx2 ( (CAST(f2 AS CHAR(50) ARRAY)) )
2474 );
2475 CREATE VIEW v1 AS
2476 SELECT *
2477 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
2478 PATH '$')) AS ids;
2479 INSERT INTO t1 VALUES ('foo', '["aa", "bb"]'), ('bar', '["xx", "yy"]');
2480 ANALYZE TABLE t1;
2481 Table Op Msg_type Msg_text
2482 test.t1 analyze status OK
2483 EXPLAIN SELECT * FROM t1 WHERE 'xx' MEMBER OF (f2);
2484 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2485 1 SIMPLE t1 NULL ref idx2 idx2 203 const 1 100.00 Using where
2486 Warnings:
2487 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where json'"xx"' member of (cast(`f2` as char(50) array))
2488 EXPLAIN SELECT * FROM t1 WHERE json_contains(f2, '"xx"');
2489 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2490 1 SIMPLE t1 NULL range idx2 idx2 203 NULL 1 100.00 Using where; Using MRR
2491 Warnings:
2492 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where json_contains(cast(`f2` as char(50) array),json'["xx"]')
2493 EXPLAIN SELECT * FROM t1 WHERE json_overlaps(f2, '["xx", "zz"]');
2494 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2495 1 SIMPLE t1 NULL range idx2 idx2 203 NULL 2 100.00 Using where; Using MRR
2496 Warnings:
2497 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where json_overlaps(cast(`f2` as char(50) array),json'["xx", "zz"]')
2498 EXPLAIN SELECT * FROM v1 WHERE 'xx' MEMBER OF (f2);
2499 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2500 1 SIMPLE t1 NULL ref idx2 idx2 203 const 1 100.00 Using where
2501 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
2502 Warnings:
2503 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where json'"xx"' member of (cast(`f2` as char(50) array))
2504 EXPLAIN SELECT * FROM v1 WHERE json_contains(f2, '"xx"');
2505 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2506 1 SIMPLE t1 NULL range idx2 idx2 203 NULL 1 100.00 Using where; Using MRR
2507 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
2508 Warnings:
2509 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where json_contains(cast(`f2` as char(50) array),json'["xx"]')
2510 EXPLAIN SELECT * FROM v1 WHERE json_overlaps(f2, '["xx", "zz"]');
2511 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2512 1 SIMPLE t1 NULL range idx2 idx2 203 NULL 2 100.00 Using where; Using MRR
2513 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
2514 Warnings:
2515 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where json_overlaps(cast(`f2` as char(50) array),json'["xx", "zz"]')
2516 EXPLAIN
2517 SELECT *
2518 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
2519 PATH '$')) AS ids
2520 WHERE 'xx' MEMBER OF (f2);
2521 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2522 1 SIMPLE t1 NULL ref idx2 idx2 203 const 1 100.00 Using where
2523 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
2524 Warnings:
2525 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where json'"xx"' member of (cast(`f2` as char(50) array))
2526 EXPLAIN
2527 SELECT *
2528 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
2529 PATH '$')) AS ids
2530 WHERE json_contains(f2, '"xx"');
2531 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2532 1 SIMPLE t1 NULL range idx2 idx2 203 NULL 1 100.00 Using where; Using MRR
2533 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
2534 Warnings:
2535 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where json_contains(cast(`f2` as char(50) array),json'["xx"]')
2536 EXPLAIN
2537 SELECT *
2538 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
2539 PATH '$')) AS ids
2540 WHERE json_overlaps(f2, '["xx", "zz"]');
2541 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2542 1 SIMPLE t1 NULL range idx2 idx2 203 NULL 2 100.00 Using where; Using MRR
2543 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
2544 Warnings:
2545 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where json_overlaps(cast(`f2` as char(50) array),json'["xx", "zz"]')
2546 SELECT * FROM t1 WHERE 'xx' MEMBER OF (f2);
2547 f1 f2
2548 bar ["xx", "yy"]
2549 SELECT * FROM t1 WHERE json_contains(f2, '"xx"');
2550 f1 f2
2551 bar ["xx", "yy"]
2552 SELECT * FROM t1 WHERE json_overlaps(f2, '["xx", "zz"]');
2553 f1 f2
2554 bar ["xx", "yy"]
2555 SELECT * FROM v1 WHERE 'xx' MEMBER OF (f2);
2556 f1 f2 i id
2557 bar ["xx", "yy"] 1 xx
2558 bar ["xx", "yy"] 2 yy
2559 SELECT * FROM v1 WHERE json_contains(f2, '"xx"');
2560 f1 f2 i id
2561 bar ["xx", "yy"] 1 xx
2562 bar ["xx", "yy"] 2 yy
2563 SELECT * FROM v1 WHERE json_overlaps(f2, '["xx", "zz"]');
2564 f1 f2 i id
2565 bar ["xx", "yy"] 1 xx
2566 bar ["xx", "yy"] 2 yy
2567 SELECT *
2568 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
2569 PATH '$')) AS ids
2570 WHERE 'xx' MEMBER OF (f2);
2571 f1 f2 i id
2572 bar ["xx", "yy"] 1 xx
2573 bar ["xx", "yy"] 2 yy
2574 SELECT *
2575 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
2576 PATH '$')) AS ids
2577 WHERE json_contains(f2, '"xx"');
2578 f1 f2 i id
2579 bar ["xx", "yy"] 1 xx
2580 bar ["xx", "yy"] 2 yy
2581 SELECT *
2582 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
2583 PATH '$')) AS ids
2584 WHERE json_overlaps(f2, '["xx", "zz"]');
2585 f1 f2 i id
2586 bar ["xx", "yy"] 1 xx
2587 bar ["xx", "yy"] 2 yy
2588 PREPARE stmt FROM 'EXPLAIN SELECT * FROM t1 WHERE ? MEMBER OF (f2)';
2589 SET @a='xx';
2590 EXECUTE stmt USING @a;
2591 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2592 1 SIMPLE t1 NULL ref idx2 idx2 203 const 1 100.00 Using where
2593 Warnings:
2594 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where json'"xx"' member of (cast(`f2` as char(50) array))
2595 PREPARE stmt FROM 'EXPLAIN SELECT * FROM t1 WHERE json_contains(f2, ?)';
2596 SET @a='"xx"';
2597 EXECUTE stmt USING @a;
2598 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2599 1 SIMPLE t1 NULL range idx2 idx2 203 NULL 1 100.00 Using where; Using MRR
2600 Warnings:
2601 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where json_contains(cast(`f2` as char(50) array),json'["xx"]')
2602 PREPARE stmt FROM 'EXPLAIN SELECT * FROM t1 WHERE json_overlaps(f2, ?)';
2603 SET @a='["xx", "cc"]';
2604 EXECUTE stmt USING @a;
2605 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2606 1 SIMPLE t1 NULL range idx2 idx2 203 NULL 2 100.00 Using where; Using MRR
2607 Warnings:
2608 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where json_overlaps(cast(`f2` as char(50) array),json'["xx", "cc"]')
2609 PREPARE stmt FROM 'EXPLAIN SELECT * FROM v1 WHERE ? MEMBER OF (f2)';
2610 SET @a='xx';
2611 EXECUTE stmt USING @a;
2612 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2613 1 SIMPLE t1 NULL ref idx2 idx2 203 const 1 100.00 Using where
2614 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
2615 Warnings:
2616 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where json'"xx"' member of (cast(`f2` as char(50) array))
2617 PREPARE stmt FROM 'EXPLAIN SELECT * FROM v1 WHERE json_contains(f2, ?)';
2618 SET @a='"xx"';
2619 EXECUTE stmt USING @a;
2620 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2621 1 SIMPLE t1 NULL range idx2 idx2 203 NULL 1 100.00 Using where; Using MRR
2622 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
2623 Warnings:
2624 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where json_contains(cast(`f2` as char(50) array),json'["xx"]')
2625 PREPARE stmt FROM 'EXPLAIN SELECT * FROM v1 WHERE json_overlaps(f2, ?)';
2626 SET @a='["xx", "cc"]';
2627 EXECUTE stmt USING @a;
2628 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2629 1 SIMPLE t1 NULL range idx2 idx2 203 NULL 2 100.00 Using where; Using MRR
2630 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
2631 Warnings:
2632 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where json_overlaps(cast(`f2` as char(50) array),json'["xx", "cc"]')
2633 PREPARE stmt FROM '
2634 EXPLAIN
2635 SELECT *
2636 FROM t1, JSON_TABLE(f2, \'$[*]\'
2637 COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH \'$\')) AS ids
2638 WHERE ? MEMBER OF (f2)';
2639 SET @a='xx';
2640 EXECUTE stmt USING @a;
2641 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2642 1 SIMPLE t1 NULL ref idx2 idx2 203 const 1 100.00 Using where
2643 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
2644 Warnings:
2645 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where json'"xx"' member of (cast(`f2` as char(50) array))
2646 PREPARE stmt FROM '
2647 EXPLAIN
2648 SELECT *
2649 FROM t1, JSON_TABLE(f2, \'$[*]\'
2650 COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH \'$\')) AS ids
2651 WHERE json_contains(f2, ?)';
2652 SET @a='"xx"';
2653 EXECUTE stmt USING @a;
2654 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2655 1 SIMPLE t1 NULL range idx2 idx2 203 NULL 1 100.00 Using where; Using MRR
2656 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
2657 Warnings:
2658 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where json_contains(cast(`f2` as char(50) array),json'["xx"]')
2659 PREPARE stmt FROM '
2660 EXPLAIN
2661 SELECT *
2662 FROM t1, JSON_TABLE(f2, \'$[*]\'
2663 COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH \'$\')) AS ids
2664 WHERE json_overlaps(f2, ?)';
2665 SET @a='["xx", "cc"]';
2666 EXECUTE stmt USING @a;
2667 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2668 1 SIMPLE t1 NULL range idx2 idx2 203 NULL 2 100.00 Using where; Using MRR
2669 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
2670 Warnings:
2671 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where json_overlaps(cast(`f2` as char(50) array),json'["xx", "cc"]')
2672 PREPARE stmt FROM 'SELECT * FROM t1 WHERE ? MEMBER OF (f2)';
2673 SET @a='xx';
2674 EXECUTE stmt USING @a;
2675 f1 f2
2676 bar ["xx", "yy"]
2677 PREPARE stmt FROM 'SELECT * FROM t1 WHERE json_contains(f2, ?)';
2678 SET @a='"xx"';
2679 EXECUTE stmt USING @a;
2680 f1 f2
2681 bar ["xx", "yy"]
2682 PREPARE stmt FROM 'SELECT * FROM t1 WHERE json_overlaps(f2, ?)';
2683 SET @a='["xx", "cc"]';
2684 EXECUTE stmt USING @a;
2685 f1 f2
2686 bar ["xx", "yy"]
2687 PREPARE stmt FROM 'SELECT * FROM v1 WHERE ? MEMBER OF (f2)';
2688 SET @a='xx';
2689 EXECUTE stmt USING @a;
2690 f1 f2 i id
2691 bar ["xx", "yy"] 1 xx
2692 bar ["xx", "yy"] 2 yy
2693 PREPARE stmt FROM 'SELECT * FROM v1 WHERE json_contains(f2, ?)';
2694 SET @a='"xx"';
2695 EXECUTE stmt USING @a;
2696 f1 f2 i id
2697 bar ["xx", "yy"] 1 xx
2698 bar ["xx", "yy"] 2 yy
2699 PREPARE stmt FROM 'SELECT * FROM v1 WHERE json_overlaps(f2, ?)';
2700 SET @a='["xx", "cc"]';
2701 EXECUTE stmt USING @a;
2702 f1 f2 i id
2703 bar ["xx", "yy"] 1 xx
2704 bar ["xx", "yy"] 2 yy
2705 PREPARE stmt FROM '
2706 SELECT *
2707 FROM t1, JSON_TABLE(f2, \'$[*]\'
2708 COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH \'$\')) AS ids
2709 WHERE ? MEMBER OF (f2)';
2710 SET @a='xx';
2711 EXECUTE stmt USING @a;
2712 f1 f2 i id
2713 bar ["xx", "yy"] 1 xx
2714 bar ["xx", "yy"] 2 yy
2715 PREPARE stmt FROM '
2716 SELECT *
2717 FROM t1, JSON_TABLE(f2, \'$[*]\'
2718 COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH \'$\')) AS ids
2719 WHERE json_contains(f2, ?)';
2720 SET @a='"xx"';
2721 EXECUTE stmt USING @a;
2722 f1 f2 i id
2723 bar ["xx", "yy"] 1 xx
2724 bar ["xx", "yy"] 2 yy
2725 PREPARE stmt FROM '
2726 SELECT *
2727 FROM t1, JSON_TABLE(f2, \'$[*]\'
2728 COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH \'$\')) AS ids
2729 WHERE json_overlaps(f2, ?)';
2730 SET @a='["xx", "cc"]';
2731 EXECUTE stmt USING @a;
2732 f1 f2 i id
2733 bar ["xx", "yy"] 1 xx
2734 bar ["xx", "yy"] 2 yy
2735 EXPLAIN SELECT * FROM t1 WHERE 'xx' MEMBER OF (f2) AND 'yy' MEMBER OF (f2);
2736 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2737 1 SIMPLE t1 NULL ref idx2 idx2 203 const 1 100.00 Using where
2738 Warnings:
2739 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where (json'"xx"' member of (cast(`f2` as char(50) array)) and json'"yy"' member of (cast(`f2` as char(50) array)))
2740 EXPLAIN
2741 SELECT * FROM t1 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"');
2742 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2743 1 SIMPLE t1 NULL range idx2 idx2 203 NULL 1 100.00 Using where; Using MRR
2744 Warnings:
2745 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where (json_contains(cast(`f2` as char(50) array),json'["xx"]') and json_contains(cast(`f2` as char(50) array),json'["yy"]'))
2746 EXPLAIN
2747 SELECT *
2748 FROM t1
2749 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]');
2750 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2751 1 SIMPLE t1 NULL range idx2 idx2 203 NULL 2 100.00 Using where; Using MRR
2752 Warnings:
2753 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where (json_overlaps(cast(`f2` as char(50) array),json'["xx", "zz"]') and json_overlaps(cast(`f2` as char(50) array),json'["yy", "zz"]'))
2754 EXPLAIN SELECT * FROM v1 WHERE 'xx' MEMBER OF (f2) AND 'yy' MEMBER OF (f2);
2755 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2756 1 SIMPLE t1 NULL ref idx2 idx2 203 const 1 100.00 Using where
2757 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
2758 Warnings:
2759 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where (json'"xx"' member of (cast(`f2` as char(50) array)) and json'"yy"' member of (cast(`f2` as char(50) array)))
2760 EXPLAIN
2761 SELECT * FROM v1 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"');
2762 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2763 1 SIMPLE t1 NULL range idx2 idx2 203 NULL 1 100.00 Using where; Using MRR
2764 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
2765 Warnings:
2766 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where (json_contains(cast(`f2` as char(50) array),json'["xx"]') and json_contains(cast(`f2` as char(50) array),json'["yy"]'))
2767 EXPLAIN
2768 SELECT *
2769 FROM v1
2770 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]');
2771 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2772 1 SIMPLE t1 NULL range idx2 idx2 203 NULL 2 100.00 Using where; Using MRR
2773 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
2774 Warnings:
2775 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where (json_overlaps(cast(`f2` as char(50) array),json'["xx", "zz"]') and json_overlaps(cast(`f2` as char(50) array),json'["yy", "zz"]'))
2776 EXPLAIN
2777 SELECT *
2778 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
2779 PATH '$')) AS ids
2780 WHERE 'xx' MEMBER OF (f2) AND 'yy' MEMBER OF (f2);
2781 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2782 1 SIMPLE t1 NULL ref idx2 idx2 203 const 1 100.00 Using where
2783 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
2784 Warnings:
2785 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where (json'"xx"' member of (cast(`f2` as char(50) array)) and json'"yy"' member of (cast(`f2` as char(50) array)))
2786 EXPLAIN
2787 SELECT *
2788 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
2789 PATH '$')) AS ids
2790 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"');
2791 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2792 1 SIMPLE t1 NULL range idx2 idx2 203 NULL 1 100.00 Using where; Using MRR
2793 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
2794 Warnings:
2795 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where (json_contains(cast(`f2` as char(50) array),json'["xx"]') and json_contains(cast(`f2` as char(50) array),json'["yy"]'))
2796 EXPLAIN
2797 SELECT *
2798 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
2799 PATH '$')) AS ids
2800 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]');
2801 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2802 1 SIMPLE t1 NULL range idx2 idx2 203 NULL 2 100.00 Using where; Using MRR
2803 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
2804 Warnings:
2805 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where (json_overlaps(cast(`f2` as char(50) array),json'["xx", "zz"]') and json_overlaps(cast(`f2` as char(50) array),json'["yy", "zz"]'))
2806 EXPLAIN
2807 SELECT *
2808 FROM t1
2809 WHERE 'xx' MEMBER OF (f2) AND 'yy' MEMBER OF(f2) AND f1 = 'bar';
2810 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2811 1 SIMPLE t1 NULL const PRIMARY,idx2 PRIMARY 202 const 1 100.00 NULL
2812 Warnings:
2813 Note 1003 /* select#1 */ select 'bar' AS `f1`,'["xx", "yy"]' AS `f2` from `test`.`t1` where true
2814 EXPLAIN
2815 SELECT *
2816 FROM t1
2817 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"') AND f1 = 'bar';
2818 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2819 1 SIMPLE t1 NULL const PRIMARY,idx2 PRIMARY 202 const 1 100.00 NULL
2820 Warnings:
2821 Note 1003 /* select#1 */ select 'bar' AS `f1`,'["xx", "yy"]' AS `f2` from `test`.`t1` where true
2822 EXPLAIN
2823 SELECT *
2824 FROM t1
2825 WHERE json_overlaps(f2, '["xx", "zz"]') AND
2826 json_overlaps(f2, '["yy", "zz"]') AND
2827 f1 = 'bar';
2828 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2829 1 SIMPLE t1 NULL const PRIMARY,idx2 PRIMARY 202 const 1 100.00 NULL
2830 Warnings:
2831 Note 1003 /* select#1 */ select 'bar' AS `f1`,'["xx", "yy"]' AS `f2` from `test`.`t1` where true
2832 EXPLAIN
2833 SELECT *
2834 FROM v1
2835 WHERE 'xx' MEMBER OF (f2) AND 'yy' MEMBER OF(f2) AND f1 = 'bar';
2836 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2837 1 SIMPLE t1 NULL const PRIMARY,idx2 PRIMARY 202 const 1 100.00 NULL
2838 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
2839 Warnings:
2840 Note 1003 /* select#1 */ select 'bar' AS `f1`,'["xx", "yy"]' AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table('["xx", "yy"]', '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where (json'"xx"' member of (cast('["xx", "yy"]' as char(50) array)) and json'"yy"' member of (cast('["xx", "yy"]' as char(50) array)))
2841 EXPLAIN
2842 SELECT *
2843 FROM v1
2844 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"') AND f1 = 'bar';
2845 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2846 1 SIMPLE t1 NULL const PRIMARY,idx2 PRIMARY 202 const 1 100.00 NULL
2847 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
2848 Warnings:
2849 Note 1003 /* select#1 */ select 'bar' AS `f1`,'["xx", "yy"]' AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table('["xx", "yy"]', '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where (json_contains(cast('["xx", "yy"]' as char(50) array),json'["xx"]') and json_contains(cast('["xx", "yy"]' as char(50) array),json'["yy"]'))
2850 EXPLAIN
2851 SELECT *
2852 FROM v1
2853 WHERE json_overlaps(f2, '["xx", "zz"]') AND
2854 json_overlaps(f2, '["yy", "zz"]') AND
2855 f1 = 'bar';
2856 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2857 1 SIMPLE t1 NULL const PRIMARY,idx2 PRIMARY 202 const 1 100.00 NULL
2858 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
2859 Warnings:
2860 Note 1003 /* select#1 */ select 'bar' AS `f1`,'["xx", "yy"]' AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table('["xx", "yy"]', '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where (json_overlaps(cast('["xx", "yy"]' as char(50) array),json'["xx", "zz"]') and json_overlaps(cast('["xx", "yy"]' as char(50) array),json'["yy", "zz"]'))
2861 EXPLAIN
2862 SELECT *
2863 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
2864 PATH '$')) AS ids
2865 WHERE 'xx' MEMBER OF (f2) AND 'yy' MEMBER OF (f2) AND f1 = 'bar';
2866 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2867 1 SIMPLE t1 NULL const PRIMARY,idx2 PRIMARY 202 const 1 100.00 NULL
2868 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
2869 Warnings:
2870 Note 1003 /* select#1 */ select 'bar' AS `f1`,'["xx", "yy"]' AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table('["xx", "yy"]', '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where (json'"xx"' member of (cast('["xx", "yy"]' as char(50) array)) and json'"yy"' member of (cast('["xx", "yy"]' as char(50) array)))
2871 EXPLAIN
2872 SELECT *
2873 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
2874 PATH '$')) AS ids
2875 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"') AND f1 = 'bar';
2876 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2877 1 SIMPLE t1 NULL const PRIMARY,idx2 PRIMARY 202 const 1 100.00 NULL
2878 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
2879 Warnings:
2880 Note 1003 /* select#1 */ select 'bar' AS `f1`,'["xx", "yy"]' AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table('["xx", "yy"]', '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where (json_contains(cast('["xx", "yy"]' as char(50) array),json'["xx"]') and json_contains(cast('["xx", "yy"]' as char(50) array),json'["yy"]'))
2881 EXPLAIN
2882 SELECT *
2883 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
2884 PATH '$')) AS ids
2885 WHERE json_overlaps(f2, '["xx", "zz"]') AND
2886 json_overlaps(f2, '["yy", "zz"]') AND
2887 f1 = 'bar';
2888 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2889 1 SIMPLE t1 NULL const PRIMARY,idx2 PRIMARY 202 const 1 100.00 NULL
2890 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
2891 Warnings:
2892 Note 1003 /* select#1 */ select 'bar' AS `f1`,'["xx", "yy"]' AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table('["xx", "yy"]', '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where (json_overlaps(cast('["xx", "yy"]' as char(50) array),json'["xx", "zz"]') and json_overlaps(cast('["xx", "yy"]' as char(50) array),json'["yy", "zz"]'))
2893 SELECT * FROM t1 WHERE 'xx' MEMBER OF (f2) AND 'yy' MEMBER OF (f2);
2894 f1 f2
2895 bar ["xx", "yy"]
2896 SELECT * FROM t1 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"');
2897 f1 f2
2898 bar ["xx", "yy"]
2899 SELECT *
2900 FROM t1
2901 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]');
2902 f1 f2
2903 bar ["xx", "yy"]
2904 SELECT * FROM v1 WHERE 'xx' MEMBER OF (f2) AND 'yy' MEMBER OF (f2);
2905 f1 f2 i id
2906 bar ["xx", "yy"] 1 xx
2907 bar ["xx", "yy"] 2 yy
2908 SELECT * FROM v1 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"');
2909 f1 f2 i id
2910 bar ["xx", "yy"] 1 xx
2911 bar ["xx", "yy"] 2 yy
2912 SELECT *
2913 FROM v1
2914 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]');
2915 f1 f2 i id
2916 bar ["xx", "yy"] 1 xx
2917 bar ["xx", "yy"] 2 yy
2918 SELECT *
2919 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
2920 PATH '$')) AS ids
2921 WHERE 'xx' MEMBER OF (f2) AND 'yy' MEMBER OF (f2);
2922 f1 f2 i id
2923 bar ["xx", "yy"] 1 xx
2924 bar ["xx", "yy"] 2 yy
2925 SELECT *
2926 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
2927 PATH '$')) AS ids
2928 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"');
2929 f1 f2 i id
2930 bar ["xx", "yy"] 1 xx
2931 bar ["xx", "yy"] 2 yy
2932 SELECT *
2933 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
2934 PATH '$')) AS ids
2935 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]');
2936 f1 f2 i id
2937 bar ["xx", "yy"] 1 xx
2938 bar ["xx", "yy"] 2 yy
2939 SELECT *
2940 FROM t1
2941 WHERE 'xx' MEMBER OF (f2) AND 'yy' MEMBER OF (f2) AND f1 = 'bar';
2942 f1 f2
2943 bar ["xx", "yy"]
2944 SELECT *
2945 FROM t1
2946 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"') AND f1 = 'bar';
2947 f1 f2
2948 bar ["xx", "yy"]
2949 SELECT *
2950 FROM t1
2951 WHERE json_overlaps(f2, '["xx", "zz"]') AND
2952 json_overlaps(f2, '["yy", "zz"]') AND
2953 f1 = 'bar';
2954 f1 f2
2955 bar ["xx", "yy"]
2956 SELECT *
2957 FROM v1
2958 WHERE 'xx' MEMBER OF (f2) AND 'yy' MEMBER OF (f2) AND f1 = 'bar';
2959 f1 f2 i id
2960 bar ["xx", "yy"] 1 xx
2961 bar ["xx", "yy"] 2 yy
2962 SELECT *
2963 FROM v1
2964 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"') AND f1 = 'bar';
2965 f1 f2 i id
2966 bar ["xx", "yy"] 1 xx
2967 bar ["xx", "yy"] 2 yy
2968 SELECT *
2969 FROM v1
2970 WHERE json_overlaps(f2, '["xx", "zz"]') AND
2971 json_overlaps(f2, '["yy", "zz"]') AND
2972 f1 = 'bar';
2973 f1 f2 i id
2974 bar ["xx", "yy"] 1 xx
2975 bar ["xx", "yy"] 2 yy
2976 SELECT *
2977 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
2978 PATH '$')) AS ids
2979 WHERE 'xx' MEMBER OF (f2) AND 'yy' MEMBER OF(f2) AND f1 = 'bar';
2980 f1 f2 i id
2981 bar ["xx", "yy"] 1 xx
2982 bar ["xx", "yy"] 2 yy
2983 SELECT *
2984 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
2985 PATH '$')) AS ids
2986 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"') AND f1 = 'bar';
2987 f1 f2 i id
2988 bar ["xx", "yy"] 1 xx
2989 bar ["xx", "yy"] 2 yy
2990 SELECT *
2991 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
2992 PATH '$')) AS ids
2993 WHERE json_overlaps(f2, '["xx", "zz"]') AND
2994 json_overlaps(f2, '["yy", "zz"]') AND
2995 f1 = 'bar';
2996 f1 f2 i id
2997 bar ["xx", "yy"] 1 xx
2998 bar ["xx", "yy"] 2 yy
2999 EXPLAIN SELECT * FROM t1 WHERE 'xx' MEMBER OF (f2) OR 'zz' MEMBER OF (f2);
3000 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3001 1 SIMPLE t1 NULL range idx2 idx2 203 NULL 2 100.00 Using where; Using MRR
3002 Warnings:
3003 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where (json'"xx"' member of (cast(`f2` as char(50) array)) or json'"zz"' member of (cast(`f2` as char(50) array)))
3004 EXPLAIN SELECT * FROM t1 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"');
3005 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3006 1 SIMPLE t1 NULL range idx2 idx2 203 NULL 2 100.00 Using where; Using MRR
3007 Warnings:
3008 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where (json_contains(cast(`f2` as char(50) array),json'["xx"]') or json_contains(cast(`f2` as char(50) array),json'["zz"]'))
3009 EXPLAIN SELECT * FROM t1 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]');
3010 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3011 1 SIMPLE t1 NULL range idx2 idx2 203 NULL 3 100.00 Using where; Using MRR
3012 Warnings:
3013 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where (json_overlaps(cast(`f2` as char(50) array),json'["xx", "zz"]') or json_overlaps(cast(`f2` as char(50) array),json'["zz"]'))
3014 EXPLAIN SELECT * FROM v1 WHERE 'xx' MEMBER OF (f2) OR 'zz' MEMBER OF (f2);
3015 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3016 1 SIMPLE t1 NULL range idx2 idx2 203 NULL 2 100.00 Using where; Using MRR
3017 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
3018 Warnings:
3019 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where (json'"xx"' member of (cast(`f2` as char(50) array)) or json'"zz"' member of (cast(`f2` as char(50) array)))
3020 EXPLAIN SELECT * FROM v1 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"');
3021 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3022 1 SIMPLE t1 NULL range idx2 idx2 203 NULL 2 100.00 Using where; Using MRR
3023 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
3024 Warnings:
3025 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where (json_contains(cast(`f2` as char(50) array),json'["xx"]') or json_contains(cast(`f2` as char(50) array),json'["zz"]'))
3026 EXPLAIN
3027 SELECT *
3028 FROM v1
3029 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]');
3030 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3031 1 SIMPLE t1 NULL range idx2 idx2 203 NULL 3 100.00 Using where; Using MRR
3032 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
3033 Warnings:
3034 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where (json_overlaps(cast(`f2` as char(50) array),json'["xx", "zz"]') or json_overlaps(cast(`f2` as char(50) array),json'["zz"]'))
3035 EXPLAIN
3036 SELECT *
3037 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
3038 PATH '$')) AS ids
3039 WHERE 'xx' MEMBER OF (f2) OR 'zz' MEMBER OF (f2);
3040 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3041 1 SIMPLE t1 NULL range idx2 idx2 203 NULL 2 100.00 Using where; Using MRR
3042 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
3043 Warnings:
3044 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where (json'"xx"' member of (cast(`f2` as char(50) array)) or json'"zz"' member of (cast(`f2` as char(50) array)))
3045 EXPLAIN
3046 SELECT *
3047 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
3048 PATH '$')) AS ids
3049 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"');
3050 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3051 1 SIMPLE t1 NULL range idx2 idx2 203 NULL 2 100.00 Using where; Using MRR
3052 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
3053 Warnings:
3054 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where (json_contains(cast(`f2` as char(50) array),json'["xx"]') or json_contains(cast(`f2` as char(50) array),json'["zz"]'))
3055 EXPLAIN
3056 SELECT *
3057 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
3058 PATH '$')) AS ids
3059 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]');
3060 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3061 1 SIMPLE t1 NULL range idx2 idx2 203 NULL 3 100.00 Using where; Using MRR
3062 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
3063 Warnings:
3064 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where (json_overlaps(cast(`f2` as char(50) array),json'["xx", "zz"]') or json_overlaps(cast(`f2` as char(50) array),json'["zz"]'))
3065 INSERT INTO t1 VALUES
3066 ('k1', '["v1"]'), ('k2', '["v2"]'), ('k3', '["v3"]'),
3067 ('k4', '["v4"]'), ('k5', '["v5"]'), ('k6', '["v6"]'),
3068 ('k7', '["v7"]'), ('k8', '["v8"]'), ('k9', '["v9"]');
3069 ANALYZE TABLE t1;
3070 Table Op Msg_type Msg_text
3071 test.t1 analyze status OK
3072 EXPLAIN
3073 SELECT *
3074 FROM t1
3075 WHERE 'xx' MEMBER OF (f2) OR 'zz' MEMBER OF (f2) OR f1 = 'bar';
3076 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3077 1 SIMPLE t1 NULL index_merge PRIMARY,idx2 idx2,PRIMARY 203,202 NULL 3 100.00 Using sort_union(idx2,PRIMARY); Using where
3078 Warnings:
3079 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where (json'"xx"' member of (cast(`f2` as char(50) array)) or json'"zz"' member of (cast(`f2` as char(50) array)) or (`test`.`t1`.`f1` = 'bar'))
3080 EXPLAIN
3081 SELECT *
3082 FROM t1
3083 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"') OR f1 = 'bar';
3084 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3085 1 SIMPLE t1 NULL index_merge PRIMARY,idx2 idx2,PRIMARY 203,202 NULL 3 100.00 Using sort_union(idx2,PRIMARY); Using where
3086 Warnings:
3087 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where (json_contains(cast(`f2` as char(50) array),json'["xx"]') or json_contains(cast(`f2` as char(50) array),json'["zz"]') or (`test`.`t1`.`f1` = 'bar'))
3088 EXPLAIN
3089 SELECT *
3090 FROM t1
3091 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]') OR
3092 f1 = 'bar';
3093 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3094 1 SIMPLE t1 NULL index_merge PRIMARY,idx2 idx2,PRIMARY 203,202 NULL 4 100.00 Using sort_union(idx2,PRIMARY); Using where
3095 Warnings:
3096 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where (json_overlaps(cast(`f2` as char(50) array),json'["xx", "zz"]') or json_overlaps(cast(`f2` as char(50) array),json'["zz"]') or (`test`.`t1`.`f1` = 'bar'))
3097 EXPLAIN
3098 SELECT *
3099 FROM v1
3100 WHERE 'xx' MEMBER OF (f2) OR 'zz' MEMBER OF(f2) OR f1 = 'bar';
3101 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3102 1 SIMPLE t1 NULL index_merge PRIMARY,idx2 idx2,PRIMARY 203,202 NULL 3 100.00 Using sort_union(idx2,PRIMARY); Using where
3103 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
3104 Warnings:
3105 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where (json'"xx"' member of (cast(`f2` as char(50) array)) or json'"zz"' member of (cast(`f2` as char(50) array)) or (`test`.`t1`.`f1` = 'bar'))
3106 EXPLAIN
3107 SELECT *
3108 FROM v1
3109 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"') OR f1 = 'bar';
3110 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3111 1 SIMPLE t1 NULL index_merge PRIMARY,idx2 idx2,PRIMARY 203,202 NULL 3 100.00 Using sort_union(idx2,PRIMARY); Using where
3112 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
3113 Warnings:
3114 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where (json_contains(cast(`f2` as char(50) array),json'["xx"]') or json_contains(cast(`f2` as char(50) array),json'["zz"]') or (`test`.`t1`.`f1` = 'bar'))
3115 EXPLAIN
3116 SELECT *
3117 FROM v1
3118 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]') OR
3119 f1 = 'bar';
3120 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3121 1 SIMPLE t1 NULL index_merge PRIMARY,idx2 idx2,PRIMARY 203,202 NULL 4 100.00 Using sort_union(idx2,PRIMARY); Using where
3122 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
3123 Warnings:
3124 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where (json_overlaps(cast(`f2` as char(50) array),json'["xx", "zz"]') or json_overlaps(cast(`f2` as char(50) array),json'["zz"]') or (`test`.`t1`.`f1` = 'bar'))
3125 EXPLAIN
3126 SELECT *
3127 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
3128 PATH '$')) AS ids
3129 WHERE 'xx' MEMBER OF (f2) OR 'zz' MEMBER OF (f2) OR f1 = 'bar';
3130 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3131 1 SIMPLE t1 NULL index_merge PRIMARY,idx2 idx2,PRIMARY 203,202 NULL 3 100.00 Using sort_union(idx2,PRIMARY); Using where
3132 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
3133 Warnings:
3134 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where (json'"xx"' member of (cast(`f2` as char(50) array)) or json'"zz"' member of (cast(`f2` as char(50) array)) or (`test`.`t1`.`f1` = 'bar'))
3135 EXPLAIN
3136 SELECT *
3137 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
3138 PATH '$')) AS ids
3139 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"') OR f1 = 'bar';
3140 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3141 1 SIMPLE t1 NULL index_merge PRIMARY,idx2 idx2,PRIMARY 203,202 NULL 3 100.00 Using sort_union(idx2,PRIMARY); Using where
3142 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
3143 Warnings:
3144 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where (json_contains(cast(`f2` as char(50) array),json'["xx"]') or json_contains(cast(`f2` as char(50) array),json'["zz"]') or (`test`.`t1`.`f1` = 'bar'))
3145 EXPLAIN
3146 SELECT *
3147 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
3148 PATH '$')) AS ids
3149 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]') OR
3150 f1 = 'bar';
3151 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3152 1 SIMPLE t1 NULL index_merge PRIMARY,idx2 idx2,PRIMARY 203,202 NULL 4 100.00 Using sort_union(idx2,PRIMARY); Using where
3153 1 SIMPLE ids NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
3154 Warnings:
3155 Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`ids`.`i` AS `i`,`ids`.`id` AS `id` from `test`.`t1` join json_table(`test`.`t1`.`f2`, '$[*]' columns (`i` for ordinality, `id` varchar(50) character set utf8mb4 path '$')) `ids` where (json_overlaps(cast(`f2` as char(50) array),json'["xx", "zz"]') or json_overlaps(cast(`f2` as char(50) array),json'["zz"]') or (`test`.`t1`.`f1` = 'bar'))
3156 SELECT * FROM t1 WHERE 'xx' MEMBER OF (f2) OR 'zz' MEMBER OF (f2);
3157 f1 f2
3158 bar ["xx", "yy"]
3159 SELECT * FROM t1 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"');
3160 f1 f2
3161 bar ["xx", "yy"]
3162 SELECT *
3163 FROM t1
3164 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]');
3165 f1 f2
3166 bar ["xx", "yy"]
3167 SELECT * FROM v1 WHERE 'xx' MEMBER OF (f2) OR 'zz' MEMBER OF(f2);
3168 f1 f2 i id
3169 bar ["xx", "yy"] 1 xx
3170 bar ["xx", "yy"] 2 yy
3171 SELECT * FROM v1 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"');
3172 f1 f2 i id
3173 bar ["xx", "yy"] 1 xx
3174 bar ["xx", "yy"] 2 yy
3175 SELECT *
3176 FROM v1
3177 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]');
3178 f1 f2 i id
3179 bar ["xx", "yy"] 1 xx
3180 bar ["xx", "yy"] 2 yy
3181 SELECT *
3182 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
3183 PATH '$')) AS ids
3184 WHERE 'xx' MEMBER OF (f2) OR 'zz' MEMBER OF (f2);
3185 f1 f2 i id
3186 bar ["xx", "yy"] 1 xx
3187 bar ["xx", "yy"] 2 yy
3188 SELECT *
3189 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
3190 PATH '$')) AS ids
3191 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"');
3192 f1 f2 i id
3193 bar ["xx", "yy"] 1 xx
3194 bar ["xx", "yy"] 2 yy
3195 SELECT *
3196 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
3197 PATH '$')) AS ids
3198 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]');
3199 f1 f2 i id
3200 bar ["xx", "yy"] 1 xx
3201 bar ["xx", "yy"] 2 yy
3202 SELECT * FROM t1 WHERE 'xx' MEMBER OF (f2) OR 'zz' MEMBER OF (f2) OR f1 = 'bar';
3203 f1 f2
3204 bar ["xx", "yy"]
3205 SELECT *
3206 FROM t1
3207 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"') OR f1 = 'bar';
3208 f1 f2
3209 bar ["xx", "yy"]
3210 SELECT *
3211 FROM t1
3212 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]') OR
3213 f1 = 'bar';
3214 f1 f2
3215 bar ["xx", "yy"]
3216 SELECT * FROM v1 WHERE 'xx' MEMBER OF (f2) OR 'zz' MEMBER OF (f2) OR f1 = 'bar';
3217 f1 f2 i id
3218 bar ["xx", "yy"] 1 xx
3219 bar ["xx", "yy"] 2 yy
3220 SELECT *
3221 FROM v1
3222 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"') OR f1 = 'bar';
3223 f1 f2 i id
3224 bar ["xx", "yy"] 1 xx
3225 bar ["xx", "yy"] 2 yy
3226 SELECT *
3227 FROM v1
3228 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]') OR
3229 f1 = 'bar';
3230 f1 f2 i id
3231 bar ["xx", "yy"] 1 xx
3232 bar ["xx", "yy"] 2 yy
3233 SELECT *
3234 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
3235 PATH '$')) AS ids
3236 WHERE 'xx' MEMBER OF (f2) OR 'zz' MEMBER OF (f2) OR f1 = 'bar';
3237 f1 f2 i id
3238 bar ["xx", "yy"] 1 xx
3239 bar ["xx", "yy"] 2 yy
3240 SELECT *
3241 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
3242 PATH '$')) AS ids
3243 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"') OR f1 = 'bar';
3244 f1 f2 i id
3245 bar ["xx", "yy"] 1 xx
3246 bar ["xx", "yy"] 2 yy
3247 SELECT *
3248 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
3249 PATH '$')) AS ids
3250 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]') OR
3251 f1 = 'bar';
3252 f1 f2 i id
3253 bar ["xx", "yy"] 1 xx
3254 bar ["xx", "yy"] 2 yy
3255 DROP VIEW v1;
3256 DROP TABLE t1;
3257 #
3258 # Bug#33343948: Multi-valued index ranges are printed as binary
3259 #
3260 CREATE TABLE t(doc JSON,
3261 KEY ((CAST(doc->'$[*].name' AS CHAR(3) ARRAY))),
3262 KEY ((CAST(doc->'$[*].date' AS DATE ARRAY))));
3263 INSERT INTO t VALUES (
3264 '[{"name": "abc", "date": "2021-01-01"}, {"name": "def", "date": "2021-01-02"},
3265 {"name": "ghi", "date": "2021-01-03"}, {"name": "jkl", "date": "2021-01-04"}]'
3266 );
3267 ANALYZE TABLE t;
3268 Table Op Msg_type Msg_text
3269 test.t analyze status OK
3270 EXPLAIN FORMAT=TREE
3271 SELECT * FROM t
3272 WHERE JSON_OVERLAPS(doc->'$[*].name', '["abc", "xyz"]');
3273 EXPLAIN
3274 -> Filter: json_overlaps(cast(json_extract(doc,_utf8mb4'$[*].name') as char(3) array),json'["abc", "xyz"]') (cost=1.1 rows=2)
3275 -> Index range scan on t using functional_index over ('xyz' MEMBER OF (json_extract(doc,_utf8mb4'$[*].name'))) OR ('abc' MEMBER OF (json_extract(doc,_utf8mb4'$[*].name'))) (cost=1.1 rows=2)
3276
3277 EXPLAIN FORMAT=TREE
3278 SELECT * FROM t
3279 WHERE JSON_OVERLAPS(doc->'$[*].date', '["2020-01-01", "2021-01-01"]');
3280 EXPLAIN
3281 -> Filter: json_overlaps(cast(json_extract(doc,_utf8mb4'$[*].date') as date array),json'["2020-01-01", "2021-01-01"]') (cost=1.1 rows=2)
3282 -> Index range scan on t using functional_index_2 over ('2021-01-01' MEMBER OF (json_extract(doc,_utf8mb4'$[*].date'))) OR ('2020-01-01' MEMBER OF (json_extract(doc,_utf8mb4'$[*].date'))) (cost=1.1 rows=2)
3283
3284 DROP TABLE t;
3285 #
3286 # Bug#35012146 Multivalued index creation results in partial result for
3287 # query with ORDER BY DESC
3288 #
3289 CREATE TABLE test (id INT NOT NULL, json_value JSON, PRIMARY KEY (id),
3290 INDEX multivalued_index ((CAST(json_value->'$' AS UNSIGNED ARRAY))));
3291 INSERT INTO test VALUES (1,'[1, 2, 3, 4]');
3292 INSERT INTO test VALUES (2,'[4, 5, 6, 7]');
3293 INSERT INTO test VALUES (3,'[7, 8, 9, 1]');
3294 INSERT INTO test VALUES (4,'[9, 1, 2, 3]');
3295 INSERT INTO test VALUES (5,'[3, 4, 5, 6]');
3296 INSERT INTO test VALUES (6,'[6, 7, 8, 9]');
3297 ANALYZE TABLE test;
3298 Table Op Msg_type Msg_text
3299 test.test analyze status OK
3300 SELECT id, json_value FROM test WHERE 4 MEMBER OF (json_value->'$') ORDER BY id;
3301 id json_value
3302 1 [1, 2, 3, 4]
3303 2 [4, 5, 6, 7]
3304 5 [3, 4, 5, 6]
3305 EXPLAIN SELECT id, json_value FROM test WHERE 4 MEMBER OF (json_value->'$')
3306 ORDER BY id DESC;
3307 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3308 1 SIMPLE test NULL ref multivalued_index multivalued_index 9 const 3 100.00 Using where; Backward index scan
3309 Warnings:
3310 Note 1003 /* select#1 */ select `test`.`test`.`id` AS `id`,`test`.`test`.`json_value` AS `json_value` from `test`.`test` where json'4' member of (cast(json_extract(`json_value`,_utf8mb4'$') as unsigned array)) order by `test`.`test`.`id` desc
3311 EXPLAIN ANALYZE SELECT id, json_value FROM test WHERE
3312 4 MEMBER OF (json_value->'$') ORDER BY id DESC;
3313 EXPLAIN
3314 -> Filter: json'4' member of (cast(json_extract(`json_value`,_utf8mb4'$') as unsigned array)) (cost=0.8 rows=3) (actual rows=3 loops=1)
3315 -> Index lookup on test using multivalued_index (cast(json_extract(`json_value`,_utf8mb4'$') as unsigned array)=json'4') (reverse) (cost=0.8 rows=3) (actual rows=3 loops=1)
3316
3317 SELECT id, json_value FROM test WHERE 4 MEMBER OF (json_value->'$')
3318 ORDER BY id DESC;
3319 id json_value
3320 5 [3, 4, 5, 6]
3321 2 [4, 5, 6, 7]
3322 1 [1, 2, 3, 4]
3323 DROP TABLE test;
3324 CREATE TABLE test (id INT PRIMARY KEY, j JSON,
3325 INDEX multivalued_index ((CAST(j->'$[*]' AS CHAR(10) ARRAY))));
3326 INSERT INTO test VALUES (1, '["10"]'),
3327 (2, '["9"]'),
3328 (3, '["8", "10", "25"]'),
3329 (4, '["7", "15" ,"3", "27"]'),
3330 (5, '["6", "3", "10", "95"]'),
3331 (6, '["5"]'),
3332 (7, '["4"]'),
3333 (8, '["3"]'),
3334 (9, '["2"]'),
3335 (10, '["1"]');
3336 ANALYZE TABLE test;
3337 Table Op Msg_type Msg_text
3338 test.test analyze status OK
3339 EXPLAIN ANALYZE SELECT * FROM test WHERE "10" MEMBER OF (j->'$[*]')
3340 ORDER BY id DESC;
3341 EXPLAIN
3342 -> Filter: json'"10"' member of (cast(json_extract(j,_utf8mb4'$[*]') as char(10) array)) (cost=0.8 rows=3) (actual rows=3 loops=1)
3343 -> Index lookup on test using multivalued_index (cast(json_extract(j,_utf8mb4'$[*]') as char(10) array)=json'"10"') (reverse) (cost=0.8 rows=3) (actual rows=3 loops=1)
3344
3345 SELECT * FROM test WHERE "10" MEMBER OF (j->'$[*]') ORDER BY id DESC;
3346 id j
3347 5 ["6", "3", "10", "95"]
3348 3 ["8", "10", "25"]
3349 1 ["10"]
3350 EXPLAIN ANALYZE SELECT * FROM test WHERE "3" MEMBER OF (j->'$[*]')
3351 ORDER BY id DESC;
3352 EXPLAIN
3353 -> Filter: json'"3"' member of (cast(json_extract(j,_utf8mb4'$[*]') as char(10) array)) (cost=0.8 rows=3) (actual rows=3 loops=1)
3354 -> Index lookup on test using multivalued_index (cast(json_extract(j,_utf8mb4'$[*]') as char(10) array)=json'"3"') (reverse) (cost=0.8 rows=3) (actual rows=3 loops=1)
3355
3356 SELECT * FROM test WHERE "3" MEMBER OF (j->'$[*]') ORDER BY id DESC;
3357 id j
3358 8 ["3"]
3359 5 ["6", "3", "10", "95"]
3360 4 ["7", "15", "3", "27"]
3361 DROP TABLE test;
3362 # restart:
3363 #
3364 # Bug#33334911 - Multi-valued index performance is too slow
3365 # (Access too many rows than required)
3366 #
3367 CREATE TABLE t1 (
3368 id INT NOT NULL,
3369 f1 enum('T1','T2','T3') NOT NULL,
3370 f2 INT NOT NULL,
3371 json_value json DEFAULT (json_array()),
3372 PRIMARY KEY (id),
3373 KEY idx (f1 ,(CAST(json_extract(json_value,_utf8mb4'$') AS UNSIGNED ARRAY)), f2)
3374 );
3375 INSERT INTO t1 VALUES (1, 'T2', 15, '[1, 3, 5, 7]');
3376 INSERT INTO t1 VALUES (2, 'T2', 25, '[10, 20, 30, 40]');
3377 INSERT INTO t1 VALUES (3, 'T2', 35, '[15, 25, 35, 45]');
3378 INSERT INTO t1 VALUES (4, 'T2', 45, '[100, 200, 300, 400]');
3379 INSERT INTO t1 VALUES (5, 'T2', 55, '[120, 220, 320, 420]');
3380 INSERT INTO t1 VALUES (6, 'T2', 65, '[140, 240, 340, 440]');
3381 INSERT INTO t1 VALUES (7, 'T2', 9, '[100, 200, 300, 400]');
3382 INSERT INTO t1 VALUES (8, 'T2', 9, '[100, 200, 300, 400]');
3383 INSERT INTO t1 VALUES (9, 'T2', 9, '[100, 200, 300, 400]');
3384 INSERT INTO t1 VALUES (10, 'T2', 9, '[100, 200, 300, 400]');
3385 ANALYZE TABLE t1;
3386 Table Op Msg_type Msg_text
3387 test.t1 analyze status OK
3388 EXPLAIN SELECT COUNT(*) FROM t1
3389 WHERE f1 IN ('T2') AND JSON_OVERLAPS(json_value->'$', CAST('[100]' AS JSON)) AND f2>=10;
3390 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3391 1 SIMPLE t1 NULL range idx idx 14 NULL 1 100.00 Using where
3392 Warnings:
3393 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where ((`test`.`t1`.`f1` = 'T2') and json_overlaps(cast(json_extract(`json_value`,_utf8mb4'$') as unsigned array),json'[100]') and (`test`.`t1`.`f2` >= 10))
3394 FLUSH STATUS;
3395 SELECT COUNT(*) FROM t1
3396 WHERE f1 IN ('T2') AND JSON_OVERLAPS(json_value->'$', CAST('[100]' AS JSON)) AND f2>=10;
3397 COUNT(*)
3398 1
3399 SHOW STATUS LIKE 'Handler_read%';
3400 Variable_name Value
3401 Handler_read_first 0
3402 Handler_read_key 1
3403 Handler_read_last 0
3404 Handler_read_next 1
3405 Handler_read_prev 0
3406 Handler_read_rnd 0
3407 Handler_read_rnd_next 0
3408 SET @save_opt=@@optimizer_switch;
3409 SET @@optimizer_switch="mrr_cost_based=off";
3410 EXPLAIN SELECT COUNT(*) FROM t1
3411 WHERE f1 IN ('T2') AND JSON_OVERLAPS(json_value->'$', CAST('[100]' AS JSON)) AND f2>=10;
3412 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
3413 1 SIMPLE t1 NULL range idx idx 14 NULL 1 100.00 Using where
3414 Warnings:
3415 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where ((`test`.`t1`.`f1` = 'T2') and json_overlaps(cast(json_extract(`json_value`,_utf8mb4'$') as unsigned array),json'[100]') and (`test`.`t1`.`f2` >= 10))
3416 SELECT COUNT(*) FROM t1
3417 WHERE f1 IN ('T2') AND JSON_OVERLAPS(json_value->'$', CAST('[100]' AS JSON)) AND f2>=10;
3418 COUNT(*)
3419 1
3420 SET @@optimizer_switch=@save_opt;
3421 DROP TABLE t1;
3422 #
3423 # Bug#37436310 - Performance issue with Multi-Valued Index and ORDER BY DESC LIMIT
3424 # (Reads more data than expected)
3425 #
3426 CREATE TABLE t1 (
3427 id INT NOT NULL AUTO_INCREMENT,
3428 ids json NOT NULL,
3429 PRIMARY KEY (`id`),
3430 KEY mx_ids_id ((CAST(json_extract(ids,_utf8mb4'$[*]') AS SIGNED ARRAY)),id)
3431 );
3432 INSERT INTO `t1` VALUES (1,'[10]');
3433 INSERT INTO `t1` VALUES (2,'[10]');
3434 INSERT INTO `t1` VALUES (3,'[20]');
3435 INSERT INTO `t1` VALUES (4,'[30]');
3436 INSERT INTO `t1` VALUES (5,'[40]');
3437 INSERT INTO `t1` VALUES (6,'[50]');
3438 INSERT INTO `t1` VALUES (7,'[50]');
3439 INSERT INTO `t1` VALUES (8,'[50]');
3440 ANALYZE TABLE t1;
3441 Table Op Msg_type Msg_text
3442 test.t1 analyze status OK
3443 FLUSH STATUS;
3444 SELECT * FROM t1 WHERE 30 MEMBER OF (ids->'$[*]') AND id < 7 ORDER BY id DESC;
3445 id ids
3446 4 [30]
3447 SHOW STATUS LIKE 'Handler_read%';
3448 Variable_name Value
3449 Handler_read_first 0
3450 Handler_read_key 1
3451 Handler_read_last 0
3452 Handler_read_next 0
3453 Handler_read_prev 1
3454 Handler_read_rnd 0
3455 Handler_read_rnd_next 0
3456 SELECT /*+ INDEX(t1 mx_ids_id) */ * FROM t1 WHERE 50 MEMBER OF (ids->'$[*]') AND id > 5 ORDER BY id DESC;
3457 id ids
3458 8 [50]
3459 7 [50]
3460 6 [50]
3461 DROP TABLE t1;