utf16 is not a super-set for ucs2 because these two character sets treat high surrogate codes (0xD800..0xDBFF) and low surrogate codes (0xDC00..0xDFFF) differently:
- ucs2 does not treat surrogates in any special way, so a single surrogate can present in data
- utf16 uses surrogages to encode non-BMP characters, so a single surrogate cannot appear (surrogates can go only in pairs)
Non-instant ALTER catches such bad conversion attempts:
Instant ALTER does not catch surrogates and alters the table silently, so bad data is possible after ALTER:
Notice, in the last line OCTET_LENGTH(a) is greater than 0, while CHAR_LENGTH(a) is 0, which is not possible normally.
There are two ways to fix this:
- Disallow surrogates in ucs2
- Disallow instant ALTER for ucs2 to utf16
The former is probably preferable, but can bring previous version compatibility issues.
If we ever disallow surrogates in ucs2, we should probably also disallow them in all other character sets, e.g. utf8, utf8mb4, utf32.