Bug #104898 SELECT COUNT(*) using multi-valued index reports wrong number of rows.

• Submitted: 10 Sep 2021 6:45
• Modified: 2 Feb 2022 13:19
• Reporter: Seunguck Lee
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 8.0.26, 8.0.17
• OS: Any
• CPU Architecture: x86
• Tags: multi-valued index

[10 Sep 2021 6:45] Seunguck Lee

Description:
COUNT(*) query report wrong number of rows when using multi-valued index.

> SELECT COUNT(*) FROM test_mvi;
+----------+
| COUNT(*) |
+----------+
|   100000 |
+----------+

> SELECT COUNT(*) FROM test_mvi FORCE INDEX (PRIMARY) WHERE type='T1';
+----------+
| COUNT(*) |
+----------+
|    97998 |
+----------+

> SELECT COUNT(*) FROM test_mvi FORCE INDEX (ix_type_hid_pat) WHERE type='T1';
+----------+
| COUNT(*) |
+----------+
|   391989 |
+----------+

How to repeat:
You can reproduce this bug using the attached file of following bug report.

https://bugs.mysql.com/bug.php?id=104897

[10 Sep 2021 10:00] MySQL Verification Team

Hello Seunguck Lee,

Thank you for the report and test case.

regards,
Umesh

[2 Feb 2022 13:19] Jon Stephens

Documented fix as follows in the MySQL 8.0.29 changelog:

    SELECT COUNT(*) using a multi-valued index reported the wrong
    number of rows.

Closed.

[2 Feb 2022 13:31] MySQL Verification Team

Thank you, Jon.