MySQL Bugs: #103556: CAST does not accept VARCHAR in JSON

Bug #103556	CAST does not accept VARCHAR in JSON_VALUE index example
Submitted:	3 May 2021 7:20	Modified:	5 May 2021 18:02
Reporter:	Jens Hatlak	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-21.html#mysqld-8-0-21-json

This does not work:

CREATE TABLE inventory(
    items JSON,
    INDEX i1 ( (JSON_VALUE(items, '$.name' RETURNING VARCHAR(50))) ),
    INDEX i2 ( (JSON_VALUE(items, '$.price' RETURNING DECIMAL(5,2))) ),
    INDEX i3 ( (JSON_VALUE(items, '$.quantity' RETURNING UNSIGNED)) )
);

in particular this part:

INDEX i1 ( (JSON_VALUE(items, '$.name' RETURNING VARCHAR(50))) ),

because CAST does not accept VARCHAR, only CHAR.

How to repeat:
See description

Hello Jens Hatlak,

Thank you for the report and feedback.

regards,
Umesh

- 8.0.24

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.24 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.02 sec)

mysql> use test
Database changed
mysql> CREATE TABLE inventory(
    ->     items JSON,
    ->     INDEX i1 ( (JSON_VALUE(items, '$.name' RETURNING VARCHAR(50))) ),
    ->     INDEX i2 ( (JSON_VALUE(items, '$.price' RETURNING DECIMAL(5,2))) ),
    ->     INDEX i3 ( (JSON_VALUE(items, '$.quantity' RETURNING UNSIGNED)) )
    -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VARCHAR(50))) ),
    INDEX i2 ( (JSON_VALUE(items, '$.price' RETURNING DECIMAL(5' at line 3
mysql>
mysql> CREATE TABLE inventory(     items JSON,     INDEX i1 ( (JSON_VALUE(items, '$.name' RETURNING CHAR(50))) ),     INDEX i2 ( (JSON_VALUE(items, '$.price' RETURNING DECIMAL(5,2))) ),     INDEX i3 ( (JSON_VALUE(items, '$.quantity' RETURNING UNSIGNED)) ) );
Query OK, 0 rows affected (0.10 sec)

mysql> show create table inventory\G
*************************** 1. row ***************************
       Table: inventory
Create Table: CREATE TABLE `inventory` (
  `items` json DEFAULT NULL,
  KEY `i1` ((json_value(`items`, _utf8mb4'$.name' returning char(50)))),
  KEY `i2` ((json_value(`items`, _utf8mb4'$.price' returning decimal(5, 2)))),
  KEY `i3` ((json_value(`items`, _utf8mb4'$.quantity' returning unsigned)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Fixed in mysql-relnotes rev 22495.

Closed.