TIDB版本升级为7.5， java执行带json的SQL结果和升级前不一致问题

huixiang · 2025 年7 月 8 日 06:22

1）有类似事件

github.com/pingcap/tidb

json opaque value has different behavior with MySQL after stored

已打开 12:50PM - 29 Aug 22 UTC

已关闭 07:18AM - 31 Aug 22 UTC

YangKeao

type/enhancement component/json

## Enhancement In the mysql 5.7 `mysql-server/sql/item.cc: Item::save_in_fiel…d_inner`, it transformed the JSON into the string, and stored it in the field: ```cpp 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: ```sql 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: ```sql 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: ```sql 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 https://github.com/pingcap/tidb/issues/37435, because this function will also lose the charset / collate information :cry: ) 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.

2）研发改代码解决的。
Map<String, Object> item = Maps.newHashMap();
for(Map.Entry<String,Object> entry: item1.entrySet()){
String key = entry.getKey();
Object value = entry.getValue();
if (value != null && value instanceof byte){
value = new String((byte) value, StandardCharsets.UTF_8);
}
item.put(key,value);
}