MySQL Bugs: #20064: Integer values inserted into a double field through MSAccess are not selectable

Bug #20064	Integer values inserted into a double field through MSAccess are not selectable
Submitted:	25 May 2006 9:39	Modified:	26 May 2006 9:49
Reporter:	Stefano Sapienti	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.09 >	OS:	Windows (Windows2000/XP)
Assigned to:		CPU Architecture:	Any

Description:
Inserting an integer value (such as 200 or 54678) into a double field using the MSAccess window that show linked table data result into a non selectable value cause is converted into a decimal value. 
Some numbers, like 10 or 99 works fine, inserting the number using MySQL QueryBrowser works fine so I think it's an ODBC problem and not the famous float problem.

I apologize if it's a known problem but I haven't found it.

How to repeat:
Create a table in mysql containing a Double type field.
Link it in MSAccess using a MyODBC DSN.
Double click on the table to open it.
Insert a new row or modify an existing rows inserting 117 in the double field.
Create a new query that selects the table where double field=117
It returns 0 records.
Open MySQL Query Browser and connect to the database (without using ODBC)
Launch an "UPDATE table SET field=117 WHERE condition" command
Return to MSAccess and refresh the query.
It returns 1 (or more) records.

I tested integer values but it seems to be the same thing for decimal value.

I tested it with Mysql 4.1.10 and MyODBC 3.51.09 and 3.51.12

Hi Stefano. Thanks for your problem report.
Sorry I was unable to repeat it.

Environment:
MySQL server 4.1.18-nt-max-log
MyODBC 3.51.12 - standard
WIN XP SP2
Access v11.0 build 6566 (Access 2003)
Jet 2.x msrd2x35.dll v. 3.51.0623.0 (From MS Access About box)

Server log file:
MySql, Version: 4.1.18-nt-max-log. started with:
TCP Port: 3306, Named Pipe: MySQL
Time                 Id Command    Argument
060525 21:11:30	      1 Connect     root@localhost on 
		      1 Query       SET AUTOCOMMIT=0
060525 21:11:31	      1 Query       SET AUTOCOMMIT=1
		      1 Query       SHOW DATABASES
060525 21:11:55	      1 Query       USE test
		      1 Query       SHOW TABLES
060525 21:13:19	      1 Query       create table test20064 (  ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  DblFld DOUBLE (10,4)  )
		      1 Query       USE test
060525 21:17:14	      2 Connect     root@localhost on test
		      2 Quit       
060525 21:20:59	      3 Connect     root@localhost on test
		      3 Query       SELECT Config, nValue FROM MSysConf
		      3 Query       show tables
		      3 Query       SHOW TABLES FROM `mysql` like '%'
060525 21:21:04	      3 Field List  test20064 
		      3 Field List  test20064 
		      3 Query       SHOW KEYS FROM `test20064`
060525 21:21:05	      3 Query       SELECT `ID` ,`DblFld`  FROM `test20064`
		      3 Query       SET AUTOCOMMIT=0
		      3 Query       COMMIT
		      3 Query       SET AUTOCOMMIT=1
060525 21:26:47	      3 Quit       
060525 21:28:23	      4 Connect     root@localhost on test
		      4 Query       SELECT Config, nValue FROM MSysConf
		      4 Query       show tables
		      4 Query       SHOW TABLES FROM `mysql` like '%'
060525 21:28:33	      4 Field List  test20064 
		      4 Field List  test20064 
		      4 Query       SHOW KEYS FROM `test20064`
060525 21:28:38	      4 Query       SELECT `test20064`.`ID` FROM `test20064`
060525 21:28:41	      4 Query       SET AUTOCOMMIT=0
		      4 Query       INSERT INTO  `test20064`  (`DblFld`) VALUES (1.17000000000000000e+002)
		      4 Query       COMMIT
		      4 Query       SET AUTOCOMMIT=1
		      4 Query       SELECT `ID`,`DblFld`  FROM `test20064`  WHERE `ID` IS NULL
		      4 Query       SELECT `ID`,`DblFld`  FROM `test20064`  WHERE `ID` IS NULL
