WL#12129: Deprecate SQL_MODE PAD_CHAR_TO_FULL_LENGTH

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

This is to deprecate the SQL_MODE PAD_CHAR_TO_FULL_LENGTH, for later removal.

We have an SQL mode called PAD_CHAR_TO_FULL_LENGTH that changes CHAR(n)
behavior to be more like ANSI, in that it keeps the full padding when
converting from CHAR to VARCHAR. However, it's poorly supported (ie., indexes
don't seem to heed it), and it doesn't seem to be widely used. Instead of
having it linger in its current half-baked state, just deprecate it for later
removal.

The ANSI behavior is to add spaces when converting e.g. CHAR(10) to something
else, e.g. 'foo' becomes 'foo       ' on CONCAT. However, this is not nearly
universal; e.g. Postgres and SQLite both have the same behavior as default MySQL
(strip spaces on conversion). Furthermore, in a Unicode world, the notion of
padding is becoming increasingly problematic; if you have a CHAR(10)
and store é in it in a way that takes two code points (e.g., e followed by a
combining accent), should you add 8 or 9 spaces? What about a double-width
character like in Japanese or Chinese; should you count that as 1 or 2?

In general, like with most SQL modes, we can't really support them (it's so
often that you don't actually know whether they should be interpreted as on or
off), so we have to choose one and stick with it. Given the relative decline of
fixed-length columns in modern systems, and the problems above, it would not
seem worthwhile to change our behavior here; we should instead just stick with
what we have and remove the mode.

F1. There should be a deprecation warning whenever the SQL mode is set to PAD_CHAR_TO_FULL_LENGTH (or a combination of modes that includes it).