Re: Partly non-unique key on varchar field seems faulty.

[Thomas Kettenbach]

Okay, i tested the bug # 14980, it it worked properly. No problem here.

MySQL Version is: 4.1.10a

I also tested my problem again, and i guess it is a problem of the length
of the index and the fulltext search.

What i tried for now was the following:

create table textwith4charindex (
name varchar(64) not null default "",
index idx ( name(4))
) character set=utf8;

Then i loaded the table with the words from an english dictionary with about 46.000
words. I did the following SQL statements:

--------------
LOAD DATA LOCAL INFILE "/tmp/wordlist-utf8.txt" INTO TABLE textwith4charindex
--------------

Query OK, 46147 rows affected (0,60 sec)
Records: 46147 Deleted: 0 Skipped: 0 Warnings: 0


--------------
SELECT name,count(*) AS CNT FROM textwith4charindex GROUP BY NAME HAVING CNT!=1
--------------

+---------------------+-----+
| name | CNT |
+---------------------+-----+
| a | 3 |
| Ac | 2 |
... snip ...

The following query shows my mistake:
mysql> select * from textwith4charindex where name="a";
--------------
select * from textwith4charindex where name="a"
--------------

+------+
| name |
+------+
| a |
| A |
| à |
+------+
3 rows in set (0,00 sec)

So MySQL's ansers seems to be perfectly right.
Sorry for any inconvenience.