| Bug #112053 | COALESCE a null value with a JSON object returns hex string | ||
|---|---|---|---|
| Submitted: | 12 Aug 2023 19:55 | Modified: | 16 Aug 2023 12:04 |
| Reporter: | Douglas Sims | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: JSON | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[14 Aug 2023 12:55]
MySQL Verification Team
Hi Mr. Sims,
Thank you for your bug report.
However, we can not repeat it with properly defined JSON values:
set @choices= cast(' { "name": "Jackma" }' as json);
Query OK, 0 rows affected (0.00 sec)
mysql> select coalesce (NULL, @choices);
+---------------------------+
| coalesce (NULL, @choices) |
+---------------------------+
| {"name": "Jackma"} |
+---------------------------+
Hence, this is not a bug.
Can't repeat.
[14 Aug 2023 13:13]
MySQL Verification Team
Hi,
One more additional information.
According to the standard, '{}' can not be considered, since it's an object without any attributes.
Not a bug.
[16 Aug 2023 12:04]
MySQL Verification Team
Hi Mr. Sims, It turns out that this is, actually, a low priority bug. I think the problem is related to incorrect assignment of collation for user variables that are assigned a NULL value. We can workaround the problem by giving the NULL user variable a proper collation: select coalesce(CAST(@choicesnull AS CHAR), @choices); This report is now a verified bug.
[21 Aug 2023 3:25]
huahua xu
Hi Douglas Sims,
It could help you avoid your problem:
mysql> set @choicesnull = cast(null as json);
mysql> set @choices := cast( '{}' as json);
mysql> select coalesce(@choicesnull, @choices);
+----------------------------------+
| coalesce(@choicesnull, @choices) |
+----------------------------------+
| {} |
+----------------------------------+
[21 Aug 2023 12:36]
MySQL Verification Team
Thank you for the workaround .....
Description: If COALESCE is given two json objects, it returns the first, but if it is given a null value and a json object, it returns a hex value representing the JSON (which can be cast to JSON) instead of a JSON value How to repeat: mysql> set @choices := cast( '{}' as json); mysql> select @choices; +----------+ | @choices | +----------+ | {} | +----------+ 1 row in set (0.00 sec) mysql> set @choicesnull = null; mysql> select @choicesnull; +----------------------------+ | @choicesnull | +----------------------------+ | NULL | +----------------------------+ 1 row in set (0.00 sec) mysql> select coalesce(@choicesnull, @choices); +--------------------------------------------------------------------+ | coalesce(@choicesnull, @choices) | +--------------------------------------------------------------------+ | 0x7B7D | +--------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> set @choices := cast( '{"bacon":"lettuce"}' as json); mysql> select @choices; +----------------------+ | @choices | +----------------------+ | {"bacon": "lettuce"} | +----------------------+ 1 row in set (0.00 sec) mysql> select coalesce(@choicesnull, @choices); +--------------------------------------------------------------------+ | coalesce(@choicesnull, @choices) | +--------------------------------------------------------------------+ | 0x7B226261636F6E223A20226C657474756365227D | +--------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select cast(coalesce(@choicesnull, @choices) as json); +------------------------------------------------+ | cast(coalesce(@choicesnull, @choices) as json) | +------------------------------------------------+ | {"bacon": "lettuce"} | +------------------------------------------------+ 1 row in set (0.00 sec)