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

[6 Oct 2009 20:07] Hal Lind
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
[6 Oct 2009 20:07] Hal Lind
Double in where clause problem

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

[6 Oct 2009 20:09] Hal Lind
.
[7 Oct 2009 7:40] Tonci Grgin
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.
[7 Oct 2009 7:42] Tonci Grgin
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.