Bug #105173 JSON_ARRAY() returns NULL when used with CASE and CAST in one statement

• Submitted: 8 Oct 2021 11:15
• Modified: 5 Nov 2021 11:20
• Reporter: Stanislav S.
• Status: Closed
• Category: MySQL Server: JSON
• Severity: S3 (Non-critical)
• Version: 5.7
• OS: Any (buster)
• CPU Architecture: Any (x86_64)

[8 Oct 2021 11:15] Stanislav S.

Description:
JSON_ARRAY() returns NULL which is wrong. This issue apply only to 5.7, MySQL 8 works fine.
Tested on the latest available mysql:5.7 official docker image.

How to repeat:
mysql> SET @a1=NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a1;
+------+
| @a1  |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> SELECT JSON_ARRAY(CASE WHEN @a1 IS NULL THEN NULL WHEN @a1 THEN CAST(true AS JSON) ELSE CAST(false AS JSON) END);
+-----------------------------------------------------------------------------------------------------------+
| JSON_ARRAY(CASE WHEN @a1 IS NULL THEN NULL WHEN @a1 THEN CAST(true AS JSON) ELSE CAST(false AS JSON) END) |
+-----------------------------------------------------------------------------------------------------------+
| NULL                                                                                                      |
+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Here the correct result should be [null], below is a workaround using two IFs:

mysql> SELECT JSON_ARRAY(IF(@a1 IS NULL, NULL, IF(@a1, CAST(true AS JSON), CAST(false AS JSON))));
+-------------------------------------------------------------------------------------+
| JSON_ARRAY(IF(@a1 IS NULL, NULL, IF(@a1, CAST(true AS JSON), CAST(false AS JSON)))) |
+-------------------------------------------------------------------------------------+
| [null]                                                                              |
+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[8 Oct 2021 12:38] MySQL Verification Team

Hi Mr. S.

Thank you for your bug report.

We have managed to repeat the behaviour that you are describing. We are verifying this report, but it is up to our Development to decide whether it will be fixed in 5.7 or not.

Verified as reported.

This bug affects only 5.7 and does not affect 8.0.

[5 Nov 2021 11:20] Erlend Dahl

Fixed in 8.0.1 under the heading of:

Bug#22887227 ASSERTION FAILED: THIS_TYPE != JSON_DOM::J_ERROR

[5 Nov 2021 13:40] MySQL Verification Team

Thank you , Erlend, for informing us that this patch can not be backported to 5.7.