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

[2 Dec 2016 6:51] Alfredo Kojima
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!?
[2 Dec 2016 7:12] Umesh Shastry
Hello Alfredo,

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

Thanks,
Umesh
[2 Dec 2016 11:07] Knut Anders Hatlen
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.