MySQL Bugs: #30059: End-space truncation is inconsistent or incorrect

Bug #30059	End-space truncation is inconsistent or incorrect
Submitted:	26 Jul 2007 3:01	Modified:	17 Jun 2010 22:44
Reporter:	Paul DuBois	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0 and up	OS:	Any
Assigned to:	Gleb Shchepa	CPU Architecture:	Any

Description:
I have been checking how the server handles truncation for inserts
of too-long values into CHAR/VARCHAR/TEXT/BLOB columns when the
truncated characters are spaces.

What is supposed to happen is that the insert should be performed,
the extra spaces should be truncated, and a warning should be 
produced.  This is true both for non-strict and strict SQL mode.
(Even for strict mode, truncation of end spaces is allowed without
producing an error, but a warning should occur.)

References:
http://dev.mysql.com/doc/refman/5.0/en/char.html
http://dev.mysql.com/doc/refman/5.0/en/blob.html

The first page says:
"If strict SQL mode is not enabled and you assign a value to a CHAR
or VARCHAR column that exceeds the column's maximum length, the
value is truncated to fit and a warning is generated. For truncation
of non-space characters, you can cause an error to occur (rather
than a warning) and suppress insertion of the value by using strict
SQL mode."

The second page is similar, but refers to TEXT and BLOB.

Here is what actually happens for inserts into the various data
types, both for non-strict and strict mode:

Strict mode not enabled:

Type    Desired result       Actual current behavior
CHAR    Insert with Warning  Insert with no Warning (incorrect)
VARCHAR Insert with Warning  Insert with Note (incorrect)
TEXT    Insert with Warning  Insert with Warning (correct)
BLOB    Insert with Warning  Insert with Warning (correct)

Strict mode enabled:

Type    Desired result       Actual current behavior
CHAR    Insert with Warning  Insert with no Warning (incorrect)
VARCHAR Insert with Warning  Insert with Note (incorrect)
TEXT    Insert with Warning  An Error occurs (incorrect)
BLOB    Insert with Warning  An Error occurs (incorrect)

A script to generate these results follows.

How to repeat:
To check end-space truncation in non-strict mode:

set sql_mode = '';
drop table if exists t;
create table t (c char(255), vc varchar(255), t tinytext, b tinyblob);

# insert with truncation of end spaces
set @c = repeat(' ', 256);
insert into t values(@c, @c, @c, @c);
show warnings;

To check end-space truncation in strict mode:

set sql_mode = 'traditional';
drop table if exists t;
create table t (c char(255), vc varchar(255), t tinytext, b tinyblob);

# insert with truncation of end spaces
set @c = repeat(' ', 256);
insert into t (c) values(@c);
show warnings;
insert into t (vc) values(@c);
show warnings;
insert into t (t) values(@c);
show warnings;
insert into t (b) values(@c);
show warnings;

Results for non-strict mode:

mysql> set sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists t;
Query OK, 0 rows affected (0.04 sec)

mysql> create table t (c char(255), vc varchar(255), t tinytext, b tinyblob);
Query OK, 0 rows affected (0.09 sec)

mysql> 
mysql> # insert with truncation of end spaces
mysql> set @c = repeat(' ', 256);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(@c, @c, @c, @c);
Query OK, 1 row affected, 3 warnings (0.01 sec)

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Note    | 1265 | Data truncated for column 'vc' at row 1 | 
| Warning | 1265 | Data truncated for column 't' at row 1  | 
| Warning | 1265 | Data truncated for column 'b' at row 1  | 
+---------+------+-----------------------------------------+
3 rows in set (0.01 sec)

Results for strict mode:

mysql> set sql_mode = 'traditional';
Query OK, 0 rows affected (0.34 sec)

mysql> drop table if exists t;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t (c char(255), vc varchar(255), t tinytext, b tinyblob);
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> # insert with truncation of end spaces
mysql> set @c = repeat(' ', 256);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t (c) values(@c);
Query OK, 1 row affected (0.03 sec)

mysql> show warnings;
Empty set (0.02 sec)

mysql> insert into t (vc) values(@c);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-----------------------------------------+
| Level | Code | Message                                 |
+-------+------+-----------------------------------------+
| Note  | 1265 | Data truncated for column 'vc' at row 1 | 
+-------+------+-----------------------------------------+
1 row in set (0.00 sec)

