MySQL Bugs: #7083: CHAR columns should report correct length

Bug #7083	CHAR columns should report correct length
Submitted:	7 Dec 2004 20:30	Modified:	8 Jan 2014 14:38
Reporter:	Trudy Pelzer	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0.3-alpha-debug	OS:	Linux (SuSE 9.1)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When a string that is shorter than the defined length 
of a CHAR column is assigned to that column, the 
string should be right-padded with spaces, to make 
it the full length. CHAR_LENGTH should then report 
this length; that is, the CHAR_LENGTH of a column 
defined as CHAR(5) should always return "5". But  
MySQL is not right-padding, as required by the SQL 
standard. 
 
Note: This item is one of the problems noted on 
the infamous "gotchas" list. 

How to repeat:
mysql> set sql_mode='ansi,traditional'; 
 
mysql> CREATE TABLE t1 (col1 CHAR(5), col2 VARCHAR(5)); 
mysql> INSERT INTO t1 VALUES ('hello','hello'); 
mysql> INSERT INTO t1 VALUES ('hi','hi'); 
 
mysql> SELECT char_length(col1), char_length(col2) FROM t1 WHERE col1='hello'; 
+---------------------+----------------------+ 
| char_length(col1) | char_length(col2) |  
+---------------------+----------------------+ 
|                 5              |                               5 | 
+---------------------+----------------------+ 
2 rows in set (0.00 sec) 
-- This is the correct response. 
 
mysql> SELECT char_length(col1), char_length(col2) FROM t1 WHERE col1='hi'; 
+---------------------+----------------------+ 
| char_length(col1) | char_length(col2) |  
+---------------------+----------------------+ 
|                 2              |                               2 | 
+---------------------+----------------------+ 
2 rows in set (0.00 sec) 
-- This is the incorrect response. The CHAR_LENGTH 
of col1 should be 5, not 2.

Reassigning to Sergey Glukhov, due to leaving to UC.

This is not a bug in CHAR_LENGTH() but a consequence of how CHAR columns are returned.
In MySQL we automaticly strip of all end space from CHAR columns as the end spare are not signficant anyway. (In MySQL 5.0 we don't do this for VARCHAR columns as for these end space are significant)

We can't change this in 5.0 becasue of the following reasons:

-  This would case a notable problem for current users as they have to add TRIM() around all character columns to get the current behaviour.
- Having to use TRIM would be slow down things for normal users
- LIKE expressions for CHAR columns would have to be rewritten to take into account space
  expansion
- It would make MySQL notable slower for CHAR columns as we would have to padd all CHAR strings with end space when sending to the client.

- In 5.1 we can probably add a mode for not automaticly striping of end space for CHAR columns to satisfy those users that would need this for compability reasons.

Any idea when on 5.1 this would be fixed? We're unable to use MySQL until this issue is solved.

Setting this to 'verified' as we are discontinuing the use of 'to be fixed later'.

The same behaviour is still there on mysql-trunk.

Frankly, I don't see how CHAR_LENGTH() behaviour contradicts the documentation. In my opinion this is the real bug here: if documentation doesn't specify a behaviour for CHAR, different users may expect different behaviours, based on their interpretation.

By the way, behaviour and documentation ambiguity are still in 8.0.3.