Bug #11741 Cannot index a 0-length blob prefix

• Submitted: 5 Jul 2005 18:23
• Modified: 17 Dec 2010 13:22
• Reporter: Frederic Briere
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S4 (Feature request)
• Version: 4.0.24_Debian-10
• OS: Linux (Linux)
• CPU Architecture: Any

[5 Jul 2005 18:23] Frederic Briere

Description:
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. :)