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
Triage: D2 (Serious) / R3 (Medium) / E3 (Medium)

[26 Jul 2007 3:01] Paul Dubois
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)
[26 Jul 2007 3:26] Miguel Solorzano
Thank you for the bug report. Verified as described.
[5 Feb 2008 18:05] Bugs System
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.
[6 Feb 2008 15:21] Bugs System
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.
[6 Feb 2008 20:50] Bugs System
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.
[13 Mar 2008 19:27] Bugs System
Pushed into 6.0.5-alpha
[13 Mar 2008 19:35] Bugs System
Pushed into 5.1.24-rc
[13 Mar 2008 19:42] Bugs System
Pushed into 5.0.60
[30 Mar 2008 18:43] Jon Stephens
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.
[31 Mar 2008 14:54] Bugs System
Pushed into 5.1.24-rc
[3 Apr 2008 13:02] Bugs System
Pushed into 6.0.5-alpha
[6 May 2008 16:45] Bugs System
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.
[13 May 2008 14:16] Bugs System
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).
[14 May 2008 15:19] Bugs System
Pushed into 5.0.62
[14 May 2008 15:21] Bugs System
Pushed into 5.1.25-rc
[15 May 2008 16:13] Paul Dubois
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.
[22 May 2008 9:49] Bugs System
Pushed into 6.0.6-alpha
[22 May 2008 12:02] Paul Dubois
Noted in 6.0.6 changelog.
[5 May 2010 15:01] Bugs System
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)
[6 May 2010 3:01] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 5:59] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:27] Bugs System
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)
[28 May 2010 6:55] Bugs System
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)
[28 May 2010 15:23] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 12:02] Bugs System
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)
[17 Jun 2010 12:43] Bugs System
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)
[17 Jun 2010 13:29] Bugs System
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)