Bug #15301 Server mode to reject UDF calls with type mismatched arguments
Submitted: 29 Nov 2005 0:10 Modified: 27 Sep 2008 12:41
Reporter: Roland Bouman Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: User-defined functions ( UDF ) Severity:S3 (Non-critical)
Version:5.0.15 OS:Microsoft Windows (WinXP pro)
Assigned to: CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[29 Nov 2005 0:10] Roland Bouman
Description:
Setting the 

args->arg_type[X]

array entry to one of the Item_result enum values in the XXX_init() function 

"causes MySQL to coerce arguments to those types for each call to xxx()" 

when coercion fails, a warning is generated. 
It would be nice to be able to generate errors instead of warnings, like you can for table column assignments in the STRICT or TRADITIONAL server modes.

How to repeat:
//this is where we specify only integer arguments
my_bool udf_init(
    UDF_INIT* initid
,   UDF_ARGS* args
,   char *message
){
    for (unsigned short i=0;i<args->arg_count;i++){
        args->arg_type[i] = INT_RESULT;
    }
    return 0;
}
//this does not matter right now
my_ulonglong udf(
    UDF_INIT *initid
,   UDF_ARGS *args
,   char *is_null
,   char *error
){
    return 0;
}

now, suppose we register this:

create function udf returns int soname 'my.dll';

and then call it:

mysql> select udf('a');
+----------+
| udf('a') |
+----------+
|        0 |
+----------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'a' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
create a new server mode, something like STRICT_UDFS to enable these warnings to be reported as errors
[29 Nov 2005 12:13] Sergei Golubchik
if you'd try it in INSERT

  INSERT t1 VALUES (udf('a'));

in the strict mode, will be a warning too ?
[29 Nov 2005 14:03] Roland Bouman
I tried that, and this does fail with an error:

mysql> insert into nums (i) values (bin_to_num('a'))
    -> //
ERROR 1292 (22007): Truncated incorrect INTEGER value: 'a'

("bin_to_num()" is my UDF, wich coerces it's arguments into my_ulonglong 's and "nums" is a base table with a single int column "i")

So, it works there, like you expected. However, if i was to use this UDF in a stored procedure, it would execute without error, just a warning:

mysql> create procedure pppp()
    -> begin
    ->     declare v int;
    ->     set v:=bin_to_num('a');
    ->     insert into nums (i) values (v);
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> call pppp()
    -> //
Query OK, 1 row affected, 1 warning (0.03 sec)

I would want to set the sqlmode so that just the invokation of the UDF would fail.
[30 Nov 2005 12:22] Sergei Golubchik
Ok, then it's in todo. Same issue with division by zero, for example - it's an error in INSERT, but a warning in SELECT.
The reason is that when strict mode was added it was fairly easy to abort an INSERT (or UPDATE), but to abort a SELECT rather big changes were required, that would destabilize 5.0. Thus it was moved to a later version.
[30 Nov 2005 17:55] Roland Bouman
Ok, fair enough. Thanks for your time!
[9 Dec 2005 11:47] Matthias Leich
Please have also a look on Bug#15600 which is declared as
a duplicate of this one. There is also a simple testcase attached.