MySQL Bugs: #65351: power(a,b) fails in some cases for no obvious reason

Bug #65351	power(a,b) fails in some cases for no obvious reason
Submitted:	17 May 2012 12:46	Modified:	4 Mar 2013 14:50
Reporter:	Volker Raab	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.5.22-0ubuntu1, 5.5.26, 5.6.6	OS:	Linux
Assigned to:	Tor Didriksen	CPU Architecture:	Any
Tags:	double, ERROR 1690, pow()

Description:
after upgrading mysql on ubuntu linux from 5.1.62-0ubuntu0.11.10.1 (oneric) to version 5.5.22-0ubuntu1 (Ubuntu perfect)
some calculations fail with 
ERROR 1690 (22003): DOUBLE value is out of range in 'pow((@aaa),(@bbb))'

However, in the old version nothing was wrong, and also the corresponding values for the pow-function seem O.K (e.g. apparently not negative).

B.t.w.: if I mysqldump the database and reimport it into the old version it seems to go through without problems.

How to repeat:
as an example please find a cut from output:

mysql> select @aaa:=1.0+a.irr/100, @bbb:=b.laufzeit/365., abs(@aaa), abs(@bbb), pow(@aaa,@bbb) from T3Irr as a, V3IrrOutput as b         where a.depot = b.depot and a.posten = b.posten and a.wpid = b.wpid limit 1;
ERROR 1690 (22003): DOUBLE value is out of range in 'pow((@aaa),(@bbb))'

# if I then wrap abs() around the arguments it works

select @aaa:=1.0+a.irr/100, @bbb:=b.laufzeit/365., abs(@aaa), abs(@bbb), pow(abs(@aaa),abs(@bbb)) from T3Irr as a, V3IrrOutput as b         where a.depot = b.depot and a.posten = b.posten and a.wpid = b.wpid limit 1;
+---------------------+-----------------------+--------------------+----------------------------------+--------------------------+
| @aaa:=1.0+a.irr/100 | @bbb:=b.laufzeit/365. | abs(@aaa)          | abs(@bbb)                        | pow(abs(@aaa),abs(@bbb)) |
+---------------------+-----------------------+--------------------+----------------------------------+--------------------------+
|  1.4556840133666993 |                2.9068 | 1.4556840133666993 | 2.906849315000000000000000000000 |        2.978595467533429 |
+---------------------+-----------------------+--------------------+----------------------------------+--------------------------+
1 row in set (0.03 sec)

#  in fact: wrapping around the first argument is enough

mysql> select @aaa:=1.0+a.irr/100, @bbb:=b.laufzeit/365., abs(@aaa), abs(@bbb), pow(abs(@aaa),@bbb) from T3Irr as a, V3IrrOutput as b         where a.depot = b.depot and a.posten = b.posten and a.wpid = b.wpid limit 1;
+---------------------+-----------------------+--------------------+----------------------------------+---------------------+
| @aaa:=1.0+a.irr/100 | @bbb:=b.laufzeit/365. | abs(@aaa)          | abs(@bbb)                        | pow(abs(@aaa),@bbb) |
+---------------------+-----------------------+--------------------+----------------------------------+---------------------+
|  1.4556840133666993 |                2.9068 | 1.4556840133666993 | 2.906849315000000000000000000000 |   2.978595467533429 |
+---------------------+-----------------------+--------------------+----------------------------------+---------------------+
1 row in set (0.02 sec)

# just 'copy-paste' of the numbers does not reveal surprises:

mysql> select pow(1.4556840133666993, 2.906849315000000000000000000000);
+-----------------------------------------------------------+
| pow(1.4556840133666993, 2.906849315000000000000000000000) |
+-----------------------------------------------------------+
|                                         2.978595467533429 |
+-----------------------------------------------------------+
1 row in set (0.01 sec)

It is difficult for me to boil it down any further, but if there is something additional that I can supply, please let me know.

Suggested fix:
In my case it seems to help to wrap an abs(...) around some arguments. However, this cannot be a general fix/workaround.

It would be nice to get the output of SHOW CREATE TABLE for both tables used, the results of EXPLAIN for the query and also exact values in that a.irr and b.laufzeit columns for the row in the result set that satisfies the query.

