Debian Codegraph @

mysql-test/suite/json/t/array_index.test

raw
1 --source include/elide_costs.inc
2
3 CREATE TABLE t1 (f1 json);
4 create index i1 on t1((cast(f1 as unsigned array)));
5 show create table t1;
6 insert into t1(f1) values
7 (cast(1 as json)),
8 (cast('[2,3,4]' as json)),
9 (cast('[3,4,3,4,2,2,2]' as json)),
10 (cast('[5,5,6,6]' as json));
11 select * from t1;
12 alter table t1 add index mv_idx2((cast(f1 as signed array)));
13 show create table t1;
14 alter table t1 drop index i1;
15 show create table t1;
16 drop index mv_idx2 on t1;
17 show create table t1;
18 drop table t1;
19
20 CREATE TABLE t1 (f1 json);
21
22 insert into t1(f1) values (cast("null" as json));
23 --error ER_INVALID_JSON_VALUE_FOR_FUNC_INDEX
24 create index i1 on t1((cast(f1 as unsigned array)));
25 delete from t1;
26
27 insert into t1(f1) values ('1111111111111111111111');
28 -- error ER_JSON_VALUE_OUT_OF_RANGE_FOR_FUNC_INDEX
29 create index i1 on t1((cast(f1 as unsigned array)));
30 delete from t1;
31
32 create index i1 on t1((cast(f1 as unsigned array)));
33 --error ER_INVALID_JSON_VALUE_FOR_FUNC_INDEX
34 insert into t1(f1) values (cast("null" as json));
35 -- error ER_JSON_VALUE_OUT_OF_RANGE_FOR_FUNC_INDEX
36 insert into t1(f1) values ('1111111111111111111111');
37 drop table t1;
38
39 create table t1 (f1 json, key mvi((cast(f1 as unsigned array))));
40 show create table t1;
41 drop table t1;
42
43 --error ER_NOT_SUPPORTED_YET
44 select cast('[1,2,3]' as unsigned array);
45 --error ER_NOT_SUPPORTED_YET
46 create table t1 as select cast('[1,2,3]' as unsigned array);
47 --error ER_NOT_SUPPORTED_YET
48 create table t(j json, gc json as (cast(j->'$[*]' as unsigned array)));
49 --error ER_NOT_SUPPORTED_YET
50 create table t(j json, gc json as
51 ((concat(cast(j->'$[*]' as unsigned array),"x"))));
52
53 --error ER_NOT_SUPPORTED_YET
54 create table t1(j json, key i1((cast(j->"$" as json array))));
55 --error ER_NOT_SUPPORTED_YET
56 create table t1(j json, key i1((cast(j->"$" as char array))));
57 --error ER_NOT_SUPPORTED_YET
58 create table t1(j json, key i1((cast(j->"$" as binary array))));
59 --error ER_NOT_SUPPORTED_YET
60 create table t1(j json, key i1((cast(j->"$" as float array))));
61 --error ER_NOT_SUPPORTED_YET
62 create table t1(j json, key i1((cast(j->"$" as double array))));
63
64 --error ER_CHECK_NOT_IMPLEMENTED
65 CREATE TABLE t1 (f1 json, key mvi((cast(f1 as unsigned array)))) engine=myisam;
66 --error ER_WRONG_USAGE
67 CREATE TABLE t1 (f1 json, key mvi((cast(f1 as unsigned array)) asc));
68 --error ER_NOT_SUPPORTED_YET
69 CREATE TABLE t1 (f1 json, key mvi((cast(cast(f1 as unsigned array) as unsigned array))));
70 --error ER_NOT_SUPPORTED_YET
71 CREATE TABLE t(x INT, KEY k ((1 AND CAST(JSON_ARRAY(x) AS UNSIGNED ARRAY))));
72
73 --error ER_NOT_SUPPORTED_YET
74 create table col(doc json,
75 key i1((cast(doc->'$.text' as char(10) array)),
76 (cast(doc->'$.integer' as signed array))));
77
78 create table t1(j json, key i1((cast(j as char(10) array))));
79 --error ER_NOT_SUPPORTED_YET
80 insert into t1 values('{"asd":1}');
81 --error ER_NOT_SUPPORTED_YET
82 insert into t1 values('true');
83 drop table t1;
84
85 --error ER_NOT_SUPPORTED_YET
86 create table t1 (j json, key mv_idx_char ((cast(j as char(16384) array))));
87 create table t1 (j json, key mv_idx_char ((cast(j as char(512) array))));
88 show create table t1;
89 drop table t1;
90
91 --error ER_WRONG_USAGE
92 create function f() returns int deterministic return cast(json_array(1,2) as
93 unsigned array);
94
95 create table t(x int, key k ((cast(json_array(x) as decimal(10,3) array))));
96 drop table t;
97
98 create table t(j json, key k ((cast(j as unsigned array))));
99 insert into t values ('[]');
100 drop table t;
101
102 select 1 member of ('[1,2,3]');
103 select 1 member of ('1');
104 select 1 member ('1');
105
106 select cast(1 as json) member of(json_array(1,2,3));
107 select cast(4 as json) member of(json_array(1,2,3));
108 select cast(1 as json) member of(json_array(2,NULL,1));
109 select cast(4 as json) member of(json_array(1,2,NULL));
110
111 set @A_VAR=json_array(1,2,3);
112 select cast(1 as json) member of(cast(@A_VAR as json));
113 select cast(4 as json) member of(cast(@A_VAR as json));
114
115 set @A_VAR=json_array(2,cast('{"A":1, "B":2}' as json),3);
116 select cast('{"A":1, "B":2}' as json) member of(cast(@A_VAR as json));
117 select cast('{"B":2, "A":1}' as json) member of(cast(@A_VAR as json));
118 select cast('{"B":2, "A":2}' as json) member of(cast(@A_VAR as json));
119
120 set @A_VAR=json_array(2,cast('{"A":1, "B":2}' as json), NULL);
121 select cast('{"B":2, "A":1}' as json) member of(cast(@A_VAR as json));
122
123 create table t1 (f1 json, key i1 ((cast(f1->"$.a" as unsigned array))));
124 insert into t1(f1) values (cast('{}' as json));
125 drop table t1;
126
127 --error ER_NOT_SUPPORTED_YET
128 create table t (j json default (cast(json_object() as unsigned array)));
129
130 # InnoDB doesn''t support index record per data record more than some threshold
131 # which depends on the data type.
132 # Test the limit
133 create table t(f char(1));
134 insert into t values
135 ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('A'),('B'),('C'),('D'),('E'),('F');
136 create table t1(doc json,key i1((cast(doc->'$' as unsigned array))));
137 set session group_concat_max_len = 1000000;
138 --echo Check limit on max total mv keys length
139 --echo Should succeed
140 insert into t1 select concat('[',group_concat(val),']') from (
141 select conv(concat(t1.f, t2.f, t3.f), 16, 10) val
142 from t t1, t t2, t t3
143 having val < 1605) tt;
144 --error ER_EXCEEDED_MV_KEYS_NUM
145 insert into t1 select concat('[',group_concat(val),']') from (
146 select conv(concat(t1.f, t2.f, t3.f), 16, 10) val
147 from t t1, t t2, t t3
148 having val < 1606) tt;
149 create table t2(doc json,key i1((cast(doc->'$' as char(10) array))));
150 --error ER_EXCEEDED_MV_KEYS_SPACE
151 insert into t2 select concat('[',group_concat(val),']') from (
152 select conv(concat(t1.f, t2.f, t3.f), 16, 10) dec_val,
153 concat('"',
154 repeat(t1.f,3),
155 repeat(t2.f,3),
156 repeat(t3.f,2),
157 '"') val
158 from t t1, t t2, t t3
159 having dec_val < 1600) tt;
160 --error ER_EXCEEDED_MV_KEYS_NUM
161 insert into t2 select concat('[',group_concat(val),']') from (
162 select conv(concat(t1.f, t2.f, t3.f, t4.f), 16, 10) dec_val,
163 concat('"',
164 repeat(t1.f,3),
165 repeat(t2.f,3),
166 repeat(t3.f,2),
167 repeat(t4.f,2),
168 '"') val
169 from t t1, t t2, t t3, t t4) tt;
170 analyze table t1;
171 explain select * from t1 ignore key(i1) where json_contains(doc->"$","[1,2,3]");
172 explain select * from t1 where json_contains(doc->"$","[1,2,3]");
173 explain select * from t1 where json_overlaps(doc->"$","[1,2,3]");
174 explain select * from t1 where json_overlaps("[1,2,3]", doc->"$");
175 drop table t2, t1, t;
176
177 create table t2(j json, key k ((cast(j->'$[*]' as char(10) array))));
178 insert into t2 values (json_array('abc')), (json_array('ABC'));
179 analyze table t2;
180 select * from t2 where "abc" member of (j->'$[*]');
181 explain select * from t2 where "abc" member of (j->'$[*]');
182 explain select * from t2 ignore key(k) where "abc" member of (j->'$[*]');
183 insert into t2 values('[]');
184 drop table t2;
185
186 create table t1(j json, key i1((cast(j->"$.id" as char(10) array))), key i2((cast(j->"$.id" as unsigned array))));
187 insert into t1 values('{"id":1}');
188 select * from t1 where 1 member of (j->"$.id");
189 explain select * from t1 where 1 member of (j->"$.id");
190 select * from t1 where '1' member of (j->"$.id");
191 explain select * from t1 where '1' member of (j->"$.id");
192 drop table t1;
193
194 create table t(j json, key k ((cast(json_array(j) as unsigned array))));
195 insert into t values ('1');
196 select * from t where 1 member of (json_array(j));
197 explain select * from t where 1 member of (json_array(j));
198 drop table t;
199
200 create table t1(x varchar(10), key k ((cast(concat(x,x) as unsigned array))));
201 insert into t1 values ('1');
202 select * from t1 where 11 member of (concat(x,x));
203 explain select * from t1 where 11 member of (concat(x,x));
204 drop table t1;
205
206 --echo Test for error on array/object insertion into a scalar index
207 create table t1(j json, key i1((cast(json_array(j) as unsigned array))));
208 --error ER_WRONG_MVI_VALUE
209 insert into t1 values ('{"a":1}');
210 --error ER_WRONG_MVI_VALUE
211 insert into t1 values ('[1,2,3]');
212 drop table t1;
213
214 --echo Same as above, but error is thrown from inside of InnoDB
215 create table t(j json);
216 insert into t values ('{"a":1}');
217 --error ER_WRONG_MVI_VALUE
218 create index idx on t((cast(json_array(j) as unsigned array)));
219 drop table t;
220
221 create table t1(j json,
222 sp_f json as (ifnull(j->'$.data','[]')) virtual,
223 key sp_i((cast(sp_f as unsigned array))));
224 insert into t1(j) values
225 ('{"data":[1,2,3]}'),
226 ('{"other_data":[4,5,6]}'),
227 ('"no data"');
228 analyze table t1;
229 select * from t1 where 2 member of(sp_f);
230 explain select * from t1 where 2 member of(sp_f);
231 drop table t1;
232
233 create table t(vc varchar(10), key ((cast(vc->'$' as unsigned array))));
234 --error ER_INVALID_JSON_TEXT_IN_PARAM
235 insert into t values ('');
236 drop table t;
237
238 create table t(j json, key i1((cast(j->'$' as unsigned))));
239 insert into t values('1');
240 select * from t where json_contains(j->'$', '1');
241 explain select * from t where json_contains(j->'$', '1');
242 drop table t;
243
244 create table t(j json, key i1((cast(j->'$' as unsigned array))));
245 insert into t values('1');
246 select * from t where json_contains(j->'$', '1');
247 explain select * from t where json_contains(j->'$', '1');
248 select * from t where json_contains(j->'$', '{"a":1}');
249 select * from t where j->'$'= 1;
250 explain select * from t where j->'$'= 1;
251 select * from t where j->'$'>= 1;
252 explain select * from t where j->'$'>= 1;
253 drop table t;
254
255 # Test Unicode chars collision
256 CREATE TABLE t1 (id INT AUTO_INCREMENT KEY, f1 JSON, KEY i1((CAST(f1->"$[*]" AS CHAR(10) ARRAY))));
257 INSERT INTO t1(f1) VALUES(JSON_ARRAY(_utf8mb4 0xE284AB, _utf8mb4 0xc385));
258 SELECT * FROM t1 WHERE _utf8mb4 0xE284AB MEMBER OF (f1->"$[*]");
259 SELECT * FROM t1 WHERE _utf8mb4 0xc385 MEMBER OF (f1->"$[*]");
260 DELETE FROM t1;
261 INSERT INTO t1(f1) VALUES(JSON_ARRAY( _utf8mb4 0xc385, _utf8mb4 0xE284AB));
262 SELECT * FROM t1 WHERE _utf8mb4 0xE284AB MEMBER OF (f1->"$[*]");
263 SELECT * FROM t1 WHERE _utf8mb4 0xc385 MEMBER OF (f1->"$[*]");
264 DELETE FROM t1;
265 INSERT INTO t1(f1) VALUES(JSON_ARRAY(_utf8mb4 0xE284AB));
266 SELECT * FROM t1 WHERE _utf8mb4 0xE284AB MEMBER OF (f1->"$[*]");
267 --echo Empty result is expected
268 SELECT * FROM t1 WHERE _utf8mb4 0xc385 MEMBER OF (f1->"$[*]");
269 DELETE FROM t1;
270 INSERT INTO t1(f1) VALUES(JSON_ARRAY( _utf8mb4 0xc385));
271 --echo Empty result is expected
272 SELECT * FROM t1 WHERE _utf8mb4 0xE284AB MEMBER OF (f1->"$[*]");
273 SELECT * FROM t1 WHERE _utf8mb4 0xc385 MEMBER OF (f1->"$[*]");
274 DROP TABLE t1;
275
276 CREATE TABLE t1 (id INT AUTO_INCREMENT KEY, f1 JSON, UNIQUE KEY i1((CAST(f1->"$[*]" AS CHAR(10) ARRAY))));
277 INSERT INTO t1(f1) VALUES(JSON_ARRAY(_utf8mb4 0xE284AB, _utf8mb4 0xc385));
278 SELECT * FROM t1 WHERE _utf8mb4 0xE284AB MEMBER OF (f1->"$[*]");
279 SELECT * FROM t1 WHERE _utf8mb4 0xc385 MEMBER OF (f1->"$[*]");
280 DELETE FROM t1;
281 INSERT INTO t1(f1) VALUES(JSON_ARRAY( _utf8mb4 0xc385, _utf8mb4 0xE284AB));
282 SELECT * FROM t1 WHERE _utf8mb4 0xE284AB MEMBER OF (f1->"$[*]");
283 SELECT * FROM t1 WHERE _utf8mb4 0xc385 MEMBER OF (f1->"$[*]");
284 DELETE FROM t1;
285 INSERT INTO t1(f1) VALUES(JSON_ARRAY(_utf8mb4 0xE284AB));
286 SELECT * FROM t1 WHERE _utf8mb4 0xE284AB MEMBER OF (f1->"$[*]");
287 --echo Empty result is expected
288 SELECT * FROM t1 WHERE _utf8mb4 0xc385 MEMBER OF (f1->"$[*]");
289 DELETE FROM t1;
290 INSERT INTO t1(f1) VALUES(JSON_ARRAY( _utf8mb4 0xc385));
291 --echo Empty result is expected
292 SELECT * FROM t1 WHERE _utf8mb4 0xE284AB MEMBER OF (f1->"$[*]");
293 SELECT * FROM t1 WHERE _utf8mb4 0xc385 MEMBER OF (f1->"$[*]");
294 DROP TABLE t1;
295
296 --echo Test CAST( .. AS UNSIGNED ARRAY)
297 create table t1 (id int not null key auto_increment, f1 json);
298 create index i1 on t1((cast(f1->"$[*]" as unsigned array)));
299 show create table t1;
300 insert into t1(f1) values
301 (cast('[1,3]' as json)), (cast(2 as json)), (cast('[3,3,4,4,7]' as json)),
302 (cast('[5,7]' as json)),
303 (cast('[8,4,3,5]' as json)), (cast('[5,6,7]' as json)),
304 (cast('[9,2,7]' as json)), (cast('[1,3]' as json)),
305 (cast('[3,3,4,4,7]' as json)), (cast(4 as json)), (cast('[8,4,3,5]' as json)),
306 (cast('[9,2,7]' as json)), (cast('[9,2,7]' as json)),
307 (cast('[1,3]' as json)),
308 (cast('[3,3,4,4,7]' as json)), (cast(4 as json)),
309 (cast(7 as json)), (cast('[8,4,3,5]' as json)), (cast('[9,2,7]' as json)),
310 ('[98,99]');
311 analyze table t1;
312 --error ER_WARN_DATA_OUT_OF_RANGE_FUNCTIONAL_INDEX
313 insert into t1(f1) values (cast("[-2]" as json));
314 --error ER_WARN_DATA_OUT_OF_RANGE_FUNCTIONAL_INDEX
315 insert into t1(f1) values (cast('[1,-3]' as json));
316 select * from t1;
317 select * from t1 where 5 member of (f1->"$[*]");
318 explain select * from t1 where 5 member of (f1->"$[*]");
319
320 select * from t1 where f1->"$[0]" member of ('[1,3,9]');
321 explain select * from t1 where f1->"$[0]" member of ('[1,3,9]');
322
323 select * from t1 force index(i1) where json_contains(f1->"$[*]", "[4,3]");
324 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[4,3]");
325 select * from t1 force index(i1) where json_contains(f1->"$[*]", "[5,7]");
326 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[5,7]");
327 select * from t1 force index(i1) where json_contains(f1->"$[*]", "[7]");
328 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[7]");
329 set @save_opt=@@optimizer_switch;
330 set @@optimizer_switch="mrr=off";
331 select * from t1 force index(i1) where json_contains(f1->"$[*]", "[4,3]");
332 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[4,3]");
333 select * from t1 force index(i1) where json_contains(f1->"$[*]", "[5,7]");
334 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[5,7]");
335 select * from t1 force index(i1) where json_contains(f1->"$[*]", "[7]");
336 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[7]");
337
338 select * from t1 force index(i1) where 99 member of (f1->"$[*]");
339 update t1 set f1=cast('[100,99]' as json) where 99 member of (f1->"$[*]");
340 select * from t1 force index(i1) where json_contains(f1->"$[*]", "[100]");
341 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[100]");
342
343 set @@optimizer_switch=@save_opt;
344
345 --echo # Check that index applicability conditions aren't met as expected
346 explain select * from t1 force index(i1) where json_contains("[1,2,3,4]", "[4,3]");
347 explain select * from t1 force index(i1) where
348 json_contains(concat("[1,2",",3,4]"), "[4,3]");
349 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", f1->"$[*]");
350 --error ER_INVALID_JSON_TEXT_IN_PARAM
351 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[4,");
352 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", '{"a":4}');
353 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", '[4,"a"]');
354
355
356 delete from t1;
357 drop table t1;
358
359 --echo Test CAST( .. AS SIGNED ARRAY)
360 create table t1 (id int not null key auto_increment, f1 json);
361 create index i1 on t1((cast(f1->"$" as signed array)));
362 show create table t1;
363 insert into t1(f1) values
364 (cast('[1,3]' as json)),
365 (cast(2 as json)),
366 (cast('[3,3,4,4,7]' as json)),
367 (cast(4 as json)),
368 (cast('[5,7]' as json)),
369 (cast(6 as json)),
370 (cast(7 as json)),
371 (cast('[8,5]' as json)),
372 (cast('[9,2]' as json));
373 insert into t1(f1) values
374 (cast('[1,-3]' as json)),
375 (cast(-2 as json)), ('[98,99]');
376 analyze table t1;
377 select * from t1;
378 --echo should return record ## 5,8
379 select * from t1 where 5 member of (f1->"$");
380 explain select * from t1 where 5 member of (f1->"$");
381
382 select * from t1 force index(i1) where 99 member of (f1->"$[*]");
383 update t1 set f1=cast('[100,99]' as json) where 99 member of (f1->"$[*]");
384 select * from t1 force index(i1) where 100 member of (f1->"$[*]");
385 explain select * from t1 force index(i1) where 100 member of (f1->"$[*]");
386
387 delete from t1;
388 drop table t1;
389
390 --echo Test CAST( .. AS CHAR(X) ARRAY)
391 create table t1 (f1 json);
392 create index i1 on t1((cast(f1->"$[*]" as char(10) array)));
393 show create table t1;
394 insert into t1(f1) values(cast('"fgh"' as json));
395 insert into t1(f1) values(cast('["asd","qwe"]' as json));
396 insert into t1(f1) values(cast('["qe"]' as json));
397 insert into t1(f1) values(cast('["gfd","qwe"]' as json));
398 insert into t1(f1) values(cast('["ew","sdf"]' as json));
399 insert into t1(f1) values(cast('["xcv","cvb"]' as json));
400 insert into t1(f1) values(cast('["adf"]' as json));
401 insert into t1(f1) values(cast('["asd","sdfqwe"]' as json));
402 insert into t1(f1) values(cast('["ew","sdf"]' as json));
403 insert into t1(f1) values(cast('["xcv","cvb"]' as json));
404 insert into t1(f1) values(cast('["adf"]' as json));
405 insert into t1(f1) values(cast('["asd","sdfqwe"]' as json));
406 insert into t1(f1) values(cast('["bnm","sdfqwe"]' as json));
407 analyze table t1;
408
409 select * from t1 where "qwe" member of (f1->"$[*]");
410 explain select * from t1 where "qwe" member of (f1->"$[*]");
411
412 select * from t1 force index(i1) where "bnm" member of (f1->"$[*]");
413 update t1 set f1=cast('["bvc", "hgfd"]' as json) where "bnm" member of (f1->"$[*]");
414 select * from t1 force index(i1) where "bvc" member of (f1->"$[*]");
415 explain select * from t1 force index(i1) where "bvc" member of (f1->"$[*]");
416
417 delete from t1;
418 drop table t1;
419
420 --echo Test CAST( .. AS BINARY(X) ARRAY)
421 create table t1 (f1 json);
422 create index i1 on t1((cast(f1->"$[*]" as binary(10) array)));
423 show create table t1;
424 insert into t1(f1) values(cast('"fgh"' as json));
425 insert into t1(f1) values(cast('["asd","qwe"]' as json));
426 insert into t1(f1) values(cast('["qe"]' as json));
427 insert into t1(f1) values(cast('["gfd","qwe"]' as json));
428 insert into t1(f1) values(cast('["ew","sdf"]' as json));
429 insert into t1(f1) values(cast('["xcv","cvb"]' as json));
430 insert into t1(f1) values(cast('["adf"]' as json));
431 insert into t1(f1) values(cast('["asd","sdfqwe"]' as json));
432 insert into t1(f1) values(cast('["ew","sdf"]' as json));
433 insert into t1(f1) values(cast('["xcv","cvb"]' as json));
434 insert into t1(f1) values(cast('["adf"]' as json));
435 insert into t1(f1) values(cast('["asd","sdfqwe"]' as json));
436 insert into t1(f1) values(cast('["bnm","sdfqwe"]' as json));
437 analyze table t1;
438
439 select * from t1 where "qwe" member of (f1->"$[*]");
440 explain select * from t1 where "qwe" member of (f1->"$[*]");
441
442 select * from t1 force index(i1) where "bnm" member of (f1->"$[*]");
443 update t1 set f1=cast('["bvc", "hgfd"]' as json) where "bnm" member of (f1->"$[*]");
444 select * from t1 force index(i1) where "bvc" member of (f1->"$[*]");
445 explain select * from t1 force index(i1) where "bvc" member of (f1->"$[*]");
446
447 delete from t1;
448 drop table t1;
449
450
451 --echo Test CAST( .. AS DECIMAL ARRAY)
452 create table t1 (id int not null key auto_increment, f1 json);
453 create index i1 on t1((cast(f1->"$" as decimal(10,4) array)));
454 show create table t1;
455 insert into t1(f1) values(cast(cast(0.0 as decimal) as json));
456 insert into t1(f1) values(cast(cast(1 as decimal) as json));
457 insert into t1(f1) values(cast(cast(-3 as decimal) as json));
458 insert into t1(f1) values(cast(cast(0.0 as decimal) as json));
459 insert into t1(f1) values(cast(cast(1 as decimal) as json));
460 insert into t1(f1) values(cast(cast(-3 as decimal) as json));
461 insert into t1(f1) values(json_array(
462 cast(1.33 as decimal(10,3)),
463 cast(5 as decimal(10,3)),
464 cast(1.33 as decimal(10,1))));
465 insert into t1(f1) values(json_array(
466 cast(-1.33 as decimal(10,3)),
467 cast(5 as decimal),
468 cast(1.33 as decimal(10,4))));
469 insert into t1(f1) values(json_array(
470 cast(98 as decimal(10,3)),
471 cast(99 as decimal)));
472 analyze table t1;
473 select * from t1;
474 select * from t1 where 1.33 member of (f1->"$");
475 explain select * from t1 where 1.33 member of (f1->"$");
476
477 select * from t1 force index(i1) where 99 member of (f1->"$[*]");
478 update t1 set f1=cast('[100,99]' as json) where 99 member of (f1->"$[*]");
479 select * from t1 force index(i1) where 100 member of (f1->"$[*]");
480 explain select * from t1 force index(i1) where 100 member of (f1->"$[*]");
481
482 delete from t1;
483 drop table t1;
484
485
486 --echo #Test CAST( .. AS YEAR ARRAY)
487 create table t1 (id int not null key auto_increment, f1 json);
488 --error ER_NOT_SUPPORTED_YET
489 create index i1 on t1((cast(f1->"$" as YEAR array)));
490 show create table t1;
491 DROP TABLE t1;
492 --error ER_NOT_SUPPORTED_YET
493 create table t1 (id int not null key auto_increment, f1 json, key i1 ((cast(f1->"$" as YEAR array)));
494
495
496 --echo Test CAST( .. AS DATE ARRAY)
497 create table t1 (id int not null key auto_increment, f1 json);
498 create index i1 on t1((cast(f1->"$" as date array)));
499 show create table t1;
500 insert into t1(f1) values(cast(cast('01-01-01' as date) as json));
501 insert into t1(f1) values(cast(cast('01-02-03' as date) as json));
502 insert into t1(f1) values(cast(cast('22.11.17' as date) as json));
503 insert into t1(f1) values(json_array(
504 cast('01-02-03' as date),
505 cast('03-03-03' as date),
506 cast('01.01.17' as date)));
507 insert into t1(f1) values(json_array(
508 cast('03-03-03' as date),
509 cast('05-05-05' as date),
510 cast('01-02-03' as date)));
511 insert into t1(f1) values(json_array(
512 cast('01-01-12' as date),
513 cast('01-01-13' as date)));
514 insert into t1(f1) values('["01-02-03","22.11.17"]');
515 analyze table t1;
516
517 select * from t1 where cast('01-02-03' as date) member of (f1->"$");
518 explain select * from t1 where cast('01-02-03' as date) member of (f1->"$");
519
520 select * from t1 force index(i1) where cast('01-01-12' as date) member of (f1->"$");
521 update t1 set f1=
522 json_array(cast('01-01-14' as date), cast('01-01-12' as date)) where
523 cast('01-01-12' as date) member of (f1->"$");
524 select * from t1 force index(i1) where
525 cast('01-01-14' as date) member of (f1->"$");
526 explain select * from t1 force index(i1) where
527 cast('01-01-14' as date) member of (f1->"$");
528
529 delete from t1;
530 drop table t1;
531
532 --echo Test CAST( .. AS TIME ARRAY)
533 create table t1 (id int not null key auto_increment, f1 json);
534 create index i1 on t1((cast(f1->"$" as time(6) array)));
535 show create table t1;
536 insert into t1(f1) values(cast(cast('01:01:01' as time) as json));
537 select * from t1;
538 drop table t1;
539 create table t1 (id int not null key auto_increment, f1 json);
540 create index i1 on t1((cast(f1->"$" as time array)));
541 show create table t1;
542 insert into t1(f1) values(cast(cast('01:01:01' as time) as json));
543 insert into t1(f1) values(cast(cast('01:02:03' as time) as json));
544 insert into t1(f1) values(cast(cast('22:11:17' as time) as json));
545 insert into t1(f1) values(json_array(
546 cast('01:02:03' as time),
547 cast('03:03:03' as time),
548 cast('01:01:17' as time)));
549 insert into t1(f1) values(json_array(
550 cast('03:03:03' as time),
551 cast('05:05:05' as time),
552 cast('01:02:03' as time)));
553 insert into t1(f1) values(json_array(
554 cast('01:12:54' as time),
555 cast('01:12:55' as time)));
556 insert into t1(f1) values('["01:02:03","22:11:17"]');
557 analyze table t1;
558
559 select * from t1 where cast('01:02:03' as time) member of (f1->"$");
560 explain select * from t1 where cast('01:02:03' as time) member of (f1->"$");
561
562 select * from t1 force index(i1) where
563 cast('01:12:54' as time) member of (f1->"$");
564 update t1 set f1=
565 json_array(cast('01:12:56' as time), cast('01:12:55' as time)) where
566 cast('01:12:54' as time) member of (f1->"$");
567 select * from t1 force index(i1) where
568 cast('01:12:56' as time) member of (f1->"$");
569 explain select * from t1 force index(i1) where
570 cast('01:12:56' as time) member of (f1->"$");
571
572 delete from t1;
573 drop table t1;
574
575 --echo Test CAST( .. AS DATETIME ARRAY)
576 create table t1 (id int not null key auto_increment, f1 json);
577 create index i1 on t1((cast(f1->"$" as datetime(6) array)));
578 show create table t1;
579 insert into t1(f1) values(cast(cast('01-01-01 01:01:01' as datetime) as json));
580 select * from t1;
581 drop table t1;
582 create table t1 (id int not null key auto_increment, f1 json);
583 create index i1 on t1((cast(f1->"$" as datetime array)));
584 show create table t1;
585 insert into t1(f1) values(cast(cast('01-01-01 01:01:01' as datetime) as json));
586 insert into t1(f1) values(cast(cast('01-01-01 01:02:03' as datetime) as json));
587 insert into t1(f1) values(cast(cast('01-01-01 22:11:17' as datetime) as json));
588 insert into t1(f1) values(json_array(
589 cast('01-01-01 01:02:03' as datetime),
590 cast('01-01-01 03:03:03' as datetime),
591 cast('01-01-01 01:01:17' as datetime)));
592 insert into t1(f1) values(json_array(
593 cast('01-01-01 03:03:03' as datetime),
594 cast('01-01-01 05:05:05' as datetime),
595 cast('01-01-01 01:02:03' as datetime)));
596 insert into t1(f1) values(json_array(
597 cast('01-01-01 01:12:54' as datetime),
598 cast('01-01-01 01:12:55' as datetime)));
599 insert into t1(f1) values('["01-01-01 01:02:03","01-01-01 22:11:17"]');
600 analyze table t1;
601
602 select * from t1 where cast('01-01-01 01:02:03' as datetime) member of (f1->"$");
603 explain select * from t1 where cast('01-01-01 01:02:03' as datetime) member
604 of (f1->"$");
605
606 select * from t1 force index(i1) where
607 cast('01-01-01 01:12:54' as datetime) member of (f1->"$");
608 update t1 set f1=
609 json_array(cast('01-01-01 01:12:56' as datetime),
610 cast('01-01-01 01:12:55' as datetime)) where
611 cast('01-01-01 01:12:54' as datetime) member of (f1->"$");
612 select * from t1 force index(i1) where
613 cast('01-01-01 01:12:56' as datetime) member of (f1->"$");
614 explain select * from t1 force index(i1) where
615 cast('01-01-01 01:12:56' as datetime) member of (f1->"$");
616
617 delete from t1;
618 drop table t1;
619
620 select json_overlaps(cast('[1,2,3]' as json), cast('[3,4,5]' as json));
621 select json_overlaps(cast('[1,2,3]' as json), cast('[4,4,5]' as json));
622 select json_overlaps(cast('[1,2,3]' as json), cast('[4,5]' as json));
623 select json_overlaps(cast('[1,2]' as json), cast('[3,4,5]' as json));
624 select json_overlaps(cast('[1,2]' as json), cast('[2,4,5]' as json));
625 select json_overlaps(cast('1' as json), cast('[3,4,5]' as json));
626 select json_overlaps(cast('[3,4,5]' as json), cast('1' as json));
627 select json_overlaps(cast('[3,4,{"a":5}]' as json), cast('{"a":5}' as json));
628 select json_overlaps(cast('{"a":1, "b":2}' as json), cast('{"a":1,"c":3}' as json));
629 select json_overlaps(cast('{"a":1, "b":2}' as json), cast('{"a":2,"c":3}' as json));
630 select json_overlaps(cast('{"a":1, "b":null}' as json), cast('{"a":2,"c":3}' as json));
631 select json_overlaps(cast('{"a":1, "b":2}' as json), cast('{"a":null, "c":3}' as json));
632 select json_overlaps('null','[null]');
633 select json_overlaps('1234',NULL);
634 select json_overlaps('null',NULL);
635 --error ER_INVALID_JSON_TEXT_IN_PARAM
636 select json_overlaps('asdasd',NULL);
637 select json_overlaps('[{"a":1}]', '{"a":1}') as c1,
638 json_overlaps('[{"a":1}]', '[{"a":1}]') as c2;
639 select json_overlaps('[{}]', '{}') as c1;
640 select json_overlaps('{}', '[{}]') as c1;
641 select json_overlaps('[{}]', '{"a":1, "b":2}') as c1;
642 select json_overlaps('[{}]', '1') as c1;
643 select json_overlaps("1","1") as c1;
644 select json_overlaps("true","false") as c1;
645 select json_overlaps("null","null") as c1;
646 select json_overlaps("123",'{"asd":123}') as c1;
647
648
649 create table t(j json, key ((cast(j->'$[*]' as unsigned array))));
650 select * from t where json_overlaps('[]', j->'$[*]');
651 select * from t where json_contains(j->'$[*]', '[]');
652 drop table t;
653
654 create table t1 (id int not null key auto_increment, f1 json);
655 create index i1 on t1((cast(f1->"$[*]" as signed array)));
656 show create table t1;
657 insert into t1(f1) values
658 (cast('[1,3]' as json)),
659 (cast(2 as json)),
660 (cast('[3,3,4,4,7]' as json)),
661 (cast(4 as json)),
662 (cast('[5,7]' as json)),
663 (cast(6 as json)),
664 (cast(7 as json)),
665 (cast('[8,4,3,5]' as json)),
666 (cast('[9,2,7]' as json)),
667 (cast('null' as json));
668 analyze table t1;
669
670 select * from t1 where json_overlaps(cast('[4,6]' as json), f1->'$[*]');
671 explain select * from t1 where json_overlaps(cast('[4,6]' as json), f1->'$[*]');
672 select * from t1 where json_overlaps(f1->'$[*]', cast('[2,5]' as json));
673 explain select * from t1 where json_overlaps(f1->'$[*]', cast('[2,5]' as json));
674 drop table t1;
675
676 # Test that unique filter properly flushes on disk
677
678 create table t1 (f1 json, id varchar(255) as (f1->"$[0]") stored not null primary key );
679 create index i1 on t1((cast(f1->"$[*]" as unsigned array)));
680 show create table t1;
681 insert into t1(f1) values
682 (cast('[1,3]' as json)), (cast(2 as json)), (cast('[3,3,4,4,7]' as json)),
683 (cast(4 as json)), (cast('[100,5,7]' as json)), (cast(6 as json)),
684 (cast(7 as json)), (cast('[8,4,3,5]' as json)), (cast('[5,6,7]' as json)),
685 (cast('[9,2,7]' as json));
686 insert into t1(f1) values
687 (cast('[11,3]' as json)), (cast(12 as json)), (cast('[13,3,4,4,7]' as json)),
688 (cast(14 as json)), (cast('[0,5,7]' as json)), (cast(16 as json)),
689 (cast(17 as json)), (cast('[18,4,3,5]' as json)), (cast('[15,6,7]' as json)),
690 (cast('[19,2,7]' as json));
691 insert into t1(f1) values
692 (cast('[21,3]' as json)), (cast(22 as json)), (cast('[23,3,4,4,7]' as json)),
693 (cast(24 as json)), (cast('[10,5,7]' as json)), (cast(26 as json)),
694 (cast(27 as json)), (cast('[28,4,3,5]' as json)), (cast('[25,6,7]' as json)),
695 (cast('[29,2,7]' as json));
696 insert into t1(f1) values
697 (cast('[31,3]' as json)), (cast(32 as json)), (cast('[33,3,4,4,7]' as json)),
698 (cast(34 as json)), (cast('[20,5,7]' as json)), (cast(36 as json)),
699 (cast(37 as json)), (cast('[38,4,3,5]' as json)), (cast('[35,6,7]' as json)),
700 (cast('[39,2,7]' as json));
701 insert into t1(f1) values
702 (cast('[41,3]' as json)), (cast(42 as json)), (cast('[43,3,4,4,7]' as json)),
703 (cast(44 as json)), (cast('[30,5,7]' as json)), (cast(46 as json)),
704 (cast(47 as json)), (cast('[48,4,3,5]' as json)), (cast('[45,6,7]' as json)),
705 (cast('[49,2,7]' as json));
706 insert into t1(f1) values
707 (cast('[51,3]' as json)), (cast(52 as json)), (cast('[53,3,4,4,7]' as json)),
708 (cast(54 as json)), (cast('[40,5,7]' as json)), (cast(56 as json)),
709 (cast(57 as json)), (cast('[58,4,3,5]' as json)), (cast('[55,6,7]' as json)),
710 (cast('[59,2,7]' as json));
711 analyze table t1;
712
713 set @save_sbs= @@sort_buffer_size;
714 set @@sort_buffer_size=32768;
715
716 let $query= select * from t1 force index(i1)
717 where json_overlaps(f1->'$[*]', '[4,3,7]') order by id;
718 --eval $query
719 --eval explain $query
720 --eval prepare stmt from "$query"
721 execute stmt;
722 deallocate prepare stmt;
723 let $query= select count(*) from t1 force index(i1)
724 where json_overlaps(f1->'$[*]', '[4,3,7]') order by id;
725 --eval $query
726 --eval explain $query
727 --eval prepare stmt from "$query"
728 execute stmt;
729 deallocate prepare stmt;
730
731 #test unique filter''s flushing to disk
732 # test that unique filter is properly reset
733 # join of 2 tables, mv is 2nd
734
735 let $query= select /*+ NO_BNL(t1,tt) JOIN_ORDER(t1,tt) */ * from t1 join t1 as tt force index(i1)
736 where json_overlaps(tt.f1->'$[*]', '[4,3,7]') and t1.id in (1,2) order by t1.id,tt.id;
737 --eval $query
738 --eval explain $query
739 --eval prepare stmt from "$query"
740 execute stmt;
741 deallocate prepare stmt;
742
743 let $query= select /*+ NO_BNL(t1,tt) JOIN_ORDER(t1,tt) */ count(*) from t1 join t1 as tt force index(i1)
744 where json_overlaps(tt.f1->'$[*]', '[4,3,7]') and t1.id in (1,2) order by t1.id,tt.id;
745 --eval $query
746 --eval explain $query
747 --eval prepare stmt from "$query"
748 execute stmt;
749 deallocate prepare stmt;
750
751 set @@sort_buffer_size= @save_sbs;
752
753 # Test that unique filter works with SE defined MRR implementation
754 set @save_opt_sw= @@optimizer_switch;
755 #set @@optimizer_switch="mrr_cost_based=off";
756 select * from t1 force index(i1)
757 where json_overlaps(f1->"$[*]", "[4,3,7]") order by id;
758 explain select * from t1 force index(i1)
759 where json_overlaps(f1->"$[*]", "[4,3,7]") order by id;
760
761 select count(*) from t1 force index(i1)
762 where json_overlaps(f1->"$[*]", "[4,3,7]") order by id;
763 explain select count(*) from t1 force index(i1)
764 where json_overlaps(f1->"$[*]", "[4,3,7]") order by id;
765
766 drop table t1;
767
768 create table t1 (f1 json,
769 id varchar(255) as (f1->"$[0]") stored not null primary key );
770 create index i1 on t1((cast(f1->"$[*]" as unsigned array)));
771 show create table t1;
772 insert into t1(f1) values
773 (cast('[1,3]' as json)), (cast(2 as json)), (cast('[3,3,4,4,7]' as json)),
774 (cast(4 as json)), (cast('[0,5,7]' as json)), (cast(6 as json)),
775 (cast(7 as json)), (cast('[8,4,3,5]' as json)), (cast('[5,6,7]' as json)),
776 (cast('[9,2,7]' as json));
777 analyze table t1;
778 set @@optimizer_switch="mrr_cost_based=off";
779 select * from t1 force index(i1)
780 where json_overlaps(f1->"$[*]", "[4,3,7]") order by id;
781 explain select * from t1 force index(i1)
782 where json_overlaps(f1->"$[*]", "[4,3,7]") order by id;
783 drop table t1;
784
785 create table t(j json, key k ((cast(j->'$[*]' as unsigned array))));
786 insert into t values('[2,2,3]');
787 select * from t where 1 member of (j);
788 explain select * from t where 1 member of (j);
789 drop table t;
790
791 create table t1 (j JSON, KEY mv_idx ((CAST(j->'$[*]' AS DATETIME ARRAY))));
792 --error ER_WARN_DATA_TRUNCATED_FUNCTIONAL_INDEX
793 insert into t1(j) values("[ 1 ]") ;
794 drop table t1;
795
796 create table t2 (j json default (cast('[9,-1]' as json)),
797 key mv_idx ((cast(j->'$[*]' as unsigned array))));
798 --error ER_WARN_DATA_OUT_OF_RANGE_FUNCTIONAL_INDEX
799 insert into t2 values ();
800 drop table t2;
801
802 create table t1 (j JSON DEFAULT( '["foobar"]' ));
803 insert into t1 values ();
804 --error ER_INVALID_JSON_VALUE_FOR_FUNC_INDEX
805 alter table t1 add index mv_idx((CAST(j->'$[0]' AS UNSIGNED ARRAY)));
806 drop table t1;
807
808 create table t1 (j JSON, KEY mv_idx ((cast(j->'$[*]' AS BINARY(10) ARRAY))));
809 --error ER_FUNCTIONAL_INDEX_DATA_IS_TOO_LONG
810 insert into t1(j) values('["asdffggasdasdasdasd"]');
811 drop table t1;
812
813 CREATE TABLE t1 (j JSON DEFAULT (JSON_ARRAY(PI())),
814 KEY mv_idx((CAST(j->'$[0]' AS DECIMAL(10,4) ARRAY))));
815 INSERT INTO t1 VALUES();
816 DROP TABLE t1;
817
818 create table t1 (j JSON, KEY Mv_idx ((cast(j->'$[*]' AS BINARY(10) ARRAY))) ) ;
819 --error ER_FUNCTIONAL_INDEX_DATA_IS_TOO_LONG
820 insert into t1(j) values('["asdffggasdasdasdasd"]');
821 create table t2 (j JSON, KEY Mv_idx ((cast(j->'$[*]' AS CHAR(10) ARRAY))) ) ;
822 --error ER_FUNCTIONAL_INDEX_DATA_IS_TOO_LONG
823 insert into t2(j) values('["asdffggasdasdasdasd"]');
824 drop table t1,t2;
825
826 create table t1(j json);
827 insert into t1 values (json_array(1,2,3));
828 insert into t1 values (json_array(1,2,3, null));
829 select * from t1 where json_overlaps(j->'$[*]', '[2,3,4]');
830 select * from t1 where json_overlaps(j->'$[*]', '[2,3,4, null]');
831
832 --echo Lookups of single SON null value can't use index, only table scan
833 select * from t1 where json_overlaps(j->'$[*]', 'null');
834 explain select * from t1 where json_overlaps(j->'$[*]', 'null');
835 select * from t1 where json_overlaps(j->'$[*]', '[null]');
836 explain select * from t1 where json_overlaps(j->'$[*]', '[null]');
837
838 select * from t1 where cast('null' as json) member of(j->'$[*]');
839 explain select * from t1 where cast('null' as json) member of(j->'$[*]');
840 select * from t1 where cast('[null]' as json) member of(j->'$[*]');
841 explain select * from t1 where cast('[null]' as json) member of(j->'$[*]');
842
843 --error ER_INVALID_JSON_VALUE_FOR_FUNC_INDEX
844 alter table t1 add key k ((cast(j->'$[*]' as unsigned array)));
845 delete from t1 where cast('null' as json) member of(j->'$[*]');
846 alter table t1 add key k ((cast(j->'$[*]' as unsigned array)));
847 select * from t1 where json_overlaps(j->'$[*]', '[2,3,4, null]');
848 explain select * from t1 where json_overlaps(j->'$[*]', '[2,3,4, null]');
849 select * from t1 where json_overlaps(j->'$[*]', '[2,3,4, "asd"]');
850 explain select * from t1 where json_overlaps(j->'$[*]', '[2,3,4, "asd"]');
851
852 explain select * from t1 where json_overlaps(j->'$[*]', '{"a":1}');
853 explain select * from t1 where json_contains(j->'$[*]', '{"a":1}');
854
855 drop table t1;
856
857 create table if not exists t1 (
858 j2 json default (cast('[9,8,0,1]' as json)),
859 j3 json default (cast('["foobar"]' as json)),
860 key mv_idx_unsigned (( cast(j2->'$[*]' as unsigned array) ))
861 );
862 insert into t1(j3) values(cast('[ 1,2,3,4 ]' as json));
863 insert into t1(j2, j3) select j2, j3 from t1;
864 insert into t1(j2, j3) select j2, j3 from t1;
865 insert into t1(j2, j3) select j2, j3 from t1;
866 insert into t1(j2, j3) select j2, j3 from t1;
867 analyze table t1;
868 explain select count(*) from t1 force index(mv_idx_unsigned);
869
870 drop table t1;
871
872 create table if not exists t1 (
873 j7 json default (json_array(time('11:11:11'))),
874 j8 json default (json_array(-1,-2,9)),
875 key mv_idx_time (( cast(j7->'$[*]' as time array))));
876 insert into t1() values() ;
877 select * from t1 where cast('11:11:11' as time) member of (j7->'$[*]');
878 alter table t1 add column ttt int, algorithm = copy;
879 select * from t1 where cast('11:11:11' as time) member of (j7->'$[*]');
880 explain select * from t1 where cast('11:11:11' as time) member of (j7->'$[*]');
881 drop table t1;
882
883 set @save_mode= @@sql_mode;
884 set sql_mode='';
885 create table t1 (j json, key mv_idx_time ((cast(j->'$[*]' as time array))));
886 insert into t1 values(json_array(cast('2018-01-03' as date),
887 cast( '2018-01-01' as date)));
888 drop table t1;
889
890 create table t(j json, key ((cast(j->'$[*]' as time array))));
891 insert into t values (json_array(timestamp'2001-01-01 00:00:00',
892 timestamp'2002-02-02 00:00:02',
893 timestamp'2003-03-03 00:00:00'));
894 drop table t;
895 set @@sql_mode= @save_mode;
896
897 --echo #
898 --echo # Bug#28752637: SIG11 IN JOIN::UPDATE_DEPEND_MAP() AT SQL/SQL_OPTIMIZER.CC
899 --echo #
900 CREATE TABLE t1 (
901 j3 JSON, KEY mv_idx_char (( CAST(j3->'$[*]' AS CHAR(10) ARRAY) )));
902 INSERT INTO t1 SELECT * FROM t1 WHERE null MEMBER OF ( j3 -> '$[*]' ) ;
903 --echo Shouldn't use ref access
904 EXPLAIN SELECT * FROM t1 WHERE null MEMBER OF ( j3 -> '$[*]' ) ;
905 DROP TABLE t1;
906 --echo #
907 --echo # Bug#28876519: ASSERT FAILURE:ROW0INS.CC:266:UPDATE->N_FIELDS == 0
908 --echo #
909 CREATE TABLE t1 (f1 JSON, UNIQUE KEY i1((CAST(f1->"$[*]" AS CHAR(10) ARRAY))));
910 INSERT INTO t1(f1) VALUES(CAST('["abc", "abc "]' AS JSON));
911 SELECT * FROM t1 WHERE "abc" MEMBER OF (f1->"$[*]");
912 EXPLAIN SELECT * FROM t1 WHERE "abc" MEMBER OF (f1->"$[*]");
913 SELECT * FROM t1 WHERE "abc " MEMBER OF (f1->"$[*]");
914 EXPLAIN SELECT * FROM t1 WHERE "abc " MEMBER OF (f1->"$[*]");
915 DELETE FROM t1;
916 INSERT INTO t1(f1) VALUES(CAST('["abc"]' AS JSON));
917 INSERT INTO t1(f1) VALUES(CAST('["abc "]' AS JSON));
918 DROP TABLE t1;
919 CREATE TABLE t1 (f1 JSON, KEY i1((CAST(f1->"$[*]" AS CHAR(10) ARRAY))));
920 INSERT INTO t1(f1) VALUES(CAST('["abc"]' AS JSON));
921 INSERT INTO t1(f1) VALUES(CAST('["abc "]' AS JSON));
922 SELECT * FROM t1 WHERE "abc" MEMBER OF (f1->"$[*]");
923 SELECT * FROM t1 WHERE "abc " MEMBER OF (f1->"$[*]");
924 DROP TABLE t1;
925 CREATE TABLE t1 (f1 JSON, KEY i1((CAST(f1->"$[*]" AS BINARY(10) ARRAY))));
926 INSERT INTO t1(f1) VALUES(CAST('["abc"]' AS JSON));
927 INSERT INTO t1(f1) VALUES(CAST('["abc "]' AS JSON));
928 SELECT * FROM t1 WHERE "abc" MEMBER OF (f1->"$[*]");
929 SELECT * FROM t1 WHERE "abc " MEMBER OF (f1->"$[*]");
930 DROP TABLE t1;
931 --error ER_NOT_SUPPORTED_YET
932 CREATE TABLE t1 (f1 JSON,
933 KEY i1((CAST(f1->"$[*]" AS CHAR(10) CHARACTER SET UTF32 ARRAY))));
934 SET NAMES latin1;
935 CREATE TABLE t1 (f1 JSON, KEY i1((CAST(f1->"$[*]" AS CHAR(10) ARRAY))));
936 INSERT INTO t1(f1) VALUES(CAST('["abc"]' AS JSON));
937 INSERT INTO t1(f1) VALUES(CAST('["abc "]' AS JSON));
938 SELECT * FROM t1 WHERE "abc" MEMBER OF (f1->"$[*]");
939 SELECT * FROM t1 WHERE "abc " MEMBER OF (f1->"$[*]");
940 DROP TABLE t1;
941 SET NAMES utf8mb4;
942
943 --echo #
944 --echo # Bug #28893289: DUPLICATE RESULT OF JSON_OVERLAPS() WITH UNIQUE MV INDEX
945 --echo #
946 CREATE TABLE t1 (j JSON, UNIQUE KEY mv_idx ((CAST(j->'$[*]' AS SIGNED ARRAY))));
947 INSERT INTO t1(j) VALUES ('[2,2,2]');
948 SELECT * FROM t1 WHERE JSON_OVERLAPS(CAST('[2,2]' AS JSON), j->'$[*]');
949 EXPLAIN SELECT * FROM t1 WHERE JSON_OVERLAPS(CAST('[2,2]' AS JSON), j->'$[*]');
950 DROP TABLE t1;
951
952 --echo #
953 --echo # Bug#28929529: SERVER CRASH WITH MEMBER OF() FUNCTION
954 --echo #
955 CREATE TABLE t1 ( col_int INT) ;
956 INSERT INTO t1 VALUES (1);
957 SELECT col_int FROM t1 WHERE col_int MEMBER OF('[98989,67976]');
958 EXPLAIN SELECT col_int FROM t1 WHERE col_int MEMBER OF('[98989,67976]');
959 DROP TABLE t1;
960
961 --echo #
962 --echo # Bug#28935260: SIG11 IN HANDLER::HA_EXTRA() AT SQL/HANDLER.CC
963 --echo #
964 CREATE TABLE t1 (
965 col_key VARCHAR(1) DEFAULT NULL,
966 col_jsn json DEFAULT NULL,
967 KEY idx_col_key (col_key),
968 KEY mv_idx ((CAST(col_jsn->'$[*]' AS UNSIGNED ARRAY))));
969 INSERT INTO t1 VALUES ('i','[0]'), ('E','[0]'), ('l','[0]');
970 SELECT col_key FROM t1 WHERE
971 (JSON_OVERLAPS(CAST(9444 AS JSON),col_jsn->'$[*]')) OR col_key = 't';
972 EXPLAIN SELECT col_key FROM t1 WHERE
973 (JSON_OVERLAPS(CAST(9444 AS JSON),col_jsn->'$[*]')) OR col_key = 't';
974 DROP TABLE t1;
975
976 --echo #
977 --echo # Bug#28960833: SIG11 IN FILESORT::MAKE_SORTORDER() AT SQL/FILESORT.CC
978 --echo #
979 CREATE TABLE C1 (
980 pk int(11) NOT NULL AUTO_INCREMENT KEY,
981 col_int int(11) DEFAULT NULL) ;
982 INSERT INTO C1 VALUES (1,2);
983 --skip_if_hypergraph # Errors out on a different row, changing the error text.
984 --error ER_INVALID_JSON_TEXT_IN_PARAM
985 SELECT SUM( DISTINCT table1.col_int ) AS field1,
986 MAX( table1.col_int ) AS field3
987 FROM C1 AS table1 LEFT JOIN C1 AS table2 ON 1
988 WHERE CAST(RAND()*100 AS JSON) MEMBER OF( 'sTfW' )
989 GROUP BY table1.pk ORDER BY field1, field3 ;
990 DROP TABLE C1;
991
992 --echo #
993 --echo # Bug#28959908: REPEATED EXECUTION OF SELECT RETURNS DIFFERENT RESULT
994 --echo #
995 CREATE TABLE t1 (
996 col_int_key int(11) DEFAULT NULL,
997 col_jsonn json DEFAULT NULL,
998 KEY mv_idx ((cast(col_jsonn->'$[*]' as char(40) array)))
999 );
1000
1001 INSERT INTO t1 VALUES (NULL,'[1]'), (4,'[1]'), (1,'[2]');
1002
1003 CREATE TABLE t2(col_int int(11));
1004
1005 INSERT INTO t2 VALUES (1), (2), (3), (11), (12);
1006 ANALYZE TABLE t1,t2;
1007
1008 EXPLAIN SELECT t1.col_int_key AS field1, t2.col_int AS field2 FROM t2 LEFT
1009 JOIN t1 ON 1 WHERE (CAST("1" AS JSON) MEMBER OF( t1.col_jsonn->'$[*]'));
1010
1011 --sorted_result
1012 SELECT t1.col_int_key AS field1, t2.col_int AS field2 FROM t2 LEFT
1013 JOIN t1 ON 1 WHERE (CAST("1" AS JSON) MEMBER OF( t1.col_jsonn->'$[*]'));
1014
1015 DROP TABLE t1,t2;
1016
1017 --echo #
1018 --echo # Bug#29111067: SIG6 IN HANDLER::HA_EXTRA() AT SQL/HANDLER.CC
1019 --echo #
1020
1021 CREATE TABLE c1 (
1022 col_int int(11) DEFAULT NULL,
1023 col_jsonn json DEFAULT NULL,
1024 KEY mv_idx_char
1025 ((cast(json_extract(`col_jsonn`,_utf8mb4'$[2][*]') as char(10) array))),
1026 KEY mv_idx_time
1027 ((cast(json_extract(`col_jsonn`,_utf8mb4'$[6][*]') as time array)))
1028 );
1029
1030 INSERT INTO c1 VALUES (266852963,'[[1835802859, 1212466474, 1958770846],
1031 [140577309, 37575131, 1480381571, 1610621995], ["6PivylM", "D4MJJ1N", "M0KX",
1032 "0ELw", "sIgqeN", "HHOzYx8vo", "u7htig76J0", "SVd7N"], [1, 0, 0, 1],
1033 ["2027-03-28 09:30:48", "1981-12-19 07:23:59"], ["1992-02-21"], ["04:08:11",
1034 "03:14:43", "16:15:47", "08:35:02", "01:54:19", "12:23:50", "23:19:54",
1035 "03:19:05", "06:20:09"]]'),(827337874,'[[-289358475], [1365589563,
1036 1136730961, 1355533008, 623792249, 1492351200], ["quU8k89", "b", "5",
1037 "qUCBav5wm", "4qw", "48esnGIpJz", "OHFde8", "MvWemE3ON", "kfWRfhif"], [0],
1038 ["2029-08-21 11:07:49", "2010-12-30 13:23:27", "1982-08-13 20:37:26"],
1039 ["2003-08-19", "1996-06-18", "2010-12-29", "2023-02-26", "1994-03-09"],
1040 ["23:05:01"]]'),(1335355510,'[[-565849481, -24516610, -2120954195,
1041 1161359986, -1413651589], [], ["G", "G", "G", "2h", "AjfPYjosm", "gq", "qwX",
1042 "iap94E", "S", "BHLb3XfcD", "kyJSC"], [1, 1, 1, 1, 1, 1],
1043 ["2021-11-20 06:14:11"], ["1998-01-19", "2025-01-28", "2036-12-07",
1044 "1992-01-10",
1045 "1992-02-13", "2000-10-31", "1976-10-16", "1988-01-15"], ["07:24:55",
1046 "18:26:39", "01:03:31", "10:01:50", "10:32:40"]]'),(717723030,'[[721357711,
1047 443763172, 1346794776, 1228470359, 810183154, 1544293472, 182301524],
1048 [1274610824, 1044052482, 1788179665, 1649506330, 969248599, 1368415902,
1049 15095787], ["65h", "zCOtWhY", "vtIr", "9", "b7vckqds", "3RGQx", "jW1",
1050 "r0CqU"], [1, 0, 1, 1, 1], ["2010-02-23 02:29:37", "2011-07-17 16:44:14",
1051 "1987-08-17 03:47:04", "2012-05-24 09:09:40"], ["1997-01-12", "1992-10-26",
1052 "1975-05-29", "1988-05-02", "1984-07-24", "2001-06-21"], ["01:16:30",
1053 "12:10:12", "16:52:37", "11:15:48"]]'),(-1455798357,'[[1160950873,
1054 -1845559138, -591619735], [1947296343, 1928779367, 2046082843, 1714804343,
1055 1903096605, 230458028], ["8j8RNBGsci", "QWU2x", "b4a7", "uIcTSlagrr",
1056 "rpCiT2w"], [0, 1, 1], ["1971-10-25 17:55:12", "2021-04-07 00:09:33",
1057 "2015-06-11 05:08:07", "1981-08-25 16:50:38", "2035-10-19 05:23:43"],
1058 ["2016-03-08", "2034-05-14", "2026-11-18", "1988-12-08", "2027-03-26",
1059 "2003-05-09"], ["01:45:07", "08:13:35",
1060 "22:51:45"]]'),(-205841342,'[[-286383407, -2132945193, -1440807096,
1061 712069046], [329306555, 927040824, 397088016, 951650479, 1320369164,
1062 2135904858], ["d0", "lG2", "uY", "6K9"], [1, 0, 0, 0, 1, 1],
1063 ["1980-03-14 17:42:17", "2009-05-12 20:16:27", "1991-10-09 13:20:26",
1064 "1973-08-03 01:28:46", "2027-02-28 14:26:25", "2014-07-24 05:46:40",
1065 "2035-04-30 15:17:48"], [], ["06:32:03"]]');
1066 analyze table c1;
1067
1068 SELECT col_int FROM c1 WHERE
1069 (JSON_CONTAINS( col_jsonn-> '$[2][*]' , JSON_ARRAY( '2030-11-16' ) ) ) OR
1070 JSON_OVERLAPS(JSON_ARRAY('12:38:26', '17:42:23'), col_jsonn->'$[6][*]');
1071 EXPLAIN SELECT col_int FROM c1 WHERE
1072 (JSON_CONTAINS( col_jsonn-> '$[2][*]' , JSON_ARRAY( '2030-11-16' ) ) ) OR
1073 JSON_OVERLAPS(JSON_ARRAY('12:38:26', '17:42:23'), col_jsonn->'$[6][*]');
1074 DROP TABLE c1;
1075
1076 --echo #
1077 --echo # Bug#29114081: ERROR WHILE READING TABLE
1078 --echo #
1079 CREATE TABLE a (pk integer auto_increment key, col_varchar_key varchar(1));
1080 CREATE TABLE c (pk integer auto_increment key, col_varchar varchar(1) ,
1081 col_varchar_key varchar(1));
1082
1083 ALTER TABLE a ADD COLUMN col_jsonn JSON;
1084 ALTER TABLE a
1085 ADD INDEX mv_idx_binary ((CAST(col_jsonn->'$[*]' AS BINARY(10) ARRAY)));
1086
1087 ALTER TABLE c ADD COLUMN col_jsonn JSON;
1088 ALTER TABLE c
1089 ADD INDEX mv_idx_binary ((CAST(col_jsonn->'$[*]' AS BINARY(10) ARRAY)));
1090
1091 insert into c values (DEFAULT,'8','q','[0,0,1,1,0,1]'),
1092 (DEFAULT,'T','N','[1,0,0,0,0,1,0]'), (DEFAULT,'H','p','[0,0,1]'),
1093 (DEFAULT,'Z','t','[1]'), (DEFAULT,'T','v','[0]'), (DEFAULT,'m','N','[1,1,0]'),
1094 (DEFAULT,'4','o','[1,1,0,1]'), (DEFAULT,'1','5','[1,0,1,0,0,0]');
1095 analyze table c;
1096
1097 --error ER_INVALID_JSON_TEXT_IN_PARAM
1098 SELECT alias1 . pk AS field1 , alias2 . pk AS field3
1099 FROM ( c AS alias1 LEFT JOIN c AS alias2
1100 ON ( alias1 . col_varchar = alias2 . col_varchar_key ) )
1101 WHERE (
1102 EXISTS ( ( SELECT sq2_alias2 . col_varchar_key AS sq2_field1
1103 FROM a AS sq2_alias2
1104 WHERE JSON_OVERLAPS(CAST('TOoqLsu' AS JSON),sq2_alias2.col_jsonn->'$[*]'))))
1105 AND ( alias1 . pk = alias2 . pk AND alias1 . pk = 8 );
1106
1107 SELECT alias1 . pk AS field1 , alias2 . pk AS field3
1108 FROM ( c AS alias1 LEFT JOIN c AS alias2
1109 ON ( alias1 . col_varchar = alias2 . col_varchar_key ) )
1110 WHERE (
1111 EXISTS ( ( SELECT sq2_alias2 . col_varchar_key AS sq2_field1
1112 FROM a AS sq2_alias2
1113 WHERE JSON_OVERLAPS(CAST('"TOoqLsu"' AS JSON),sq2_alias2.col_jsonn->'$[*]'))))
1114 AND ( alias1 . pk = alias2 . pk AND alias1 . pk = 8 );
1115
1116 EXPLAIN SELECT alias1 . pk AS field1 , alias2 . pk AS field3
1117 FROM ( c AS alias1 LEFT JOIN c AS alias2
1118 ON ( alias1 . col_varchar = alias2 . col_varchar_key ) )
1119 WHERE (
1120 EXISTS ( ( SELECT sq2_alias2 . col_varchar_key AS sq2_field1
1121 FROM a AS sq2_alias2
1122 WHERE JSON_OVERLAPS(CAST('"TOoqLsu"' AS JSON),sq2_alias2.col_jsonn->'$[*]'))))
1123 AND ( alias1 . pk = alias2 . pk AND alias1 . pk = 8 );
1124
1125 DROP TABLE a,c;
1126
1127 --echo #
1128 --echo # Bug#29303026: TOO MANY RESULTS FROM UNIQUE MULTI-VALUE INDEX
1129 --echo #
1130 create table t (j json, unique key ((cast(j->'$[*]' as char(10) array))));
1131 insert into t values ('[1,"1"]');
1132 select * from t where json_overlaps('["1",1]', j->'$[*]');
1133 explain select * from t where json_overlaps('["1",1]', j->'$[*]');
1134 drop table t;
1135
1136 --echo #
1137 --echo # Bug#29582655: ASSERTION FAILURE: `!THD->IS_ERROR()'
1138 --echo #
1139 CREATE TABLE t1 (pk INT primary key, col_jsonn JSON, INDEX
1140 mv_idx((CAST(col_jsonn->'$[0][*]' AS BINARY(10) ARRAY))));
1141 CREATE TABLE t2 (pk INT primary key, col_jsonn JSON);
1142 --skip_if_hypergraph # Does not evaluate the condition during optimization.
1143 --error ER_INVALID_JSON_TEXT_IN_PARAM
1144 SELECT JSON_OVERLAPS( '0' , t1. col_jsonn-> '$[0][*]' ) FROM t1 LEFT JOIN t2
1145 ON t1.pk = JSON_OVERLAPS( 'OJNRaUr' , JSON_ARRAY( 'Q' , 'qX' ) ) WHERE t2 .
1146 pk = 1;
1147 DROP TABLE t1,t2;
1148
1149 --echo #
1150 --echo # Bug#29752056: COLLECTION.FIND() FAILS FOR DATE TYPE WHEN IN IS USED
1151 --echo #
1152 CREATE TABLE t1 (doc JSON,_id VARBINARY(32)
1153 GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) STORED PRIMARY
1154 KEY);
1155
1156 ALTER TABLE t1 ADD INDEX dateArrayIndex
1157 ((CAST(JSON_EXTRACT(doc,'$.dateField') AS DATE ARRAY)));
1158 ALTER TABLE t1 ADD INDEX datetimeArrayIndex
1159 ((CAST(JSON_EXTRACT(doc,'$.datetimeField') AS DATETIME ARRAY)));
1160 ALTER TABLE t1 ADD INDEX timeArrayIndex
1161 ((CAST(JSON_EXTRACT(doc,'$.timeField') AS TIME ARRAY)));
1162
1163 INSERT INTO t1 (doc) VALUES
1164 ('{"dateField":["2019-1-1","2019-2-1","2019-3-1"],
1165 "datetimeField":["2019-12-29 23:59:59","2019-12-30 23:59:59",
1166 "2019-12-31 23:59:59"],
1167 "timeField":["10:10","11:10","12:10"],
1168 "_id": "00005cd4075c0000000000000087"}'),
1169 ('{"dateField":["2019-1-2","2019-2-2","2019-3-2"],
1170 "datetimeField":["2018-12-29 23:59:59","2018-12-30 23:59:59",
1171 "2018-12-31 23:59:59"],
1172 "timeField":["10:11","11:11","12:11"],
1173 "_id": "00005cd4075c0000000000000088"}'),
1174 ('{"dateField":["2019-1-3","2019-2-3","2019-3-3"],
1175 "datetimeField":["2017-12-29 23:59:59","2017-12-30 23:59:59",
1176 "2017-12-31 23:59:59"],
1177 "timeField":["10:12","11:12","12:12"],
1178 "_id": "00005cd4075c0000000000000089"}');
1179 ANALYZE TABLE t1;
1180 --echo # DATE
1181 SELECT doc->'$.dateField' FROM t1 WHERE
1182 JSON_CONTAINS(doc->'$.dateField',JSON_QUOTE('2019-2-1'));
1183
1184 SELECT doc->'$.dateField' FROM t1 IGNORE KEY(dateArrayIndex) WHERE
1185 JSON_CONTAINS(doc->'$.dateField',JSON_QUOTE('2019-2-1'));
1186
1187 --echo # Empty result as $.dateField is string and index isn't used
1188 SELECT doc->'$.dateField' FROM t1 IGNORE KEY(dateArrayIndex) WHERE
1189 JSON_CONTAINS(doc->'$.dateField', CAST(CAST('2019-2-1' AS DATE) AS JSON));
1190
1191 EXPLAIN SELECT doc->'$.dateField' FROM t1 WHERE
1192 JSON_CONTAINS(doc->'$.dateField',JSON_QUOTE('2019-2-1'));
1193
1194 SELECT doc->'$.dateField' FROM t1 WHERE
1195 JSON_CONTAINS(doc->'$.dateField',JSON_ARRAY('2019-2-1','2019-3-1'));
1196
1197 EXPLAIN SELECT doc->'$.dateField' FROM t1 WHERE
1198 JSON_CONTAINS(doc->'$.dateField',JSON_ARRAY('2019-2-1','2019-3-1'));
1199
1200 SELECT doc->'$.dateField' FROM t1 WHERE
1201 '2019-2-1' MEMBER OF (doc->'$.dateField');
1202
1203 EXPLAIN SELECT doc->'$.dateField' FROM t1 WHERE
1204 '2019-2-1' MEMBER OF (doc->'$.dateField');
1205
1206 SELECT doc->'$.dateField' FROM t1 WHERE
1207 DATE'2019-2-1' MEMBER OF (doc->'$.dateField');
1208
1209 EXPLAIN SELECT doc->'$.dateField' FROM t1 WHERE
1210 DATE'2019-2-1' MEMBER OF (doc->'$.dateField');
1211
1212 SELECT doc->'$.dateField' FROM t1 WHERE
1213 'asd2019' MEMBER OF (doc->'$.dateField');
1214
1215 EXPLAIN SELECT doc->'$.dateField' FROM t1 WHERE
1216 'asd2019' MEMBER OF (doc->'$.dateField');
1217
1218 --echo # DATETIME
1219 SELECT doc->'$.datetimeField' FROM t1 WHERE
1220 JSON_CONTAINS(doc->'$.datetimeField',JSON_QUOTE('2017-12-29 23:59:59'));
1221
1222 SELECT doc->'$.datetimeField' FROM t1 IGNORE KEY(datetimeArrayIndex) WHERE
1223 JSON_CONTAINS(doc->'$.datetimeField',JSON_QUOTE('2017-12-29 23:59:59'));
1224
1225 --echo # Empty result as $.datetimeField is string and index isn't used
1226 SELECT doc->'$.datetimeField' FROM t1 IGNORE KEY(datetimeArrayIndex) WHERE
1227 JSON_CONTAINS(doc->'$.datetimeField',
1228 CAST(CAST('2017-12-29 23:59:59' AS DATETIME) AS JSON));
1229
1230 EXPLAIN SELECT doc->'$.datetimeField' FROM t1 WHERE
1231 JSON_CONTAINS(doc->'$.datetimeField',JSON_QUOTE('2017-12-29 23:59:59'));
1232
1233 SELECT doc->'$.datetimeField' FROM t1 WHERE
1234 JSON_CONTAINS(doc->'$.datetimeField',
1235 JSON_ARRAY('2017-12-29 23:59:59','2017-12-30 23:59:59'));
1236
1237 EXPLAIN SELECT doc->'$.datetimeField' FROM t1 WHERE
1238 JSON_CONTAINS(doc->'$.datetimeField',
1239 JSON_ARRAY('2017-12-29 23:59:59','2017-12-30 23:59:59'));
1240
1241 SELECT doc->'$.datetimeField' FROM t1 WHERE
1242 '2017-12-29 23:59:59' MEMBER OF (doc->'$.datetimeField');
1243
1244 EXPLAIN SELECT doc->'$.datetimeField' FROM t1 WHERE
1245 '2017-12-29 23:59:59' MEMBER OF (doc->'$.datetimeField');
1246
1247 SELECT doc->'$.datetimeField' FROM t1 WHERE
1248 TIMESTAMP'2017-12-29 23:59:59' MEMBER OF (doc->'$.datetimeField');
1249
1250 EXPLAIN SELECT doc->'$.datetimeField' FROM t1 WHERE
1251 TIMESTAMP'2017-12-29 23:59:59' MEMBER OF (doc->'$.datetimeField');
1252
1253 --echo # TIME
1254 SELECT doc->'$.timeField' FROM t1 WHERE
1255 JSON_CONTAINS(doc->'$.timeField',JSON_QUOTE('11:11'));
1256
1257 SELECT doc->'$.timeField' FROM t1 IGNORE KEY(timeArrayIndex) WHERE
1258 JSON_CONTAINS(doc->'$.timeField',JSON_QUOTE('11:11'));
1259
1260 --echo # Empty result as $.timeField is string and index isn't used
1261 SELECT doc->'$.timeField' FROM t1 IGNORE KEY(timeArrayIndex) WHERE
1262 JSON_CONTAINS(doc->'$.timeField', CAST(CAST('11:11' AS TIME) AS JSON));
1263
1264 EXPLAIN SELECT doc->'$.timeField' FROM t1 WHERE
1265 JSON_CONTAINS(doc->'$.timeField',JSON_QUOTE('11:11'));
1266
1267 SELECT doc->'$.timeField' FROM t1 WHERE
1268 JSON_CONTAINS(doc->'$.timeField',JSON_ARRAY('11:11','12:11'));
1269
1270 EXPLAIN SELECT doc->'$.timeField' FROM t1 WHERE
1271 JSON_CONTAINS(doc->'$.timeField',JSON_ARRAY('11:11','12:11'));
1272
1273 SELECT doc->'$.timeField' FROM t1 WHERE
1274 '11:11' MEMBER OF (doc->'$.timeField');
1275
1276 EXPLAIN SELECT doc->'$.timeField' FROM t1 WHERE
1277 '11:11' MEMBER OF (doc->'$.timeField');
1278
1279 SELECT doc->'$.timeField' FROM t1 WHERE
1280 TIME'11:11' MEMBER OF (doc->'$.timeField');
1281
1282 EXPLAIN SELECT doc->'$.timeField' FROM t1 WHERE
1283 TIME'11:11' MEMBER OF (doc->'$.timeField');
1284
1285 DROP TABLE t1;
1286
1287 CREATE TABLE t(i INT, j JSON);
1288 INSERT INTO t VALUES
1289 (1,'"2019-1-1"'),(2,'"2019-01-01"'),(3,CAST(DATE'2019-1-1' AS JSON));
1290 --echo # Untyped comparison
1291 SELECT * FROM t WHERE j->'$' = '2019-1-1';
1292 SELECT * FROM t WHERE j->'$' = DATE'2019-01-01';
1293 SELECT * FROM t WHERE j->'$' = '2019-01-1';
1294 CREATE INDEX idx ON t((CAST(j->'$' AS DATE)));
1295 --echo # Typed comparison using index's type
1296 SELECT * FROM t WHERE j->'$' = '2019-1-1';
1297 SELECT * FROM t WHERE j->'$' = DATE'2019-01-01';
1298 SELECT * FROM t WHERE j->'$' = '2019-01-1';
1299 ALTER TABLE t DROP INDEX idx;
1300 DELETE FROM t;
1301 INSERT INTO t VALUES
1302 (1,'"1:1"'),(2,'"01:1"'),(3,CAST(TIME'01:1' AS JSON));
1303 SELECT * FROM t WHERE j->'$' = '1:1';
1304 SELECT * FROM t WHERE j->'$' = TIME'1:1';
1305 SELECT * FROM t WHERE j->'$' = '1:01';
1306 CREATE INDEX idx ON t((CAST(j->'$' AS TIME)));
1307 --echo # Typed comparison using index's type
1308 SELECT * FROM t WHERE j->'$' = '1:1';
1309 SELECT * FROM t WHERE j->'$' = TIME'1:1';
1310 SELECT * FROM t WHERE j->'$' = '1:01';
1311 DROP TABLE t;
1312
1313 --echo #
1314 --echo # Bug#30227756: JSON.JSON_PERFSCHEMA FAILS ON WINDOWS
1315 --echo #
1316 CREATE TABLE t1 (
1317 j JSON DEFAULT (CAST('[9,8,0,1]' AS JSON)),
1318 KEY mv_idx ((CAST(j->'$[*]' AS UNSIGNED ARRAY))));
1319 INSERT INTO t1 VALUES();
1320 DROP TABLE t1;
1321 # This query should return zero rows unless some allocated JSON data
1322 # has not been freed. It used to return a row on Windows due to a
1323 # memory leak in the CREATE TABLE statement above.
1324 SELECT * FROM performance_schema.memory_summary_global_by_event_name
1325 WHERE event_name = 'memory/sql/JSON' AND
1326 (count_alloc <> count_free OR
1327 sum_number_of_bytes_alloc <> sum_number_of_bytes_free);
1328
1329 --echo #
1330 --echo # Bug#31301101 ASSERTION ".CONV_FIELD->HAS_CHARSET"
1331 --echo #
1332 CREATE TABLE t1(j char(1), KEY idxbn ((CAST('' AS CHAR(1) ARRAY))));
1333 ALTER TABLE t1 CONVERT TO CHARACTER SET latin1;
1334 DROP TABLE t1;
1335
1336 --echo #
1337 --echo # Bug#30838807: MULTI-VALUED INDEX ON JSON COLUMN IS NOT USED
1338 --echo #
1339 CREATE TABLE t(vc VARCHAR(10), j JSON DEFAULT (vc),
1340 KEY j_idx ((CAST(j AS UNSIGNED ARRAY))),
1341 KEY vc_idx ((CAST(vc AS UNSIGNED ARRAY))));
1342 INSERT INTO t(vc) VALUES ('[1,2,3]'), ('[2,3,4]'), ('[3,4,5]'), ('[4,5,6]');
1343 ANALYZE TABLE t;
1344
1345 --echo # Multi-valued index on a JSON column.
1346
1347 --skip_if_hypergraph # Multi-valued indexes are not supported yet.
1348 EXPLAIN FORMAT=TREE SELECT * FROM t WHERE JSON_OVERLAPS('[2,3]', j);
1349 --sorted_result
1350 SELECT * FROM t WHERE JSON_OVERLAPS('[2,3]', j);
1351
1352 --skip_if_hypergraph # Multi-valued indexes are not supported yet.
1353 EXPLAIN FORMAT=TREE SELECT * FROM t WHERE JSON_CONTAINS(j, '[2,3]');
1354 --sorted_result
1355 SELECT * FROM t WHERE JSON_CONTAINS(j, '[2,3]');
1356
1357 --skip_if_hypergraph # Multi-valued indexes are not supported yet.
1358 EXPLAIN FORMAT=TREE SELECT * FROM t WHERE 3 MEMBER OF (j);
1359 --sorted_result
1360 SELECT * FROM t WHERE 3 MEMBER OF (j);
1361
1362 --echo # Multi-valued index on a VARCHAR column.
1363
1364 --skip_if_hypergraph # Multi-valued indexes are not supported yet.
1365 EXPLAIN FORMAT=TREE SELECT * FROM t WHERE JSON_OVERLAPS('[2,3]', vc);
1366 --sorted_result
1367 SELECT * FROM t WHERE JSON_OVERLAPS('[2,3]', vc);
1368
1369 --skip_if_hypergraph # Multi-valued indexes are not supported yet.
1370 EXPLAIN FORMAT=TREE SELECT * FROM t WHERE JSON_CONTAINS(vc, '[2,3]');
1371 --sorted_result
1372 SELECT * FROM t WHERE JSON_CONTAINS(vc, '[2,3]');
1373
1374 --skip_if_hypergraph # Multi-valued indexes are not supported yet.
1375 EXPLAIN FORMAT=TREE SELECT * FROM t WHERE 3 MEMBER OF (vc);
1376 --sorted_result
1377 SELECT * FROM t WHERE 3 MEMBER OF (vc);
1378
1379 DROP TABLE t;
1380
1381 --echo # Bug#33275457: Fix multi-valued index
1382
1383 CREATE TABLE t1 (
1384 f1 VARCHAR(50) NOT NULL PRIMARY KEY,
1385 f2 JSON NOT NULL,
1386 INDEX idx2 ( (CAST(f2 AS CHAR(50) ARRAY)) )
1387 );
1388
1389 CREATE VIEW v1 AS
1390 SELECT *
1391 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1392 PATH '$')) AS ids;
1393
1394 INSERT INTO t1 VALUES ('foo', '["aa", "bb"]'), ('bar', '["xx", "yy"]');
1395
1396 ANALYZE TABLE t1;
1397
1398 EXPLAIN SELECT * FROM t1 WHERE 'xx' MEMBER OF (f2);
1399 EXPLAIN SELECT * FROM t1 WHERE json_contains(f2, '"xx"');
1400 EXPLAIN SELECT * FROM t1 WHERE json_overlaps(f2, '["xx", "zz"]');
1401
1402 EXPLAIN SELECT * FROM v1 WHERE 'xx' MEMBER OF (f2);
1403 EXPLAIN SELECT * FROM v1 WHERE json_contains(f2, '"xx"');
1404 EXPLAIN SELECT * FROM v1 WHERE json_overlaps(f2, '["xx", "zz"]');
1405
1406 EXPLAIN
1407 SELECT *
1408 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1409 PATH '$')) AS ids
1410 WHERE 'xx' MEMBER OF (f2);
1411
1412 EXPLAIN
1413 SELECT *
1414 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1415 PATH '$')) AS ids
1416 WHERE json_contains(f2, '"xx"');
1417
1418 EXPLAIN
1419 SELECT *
1420 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1421 PATH '$')) AS ids
1422 WHERE json_overlaps(f2, '["xx", "zz"]');
1423
1424 SELECT * FROM t1 WHERE 'xx' MEMBER OF (f2);
1425 SELECT * FROM t1 WHERE json_contains(f2, '"xx"');
1426 SELECT * FROM t1 WHERE json_overlaps(f2, '["xx", "zz"]');
1427
1428 SELECT * FROM v1 WHERE 'xx' MEMBER OF (f2);
1429 SELECT * FROM v1 WHERE json_contains(f2, '"xx"');
1430 SELECT * FROM v1 WHERE json_overlaps(f2, '["xx", "zz"]');
1431
1432 SELECT *
1433 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1434 PATH '$')) AS ids
1435 WHERE 'xx' MEMBER OF (f2);
1436
1437 SELECT *
1438 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1439 PATH '$')) AS ids
1440 WHERE json_contains(f2, '"xx"');
1441
1442 SELECT *
1443 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1444 PATH '$')) AS ids
1445 WHERE json_overlaps(f2, '["xx", "zz"]');
1446
1447 PREPARE stmt FROM 'EXPLAIN SELECT * FROM t1 WHERE ? MEMBER OF (f2)';
1448 SET @a='xx';
1449 EXECUTE stmt USING @a;
1450
1451 PREPARE stmt FROM 'EXPLAIN SELECT * FROM t1 WHERE json_contains(f2, ?)';
1452 SET @a='"xx"';
1453 EXECUTE stmt USING @a;
1454
1455 PREPARE stmt FROM 'EXPLAIN SELECT * FROM t1 WHERE json_overlaps(f2, ?)';
1456 SET @a='["xx", "cc"]';
1457 EXECUTE stmt USING @a;
1458
1459 PREPARE stmt FROM 'EXPLAIN SELECT * FROM v1 WHERE ? MEMBER OF (f2)';
1460 SET @a='xx';
1461 EXECUTE stmt USING @a;
1462
1463 PREPARE stmt FROM 'EXPLAIN SELECT * FROM v1 WHERE json_contains(f2, ?)';
1464 SET @a='"xx"';
1465 EXECUTE stmt USING @a;
1466
1467 PREPARE stmt FROM 'EXPLAIN SELECT * FROM v1 WHERE json_overlaps(f2, ?)';
1468 SET @a='["xx", "cc"]';
1469 EXECUTE stmt USING @a;
1470
1471 PREPARE stmt FROM '
1472 EXPLAIN
1473 SELECT *
1474 FROM t1, JSON_TABLE(f2, \'$[*]\'
1475 COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH \'$\')) AS ids
1476 WHERE ? MEMBER OF (f2)';
1477 SET @a='xx';
1478 EXECUTE stmt USING @a;
1479
1480 PREPARE stmt FROM '
1481 EXPLAIN
1482 SELECT *
1483 FROM t1, JSON_TABLE(f2, \'$[*]\'
1484 COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH \'$\')) AS ids
1485 WHERE json_contains(f2, ?)';
1486 SET @a='"xx"';
1487 EXECUTE stmt USING @a;
1488
1489 PREPARE stmt FROM '
1490 EXPLAIN
1491 SELECT *
1492 FROM t1, JSON_TABLE(f2, \'$[*]\'
1493 COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH \'$\')) AS ids
1494 WHERE json_overlaps(f2, ?)';
1495 SET @a='["xx", "cc"]';
1496 EXECUTE stmt USING @a;
1497
1498 PREPARE stmt FROM 'SELECT * FROM t1 WHERE ? MEMBER OF (f2)';
1499 SET @a='xx';
1500 EXECUTE stmt USING @a;
1501
1502 PREPARE stmt FROM 'SELECT * FROM t1 WHERE json_contains(f2, ?)';
1503 SET @a='"xx"';
1504 EXECUTE stmt USING @a;
1505
1506 PREPARE stmt FROM 'SELECT * FROM t1 WHERE json_overlaps(f2, ?)';
1507 SET @a='["xx", "cc"]';
1508 EXECUTE stmt USING @a;
1509
1510 PREPARE stmt FROM 'SELECT * FROM v1 WHERE ? MEMBER OF (f2)';
1511 SET @a='xx';
1512 EXECUTE stmt USING @a;
1513
1514 PREPARE stmt FROM 'SELECT * FROM v1 WHERE json_contains(f2, ?)';
1515 SET @a='"xx"';
1516 EXECUTE stmt USING @a;
1517
1518 PREPARE stmt FROM 'SELECT * FROM v1 WHERE json_overlaps(f2, ?)';
1519 SET @a='["xx", "cc"]';
1520 EXECUTE stmt USING @a;
1521
1522 PREPARE stmt FROM '
1523 SELECT *
1524 FROM t1, JSON_TABLE(f2, \'$[*]\'
1525 COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH \'$\')) AS ids
1526 WHERE ? MEMBER OF (f2)';
1527 SET @a='xx';
1528 EXECUTE stmt USING @a;
1529
1530 PREPARE stmt FROM '
1531 SELECT *
1532 FROM t1, JSON_TABLE(f2, \'$[*]\'
1533 COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH \'$\')) AS ids
1534 WHERE json_contains(f2, ?)';
1535 SET @a='"xx"';
1536 EXECUTE stmt USING @a;
1537
1538 PREPARE stmt FROM '
1539 SELECT *
1540 FROM t1, JSON_TABLE(f2, \'$[*]\'
1541 COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH \'$\')) AS ids
1542 WHERE json_overlaps(f2, ?)';
1543 SET @a='["xx", "cc"]';
1544 EXECUTE stmt USING @a;
1545
1546 EXPLAIN SELECT * FROM t1 WHERE 'xx' MEMBER OF (f2) AND 'yy' MEMBER OF (f2);
1547
1548 EXPLAIN
1549 SELECT * FROM t1 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"');
1550
1551 EXPLAIN
1552 SELECT *
1553 FROM t1
1554 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]');
1555
1556 EXPLAIN SELECT * FROM v1 WHERE 'xx' MEMBER OF (f2) AND 'yy' MEMBER OF (f2);
1557
1558 EXPLAIN
1559 SELECT * FROM v1 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"');
1560
1561 EXPLAIN
1562 SELECT *
1563 FROM v1
1564 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]');
1565
1566 EXPLAIN
1567 SELECT *
1568 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1569 PATH '$')) AS ids
1570 WHERE 'xx' MEMBER OF (f2) AND 'yy' MEMBER OF (f2);
1571
1572 EXPLAIN
1573 SELECT *
1574 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1575 PATH '$')) AS ids
1576 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"');
1577
1578 EXPLAIN
1579 SELECT *
1580 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1581 PATH '$')) AS ids
1582 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]');
1583
1584 EXPLAIN
1585 SELECT *
1586 FROM t1
1587 WHERE 'xx' MEMBER OF (f2) AND 'yy' MEMBER OF(f2) AND f1 = 'bar';
1588
1589 EXPLAIN
1590 SELECT *
1591 FROM t1
1592 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"') AND f1 = 'bar';
1593
1594 EXPLAIN
1595 SELECT *
1596 FROM t1
1597 WHERE json_overlaps(f2, '["xx", "zz"]') AND
1598 json_overlaps(f2, '["yy", "zz"]') AND
1599 f1 = 'bar';
1600
1601 EXPLAIN
1602 SELECT *
1603 FROM v1
1604 WHERE 'xx' MEMBER OF (f2) AND 'yy' MEMBER OF(f2) AND f1 = 'bar';
1605
1606 EXPLAIN
1607 SELECT *
1608 FROM v1
1609 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"') AND f1 = 'bar';
1610
1611 EXPLAIN
1612 SELECT *
1613 FROM v1
1614 WHERE json_overlaps(f2, '["xx", "zz"]') AND
1615 json_overlaps(f2, '["yy", "zz"]') AND
1616 f1 = 'bar';
1617
1618 EXPLAIN
1619 SELECT *
1620 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1621 PATH '$')) AS ids
1622 WHERE 'xx' MEMBER OF (f2) AND 'yy' MEMBER OF (f2) AND f1 = 'bar';
1623
1624 EXPLAIN
1625 SELECT *
1626 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1627 PATH '$')) AS ids
1628 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"') AND f1 = 'bar';
1629
1630 EXPLAIN
1631 SELECT *
1632 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1633 PATH '$')) AS ids
1634 WHERE json_overlaps(f2, '["xx", "zz"]') AND
1635 json_overlaps(f2, '["yy", "zz"]') AND
1636 f1 = 'bar';
1637
1638 SELECT * FROM t1 WHERE 'xx' MEMBER OF (f2) AND 'yy' MEMBER OF (f2);
1639
1640 SELECT * FROM t1 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"');
1641
1642 SELECT *
1643 FROM t1
1644 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]');
1645
1646 SELECT * FROM v1 WHERE 'xx' MEMBER OF (f2) AND 'yy' MEMBER OF (f2);
1647
1648 SELECT * FROM v1 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"');
1649
1650 SELECT *
1651 FROM v1
1652 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]');
1653
1654 SELECT *
1655 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1656 PATH '$')) AS ids
1657 WHERE 'xx' MEMBER OF (f2) AND 'yy' MEMBER OF (f2);
1658
1659 SELECT *
1660 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1661 PATH '$')) AS ids
1662 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"');
1663
1664 SELECT *
1665 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1666 PATH '$')) AS ids
1667 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]');
1668
1669 SELECT *
1670 FROM t1
1671 WHERE 'xx' MEMBER OF (f2) AND 'yy' MEMBER OF (f2) AND f1 = 'bar';
1672
1673 SELECT *
1674 FROM t1
1675 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"') AND f1 = 'bar';
1676
1677 SELECT *
1678 FROM t1
1679 WHERE json_overlaps(f2, '["xx", "zz"]') AND
1680 json_overlaps(f2, '["yy", "zz"]') AND
1681 f1 = 'bar';
1682
1683 SELECT *
1684 FROM v1
1685 WHERE 'xx' MEMBER OF (f2) AND 'yy' MEMBER OF (f2) AND f1 = 'bar';
1686
1687 SELECT *
1688 FROM v1
1689 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"') AND f1 = 'bar';
1690
1691 SELECT *
1692 FROM v1
1693 WHERE json_overlaps(f2, '["xx", "zz"]') AND
1694 json_overlaps(f2, '["yy", "zz"]') AND
1695 f1 = 'bar';
1696
1697 SELECT *
1698 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1699 PATH '$')) AS ids
1700 WHERE 'xx' MEMBER OF (f2) AND 'yy' MEMBER OF(f2) AND f1 = 'bar';
1701
1702 SELECT *
1703 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1704 PATH '$')) AS ids
1705 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"') AND f1 = 'bar';
1706
1707 SELECT *
1708 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1709 PATH '$')) AS ids
1710 WHERE json_overlaps(f2, '["xx", "zz"]') AND
1711 json_overlaps(f2, '["yy", "zz"]') AND
1712 f1 = 'bar';
1713
1714 EXPLAIN SELECT * FROM t1 WHERE 'xx' MEMBER OF (f2) OR 'zz' MEMBER OF (f2);
1715 EXPLAIN SELECT * FROM t1 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"');
1716 EXPLAIN SELECT * FROM t1 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]');
1717 EXPLAIN SELECT * FROM v1 WHERE 'xx' MEMBER OF (f2) OR 'zz' MEMBER OF (f2);
1718 EXPLAIN SELECT * FROM v1 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"');
1719
1720 EXPLAIN
1721 SELECT *
1722 FROM v1
1723 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]');
1724
1725 EXPLAIN
1726 SELECT *
1727 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1728 PATH '$')) AS ids
1729 WHERE 'xx' MEMBER OF (f2) OR 'zz' MEMBER OF (f2);
1730
1731 EXPLAIN
1732 SELECT *
1733 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1734 PATH '$')) AS ids
1735 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"');
1736
1737 EXPLAIN
1738 SELECT *
1739 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1740 PATH '$')) AS ids
1741 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]');
1742
1743 # need more rows, or cost based optimizer chooses full table scan
1744 INSERT INTO t1 VALUES
1745 ('k1', '["v1"]'), ('k2', '["v2"]'), ('k3', '["v3"]'),
1746 ('k4', '["v4"]'), ('k5', '["v5"]'), ('k6', '["v6"]'),
1747 ('k7', '["v7"]'), ('k8', '["v8"]'), ('k9', '["v9"]');
1748
1749 ANALYZE TABLE t1;
1750
1751 EXPLAIN
1752 SELECT *
1753 FROM t1
1754 WHERE 'xx' MEMBER OF (f2) OR 'zz' MEMBER OF (f2) OR f1 = 'bar';
1755
1756 EXPLAIN
1757 SELECT *
1758 FROM t1
1759 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"') OR f1 = 'bar';
1760
1761 EXPLAIN
1762 SELECT *
1763 FROM t1
1764 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]') OR
1765 f1 = 'bar';
1766
1767 EXPLAIN
1768 SELECT *
1769 FROM v1
1770 WHERE 'xx' MEMBER OF (f2) OR 'zz' MEMBER OF(f2) OR f1 = 'bar';
1771
1772 EXPLAIN
1773 SELECT *
1774 FROM v1
1775 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"') OR f1 = 'bar';
1776
1777 EXPLAIN
1778 SELECT *
1779 FROM v1
1780 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]') OR
1781 f1 = 'bar';
1782
1783 EXPLAIN
1784 SELECT *
1785 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1786 PATH '$')) AS ids
1787 WHERE 'xx' MEMBER OF (f2) OR 'zz' MEMBER OF (f2) OR f1 = 'bar';
1788
1789 EXPLAIN
1790 SELECT *
1791 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1792 PATH '$')) AS ids
1793 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"') OR f1 = 'bar';
1794
1795 EXPLAIN
1796 SELECT *
1797 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1798 PATH '$')) AS ids
1799 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]') OR
1800 f1 = 'bar';
1801
1802 SELECT * FROM t1 WHERE 'xx' MEMBER OF (f2) OR 'zz' MEMBER OF (f2);
1803 SELECT * FROM t1 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"');
1804
1805 SELECT *
1806 FROM t1
1807 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]');
1808
1809 SELECT * FROM v1 WHERE 'xx' MEMBER OF (f2) OR 'zz' MEMBER OF(f2);
1810 SELECT * FROM v1 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"');
1811
1812 SELECT *
1813 FROM v1
1814 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]');
1815
1816 SELECT *
1817 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1818 PATH '$')) AS ids
1819 WHERE 'xx' MEMBER OF (f2) OR 'zz' MEMBER OF (f2);
1820
1821 SELECT *
1822 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1823 PATH '$')) AS ids
1824 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"');
1825
1826 SELECT *
1827 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1828 PATH '$')) AS ids
1829 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]');
1830
1831 SELECT * FROM t1 WHERE 'xx' MEMBER OF (f2) OR 'zz' MEMBER OF (f2) OR f1 = 'bar';
1832
1833 SELECT *
1834 FROM t1
1835 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"') OR f1 = 'bar';
1836
1837 SELECT *
1838 FROM t1
1839 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]') OR
1840 f1 = 'bar';
1841
1842 SELECT * FROM v1 WHERE 'xx' MEMBER OF (f2) OR 'zz' MEMBER OF (f2) OR f1 = 'bar';
1843
1844 SELECT *
1845 FROM v1
1846 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"') OR f1 = 'bar';
1847
1848 SELECT *
1849 FROM v1
1850 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]') OR
1851 f1 = 'bar';
1852
1853 SELECT *
1854 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1855 PATH '$')) AS ids
1856 WHERE 'xx' MEMBER OF (f2) OR 'zz' MEMBER OF (f2) OR f1 = 'bar';
1857
1858 SELECT *
1859 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1860 PATH '$')) AS ids
1861 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"') OR f1 = 'bar';
1862
1863 SELECT *
1864 FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50)
1865 PATH '$')) AS ids
1866 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]') OR
1867 f1 = 'bar';
1868
1869 DROP VIEW v1;
1870 DROP TABLE t1;
1871
1872 --echo #
1873 --echo # Bug#33343948: Multi-valued index ranges are printed as binary
1874 --echo #
1875
1876 CREATE TABLE t(doc JSON,
1877 KEY ((CAST(doc->'$[*].name' AS CHAR(3) ARRAY))),
1878 KEY ((CAST(doc->'$[*].date' AS DATE ARRAY))));
1879
1880 INSERT INTO t VALUES (
1881 '[{"name": "abc", "date": "2021-01-01"}, {"name": "def", "date": "2021-01-02"},
1882 {"name": "ghi", "date": "2021-01-03"}, {"name": "jkl", "date": "2021-01-04"}]'
1883 );
1884
1885 ANALYZE TABLE t;
1886
1887 --skip_if_hypergraph # Multi-valued indexes are not supported yet.
1888 EXPLAIN FORMAT=TREE
1889 SELECT * FROM t
1890 WHERE JSON_OVERLAPS(doc->'$[*].name', '["abc", "xyz"]');
1891
1892 --skip_if_hypergraph # Multi-valued indexes are not supported yet.
1893 EXPLAIN FORMAT=TREE
1894 SELECT * FROM t
1895 WHERE JSON_OVERLAPS(doc->'$[*].date', '["2020-01-01", "2021-01-01"]');
1896
1897 DROP TABLE t;
1898
1899 --echo #
1900 --echo # Bug#35012146 Multivalued index creation results in partial result for
1901 --echo # query with ORDER BY DESC
1902 --echo #
1903
1904 CREATE TABLE test (id INT NOT NULL, json_value JSON, PRIMARY KEY (id),
1905 INDEX multivalued_index ((CAST(json_value->'$' AS UNSIGNED ARRAY))));
1906 INSERT INTO test VALUES (1,'[1, 2, 3, 4]');
1907 INSERT INTO test VALUES (2,'[4, 5, 6, 7]');
1908 INSERT INTO test VALUES (3,'[7, 8, 9, 1]');
1909 INSERT INTO test VALUES (4,'[9, 1, 2, 3]');
1910 INSERT INTO test VALUES (5,'[3, 4, 5, 6]');
1911 INSERT INTO test VALUES (6,'[6, 7, 8, 9]');
1912 ANALYZE TABLE test;
1913 SELECT id, json_value FROM test WHERE 4 MEMBER OF (json_value->'$') ORDER BY id;
1914 EXPLAIN SELECT id, json_value FROM test WHERE 4 MEMBER OF (json_value->'$')
1915 ORDER BY id DESC;
1916 --replace_regex $elide_time
1917 --skip_if_hypergraph # Different plan.
1918 EXPLAIN ANALYZE SELECT id, json_value FROM test WHERE
1919 4 MEMBER OF (json_value->'$') ORDER BY id DESC;
1920 SELECT id, json_value FROM test WHERE 4 MEMBER OF (json_value->'$')
1921 ORDER BY id DESC;
1922 DROP TABLE test;
1923
1924 CREATE TABLE test (id INT PRIMARY KEY, j JSON,
1925 INDEX multivalued_index ((CAST(j->'$[*]' AS CHAR(10) ARRAY))));
1926 INSERT INTO test VALUES (1, '["10"]'),
1927 (2, '["9"]'),
1928 (3, '["8", "10", "25"]'),
1929 (4, '["7", "15" ,"3", "27"]'),
1930 (5, '["6", "3", "10", "95"]'),
1931 (6, '["5"]'),
1932 (7, '["4"]'),
1933 (8, '["3"]'),
1934 (9, '["2"]'),
1935 (10, '["1"]');
1936 ANALYZE TABLE test;
1937 --replace_regex $elide_time
1938 --skip_if_hypergraph # Different plan.
1939 EXPLAIN ANALYZE SELECT * FROM test WHERE "10" MEMBER OF (j->'$[*]')
1940 ORDER BY id DESC;
1941 SELECT * FROM test WHERE "10" MEMBER OF (j->'$[*]') ORDER BY id DESC;
1942 --replace_regex $elide_time
1943 --skip_if_hypergraph # Different plan.
1944 EXPLAIN ANALYZE SELECT * FROM test WHERE "3" MEMBER OF (j->'$[*]')
1945 ORDER BY id DESC;
1946 SELECT * FROM test WHERE "3" MEMBER OF (j->'$[*]') ORDER BY id DESC;
1947 DROP TABLE test;
1948
1949 # If the unique record filter is not properly closed by any of queries above,
1950 # then the TempTable plugin will have ref_count > 0 on restart. This should be
1951 # at the very end of the test.
1952 --let $restart_parameters= restart:
1953 --source include/restart_mysqld.inc
1954
1955 --echo #
1956 --echo # Bug#33334911 - Multi-valued index performance is too slow
1957 --echo # (Access too many rows than required)
1958 --echo #
1959
1960 CREATE TABLE t1 (
1961 id INT NOT NULL,
1962 f1 enum('T1','T2','T3') NOT NULL,
1963 f2 INT NOT NULL,
1964 json_value json DEFAULT (json_array()),
1965 PRIMARY KEY (id),
1966 KEY idx (f1 ,(CAST(json_extract(json_value,_utf8mb4'$') AS UNSIGNED ARRAY)), f2)
1967 );
1968
1969 INSERT INTO t1 VALUES (1, 'T2', 15, '[1, 3, 5, 7]');
1970 INSERT INTO t1 VALUES (2, 'T2', 25, '[10, 20, 30, 40]');
1971 INSERT INTO t1 VALUES (3, 'T2', 35, '[15, 25, 35, 45]');
1972 INSERT INTO t1 VALUES (4, 'T2', 45, '[100, 200, 300, 400]');
1973 INSERT INTO t1 VALUES (5, 'T2', 55, '[120, 220, 320, 420]');
1974 INSERT INTO t1 VALUES (6, 'T2', 65, '[140, 240, 340, 440]');
1975 INSERT INTO t1 VALUES (7, 'T2', 9, '[100, 200, 300, 400]');
1976 INSERT INTO t1 VALUES (8, 'T2', 9, '[100, 200, 300, 400]');
1977 INSERT INTO t1 VALUES (9, 'T2', 9, '[100, 200, 300, 400]');
1978 INSERT INTO t1 VALUES (10, 'T2', 9, '[100, 200, 300, 400]');
1979
1980 ANALYZE TABLE t1;
1981
1982 let $query = SELECT COUNT(*) FROM t1
1983 WHERE f1 IN ('T2') AND JSON_OVERLAPS(json_value->'$', CAST('[100]' AS JSON)) AND f2>=10;
1984
1985 --eval EXPLAIN $query
1986
1987 FLUSH STATUS;
1988 --eval $query
1989 SHOW STATUS LIKE 'Handler_read%';
1990
1991 SET @save_opt=@@optimizer_switch;
1992 SET @@optimizer_switch="mrr_cost_based=off";
1993
1994 --eval EXPLAIN $query
1995 --eval $query
1996
1997 SET @@optimizer_switch=@save_opt;
1998 DROP TABLE t1;
1999
2000 --echo #
2001 --echo # Bug#37436310 - Performance issue with Multi-Valued Index and ORDER BY DESC LIMIT
2002 --echo # (Reads more data than expected)
2003 --echo #
2004
2005 CREATE TABLE t1 (
2006 id INT NOT NULL AUTO_INCREMENT,
2007 ids json NOT NULL,
2008 PRIMARY KEY (`id`),
2009 KEY mx_ids_id ((CAST(json_extract(ids,_utf8mb4'$[*]') AS SIGNED ARRAY)),id)
2010 );
2011
2012 INSERT INTO `t1` VALUES (1,'[10]');
2013 INSERT INTO `t1` VALUES (2,'[10]');
2014 INSERT INTO `t1` VALUES (3,'[20]');
2015 INSERT INTO `t1` VALUES (4,'[30]');
2016 INSERT INTO `t1` VALUES (5,'[40]');
2017 INSERT INTO `t1` VALUES (6,'[50]');
2018 INSERT INTO `t1` VALUES (7,'[50]');
2019 INSERT INTO `t1` VALUES (8,'[50]');
2020
2021 ANALYZE TABLE t1;
2022 FLUSH STATUS;
2023
2024 SELECT * FROM t1 WHERE 30 MEMBER OF (ids->'$[*]') AND id < 7 ORDER BY id DESC;
2025
2026 --skip_if_hypergraph # Bug#37543247 Missing optimization in hypergraph in case of multi-valued index
2027 SHOW STATUS LIKE 'Handler_read%';
2028
2029 SELECT /*+ INDEX(t1 mx_ids_id) */ * FROM t1 WHERE 50 MEMBER OF (ids->'$[*]') AND id > 5 ORDER BY id DESC;
2030
2031 DROP TABLE t1;