MySQL Bugs: #7840: 4.1.7 fails to return matching records when types do not match

Bug #7840	4.1.7 fails to return matching records when types do not match
Submitted:	12 Jan 2005 16:28	Modified:	22 Feb 2005 13:51
Reporter:	Tom Price	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.9	OS:	Any (any)
Assigned to:	Michael Widenius	CPU Architecture:	Any

Description:
Using MySQL 4.1.7 with Visual Foxpro 7.0 and MyODBC Connector 3.51.10.

Visual Foxpro is loosely typed in that all numeric values are floats. Using an ODBC parameter query via VFP's SQLExec statement. i.e:

keyval=108
rc=SQLexec(nConn,"select * from test where reckey=?keyval")

On the database, the query above is realized as:

select * from test where reckey=1.08E02

Note that it is not possible to explicitly declare this variable as an int so it is passed as type SQL_FLOAT.

In my table, the key is an unsigned int.

This query returns a row but replacing the 108 with a value that (I think) is imprecisely represented as a float fails. For example, replacing the 108 with a 109 does not return a record even though one is there. 

Note 1: Same type of parameterization was used to insert these records to begin with.

Note 2: This behavior has changed from the 4.0.x versions. Both queries return rows with 4.0.17 and 4.0.20.

Problem is exacerbated because of widespread use of auto_increment keys in my database design.

The only workarounds I've found are:

1) Convert keyval to a string prior to parameterization.

2) String the value into the query itself similar to the way PHP would do it. (I really hate this because it will prevent the use of prepared queries at some point in the future.)

How to repeat:
CREATE TABLE test (
  reckey int unsigned NOT NULL,
  recdesc varchar(50) NOT NULL,
  PRIMARY KEY  (reckey)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO test VALUES (108, 'Has 108 as key');
INSERT INTO test VALUES (109, 'Has 109 as key');

select * from test where reckey=108 <-WORKS
select * from test where reckey=1.08E2 <-WORKS
select * from test where reckey=109 <-WORKS
select * from test where reckey=1.09E2 <-FAILS

Suggested fix:
Revert the parameter conversion process to the one used in 4.0.x.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Background:
We have in 4.1 replaced strtod() with our own strtod() function to ensure that MySQL work as similar as possible on different platforms.

The problem is that our strtod() calulated fractions by division, which caused some inaccuracy for numbers of type 123.45E+02.

I have now changed the algoritm so that we delay the division as long as possible, which ensures that we get floating point values  like the above to work for the range of integer values covered by 'double'

The fix will be in 4.1.11