Bug #4780 microsecond() returns incorrect value
Submitted: 28 Jul 2004 2:50 Modified: 28 Jul 2004 16:34
Reporter: Christopher Miller Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.0-alpha OS:Windows (XP)
Assigned to: Dean Ellis CPU Architecture:Any

[28 Jul 2004 2:50] Christopher Miller 
Description:
microsecond() does not honor the position of the decimal place. 

mysql> set @t='12:34:56.89'; select microsecond(@t);

+-----------------+
| microsecond(@t) |
+-----------------+
|              89 |
+-----------------+

The correct result is 890000.

How to repeat:
See above.

Suggested fix:
Have microsecond() compute the result relative to the decimal place in the seconds part of a time string rather than the rightmost part of that string.
[28 Jul 2004 3:38] Christopher Miller 
for whomever wishes to incorporate the change into cvs (i'm a developer but not active with mysql on cvs) here is a fix to sql/time.cpp:775 that will eliminate this bug...

fractional:
  /* Get fractional second part */
  if ((end-str) >= 2 && *str == '.' && my_isdigit(&my_charset_latin1,str[1]))
  {
	value = 0; uint mul = 1000000U;

	while ( ++str < end && my_isdigit(&my_charset_latin1,*str) )
		value += ( (uint) (uchar) ( *str - '0' ) ) * ( mul /= 10 );

    date[4]=value;
  }
  else
    date[4]=0;
[28 Jul 2004 4:04] Paul DuBois 
The incorrect result also occurs in 4.1.1. The correct result
occurs in 4.1.2, 4.1.3, and 4.1.4. Perhaps the merge from
4.1 -> 5.0 will take care of the issue.
[28 Jul 2004 16:34] Dean Ellis 
As suggested, this is already corrected in 5.0.1-alpha, so you could build from the BitKeeper repository for 5.0 or await the next binary release.