mysql> insert into t (t) values(@c);
ERROR 1406 (22001): Data too long for column 't' at row 1
mysql> show warnings;
+-------+------+---------------------------------------+
| Level | Code | Message                               |
+-------+------+---------------------------------------+
| Error | 1406 | Data too long for column 't' at row 1 | 
+-------+------+---------------------------------------+
1 row in set (0.00 sec)

mysql> insert into t (b) values(@c);
ERROR 1406 (22001): Data too long for column 'b' at row 1
mysql> show warnings;
+-------+------+---------------------------------------+
| Level | Code | Message                               |
+-------+------+---------------------------------------+
| Error | 1406 | Data too long for column 'b' at row 1 | 
+-------+------+---------------------------------------+
1 row in set (0.00 sec)

Thank you for the bug report. Verified as described.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/41731

ChangeSet@1.2662, 2008-02-05 22:04:08+04:00, gshchepa@host.loc +4 -0
  Fixed bug#30059.
  Server handles truncation for assignment of too-long values
  into CHAR/VARCHAR/TEXT columns in a different ways when the
  truncated characters are spaces.
  
  Space truncation processing has been synchronised over
  CHAR/VARCHAR/TEXT columns and strict/non-strict server
  modes: server always sends a truncation note instead of
  a warning or an error.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/41790

ChangeSet@1.2596, 2008-02-06 23:26:05+04:00, gshchepa@host.loc +9 -0
  Fixed bug#30059.
  Server handles truncation for assignment of too-long values
  into CHAR/VARCHAR/TEXT columns in a different ways when the
  truncated characters are spaces:
  1. CHAR(N) columns silently ignore end-space truncation;
  2. TEXT columns post a truncation warning/error in the
     non-strict/strict mode.
  3. VARCHAR columns always post a truncation note in
     any mode.
  
  Space truncation processing has been synchronised over
  CHAR/VARCHAR/TEXT columns: current behavior of VARCHAR
  columns has been propagated as standard.
  
  Binary-encoded string/BLOB columns are not affected.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/41831

ChangeSet@1.2597, 2008-02-07 04:54:58+04:00, gshchepa@host.loc +1 -0
  bdb.result:
    Minor post-fix for bug#30059.

Pushed into 6.0.5-alpha

Pushed into 5.1.24-rc

Pushed into 5.0.60

Documented as follows in the 5.0.60, 5.1.23-ndb-6.3.11, 5.1.24, and 6.0.5 changelogs:

        The server handled truncation of values having excess trailing spaces
        into CHAR, VARCHAR, and TEXT columns in different ways. This behavior
        has now been made consistent for columns of all three of these types,
        and now follows the existing behavior of VARCHAR columns in this regard;
        that is, a Note is always issued whenever such truncation occurs.

        This change does not affect columns of these three types when using a
        binary encoding; BLOB columns are also unaffected by the change, since
        they always use a binary encoding.

Behaviour change also noted in the char and text sections of the 5.0/5.1/6.0 Manual's Data Types chapter.

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/46410

ChangeSet@1.2623, 2008-05-06 21:43:46+05:00, gshchepa@host.loc +9 -0
  Partial rollback of fix for bug #30059: End-space truncation is inconsistent
  or incorrect.
  
  For better conformance with standard, truncation procedure of CHAR columns
  has been changed to ignore truncation of trailing whitespace characters
  (note has been removed).
  
  Finally, for columns with non-binary charsets:
  
  1. CHAR(N) columns silently ignore trailing whitespace truncation;
  2. VARCHAR and TEXT columns issue Note about truncation.
  
  BLOBs and other columns with BINARY charset are unaffected.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/46656

ChangeSet@1.2630, 2008-05-13 19:14:49+05:00, gshchepa@host.loc +1 -0
  mix2_myisam.result:
    Post-push test case update (bug #30059).

Pushed into 5.0.62

Pushed into 5.1.25-rc

Noted in 5.0.62, 5.1.25 changelogs.

The server no longer issues warnings for truncation of excess spaces
for values inserted into CHAR columns. This reverts a change in the
previous release that caused warnings to be issued.

Setting report to Need Doc Info pending push of fix into 6.0.x.

Pushed into 6.0.6-alpha

Noted in 6.0.6 changelog.

Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)