Bug #108563 Cannot create json index in procedure

• Submitted: 21 Sep 2022 9:36
• Modified: 21 Sep 2022 9:40
• Reporter: Shaohua Wang (OCA)
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 8.0.28, 8.0.30
• OS: Any
• CPU Architecture: Any

[21 Sep 2022 9:36] Shaohua Wang

Description:
Fail to create json index in procedure, but we can create it directly.

ERROR 1221 (HY000): Incorrect usage of CAST( .. AS .. ARRAY) and stored routines

How to repeat:
DELIMITER |;
CREATE PROCEDURE `TestCreateTable`()
BEGIN

  CREATE table job_test(
      id int ,
      `name` json
  );
  ALTER table job_test add index idx_name(( cast(name->>'$.node' as char(64) array) ));
  DROP table job_test;
END|
DELIMITER ;|

[21 Sep 2022 9:40] MySQL Verification Team

Hello Shaohua,

Thank you for the report and feedback.

regards,
Umesh