Bug #2380 incorrect value of PI
Submitted: 13 Jan 2004 17:51 Modified: 17 Jan 2004 11:20
Reporter: Spencer Sun Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:3.23.52 OS:Linux (Linux RedHat 6.2)
Assigned to: CPU Architecture:Any

[13 Jan 2004 17:51] Spencer Sun 
Description:
I noticed on

http://www.mysql.com/documentation/mysql/bychapter/manual_Functions.html#Functions

that it says for the function PI():

PI()
    Returns the value of PI. The default shown number of decimals is 5, but
    MySQL internally uses the full double precession for PI.

mysql> SELECT PI();
        -> 3.141593
mysql> SELECT PI()+0.000000000000000000;
        -> 3.141592653589793116

Firstly, "precession" should be "precision."  Secondly, that's not the correct value of pi, even considering rounding.  It should be 3.141592653589793238.

The docs on the web page are apparently for 5.0.0 alpha; the output in my
version (3.23.52) matches the output in the documentation, which is to say
that it returns the incorrect value ending in 3116.

How to repeat:
enter the commands from the docs:
mysql> SELECT PI();
        -> 3.141593
mysql> SELECT PI()+0.000000000000000000;
        -> 3.141592653589793116
[16 Jan 2004 2:26] Alexander Keremidarski 
I got exactly the same result with 3.23, 4.0, 4.1 and 5.0
[16 Jan 2004 3:55] Spencer Sun 
On further exploration, this seems to be just a limitation of storing it as a floating point number.

If I SELECT the correct value, I get the wrong one back:

mysql> select 3.141592653589793238;
+----------------------+
| 3.141592653589793238 |
+----------------------+
| 3.141592653589793116 |
+----------------------+
1 row in set (0.00 sec)

For some reason I thought MySQL used an arbitrary-precision internal representation of decimal numbers, but apparently not?

In any case I guess this can't technically be called a bug :-)
[17 Jan 2004 11:20] Brian Aker 
Limitation of the type (look for a new type in a future version that will let you do this with higher precision).