MySQL Bugs: #2338: Trignometric arithmatic problems

Bug #2338	Trignometric arithmatic problems
Submitted:	9 Jan 2004 13:00	Modified:	4 Feb 2004 1:28
Reporter:	Shaun Thomas	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.17	OS:	Linux (Linux)
Assigned to:	Victor Vagin	CPU Architecture:	Any

Description:
ASIN and ACOS unpredictably return an incorrect value of NULL when given valid arguments.

How to repeat:
SELECT ACOS(1.0);  -- Returns 0.0
SELECT ASIN(1.0);  -- Returns 1.570796 (PI/2)

The above work as expected.  Now try these:

SELECT ACOS(0.2*5.0);  -- returns NULL
SELECT ACOS(0.5*2.0);  -- returns 0.0
SELECT ASIN(0.8+0.2);  -- returns NULL
SELECT ASIN(1.2-0.2);  -- returns 1.570796

All of these are equivalent to taking the ACOS or ASIN of 1.0.

Verified against 4.0.18.

Thank you.

Happens with -O2 and -03, not with -01.
Looked a bit at the generated assembler, sent questions to Sinisa...

Verified on a 3.23.58 too.

Let's fix it by using 'volatile'..

--- 1.54/sql/item_func.cc       Wed Jun 25 21:11:08 2003
+++ 1.55/sql/item_func.cc       Mon Jan 12 22:47:15 2004
@@ -456,7 +456,8 @@
 
 double Item_func_acos::val()
 {
-  double value=args[0]->val();
+  // this 'volatile' was added as a fix for BUG #2338 to calm optimizer down
+  volatile double value=args[0]->val();
   if ((null_value=(args[0]->null_value || (value < -1.0 || value > 1.0))))
     return 0.0;
   return fix_result(acos(value));
@@ -464,7 +465,8 @@
 
 double Item_func_asin::val()
 {
-  double value=args[0]->val();
+  // this 'volatile' was added as a fix for BUG #2338 to calm optimizer down
+  volatile double value=args[0]->val();
   if ((null_value=(args[0]->null_value || (value < -1.0 || value > 1.0))))
     return 0.0;
   return fix_result(asin(value));

--- 1.4/mysql-test/r/func_math.result   Fri May 31 08:22:34 2002
+++ 1.5/mysql-test/r/func_math.result   Mon Jan 12 22:47:15 2004
@@ -20,3 +20,15 @@
 3.141593       1.000000        0.000000        0.000000        0.64209262      1.570796        1.570796      
0.785398
 degrees(pi())  radians(360)
 180    6.2831853071796
+ACOS(1.0)
+0.000000
+ASIN(1.0)
+1.570796
+ACOS(0.2*5.0)
+0.000000
+ACOS(0.5*2.0)
+0.000000
+ASIN(0.8+0.2)
+1.570796
+ASIN(1.2-0.2)
+1.570796

--- 1.4/mysql-test/t/func_math.test     Fri May 31 08:22:34 2002
+++ 1.5/mysql-test/t/func_math.test     Mon Jan 12 22:47:15 2004
@@ -13,3 +13,14 @@
 select rand(999999),rand();
 select pi(),sin(pi()/2),cos(pi()/2),abs(tan(pi())),cot(1),asin(1),acos(0),atan(1);
 select degrees(pi()),radians(360);
+
+#
+# Bug #2338 Trignometric arithmatic problems 
+#
+
+SELECT ACOS(1.0);
+SELECT ASIN(1.0);
+SELECT ACOS(0.2*5.0);
+SELECT ACOS(0.5*2.0);
+SELECT ASIN(0.8+0.2);
+SELECT ASIN(1.2-0.2);

Note that is a bug in gcc, we just reported it to the gcc maintainers
http://gcc.gnu.org/bugzilla/show_bug.cgi?id=13719
and are waiting for their feedback.

Thank you for this bug report. To properly diagnose the problem, we
need a backtrace to see what is happening behind the scenes. To
find out how to generate a backtrace, please read
http://www.mysql.com/doc/en/Making_trace_files.html

Once you have generated a backtrace, please submit it to this bug
report and change the status back to 'Open'. Thank you for helping
us make our products better.

Additional info:

monty said, that mysql must work fine independently on 
compiler's bugs :) to satisfy our clients better.

So, I've pushed the fix to mysql-3.23 and 
after merge it'll appear in mysql-4.0 until 
gcc fix it's bug

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

As I said in an email (subject "URGENT using -ffloat-store to solve BUG#2338 ?? [Pnd]: Trignometric arithmatic problems" Jan 18th) (but I should probably have copied it here), the gcc guys said it's "not a bug, gcc is allowed to keep intermediate results in the precision of the FPU; to have them rounded to the precision of 'double' you have to store the intermediate result in a variable [which the MySQL code does, see item_func_acos::val()] *and* use -ffloat-store".
Indeed, compiling item_func.cc with -ffloat-store makes results correct.
So I had proposed in the email to use -ffloat-store.

monty said, volatile is better