T3Irr is a table.
However, V3IrrOutput is a view. So the query cascades through a whole bunch of other tables...

mysql> show create table T3Irr;
 CREATE TABLE `T3Irr` (
  `Depot` int(11) NOT NULL,
  `Posten` int(11) DEFAULT NULL,
  `wpid` int(11) NOT NULL,
  `nam` varchar(30) NOT NULL,
  `Wert` decimal(30,2) DEFAULT NULL,
  `Dividende` decimal(30,2) DEFAULT NULL,
  `Saldo` decimal(30,2) DEFAULT NULL,
  `IRR` float DEFAULT '3',
  `dirr` float DEFAULT '1'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

mysql> show create table V3IrrOutput;
CREATE ALGORITHM=UNDEFINED DEFINER=`volker`@`%` SQL SECURITY DEFINER VIEW `V3IrrOutput` AS select `a`.`Depot` AS `Depot`,`a`.`Posten` AS `Posten`,`a`.`wpid` AS `wpid`,`a`.`Nam` AS `Nam`,`a`.`Wert` AS `Wert`,(to_days(`b`.`mdat`) - to_days(`a`.`Datum`)) AS `Laufzeit`,`b`.`mdat` AS `mdat` from (`T3IrrBelegeNext` `a` left join `V3IrrOutTmp` `b` on(((`a`.`Depot` = `b`.`Depot`) and (`a`.`Posten` = `b`.`Posten`)))) order by `a`.`Depot`,`a`.`Posten`,`a`.`Datum` | utf8                 | utf8_general_ci      |

mysql> explain select @aaa:=1.0+a.irr/100, @bbb:=b.laufzeit/365., abs(@aaa), abs(@bbb), pow(@aaa,@bbb) from T3Irr as a, V3IrrOutput as b         where a.depot = b.depot and a.posten = b.posten and a.wpid = b.wpid limit 1;
+----+-------------+-----------------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-----------------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | PRIMARY     | a               | ALL  | NULL          | NULL | NULL    | NULL |  629 | Using temporary; Using filesort |
|  1 | PRIMARY     | a               | ALL  | NULL          | NULL | NULL    | NULL |  101 | Using where; Using join buffer  |
|  1 | PRIMARY     | <derived3>      | ALL  | NULL          | NULL | NULL    | NULL |  106 |                                 |
|  3 | DERIVED     | T3IrrBelegeNext | ALL  | NULL          | NULL | NULL    | NULL |  629 | Using temporary; Using filesort |
+----+-------------+-----------------+------+---------------+------+---------+------+------+---------------------------------+
4 rows in set (0.00 sec)

I, then, tried to be "smart" and first select the data into an intermediate table named `burgreqest`. But out of that it works as expected:

mysql> create table bugrequest select @aaa:=1.0+a.irr/100 as aaa, @bbb:=b.laufzeit/365. as bbb, abs(@aaa) as abs_aaa, abs(@bbb) as abs_bbb, pow(abs(@aaa),@bbb) as pow_abs from T3Irr as a, V3IrrOutput as b         where a.depot = b.depot and a.posten = b.posten and a.wpid = b.wpid limit 5;
Query OK, 5 rows affected (0.15 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select *, pow(aaa,bbb) from bugrequest
    -> ;
+--------------------+--------+--------------------+----------------------------------+--------------------+--------------------+
| aaa                | bbb    | abs_aaa            | abs_bbb                          | pow_abs            | pow(aaa,bbb)       |
+--------------------+--------+--------------------+----------------------------------+--------------------+--------------------+
| 1.4556840133666993 | 2.9068 | 1.4556840133666993 | 2.906849315000000000000000000000 |  2.978595467533429 | 2.9785403146007075 |
| 1.4556840133666993 | 2.5041 | 1.4556840133666993 | 2.504109589000000000000000000000 | 2.5605774711606353 | 2.5605682519757056 |
| 1.4556840133666993 | 1.4795 | 1.4556840133666993 | 1.479452054000000000000000000000 |  1.742809724543602 | 1.7428410998759312 |
| 1.4556840133666993 | 0.5014 | 1.4556840133666993 | 0.501369863000000000000000000000 |   1.20713803998827 | 1.2071516996983231 |
| 1.4556840133666993 | 0.0000 | 1.4556840133666993 | 0.000000000000000000000000000000 |                  1 |                  1 |
+--------------------+--------+--------------------+----------------------------------+--------------------+--------------------+
5 rows in set (0.00 sec)

mysql> select *, pow(aaa,bbb) from bugrequest;
+--------------------+--------+--------------------+----------------------------------+--------------------+--------------------+
| aaa                | bbb    | abs_aaa            | abs_bbb                          | pow_abs            | pow(aaa,bbb)       |
+--------------------+--------+--------------------+----------------------------------+--------------------+--------------------+
| 1.4556840133666993 | 2.9068 | 1.4556840133666993 | 2.906849315000000000000000000000 |  2.978595467533429 | 2.9785403146007075 |
| 1.4556840133666993 | 2.5041 | 1.4556840133666993 | 2.504109589000000000000000000000 | 2.5605774711606353 | 2.5605682519757056 |
| 1.4556840133666993 | 1.4795 | 1.4556840133666993 | 1.479452054000000000000000000000 |  1.742809724543602 | 1.7428410998759312 |
| 1.4556840133666993 | 0.5014 | 1.4556840133666993 | 0.501369863000000000000000000000 |   1.20713803998827 | 1.2071516996983231 |
| 1.4556840133666993 | 0.0000 | 1.4556840133666993 | 0.000000000000000000000000000000 |                  1 |                  1 |
+--------------------+--------+--------------------+----------------------------------+--------------------+--------------------+
5 rows in set (0.00 sec)

So, if this does not work, then how can I get 'exact' values out of the tables?
(Unfortunately this table contains sensitive private data. Otherwise I would have just sent you the whole thing.)

Hi Valeriy,

going over my last comment again I notice two things:

(a) I accidentally pasted the last table twice. Sorry 'bout that!

(b) The values in columns `pow_abs` and pow(aaa,bbb) deviate considerably!

Cheers, Volker

Thank you for the feedback.

Please also send output of select a.irr, b.laufzeit from T3Irr as a, V3IrrOutput as b         where a.depot = b.depot and a.posten = b.posten and a.wpid = b.wpid limit 1;

Hi Sveta,

please find appended the requested output.
I hope it helps.

Cheers
           Volker

mysql> select a.irr, b.laufzeit from T3Irr as a,
    -> V3IrrOutput as b         where a.depot = b.depot and a.posten =
    -> b.posten and a.wpid = b.wpid limit 4;
+---------+----------+
| irr     | Laufzeit |
+---------+----------+
| 45.5684 |     1061 |
| 45.5684 |      914 |
| 45.5684 |      540 |
| 45.5684 |      183 |
+---------+----------+
4 rows in set (0.02 sec)

Thank you for the feedback.

I can not repeat described behavior.

Please try with current version 5.5.24, available from http://dev.mysql.com/downloads, and if problem still exists indicate if you have not empty SQL Mode.

Finally I managed to reduce the table to its essence and (hopefully) remove the bulk of private data (see file pow-bug.sql.bz2)
If I create a new database and read its contents it still fails with the same message. Maybe you can reproduce it now.
Thanks a lot for all the effort!

Thank you for the test case.

Verified as described.

Source of issue is GROUP BY and ORDER BY in view definitions.

I uploaded even smaller test case which uses only tables. Problem with my test case first query runs successfully, then following fail.

In version 5.6 ORDER BY issue seems to be resolved.

The error message
1690: DOUBLE value is out of range in 'pow((@`aaa`),(@`bbb`))'
is caused by the fix for
http://bugs.mysql.com/bug.php?id=8433
Overflow must be an error

Previous to that fix, MySQL would silently ignore NaN errors from pow()
and return zero/NULL.

There are no "sequence points" in SQL, as there are in C/C++
and the variables are indeed read many times, before they are assigned new
values.
At some point we evaluate pow(-0.36304992675781245, 3.446575342)
MySQL 5.1 will return 0.0, MySQL 5.5 and up will report value out of range.

My previous suggestion, reset variables to zero,
sort-of-works, the query does not fail.
The result is however undefined.
The correct solution is to avoid reading/writing of the same user variable in
one statement.