Bug #105173 JSON_ARRAY() returns NULL when used with CASE and CAST in one statement
Submitted: 8 Oct 11:15 Modified: 5 Nov 11:20
Reporter: Stanislav S. Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: JSON Severity:S3 (Non-critical)
Version:5.7 OS:Any (buster)
Assigned to: CPU Architecture:Any (x86_64)

[8 Oct 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 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 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 13:40] MySQL Verification Team
Thank you , Erlend, for informing us that this patch can not be backported to 5.7.