| Bug #120001 | Inconsistent Type Aggregation and Promotion in UNION Operations | ||
|---|---|---|---|
| Submitted: | 7 Mar 5:36 | Modified: | 9 Mar 9:34 |
| Reporter: | cl hl | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 9.5.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[9 Mar 9:34]
Roy Lyseng
Thank you for the bug report. However, this is a consequence of the collation coercibility rules in MySQL. A database column has stronger coercibility than a literal value, thus the column's character set and collation will be preferred as the UNIONed type. A workaround is to append "COLLATE 'binary'" after the selected literal, which will promote the literal value's coercibility.
Description: There is a logical inconsistency in how the MySQL optimizer resolves the result set metadata for UNION operations. When a binary-compatible type (like GEOMETRY results or BLOB) is unioned with a LONGTEXT column, MySQL incorrectly attempts to promote the result to a character set-aware string (e.g., utf8mb4), leading to conversion errors. However, when the same column is replaced with a string literal (''), MySQL correctly promotes the result to a BLOB type. mysql> SELECT x'FF' AS col UNION ALL SELECT c_text FROM t_test; ERROR 3854 (HY000): Cannot convert string '\xFF' from binary to utf8mb4 mysql> SELECT x'FF' AS col UNION ALL SELECT ''; +----------+ | col | +----------+ | 0xFF | | 0x | +----------+ 2 rows in set (0.00 sec) How to repeat: DROP TABLE IF EXISTS t_test; CREATE TABLE t_test (c_text LONGTEXT) CHARSET=utf8mb4; INSERT INTO t_test VALUES ('a'); SELECT x'FF' AS col UNION ALL SELECT c_text FROM t_test; SELECT x'FF' AS col UNION ALL SELECT '';