Bug #2750 select right(foo, 1) from varchar column returns right most-non space

• Submitted: 12 Feb 2004 8:40
• Modified: 22 Mar 2004 10:02
• Reporter: Keith Gabryelski
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.1a-alpha-nt
• OS: Windows (xp)
• Assigned to: Paul DuBois
• CPU Architecture: Any

[12 Feb 2004 8:40] Keith Gabryelski

Description:
right('x ', 1) returns something different than right(column, 1) where column is 'x '.

How to repeat:
Witness:

mysql> select right('a ', 1);
+----------------+
| right('x ', 1) |
+----------------+
|                |
+----------------+
1 row in set (0.00 sec)

mysql> create table temp ( str varchar(100) );
Query OK, 0 rows affected (0.13 sec)

mysql> insert into temp (str) values ('x ');
Query OK, 1 row affected (0.01 sec)

mysql> select right(str, 1) from temp;
+---------------+
| right(str, 1) |
+---------------+
| x             |
+---------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 4.1.1a-alpha-nt |
+-----------------+
1 row in set (0.00 sec)

mysql>

Suggested fix:
1) right(column, 1) where there is trailing space should return ' '.
2) AND/OR right('x ', 1) return the same thing as right(column, 1) where column is 'x '.
3) OR (if I am mistaken that this is a feature) it should be documented.

[16 Feb 2004 9:24] Brian Aker

The behavior is correct by current design. In one example you have a fixed string and in the other you 
have a varchar (which is packed).

[22 Mar 2004 8:23] Paul DuBois

Trailing spaces are removed when values are stored in a VARCHAR
column.  This is documented at:

http://www.mysql.com/doc/en/Column_types.html

and:

http://www.mysql.com/doc/en/CHAR.html