Description:
When trailing space is included in string binary comparison, access using primary key ignore the trailing space while simple table scan do not.
For documentation, trailing space should not be ignored.
Following is the output from mysql
C:\MySQL_4.0.16\bin>mysql -u root -h LX006APP
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1756 to server version: 4.0.20-Max-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test
Database changed
mysql> select * from project_team where project_id = 'FMS ';
+------------+----------+--------------+
| project_id | staff_id | project_role |
+------------+----------+--------------+
| FMS | 123 | PL |
| FMS | 234 | AP |
+------------+----------+--------------+
2 rows in set (0.00 sec)
mysql> select * from project_team ignore index (PRIMARY) where project_id = 'FMS
';
Empty set (0.00 sec)
mysql>
The result is not dependent on the table type (same result using INNODB or MYISAM)
How to repeat:
Execute following SQL:
use test;
create table project_team (
project_id char(5) binary not null,
staff_id int(11) not null,
project_role char(2) binary not null,
primary key (project_id,staff_id) )
Type = INNODB;
insert project_team values('FMS',123,'PL');
insert project_team values('FMS',234,'AP');
select * from project_team where project_id = 'FMS ';
select * from project_team ignore index (PRIMARY) where project_id = 'FMS ';
Description: When trailing space is included in string binary comparison, access using primary key ignore the trailing space while simple table scan do not. For documentation, trailing space should not be ignored. Following is the output from mysql C:\MySQL_4.0.16\bin>mysql -u root -h LX006APP Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1756 to server version: 4.0.20-Max-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use test Database changed mysql> select * from project_team where project_id = 'FMS '; +------------+----------+--------------+ | project_id | staff_id | project_role | +------------+----------+--------------+ | FMS | 123 | PL | | FMS | 234 | AP | +------------+----------+--------------+ 2 rows in set (0.00 sec) mysql> select * from project_team ignore index (PRIMARY) where project_id = 'FMS '; Empty set (0.00 sec) mysql> The result is not dependent on the table type (same result using INNODB or MYISAM) How to repeat: Execute following SQL: use test; create table project_team ( project_id char(5) binary not null, staff_id int(11) not null, project_role char(2) binary not null, primary key (project_id,staff_id) ) Type = INNODB; insert project_team values('FMS',123,'PL'); insert project_team values('FMS',234,'AP'); select * from project_team where project_id = 'FMS '; select * from project_team ignore index (PRIMARY) where project_id = 'FMS ';