Bug #108367 implicate cast and explicate cast is different for json value
Submitted: 2 Sep 2022 9:34 Modified: 6 Sep 2022 8:17
Reporter: x j Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.26, 8.0.30 OS:Any
Assigned to: CPU Architecture:Any

[2 Sep 2022 9:34] x j
Description:
as the title says

How to repeat:
mysql root@localhost:test> SELECT JSON_UNQUOTE(cast(CAST('ABCD' AS BINARY) as json));
+----------------------------------------------------+
| JSON_UNQUOTE(cast(CAST('ABCD' AS BINARY) as json)) |
+----------------------------------------------------+
| base64:type15:QUJDRA==                             |
+----------------------------------------------------+

mysql root@localhost:test> SELECT JSON_UNQUOTE(CAST('ABCD' AS BINARY));
(3144, "Cannot create a JSON value from a string with CHARACTER SET 'binary'.")

the behavior is inconsistent
I do not know if it is a bug or not, but it does not make sense
[2 Sep 2022 10:04] MySQL Verification Team
Hello x j,

Thank you for the report and test case.

regards,
Umesh
[3 Sep 2022 10:19] huahua xu
Hi x j,

I can show the execution path of these query statements, which may be helpful to you.

For `SELECT JSON_UNQUOTE(cast(CAST('ABCD' AS BINARY) as json))`:

Item_func_json_unquote
-->args[0] = Item_typecast_json                                        // (m_is_dom=true, m_dom_alias=false, m_dom_value=(Json_opaque:{m_mytype=MYSQL_TYPE_VARCHAR, m_val="ABCD" }))
             -->m_data_type = MYSQL_TYPE_JSON
	     -->args[0] = Item_typecast_char                           // (m_ptr="ABCD", m_length=4, m_charset=my_charset_bin)
	                  -->m_data_type = MYSQL_TYPE_VARCHAR
			  -->from_cs = ...
			  -->cast_cs = my_charset_bin
	                  -->cast_length = -1
			  -->charset_conversion = false
			  -->args[0] = PTI_text_literal_text_string    // (res = "ABCB")

For `SELECT JSON_UNQUOTE(CAST('ABCD' AS BINARY))`:

Item_func_json_unquote
-->args[0] = Item_typecast_char                                       // (m_ptr="ABCD", m_length=4, m_charset=my_charset_bin)
             -->m_data_type = MYSQL_TYPE_VARCHAR
	     -->from_cs = ...
	     -->cast_cs = my_charset_bin
	     -->cast_length = -1
	     -->charset_conversion = false
	     -->args[0] = PTI_text_literal_text_string               // (res = "ABCB")

At present, I think it is most likely not a bug.
[6 Sep 2022 8:17] Knut Anders Hatlen
Posted by developer:
 
I believe this is not a bug. I'm not aware of any good use case for expressions such as JSON_UNQUOTE(binary_col) or JSON_EXTRACT(binary_col, path), and when they occur in a query, I think it is unlikely that it's intentional. So it sounds reasonable to signal an error to let the user know about it. If it is indeed intentional, the user can make that clear by adding an explicit cast to JSON. The behaviour of CAST from BINARY to JSON is described here (see the row for "All other types" in the table of conversion rules): https://dev.mysql.com/doc/refman/8.0/en/json.html#json-converting-between-types

So I believe raising an error for implicit conversion from BINARY to JSON and accepting it as an explicit conversion, has some merit, even though some may argue that it is inconsistent.