Bug #46839 ANSI SQL_MODE should include PAD_CHAR_TO_FULL_LENGTH
Submitted: 20 Aug 2009 18:45 Modified: 1 Sep 2009 19:15
Reporter: Justin Swanhart Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.4.0 OS:Any
Assigned to: CPU Architecture:Any

[20 Aug 2009 18:45] Justin Swanhart
Description:
PAD_CHAR_TO_FULL_LENGTH was added in MySQL 5.1 to make MySQL CHAR behavior ANSI SQL compliant with respect to retrieving trailing space from CHAR columns.

This SQL_MODE should be part of the ANSI SQL_MODE as the default MySQL behavior is not ANSI compliant, and I would expect that setting ANSI would get me this behavior.

How to repeat:
mysql> set SQL_MODE=ANSI;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'SQL_MODE';
+---------------+-------------------------------------------------------------+
| Variable_name | Value                                                       |
+---------------+-------------------------------------------------------------+
| sql_mode      | REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
+---------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.4.0-beta |
+------------+
1 row in set (0.00 sec)

Suggested fix:
Include PAD_CHAR_TO_FULL_LENGTH to ANSI SQL_MODE.
[1 Sep 2009 18:20] Peter Gulutzan
We gave this consideration, with these arguments:

On the plus side: it's true that an ANSI-compliant DBMS should return
trailing spaces in this case, and our manual says "This mode [ANSI]
changes syntax and behavior to conform more closely to standard SQL".
So users can reasonably expect that all the sql_mode options which are
more ANSI-compliant, will be included in the sql_mode=ansi package.

On the minus side: the manual lists what's in the package,
REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE.
http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html
Adding PAD_CHAR_TO_FULL_LENGTH now is a change in documented behaviour.

The decision was: this is not a bug, and we won't make the change.
[1 Sep 2009 19:15] Justin Swanhart
The pluses clearly outweigh the minuses.

I don't understand why this shouldn't be changed in future versions (5.4 forward).  

ONLY_FULL_GROUP_BY was REMOVED previously, and supposedly will be added back in when it actually supports ANSI queries w/ functionally dependent group by keys.

Why can't PAD_CHAR_TO_FULL_LENGTH be ADDED to new versions?
[1 Sep 2009 19:23] Peter Laursen
I also have difficulty in accepting PG's explanation.  It can be paraphrased like this: "It is not a bug because it is documented like that".  My conclusion: this is not only a *bug* it is even a *documented bug*.  It does not make it less a bug - it is worse actually in my opinion. But this is not first time that we see *specification* and *documentation* confused here. 

The *specification* for ANSI sql_mode is in the ANSI standards - nowhere else.
[2 Sep 2009 16:04] Peter Gulutzan
ONLY_FULL_GROUP_BY was removed from the sql_mode=ansi package
long ago (Bug#8510). I am not aware of an official statement
that we'll put it back in.