MySQL Bugs: #71220: pow() function returns an error for bad values

Bug #71220	pow() function returns an error for bad values
Submitted:	23 Dec 2013 16:25	Modified:	25 Dec 2013 5:53
Reporter:	Philip Smolen	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S2 (Serious)
Version:	5.5.13, 5.5.35, 5.6.16, 5.7.3	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
All math functions should return null on errors.  The pow() function reports an error and aborts the statement if it gets bad inputs.  

http://dev.mysql.com/doc/refman/5.5/en/mathematical-functions.html#function_pow confirms the desired behavior: "All mathematical functions return NULL in the event of an error."

This unexpected behavior can cause my software to fail.  If I'm running a query on user input, I could get an error at any time.  Since the entire statement fails, the error will not be limited to the area that was actually causing trouble.

My notes say that pow() used to return "NaN" when it had bad inputs.  This was also a bug and would cause some unexpected behavior, but was not as bad as aborting the entire statement.  My notes aren't specific about the version number.

How to repeat:
Case 1:
SELECT POW(-1, 0.5);
Should return NULL, instead reports "ERROR 1690 (22003): DOUBLE value is out of range in 'pow(-(2.0),0.5)'".

Case 2:
CREATE TEMPORARY TABLE tt (value INTEGER);
INSERT INTO tt VALUES(4),(9),(-2);
SELECT value,POW(value, 0.5) FROM tt LIMIT 2;
SELECT value,POW(value, 0.5) FROM tt LIMIT 3;
The last statement should return 3 rows.  The very last value should be NULL.  Not only do we not get the last value, but we don't get any values.

Suggested fix:
Return NULL rather than an error in this case.  Do the same thing as you do with "SELECT LOG(-100);"

5.1.72 returns NULL.  5.5.35 and 5.6.15 both return the error.

Peter
(not a MySQL/Oracle person)

The handling is compatible with the SQL standard:

<power function> ::=
  POWER <left paren> <numeric value expression base> <comma>
      <numeric value expression exponent> <right paren>

12) If <power function> is specified, then let NVEB be the <numeric value expression base>, then let VB be the value of NVEB, let NVEE be the <numeric value expression exponent>, and let VE be the value of NVEE.
Case:
a) If at least one of VB and VE is the null value, then the result is the null value.
b) If VB is 0 (zero) and VE is negative, then an exception condition is raised: data exception — invalid argument for power function.
c) If VB is 0 (zero) and VE is 0 (zero), then the result is 1 (one).
d) If VB is 0 (zero) and VE is positive, then the result is 0 (zero).
e) If VB is negative and VE is not equal to an exact numeric value with scale 0 (zero), then an exception condition is raised: data exception — invalid argument for power function.
f) If VB is negative and VE is equal to an exact numeric value with scale 0 (zero) that is an even number, then the result is the result of
EXP(NVEE*LN(-NVEB))
g) If VB is negative and VE is equal to an exact numeric value with scale 0 (zero) that is an odd number, then the result is the result of
-EXP(NVEE*LN(-NVEB))
h) Otherwise, the result is the result of
EXP(NVEE*LN(NVEB))

Thus, invalid input should cause an exception to be raised. A NULL value should be returned only when one of the input values is NULL.

MySQL returns (according to documentation and according to how it always did) NULL on impossible parameters to mathematical functions.

If "SQL Standard* should be a guideline, MySQL could be scrapped.

What then about 
"SELECT LOG(-1);"? 
It returns NULL in all versions.

Such change would at least need an sql_mode (like 'null_on_impossible_math') so that old behavior could be preserved IMO.  It is a somewhat severe (in)compability change (not that that function is used a lot, but when it is, it may cause scripts to abort and render a server environment unusable after an upgrade to 5.5 or 5.6). And further it is completely un-announced and undocumented and  probably passed tests and any code review unnoticed. It has to be reversed in 5.5 and 5.6 at least.

Please drop the attitude that this is OK and and intended change.  It is an overlook by a specific developersof MySQL works and always did simply! And a test failure and code review failure. If it was intended for which version will the release notes tell about this change?

Even "SELECT 1/0" returns NULL in all versions!

