Bug #108090 JSON value comparisons not working as in 8.0.25

• Submitted: 8 Aug 2022 12:09
• Modified: 8 Aug 2022 13:09
• Reporter: Vivek V
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.21
• OS: Any (7.6.1810)
• CPU Architecture: Any (64 bit)

[8 Aug 2022 12:09] Vivek V

Description:
In 8.0.21:

select JSON_VALUE('{"amount": 0.00000666, "currency": "GBP"}', '$.amount') != 0 as IsZero;
+--------+
| IsZero |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

In comparison on 8.0.25:
select JSON_VALUE('{"amount": 0.00000666, "currency": "GBP"}', '$.amount') != 0 as IsZero;
+--------+
| IsZero |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

How to repeat:
Run the following command in 8.0.21:
select JSON_VALUE('{"amount": 0.00000666, "currency": "GBP"}', '$.amount') != 0 as IsZero;

Suggested fix:
select JSON_VALUE('{"amount": 0.00000666, "currency": "GBP"}', '$.amount' returning DECIMAL(24,12)) != 0 as IsZero;

[8 Aug 2022 13:09] MySQL Verification Team

Hi Mr. V,

Thank you for your bug report.

However, it is not a bug.

Indeed, that JSON_VALUE does differ from 0. Since it differs from zero, the boolean expression is true, so it is true (value of 1) that is returned by that statement.

Also, with 8.0.30, we get the result of 1 (true) with both queries that you have provided.

Not a bug.