MySQL Bugs: #40594: VARCHAR

Bug #40594	VARCHAR
Submitted:	8 Nov 2008 23:33	Modified:	25 Nov 2008 19:16
Reporter:	Sheeri Cabral (Candidate Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	6.0.6-alpha-community-log	OS:	Windows
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	qc, space, spaces, trailing space, varchar

Description:
http://dev.mysql.com/doc/refman/6.0/en/char.html states:

"For VARCHAR columns, excess trailing spaces are truncated prior to insertion and a warning is generated, regardless of the SQL mode in use. For CHAR  columns, truncation of excess trailing spaces from inserted values is performed silently regardless of the SQL mode."

However, this does not seem to be the case.

How to repeat:
mysql> SELECT @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode                                                     |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE varchar_test (vc VARCHAR(10)) ENGINE=INNODB DEFAULT CHARSET=
latin1;
Query OK, 0 rows affected (0.48 sec)

mysql> SHOW CREATE TABLE varchar_test\G
*************************** 1. row ***************************
       Table: varchar_test
Create Table: CREATE TABLE `varchar_test` (
  `vc` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> INSERT INTO varchar_test (vc) VALUES ('a'),(' a'),('a '),(' a '),(' ');
Query OK, 5 rows affected (0.48 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT CONCAT('/',vc,'/'), LENGTH(vc) FROM varchar_test;
+--------------------+------------+
| CONCAT('/',vc,'/') | LENGTH(vc) |
+--------------------+------------+
| /a/                |          1 |
| / a/               |          2 |
| /a /               |          2 |
| / a /              |          3 |
| / /                |          1 |
+--------------------+------------+
5 rows in set (0.00 sec)

Suggested fix:
Correct the documentation.

Don't know if the Row Format or Version of InnoDB has any bearing on the result:

mysql> SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='varchar_test'\G

*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: test
     TABLE_NAME: varchar_test
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact
     TABLE_ROWS: 5
 AVG_ROW_LENGTH: 3276
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 169869312
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2008-11-08 18:24:11
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.16 sec)

This is definitely a documentation issue.....:

"For VARCHAR columns, excess trailing spaces are truncated prior to insertion and a warning is generated, regardless of the SQL mode in use. For CHAR  columns, truncation of excess trailing spaces from inserted values is performed silently regardless of the SQL mode.

VARCHAR values are not padded when they are stored. Trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL."

It seems like you mean to say, "If a value larger than the length is inserted, and the characters beyond the maximum length are spaces:

* For a VARCHAR field, the spaces beyond the maximum length are truncated and a warning is generated, regardless of SQL mode.
* For a CHAR field, the spaces beyond the length are truncated silently.

I think that trailing spaces are *not* removed since early 5.0 versions! (in 3.x and 4.x they were)

Thank you for the report.

Behavior changed in 5.0.3 due to fix of bug #7084. But documentation still is not updated. So I verify this report as documentation bug.

Yes, "excess trailing spaces" doesn't mean just "trailing spaces," it means trailing spaces that are too long to fit.  That is: If VARCHAR truncated characters are spaces, no error. If truncated characters are not spaces, warning or error depending on SQL mode.

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.