WL#12129: Deprecate SQL_MODE PAD_CHAR_TO_FULL_LENGTH
Affects: Server-8.0 — Status: Complete
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).
Copyright (c) 2000, 2019, Oracle Corporation and/or its affiliates. All rights reserved.