Bug #8463 SQRT: Should return an error when argument is negative
Submitted: 11 Feb 2005 23:30 Modified: 29 Sep 2008 21:28
Reporter: Trudy Pelzer Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.0.3-alpha-debug OS:Linux (SuSE 9.1)
Assigned to: Assigned Account CPU Architecture:Any

[11 Feb 2005 23:30] Trudy Pelzer
Description:
The square root of a negative number is a complex
number and therefore cannot be handled by the
server. Currently, when I execute SQRT(negative number),
the result is NULL. It would be better if the result was
an error: SQLSTATE HY000 number out of range for function.
Barring that, the MySQL Reference Manual should 
document that NULL is the result when SQRT gets passed
a negative argument.

How to repeat:
mysql> create table t1 (col1 int,col2 int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(1,4),(2,-4);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select sqrt(col2) from t1 where col1=1;
+------------+
| sqrt(col2) |
+------------+
|          2 |
+------------+
1 row in set (0.00 sec)
-- This is the correct result.

mysql> select sqrt(col2) from t1 where col1=2;
+------------+
| sqrt(col2) |
+------------+
|       NULL |
+------------+
1 row in set (0.00 sec)
-- I'd like to see this statement fail, with SQLSTATE HY000,
since the server cannot return the complex number that
results from the calculation.
mysql>
[10 Mar 2005 7:34] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/22875
[11 Mar 2005 6:55] Alexey Botchkov
This can't be fixed right now
see also http://bugs.mysql.com/8423
[29 Sep 2008 21:28] Konstantin Osipov
There is no reason this can't be fixed now.
[11 Nov 2017 21:18] Federico Razzoli
Same results in 8.0.3.