MySQL Bugs: #30953: suspicious results from LOG(B,X) functions

Bug #30953	suspicious results from LOG(B,X) functions
Submitted:	11 Sep 2007 13:05	Modified:	29 Oct 2007 12:10
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	6.0.2-alpha, 5.0.48	OS:	Windows (XP 64-bit)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any
Tags:	log()

Description:
According to http://dev.mysql.com/doc/refman/5.1/en/mathematical-functions.html#function_log

"LOG(B,X)  is equivalent to LOG(X) / LOG(B)."  

However this doesn't appear to be the case always.  I get duplicate results in GROUP BY queries for output of LOG function.

On the *same OS and hardware* I get suspicious results:

5.0.48-enterprise-gpl-debug
---------------------------
mysql> select log(col1,col1) from t1; #good
+----------------+
| log(col1,col1) |
+----------------+
|              1 |
|              1 |
+----------------+
2 rows in set (0.00 sec)

mysql> select log(col1,col1) a from t1 group by a; #bad
+------+
| a    |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.01 sec)

mysql> select log(col1)/log(col1) a from t1 group by a ;#good
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

6.0.2-alpha-community-nt-debug:
--------------------------------

mysql> select log(col1,col1) from t1; #good
+----------------+
| log(col1,col1) |
+----------------+
|              1 |
|              1 |
+----------------+
2 rows in set (0.00 sec)

mysql> select log(col1,col1) a from t1 group by a; #good
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select log(col1)/log(col1) a from t1 group by a ; #bad
+------+
| a    |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.00 sec)

Noting that log() is one of the few functions (if not the only function) exhibiting this exact problem.  I tested them all.  Hence, I'm not sure it's the typical "don't compare floats" bug.  Results differ slightly on Linux.

Furthermore, on the same system the above test was run, this outputs 1 for the equality test, so it's really mysql doing something to mangle the results of this function:

printf("log(97)/log(97)==log(25)/log(25) = %d\n",log((double)97)/log((double)97)==log((double)25)/log((double)25));

How to repeat:
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (`col1` int);
INSERT INTO `t1` VALUES (25);
INSERT INTO `t1` VALUES (97);
select log(col1,col1) from t1;
select log(col1,col1) a from t1 group by a;
select log(col1)/log(col1) a from t1 group by a ;
select log(col1)/log(col1)=log(col1)/log(col1) from t1;

On suse 9.3 the correct results are returned. Seems to only be a problem on windows for some reason.  sql server however, returns correct results.

Bad data.

Shane, please try

select format(log(col1,col1), 30) from t1;

to see whether values are really identical, or just close enough and printf() rounds them both to the same value.

5.0.48:

mysql> select format(log(col1,col1), 30) from t1;
+----------------------------------+
| format(log(col1,col1), 30)       |
+----------------------------------+
| 1.000000000000000000000000000000 |
| 1.000000000000000000000000000000 |
+----------------------------------+
2 rows in set (0.00 sec)

I tested it on my 32bit Windows, got the 'proper' behaviour.
So it seems that the result here depends on the OS/processor.
Unfortunately there's not much we can do about that in 5.0,
besides setting the rule like 'Do not expect exact comparisons
with DOUBLE types to work predictable'. I'd recommend CAST DECIMAL
or ROUND() functions as workaround.