Bug #87854 JSON_OBJECT creates invalid JSON code

• Submitted: 25 Sep 2017 12:49
• Modified: 29 Sep 2017 12:49
• Reporter: Dominik Luntzer
• Status: Closed
• Category: MySQL Server: JSON
• Severity: S3 (Non-critical)
• Version: 5.7, 8.0
• OS: Any
• CPU Architecture: Any

[25 Sep 2017 12:49] Dominik Luntzer

Description:
The code below is creating invalid JSON output.

I tried to reduce the example as far as possible.

The error will not occur when one of the following changes is made:
* Remove a key declaration
* Use inner join instead of left join
* Remove group by clause
* Remove union select

How to repeat:
https://www.db-fiddle.com/f/35bJkFuB1kUHosiHTZ7R55/7

CREATE TABLE songs (
  id INT,
  KEY (id)
);

INSERT INTO songs VALUES (1),(2),(3);

CREATE TABLE ratings (
  songId INT,
  KEY (songId)
);

INSERT INTO ratings VALUES (1),(1);

SELECT id, JSON_OBJECT("id", id) AS brokenJSON
FROM songs
LEFT JOIN ratings ON ratings.songId = id
GROUP BY id
UNION ALL
SELECT 0, 0;

***** OUTPUT *****

+------+-----------------------------+
| id   | brokenJSON                  |
+------+-----------------------------+
|    1 | {"id": 1}                   |
|    2 | {"id": 1}{"id": 2}          |
|    3 | {"id": 1}{"id": 2}{"id": 3} |
|    0 | 0                           |
+------+-----------------------------+
4 rows in set (0,00 sec)

[25 Sep 2017 13:04] Dominik Luntzer

change synopsis

[26 Sep 2017 8:19] Knut Anders Hatlen

This looks like a bug to me. Thanks for the bug report, Dominik.

[29 Sep 2017 12:49] Jon Stephens

Documented fix in the MySQL 8.0.4 changelog as follows:

    When inserting JSON values created from the result of a GROUP BY
    query, the inserted values could sometimes include the
    concatenation of all the values previously inserted into that
    column.

Closed.

[30 Sep 2017 5:24] Jon Stephens

Also fixed in 5.7.21, and noted in the appropriate changelog.

Status unchanged.