From 027237367f530fd30c433de0cd24c9e54cd59858 Mon Sep 17 00:00:00 2001
From: Yubao Liu <yubao.liu@gmail.com>
Date: Mon, 28 Jun 2021 07:24:30 +0800
Subject: [PATCH 1/4] Bug#104700: enable multi-valued index in view

REF_ITEM was not considered in Item::can_be_substituted_for_gc() and get_gc_for_expr(),
so optimizer failed to use multi-valued index in view.
---
 .../json/r/multi-valued-index-in-view.result  | 86 +++++++++++++++++++
 .../json/t/multi-valued-index-in-view.test    | 45 ++++++++++
 sql/item.cc                                   |  2 +-
 sql/item_func.cc                              |  1 +
 4 files changed, 133 insertions(+), 1 deletion(-)
 create mode 100644 mysql-test/suite/json/r/multi-valued-index-in-view.result
 create mode 100644 mysql-test/suite/json/t/multi-valued-index-in-view.test

diff --git a/mysql-test/suite/json/r/multi-valued-index-in-view.result b/mysql-test/suite/json/r/multi-valued-index-in-view.result
new file mode 100644
index 000000000000..303ea762e855
--- /dev/null
+++ b/mysql-test/suite/json/r/multi-valued-index-in-view.result
@@ -0,0 +1,86 @@
+DROP TABLE IF EXISTS t1;
+DROP VIEW IF EXISTS v1;
+CREATE TABLE t1 (
+f1 VARCHAR(50) NOT NULL PRIMARY KEY,
+f2 JSON NOT NULL,
+INDEX idx2 ( (CAST(f2 AS CHAR(50) ARRAY)) )
+);
+CREATE VIEW v1 AS
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids;
+INSERT INTO t1 VALUES ('foo', '["aa", "bb"]');
+INSERT INTO t1 VALUES ('bar', '["xx", "yy"]');
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+SELECT * FROM t1;
+f1	f2
+bar	["xx", "yy"]
+foo	["aa", "bb"]
+EXPLAIN SELECT * FROM t1 WHERE 'xx' member of (f2);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	ref	idx2	idx2	203	const	1	100.00	Using where
+EXPLAIN SELECT * FROM t1 WHERE json_contains(f2, '"xx"');
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	range	idx2	idx2	203	NULL	1	100.00	Using where
+EXPLAIN SELECT * FROM t1 WHERE json_overlaps(f2, '["xx", "zz"]');
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	range	idx2	idx2	203	NULL	2	100.00	Using where
+EXPLAIN SELECT * FROM v1 WHERE 'xx' member of (f2);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	ref	idx2	idx2	203	const	1	100.00	Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+EXPLAIN SELECT * FROM v1 WHERE json_contains(f2, '"xx"');
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	range	idx2	idx2	203	NULL	1	100.00	Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+EXPLAIN SELECT * FROM v1 WHERE json_overlaps(f2, '["xx", "zz"]');
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	range	idx2	idx2	203	NULL	2	100.00	Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE 'xx' member of (f2);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	ref	idx2	idx2	203	const	1	100.00	Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_contains(f2, '"xx"');
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	range	idx2	idx2	203	NULL	1	100.00	Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_overlaps(f2, '["xx", "zz"]');
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	range	idx2	idx2	203	NULL	2	100.00	Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+SELECT * FROM t1 WHERE 'xx' member of (f2);
+f1	f2
+bar	["xx", "yy"]
+SELECT * FROM t1 WHERE json_contains(f2, '"xx"');
+f1	f2
+bar	["xx", "yy"]
+SELECT * FROM t1 WHERE json_overlaps(f2, '["xx", "zz"]');
+f1	f2
+bar	["xx", "yy"]
+SELECT * FROM v1 WHERE 'xx' member of (f2);
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+SELECT * FROM v1 WHERE json_contains(f2, '"xx"');
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+SELECT * FROM v1 WHERE json_overlaps(f2, '["xx", "zz"]');
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE 'xx' member of (f2);
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_contains(f2, '"xx"');
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_overlaps(f2, '["xx", "zz"]');
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+DROP TABLE t1;
+DROP VIEW v1;
diff --git a/mysql-test/suite/json/t/multi-valued-index-in-view.test b/mysql-test/suite/json/t/multi-valued-index-in-view.test
new file mode 100644
index 000000000000..7f3891818f11
--- /dev/null
+++ b/mysql-test/suite/json/t/multi-valued-index-in-view.test
@@ -0,0 +1,45 @@
+--disable_warnings
+
+DROP TABLE IF EXISTS t1;
+DROP VIEW IF EXISTS v1;
+
+CREATE TABLE t1 (
+  f1 VARCHAR(50) NOT NULL PRIMARY KEY,
+  f2 JSON NOT NULL,
+  INDEX idx2 ( (CAST(f2 AS CHAR(50) ARRAY)) )
+);
+
+CREATE VIEW v1 AS
+  SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids;
+
+INSERT INTO t1 VALUES ('foo', '["aa", "bb"]');
+INSERT INTO t1 VALUES ('bar', '["xx", "yy"]');
+
+ANALYZE TABLE t1;
+
+SELECT * FROM t1;
+
+EXPLAIN SELECT * FROM t1 WHERE 'xx' member of (f2);
+EXPLAIN SELECT * FROM t1 WHERE json_contains(f2, '"xx"');
+EXPLAIN SELECT * FROM t1 WHERE json_overlaps(f2, '["xx", "zz"]');
+EXPLAIN SELECT * FROM v1 WHERE 'xx' member of (f2);
+EXPLAIN SELECT * FROM v1 WHERE json_contains(f2, '"xx"');
+EXPLAIN SELECT * FROM v1 WHERE json_overlaps(f2, '["xx", "zz"]');
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE 'xx' member of (f2);
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_contains(f2, '"xx"');
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_overlaps(f2, '["xx", "zz"]');
+
+SELECT * FROM t1 WHERE 'xx' member of (f2);
+SELECT * FROM t1 WHERE json_contains(f2, '"xx"');
+SELECT * FROM t1 WHERE json_overlaps(f2, '["xx", "zz"]');
+SELECT * FROM v1 WHERE 'xx' member of (f2);
+SELECT * FROM v1 WHERE json_contains(f2, '"xx"');
+SELECT * FROM v1 WHERE json_overlaps(f2, '["xx", "zz"]');
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE 'xx' member of (f2);
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_contains(f2, '"xx"');
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_overlaps(f2, '["xx", "zz"]');
+
+DROP TABLE t1;
+DROP VIEW v1;
+
+--enable_warnings
diff --git a/sql/item.cc b/sql/item.cc
index a0fcca24392b..e30e19720690 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -7074,7 +7074,7 @@ bool Item::cache_const_expr_analyzer(uchar **arg) {
 }
 
 bool Item::can_be_substituted_for_gc(bool array) const {
-  switch (type()) {
+  switch (const_cast<Item *>(this)->real_item()->type()) {
     case FUNC_ITEM:
     case COND_ITEM:
       return true;
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 46f365e9fabf..ac53d82d8951 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -1018,6 +1018,7 @@ static bool is_function_of_type(const Item *item, Item_func::Functype type) {
 
 Item_field *get_gc_for_expr(const Item *func, Field *fld, Item_result type,
                             Field **found) {
+  func = const_cast<Item *>(func)->real_item();
   Item *expr = fld->gcol_info->expr_item;
 
   /*

From 6aedafb942acb3c9dbaacbbdcdfd24137353710b Mon Sep 17 00:00:00 2001
From: Yubao Liu <yubao.liu@gmail.com>
Date: Sat, 21 Aug 2021 10:25:44 +0800
Subject: [PATCH 2/4] Bug#104700: enable multi-valued index in prepare
 statement

Parameters in prepare statement are not constant literal but if they are
constant during statement execution the multi-valued index should be picked.

This also fixes Bug#104325.
---
 .../r/multi-valued-index-in-prepare.result    | 122 ++++++++++++++++++
 .../json/t/multi-valued-index-in-prepare.test |  82 ++++++++++++
 sql/item_func.cc                              |  14 +-
 3 files changed, 211 insertions(+), 7 deletions(-)
 create mode 100644 mysql-test/suite/json/r/multi-valued-index-in-prepare.result
 create mode 100644 mysql-test/suite/json/t/multi-valued-index-in-prepare.test

diff --git a/mysql-test/suite/json/r/multi-valued-index-in-prepare.result b/mysql-test/suite/json/r/multi-valued-index-in-prepare.result
new file mode 100644
index 000000000000..3b7177be5cee
--- /dev/null
+++ b/mysql-test/suite/json/r/multi-valued-index-in-prepare.result
@@ -0,0 +1,122 @@
+DROP TABLE IF EXISTS t1;
+DROP VIEW IF EXISTS v1;
+CREATE TABLE t1 (
+f1 VARCHAR(50) NOT NULL PRIMARY KEY,
+f2 JSON NOT NULL,
+INDEX idx2 ( (CAST(f2 AS CHAR(50) ARRAY)) )
+);
+CREATE VIEW v1 AS
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids;
+INSERT INTO t1 VALUES ('foo', '["aa", "bb"]');
+INSERT INTO t1 VALUES ('bar', '["xx", "yy"]');
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+SELECT * FROM t1;
+f1	f2
+bar	["xx", "yy"]
+foo	["aa", "bb"]
+PREPARE stmt FROM 'EXPLAIN SELECT * FROM t1 WHERE ? member of (f2)';
+SET @a='xx';
+EXECUTE stmt USING @a;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	ref	idx2	idx2	203	const	1	100.00	Using where
+PREPARE stmt FROM 'EXPLAIN SELECT * FROM t1 WHERE json_contains(f2, ?)';
+SET @a='"xx"';
+EXECUTE stmt USING @a;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	range	idx2	idx2	203	NULL	1	100.00	Using where
+PREPARE stmt FROM 'EXPLAIN SELECT * FROM t1 WHERE json_overlaps(f2, ?)';
+SET @a='["xx", "cc"]';
+EXECUTE stmt USING @a;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	range	idx2	idx2	203	NULL	2	100.00	Using where
+PREPARE stmt FROM 'EXPLAIN SELECT * FROM v1 WHERE ? member of (f2)';
+SET @a='xx';
+EXECUTE stmt USING @a;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	ref	idx2	idx2	203	const	1	100.00	Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+PREPARE stmt FROM 'EXPLAIN SELECT * FROM v1 WHERE json_contains(f2, ?)';
+SET @a='"xx"';
+EXECUTE stmt USING @a;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	range	idx2	idx2	203	NULL	1	100.00	Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+PREPARE stmt FROM 'EXPLAIN SELECT * FROM v1 WHERE json_overlaps(f2, ?)';
+SET @a='["xx", "cc"]';
+EXECUTE stmt USING @a;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	range	idx2	idx2	203	NULL	2	100.00	Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+PREPARE stmt FROM 'EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, \'$[*]\' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH \'$\')) AS ids WHERE ? member of (f2)';
+SET @a='xx';
+EXECUTE stmt USING @a;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	ref	idx2	idx2	203	const	1	100.00	Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+PREPARE stmt FROM 'EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, \'$[*]\' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH \'$\')) AS ids WHERE json_contains(f2, ?)';
+SET @a='"xx"';
+EXECUTE stmt USING @a;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	range	idx2	idx2	203	NULL	1	100.00	Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+PREPARE stmt FROM 'EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, \'$[*]\' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH \'$\')) AS ids WHERE json_overlaps(f2, ?)';
+SET @a='["xx", "cc"]';
+EXECUTE stmt USING @a;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	range	idx2	idx2	203	NULL	2	100.00	Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+PREPARE stmt FROM 'SELECT * FROM t1 WHERE ? member of (f2)';
+SET @a='xx';
+EXECUTE stmt USING @a;
+f1	f2
+bar	["xx", "yy"]
+PREPARE stmt FROM 'SELECT * FROM t1 WHERE json_contains(f2, ?)';
+SET @a='"xx"';
+EXECUTE stmt USING @a;
+f1	f2
+bar	["xx", "yy"]
+PREPARE stmt FROM 'SELECT * FROM t1 WHERE json_overlaps(f2, ?)';
+SET @a='["xx", "cc"]';
+EXECUTE stmt USING @a;
+f1	f2
+bar	["xx", "yy"]
+PREPARE stmt FROM 'SELECT * FROM v1 WHERE ? member of (f2)';
+SET @a='xx';
+EXECUTE stmt USING @a;
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+PREPARE stmt FROM 'SELECT * FROM v1 WHERE json_contains(f2, ?)';
+SET @a='"xx"';
+EXECUTE stmt USING @a;
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+PREPARE stmt FROM 'SELECT * FROM v1 WHERE json_overlaps(f2, ?)';
+SET @a='["xx", "cc"]';
+EXECUTE stmt USING @a;
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+PREPARE stmt FROM 'SELECT * FROM t1, JSON_TABLE(f2, \'$[*]\' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH \'$\')) AS ids WHERE ? member of (f2)';
+SET @a='xx';
+EXECUTE stmt USING @a;
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+PREPARE stmt FROM 'SELECT * FROM t1, JSON_TABLE(f2, \'$[*]\' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH \'$\')) AS ids WHERE json_contains(f2, ?)';
+SET @a='"xx"';
+EXECUTE stmt USING @a;
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+PREPARE stmt FROM 'SELECT * FROM t1, JSON_TABLE(f2, \'$[*]\' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH \'$\')) AS ids WHERE json_overlaps(f2, ?)';
+SET @a='["xx", "cc"]';
+EXECUTE stmt USING @a;
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+DROP TABLE t1;
+DROP VIEW v1;
diff --git a/mysql-test/suite/json/t/multi-valued-index-in-prepare.test b/mysql-test/suite/json/t/multi-valued-index-in-prepare.test
new file mode 100644
index 000000000000..dfd13607981a
--- /dev/null
+++ b/mysql-test/suite/json/t/multi-valued-index-in-prepare.test
@@ -0,0 +1,82 @@
+--disable_warnings
+
+DROP TABLE IF EXISTS t1;
+DROP VIEW IF EXISTS v1;
+
+CREATE TABLE t1 (
+  f1 VARCHAR(50) NOT NULL PRIMARY KEY,
+  f2 JSON NOT NULL,
+  INDEX idx2 ( (CAST(f2 AS CHAR(50) ARRAY)) )
+);
+
+CREATE VIEW v1 AS
+  SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids;
+
+INSERT INTO t1 VALUES ('foo', '["aa", "bb"]');
+INSERT INTO t1 VALUES ('bar', '["xx", "yy"]');
+
+ANALYZE TABLE t1;
+
+SELECT * FROM t1;
+
+PREPARE stmt FROM 'EXPLAIN SELECT * FROM t1 WHERE ? member of (f2)';
+        SET @a='xx';
+        EXECUTE stmt USING @a;
+PREPARE stmt FROM 'EXPLAIN SELECT * FROM t1 WHERE json_contains(f2, ?)';
+        SET @a='"xx"';
+        EXECUTE stmt USING @a;
+PREPARE stmt FROM 'EXPLAIN SELECT * FROM t1 WHERE json_overlaps(f2, ?)';
+        SET @a='["xx", "cc"]';
+        EXECUTE stmt USING @a;
+PREPARE stmt FROM 'EXPLAIN SELECT * FROM v1 WHERE ? member of (f2)';
+        SET @a='xx';
+        EXECUTE stmt USING @a;
+PREPARE stmt FROM 'EXPLAIN SELECT * FROM v1 WHERE json_contains(f2, ?)';
+        SET @a='"xx"';
+        EXECUTE stmt USING @a;
+PREPARE stmt FROM 'EXPLAIN SELECT * FROM v1 WHERE json_overlaps(f2, ?)';
+        SET @a='["xx", "cc"]';
+        EXECUTE stmt USING @a;
+PREPARE stmt FROM 'EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, \'$[*]\' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH \'$\')) AS ids WHERE ? member of (f2)';
+        SET @a='xx';
+        EXECUTE stmt USING @a;
+PREPARE stmt FROM 'EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, \'$[*]\' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH \'$\')) AS ids WHERE json_contains(f2, ?)';
+        SET @a='"xx"';
+        EXECUTE stmt USING @a;
+PREPARE stmt FROM 'EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, \'$[*]\' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH \'$\')) AS ids WHERE json_overlaps(f2, ?)';
+        SET @a='["xx", "cc"]';
+        EXECUTE stmt USING @a;
+
+
+PREPARE stmt FROM 'SELECT * FROM t1 WHERE ? member of (f2)';
+        SET @a='xx';
+        EXECUTE stmt USING @a;
+PREPARE stmt FROM 'SELECT * FROM t1 WHERE json_contains(f2, ?)';
+        SET @a='"xx"';
+        EXECUTE stmt USING @a;
+PREPARE stmt FROM 'SELECT * FROM t1 WHERE json_overlaps(f2, ?)';
+        SET @a='["xx", "cc"]';
+        EXECUTE stmt USING @a;
+PREPARE stmt FROM 'SELECT * FROM v1 WHERE ? member of (f2)';
+        SET @a='xx';
+        EXECUTE stmt USING @a;
+PREPARE stmt FROM 'SELECT * FROM v1 WHERE json_contains(f2, ?)';
+        SET @a='"xx"';
+        EXECUTE stmt USING @a;
+PREPARE stmt FROM 'SELECT * FROM v1 WHERE json_overlaps(f2, ?)';
+        SET @a='["xx", "cc"]';
+        EXECUTE stmt USING @a;
+PREPARE stmt FROM 'SELECT * FROM t1, JSON_TABLE(f2, \'$[*]\' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH \'$\')) AS ids WHERE ? member of (f2)';
+        SET @a='xx';
+        EXECUTE stmt USING @a;
+PREPARE stmt FROM 'SELECT * FROM t1, JSON_TABLE(f2, \'$[*]\' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH \'$\')) AS ids WHERE json_contains(f2, ?)';
+        SET @a='"xx"';
+        EXECUTE stmt USING @a;
+PREPARE stmt FROM 'SELECT * FROM t1, JSON_TABLE(f2, \'$[*]\' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH \'$\')) AS ids WHERE json_overlaps(f2, ?)';
+        SET @a='["xx", "cc"]';
+        EXECUTE stmt USING @a;
+
+DROP TABLE t1;
+DROP VIEW v1;
+
+--enable_warnings
diff --git a/sql/item_func.cc b/sql/item_func.cc
index ac53d82d8951..d379a9d8eeda 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -1315,11 +1315,11 @@ Item *Item_func::gc_subst_transformer(uchar *arg) {
       Item_result type = args[0]->result_type();
       /*
         Check whether MEMBER OF is applicable for optimization:
-        1) 1st arg is a constant
+        1) 1st arg is constant for execution
         2) .. and it isn't NULL, as MEMBER OF can't be used to lookup NULLs
         3) 2nd arg can be substituted for a GC
       */
-      if (args[0]->const_item() &&                               // 1
+      if (args[0]->const_for_execution() &&                      // 1
           !args[0]->is_null() &&                                 // 2
           args[1]->can_be_substituted_for_gc(/*array=*/true)) {  // 3
         if (substitute_gc_expression(args + 1, args, gc_fields, type, this))
@@ -1333,12 +1333,12 @@ Item *Item_func::gc_subst_transformer(uchar *arg) {
       /*
         Check whether JSON_CONTAINS is applicable for optimization:
         1) 1st arg can be substituted with a generated column
-        2) value to lookup is a constant
+        2) value to lookup is constant for execution
         3) value to lookup is a proper JSON doc
         4) value to lookup is an array or scalar
       */
       if (!args[0]->can_be_substituted_for_gc(/*array=*/true) ||  // 1
-          !args[1]->real_item()->const_item())                    // 2
+          !args[1]->real_item()->const_for_execution())           // 2
         break;
       if (get_json_wrapper(args, 1, &str, func_name(), &vals_wr) ||  // 3
           args[1]->null_value ||
@@ -1353,17 +1353,17 @@ Item *Item_func::gc_subst_transformer(uchar *arg) {
 
       /*
         Check whether JSON_OVERLAPS is applicable for optimization:
-        1) One argument is a constant
+        1) One argument is constant for execution
         2) The other argument can be substituted with a generated column
         3) value to lookup is a proper JSON doc
         4) value to lookup is an array or scalar
       */
       if (args[0]->can_be_substituted_for_gc(/*array=*/true) &&  // 2
-          args[1]->const_item()) {                               // 1
+          args[1]->const_for_execution()) {                      // 1
         func = args;
         vals = 1;
       } else if (args[1]->can_be_substituted_for_gc(/*array=*/true) &&  // 2
-                 args[0]->const_item()) {                               // 1
+                 args[0]->const_for_execution()) {                      // 1
         func = args + 1;
         vals = 0;
       } else {

From 5905ffd340a16443915b62f61d59e2e835930315 Mon Sep 17 00:00:00 2001
From: Yubao Liu <yubao.liu@gmail.com>
Date: Sat, 21 Aug 2021 14:01:46 +0800
Subject: [PATCH 3/4] Bug#104700: enable multi-valued index for "member of"
 function in "OR" expression

"json_contains" and "json_overlaps" already works but "member of" didn't work.
---
 mysql-test/r/hash_join.result                 |   6 +-
 mysql-test/suite/json/r/array_index.result    |  26 +--
 .../json/r/multi-valued-index-in-or.result    | 164 ++++++++++++++++++
 .../json/t/multi-valued-index-in-or.test      |  79 +++++++++
 .../suite/rpl/r/rpl_multi_valued_index.result |   2 +-
 sql/opt_range.cc                              |  31 ++++
 6 files changed, 291 insertions(+), 17 deletions(-)
 create mode 100644 mysql-test/suite/json/r/multi-valued-index-in-or.result
 create mode 100644 mysql-test/suite/json/t/multi-valued-index-in-or.test

diff --git a/mysql-test/r/hash_join.result b/mysql-test/r/hash_join.result
index 5ae102234e46..3aea96a6f84a 100644
--- a/mysql-test/r/hash_join.result
+++ b/mysql-test/r/hash_join.result
@@ -812,11 +812,11 @@ test.t2	analyze	status	OK
 EXPLAIN FORMAT=tree SELECT t1.col_int_key AS field1, t2.col_int AS field2 FROM t2
 JOIN t1 ON 1 WHERE (CAST("1" AS JSON) MEMBER OF( t1.col_json->'$[*]'));
 EXPLAIN
--> Inner hash join (no condition)  (cost=*** rows=5)
+-> Inner hash join (no condition)  (cost=*** rows=10)
     -> Table scan on t2  (cost=*** rows=5)
     -> Hash
-        -> Filter: json'"1"' member of (cast(json_extract(col_json,_utf8mb4'$[*]') as char(40) array))  (cost=*** rows=1)
-            -> Index lookup on t1 using mv_idx (cast(json_extract(col_json,_utf8mb4'$[*]') as char(40) array)=json'"1"')  (cost=*** rows=1)
+        -> Filter: json'"1"' member of (cast(json_extract(col_json,_utf8mb4'$[*]') as char(40) array))  (cost=*** rows=2)
+            -> Index lookup on t1 using mv_idx (cast(json_extract(col_json,_utf8mb4'$[*]') as char(40) array)=json'"1"')  (cost=*** rows=2)
 
 SELECT t1.col_int_key AS field1, t2.col_int AS field2 FROM t2
 JOIN t1 ON 1 WHERE (CAST("1" AS JSON) MEMBER OF( t1.col_json->'$[*]'));
diff --git a/mysql-test/suite/json/r/array_index.result b/mysql-test/suite/json/r/array_index.result
index 393f73bc5115..1f980a9cd84c 100644
--- a/mysql-test/suite/json/r/array_index.result
+++ b/mysql-test/suite/json/r/array_index.result
@@ -488,7 +488,7 @@ id	f1
 18	[8, 4, 3, 5]
 explain select * from t1 where 5 member of (f1->"$[*]");
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	NULL	ref	i1	i1	9	const	1	100.00	Using where
+1	SIMPLE	t1	NULL	ref	i1	i1	9	const	5	100.00	Using where
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` where json'5' member of (cast(json_extract(`f1`,_utf8mb4'$[*]') as unsigned array))
 select * from t1 where f1->"$[0]" member of ('[1,3,9]');
@@ -679,7 +679,7 @@ id	f1
 8	[8, 5]
 explain select * from t1 where 5 member of (f1->"$");
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	NULL	ref	i1	i1	9	const	1	100.00	Using where
+1	SIMPLE	t1	NULL	ref	i1	i1	9	const	2	100.00	Using where
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` where json'5' member of (cast(json_extract(`f1`,_utf8mb4'$') as signed array))
 select * from t1 force index(i1) where 99 member of (f1->"$[*]");
@@ -727,7 +727,7 @@ f1
 ["gfd", "qwe"]
 explain select * from t1 where "qwe" member of (f1->"$[*]");
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	NULL	ref	i1	i1	43	const	1	100.00	Using where
+1	SIMPLE	t1	NULL	ref	i1	i1	43	const	2	100.00	Using where
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where json'"qwe"' member of (cast(json_extract(`f1`,_utf8mb4'$[*]') as char(10) array))
 select * from t1 force index(i1) where "bnm" member of  (f1->"$[*]");
@@ -775,7 +775,7 @@ f1
 ["gfd", "qwe"]
 explain select * from t1 where "qwe" member of (f1->"$[*]");
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	NULL	ref	i1	i1	13	const	1	100.00	Using where
+1	SIMPLE	t1	NULL	ref	i1	i1	13	const	2	100.00	Using where
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where json'"base64:type15:cXdl"' member of (cast(json_extract(`f1`,_utf8mb4'$[*]') as binary(10) array))
 select * from t1 force index(i1) where "bnm" member of (f1->"$[*]");
@@ -840,7 +840,7 @@ id	f1
 8	[-1.330, 5, 1.3300]
 explain select * from t1 where 1.33 member of (f1->"$");
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	NULL	ref	i1	i1	6	const	1	100.00	Using where
+1	SIMPLE	t1	NULL	ref	i1	i1	6	const	2	100.00	Using where
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` where json'1.3300' member of (cast(json_extract(`f1`,_utf8mb4'$') as decimal(10, 4) array))
 select * from t1 force index(i1) where 99 member of (f1->"$[*]");
@@ -908,7 +908,7 @@ id	f1
 7	["01-02-03", "22.11.17"]
 explain select * from t1 where cast('01-02-03' as date) member of (f1->"$");
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	NULL	ref	i1	i1	4	const	1	100.00	Using where
+1	SIMPLE	t1	NULL	ref	i1	i1	4	const	4	100.00	Using where
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` where json'"2001-02-03"' member of (cast(json_extract(`f1`,_utf8mb4'$') as date array))
 select * from t1 force index(i1) where cast('01-01-12' as date) member of (f1->"$");
@@ -981,7 +981,7 @@ id	f1
 7	["01:02:03", "22:11:17"]
 explain select * from t1 where cast('01:02:03' as time) member of (f1->"$");
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	NULL	ref	i1	i1	4	const	1	100.00	Using where
+1	SIMPLE	t1	NULL	ref	i1	i1	4	const	4	100.00	Using where
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` where json'"01:02:03.000000"' member of (cast(json_extract(`f1`,_utf8mb4'$') as time array))
 select * from t1 force index(i1) where
@@ -1056,7 +1056,7 @@ id	f1
 explain select * from t1 where cast('01-01-01 01:02:03' as datetime) member
 of (f1->"$");
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	NULL	ref	i1	i1	6	const	1	100.00	Using where
+1	SIMPLE	t1	NULL	ref	i1	i1	6	const	4	100.00	Using where
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` where json'"2001-01-01 01:02:03.000000"' member of (cast(json_extract(`f1`,_utf8mb4'$') as datetime array))
 select * from t1 force index(i1) where
@@ -1955,7 +1955,7 @@ test.t2	analyze	status	OK
 EXPLAIN SELECT t1.col_int_key AS field1, t2.col_int AS field2 FROM t2 LEFT
 JOIN t1 ON 1 WHERE (CAST("1" AS JSON) MEMBER OF( t1.col_jsonn->'$[*]'));
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	NULL	ref	mv_idx	mv_idx	163	const	1	100.00	Using where
+1	SIMPLE	t1	NULL	ref	mv_idx	mv_idx	163	const	2	100.00	Using where
 1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	Using join buffer (hash join)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`col_int_key` AS `field1`,`test`.`t2`.`col_int` AS `field2` from `test`.`t2` join `test`.`t1` where json'"1"' member of (cast(json_extract(`col_jsonn`,_utf8mb4'$[*]') as char(40) array))
@@ -2416,8 +2416,8 @@ vc	j
 [2,3,4]	[2, 3, 4]
 EXPLAIN FORMAT=TREE SELECT * FROM t WHERE 3 MEMBER OF (j);
 EXPLAIN
--> Filter: json'3' member of (cast(j as unsigned array))  (cost=0.35 rows=1)
-    -> Index lookup on t using j_idx (cast(j as unsigned array)=json'3')  (cost=0.35 rows=1)
+-> Filter: json'3' member of (cast(j as unsigned array))  (cost=0.80 rows=3)
+    -> Index lookup on t using j_idx (cast(j as unsigned array)=json'3')  (cost=0.80 rows=3)
 
 SELECT * FROM t WHERE 3 MEMBER OF (j);
 vc	j
@@ -2446,8 +2446,8 @@ vc	j
 [2,3,4]	[2, 3, 4]
 EXPLAIN FORMAT=TREE SELECT * FROM t WHERE 3 MEMBER OF (vc);
 EXPLAIN
--> Filter: json'3' member of (cast(vc as unsigned array))  (cost=0.35 rows=1)
-    -> Index lookup on t using vc_idx (cast(vc as unsigned array)=json'3')  (cost=0.35 rows=1)
+-> Filter: json'3' member of (cast(vc as unsigned array))  (cost=0.80 rows=3)
+    -> Index lookup on t using vc_idx (cast(vc as unsigned array)=json'3')  (cost=0.80 rows=3)
 
 SELECT * FROM t WHERE 3 MEMBER OF (vc);
 vc	j
diff --git a/mysql-test/suite/json/r/multi-valued-index-in-or.result b/mysql-test/suite/json/r/multi-valued-index-in-or.result
new file mode 100644
index 000000000000..d1102740ea72
--- /dev/null
+++ b/mysql-test/suite/json/r/multi-valued-index-in-or.result
@@ -0,0 +1,164 @@
+DROP TABLE IF EXISTS t1;
+DROP VIEW IF EXISTS v1;
+CREATE TABLE t1 (
+f1 VARCHAR(50) NOT NULL PRIMARY KEY,
+f2 JSON NOT NULL,
+INDEX idx2 ( (CAST(f2 AS CHAR(50) ARRAY)) )
+);
+CREATE VIEW v1 AS
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids;
+INSERT INTO t1 VALUES ('foo', '["aa", "bb"]');
+INSERT INTO t1 VALUES ('bar', '["xx", "yy"]');
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+SELECT * FROM t1;
+f1	f2
+bar	["xx", "yy"]
+foo	["aa", "bb"]
+EXPLAIN SELECT * FROM t1 WHERE 'xx' member of (f2) OR 'zz' member of(f2);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	range	idx2	idx2	203	NULL	2	100.00	Using where
+EXPLAIN SELECT * FROM t1 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"');
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	range	idx2	idx2	203	NULL	2	100.00	Using where
+EXPLAIN SELECT * FROM t1 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]');
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	range	idx2	idx2	203	NULL	3	100.00	Using where
+EXPLAIN SELECT * FROM v1 WHERE 'xx' member of (f2) OR 'zz' member of(f2);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	range	idx2	idx2	203	NULL	2	100.00	Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+EXPLAIN SELECT * FROM v1 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"');
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	range	idx2	idx2	203	NULL	2	100.00	Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+EXPLAIN SELECT * FROM v1 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]');
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	range	idx2	idx2	203	NULL	3	100.00	Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE 'xx' member of (f2) OR 'zz' member of(f2);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	range	idx2	idx2	203	NULL	2	100.00	Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"');
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	range	idx2	idx2	203	NULL	2	100.00	Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]');
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	range	idx2	idx2	203	NULL	3	100.00	Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+INSERT INTO t1 VALUES ('k1', '["v1"]');
+INSERT INTO t1 VALUES ('k2', '["v2"]');
+INSERT INTO t1 VALUES ('k3', '["v3"]');
+INSERT INTO t1 VALUES ('k4', '["v4"]');
+INSERT INTO t1 VALUES ('k5', '["v5"]');
+INSERT INTO t1 VALUES ('k6', '["v6"]');
+INSERT INTO t1 VALUES ('k7', '["v7"]');
+INSERT INTO t1 VALUES ('k8', '["v8"]');
+INSERT INTO t1 VALUES ('k9', '["v9"]');
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+EXPLAIN SELECT * FROM t1 WHERE 'xx' member of (f2) OR 'zz' member of(f2) OR f1 = 'bar';
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	index_merge	PRIMARY,idx2	idx2,PRIMARY	203,202	NULL	3	100.00	Using sort_union(idx2,PRIMARY); Using where
+EXPLAIN SELECT * FROM t1 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"') OR f1 = 'bar';
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	index_merge	PRIMARY,idx2	idx2,PRIMARY	203,202	NULL	3	100.00	Using sort_union(idx2,PRIMARY); Using where
+EXPLAIN SELECT * FROM t1 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]') OR f1 = 'bar';
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	index_merge	PRIMARY,idx2	idx2,PRIMARY	203,202	NULL	4	100.00	Using sort_union(idx2,PRIMARY); Using where
+EXPLAIN SELECT * FROM v1 WHERE 'xx' member of (f2) OR 'zz' member of(f2) OR f1 = 'bar';
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	index_merge	PRIMARY,idx2	idx2,PRIMARY	203,202	NULL	3	100.00	Using sort_union(idx2,PRIMARY); Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+EXPLAIN SELECT * FROM v1 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"') OR f1 = 'bar';
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	index_merge	PRIMARY,idx2	idx2,PRIMARY	203,202	NULL	3	100.00	Using sort_union(idx2,PRIMARY); Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+EXPLAIN SELECT * FROM v1 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]') OR f1 = 'bar';
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	index_merge	PRIMARY,idx2	idx2,PRIMARY	203,202	NULL	4	100.00	Using sort_union(idx2,PRIMARY); Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE 'xx' member of (f2) OR 'zz' member of(f2) OR f1 = 'bar';
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	index_merge	PRIMARY,idx2	idx2,PRIMARY	203,202	NULL	3	100.00	Using sort_union(idx2,PRIMARY); Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"') OR f1 = 'bar';
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	index_merge	PRIMARY,idx2	idx2,PRIMARY	203,202	NULL	3	100.00	Using sort_union(idx2,PRIMARY); Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]') OR f1 = 'bar';
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	index_merge	PRIMARY,idx2	idx2,PRIMARY	203,202	NULL	4	100.00	Using sort_union(idx2,PRIMARY); Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+SELECT * FROM t1 WHERE 'xx' member of (f2) OR 'zz' member of(f2);
+f1	f2
+bar	["xx", "yy"]
+SELECT * FROM t1 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"');
+f1	f2
+bar	["xx", "yy"]
+SELECT * FROM t1 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]');
+f1	f2
+bar	["xx", "yy"]
+SELECT * FROM v1 WHERE 'xx' member of (f2) OR 'zz' member of(f2);
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+SELECT * FROM v1 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"');
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+SELECT * FROM v1 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]');
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE 'xx' member of (f2) OR 'zz' member of(f2);
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"');
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]');
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+SELECT * FROM t1 WHERE 'xx' member of (f2) OR 'zz' member of(f2) OR f1 = 'bar';
+f1	f2
+bar	["xx", "yy"]
+SELECT * FROM t1 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"') OR f1 = 'bar';
+f1	f2
+bar	["xx", "yy"]
+SELECT * FROM t1 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]') OR f1 = 'bar';
+f1	f2
+bar	["xx", "yy"]
+SELECT * FROM v1 WHERE 'xx' member of (f2) OR 'zz' member of(f2) OR f1 = 'bar';
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+SELECT * FROM v1 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"') OR f1 = 'bar';
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+SELECT * FROM v1 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]') OR f1 = 'bar';
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE 'xx' member of (f2) OR 'zz' member of(f2) OR f1 = 'bar';
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"') OR f1 = 'bar';
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]') OR f1 = 'bar';
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+DROP TABLE t1;
+DROP VIEW v1;
diff --git a/mysql-test/suite/json/t/multi-valued-index-in-or.test b/mysql-test/suite/json/t/multi-valued-index-in-or.test
new file mode 100644
index 000000000000..fbc2430cd443
--- /dev/null
+++ b/mysql-test/suite/json/t/multi-valued-index-in-or.test
@@ -0,0 +1,79 @@
+--disable_warnings
+
+DROP TABLE IF EXISTS t1;
+DROP VIEW IF EXISTS v1;
+
+CREATE TABLE t1 (
+  f1 VARCHAR(50) NOT NULL PRIMARY KEY,
+  f2 JSON NOT NULL,
+  INDEX idx2 ( (CAST(f2 AS CHAR(50) ARRAY)) )
+);
+
+CREATE VIEW v1 AS
+  SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids;
+
+INSERT INTO t1 VALUES ('foo', '["aa", "bb"]');
+INSERT INTO t1 VALUES ('bar', '["xx", "yy"]');
+
+ANALYZE TABLE t1;
+
+SELECT * FROM t1;
+
+EXPLAIN SELECT * FROM t1 WHERE 'xx' member of (f2) OR 'zz' member of(f2);
+EXPLAIN SELECT * FROM t1 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"');
+EXPLAIN SELECT * FROM t1 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]');
+EXPLAIN SELECT * FROM v1 WHERE 'xx' member of (f2) OR 'zz' member of(f2);
+EXPLAIN SELECT * FROM v1 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"');
+EXPLAIN SELECT * FROM v1 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]');
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE 'xx' member of (f2) OR 'zz' member of(f2);
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"');
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]');
+
+# need more rows, or cost based optimizer chooses full table scan
+INSERT INTO t1 VALUES ('k1', '["v1"]');
+INSERT INTO t1 VALUES ('k2', '["v2"]');
+INSERT INTO t1 VALUES ('k3', '["v3"]');
+INSERT INTO t1 VALUES ('k4', '["v4"]');
+INSERT INTO t1 VALUES ('k5', '["v5"]');
+INSERT INTO t1 VALUES ('k6', '["v6"]');
+INSERT INTO t1 VALUES ('k7', '["v7"]');
+INSERT INTO t1 VALUES ('k8', '["v8"]');
+INSERT INTO t1 VALUES ('k9', '["v9"]');
+
+ANALYZE TABLE t1;
+
+EXPLAIN SELECT * FROM t1 WHERE 'xx' member of (f2) OR 'zz' member of(f2) OR f1 = 'bar';
+EXPLAIN SELECT * FROM t1 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"') OR f1 = 'bar';
+EXPLAIN SELECT * FROM t1 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]') OR f1 = 'bar';
+EXPLAIN SELECT * FROM v1 WHERE 'xx' member of (f2) OR 'zz' member of(f2) OR f1 = 'bar';
+EXPLAIN SELECT * FROM v1 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"') OR f1 = 'bar';
+EXPLAIN SELECT * FROM v1 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]') OR f1 = 'bar';
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE 'xx' member of (f2) OR 'zz' member of(f2) OR f1 = 'bar';
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"') OR f1 = 'bar';
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]') OR f1 = 'bar';
+
+
+SELECT * FROM t1 WHERE 'xx' member of (f2) OR 'zz' member of(f2);
+SELECT * FROM t1 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"');
+SELECT * FROM t1 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]');
+SELECT * FROM v1 WHERE 'xx' member of (f2) OR 'zz' member of(f2);
+SELECT * FROM v1 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"');
+SELECT * FROM v1 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]');
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE 'xx' member of (f2) OR 'zz' member of(f2);
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"');
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]');
+
+SELECT * FROM t1 WHERE 'xx' member of (f2) OR 'zz' member of(f2) OR f1 = 'bar';
+SELECT * FROM t1 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"') OR f1 = 'bar';
+SELECT * FROM t1 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]') OR f1 = 'bar';
+SELECT * FROM v1 WHERE 'xx' member of (f2) OR 'zz' member of(f2) OR f1 = 'bar';
+SELECT * FROM v1 WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"') OR f1 = 'bar';
+SELECT * FROM v1 WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]') OR f1 = 'bar';
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE 'xx' member of (f2) OR 'zz' member of(f2) OR f1 = 'bar';
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_contains(f2, '"xx"') OR json_contains(f2, '"zz"') OR f1 = 'bar';
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_overlaps(f2, '["xx", "zz"]') OR json_overlaps(f2, '["zz"]') OR f1 = 'bar';
+
+DROP TABLE t1;
+DROP VIEW v1;
+
+--enable_warnings
diff --git a/mysql-test/suite/rpl/r/rpl_multi_valued_index.result b/mysql-test/suite/rpl/r/rpl_multi_valued_index.result
index 622e9b208c81..884549a3dab6 100644
--- a/mysql-test/suite/rpl/r/rpl_multi_valued_index.result
+++ b/mysql-test/suite/rpl/r/rpl_multi_valued_index.result
@@ -44,7 +44,7 @@ pl	f1
 18	[8, 4, 3, 5]
 explain select * from t1 where 5 member of (f1->"$[*]");
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	NULL	ref	i1	i1	9	const	1	100.00	Using where
+1	SIMPLE	t1	NULL	ref	i1	i1	9	const	5	100.00	Using where
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pl` AS `pl`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` where json'5' member of (cast(json_extract(`f1`,_utf8mb4'$[*]') as unsigned array))
 select * from t1 force index(i1) where json_contains(f1->"$[*]", "[4,3]");
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 230252a9c49d..6c35582a8958 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -5798,6 +5798,36 @@ static SEL_TREE *get_func_mm_tree(RANGE_OPT_PARAM *param, Item *predicand,
       tree = get_func_mm_tree_from_json_overlaps_contains(param, predicand,
                                                           cond_func);
     } break;
