Bug #119019 Optimize MEMBER OF operator for joining an indexed column against JSON array

• Submitted: 16 Sep 21:26
• Reporter: Chad Berchek
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S4 (Feature request)
• Version: 9.4.0
• OS: Any
• CPU Architecture: Any

[16 Sep 21:26] Chad Berchek

Description:
When the left-hand operand of MEMBER OF is an indexed column, the index is not used. Instead, a full table scan is performed. It would by very useful when joining tables if this scenario were optimized like an IN subquery.

Note: This is the opposite case of Bug #112346. That issue refers to the use of a multi-value index on the right-hand operand. In my issue, I am referring to a normal scalar index on the left-hand operand, where the right-hand is coming from a table earlier in the plan or a literal.

How to repeat:
-- 
create table user_group (
    id int primary key,
    name varchar(100)
) as
with recursive seq as (
    select 1 id
    union all
    select id + 1 from seq where id < 1000
)
select id, concat('Group ', id) name from seq;

explain
select name
from user_group
where id member of ('[7, 209, 781]');
--

Of course, in this minimal example, the MEMBER OF could trivially be converted to an IN operator. However, the same problem occurs when joining tables, where the JSON array comes from the first table in the plan order, and the second table has a scalar indexed column. The second table gets a full table scan whereas it could use the index. For example: 

--
create table user (
    id int primary key,
    group_ids json
);
insert into user () values (1, '[7, 209, 781]');

explain
select
    user.id,
    user_group.name
from
    user
    join user_group on user_group.id member of (user.group_ids)
where user.id = 1;
--

As a workaround, using JSON_TABLE enables the optimizer to use the index, though the syntax is not nearly as convenient:

--
explain
select
    user.id,
    user_group.name
from
    user,
    json_table(
        user.group_ids,
        '$[*]' columns (
            group_id int path '$'
        )
    ) user_to_group
    join user_group on user_group.id = user_to_group.group_id
where user.id = 1;