MySQL Bugs: #116522: Concatenate char and binary, return data type with incorrect length

Bug #116522	Concatenate char and binary, return data type with incorrect length
Submitted:	1 Nov 2024 7:13	Modified:	4 Nov 2024 2:56
Reporter:	li liu	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.7.44	OS:	CentOS
Assigned to:		CPU Architecture:	Any

Description:
The char and binary types are concatenated to form a new piece of data, which returns the varbinary type. However, the length of the varbinary type is incorrect and not the maximum safe length. The actual length of the data may be greater than the length returned
Creating a table through 'create table select' may result in errors

How to repeat:
The utf8 character set used
mysql> create table t1(c1 char(20),c2 binary(20));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values('一二三四五六七八九十一二三四五六七八九十','01234567890123456789');
Query OK, 1 row affected (0.02 sec)

mysql> create view v1 as select CONCAT(c1,c2) as col from t1;
Query OK, 0 rows affected (0.01 sec)

mysql> desc v1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| col   | varbinary(40) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> create table t2 as select CONCAT(c1,c2) as col from t1;
ERROR 1406 (22001): Data too long for column 'col' at row 1

Suggested fix:
I think the data type seen by desc v1 should be varbinary (80)
And 'create table t2 as select CONCAT(c1,c2) as col from t1;' can succeed

Hi Mr. Liu,

Thank you for your bug report.

However, version 5.7 is not supported for several years , so far .....

Also, you are trying an operation on the two different data types. This is forbidden, according to SQL standard. MySQL does what it can, but mixing different data types never lead to the desired result. Trying using the CAST functionality, as described in our Reference Manual.

Unsupported.

Thank you very much for your reply
I performed the same operation on MySQL version 8.0.40 and obtained a reasonable return result. The new version has fixed this defect
mysql> create table t1(c1 char(10), c2 binary(10));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1 values('一二三四五六七八九十','1234567890');
Query OK, 1 row affected (0.01 sec)

mysql> create view v1 as select concat(c1,c2) as col from t1;
Query OK, 0 rows affected (0.01 sec)

mysql> desc v1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| col   | varbinary(40) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> create table t2 as select concat(c1,c2) as col from t1;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> exit
Bye
[yashan_sa@AchorBase ~]$ mysql --version
mysql  Ver 8.0.40 for Linux on x86_64 (MySQL Community Server - GPL)