Trailing spaces in TEXT columns

Hi,

Please take a look at the following script:

CREATE TABLE test (str TEXT);
INSERT INTO test VALUES ("hello"), ("hello ");
SELECT str,length(str) FROM test WHERE str="hello";

Since this is a TEXT column, the last SELECT should return one row (the one
without a trailing space). Instead, it returns both rows:
+--------+-------------+
| str | length(str) |
+--------+-------------+
| hello | 5 |
| hello | 6 |
+--------+-------------+
2 rows in set (0.00 sec)

I didn't find anything that mentions this in the documentation. All it says is
that "There is no trailing-space removal for BLOB and TEXT columns when values
are stored or retrieved.", so it looks like a bug to me. Also, this doesn't
happen with BLOB columns.

I've tested it in mysql 4.0.21 on Windows 2000 (didn't see anything relevant
mentioned in the list of changes for 4.0, so I assume it's still there in 4.0.23).

Thanks for your help,

Netta


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Trailing spaces in TEXT columns

Hi!

On Feb 02, lists@spymac.com wrote:
> Hi,
>
> Please take a look at the following script:
>
> CREATE TABLE test (str TEXT);
> INSERT INTO test VALUES ("hello"), ("hello ");
> SELECT str,length(str) FROM test WHERE str="hello";
>
> Since this is a TEXT column, the last SELECT should return one row (the one
> without a trailing space). Instead, it returns both rows:
> +--------+-------------+
> | str | length(str) |
> +--------+-------------+
> | hello | 5 |
> | hello | 6 |
> +--------+-------------+
> 2 rows in set (0.00 sec)
>
> I didn't find anything that mentions this in the documentation. All it says is
> that "There is no trailing-space removal for BLOB and TEXT columns when values
> are stored or retrieved.", so it looks like a bug to me. Also, this doesn't
> happen with BLOB columns.

it is removed on comparison (the value you compare to is considered
CHAR).

> I've tested it in mysql 4.0.21 on Windows 2000 (didn't see anything relevant
> mentioned in the list of changes for 4.0, so I assume it's still there in 4.0.23).

Regards,
Sergei

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/ www.mysql.com

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org