| Bug #7084 | VARCHAR columns should not strip trailing spaces | ||
|---|---|---|---|
| Submitted: | 7 Dec 2004 20:54 | Modified: | 11 Jan 2005 13:20 |
| Reporter: | Trudy Pelzer | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.3-alpha-debug | OS: | Linux (SuSE 9.1) |
| Assigned to: | Alexander Barkov | CPU Architecture: | Any |
[8 Dec 2004 20:22]
MySQL Verification Team
Hi, Did you do it in the "traditional" mode?
[8 Dec 2004 21:06]
Trudy Pelzer
Note to Victoria: Yes, I did the test using sql_mode='traditional'. But this should make no difference to the result; the rules that I quoted should work regardless of sql_mode, now that Monty has finished the "true varchar" work.
[8 Dec 2004 21:35]
MySQL Verification Team
Verified with latest 5.0 BK tree
I got the same error only with "traditional" mode.
mysql> create table t1 (col1 varchar(5));
Query OK, 0 rows affected (0.14 sec)
mysql> insert into t1 values ('hi');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 values ('hi ');
Query OK, 1 row affected (0.00 sec)
mysql> select col1,char_length(col1) from t1;
+-------+-------------------+
| col1 | char_length(col1) |
+-------+-------------------+
| hi | 2 |
| hi | 5 |
+-------+-------------------+
2 rows in set (0.03 sec)
mysql> insert into t1 values('hi ');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'col1' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)
mysql> set sql_mode="traditional";
Query OK, 0 rows affected (0.00 sec)
mysql> create table t1 (col1 varchar(5));
Query OK, 0 rows affected (0.26 sec)
mysql> insert into t1 values ('hi');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values ('hi ');
Query OK, 1 row affected (0.00 sec)
mysql> select col1,char_length(col1) from t1;
+-------+-------------------+
| col1 | char_length(col1) |
+-------+-------------------+
| hi | 2 |
| hi | 5 |
+-------+-------------------+
2 rows in set (0.01 sec)
mysql> insert into t1 values('hi ');
ERROR 1265 (01000): Data truncated for column 'col1' at row 1
[6 Jan 2005 12:01]
Sergei Golubchik
Looks like it is fixed already ?
[11 Jan 2005 13:20]
Alexander Barkov
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Additional info:
Fixed in 5.0.3
Description: When a string with trailing spaces is assigned to a VARCHAR column, the trailing spaces should not be trimmed off unless they make the string too long for the target. That is: (a) If a column is VARCHAR(5) and the string is '12345 ', then we can assign '12345' to the column, trimming the extra spaces. (b) If a column is VARCHAR(5) and the string is '123 ' (there are 2 spaces), then we must assign '123 ' to the column, without space trimming. (c) If a column is VARCHAR(5) and the string is '123 ' (there are 4 spaces), then we must assign '123 ' to the column, trimming only those spaces which make the string too long for the column. MySQL is not always following these rules. How to repeat: mysql> create table t1 (col1 varchar(5)); mysql> insert into t1 values ('hi'); mysql> insert into t1 values ('hi '); mysql> select col1,char_length(col1) from t1; +-------+-------------------+ | col1 | char_length(col1) | +-------+-------------------+ | hi | 2 | | hi | 5 | +-------+-------------------+ 2 rows in set (0.00 sec) -- This is the correct result; the trailing spaces were kept on the 2nd insert. mysql> insert into t1 values('hi '); ERROR 1265 (01000): Data truncated for column 'col1' at row 1 -- This is the incorrect result; it is safe to cut any trailing spaces that will make the string too long for the column.