MySQL Bugs: #84037: Error on insert JSON data to a table with generated columns from a SP

Bug #84037	Error on insert JSON data to a table with generated columns from a SP
Submitted:	2 Dec 2016 6:51	Modified:	2 Dec 2016 7:12
Reporter:	Alfredo Kojima	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.7.17, 5.7.16	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Inserting a JSON value to a new table with a generated column via a stored procedure produces an error ERROR: 3102: Expression of generated column 'name' contains a disallowed function.

However,  inserting the same data by hand, without using a SP, succeeds. Moreover, calling the SP again afterwards succeeds.

How to repeat:
DROP TABLE if EXISTS player_js; 
-- create table 
CREATE TABLE `player_js` ( 
`id` int(11) NOT NULL AUTO_INCREMENT, 
`content` json DEFAULT NULL, 
`name` varchar(100) COLLATE utf8mb4_bin GENERATED ALWAYS AS (json_extract(content,'$.nam')) VIRTUAL, 
`level` int(11) GENERATED ALWAYS AS (json_extract(content,'$.lv')) VIRTUAL, 
`career` int(11) GENERATED ALWAYS AS (json_extract(content,'$.car')) VIRTUAL, 
`money` int(11) GENERATED ALWAYS AS (json_extract(content,'$.mny')) VIRTUAL, 
PRIMARY KEY (`id`), 
KEY `idx_level` (`level`), 
KEY `idx_money` (`money`) 
) ENGINE=InnoDB AUTO_INCREMENT=5005 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; 

DROP PROCEDURE IF EXISTS SavePlayer_js; 
delimiter $$ 
-- create procedure 
CREATE PROCEDURE `SavePlayer_js`(IN content JSON) 
BEGIN 
INSERT INTO player_js(player_js.content)VALUES(content); 
END$$ 
delimiter ; 

CALL SavePlayer_js('{"nam":"hehe","lv":1,"car":2,"mny":7}');
-- This will produce: ERROR: 3102: Expression of generated column 'name' contains a disallowed function.

INSERT INTO player_js(content)VALUES('{"nam":"hehe","lv":1,"car":2,"mny":7}'); 
-- This will succeed.

CALL SavePlayer_js('{"nam":"hehe","lv":1,"car":2,"mny":7}');
-- This time it works!?

Hello Alfredo,

Thank you for the bug report and test case.
verified as described.

Thanks,
Umesh

Posted by developer:
 
It seems to work if the stored procedure's parameter is renamed to something different from "content". It looks like the name resolution confuses the procedure's "content" parameter and the table's "content" column somehow.

The problem can also be seen without any JSON columns or JSON functions:

create table t(x int, y int generated always as (x+1));
delimiter $$
create procedure sp(in x int)
begin
insert into t (t.x) values (x);
end$$
delimiter ;
call sp(1);
--> ERROR 3102 (HY000): Expression of generated column 'y' contains a disallowed function.

Just wanted to thank you for this Knut!