MySQL Bugs: #15301: Server mode to reject UDF calls with type mismatched arguments

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:	Windows (WinXP pro)
Assigned to:		CPU Architecture:	Any

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

if you'd try it in INSERT

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

in the strict mode, will be a warning too ?

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.

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.

Ok, fair enough. Thanks for your time!

Please have also a look on Bug#15600 which is declared as
a duplicate of this one. There is also a simple testcase attached.