Description:
Hi,
Have observed that after inserting a record(string) in the VARCHAR data type with a trailing white space, the server fails to recognize the underlying white space when we do a select with this string in the where clause
How to repeat:
please see the below example:
1. Create table:
mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| descr | varchar(10) | YES | | NULL | |
| sal | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
2. Insert Data:
mysql> insert into test values(10,'HR',10000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(10,'HR ',10000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(10,' HR ',10000);
Query OK, 1 row affected (0.00 sec)
3. Query as a whole:
mysql> select * from test;
+------+-------+-------+
| id | descr | sal |
+------+-------+-------+
| 10 | HR | 10000 |
| 10 | HR | 10000 |
| 10 | HR | 10000 |
+------+-------+-------+
3 rows in set (0.00 sec)
--> Observe that leading white space is being recognized...
4. select with where clause
4.1 Leading white space
mysql> select * from test where descr=' HR';
+------+-------+-------+
| id | descr | sal |
+------+-------+-------+
| 10 | HR | 10000 |
+------+-------+-------+
1 row in set (0.00 sec)
4.2 Exact String
mysql> select * from test where descr='HR';
+------+-------+-------+
| id | descr | sal |
+------+-------+-------+
| 10 | HR | 10000 |
| 10 | HR | 10000 |
+------+-------+-------+
2 rows in set (0.00 sec)
--> Was expecting 1 row
4.3 Training white space
mysql> select * from test where descr='HR ';
+------+-------+-------+
| id | descr | sal |
+------+-------+-------+
| 10 | HR | 10000 |
| 10 | HR | 10000 |
+------+-------+-------+
2 rows in set (0.00 sec)
--> Again was expecting 1 row.
Suggested fix:
None
