Bug #101861 Putting a missing row into JSON_OBJECT causes JSON_EXTRACT to fail
Submitted: 3 Dec 2020 20:28 Modified: 28 Jan 2021 0:22
Reporter: Joseph Boman Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: JSON Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: json, json_extract, JSON_OBJECT

[3 Dec 2020 20:28] Joseph Boman 
Description:
When joining two tables, adding a value to a JSON_OBJECT that is not present in one of the tables causes the JSON_OBJECT to be unsearchable in the WHERE statement.

DBFiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=32ff02b7dc5e34216f90a088209a0eb8

This doesn't seem to affect SELECT or HAVING.

How to repeat:
Create two tables, where one table has a subset of the rows in the other table.

Join the superset table to the subtable using a LEFT JOIN.

In the WHERE clause, wrap the fields of both tables into a JSON_OBJECT and attempt to search it with any means (JSON_EXTRACT, JSON_CONTAINS, etc.)

None of the rows missing from the subtable will be returned, even if they match the WHERE query.

Suggested fix:
The same behavior should occur regardless of whether the JSON_OBJECT is being searched in SELECT, WHERE, or HAVING clauses.
[4 Dec 2020 11:09] MySQL Verification Team 
Please provide a complete test case with a SQL script file instead to describe in how to repeat. Thanks in advance.
[4 Dec 2020 22:14] Joseph Boman 
All four select statements in this file should produce the same output, but they do not.

Attachment: json_extract_error.sql (application/octet-stream, text), 1.43 KiB.
[8 Dec 2020 20:05] MySQL Verification Team 
Hi,

Thanks for the test case. Verified.

all best
Bogdan
[28 Jan 2021 15:04] Jon Stephens 
Documented fix as follows in the MySQL 8.0.24 changelog:

    In some cases, when used in a left join, some JSON functions
    caused the optimizer transform it into an inner join, even
    though the inner join was not equivalent to the original left
    join. This was due to the fact that they return a value
    which is not NULL, even though one of their arguments is NULL,
    and the optimizer expected them to return NULL on NULL input.

    Functions affected by this issue included JSON_ARRAY(),
    JSON_OBJECT(), JSON_ARRAY_APPEND(), JSON_ARRAY_INSERT(),
    JSON_INSERT(), JSON_REPLACE(), and JSON_SET().

Closed.