Bug #105737 MySQL parser bug, does not allow returning SIGNED in json_value
Submitted: 29 Nov 2021 13:01 Modified: 8 Dec 2021 13:31
Reporter: Justin Levene Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S3 (Non-critical)
Version:8.0.26 OS:Any
Assigned to: CPU Architecture:Any
Tags: json_value, returning, signed

[29 Nov 2021 13:01] Justin Levene
Description:
MySQL workbench won't allow the following, yet it is allowed (https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-value)

set remainder = json_value(_json, "$.path" returning SIGNED);

It highlights "SIGNED" as bad syntax when it is allowed.

How to repeat:
Just type in the following into an SQL editor window. Workbench won't allow it to run due to it thinking there is a SYNTAX ERROR.

set remainder = json_value(_json, "$.path" returning SIGNED);

Suggested fix:
Allow the code.
[29 Nov 2021 13:03] Justin Levene
Even the following isn't allowed.

select json_value(@_json, "$.path" returning SIGNED);
[29 Nov 2021 13:04] Justin Levene
It actually doesn't allow any of the types, FLOAT, DATE, etc.
[8 Dec 2021 13:03] MySQL Verification Team
Hello Justin Levene,

Thank you for the bug report.
Could you please provide exact steps, test case, screenshot etc. to reproduce this issue at our end? Thanks.

Regards,
Ashwini Patil
[8 Dec 2021 13:17] Justin Levene
Just enter the following into a MySQL Workbench SQL tab:

select json_value('{"path":-2,"another":"hello"}', "$.path" returning SIGNED);

The word "SIGNED" is marked as syntax error in MySQL Workbench, which is wrong as it is not.  It allows it to be executed even with the wrong syntax error warning, and it provides the correct result.

However, more serious, due to this bug, you can't use statements like this inside functions and procedures when creating them in Workbench, as Workbench won't even try to execute them with a syntax error.  So try, "Create function" then add:

CREATE FUNCTION `new_function` ()
RETURNS INTEGER
BEGIN
	set @result = (select json_value('{"path":-2,"another":"hello"}', "$.path" returning SIGNED));
RETURN @result;
END

Then click apply, and it won't allow it because it thinks there is a syntax error on line 4 due to the word "SIGNED" which is wrong.
[8 Dec 2021 13:26] Justin Levene
SQL Editor screen shot

Attachment: Screenshot1.png (image/png, text), 23.07 KiB.

[8 Dec 2021 13:26] Justin Levene
Creating function screen shot

Attachment: Screenshot2.png (image/png, text), 34.67 KiB.

[8 Dec 2021 13:31] MySQL Verification Team
Hello Justin Levene,

Thank you for the details.
Imho this is duplicate of Bug #103689, please see Bug #103689.

Regards,
Ashwini Patil