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:
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:3.51.12 OS:Microsoft Windows (MS XP)
Assigned to: CPU Architecture:Any
Tags: ODBC5-RC

[10 Mar 2006 8:51] Grace Coronado

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`)

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:

[24 Mar 2006 8:42] Tonci Grgin
Thanks for your bug report. Verified as described by user:
CREATE TABLE `testtab` (
  `Col1` float NOT NULL,
  `Col2` varchar(10) default NULL,
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.
[30 Mar 2006 6:12] Grace Coronado
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.
[30 Mar 2006 13:41] Tonci Grgin
Right now I am not sure why this happens. We'll make a note here when we learn more.
[27 Apr 2007 18:21] Jim Winstead
This is a duplicate of Bug #13540.

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