Bug #108563 Cannot create json index in procedure
Submitted: 21 Sep 9:36 Modified: 21 Sep 9:40
Reporter: Shaohua Wang (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:8.0.28, 8.0.30 OS:Any
Assigned to: CPU Architecture:Any

[21 Sep 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 9:40] MySQL Verification Team
Hello Shaohua,

Thank you for the report and feedback.

regards,
Umesh