MySQL Bugs: #108326: confusing result about json value

Bug #108326	confusing result about json value
Submitted:	30 Aug 2022 2:56	Modified:	7 Sep 2022 13:02
Reporter:	x j	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

Description:
if create a json value with a binary string, it will be different between columns and constant

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

select json_type(json_extract(a, '$.a')) from test; -- STRING
select json_type(json_extract(json_object('a', b'01010101'), '$.a')); --BLOB

the value is the same but one comes from a table, the type of it is "string", another is BLOB, and it causes:

mysql> select * from test where  json_extract(a, '$.a') = "base64:type15:VQ==";
+-----------------------------+
| a                           |
+-----------------------------+
| {"a": "base64:type15:VQ=="} |
+-----------------------------+
1 row in set (0.00 sec)

well, if we use the constant value, we get:

mysql> select json_extract(json_object('a', b'01010101'), '$.a') = "base64:type15:VQ==";
+---------------------------------------------------------------------------+
| json_extract(json_object('a', b'01010101'), '$.a') = "base64:type15:VQ==" |
+---------------------------------------------------------------------------+
|                                                                         0 |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

I do not think a value should be affected by where it comes from

Hi Mr j,

Thank you for your bug report.

We have managed to repeat the behaviour with 8.0.30:

json_type(json_extract(a, '$.a'))
STRING
json_type(json_extract(json_object('a', b'01010101'), '$.a'))
BLOB

Verified as reported.

The execution path for `select json_type(json_extract(json_object('a', b'01010101'), '$.a'));`: 

Item_func_json_type
-->args[0] = Item_func_json_extract                                     # (m_is_dom = true, m_dom_value = {json_type=enum_json_type::J_OPAQUE, m_mytype=MYSQL_TYPE_VARCHAR, m_val="U"} })
             -->m_data_type = MYSQL_TYPE_JSON
	        args[0] = Item_func_json_row_object                     # Json_object:(m_map={"a":{json_type=enum_json_type::J_OPAQUE, m_mytype=MYSQL_TYPE_VARCHAR, m_val="U" }})
		          -->m_data_type = MYSQL_TYPE_JSON
			     args[0] = PTI_text_literal_text_string
			     args[1] = Item_bin_string                  # val_json_func_field_subselect(args[1]) = ([Json_opaque] = {m_mytype=MYSQL_TYPE_VARCHAR, m_val="U" })
                args[1] = PTI_text_literal_text_string

for `select json_type(json_extract(a, '$.a')) from test;`: 

Item_func_json_type
-->args[0] = Item_func_json_extract                                     # (m_is_dom = false, m_value={m_data="base64:type15:VQ==", m_type=STRING})
             -->m_data_type = MYSQL_TYPE_JSON
	        args[0] = Item_field
                args[1] = PTI_text_literal_text_string

I think that the mysql server serializes json value with missing type information, when it stores the column value.

Documented fix as follows in the MySQL 8.0.32 changelog:

  While saving the result of JSON_ARRAYAGG() or JSON_OBJECTAGG()
  in a column, the data type information was lost due to the
  result being an item of type SUM_FUNC_ITEM. To fix this, we
  remove the type check and this way retain the type information.

Closed.

Thank you, Jon .......