MySQL Bugs: #18123: Problem in declaring FLOAT/DOUBLE fields as PRIMARY KEY

Bug #18123	Problem in declaring FLOAT/DOUBLE fields as PRIMARY KEY
Submitted:	10 Mar 2006 8:51	Modified:	27 Apr 2007 18:21
Reporter:	Grace Coronado	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51.12	OS:	Windows (MS XP)
Assigned to:		CPU Architecture:	Any
Tags:	ODBC5-RC

Description:

If one of the primary keys is declared as float or double, some records in the table will be displayed as ‘#DELETED’, when viewed from MS Access (whether table view or through form).

Our current settings:

Windows XP 5.1
MS Access 2002
MySQL Server 5.0.4
MyODBC 3.51.12 
MS Jet Engine 4.0

How to repeat:

Create ‘testtab’ table as follows:

CREATE TABLE `testtab` (
  `Col1` float NOT NULL,
  `Col2` varchar(10) default NULL,
  PRIMARY KEY  (`Col1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Enter the following values for Col1 and Co2:
    (1.11, Row1)
    (2.22, Row2)
    (3.333, Row3)
    (4.4444, Row4)

Or, enter any test data with decimal points for Col1.

The data will be viewed without any problem from MySQL only when viewed as linked table from MS Access

Suggested fix:

We have tried changing the float declaration to:

float(15,5)     -> to define length and decimal places; didn't work
double(15,5)  -> didn't work
decimal(15,5)  - > truncates decimal places 

Other links:

http://www.codekabinett.com/page.php?Theme=4&Lang=2

Thanks for your bug report. Verified as described by user:
DROP TABLE IF EXISTS `testtab`;
CREATE TABLE `testtab` (
  `Col1` float NOT NULL,
  `Col2` varchar(10) default NULL,
PRIMARY KEY (`Col1`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `testtab` values (1.11, "Row1"), (2.22, "Row2"), (3.333, "Row3"), (4.4444, "Row4");

MS Access, GetExternalData (System DSN and File DSN behave the same), if tables are imported there's no error. If tables are Linked first two rows appear with text #DELETED in both fields.
MyODBC 3.51.12, MS Access 2003, WinXP SP2.

Is there any way to workaround this problem (e.g., adding timestamp field)?  Or will it be solved in future release of MyODBC?

Thanks for your reply.

Right now I am not sure why this happens. We'll make a note here when we learn more.

This is a duplicate of Bug #13540.

(DOUBLE or FLOAT as a primary key is a bad idea, because these are inexact numeric types.)