CREATE TABLE t1 (id INT UNSIGNED NOT NULL, arr JSON NOT NULL, PRIMARY KEY (id));
ALTER TABLE t1 ADD INDEX test_categories ((CAST(arr AS UNSIGNED ARRAY)));
DROP TABLE t1;
CREATE TABLE t1 (id INT NOT NULL KEY AUTO_INCREMENT, f1 JSON);
CREATE INDEX i1 ON t1((CAST(f1->"$[*]" AS UNSIGNED ARRAY)));
INSERT INTO t1(f1) VALUES(CAST('[1333,1321]' AS JSON));
SELECT * FROM t1;
id	f1
1	[1333, 1321]
DROP TABLE t1;
CREATE TABLE t1 (id INT NOT NULL KEY AUTO_INCREMENT, f1 JSON);
CREATE INDEX i1 ON t1((CAST(f1->"$[*]" AS CHAR(10) ARRAY)));
INSERT INTO t1(f1) VALUES(CAST('["13.33","13.21"]' AS JSON));
SELECT * FROM t1;
id	f1
1	["13.33", "13.21"]
DROP TABLE t1;
CREATE TABLE t1 (
j4 json ,
KEY mv_idx_binary ((( CAST(j4->'$[*]' AS BINARY(10) ARRAY))), (json_depth(j4)), (json_valid(j4))) USING BTREE);
INSERT INTO t1 VALUES ('["foobar"]');
SELECT * FROM t1 WHERE "foobar" MEMBER OF (j4->'$[*]');
j4
["foobar"]
DELETE FROM t1;
DROP TABLE t1;
CREATE TABLE t1 (id INT NOT NULL KEY AUTO_INCREMENT, f1 JSON);
CREATE INDEX i1 ON t1((CAST(f1->"$[*]" AS DECIMAL(4,2) ARRAY)));
INSERT INTO t1(f1) VALUES(CAST('["13.33","13.21"]' AS JSON));
UPDATE t1 SET f1 = NULL;
SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
id	f1
SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
id	f1
SELECT * FROM t1 WHERE f1 IS NULL;
id	f1
1	NULL
UPDATE t1 SET f1 = CAST('["13.33","13.21"]' AS JSON);
SELECT * FROM t1 WHERE f1 IS NULL;
id	f1
SELECT * FROM t1 WHERE 12.34 MEMBER OF (f1->'$[*]');
id	f1
SELECT * FROM t1 WHERE 56.78 MEMBER OF (f1->'$[*]');
id	f1
SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
id	f1
1	["13.33", "13.21"]
SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
id	f1
1	["13.33", "13.21"]
UPDATE t1 SET f1 = CAST('["12.34","56.78"]' AS JSON);
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1 WHERE 12.34 MEMBER OF (f1->'$[*]');
id	f1
1	["12.34", "56.78"]
SELECT * FROM t1 WHERE 56.78 MEMBER OF (f1->'$[*]');
id	f1
1	["12.34", "56.78"]
SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
id	f1
SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
id	f1
UPDATE t1 SET f1 = CAST('["43.21","98.76", "76.54"]' AS JSON);
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1 WHERE 12.34 MEMBER OF (f1->'$[*]');
id	f1
SELECT * FROM t1 WHERE 56.78 MEMBER OF (f1->'$[*]');
id	f1
SELECT * FROM t1 WHERE 43.21 MEMBER OF (f1->'$[*]');
id	f1
1	["43.21", "98.76", "76.54"]
SELECT * FROM t1 WHERE 98.76 MEMBER OF (f1->'$[*]');
id	f1
1	["43.21", "98.76", "76.54"]
SELECT * FROM t1 WHERE 76.54 MEMBER OF (f1->'$[*]');
id	f1
1	["43.21", "98.76", "76.54"]
DROP TABLE t1;
CREATE TEMPORARY TABLE IF NOT EXISTS t1 (
j JSON DEFAULT (CAST('["HelloWorld"]' AS JSON)),
KEY mv_idx_binary (( CAST(j->'$[*]' AS CHAR(10) ARRAY) ))  );
INSERT INTO t1 VALUES() ;
SELECT * FROM t1;
j
["HelloWorld"]
UPDATE t1 SET j = CAST('["HelloMySQL"]' AS JSON);
SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
j
["HelloMySQL"]
DELETE FROM t1;
DROP TABLE t1;
CREATE TABLE t1(j JSON, KEY i1((CAST(j AS UNSIGNED ARRAY))));
INSERT INTO t1 VALUES('[1,2,3]'), ('[]'), (NULL);
SELECT * FROM t1;
j
[1, 2, 3]
[]
NULL
UPDATE t1 SET j = '[]' WHERE j IS NULL;
SELECT * FROM t1;
j
[1, 2, 3]
[]
[]
DELETE FROM t1;
INSERT INTO t1 VALUES('[1,2,3]'), ('[]'), (NULL);
UPDATE t1 SET j = NULL;
SELECT * FROM t1;
j
NULL
NULL
NULL
DELETE FROM t1;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
DROP TABLE t1;
CREATE TABLE t1 (
j JSON DEFAULT (CAST('["HelloWorld"]' AS JSON)),
KEY mv_idx_binary ((CAST(j->'$[*]' AS BINARY(10) ARRAY))));
INSERT INTO t1 VALUES();
ALTER TABLE t1 DROP INDEX mv_idx_binary ;
CREATE INDEX mv_idx_binary ON t1 ((CAST( j -> '$[*]' AS BINARY(10) ARRAY))) VISIBLE;
SELECT * FROM t1;
j
["HelloWorld"]
DROP TABLE t1;
CREATE TABLE IF NOT EXISTS t1 (
j2 JSON DEFAULT (CAST('[9,8,0,1]' AS JSON)),
j3 JSON DEFAULT (CAST('["foobar"]' AS JSON)),
KEY mv_idx_unsigned (( CAST(j2->'$[*]' AS UNSIGNED ARRAY) )) ,
KEY mv_idx_char (( CAST(j3->'$[*]' AS CHAR(10) ARRAY) ))
);
INSERT INTO t1(j3) VALUES(CAST('[ "foobar", "HelloWorld", "MySQL", "1234" ]' AS JSON));
INSERT INTO t1(j3) VALUES(CAST('[ "Hello", "World", "InnoDB", "56" ]' AS JSON));
SELECT * FROM t1;
j2	j3
[9, 8, 0, 1]	["foobar", "HelloWorld", "MySQL", "1234"]
[9, 8, 0, 1]	["Hello", "World", "InnoDB", "56"]
DELETE FROM t1;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
DROP TABLE t1;
CREATE TABLE t1 (j JSON, key i1((cast(j->"$.id" AS CHAR(10) ARRAY))), key i2((cast(j->"$.id" AS UNSIGNED ARRAY))));
INSERT INTO t1 VALUES('{"id":1}');
INSERT INTO t1 VALUES('{"group":2}');
INSERT INTO t1 VALUES(NULL);
SELECT * FROM t1;
j
{"id": 1}
{"group": 2}
NULL
DROP TABLE t1;
CREATE TABLE t1(j JSON);
INSERT INTO t1 VALUES('[]'), ('null');
CREATE INDEX mv_idx_date ON t1 ((CAST(j->'$' AS UNSIGNED ARRAY)));
ERROR 22018: Invalid JSON value for CAST for functional index 'mv_idx_date'.
DELETE FROM t1;
INSERT INTO t1 VALUES (CAST('[9,8,0,1]' AS JSON));
CREATE INDEX mv_idx_date ON t1 ((CAST(j->'$' AS UNSIGNED ARRAY)));
DROP TABLE t1;
CREATE TABLE IF NOT EXISTS t1 (
i INT DEFAULT 100,
j JSON DEFAULT (CAST('["HelloWorld"]' AS JSON)),
KEY mv_idx_binary (( CAST(j->'$[*]' AS BINARY(10) ARRAY) )) );
INSERT INTO t1 VALUES();
SELECT * FROM t1;
i	j
100	["HelloWorld"]
START TRANSACTION;
DELETE FROM t1;
SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
i	j
100	["HelloWorld"]
COMMIT;
SELECT * FROM t1;
i	j
DROP TABLE t1;
CREATE TABLE t1 (f1 JSON, UNIQUE KEY i1((CAST(f1->"$[*]" AS CHAR(10) ARRAY))));
INSERT INTO t1(f1) VALUES(CAST('["abc", "abc "]' AS JSON));
START TRANSACTION;
SELECT * FROM t1 WHERE "abc" MEMBER OF (f1->"$[*]");
f1
["abc", "abc "]
UPDATE t1 SET f1 = CAST('["def"]' AS JSON);
SELECT * FROM t1 WHERE "def" MEMBER OF (f1->"$[*]");
f1
ROLLBACK;
DROP TABLE t1;
CREATE TABLE t1 (id INT NOT NULL KEY AUTO_INCREMENT, f1 JSON);
CREATE INDEX i1 ON t1((CAST(f1->"$[*]" AS UNSIGNED ARRAY)));
INSERT INTO t1 VALUES(0, NULL);
DELETE FROM t1;
INSERT INTO t1 VALUES(1, NULL);
SELECT * FROM t1;
id	f1
1	NULL
DELETE FROM t1;
INSERT INTO t1 VALUES(2, CAST('[10]' AS JSON));
DELETE FROM t1;
INSERT INTO t1 VALUES(3, CAST('[10]' AS JSON));
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1;
id	f1
3	[10]
DROP TABLE t1;
CREATE TABLE t1 (id INT NOT NULL KEY AUTO_INCREMENT, f1 JSON);
CREATE INDEX i1 ON t1((CAST(f1->"$[*]" AS DECIMAL(4,2) ARRAY)));
INSERT INTO t1(f1) VALUES(CAST('["13.33","13.21"]' AS JSON));
UPDATE t1 SET f1 = NULL;
UPDATE t1 SET f1 = CAST('["13.33","13.21"]' AS JSON);
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1 WHERE f1 IS NULL;
id	f1
SELECT * FROM t1 WHERE 12.34 MEMBER OF (f1->'$[*]');
id	f1
SELECT * FROM t1 WHERE 56.78 MEMBER OF (f1->'$[*]');
id	f1
SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
id	f1
1	["13.33", "13.21"]
SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
id	f1
1	["13.33", "13.21"]
UPDATE t1 SET f1 = CAST('["12.34","56.78"]' AS JSON);
UPDATE t1 SET f1 = CAST('["43.21","98.76", "76.54"]' AS JSON);
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1 WHERE 12.34 MEMBER OF (f1->'$[*]');
id	f1
SELECT * FROM t1 WHERE 56.78 MEMBER OF (f1->'$[*]');
id	f1
SELECT * FROM t1 WHERE 43.21 MEMBER OF (f1->'$[*]');
id	f1
1	["43.21", "98.76", "76.54"]
SELECT * FROM t1 WHERE 98.76 MEMBER OF (f1->'$[*]');
id	f1
1	["43.21", "98.76", "76.54"]
SELECT * FROM t1 WHERE 76.54 MEMBER OF (f1->'$[*]');
id	f1
1	["43.21", "98.76", "76.54"]
DROP TABLE t1;
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`j1` json DEFAULT (cast(_utf8mb4'[5,-1,0]' as json)),
`j2` json DEFAULT (cast(_utf8mb4'[9,8,0,1]' as json)),
`j3` json DEFAULT (cast(_utf8mb4'["foobar"]' as json)),
`j4` json DEFAULT (cast(_utf8mb4'["HelloWorld"]' as json)),
`j5` json DEFAULT (json_array(now())),
`j7` json DEFAULT (json_array(cast(now() as time))),
`extra` json DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `mv_idx_binary` ((json_length(`j3`)),(cast(json_extract(`j4`,_utf8mb4'$[*]') as binary(10) array)),(json_depth(`j2`))),
KEY `mv_idx_signed` ((cast(json_extract(`j1`,_utf8mb4'$[*]') as signed array))),
KEY `mv_idx_unsigned` ((cast(json_extract(`j2`,_utf8mb4'$[*]') as unsigned array))),
KEY `mv_idx_char` ((cast(json_extract(`j3`,_utf8mb4'$[*]') as char(10) array))),
KEY `mv_idx_time` ((cast(json_extract(`j7`,_utf8mb4'$[*]') as time array)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO t1 ( j4 ) VALUES( JSON_OBJECT( 'key1' , CAST( '2019-11-27 03:43:53' AS DATETIME)) );
INSERT INTO t1 VALUES();
DELETE FROM t1 WHERE ( JSON_OVERLAPS( JSON_OBJECT( 'key1' , CAST(null AS JSON)) , j1 -> '$[*]' ) ) XOR ( JSON_CONTAINS( j5 -> '$[*]' , JSON_OBJECT('key1' , CAST(CONCAT( '[ 1, 2, 3, ' , null, ', 5 ]' ) AS JSON)) ) );
UPDATE t1 SET j4 = '["foobar", "HelloWorld", "MySQL", "1234" ]' WHERE NOT ('');
ERROR 23000: Duplicate entry '1-["base64:t-2' for key 't1.mv_idx_binary'
UPDATE t1 SET j7 = JSON_OBJECT( 'key1' , JSON_OBJECT()) WHERE JSON_OBJECT('key1' , CAST( '[]' AS JSON)) MEMBER OF ( j1 -> '$[*]' );
DELETE FROM t1;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
DROP TABLE t1;
CREATE TABLE IF NOT EXISTS t1 (
j2 JSON DEFAULT (CAST('[9,8,0,1]' AS JSON)),
j8 JSON DEFAULT (JSON_ARRAY(3.1456,6.022,-1)),
KEY mv_idx_unsigned (( CAST(j2->'$[*]' AS UNSIGNED ARRAY) )) ,
KEY mv_idx_decimal (( CAST(j8->'$[*]' AS DECIMAL(10,4) ARRAY) )) );
ALTER TABLE t1 DROP INDEX mv_idx_decimal;
INSERT INTO t1 VALUES();
DELETE FROM t1;
ALTER TABLE t1 DROP INDEX mv_idx_unsigned;
SELECT * FROM t1;
j2	j8
DROP TABLE t1;
CREATE TABLE IF NOT EXISTS t1 ( id int AUTO_INCREMENT PRIMARY KEY, j1 JSON DEFAULT (CAST('[5,-1,0]' AS JSON)), j2 JSON DEFAULT (CAST('[9,8,0,1]' AS JSON)), j3 JSON DEFAULT (CAST('["foobar"]' AS JSON)), j4 JSON DEFAULT (CAST('["HelloWorld"]' AS JSON)), j5 JSON DEFAULT (JSON_ARRAY(NOW())), j6 JSON DEFAULT (JSON_ARRAY(DATE(NOW()), DATE(NOW()), DATE(NOW()))), j7 JSON DEFAULT (JSON_ARRAY(TIME(NOW()))), j8 JSON DEFAULT (JSON_ARRAY(3.1456,6.022,-1)), KEY mv_idx_signed (( CAST(j1->'$[*]' AS SIGNED ARRAY) )) , KEY mv_idx_unsigned (( CAST(j2->'$[*]' AS UNSIGNED ARRAY) )) , KEY mv_idx_char (( CAST(j3->'$[*]' AS CHAR(10) ARRAY) )) , KEY mv_idx_binary (( CAST(j4->'$[*]' AS BINARY(10) ARRAY) )) , KEY mv_idx_datetime (( CAST(j5->'$[*]' AS DATETIME ARRAY) )) , KEY mv_idx_date (( CAST(j6->'$[*]' AS DATE ARRAY) )) , KEY mv_idx_time (( CAST(j7->'$[*]' AS TIME ARRAY) )) , KEY mv_idx_decimal (( CAST(j8->'$[*]' AS DECIMAL(10,4) ARRAY) )) ) ;
SET autocommit=0 ;
SET SESSION sql_mode ='' ;
DELETE FROM t1 WHERE 0 ;
INSERT INTO t1 SELECT * FROM t1  GROUP BY j2 ;
INSERT INTO t1 ( j4 ) VALUES( JSON_OBJECT( 'key1' , CAST( '2007-08-18' AS DATE)) ) ;
INSERT INTO t1 VALUES() ;
EXPLAIN DELETE FROM t1 ;
ALTER TABLE t1 RENAME KEY mv_idx_signed to new_idx_1 ;
ALTER TABLE t1 RENAME KEY new_idx_1 to mv_idx_signed ;
UPDATE t1  SET j5 = JSON_OBJECT( 'key1' , true) WHERE ( 1 ) AND ( 0 ) XOR ( '' ) ;
ALTER TABLE t1 DROP INDEX mv_idx_datetime ;
ALTER TABLE t1 ADD COLUMN extra JSON ;
UPDATE t1 SET extra = j1 ;
ALTER TABLE t1 DROP INDEX mv_idx_date ;
INSERT INTO t1 VALUES() ;
DELETE FROM t1 ;
EXPLAIN UPDATE t1 IGNORE KEY FOR JOIN ( mv_idx_decimal ) SET j4 = JSON_OBJECT( 'key1' , CAST( '12:58:44' AS TIME)) WHERE ( 1 ) OR ( '' ) ;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t1	NULL	index	NULL	PRIMARY	4	NULL	1	100.00	NULL
Warnings:
Note	1003	update `test`.`t1` IGNORE INDEX FOR JOIN (`mv_idx_decimal`) set `test`.`t1`.`j4` = json_object('key1',cast('12:58:44' as time))
INSERT INTO t1 SELECT * FROM t1  GROUP BY j2 ORDER BY j6 DESC ;
UPDATE t1 USE INDEX FOR ORDER BY ( mv_idx_char , mv_idx_unsigned , mv_idx_binary ) SET j5 = JSON_ARRAY(CAST(-69.56 AS DECIMAL (10,3)), CAST(PI() AS DECIMAL(10,5)), 1947.95) WHERE 1 ;
INSERT INTO t1 ( j2 ) VALUES( JSON_OBJECT( 'key1' , 'to' ) ) ;
ALTER TABLE t1 DROP INDEX mv_idx_decimal ;
EXPLAIN SELECT * FROM t1  WHERE 0  ORDER BY j6 ;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`j1` AS `j1`,`test`.`t1`.`j2` AS `j2`,`test`.`t1`.`j3` AS `j3`,`test`.`t1`.`j4` AS `j4`,`test`.`t1`.`j5` AS `j5`,`test`.`t1`.`j6` AS `j6`,`test`.`t1`.`j7` AS `j7`,`test`.`t1`.`j8` AS `j8`,`test`.`t1`.`extra` AS `extra` from `test`.`t1` where false order by `test`.`t1`.`j6`
INSERT INTO t1 VALUES() ;
UPDATE t1  SET j1 = CAST( '[ -1 , 10, 7, 6, -999 ]' AS JSON ) WHERE 0 ;
DELETE FROM t1 WHERE NOT ( '' ) ;
ALTER TABLE t1 RENAME KEY mv_idx_binary to new_idx_1 ;
ALTER TABLE t1 RENAME KEY new_idx_1 to mv_idx_binary ;
EXPLAIN INSERT INTO t1 ( j3 ) VALUES( JSON_OBJECT( 'key1' , CAST(null AS JSON)) ) ;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	INSERT	t1	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
Warnings:
Note	1003	insert into `test`.`t1` (`test`.`t1`.`j3`) values (json_object('key1',cast(NULL as json)))
DELETE FROM t1 ;
CREATE  INDEX mv_idx_decimal  ON t1 (( CAST( j8 -> '$[*]' AS DECIMAL(10,4) ARRAY) )) VISIBLE ;
INSERT INTO t1 VALUES() ;
INSERT INTO t1 ( j4 ) VALUES( JSON_OBJECT() ) ;
ALTER TABLE t1 RENAME KEY mv_idx_binary to new_idx_1 ;
ALTER TABLE t1 RENAME KEY new_idx_1 to mv_idx_binary ;
EXPLAIN DELETE FROM t1 WHERE '' ;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	delete from `test`.`t1` where false
DELETE FROM t1 WHERE '2034-10-29 21:05:14' MEMBER OF ( j8 -> '$[*]' ) ;
INSERT INTO t1 VALUES() ;
UPDATE t1  SET j5 = '[7, 5, 1, -56, -45]' WHERE 0 ;
ALTER TABLE t1 DROP INDEX mv_idx_time ;
ALTER TABLE t1 DROP INDEX mv_idx_binary ;
ALTER TABLE t1 ADD  INDEX mv_idx_date  ( ( JSON_DEPTH( j3 ) ) ,( CAST( j6 -> '$.key1[*]' AS DATE ARRAY) ), ( JSON_LENGTH( j7 ) ) ) VISIBLE ;
INSERT INTO t1 ( j4 ) VALUES( JSON_OBJECT( 'key1' , CAST( '1978-09-05 12:08:06' AS DATETIME)) ) ;
ALTER TABLE t1 ADD  INDEX mv_idx_time USING HASH ( ( JSON_LENGTH( j5 ) ) , ( JSON_DEPTH( j1 ) ) ,( CAST( j7 -> '$.key1' AS TIME ARRAY) )) INVISIBLE ;
Warnings:
Note	3502	This storage engine does not support the HASH index algorithm, storage engine default was used instead.
INSERT INTO t1 VALUES() ;
INSERT INTO t1 VALUES() ;
UPDATE t1  SET j3 = JSON_OBJECT( 'key1' , 3 ) WHERE ( '' ) XOR ( 1 ) OR ( 0 ) ;
ROLLBACK;
UPDATE t1  SET j3 = JSON_OBJECT( 'key1' , 3 ) WHERE ( '' ) XOR ( 1 ) OR ( 0 ) ;
DROP TABLE t1;
SET autocommit = default;
SET SESSION sql_mode = default;
SET autocommit=0;
SET SESSION sql_mode ='';
CREATE TABLE IF NOT EXISTS t1 ( id int AUTO_INCREMENT PRIMARY KEY, j1 JSON DEFAULT (CAST( '[5,-1,0]' AS JSON)), j2 JSON DEFAULT (CAST( '[9,8,0,1]' AS JSON)), j3 JSON DEFAULT (CAST( '["foobar"]' AS JSON)), j4 JSON DEFAULT (CAST( '["HelloWorld"]' AS JSON)), j5 JSON DEFAULT (JSON_ARRAY(NOW())), j6 JSON DEFAULT (JSON_ARRAY(DATE(NOW()), DATE(NOW()), DATE(NOW()))), j7 JSON DEFAULT (JSON_ARRAY(TIME(NOW()))), j8 JSON DEFAULT (JSON_ARRAY(3.1456,6.022,-1)), KEY mv_idx_signed (( CAST(j1-> '$[*]' AS SIGNED ARRAY) )) , KEY mv_idx_unsigned (( CAST(j2-> '$[*]' AS UNSIGNED ARRAY) )) , KEY mv_idx_char (( CAST(j3-> '$[*]' AS CHAR(10) ARRAY) )) , KEY mv_idx_binary (( CAST(j4-> '$[*]' AS BINARY(10) ARRAY) )) , KEY mv_idx_datetime (( CAST(j5-> '$[*]' AS DATETIME ARRAY) )) , KEY mv_idx_date (( CAST(j6-> '$[*]' AS DATE ARRAY) )) , KEY mv_idx_time (( CAST(j7-> '$[*]' AS TIME ARRAY) )) , KEY mv_idx_decimal (( CAST(j8-> '$[*]' AS DECIMAL(10,4) ARRAY) )) );
SELECT * FROM t1  WHERE NOT ( JSON_ARRAY(TIME(NOW()), CAST( '00:00:00' AS TIME)) MEMBER OF ( j2 -> '$[*]' ) ) GROUP BY j4  LIMIT 84;
id	j1	j2	j3	j4	j5	j6	j7	j8
INSERT INTO t1 SELECT * FROM t1 FORCE KEY FOR JOIN ( mv_idx_decimal , mv_idx_char );
SELECT * FROM t1 IGNORE INDEX FOR ORDER BY ( mv_idx_date , mv_idx_binary ) WHERE ( 0 ) XOR ( 1 ) GROUP BY j5 ORDER BY j3;
id	j1	j2	j3	j4	j5	j6	j7	j8
ALTER TABLE t1 DROP INDEX mv_idx_time;
INSERT INTO t1 VALUES();
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1  WHERE 0;
id	j1	j2	j3	j4	j5	j6	j7	j8
DELETE FROM t1;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
INSERT INTO t1 SELECT * FROM t1  GROUP BY j3 ORDER BY j7;
INSERT INTO t1 VALUES();
ALTER TABLE t1 ADD COLUMN extra JSON;
UPDATE t1 SET extra = j5;
ALTER TABLE t1 DROP INDEX mv_idx_datetime;
ALTER TABLE t1 DROP COLUMN j5;
ALTER TABLE t1 CHANGE COLUMN extra j5 JSON;
DELETE FROM t1 WHERE '';
INSERT INTO t1 ( j1 ) VALUES( JSON_OBJECT( 'key1' , CAST( '00:52:03' AS TIME)) );
ALTER TABLE t1 ADD COLUMN extra JSON;
UPDATE t1 SET extra = j5;
ALTER TABLE t1 DROP INDEX mv_idx_date;
SELECT id, j1, j2, j3, j4 FROM t1;
id	j1	j2	j3	j4
2	[5, -1, 0]	[9, 8, 0, 1]	["foobar"]	["HelloWorld"]
3	{"key1": "00:52:03.000000"}	[9, 8, 0, 1]	["foobar"]	["HelloWorld"]
SELECT COUNT(*) FROM t1;
COUNT(*)
2
ALTER TABLE t1 DROP COLUMN j5;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT id, j1, j2, j3, j4 FROM t1 WHERE "foobar" MEMBER OF (j3->'$[*]');
id	j1	j2	j3	j4
2	[5, -1, 0]	[9, 8, 0, 1]	["foobar"]	["HelloWorld"]
3	{"key1": "00:52:03.000000"}	[9, 8, 0, 1]	["foobar"]	["HelloWorld"]
SELECT id, j1, j2, j3, j4 FROM t1 WHERE "HelloWorld" MEMBER OF (j4->'$[*]');
id	j1	j2	j3	j4
2	[5, -1, 0]	[9, 8, 0, 1]	["foobar"]	["HelloWorld"]
3	{"key1": "00:52:03.000000"}	[9, 8, 0, 1]	["foobar"]	["HelloWorld"]
DROP TABLE t1;
SET autocommit = default;
SET SESSION sql_mode = default;
SET SESSION sql_mode = '';
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`j1` json DEFAULT (cast(_utf8mb4'[5,-1,0]' as json)),
`j2` json DEFAULT (cast(_utf8mb4'[9,8,0,1]' as json)),
`j3` json DEFAULT (cast(_utf8mb4'["foobar"]' as json)),
`j4` json DEFAULT (cast(_utf8mb4'["HelloWorld"]' as json)),
`j5` json DEFAULT (json_array(now())),
`j6` json DEFAULT (json_array(cast(now() as date),cast(now() as date),cast(now() as date))),
`j7` json DEFAULT (json_array(cast(now() as time))),
`j8` json DEFAULT (json_array(3.1456,6.022,-(1))),
`extra` json DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `mv_idx_signed` ((json_length(`j5`)),(cast(json_extract(`j1`,_utf8mb4'$.key1[*]') as signed array)),(json_depth(`j3`))) USING BTREE,
KEY `mv_idx_char` ((cast(json_extract(`j3`,_utf8mb4'$.key2') as char(10) array)),(json_length(`j7`)),(((strcmp(_utf8mb4' ',_utf8mb4'NwKf2G3hn6eKy8TCMVTfh0xgGZfg68dchTUUxQtg9xfvCdePZCAxIZEvUETuWATcdjuOLZx') <> (_utf8mb4'6q' like _utf8mb4'_world_')) and (_utf8mb4'1994-01-16 22:25:11' not between _utf8mb4'2033-04-12 09:41:42' and _utf8mb4'1991-05-20 06:44:18')))),
KEY `mv_idx_unsigned` ((json_length(`j3`)),(json_valid(`j4`)),(cast(json_extract(`j2`,_utf8mb4'$[*]') as unsigned array))) USING BTREE /*!80000 INVISIBLE */,
KEY `mv_idx_decimal` ((cast(json_extract(`j8`,_utf8mb4'$[*]') as decimal(10, 4) array))) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1283 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
ALTER TABLE t1 ADD UNIQUE INDEX mv_idx_date USING BTREE ( ( JSON_DEPTH( j3 ) ) ,( CAST( j6 -> '$[*]' AS DATE ARRAY) ), ( JSON_LENGTH( j1 ) ) ) VISIBLE;
UPDATE t1  SET j2 = JSON_OBJECT( 'key1' , 1772955722 ) WHERE ( JSON_OVERLAPS( j7 -> '$[*]' , '[ ]' ) ) XOR ( 1 );
INSERT INTO t1 SELECT * FROM t1  WHERE JSON_CONTAINS( JSON_ARRAY(-10,100,65,JSON_ARRAY(-101,25,12,0)) , j6 -> '$[*]' )   LIMIT 55;
INSERT INTO t1 SELECT * FROM t1  GROUP BY j8;
DELETE FROM t1 WHERE ( 0 ) XOR ( 1 ) AND ( JSON_CONTAINS( '[45, 61, 54, 0]' , j3 -> '$[*]' ) );
INSERT INTO t1 VALUES();
UPDATE t1  SET j3 = '["foobar", "HelloWorld", "MySQL", "1234" ]' WHERE JSON_OVERLAPS( JSON_ARRAY(DATE(NOW()), CAST( '2018-01-01' AS DATE), CAST( '0000-00-00' AS DATE)) , j7 -> '$[*]' );
DELETE FROM t1;
SELECT * FROM t1 LIMIT 73;
id	j1	j2	j3	j4	j5	j6	j7	j8	extra
INSERT INTO t1 VALUES();
INSERT INTO t1 SELECT * FROM t1 USE KEY FOR GROUP BY ( mv_idx_unsigned , mv_idx_signed ) GROUP BY j3 ORDER BY j1;
ERROR 42000: Key 'mv_idx_unsigned' doesn't exist in table 't1'
SELECT * FROM t1 IGNORE INDEX  ( mv_idx_datetime , mv_idx_char ) WHERE JSON_CONTAINS( j5 -> '$[*]' , JSON_OBJECT( 'key1' , CAST( '1981-06-09' AS DATE)) );
ERROR 42000: Key 'mv_idx_datetime' doesn't exist in table 't1'
SELECT * FROM t1  WHERE JSON_OVERLAPS( j4 -> '$[*]' , JSON_ARRAY(CAST(-69.56 AS DECIMAL (10,3)), CAST(PI() AS DECIMAL(10,5)), 1947.95) ) GROUP BY j8;
id	j1	j2	j3	j4	j5	j6	j7	j8	extra
DELETE FROM t1;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
DROP TABLE t1;
SET sql_mode = default;
CREATE TABLE t1(j json, KEY ((CAST(j->'$' AS UNSIGNED ARRAY))));
INSERT INTO t1 VALUES ('[]'), ('null');
ERROR 22018: Invalid JSON value for CAST for functional index 'functional_index'.
SELECT * FROM t1;
j
DROP TABLE t1;
CREATE TABLE t1 (id INT NOT NULL KEY AUTO_INCREMENT, f1 JSON);
CREATE INDEX i1 ON t1((CAST(f1->"$[*]" AS DECIMAL(4,2) ARRAY)));
CREATE INDEX i2 ON t1((CAST(f1->"$[*]" AS CHAR(10) ARRAY)));
START TRANSACTION;
INSERT INTO t1(f1) VALUES(CAST('["13.33","13.21"]' AS JSON));
INSERT INTO t1(f1) VALUES(NULL);
SELECT * FROM t1;
id	f1
1	["13.33", "13.21"]
2	NULL
SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
id	f1
1	["13.33", "13.21"]
ROLLBACK;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1;
id	f1
SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
id	f1
INSERT INTO t1(f1) VALUES(CAST('["13.33","13.21"]' AS JSON));
START TRANSACTION;
UPDATE t1 SET f1 = CAST('["12.34", "56.78"]' AS JSON);
SELECT * FROM t1 WHERE 12.34 MEMBER OF (f1->'$[*]');
id	f1
3	["12.34", "56.78"]
ROLLBACK;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1;
id	f1
3	["13.33", "13.21"]
SELECT * FROM t1 WHERE 12.34 MEMBER OF (f1->'$[*]');
id	f1
SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
id	f1
3	["13.33", "13.21"]
START TRANSACTION;
UPDATE t1 SET f1 = NULL;
ROLLBACK;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1;
id	f1
3	["13.33", "13.21"]
SELECT * FROM t1 WHERE 12.34 MEMBER OF (f1->'$[*]');
id	f1
SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
id	f1
3	["13.33", "13.21"]
UPDATE t1 SET f1 = NULL;
START TRANSACTION;
UPDATE t1 SET f1 = CAST('["12.34", "56.78"]' AS JSON);
SELECT * FROM t1 WHERE 12.34 MEMBER OF (f1->'$[*]');
id	f1
3	["12.34", "56.78"]
SELECT * FROM t1 WHERE 56.78 MEMBER OF (f1->'$[*]');
id	f1
3	["12.34", "56.78"]
ROLLBACK;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1;
id	f1
3	NULL
UPDATE t1 SET f1 = CAST('["13.33", "13.21"]' AS JSON);
START TRANSACTION;
DELETE FROM t1;
SELECT * FROM t1;
id	f1
SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
id	f1
SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
id	f1
ROLLBACK;
SELECT * FROM t1;
id	f1
3	["13.33", "13.21"]
SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
id	f1
3	["13.33", "13.21"]
SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
id	f1
3	["13.33", "13.21"]
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
DROP TABLE t1;
CREATE TABLE IF NOT EXISTS t1 ( j1 JSON DEFAULT (CAST('[5,-1,0]' AS JSON)), j2 JSON DEFAULT (CAST('[9,8,0,1]' AS JSON)), j3 JSON DEFAULT (CAST('["foobar"]' AS JSON)), j5 JSON DEFAULT (JSON_ARRAY(NOW())), j6 JSON DEFAULT (JSON_ARRAY(DATE(NOW()), DATE(NOW()), DATE(NOW()))), j7 JSON DEFAULT (JSON_ARRAY(TIME(NOW()))), j8 JSON DEFAULT (JSON_ARRAY(3.1456,6.022,-1)), KEY mv_idx_signed (( CAST(j1->'$[*]' AS SIGNED ARRAY) )) , KEY mv_idx_unsigned (( CAST(j2->'$[*]' AS UNSIGNED ARRAY) )) , KEY mv_idx_char (( CAST(j3->'$[*]' AS CHAR(10) ARRAY) )) , KEY mv_idx_datetime (( CAST(j5->'$[*]' AS DATETIME ARRAY) )) , KEY mv_idx_date (( CAST(j6->'$[*]' AS DATE ARRAY) )) , KEY mv_idx_time (( CAST(j7->'$[*]' AS TIME ARRAY) )) , KEY mv_idx_decimal (( CAST(j8->'$[*]' AS DECIMAL(10,4) ARRAY) )));
INSERT INTO t1( j1 ) VALUES( CAST( '[ 0, -1, 9, -4 ]' AS JSON ) );
SET autocommit = 0;
INSERT INTO t1 VALUES();
ROLLBACK;
SELECT j1, j2, j3 FROM t1;
j1	j2	j3
[0, -1, 9, -4]	[9, 8, 0, 1]	["foobar"]
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
DROP TABLE t1;
SET autocommit = default;
CREATE TABLE IF NOT EXISTS t1 (
i INT NOT NULL PRIMARY KEY,
j JSON DEFAULT (CAST('["HelloWorld"]' AS JSON)),
KEY mv_idx_binary (( CAST(j->'$[*]' AS BINARY(10) ARRAY) )) );
INSERT INTO t1 VALUES(1, CAST('["HelloWorld", "Hello"]' AS JSON));
INSERT INTO t1 VALUES(2, CAST('["HelloMySQL", "Hello"]' AS JSON));
SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
i	j
2	["HelloMySQL", "Hello"]
SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
i	j
1	["HelloWorld", "Hello"]
2	["HelloMySQL", "Hello"]
DELETE FROM t1;
START TRANSACTION;
INSERT INTO t1 VALUES(1, CAST('["HelloMySQL", "Hello"]' AS JSON));
SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
i	j
1	["HelloMySQL", "Hello"]
SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
i	j
1	["HelloMySQL", "Hello"]
ROLLBACK;
SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
i	j
SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
i	j
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
DROP TABLE t1;
# Test the UPDATE which only updates part of the multi-value field,
# to make sure the partial delete + insert works
CREATE TABLE IF NOT EXISTS t1 (
i INT NOT NULL PRIMARY KEY,
j JSON DEFAULT (CAST('["HelloWorld"]' AS JSON)),
KEY mv_idx_binary (( CAST(j->'$[*]' AS BINARY(12) ARRAY) )) );
INSERT INTO t1 VALUES(1, CAST('["HelloWorld", "Hello"]' AS JSON));
INSERT INTO t1 VALUES(2, CAST('["HelloMySQL", "Hello"]' AS JSON));
UPDATE t1 SET j = CAST('["Helloworld", "Hello"]' AS JSON);
SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
i	j
SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
i	j
SELECT * FROM t1 WHERE "Helloworld" MEMBER OF (j->'$[*]');
i	j
1	["Helloworld", "Hello"]
2	["Helloworld", "Hello"]
SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
i	j
1	["Helloworld", "Hello"]
2	["Helloworld", "Hello"]
UPDATE t1 SET j = CAST('["Helloworld", "Hello", "World", "MySQL"]' AS JSON);
SELECT * FROM t1 WHERE "MySQL" MEMBER OF (j->'$[*]');
i	j
1	["Helloworld", "Hello", "World", "MySQL"]
2	["Helloworld", "Hello", "World", "MySQL"]
SELECT * FROM t1 WHERE "World" MEMBER OF (j->'$[*]');
i	j
1	["Helloworld", "Hello", "World", "MySQL"]
2	["Helloworld", "Hello", "World", "MySQL"]
SELECT * FROM t1 WHERE "Helloworld" MEMBER OF (j->'$[*]');
i	j
1	["Helloworld", "Hello", "World", "MySQL"]
2	["Helloworld", "Hello", "World", "MySQL"]
SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
i	j
1	["Helloworld", "Hello", "World", "MySQL"]
2	["Helloworld", "Hello", "World", "MySQL"]
INSERT INTO t1 VALUES(3, CAST('["HelloInnoDB", "Hello", "MySQL", "hello"]' AS JSON));
SELECT * FROM t1 WHERE "MySQL" MEMBER OF (j->'$[*]');
i	j
1	["Helloworld", "Hello", "World", "MySQL"]
2	["Helloworld", "Hello", "World", "MySQL"]
3	["HelloInnoDB", "Hello", "MySQL", "hello"]
SELECT * FROM t1 WHERE "World" MEMBER OF (j->'$[*]');
i	j
1	["Helloworld", "Hello", "World", "MySQL"]
2	["Helloworld", "Hello", "World", "MySQL"]
SELECT * FROM t1 WHERE "Helloworld" MEMBER OF (j->'$[*]');
i	j
1	["Helloworld", "Hello", "World", "MySQL"]
2	["Helloworld", "Hello", "World", "MySQL"]
SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
i	j
1	["Helloworld", "Hello", "World", "MySQL"]
2	["Helloworld", "Hello", "World", "MySQL"]
3	["HelloInnoDB", "Hello", "MySQL", "hello"]
SELECT * FROM t1 WHERE "HelloInnoDB" MEMBER OF (j->'$[*]');
i	j
3	["HelloInnoDB", "Hello", "MySQL", "hello"]
UPDATE t1 SET j = CAST('["HelloInnoDB", "Hello", "mysql", "HELLO"]' AS JSON) WHERE "hello" MEMBER OF (j->'$[*]');
SELECT * FROM t1 WHERE "MySQL" MEMBER OF (j->'$[*]');
i	j
1	["Helloworld", "Hello", "World", "MySQL"]
2	["Helloworld", "Hello", "World", "MySQL"]
SELECT * FROM t1 WHERE "mysql" MEMBER OF (j->'$[*]');
i	j
3	["HelloInnoDB", "Hello", "mysql", "HELLO"]
SELECT * FROM t1 WHERE "World" MEMBER OF (j->'$[*]');
i	j
1	["Helloworld", "Hello", "World", "MySQL"]
2	["Helloworld", "Hello", "World", "MySQL"]
SELECT * FROM t1 WHERE "Helloworld" MEMBER OF (j->'$[*]');
i	j
1	["Helloworld", "Hello", "World", "MySQL"]
2	["Helloworld", "Hello", "World", "MySQL"]
SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
i	j
1	["Helloworld", "Hello", "World", "MySQL"]
2	["Helloworld", "Hello", "World", "MySQL"]
3	["HelloInnoDB", "Hello", "mysql", "HELLO"]
SELECT * FROM t1 WHERE "HELLO" MEMBER OF (j->'$[*]');
i	j
3	["HelloInnoDB", "Hello", "mysql", "HELLO"]
SELECT * FROM t1 WHERE "HelloInnoDB" MEMBER OF (j->'$[*]');
i	j
3	["HelloInnoDB", "Hello", "mysql", "HELLO"]
UPDATE t1 SET j = CAST('["HelloInnoDB", "HELLO"]' AS JSON) WHERE "HELLO" MEMBER OF (j->'$[*]');
SELECT * FROM t1 WHERE "MySQL" MEMBER OF (j->'$[*]');
i	j
1	["Helloworld", "Hello", "World", "MySQL"]
2	["Helloworld", "Hello", "World", "MySQL"]
SELECT * FROM t1 WHERE "mysql" MEMBER OF (j->'$[*]');
i	j
SELECT * FROM t1 WHERE "World" MEMBER OF (j->'$[*]');
i	j
1	["Helloworld", "Hello", "World", "MySQL"]
2	["Helloworld", "Hello", "World", "MySQL"]
SELECT * FROM t1 WHERE "Helloworld" MEMBER OF (j->'$[*]');
i	j
1	["Helloworld", "Hello", "World", "MySQL"]
2	["Helloworld", "Hello", "World", "MySQL"]
SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
i	j
1	["Helloworld", "Hello", "World", "MySQL"]
2	["Helloworld", "Hello", "World", "MySQL"]
SELECT * FROM t1 WHERE "HELLO" MEMBER OF (j->'$[*]');
i	j
3	["HelloInnoDB", "HELLO"]
SELECT * FROM t1 WHERE "HelloInnoDB" MEMBER OF (j->'$[*]');
i	j
3	["HelloInnoDB", "HELLO"]
DELETE FROM t1;
INSERT INTO t1 VALUES(3, CAST('["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X"]' AS JSON));
UPDATE t1 SET j = CAST('["a", "B", "C", "d", "E", "F", "g", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X"]' AS JSON);
SELECT * FROM t1 WHERE "a" MEMBER OF (j->'$[*]');
i	j
3	["a", "B", "C", "d", "E", "F", "g", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X"]
SELECT * FROM t1 WHERE "A" MEMBER OF (j->'$[*]');
i	j
SELECT * FROM t1 WHERE "d" MEMBER OF (j->'$[*]');
i	j
3	["a", "B", "C", "d", "E", "F", "g", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X"]
SELECT * FROM t1 WHERE "D" MEMBER OF (j->'$[*]');
i	j
SELECT * FROM t1 WHERE "g" MEMBER OF (j->'$[*]');
i	j
3	["a", "B", "C", "d", "E", "F", "g", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X"]
SELECT * FROM t1 WHERE "H" MEMBER OF (j->'$[*]');
i	j
3	["a", "B", "C", "d", "E", "F", "g", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X"]
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
TRUNCATE TABLE t1;
SELECT * FROM t1;
i	j
INSERT INTO t1 VALUES(1, CAST('["HelloWorld", "Hello"]' AS JSON));
INSERT INTO t1 VALUES(2, CAST('["HelloMySQL", "Hello"]' AS JSON));
SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
i	j
2	["HelloMySQL", "Hello"]
SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
i	j
1	["HelloWorld", "Hello"]
SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
i	j
1	["HelloWorld", "Hello"]
2	["HelloMySQL", "Hello"]
START TRANSACTION;
UPDATE t1 SET j = CAST('["Helloworld", "Hello"]' AS JSON);
ROLLBACK;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
i	j
2	["HelloMySQL", "Hello"]
SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
i	j
1	["HelloWorld", "Hello"]
SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
i	j
1	["HelloWorld", "Hello"]
2	["HelloMySQL", "Hello"]
SELECT * FROM t1 WHERE "Helloworld" MEMBER OF (j->'$[*]');
i	j
START TRANSACTION;
DELETE FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
i	j
2	["HelloMySQL", "Hello"]
UPDATE t1 SET j = CAST('["HelloMySQL", "Hello", "hello", "mysql"]' AS JSON);
SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
i	j
2	["HelloMySQL", "Hello", "hello", "mysql"]
SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
i	j
2	["HelloMySQL", "Hello", "hello", "mysql"]
SELECT * FROM t1 WHERE "mysql" MEMBER OF (j->'$[*]');
i	j
2	["HelloMySQL", "Hello", "hello", "mysql"]
ROLLBACK;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
i	j
2	["HelloMySQL", "Hello"]
SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
i	j
1	["HelloWorld", "Hello"]
2	["HelloMySQL", "Hello"]
SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
i	j
SELECT * FROM t1 WHERE "mysql" MEMBER OF (j->'$[*]');
i	j
DELETE FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
i	j
2	["HelloMySQL", "Hello"]
UPDATE t1 SET j = CAST('["HelloMySQL", "Hello", "hello", "mysql", "MySQL"]' AS JSON);
START TRANSACTION;
UPDATE t1 SET j = CAST('["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]' AS JSON);
SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
i	j
2	["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
i	j
2	["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
SELECT * FROM t1 WHERE "mysql" MEMBER OF (j->'$[*]');
i	j
SELECT * FROM t1 WHERE "mySQL" MEMBER OF (j->'$[*]');
i	j
2	["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
i	j
2	["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
UPDATE t1 SET j = CAST('["HelloMySQL", "Hello"]' AS JSON);
SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
i	j
2	["HelloMySQL", "Hello"]
SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
i	j
SELECT * FROM t1 WHERE "mysql" MEMBER OF (j->'$[*]');
i	j
SELECT * FROM t1 WHERE "mySQL" MEMBER OF (j->'$[*]');
i	j
SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
i	j
ROLLBACK;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
i	j
2	["HelloMySQL", "Hello", "hello", "mysql", "MySQL"]
SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
i	j
2	["HelloMySQL", "Hello", "hello", "mysql", "MySQL"]
SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
i	j
2	["HelloMySQL", "Hello", "hello", "mysql", "MySQL"]
SELECT * FROM t1 WHERE "mysql" MEMBER OF (j->'$[*]');
i	j
2	["HelloMySQL", "Hello", "hello", "mysql", "MySQL"]
TRUNCATE TABLE t1;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1;
i	j
INSERT INTO t1 VALUES(1, CAST('["HelloWorld", "Hello"]' AS JSON));
INSERT INTO t1 VALUES(2, CAST('["HelloMySQL", "Hello"]' AS JSON));
UPDATE t1 SET i = 3 WHERE i = 2;
UPDATE t1 SET i = 4, j = CAST('["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]' AS JSON) WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
i	j
4	["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
i	j
1	["HelloWorld", "Hello"]
4	["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
i	j
4	["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
i	j
4	["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
# Force a multi-table UPDATE, to test:
# Bug #98691: Updating a multi-table view has problem if one sub-table is constant
CREATE VIEW vv AS SELECT t1.* FROM t1 STRAIGHT_JOIN (SELECT 1) AS dt;
EXPLAIN UPDATE vv SET i = 3 WHERE i = 4;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t1	NULL	const	PRIMARY	PRIMARY	4	const	1	100.00	NULL
1	PRIMARY	<derived3>	NULL	system	NULL	NULL	NULL	NULL	1	100.00	NULL
3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	update (`test`.`t1` straight_join (/* select#3 */ select 1 AS `1`) `dt`) set `test`.`t1`.`i` = 3 where true
UPDATE vv SET i = 3 WHERE i = 4;
UPDATE vv SET i = 4 WHERE i = 3;
DROP VIEW vv;
START TRANSACTION;
UPDATE t1 SET i = 5 WHERE i = 4;
ROLLBACK;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
i	j
4	["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
i	j
1	["HelloWorld", "Hello"]
4	["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
i	j
4	["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
i	j
4	["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
START TRANSACTION;
UPDATE t1 SET i = 6, j = CAST('["HelloMySQL", "mySQL", "MYSQL"]' AS JSON) WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
i	j
6	["HelloMySQL", "mySQL", "MYSQL"]
SELECT * FROM t1 WHERE "mySQL" MEMBER OF (j->'$[*]');
i	j
6	["HelloMySQL", "mySQL", "MYSQL"]
SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
i	j
6	["HelloMySQL", "mySQL", "MYSQL"]
ROLLBACK;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
i	j
4	["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
i	j
1	["HelloWorld", "Hello"]
4	["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
i	j
4	["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
i	j
4	["HelloMySQL", "Hello", "hello", "mySQL", "MYSQL"]
DROP TABLE t1;
CREATE TABLE t1 (
id INT NOT NULL PRIMARY KEY,
a INT,
b INT,
j JSON DEFAULT (CAST('["HelloWorld"]' AS JSON)),
KEY mv_idx_binary(a, (( CAST(j->'$[*]' AS BINARY(12) ARRAY) )), b));
INSERT INTO t1 VALUES(1, 1, 1, CAST('["HelloWorld", "Hello"]' AS JSON));
UPDATE t1 SET b = 2;
SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
id	a	b	j
1	1	2	["HelloWorld", "Hello"]
SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
id	a	b	j
1	1	2	["HelloWorld", "Hello"]
UPDATE t1 SET a = 2;
SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
id	a	b	j
1	2	2	["HelloWorld", "Hello"]
SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
id	a	b	j
1	2	2	["HelloWorld", "Hello"]
UPDATE t1 SET b = 3, j = CAST('["HelloMySQL", "mySQL", "MYSQL"]' AS JSON) WHERE "HelloWorld" MEMBER OF (j->'$[*]');
SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
id	a	b	j
SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
id	a	b	j
1	2	3	["HelloMySQL", "mySQL", "MYSQL"]
UPDATE t1 SET a = 3, j = CAST('["HelloMySQL", "mySQL", "MYSQL", "hello"]' AS JSON) WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
id	a	b	j
SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
id	a	b	j
1	3	3	["HelloMySQL", "mySQL", "MYSQL", "hello"]
SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
id	a	b	j
1	3	3	["HelloMySQL", "mySQL", "MYSQL", "hello"]
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
TRUNCATE TABLE t1;
INSERT INTO t1 VALUES(1, 1, 1, CAST('["HelloWorld", "Hello"]' AS JSON));
START TRANSACTION;
UPDATE t1 SET b = 2;
SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
id	a	b	j
1	1	2	["HelloWorld", "Hello"]
SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
id	a	b	j
1	1	2	["HelloWorld", "Hello"]
UPDATE t1 SET a = 2;
SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
id	a	b	j
1	2	2	["HelloWorld", "Hello"]
SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
id	a	b	j
1	2	2	["HelloWorld", "Hello"]
ROLLBACK;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
id	a	b	j
1	1	1	["HelloWorld", "Hello"]
SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
id	a	b	j
1	1	1	["HelloWorld", "Hello"]
START TRANSACTION;
UPDATE t1 SET b = 3, j = CAST('["HelloMySQL", "mySQL", "MYSQL"]' AS JSON) WHERE "HelloWorld" MEMBER OF (j->'$[*]');
SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
id	a	b	j
1	1	3	["HelloMySQL", "mySQL", "MYSQL"]
UPDATE t1 SET a = 3, j = CAST('["HelloMySQL", "mySQL", "MYSQL", "hello"]' AS JSON) WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
id	a	b	j
SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
id	a	b	j
1	3	3	["HelloMySQL", "mySQL", "MYSQL", "hello"]
SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
id	a	b	j
1	3	3	["HelloMySQL", "mySQL", "MYSQL", "hello"]
ROLLBACK;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SELECT * FROM t1 WHERE "MYSQL" MEMBER OF (j->'$[*]');
id	a	b	j
SELECT * FROM t1 WHERE "hello" MEMBER OF (j->'$[*]');
id	a	b	j
SELECT * FROM t1 WHERE "HelloWorld" MEMBER OF (j->'$[*]');
id	a	b	j
1	1	1	["HelloWorld", "Hello"]
SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
id	a	b	j
1	1	1	["HelloWorld", "Hello"]
DROP TABLE t1;
CREATE TABLE t1 (
id int NOT NULL,
j JSON DEFAULT NULL,
PRIMARY KEY (id),
KEY j_key123 ((CAST(json_extract(j,_utf8mb4'$') AS CHAR(64) ARRAY)))
);
INSERT INTO t1 values (1000,  CONCAT('[""]'));
DROP TABLE t1;
CREATE TABLE t1 (i INT, j JSON, KEY mv_idx_int ((CAST(j->'$[*]' AS SIGNED ARRAY))) ) PARTITION BY KEY(i) PARTITIONS 2;
INSERT INTO t1 VALUES(10,'[4, 5, 6]'), (1, '[1, 2, 3]'), (5, '[3, 4, 5]'), (8, '[4, 14, 24]');
SELECT * FROM t1 PARTITION (p0) WHERE 4 MEMBER OF (j->'$[*]');
i	j
5	[3, 4, 5]
SELECT * FROM t1 PARTITION (p1) WHERE 4 MEMBER OF (j->'$[*]');
i	j
10	[4, 5, 6]
8	[4, 14, 24]
SELECT * FROM t1 WHERE 4 MEMBER OF (j->'$[*]');
i	j
5	[3, 4, 5]
10	[4, 5, 6]
8	[4, 14, 24]
SELECT * FROM t1 PARTITION (p0) WHERE 1 MEMBER OF (j->'$[*]');
i	j
1	[1, 2, 3]
SELECT * FROM t1 PARTITION (p1) WHERE 1 MEMBER OF (j->'$[*]');
i	j
SELECT * FROM t1 WHERE 1 MEMBER OF (j->'$[*]');
i	j
1	[1, 2, 3]
DROP TABLE t1;
#
# Bug#33334928: SELECT COUNT(*) using multi-valued index
#               reports wrong number of rows.
#
CREATE TABLE t (
id int NOT NULL,
doc_id int DEFAULT NULL,
doc json DEFAULT NULL,
PRIMARY KEY (id),
KEY mv_id (id,(cast(json_extract(doc,'$.val') as unsigned array))),
KEY mv_doc_id (doc_id,(cast(json_extract(doc,'$.val') as unsigned array)))
);
INSERT INTO t values (1, 2, '{"val":[1,2,3,4,5]}'), (2, 1, '{"val":[2,3,4,5]}'), (3, 2, '{"val":[2,3,4,5]}'), (4, NULL, '{"val":[3,4,5]}'), (5, 2, '{"val":[4,5]}');
ANALYZE TABLE t;
Table	Op	Msg_type	Msg_text
test.t	analyze	status	OK
EXPLAIN SELECT COUNT(*) FROM t WHERE id = '1';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	const	PRIMARY,mv_id	PRIMARY	4	const	1	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t` where true
SELECT COUNT(*) FROM t WHERE id = '1';
COUNT(*)
1
EXPLAIN SELECT COUNT(*) FROM t FORCE INDEX (mv_id) WHERE id = '1';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ref	mv_id	mv_id	4	const	5	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t` FORCE INDEX (`mv_id`) where (`test`.`t`.`id` = 1)
SELECT COUNT(*) FROM t FORCE INDEX (mv_id) WHERE id = '1';
COUNT(*)
1
EXPLAIN SELECT COUNT(*) FROM t FORCE INDEX (mv_doc_id) WHERE doc_id = '2';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ref	mv_doc_id	mv_doc_id	5	const	11	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t` FORCE INDEX (`mv_doc_id`) where (`test`.`t`.`doc_id` = 2)
SELECT COUNT(*) FROM t FORCE INDEX (mv_doc_id) WHERE doc_id = '2';
COUNT(*)
3
EXPLAIN SELECT COUNT(*) FROM t FORCE INDEX (mv_doc_id) WHERE doc_id = '2' OR doc_id IS NULL;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ref_or_null	mv_doc_id	mv_doc_id	5	const	14	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t` FORCE INDEX (`mv_doc_id`) where ((`test`.`t`.`doc_id` = 2) or (`test`.`t`.`doc_id` is null))
SELECT COUNT(*) FROM t FORCE INDEX (mv_doc_id) WHERE doc_id = '2' OR doc_id IS NULL;
COUNT(*)
4
EXPLAIN SELECT * FROM t WHERE id = '1';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	const	PRIMARY,mv_id	PRIMARY	4	const	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select '1' AS `id`,'2' AS `doc_id`,'{"val": [1, 2, 3, 4, 5]}' AS `doc` from `test`.`t` where true
SELECT * FROM t WHERE id = '1';
id	doc_id	doc
1	2	{"val": [1, 2, 3, 4, 5]}
EXPLAIN SELECT * FROM t FORCE INDEX (mv_id) WHERE id = '1';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ref	mv_id	mv_id	4	const	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t`.`id` AS `id`,`test`.`t`.`doc_id` AS `doc_id`,`test`.`t`.`doc` AS `doc` from `test`.`t` FORCE INDEX (`mv_id`) where (`test`.`t`.`id` = 1)
SELECT * FROM t FORCE INDEX (mv_id) WHERE id = '1';
id	doc_id	doc
1	2	{"val": [1, 2, 3, 4, 5]}
EXPLAIN SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id = '2';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ref	mv_doc_id	mv_doc_id	5	const	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t`.`id` AS `id`,`test`.`t`.`doc_id` AS `doc_id`,`test`.`t`.`doc` AS `doc` from `test`.`t` FORCE INDEX (`mv_doc_id`) where (`test`.`t`.`doc_id` = 2)
SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id = '2';
id	doc_id	doc
1	2	{"val": [1, 2, 3, 4, 5]}
3	2	{"val": [2, 3, 4, 5]}
5	2	{"val": [4, 5]}
EXPLAIN SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id = '2' OR doc_id IS NULL;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ref_or_null	mv_doc_id	mv_doc_id	5	const	2	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t`.`id` AS `id`,`test`.`t`.`doc_id` AS `doc_id`,`test`.`t`.`doc` AS `doc` from `test`.`t` FORCE INDEX (`mv_doc_id`) where ((`test`.`t`.`doc_id` = 2) or (`test`.`t`.`doc_id` is null))
SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id = '2' OR doc_id IS NULL;
id	doc_id	doc
1	2	{"val": [1, 2, 3, 4, 5]}
3	2	{"val": [2, 3, 4, 5]}
4	NULL	{"val": [3, 4, 5]}
5	2	{"val": [4, 5]}
EXPLAIN SELECT * FROM t WHERE doc_id is NOT NULL;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	mv_doc_id	NULL	NULL	NULL	5	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t`.`id` AS `id`,`test`.`t`.`doc_id` AS `doc_id`,`test`.`t`.`doc` AS `doc` from `test`.`t` where (`test`.`t`.`doc_id` is not null)
SELECT * FROM t WHERE doc_id is NOT NULL;
id	doc_id	doc
1	2	{"val": [1, 2, 3, 4, 5]}
2	1	{"val": [2, 3, 4, 5]}
3	2	{"val": [2, 3, 4, 5]}
5	2	{"val": [4, 5]}
EXPLAIN SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id is NOT NULL;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	range	mv_doc_id	mv_doc_id	5	NULL	15	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t`.`id` AS `id`,`test`.`t`.`doc_id` AS `doc_id`,`test`.`t`.`doc` AS `doc` from `test`.`t` FORCE INDEX (`mv_doc_id`) where (`test`.`t`.`doc_id` is not null)
SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id is NOT NULL;
id	doc_id	doc
1	2	{"val": [1, 2, 3, 4, 5]}
2	1	{"val": [2, 3, 4, 5]}
3	2	{"val": [2, 3, 4, 5]}
5	2	{"val": [4, 5]}
EXPLAIN SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id <> 0;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	range	mv_doc_id	mv_doc_id	5	NULL	2	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t`.`id` AS `id`,`test`.`t`.`doc_id` AS `doc_id`,`test`.`t`.`doc` AS `doc` from `test`.`t` FORCE INDEX (`mv_doc_id`) where (`test`.`t`.`doc_id` <> 0)
SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id <> 0;
id	doc_id	doc
1	2	{"val": [1, 2, 3, 4, 5]}
2	1	{"val": [2, 3, 4, 5]}
3	2	{"val": [2, 3, 4, 5]}
5	2	{"val": [4, 5]}
EXPLAIN SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id > 0;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	range	mv_doc_id	mv_doc_id	5	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t`.`id` AS `id`,`test`.`t`.`doc_id` AS `doc_id`,`test`.`t`.`doc` AS `doc` from `test`.`t` FORCE INDEX (`mv_doc_id`) where (`test`.`t`.`doc_id` > 0)
SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id > 0;
id	doc_id	doc
1	2	{"val": [1, 2, 3, 4, 5]}
2	1	{"val": [2, 3, 4, 5]}
3	2	{"val": [2, 3, 4, 5]}
5	2	{"val": [4, 5]}
EXPLAIN SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id < 2 OR doc_id IS NULL;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	range	mv_doc_id	mv_doc_id	5	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t`.`id` AS `id`,`test`.`t`.`doc_id` AS `doc_id`,`test`.`t`.`doc` AS `doc` from `test`.`t` FORCE INDEX (`mv_doc_id`) where ((`test`.`t`.`doc_id` < 2) or (`test`.`t`.`doc_id` is null))
SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id < 2 OR doc_id IS NULL;
id	doc_id	doc
2	1	{"val": [2, 3, 4, 5]}
4	NULL	{"val": [3, 4, 5]}
EXPLAIN SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id >= 1 AND doc_id < 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	range	mv_doc_id	mv_doc_id	5	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t`.`id` AS `id`,`test`.`t`.`doc_id` AS `doc_id`,`test`.`t`.`doc` AS `doc` from `test`.`t` FORCE INDEX (`mv_doc_id`) where ((`test`.`t`.`doc_id` >= 1) and (`test`.`t`.`doc_id` < 2))
SELECT * FROM t FORCE INDEX (mv_doc_id) WHERE doc_id >= 1 AND doc_id < 2;
id	doc_id	doc
2	1	{"val": [2, 3, 4, 5]}
DROP TABLE t;
#
# Bug #32725063 CRASH WITH MULTI-VALUED INDEXES IN
# COMBINATION WITH AT LEAST GENERATED FK
CREATE TABLE t1 (id BIGINT NOT NULL AUTO_INCREMENT, data JSON,PRIMARY KEY (id));
ALTER TABLE t1 ADD INDEX ix_t1_idArray ((CAST(data->'$.idArray' AS UNSIGNED ARRAY))),
ADD COLUMN parent_id BIGINT GENERATED ALWAYS AS (data->'$.parentID') STORED,
ADD INDEX ix_t1_parent (parent_id),
ADD CONSTRAINT fk_t1_parent FOREIGN KEY (parent_id) REFERENCES t1 (id) ON DELETE CASCADE;
INSERT INTO t1 (data) VALUES ('{\"idArray\":[1]}');
INSERT INTO t1 (data) VALUES ('{\"parentID\":1,\"idArray\":[1]}');
DELETE FROM t1 WHERE id = 1;
DROP TABLE t1;
CREATE TABLE t1 (id BIGINT NOT NULL , PRIMARY KEY (id));
CREATE TABLE t2(data JSON,child_id bigint GENERATED ALWAYS AS (data->'$.parentID') STORED,key(child_id));
ALTER TABLE t2 ADD INDEX ix_t1_idArray ((CAST(data->'$.idArray' AS UNSIGNED ARRAY)));
ALTER TABLE t2 ADD CONSTRAINT fk_t1_parent FOREIGN KEY (child_id) REFERENCES t1 (id) ON DELETE CASCADE;
INSERT INTO t1 VALUES (1);
INSERT INTO t2 (data) VALUES ('{\"parentID\":1,\"idArray\":[1]}');
DELETE FROM t1 WHERE id = 1;
DROP TABLE t2,t1;