Bug #117674 JSON value is not recognized by Mysql inside Stored Routines

• Submitted: 12 Mar 6:20
• Modified: 17 Mar 9:46
• Reporter: Zafar Malik
• Status: Verified
• Category: MySQL Server: JSON
• Severity: S2 (Serious)
• Version: 8.0.37, 8.0.41, 8.4.4
• OS: Any
• CPU Architecture: Any
• Tags: json type, MySQL, stored routine

[12 Mar 6:20] Zafar Malik

Description:
A query which is using json function/value is working fine as individual query but not working from inside Stored procedure.

For example-

Below query is working fine-
SET @ip_jsonmessage =
'{
	"Message": {
		"Request": {
			"QueryRequest": {
				"UserDefined": {
						"Userdefined1": [
							"1",
							"One"
						],
						"Userdefined2": "2"
					}
			}
		}
	}
}';

-- run directly with session var works
WITH cte AS (
	SELECT j.json_key,
		JSON_EXTRACT(@ip_jsonmessage, CONCAT('$.Message.Request.QueryRequest.UserDefined.', j.json_key)) AS json_value
	FROM JSON_TABLE(
		JSON_KEYS(@ip_jsonmessage, '$.Message.Request.QueryRequest.UserDefined'),
		"$[*]"
		COLUMNS(json_key TEXT PATH "$")
	) j
)
SELECT t.json_key,
	t.json_value
FROM cte t
WHERE JSON_TYPE(t.json_value) != 'ARRAY'
UNION ALL
SELECT t.json_key,
	j.json_value
FROM cte t,
	JSON_TABLE(
			t.json_value,
			"$[*]"
			COLUMNS(json_value JSON PATH "$")
		) j
WHERE JSON_TYPE(t.json_value) = 'ARRAY';
SELECT @ip_jsonmessage, JSON_PRETTY(@ip_jsonmessage);

But when using same query inside stored procedure then throwing error-

-- run EXACT same code in SP (except using input param instead of session var even though session var is passed in as input parameter) FAILS
DELIMITER $$
DROP PROCEDURE IF EXISTS test.sp_s_json;
CREATE DEFINER = 'execution'@'127.0.0.1' PROCEDURE test.sp_s_json
(
IN ip_jsonmessage JSON)
COMMENT 'Demonstrate MySQL bug'
BEGIN

	WITH cte AS (
		SELECT j.json_key,
			JSON_EXTRACT(ip_jsonmessage, CONCAT('$.Message.Request.QueryRequest.UserDefined.', j.json_key)) AS json_value
		FROM JSON_TABLE(
			JSON_KEYS(ip_jsonmessage, '$.Message.Request.QueryRequest.UserDefined'),
			"$[*]"
			COLUMNS(json_key TEXT PATH "$")
		) j
	)
	SELECT t.json_key,
		t.json_value
	FROM cte t
	WHERE JSON_TYPE(t.json_value) != 'ARRAY'
	UNION ALL
	SELECT t.json_key,
		j.json_value
	FROM cte t,
		JSON_TABLE(
				t.json_value,
				"$[*]"
				COLUMNS(json_value JSON PATH "$")
			) j
	WHERE JSON_TYPE(t.json_value) = 'ARRAY';

	SELECT ip_jsonmessage, JSON_PRETTY(ip_jsonmessage);

END$$
DELIMITER ;

CALL test.sp_s_json(@ip_jsonmessage); -- fails: SQL Error (1210): Incorrect arguments to JSON_TABLE

How to repeat:
-- Create a stored routine
-- run EXACT same code in SP (except using input param instead of session var even though session var is passed in as input parameter) FAILS
DELIMITER $$
DROP PROCEDURE IF EXISTS test.sp_s_json;
CREATE DEFINER = 'execution'@'127.0.0.1' PROCEDURE test.sp_s_json
(
IN ip_jsonmessage JSON)
COMMENT 'Demonstrate MySQL bug'
BEGIN

	WITH cte AS (
		SELECT j.json_key,
			JSON_EXTRACT(ip_jsonmessage, CONCAT('$.Message.Request.QueryRequest.UserDefined.', j.json_key)) AS json_value
		FROM JSON_TABLE(
			JSON_KEYS(ip_jsonmessage, '$.Message.Request.QueryRequest.UserDefined'),
			"$[*]"
			COLUMNS(json_key TEXT PATH "$")
		) j
	)
	SELECT t.json_key,
		t.json_value
	FROM cte t
	WHERE JSON_TYPE(t.json_value) != 'ARRAY'
	UNION ALL
	SELECT t.json_key,
		j.json_value
	FROM cte t,
		JSON_TABLE(
				t.json_value,
				"$[*]"
				COLUMNS(json_value JSON PATH "$")
			) j
	WHERE JSON_TYPE(t.json_value) = 'ARRAY';

	SELECT ip_jsonmessage, JSON_PRETTY(ip_jsonmessage);

END$$
DELIMITER ;

-- call it now to execute code
CALL test.sp_s_json(@ip_jsonmessage); 

It will fails with SQL Error (1210): Incorrect arguments to JSON_TABLE

Suggested fix:
-- CAST the field used as the source for the table to force MySQL to recognize that the json_value is JSON, then it will work

DELIMITER $$
DROP PROCEDURE IF EXISTS test.sp_s_json;
CREATE DEFINER = 'execution'@'127.0.0.1' PROCEDURE test.sp_s_json
(
IN ip_jsonmessage JSON)
COMMENT 'Demonstrate MySQL bug'
BEGIN

	WITH cte AS (
		SELECT j.json_key,
			JSON_EXTRACT(ip_jsonmessage, CONCAT('$.Message.Request.QueryRequest.UserDefined.', j.json_key)) AS json_value
		FROM JSON_TABLE(
			JSON_KEYS(ip_jsonmessage, '$.Message.Request.QueryRequest.UserDefined'),
			"$[*]"
			COLUMNS(json_key TEXT PATH "$")
		) j
	)
	SELECT t.json_key,
		t.json_value
	FROM cte t
	WHERE JSON_TYPE(t.json_value) != 'ARRAY'
	UNION ALL
	SELECT t.json_key,
		j.json_value
	FROM cte t,
		JSON_TABLE(
				CAST(t.json_value AS JSON), -- force MySQL to recognize that the json_value is JSON
				"$[*]"
				COLUMNS(json_value JSON PATH "$")
			) j
	WHERE JSON_TYPE(t.json_value) = 'ARRAY';

	SELECT ip_jsonmessage, JSON_PRETTY(ip_jsonmessage);

END$$
DELIMITER ;

CALL test.sp_s_json(@ip_jsonmessage); -- works

[12 Mar 8:31] MySQL Verification Team

Hello Zafar Malik,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

[17 Mar 9:46] Zafar Malik

Hi Umesh,

Thanks for your update. Any chance if you can share next course of action with us.

Thanks,
Zafar