And that could be the (historical) reason for this *speciality* of MySQL: unsupported/invalid/impossible math expressions may cause overflows, infinite loops etc. causing the server to become irresponsible and even to crash. MySQL solves this by returning NULL for all math expressions that cannot be calculated. Returning an error could be better sometimes and sometimes not. But it should not be an undocumented change and a workaround (like an sql_mode) should be provided for behavior compatible with previous versions.

Yes, this is a huge change.  It can cause my automated code to fail.  At a bare minimum the documentation should be fixed to reflect this.

I'm not sure how I'll work around this.  I typically select a lot of rows and a lot of columns at once.  If just one value is bad, the whole thing fails.  Returning a null for most failures is nice because it limits damage done by any one error.  Between this and the recent changes to integer overflow, I don't know how I can avoid whole statements failing.  http://dev.mysql.com/doc/refman/5.5/en/out-of-range-and-overflow.html  "As of MySQL 5.5.5, overflow during numeric expression evaluation results in an error."

Thank you for the report.

Verified as described.

Although one can argue that error returned in time when arguments are proceed, user manual does not specify negative numbers should not be passed to function POW().

I've been digging into this further.  It appears that this new error is reported to the API client in a different way than older errors.  Can you comment on this?  Is this the expected behavior?  Is this documented anywhere?

Let me offer some examples:
A1)  select * from table_that_doesnt_exist
A2)  xyz
B1)  select pow(-2,0.5)
B2)  select 2000000000*2000000000*2000000000

In the MySQL command line client, all of these look very similar.  They will all print an error message instead of a result.

When I try these in the PHP client, B1 and B2 give me a warning, like PHP is confused.  Other than the warning, everything works as expected.  mysql_error() and mysql_errno() report something reasonable in all three cases and I get no result in any of the three cases.  See details, below.

When I try these in my own C++ program using the MySQL API, I see something strange.  When I call mysql_real_query() on A1 or A2 (or any other bad query I've seen before now) I get a result of 1, saying there was an error.  I can immediately call mysql_errno() and get more details on the error.  That's what I expect.

But when I call mysql_real_query() on B1 or B2, I get a result of 0, which means success!  When I call mysql_errno() it also returns 0, to say success.  I don't see a problem until I call mysql_store_result().  That returns NULL.  If I call mysql_errno() again, then I see details about the error.  I have never before called mysql_errno() at that time.  I've never needed to.  

I looked at the documentation for mysql_store_result().  It says that function can fail because the system is out of memory, the network connection was broken, and similar cases.  It doesn't say anything about a bad value in a function.  http://dev.mysql.com/doc/refman/5.5/en/mysql-store-result.html

PHP example:
php > $result = mysql_query("select * from table_that_doesnt_exist");
php > if (!$result) echo mysql_errno(), ": ", mysql_error(), "\n";
1146: Table 'mydb.table_that_doesnt_exist' doesn't exist
php > $result = mysql_query("xyz");
php > if (!$result) echo mysql_errno(), ": ", mysql_error(), "\n";
1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xyz' at line 1
php > $result = mysql_query("select pow(-2,0.5)");
PHP Warning:  mysql_query(): Unable to save result set in php shell code on line 1
php > if (!$result) echo mysql_errno(), ": ", mysql_error(), "\n";
1690: DOUBLE value is out of range in 'pow(-(2),0.5)'
php > $result = mysql_query("select 2000000000*2000000000*2000000000");
PHP Warning:  mysql_query(): Unable to save result set in php shell code on line 1
php > if (!$result) echo mysql_errno(), ": ", mysql_error(), "\n";
1690: BIGINT value is out of range in '((2000000000 * 2000000000) * 2000000000)'

Again, it seems like there was a big change which could break at lot of existing code.  At a bare minimum this should be documented.  But I really hope you go back to the old way.

One more detail to go with my last comment:
When I try to execute queries B1 or B2, mysql_field_count() returns 1 but mysql_store_result() returns NULL.  This is contrary to the documentation.  http://dev.mysql.com/doc/refman/5.5/en/mysql-field-count.html goes into great detail on the subject.

The behavior of the server has changed.  The change can break client software.  The change is not documented.

Thank you for the feedback.

I discussed this case with our Connectors team and they say that connectors simply return information which server provides. Therefore this is only server issue.