MySQL Bugs: #104700: multi-valued index is buggy in view, prepared statement, OR and AND clauses

Bug #104700	multi-valued index is buggy in view, prepared statement, OR and AND clauses
Submitted:	23 Aug 2021 15:06	Modified:	3 Sep 2021 22:39
Reporter:	Yubao Liu (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: JSON	Severity:	S1 (Critical)
Version:	8.0.26	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
https://github.com/mysql/mysql-server/pull/360

The bug description,  test case and fix are all in this pull request, in summary:

1. multi-valued index isn't picked in view
2. multi-valued index isn't picked in prepared statement
3. multi-valued index isn't picked in member of(col) OR member of(col)
4. MySQL wrongly gets "impossible condition" thus empty result set for WHERE func(...) AND func(...) where func is member of, json_contains or json_overlaps.

How to repeat:
These new test cases in the pull request fail under mysql 8.0.26:

1. json.multi-valued-in-view
2. json.multi-valued-in-prepare
3. json.multi-valued-in-or
4. json.multi-valued-in-and

Suggested fix:
Please review the pull request and merge it.

This fixes https://bugs.mysql.com/bug.php?id=104325 too.

Hello Yubao Liu,

Thank you for the report and contribution.

regards,
Umesh

- 5.7 - NA since WL#8763 is implemented in 8.0+

Documented fix as follows in the MySQL 8.0.27 changelog:

    Multi-valued indexes were not used in the following cases:
    
        In views
        
        In prepared statements
        
        In a WHERE containing MEMBER OF() combined using OR with
        another predicate
        
    In addition, MySQL wrongly reported 'impossible condition' for a
    WHERE clause in the form f() AND f(), where f() was any of
    MEMBER OF(), JSON_CONTAINS(), or JSON_OVERLAPS().
    
    Our thanks to Yubao Liu for the contribution.

Closed.