060525 21:29:04	      4 Query       SELECT `test20064`.`ID` FROM `test20064` WHERE (`DblFld` = 117 )
		      4 Query       SELECT `ID`,`DblFld`  FROM `test20064`  WHERE `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1
060525 21:30:27	      4 Query       SET AUTOCOMMIT=0
		      4 Query       INSERT INTO  `test20064`  (`DblFld`) VALUES (2.00000000000000000e+002)
		      4 Query       COMMIT
		      4 Query       SET AUTOCOMMIT=1
		      4 Query       SELECT `ID`,`DblFld`  FROM `test20064`  WHERE `ID` IS NULL
		      4 Query       SELECT `ID`,`DblFld`  FROM `test20064`  WHERE `ID` IS NULL
		      4 Query       SELECT `ID`,`DblFld`  FROM `test20064`  WHERE `ID` IS NULL
060525 21:30:38	      4 Query       SET AUTOCOMMIT=0
		      4 Query       INSERT INTO  `test20064`  (`DblFld`) VALUES (5.46780000000000000e+004)
		      4 Query       COMMIT
		      4 Query       SET AUTOCOMMIT=1
		      4 Query       SELECT `ID`,`DblFld`  FROM `test20064`  WHERE `ID` IS NULL
		      4 Query       SELECT `ID`,`DblFld`  FROM `test20064`  WHERE `ID` IS NULL
		      4 Query       SELECT `ID`,`DblFld`  FROM `test20064`  WHERE `ID` IS NULL
060525 21:30:42	      4 Query       SELECT `test20064`.`ID` FROM `test20064` WHERE (`DblFld` = 117 )
		      4 Query       SELECT `ID`,`DblFld`  FROM `test20064`  WHERE `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1
060525 21:30:53	      4 Query       SELECT `test20064`.`ID` FROM `test20064` WHERE (`DblFld` = 200 )
		      4 Query       SELECT `ID`,`DblFld`  FROM `test20064`  WHERE `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2
060525 21:31:12	      4 Query       SELECT `test20064`.`ID` FROM `test20064`
		      4 Query       SELECT `ID`,`DblFld`  FROM `test20064`  WHERE `ID` = 1 OR `ID` = 2 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3
060525 21:32:21	      4 Query       SELECT `test20064`.`ID` FROM `test20064` WHERE (`DblFld` = 200 )
		      4 Query       SELECT `ID`,`DblFld`  FROM `test20064`  WHERE `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2
060525 21:32:24	      4 Query       SELECT `test20064`.`ID` FROM `test20064` WHERE (`DblFld` = 200 )
		      4 Query       SELECT `ID`,`DblFld`  FROM `test20064`  WHERE `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2 OR `ID` = 2
060525 21:32:57	      1 Query       SELECT * FROM test20064
		      1 Query       SELECT * FROM test20064
		      1 Query       SHOW COLUMNS FROM test20064
		      1 Query       SHOW COLUMNS FROM test20064
060525 21:33:09	      1 Quit       

However it is visible that Access is sending wrong request to MyODBC:
  This is to fetch auto-increment field:
060525 21:29:04	      4 Query       SELECT `test20064`.`ID` FROM `test20064` WHERE (`DblFld` = 117 )
After fetching auto increment value of new record, access sends incorrect query regarding Row to be fetched (DblFld = 117):
		      4 Query       SELECT `ID`,`DblFld`  FROM `test20064`  WHERE `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1 OR `ID` = 1
but this does not affect the result which is correct.
The Access error regarding fetching of all records is visible here:
		      4 Query       SELECT `ID`,`DblFld`  FROM `test20064`  WHERE `ID` = 1 OR `ID` = 2 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3 OR `ID` = 3
Instead of sending "SELECT * FROM ..." to MyODBC it taxates *ALL* the distinct values of primary key...

Now since there's been a lot of commotion about MSAccess -> MyODBC link, let's take a look at Access log snippet:
MSACCESS        a10-150	ENTER SQLPrepareW 
		HSTMT               07E03410
		WCHAR *             0x0F371D48 [      -3] "SELECT `ID`,`DblFld`  FROM `test20064`  WHERE `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ?\ 0"
		SDWORD                    -3

MSACCESS        a10-150	EXIT  SQLPrepareW  with return code 0 (SQL_SUCCESS)
		HSTMT               07E03410
		WCHAR *             0x0F371D48 [      -3] "SELECT `ID`,`DblFld`  FROM `test20064`  WHERE `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ? OR `ID` = ?\ 0"
		SDWORD                    -3
Sadly this is what comes to MyODBC from Access query builder...
Access is notorious for it's behavior, try typing "SELECT Config, nValue FROM MSysConf" in google and check, for example, http://www.experts-exchange.com/Databases/Btrieve/Q_20772248.html.

Picture showing MS Access ws

Attachment: 20064.jpg (image/jpeg, text), 58.06 KiB.

Well, I upgrade from version 4.1.10 to 4.1.19 and now it works. 
Old values still have the problem but updating them fixes it, also with and old ODBC version.
So I think the issue can be retain solved with the upgrade, sorry for inserting a bug but maybe can be of help for someone.
Thanks again.

Stefano, glad you solved your problem although making float comparison like that is not a good practice.