MySQL Bugs: #63116: pad_char_to_full

Bug #63116	pad_char_to_full_length and TEXT types
Submitted:	6 Nov 2011 13:50	Modified:	7 May 2012 19:25
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	any	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	qc

Description:
I do not find it clearly documented how 'pad_char_to_full_length' SQL_mode behaves with TEXT types.

http://dev.mysql.com/doc/refman/5.5/en/char.html says

"The CHAR and VARCHAR types .. differ .. in whether trailing spaces are retained. When CHAR (not VARCHAR - my comment) values are stored, they are right-padded with spaces to the specified length. When CHAR (not varchar - my comment) values are retrieved, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled."

How to repeat:
This indicates that TEXT and VARCHAR behaves the same in this respect

SET sql_mode = '';

USE test;
DROP TABLE IF EXISTS trailingblanks;
CREATE TABLE trailingblanks (id INT, str1 CHAR (2), str2 VARCHAR(2), str3 TEXT);
INSERT INTO trailingblanks VALUES (1, 'a', 'a', 'a');
INSERT INTO trailingblanks VALUES (2, CONCAT('a', X'20'), CONCAT('a', X'20'), CONCAT('a', X'20'));

SELECT id, HEX(str1), HEX(str2), HEX(str3) FROM trailingblanks;
/* returns

    id  hex(str1)  hex(str2)  hex(str3)
------  ---------  ---------  ---------
     1  61         61         61       
     2  61         6120       6120             
*/

SET sql_mode = 'pad_char_to_full_length';

USE test;
DROP TABLE IF EXISTS trailingblanks;
CREATE TABLE trailingblanks (id INT, str1 CHAR (2), str2 VARCHAR(2), str3 TEXT);
INSERT INTO trailingblanks VALUES (1, 'a', 'a', 'a');
INSERT INTO trailingblanks VALUES (2, CONCAT('a', X'20'), CONCAT('a', X'20'), CONCAT('a', X'20'));

SELECT id, HEX(str1), HEX(str2), HEX(str3) FROM trailingblanks;
/* returns

    id  HEX(str1)  HEX(str2)  hex(str3)
------  ---------  ---------  ---------
     1  6120       61         61       
     2  6120       6120       6120          
*/

Suggested fix:
One option could be to rename the page to "The CHAR, VARCHAR, and TEXT Types" and add details for TEXT.  But probably not the only option how to clarify this.

This page http://dev.mysql.com/doc/refman/5.5/en/blob.html
lists sme particularies with TEXT types

"Truncation of excess trailing spaces from values to be inserted into TEXT columns always generates a warning, regardless of the SQL mode. 

If a TEXT column is indexed, index entry comparisons are space-padded at the end. This means that, if the index requires unique values, duplicate-key errors will occur for values that differ only in the number of trailing spaces. For example, if a table contains 'a', an attempt to store 'a ' causes a duplicate-key error. This is not true for BLOB columns."

.. but nowhere do I find it documented that as regards padding (on INSERT) and retrival (on SELECT) of trailing spaces a TEXT behaves identically to a VARCHAR (what I think it does).

BTW: the two 'particularities' quoted above look to me like 'documented bugs'.  Any chance that they can be fixed?

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

"
For TEXT and BLOB columns, there is no padding on insert and no bytes
are stripped on select.
"