| Bug #90833 | NULLIF returns boolean in context of JSON_ARRAYAGG and JSON_OBJECTAGG's value | ||
|---|---|---|---|
| Submitted: | 11 May 2018 10:43 | Modified: | 12 Jul 2018 12:40 |
| Reporter: | Markus Winand | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: JSON | Severity: | S3 (Non-critical) |
| Version: | 8.0.11 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[11 May 2018 11:35]
MySQL Verification Team
Thank you for the bug report.
[15 May 2018 7:03]
Catalin Besleaga
Documentation says: NULLIF(expr1,expr2) Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END. The return value has the same type as the first argument. but the return type is bool since Item_func_nullif inherits from Item_bool_func2 which is wrong. So the problem is with NULLIF not with the json functions.
[12 Jul 2018 12:40]
Jon Stephens
Documented fix as follows in the MySQL 8.0.13 changelog:
In some contexts, the NULLIF() function returned its first
argument as a boolean value rather than its actual type. This
was noticed when the result of this function was used as an
argument to JSON_ARRAYAGG() or JSON_OBJECTAGG(), but could have
occurred in other such cases.
Closed.
Description: mysql> SELECT NULLIF(1, 2) AS "nullif" -> , JSON_OBJECTAGG(NULLIF(1, 2), NULLIF(1, 2)) AS "objectagg" -> , JSON_ARRAYAGG(NULLIF(1, 2)) AS "arrayagg"; +--------+-------------+----------+ | nullif | objectagg | arrayagg | +--------+-------------+----------+ | 1 | {"1": true} | [true] | +--------+-------------+----------+ As 1 is not equal to 2, NULLIF(1,2) should be 1, as visible in the first column. In some JSON aggregate context it evaluates to a boolean value. When using strings, e.g. NULLIF('a', 'b') a conversion warning is raised: Truncated incorrect INTEGER value: 'a' (1292). The expected result can be seen when using the equivalent CASE expression instead of NULLIF (problem doesn't exist for CASE expressions): mysql> SELECT CASE WHEN 1 = 2 THEN NULL ELSE 1 END AS "nullif" -> , JSON_OBJECTAGG(CASE WHEN 1 = 2 THEN NULL ELSE 1 END, CASE WHEN 1 = 2 THEN NULL ELSE 1 END) AS "objectagg" -> , JSON_ARRAYAGG(CASE WHEN 1 = 2 THEN NULL ELSE 1 END) AS "arrayagg"; +--------+-----------+----------+ | nullif | objectagg | arrayagg | +--------+-----------+----------+ | 1 | {"1": 1} | [1] | +--------+-----------+----------+ How to repeat: Run the above queries.