| Bug #90469 | json_extract and boolean types | ||
|---|---|---|---|
| Submitted: | 17 Apr 2018 10:54 | Modified: | 19 Apr 2018 10:04 |
| Reporter: | Arnaud Adant | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: JSON | Severity: | S3 (Non-critical) |
| Version: | 5.7.21 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[17 Apr 2018 11:00]
Arnaud Adant
A JSON boolean >>true<< should be equal to 1 when extracted from JSON. A JSON boolean >>false<< should be equal to 0 when extracted from JSON.
[17 Apr 2018 11:08]
MySQL Verification Team
Hello Arnaud, Thank you for the report. Thanks, Umesh
[18 Apr 2018 7:30]
Knut Anders Hatlen
Hi Arnaud,
What you are seeing is the intended behaviour. We chose to make comparisons of JSON values treat booleans and numbers as different in order to make comparisons consistent with sorting. When you do ORDER BY json_col, you don't get a result where booleans come interleaved with the numbers such as {-2, -1, false, 0, 1, true, 2, 3}, but instead {-2, -1, 0, 1, 2, 3, false, true}, and we didn't want values that sort differently to compare as equal.
If you want to conflate numbers and booleans when doing comparisons, you would have to cast the JSON operand to a numeric type:
mysql> select cast(json_extract('{"b":true}','$.b') as signed int) = 1; +----------------------------------------------------------+
| cast(json_extract('{"b":true}','$.b') as signed int) = 1 |
+----------------------------------------------------------+
| 1 |
+----------------------------------------------------------+
1 row in set (0,00 sec)
[19 Apr 2018 10:04]
Arnaud Adant
OK, thanks. It makes sense even if it is going to confuse users ... as long as it is documented. https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html " For comparison of JSON and non-JSON values, the non-JSON value is converted to JSON and the values compared as JSON values. " and indeed, MySQL [(none)]> select cast(true as json) = cast(1 as json); +--------------------------------------+ | cast(true as json) = cast(1 as json) | +--------------------------------------+ | 0 | +--------------------------------------+ 1 row in set (0.00 sec) Thanks
Description: A JSON boolean should be equal to 1 when extracted from JSON but it is not. MySQL [(none)]> select json_extract('{"b":true}','$.b')=true; +---------------------------------------+ | json_extract('{"b":true}','$.b')=true | +---------------------------------------+ | 1 | +---------------------------------------+ 1 row in set (0.00 sec) MySQL [(none)]> select true = 1; +----------+ | true = 1 | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) MySQL [(none)]> select json_extract('{"b":true}','$.b')=1; +------------------------------------+ | json_extract('{"b":true}','$.b')=1 | +------------------------------------+ | 0 | +------------------------------------+ 1 row in set (0.00 sec) As true = 1, json_extract('{"b":true}','$.b') should be equal to 1 but 0 is returned. How to repeat: MySQL [(none)]> select json_extract('{"b":true}','$.b')=true; +---------------------------------------+ | json_extract('{"b":true}','$.b')=true | +---------------------------------------+ | 1 | +---------------------------------------+ 1 row in set (0.00 sec) MySQL [(none)]> select true = 1; +----------+ | true = 1 | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) MySQL [(none)]> select json_extract('{"b":true}','$.b')=1; +------------------------------------+ | json_extract('{"b":true}','$.b')=1 | +------------------------------------+ | 0 | +------------------------------------+ 1 row in set (0.00 sec) Suggested fix: A JSON boolean should be equal to 1 when extracted from JSON.