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

[4 Jan 2007 23:35] Peter Gulutzan
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)
[4 Jan 2007 23:52] MySQL Verification Team
Thank you for the bug report. Verified on FC 6.0 (5.0.XX also affected).
[16 Nov 2007 6:15] Lu Jingdong
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
[19 Nov 2007 14:48] Peter Gulutzan
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
[16 Dec 2007 16:49] Bugs System
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.
[31 Jan 2008 17:04] Bugs System
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.
[13 Mar 2008 19:27] Bugs System
Pushed into 6.0.5-alpha
[13 Mar 2008 19:35] Bugs System
Pushed into 5.1.24-rc
[15 Mar 2008 20:04] Jon Stephens
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-.
[30 Mar 2008 0:26] Jon Stephens
Fix also noted for 5.1.23-ndb-6.3.11.