Bug #107488 JSON_LENGTH returns 1 from NULL JSON Object

• Submitted: 4 Jun 2022 18:50
• Modified: 4 Jun 2022 18:54
• Reporter: David Allen
• Status: Not a Bug
• Category: MySQL Server: JSON
• Severity: S3 (Non-critical)
• Version: 8.0.29
• OS: Any
• CPU Architecture: Any

[4 Jun 2022 18:50] David Allen

Description:
This is a strange one; I cannot reproduce it except deep in a stored procedure.

The following code is returning 1 as var_attachment_array_size:

SET var_JSON_extracted = NULL;
SET var_attachment_array_size = NULL;
SET var_JSON_extracted = JSON_EXTRACT(var_JSON_converted, '$.attachments');
SET var_attachment_array_size = JSON_LENGTH(var_JSON_extracted);

Note var_JSON_extracted is JSON defined and var_attachment_array_size is INT.

In this example, var_JSON_extracted is NULL.

Unfortunately when I try to reproduce this specifically, I cannot, but it reproduces every single time in the stored procedure around line 850.

How to repeat:
Not sure.

Suggested fix:
This is obviously a bug; returning 1 when the value is NULL makes no sense.

[4 Jun 2022 18:54] David Allen

*sigh* This is the JSON "null" vs. MySQL NULL issue. The IDE was showing null vs (null) for the value because I wasn't using my JSON_EXTRACT_RN (which returns a true null) on it, so it was returning a JSON null value...

MySQL has truly handled the JSON NULL issue abhorrently. It's wasted so much time...