MySQL Bugs: #8463: SQRT: Should return an error when argument is negative

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

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>

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

This can't be fixed right now
see also http://bugs.mysql.com/8423

There is no reason this can't be fixed now.

Same results in 8.0.3.