+
+    case Item_func::MEMBER_OF_FUNC:
+      if (predicand->type() == Item::FIELD_ITEM && predicand->returns_array()) {
+        Field_typed_array *field = down_cast<Field_typed_array *>(
+            static_cast<Item_field *>(predicand)->field);
+        Item* arg = cond_func->arguments()[0];
+        Json_wrapper wr;
+
+        if (arg->val_json(&wr)) {
+          break;
+        }
+
+        assert(!arg->null_value && wr.type() != enum_json_type::J_ERROR);
+
+        if (wr.type() == enum_json_type::J_NULL) {
+          break;
+        }
+
+        const bool save_const = field->table->const_table;
+        field->table->const_table = true;
+        field->set_notnull();
+        field->coerce_json_value(&wr, true, nullptr);
+
+        tree = get_mm_parts(param, cond_func, field, Item_func::EQ_FUNC,
+                            predicand);
+
+        field->table->const_table = save_const;
+      }
+      break;
+
     default:
       if (predicand->type() == Item::FIELD_ITEM) {
         Field *field = static_cast<Item_field *>(predicand)->field;
@@ -6104,6 +6134,7 @@ SEL_TREE *get_mm_tree(RANGE_OPT_PARAM *param, Item *cond) {
 
     case Item_func::JSON_CONTAINS:
     case Item_func::JSON_OVERLAPS:
+    case Item_func::MEMBER_OF_FUNC:
     case Item_func::IN_FUNC: {
       Item *predicand = cond_func->key_item();
       if (!predicand) return nullptr;

From 8bf7efe64c25e57f448ec8e5aa34e52c14f283d2 Mon Sep 17 00:00:00 2001
From: Yubao Liu <yubao.liu@gmail.com>
Date: Sun, 22 Aug 2021 22:21:17 +0800
Subject: [PATCH 4/4] Bug#104700: fix wrong "impossible condition" when
 multi-valued index occurs in AND clause

For a multi-valued index on json array field f=[1, 2], this statement wrongly
returned empty result set:

  SELECT * FROM f WHERE JSON_CONTAINS(f, 1) AND JSON_CONTAINS(f, 2);

The cause is `get_func_mm_tree()` converts two JSON_CONTAINS() to `f_idx = 1 AND f_idx = 2`
which is impossible for single value index but possible for multi-valued index.
---
 .../json/r/multi-valued-index-in-and.result   | 152 ++++++++++++++++++
 .../json/t/multi-valued-index-in-and.test     |  66 ++++++++
 sql/opt_range.cc                              |   6 +
 3 files changed, 224 insertions(+)
 create mode 100644 mysql-test/suite/json/r/multi-valued-index-in-and.result
 create mode 100644 mysql-test/suite/json/t/multi-valued-index-in-and.test

diff --git a/mysql-test/suite/json/r/multi-valued-index-in-and.result b/mysql-test/suite/json/r/multi-valued-index-in-and.result
new file mode 100644
index 000000000000..e32e05030ee3
--- /dev/null
+++ b/mysql-test/suite/json/r/multi-valued-index-in-and.result
@@ -0,0 +1,152 @@
+DROP TABLE IF EXISTS t1;
+DROP VIEW IF EXISTS v1;
+CREATE TABLE t1 (
+f1 VARCHAR(50) NOT NULL PRIMARY KEY,
+f2 JSON NOT NULL,
+INDEX idx2 ( (CAST(f2 AS CHAR(50) ARRAY)) )
+);
+CREATE VIEW v1 AS
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids;
+INSERT INTO t1 VALUES ('foo', '["aa", "bb"]');
+INSERT INTO t1 VALUES ('bar', '["xx", "yy"]');
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+SELECT * FROM t1;
+f1	f2
+bar	["xx", "yy"]
+foo	["aa", "bb"]
+EXPLAIN SELECT * FROM t1 WHERE 'xx' member of (f2) AND 'yy' member of(f2);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	ref	idx2	idx2	203	const	1	100.00	Using where
+EXPLAIN SELECT * FROM t1 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"');
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	range	idx2	idx2	203	NULL	1	100.00	Using where
+EXPLAIN SELECT * FROM t1 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]');
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	range	idx2	idx2	203	NULL	2	100.00	Using where
+EXPLAIN SELECT * FROM v1 WHERE 'xx' member of (f2) AND 'yy' member of(f2);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	ref	idx2	idx2	203	const	1	100.00	Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+EXPLAIN SELECT * FROM v1 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"');
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	range	idx2	idx2	203	NULL	1	100.00	Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+EXPLAIN SELECT * FROM v1 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]');
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	range	idx2	idx2	203	NULL	2	100.00	Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE 'xx' member of (f2) AND 'yy' member of(f2);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	ref	idx2	idx2	203	const	1	100.00	Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"');
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	range	idx2	idx2	203	NULL	1	100.00	Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]');
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	range	idx2	idx2	203	NULL	2	100.00	Using where
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+EXPLAIN SELECT * FROM t1 WHERE 'xx' member of (f2) AND 'yy' member of(f2) AND f1 = 'bar';
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	const	PRIMARY,idx2	PRIMARY	202	const	1	100.00	NULL
+EXPLAIN SELECT * FROM t1 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"') AND f1 = 'bar';
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	const	PRIMARY,idx2	PRIMARY	202	const	1	100.00	NULL
+EXPLAIN SELECT * FROM t1 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]') AND f1 = 'bar';
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	const	PRIMARY,idx2	PRIMARY	202	const	1	100.00	NULL
+EXPLAIN SELECT * FROM v1 WHERE 'xx' member of (f2) AND 'yy' member of(f2) AND f1 = 'bar';
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	const	PRIMARY,idx2	PRIMARY	202	const	1	100.00	NULL
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+EXPLAIN SELECT * FROM v1 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"') AND f1 = 'bar';
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	const	PRIMARY,idx2	PRIMARY	202	const	1	100.00	NULL
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+EXPLAIN SELECT * FROM v1 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]') AND f1 = 'bar';
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	const	PRIMARY,idx2	PRIMARY	202	const	1	100.00	NULL
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE 'xx' member of (f2) AND 'yy' member of(f2) AND f1 = 'bar';
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	const	PRIMARY,idx2	PRIMARY	202	const	1	100.00	NULL
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"') AND f1 = 'bar';
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	const	PRIMARY,idx2	PRIMARY	202	const	1	100.00	NULL
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]') AND f1 = 'bar';
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	NULL	const	PRIMARY,idx2	PRIMARY	202	const	1	100.00	NULL
+1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: json_table; Using temporary
+SELECT * FROM t1 WHERE 'xx' member of (f2) AND 'yy' member of(f2);
+f1	f2
+bar	["xx", "yy"]
+SELECT * FROM t1 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"');
+f1	f2
+bar	["xx", "yy"]
+SELECT * FROM t1 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]');
+f1	f2
+bar	["xx", "yy"]
+SELECT * FROM v1 WHERE 'xx' member of (f2) AND 'yy' member of(f2);
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+SELECT * FROM v1 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"');
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+SELECT * FROM v1 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]');
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE 'xx' member of (f2) AND 'yy' member of(f2);
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"');
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]');
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+SELECT * FROM t1 WHERE 'xx' member of (f2) AND 'yy' member of(f2) AND f1 = 'bar';
+f1	f2
+bar	["xx", "yy"]
+SELECT * FROM t1 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"') AND f1 = 'bar';
+f1	f2
+bar	["xx", "yy"]
+SELECT * FROM t1 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]') AND f1 = 'bar';
+f1	f2
+bar	["xx", "yy"]
+SELECT * FROM v1 WHERE 'xx' member of (f2) AND 'yy' member of(f2) AND f1 = 'bar';
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+SELECT * FROM v1 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"') AND f1 = 'bar';
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+SELECT * FROM v1 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]') AND f1 = 'bar';
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE 'xx' member of (f2) AND 'yy' member of(f2) AND f1 = 'bar';
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"') AND f1 = 'bar';
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]') AND f1 = 'bar';
+f1	f2	i	id
+bar	["xx", "yy"]	1	xx
+bar	["xx", "yy"]	2	yy
+DROP TABLE t1;
+DROP VIEW v1;
diff --git a/mysql-test/suite/json/t/multi-valued-index-in-and.test b/mysql-test/suite/json/t/multi-valued-index-in-and.test
new file mode 100644
index 000000000000..a9d7ad379c24
--- /dev/null
+++ b/mysql-test/suite/json/t/multi-valued-index-in-and.test
@@ -0,0 +1,66 @@
+--disable_warnings
+
+DROP TABLE IF EXISTS t1;
+DROP VIEW IF EXISTS v1;
+
+CREATE TABLE t1 (
+  f1 VARCHAR(50) NOT NULL PRIMARY KEY,
+  f2 JSON NOT NULL,
+  INDEX idx2 ( (CAST(f2 AS CHAR(50) ARRAY)) )
+);
+
+CREATE VIEW v1 AS
+  SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids;
+
+INSERT INTO t1 VALUES ('foo', '["aa", "bb"]');
+INSERT INTO t1 VALUES ('bar', '["xx", "yy"]');
+
+ANALYZE TABLE t1;
+
+SELECT * FROM t1;
+
+EXPLAIN SELECT * FROM t1 WHERE 'xx' member of (f2) AND 'yy' member of(f2);
+EXPLAIN SELECT * FROM t1 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"');
+EXPLAIN SELECT * FROM t1 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]');
+EXPLAIN SELECT * FROM v1 WHERE 'xx' member of (f2) AND 'yy' member of(f2);
+EXPLAIN SELECT * FROM v1 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"');
+EXPLAIN SELECT * FROM v1 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]');
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE 'xx' member of (f2) AND 'yy' member of(f2);
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"');
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]');
+
+EXPLAIN SELECT * FROM t1 WHERE 'xx' member of (f2) AND 'yy' member of(f2) AND f1 = 'bar';
+EXPLAIN SELECT * FROM t1 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"') AND f1 = 'bar';
+EXPLAIN SELECT * FROM t1 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]') AND f1 = 'bar';
+EXPLAIN SELECT * FROM v1 WHERE 'xx' member of (f2) AND 'yy' member of(f2) AND f1 = 'bar';
+EXPLAIN SELECT * FROM v1 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"') AND f1 = 'bar';
+EXPLAIN SELECT * FROM v1 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]') AND f1 = 'bar';
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE 'xx' member of (f2) AND 'yy' member of(f2) AND f1 = 'bar';
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"') AND f1 = 'bar';
+EXPLAIN SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]') AND f1 = 'bar';
+
+
+SELECT * FROM t1 WHERE 'xx' member of (f2) AND 'yy' member of(f2);
+SELECT * FROM t1 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"');
+SELECT * FROM t1 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]');
+SELECT * FROM v1 WHERE 'xx' member of (f2) AND 'yy' member of(f2);
+SELECT * FROM v1 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"');
+SELECT * FROM v1 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]');
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE 'xx' member of (f2) AND 'yy' member of(f2);
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"');
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]');
+
+SELECT * FROM t1 WHERE 'xx' member of (f2) AND 'yy' member of(f2) AND f1 = 'bar';
+SELECT * FROM t1 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"') AND f1 = 'bar';
+SELECT * FROM t1 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]') AND f1 = 'bar';
+SELECT * FROM v1 WHERE 'xx' member of (f2) AND 'yy' member of(f2) AND f1 = 'bar';
+SELECT * FROM v1 WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"') AND f1 = 'bar';
+SELECT * FROM v1 WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]') AND f1 = 'bar';
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE 'xx' member of (f2) AND 'yy' member of(f2) AND f1 = 'bar';
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_contains(f2, '"xx"') AND json_contains(f2, '"yy"') AND f1 = 'bar';
+SELECT * FROM t1, JSON_TABLE(f2, '$[*]' COLUMNS(i FOR ORDINALITY, id VARCHAR(50) PATH '$')) AS ids WHERE json_overlaps(f2, '["xx", "zz"]') AND json_overlaps(f2, '["yy", "zz"]') AND f1 = 'bar';
+
+DROP TABLE t1;
+DROP VIEW v1;
+
+--enable_warnings
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 6c35582a8958..8dcf40c57296 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -7444,6 +7444,12 @@ SEL_ROOT *key_and(RANGE_OPT_PARAM *param, SEL_ROOT *key1, SEL_ROOT *key2) {
     return key1;
   }
 
+  // Two non-overlapped key ranges for multi-valued index don't mean impossible condition.
+  // For example, "1 member of(f) AND 2 member of(f)" for f=[1, 2].
+  if (key1->root->field->is_array() || key2->root->field->is_array()) {
+    return and_all_keys(param, key1, key2);
+  }
+
   SEL_ARG *e1 = key1->root->first(), *e2 = key2->root->first();
   SEL_ROOT *new_tree = nullptr;