Bug #67136 CONV() difference, 5.1 vs. 5.5
Submitted: 8 Oct 2012 19:10 Modified: 20 Nov 2012 13:54
Reporter: Ammon Sutherland Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.1 and 5.5 OS:Linux (RHEL 5 64 bit)
Assigned to: Tor Didriksen CPU Architecture:Any
Tags: conv, datatype, functions, regression

[8 Oct 2012 19:10] Ammon Sutherland
Description:
Different results are produced from the same set of functions under varying circumstances between MySQL 5.1 and MySQL 5.5 (tested 5.1.40, 5.1.59, 5.1.63, 5.1.66 against 5.5.25, 5.5.27):

Example query:

SELECT CAST(CONV(LEFT(MD5(CAST(LOWER('Happy New Year, everybody') AS BINARY)),16),16,10) - 9223372036854775807 AS SIGNED) as A;

5.1:
7637281099758358528

5.5:
7637281099758360576

Close, but not close enough...  Doing the same thing effectively via PHP also yields the 5.1 answer.

I have compared the various char% variables and it happens setting everything client-side to utf8 or latin1 or any mix thereof.  From what I can narrow down it is a problem in the CONV function or the data type it is inferring based on what it just got.

For example, the inside LEFT() function on that resolves to 'e9fd12b238e6d426' and then substituting that in yields:

SELECT CAST(CONV('e9fd12b238e6d426',16,10) - 9223372036854775807 AS SIGNED) as A;

5.1:
7637281099758358528

5.5:
7637281099758360576

Except that the CONV function itself inside the CAST function yields the same result on each: 16860653136613135398

So, perhaps it's in the integer math being done, except it's not order of operations because adding parens doesn't help.

For reference, sql_mode is unset ('') and it makes no difference switching to ANSI mode either.

This can be repeated with a variety of different values, but not all values for the inner string in the LOWER function and a different integer inside the CAST function.

Neither the CAST nor CONV functions list any changes from 5.1 to 5.5 in the documentation and I do not see any pertinent (in my estimation) changes listed in 5.5 "type conversion" page - http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html .

How to repeat:
fire up an instance (no my.cnf needed, defaults suffice):

SELECT CAST(CONV(LEFT(MD5(CAST(LOWER('Happy New Year, everybody') AS BINARY)),16),16,10) - 9223372036854775807 AS SIGNED) as A;

Also:

SELECT CAST(CONV('e9fd12b238e6d426',16,10) - 9223372036854775807 AS SIGNED) as A;

Suggested fix:
Make 5.1 and 5.5 behave the same or document how to do so.
[11 Oct 2012 19:31] Sveta Smirnova
Thank you for the report.

Regression verified as described, although you badly mix data types in this query: substract from VAR_STRING value without explicit casting to numeric value.
[18 Oct 2012 12:04] Ammon Sutherland
Not to be pushy, but is there any update to this one?
[26 Oct 2012 16:51] Ammon Sutherland
Still just checking to see if there is any update on this, it's been over 2 weeks now without an update.
[8 Nov 2012 7:51] Tor Didriksen
According to:
http://dev.mysql.com/doc/refman/5.1/en/mathematical-functions.html#function_conv
conv(N, from base, to base) 
Returns a *string* representation of the number N

Because one of the arguments is a string, 
the subtraction is done in floating-point according to:
http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html

For floating point operations, we have DBL_DIG == 15
which means maximum precision is 15 digits.

floating point operations changed significantly in MySQL 5.5 due to
http://dev.mysql.com/worklog/task/?id=2934

To get the correct result, cast the result of conv to a numeric type:
select cast(cast(CONV('e9fd12b238e6d426',16,10) as decimal(20)) - 9223372036854775807 as signed) as a;
a
7637281099758359591