Bug #24820 CREATE INDEX ....USING HASH on NDB table creates ordered index, not HASH index
Submitted: 5 Dec 2006 13:27 Modified: 25 Jan 2007 4:23
Reporter: Roland Bouman Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:5.1.14, 5.0 OS:Linux (ubuntu edgy)
Assigned to: Martin Skold
Tags: hash, INDEX, ndb, ordered

[5 Dec 2006 13:27] Roland Bouman
Description:
Creating a non-unique index with the using hash clause silently creates an ordered index. 

How to repeat:
mysql> use test;                                                                             
Database changed
mysql> show create table nationaldish;
ERROR 1146 (42S02): Table 'test.nationaldish' doesn't exist
mysql> CREATE TABLE nationaldish (
    ->   DishID int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   CountryCode char(3) NOT NULL,
    ->   DishTitle varchar(64) NOT NULL,
    ->   Recipe text,
    ->   calories smallint(5) unsigned DEFAULT NULL,
    ->   PRIMARY KEY (DishID)
    -> ) ENGINE=ndbcluster;
Query OK, 0 rows affected (1.07 sec)

mysql> create index i on nationaldish(countrycode,calories) using hash;
Query OK, 0 rows affected (2.67 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> exit
Bye
roland@Rolenove:/opt/mysql/mysql$ bin/ndb_desc -d test nationaldish
-- nationaldish --
Version: 16777217
Fragment type: 5
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 5
Number of primary keys: 1
Length of frm data: 360
Row Checksum: 1
Row GCI: 1
TableStatus: Retrieved
-- Attributes -- 
DishID Unsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
CountryCode Char(3;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY
DishTitle Varchar(64;latin1_swedish_ci) NOT NULL AT=SHORT_VAR ST=MEMORY
Recipe Text(256,2000;16;latin1_swedish_ci) NULL AT=FIXED ST=MEMORY
calories Smallunsigned NULL AT=FIXED ST=MEMORY

-- Indexes -- 
PRIMARY KEY(DishID) - UniqueHashIndex
PRIMARY(DishID) - OrderedIndex
i(CountryCode, calories) - OrderedIndex

NDBT_ProgramExit: 0 - OK

Suggested fix:
not sure, on of these:
-create a hash index, not an ordered one
-generate a warning or error
[6 Dec 2006 14:01] Hartmut Holzgraefe
USING HASH only works with UNIQUE indexes, you can't have a hash index on something that allows duplicates.

It should generate a warning though.
[8 Jan 2007 9:39] 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/17711

ChangeSet@1.2293, 2007-01-08 10:38:53+01:00, mskold@mysql.com +3 -0
  bug#24820  CREATE INDEX ....USING HASH on NDB table creates ordered index, not HASH index: Added error checking
[8 Jan 2007 10:42] 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/17717

ChangeSet@1.2382, 2007-01-08 11:42:32+01:00, mskold@mysql.com +3 -0
  bug#24820  CREATE INDEX ....USING HASH on NDB table creates ordered index, not HASH index: Added error checking
[8 Jan 2007 12:53] 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/17726

ChangeSet@1.2294, 2007-01-08 13:53:37+01:00, mskold@mysql.com +2 -0
  bug#24820  CREATE INDEX ....USING HASH on NDB table creates ordered index, not HASH index: Changed test since error mesage wasn't predictable
[8 Jan 2007 13:20] 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/17729

ChangeSet@1.2385, 2007-01-08 14:20:35+01:00, mskold@mysql.com +2 -0
  bug#24820  CREATE INDEX ....USING HASH on NDB table creates ordered index, not HASH index: Merged up test 5.0 case
[24 Jan 2007 1:55] Tomas Ulin
pushed to 5.1.15
[24 Jan 2007 7:52] Tomas Ulin
pushed to 5.0.36
[25 Jan 2007 4:23] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.36 and 5.1.15 changelogs.