Description:
The following was filed as Debian bug report and is available here:
http://bugs.debian.org/211618
There is a different result when prepending a string with a space in a specific situation. See How to repeat below.
-christian-
How to repeat:
From: David Fries <dfries@mail.win.org>
See Debian bug report if the formatting gets screwed up.
Here is a bug I just stumbled accross. This is based off a real
(world) example. Create a table with two columns, one of them an
index, the other a text field. Insert 'Chevy', 'Chevy ', and 'Ford'.
Start doing some select queries. If you look for any one item you get
what you expect, if you search for both Chevy values you only get the
first one you listed. If you search for all three or both Chevy's and
something not in the table you get both Chevy rows and the Ford if it was
listed.
For correct operation I expect when I put an 'or' in the where statement
it would find all rows that match at least one of the conditions, this
is not happening, one of the rows is getting lost, some of the time.
mysql> create table makes ( make_id integer primary key auto_increment, make_desc text not null, unique index make_desc_index
+(make_desc (20)));
Query OK, 0 rows affected (0.08 sec)
mysql> insert into makes (make_desc) values ('Chevy');
Query OK, 1 row affected (0.02 sec)
mysql> insert into makes (make_desc) values ('Chevy ');
Query OK, 1 row affected (0.00 sec)
mysql> select * from makes where make_desc='Chevy';
+---------+-----------+
| make_id | make_desc |
+---------+-----------+
| 1 | Chevy |
+---------+-----------+
1 row in set (0.05 sec)
mysql> select * from makes where make_desc='Chevy ';
+---------+-----------+
| make_id | make_desc |
+---------+-----------+
| 2 | Chevy |
+---------+-----------+
1 row in set (0.00 sec)
mysql> select * from makes where make_desc='Chevy ' or make_desc='Chevy';
+---------+-----------+
| make_id | make_desc |
+---------+-----------+
| 2 | Chevy |
+---------+-----------+
1 row in set (0.00 sec)
mysql> select * from makes where make_desc='Chevy' or make_desc='Chevy ';
+---------+-----------+
| make_id | make_desc |
+---------+-----------+
| 1 | Chevy |
+---------+-----------+
1 row in set (0.00 sec)
mysql> select * from makes where make_id='1' or make_id='2';
+---------+-----------+
| make_id | make_desc |
+---------+-----------+
| 1 | Chevy |
| 2 | Chevy |
+---------+-----------+
2 rows in set (0.04 sec)
mysql> insert into makes (make_desc) values('Ford');
Query OK, 1 row affected (0.00 sec)
mysql> select * from makes where make_desc='Chevy' or make_desc='Chevy ' or make_desc='Ford';
+---------+-----------+
| make_id | make_desc |
+---------+-----------+
| 1 | Chevy |
| 2 | Chevy |
| 3 | Ford |
+---------+-----------+
3 rows in set (0.02 sec)
---------------- the relevant two queries -----------------
------------- take special note on the spaces before(!) and after the words ---
mysql> select * from makes where make_desc='Chevy' or make_desc='Chevy ';
+---------+-----------+
| make_id | make_desc |
+---------+-----------+
| 1 | Chevy |
+---------+-----------+
1 row in set (0.00 sec)
mysql> select * from makes where make_desc='Chevy' or make_desc='Chevy ' or make_desc=' Chevy';
+---------+-----------+
| make_id | make_desc |
+---------+-----------+
| 1 | Chevy |
| 2 | Chevy |
+---------+-----------+
2 rows in set (0.00 sec)
Suggested fix:
none
