Bug #11741 Cannot index a 0-length blob prefix
Submitted: 5 Jul 2005 18:23 Modified: 17 Dec 2010 13:22
Reporter: Frederic Briere Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version:4.0.24_Debian-10 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[5 Jul 2005 18:23] Frederic Briere
Attempts to index a blob/text column via a 0-length prefix are rejected by the server with error 1170, as if no prefix had been provided.  Aside from this obvious documentation-omission-slash-misleading-error bug, I'd like to actually request the ability to index empty prefixes, as I have a text column where I'm only interested in indexing its null status, and not its contents.

How to repeat:
mysql> create table tmp (id int, error text);
Query OK, 0 rows affected (0.04 sec)

mysql> alter table tmp add index (id, error(0));
ERROR 1170: BLOB column 'error' used in key specification without a key length

Suggested fix:
Short term: return a more appropriate error message (1089 maybe?)

Long term: do as I asked. :)