Bug #103467 Create multi valued index documentation refers to $.zip instead of $.zipcode

• Submitted: 25 Apr 2021 11:18
• Modified: 12 May 2021 16:10
• Reporter: Nikolai Ikhalainen
• Status: Closed
• Category: MySQL Server: Documentation
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[25 Apr 2021 11:18] Nikolai Ikhalainen

Description:
https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued
examples using the document with zipcode array, but create/alter table statements using "zip" array.

As a result, the index is unused by query:
SELECT id FROM customers WHERE 94568 MEMBER OF(custinfo->'$.zipcode')

How to repeat:
The document is:
{
    "user":"Bob",
    "user_id":31,
    "zipcode":[94477,94536]
}

https://dev.mysql.com/doc/refman/8.0/en/create-index.html#idm45481643897296
suggest to create an index on the field named zip, not zipcode:
ALTER TABLE customers ADD INDEX zips( (CAST(custinfo->'$.zip' AS UNSIGNED ARRAY)) );

Suggested fix:
CREATE TABLE customers (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    custinfo JSON,
-    INDEX zips( (CAST(custinfo->'$.zip' AS UNSIGNED ARRAY)) )
+    INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) )

    );

- ALTER TABLE customers ADD INDEX zips( (CAST(custinfo->'$.zip' AS UNSIGNED ARRAY)) );
+ ALTER TABLE customers ADD INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

- CREATE INDEX zips ON customers ( (CAST(custinfo->'$.zip' AS UNSIGNED ARRAY)) );
+ CREATE INDEX zips ON customers ( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

[25 Apr 2021 13:35] MySQL Verification Team

Hello Nikolai,

Thank you for the report and feedback.

Thanks,
Umesh

[12 May 2021 16:10] Daniel Price

Posted by developer:
 
The referenced examples have been updated. Changes should appear online soon.
Thank you for the bug report.