MySQL Bugs: #63834: Add isNumeric function

Bug #63834	Add isNumeric function
Submitted:	22 Dec 2011 4:47	Modified:	23 Dec 2011 19:58
Reporter:	Eric Kent	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S4 (Feature request)
Version:	5.5.19	OS:	Windows (Windows 7)
Assigned to:		CPU Architecture:	Any

Description:
I needed an "IsInteger" function to test a string value.

I tried this:

select if (sValue = cast(cast(sValue as unsigned) as char), true, false ) 

and it worked as expected.  However if a put it into a function:

CREATE FUNCTION `IsInteger` (sValue varchar(20))
returns boolean
begin
return if(sValue = cast(cast(sValue as unsigned) as char), True, False);
end

It returned an error when passed a non-integer string:
select IsInteger('3234a');

Error Code: 1292
Truncated incorrect INTEGER value: '3234a'

I was able to get the function to work by adding a condition handler:

CREATE FUNCTION `Sailing`.`IsInteger` (sValue varchar(20))
returns boolean
begin
DECLARE Truncated_incorrect_INTEGER_value CONDITION FOR 1292;
DECLARE CONTINUE HANDLER FOR Truncated_incorrect_INTEGER_value return false;

return if(sValue = cast(cast(sValue as unsigned) as char), True, False);
end

How to repeat:
The problem doesn't always occur.  When I create the database from the schema it occurs.  However sometimes, after dropping and recreating the function, it will work with out the error message.  At other times, I re-created the function several times and it still gives the error.  

The function with the condition handler does work around the problem.  The inconsistent behavior is strange.  If the expression works in a select statement properly, it should work without the error in a function.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Singe SELECT query returns this warning too:

mysql> \W
Show warnings enabled.
mysql> set @sValue='3234a';
Query OK, 0 rows affected (0.00 sec)
mysql> select if (@sValue = cast(cast(@sValue as unsigned) as char), true, false
 ) ;
+----------------------------------------------------------------------+
| if (@sValue = cast(cast(@sValue as unsigned) as char), true, false ) |
+----------------------------------------------------------------------+
|                                                                    0 |
+----------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1292): Truncated incorrect INTEGER value: '3234a'

I re-read the Cast Functions page and noticed the user comment at the bottom from Anne Blankert.  That is the only part of the documentation that I found that addresses this.  Please let me know if it is discussed anywhere else.

Based on that note, I tried:

select if ('3234a' = cast( 0 + '3234a' as char), true, false ) ;

and I got another warning:
Warning	1292 Truncated incorrect DOUBLE value: '3234a'

This is a bug.  Other databases handle this properly.

I found a better work around by using:

return sValue not regexp '[^0-9]';

This will work for unsigned, which is what I needed.  It could be extended for floating points by adding extra clauses to the regular expression.

Other databases have a native isnumeric function.  I recommend adding this to MySQL.

I checked the ISO SQL standard, and it does say that if the string value is not strictly a numeric, "then an exception condition is raised"

Therefore, this is not a bug.  I changed the severity to a feature request.  

Other databases do handle this the way I expected.  This would be an extension to the ISO standard.
I would also recommend that the documentation explicitly discuss this.

Thank you for the reasonable feature request.