|
I run this script in Linux console:
SET sql_mode='';
|
SET NAMES utf8;
|
CREATE OR REPLACE TABLE t1 AS SELECT COALESCE('ßa',_binary 'a');
|
SHOW WARNINGS;
|
SELECT * FROM t1;
|
SHOW CREATE TABLE t1;
|
Notice, it returns a warning:
+---------+------+------------------------------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+------------------------------------------------------------------+
|
| Warning | 1265 | Data truncated for column 'COALESCE('ßa',_binary 'a')' at row 1 |
|
+---------+------+------------------------------------------------------------------+
|
The data was indeed truncated:
+-----------------------------+
|
| COALESCE('ßa',_binary 'a') |
|
+-----------------------------+
|
| ß |
|
+-----------------------------+
|
And the reason for truncation is a wrong field type:
+-------+-----------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-----------------------------------------------------------------------------------------------------------------------------+
|
| t1 | CREATE TABLE `t1` (
|
`COALESCE('ßa',_binary 'a')` varbinary(2) NOT NULL DEFAULT ''
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+-----------------------------------------------------------------------------------------------------------------------------+
|
It should create VARBINARY(3):
SELECT OCTET_LENGTH('ßa');
|
+---------------------+
|
| OCTET_LENGTH('ßa') |
|
+---------------------+
|
| 3 |
|
+---------------------+
|
|