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:Microsoft Windows (XP 64-bit)
Assigned to: Alexey Botchkov CPU Architecture:Any
Tags: log()

[11 Sep 2007 13:05] Shane Bester
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;
[11 Sep 2007 13:12] MySQL Verification Team
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.
[12 Oct 2007 14:37] Konstantin Osipov
Bad data.
[15 Oct 2007 12:38] Sergei Golubchik
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.
[15 Oct 2007 12:44] MySQL Verification Team
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)
[29 Oct 2007 12:10] Alexey Botchkov
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.