I'm having a problem with something that seems like it should be super-easy,
and yet is making me crazy. I have a simple table "A", with one column,
"C". There are two rows in this table, with values "FOO" and "foo". And I
have an index on C. Now, I perform the following query:
select * from A where C="FOO" or C="foo";
I only get back one row:
+---------+
| C +
+---------+
| foo +
+---------+
The problem doesn't occur if I get rid of the index. Here's the actual
table description (the default character set and collation of the database
are also utf8 and utf8_bin, respectively):
create table A (
C varchar(255) character set utf8 collate utf8_bin,
index(C)
) type=INNODB default character set utf8 collate utf8_bin;
insert into A values ("FOO"),("foo");
The following query works (i.e. returns both rows), but bypasses the index
(which is a problem when working with the real table, which has hundreds of
thousands of entries):
select * from A where C collate utf8_bin = "FOO" or C collate utf8_bin =
"foo";
Is this a bug? Am I messing up my set up? Giving a bad query? Or is this
the way indices are supposed to work? It seems like it should be easy, but
for some reason the index seems to be interfering with things.
Daniel