469,588 Members | 2,809 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,588 developers. It's quick & easy.

collation problems with utf8

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
Jul 20 '05 #1
0 1199

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by DH | last post: by
1 post views Thread by Bob Bedford | last post: by
reply views Thread by Temba | last post: by
2 posts views Thread by Andrew Butchart | last post: by
2 posts views Thread by Marco | last post: by
reply views Thread by polinaskulski | last post: by
27 posts views Thread by Nasir | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.