json opaque value has different behavior with MySQL after stored

[YangKeao]

Enhancement

In the mysql 5.7 mysql-server/sql/item.cc: Item::save_in_field_inner, it transformed the JSON into the string, and stored it in the field:

    String *result;
    const CHARSET_INFO *cs= collation.collation;
    char buff[MAX_FIELD_WIDTH];		// Alloc buffer for small columns
    str_value.set_quick(buff, sizeof(buff), cs);
    result=val_str(&str_value);
    if (null_value)
    {
      str_value.set_quick(0, 0, cs);
      return set_field_to_null_with_conversions(field, no_conversions);
    }

    /* NOTE: If null_value == FALSE, "result" must be not NULL.  */

    field->set_notnull();
    type_conversion_status error=
      field->store(result->ptr(),result->length(),
                   field->type() == MYSQL_TYPE_JSON ? result->charset() : cs);
    str_value.set_quick(0, 0, cs);
    return error;

During this procedure, the opaque information is lost, the MySQL cannot distinguish the opaque value and string value anymore. It causes the confusing behavior:

create table test (a json);
insert into test select json_objectagg('a', b'01010101');

select json_type(json_extract(a, '$.a')); -- returns STRING
select json_type(json_extract(json_object('a', b'01010101'), '$.a'));  -- returns BIT

Through the two json values are constructed in the same way, they have different behavior before and after storing to the disk. This confusing behavior results in the incompatibility of following SQLs:

create table test (a json);
insert into test select json_objectagg('a', b'01010101');

select * from test where  json_extract(a, '$.a') = "base64:type16:VQ=="; 

TiDB gives empty set (as the json opaque is never equal with the json string), but MySQL will give the inserted row. Instead, consider the following example:

create table test (a json);
insert into test select json_objectagg('a', b'01010101');

select * from test where json_extract(a,'$.a') = json_extract(json_object('a', b'01010101'), '$.a');

The TiDB will give a row, but the MySQL will not. (After fixing the problems in #37435, because this function will also lose the charset / collate information 😢 )

I don't know whether we should be compatible with MySQL in this case, because the current behavior (after fixing #37435) of TiDB sounds much more reasonable.

[YangKeao]

@xiongjiwei PTAL

[xiongjiwei]

I agree TiDB is more reasonable. I will report a bug to MySQL and see how they reply

[xiongjiwei]

MySQL team confirmed it is a bug from MySQL, see https://bugs.mysql.com/bug.php?id=108326

[YangKeao]

@xiongjiwei As MySQL team confimed it as a bug, I'll close this issue. If there are further discussion about this problem, feel free to reopen or comment below 😸 .