MySQL Bugs: #107225: IFNULL Stringifies JSON Object

Bug #107225	IFNULL Stringifies JSON Object
Submitted:	5 May 2022 17:13	Modified:	11 May 2022 15:08
Reporter:	David Allen	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: JSON	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	IFNULL()

Description:
Passing a function call that returns a JSON object to IFNULL causes the resulting object to be stringified, and the quotes are converted to /" /".

How to repeat:
SELECT IFNULL(json_object_function(),'not null');

Suggested fix:
IFNULL needs to keep the native object format and not convert the JSON to TEXT.

Hi Mr. Allen,

Thank you for your bug report.

However, we were not able to repeat the behaviour that you describe with 8.0.29:

select ifnull(json_object(1,'mysql',2,'heatwave'),5);
+-----------------------------------------------+
| ifnull(json_object(1,'mysql',2,'heatwave'),5) |
+-----------------------------------------------+
| {"1": "mysql", "2": "heatwave"}               |
+-----------------------------------------------+

Can't repeat.

Here you go - this reproduces the issue. Looks like the problem is with JSON_SET.

SET @var1 = JSON_SET(@var1,'$.person_main', IFNULL(JSON_EXTRACT(JSON_OBJECT('firstname','mitchell'),'$.firstname'),'isnull'));
SELECT @var1;

This also reproduces the issue; seems to be a problem with IF in general.

JSON_OBJECT
(
	'name_first', IF
	(
		JSON_UNQUOTE(JSON_EXTRACT(var_source_JSON, '$.firstname')) = '',
		'(undefined)', 
		JSON_EXTRACT(var_source_JSON, '$.firstname')
        )
)

This will always stringify the resulting key.

Hi Mr. Alen,

We have managed to repeat the behaviour with all your test cases.

Thank you very much.

Verified as reported.

I think this statement behaves as it should. In

  SELECT IFNULL(json_object_function(),'not null');

we have two arguments to IFNULL, and the types of the arguments are JSON and character string, which means that the result should be character string.

If both arguments are of type JSON you will get a JSON result. Thus, I think
the following statement will give you the result that you want:

  SELECT IFNULL(json_object_function(), CAST('"not null"' AS JSON);

Notice the single quotes are for the string argument to CAST and the double quotes are for telling that the JSON argument is a string.

Roy has an interesting point relative to the original example, however the second example makes it clear there's a data transposition issue as the code is all within a JSON construct, and still exhibits stringification.

I can't reproduce the case that you claim performs a stringification.
Can you make a more explicit case out of it?

Also note that variables do not support the JSON type, thus if you want to convert the contents of a variable to JSON, you need to wrap it like this:

  CAST(@var AS JSON);

I have the same problem.
Example:
SET @j='{"value": true}';
SELECT IFNULL(JSON_EXTRACT(@j, "$.value"), FALSE) = TRUE;

The result is 0, because 'true' != 1.