MySQL Bugs: #25426: Prefix index on DECIMAL column causes warning

Bug #25426	Prefix index on DECIMAL column causes warning
Submitted:	4 Jan 2007 23:35	Modified:	15 Mar 2008 20:04
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.14-beta-debug/5.0BK	OS:	Linux (SUSE 10.0 / 64-bit)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
I create a prefix index, i.e.
"CREATE INDEX index_name ON table_name (column_name(prefix_length))"
on a DECIMAL column.

I get a warning message about a "wrong key definition".

The content of the warning message is (I think) inappropriate,
because the prefix length is ignored anyway.

How to repeat:
mysql> create table t8 (s1 decimal(40));
Query OK, 0 rows affected (0.06 sec)

mysql> create index i8 on t8 (s1(5));
Query OK, 0 rows affected, 2 warnings (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                           |
+-------+------+---------------------------------------------------------------------------------------------------+
| Error | 1194 | Found wrong key definition in #sql-31ca_2; Please do "ALTER TABLE '#sql-31ca_2' FORCE" to fix it! |
| Error | 1194 | Found wrong key definition in #sql-31ca_2; Please do "ALTER TABLE '#sql-31ca_2' FORCE" to fix it! |
+-------+------+---------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Thank you for the bug report. Verified on FC 6.0 (5.0.XX also affected).

I tried to repeat it, but the result is different.

mysql> select version();
+-----------------------+
| version()             |
+-----------------------+
| 5.1.21-beta-debug-log |
+-----------------------+
1 row in set (0.00 sec)

mysql> create table t8(s1 decimal(40));
Query OK, 0 rows affected (0.06 sec)

mysql> create index i8 on t8 (s1(5));
ERROR 1089 (HY000): Incorrect sub part key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique sub keys

I confirm that the message is different now.

This is an improvement, but "sub part" is hard to
understand. Subpartition? Subordinate Particle?

If an abbreviation is little known or ambiguous,
it should not be in a public error message.

I used the word "prefix". So does the MySQL manual:
http://dev.mysql.com/doc/refman/5.1/en/indexes.html

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40067

ChangeSet@1.2646, 2007-12-16 20:42:34+04:00, holyfoot@mysql.com +2 -0
  Bug #25426 Prefix index on DECIMAL column causes warning.
  
  Error message modified to be consistent with the manual.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/41513

ChangeSet@1.2646, 2008-01-31 15:56:02+04:00, holyfoot@mysql.com +2 -0
  Bug #25426 Prefix index on DECIMAL column causes warning.
  
  Error message modified to be consistent with the manual.

Pushed into 6.0.5-alpha

Pushed into 5.1.24-rc

Documented bugfix in the 5.1.24 and 6.0.5 changelogs as follows:

        Attempting to create an index with a prefix on a DECIMAL column appeared
        to succeed with an inaccurate warning message. Now, this action fails
        with the error -Incorrect prefix key; the used key part isn't a string,
        the used length is longer than the key part, or the storage engine
        doesn't support unique prefix keys-.

Fix also noted for 5.1.23-ndb-6.3.11.