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: | |
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
[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.