MySQL Bugs: #47875: Using certain double values in where clause sometimes does not work.

Bug #47875	Using certain double values in where clause sometimes does not work.
Submitted:	6 Oct 2009 20:07	Modified:	7 Oct 2009 7:40
Reporter:	Hal Lind	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1.5	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	double

Description:
Using certain double values in a WHERE clause when finding on exact values sometimes does not work. 

This only appears to happen when using ADO.UPDATE Method to update/create the record (or linked tables in MS Access). Using .Execute method with INSERT/UPDATE works OK.

This is important for us since we use DOUBLES as unique key fields.

How to repeat:
See attached .vbs sample

Double in where clause problem

Attachment: double_problem.vbs (, text), 1.72 KiB.

Hi Hal and thanks for your report.

If you check bugsdb you'll find many many similar ones. Point is in how ADO does updates so please read carefully
http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q190727&GSSNB=1:

You can check that this is a problem by looking into general query log on MySQL server which should show all fields being part of update statement (created by ADO).

To fix this behavior, try code like this:
Dim rs
Set rs = CreateObject("ADODB.Recordset")
With rs
    .ActiveConnection = cnxDatabase
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .CursorType = adOpenForwardOnly
    .Properties("Update Criteria").Value = 0 <<<<<
    .Open(strSQL)
    .Update "Field", "NewValue"
End With

If you do not know why comparing double values lead to problems, please check MySQL manual too.

From Bug#14302:
 [24 Nov 2005 19:29] Sergei Golubchik

This is not a bug. Direct double=double comparison isn't expected to work anyway.