Bug #93605 UDF accidentally accepts stored functions in generated column
Submitted: 14 Dec 2018 7:46
Reporter: Mikiya Okuno Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.7.21 OS:Any
Assigned to: CPU Architecture:Any

[14 Dec 2018 7:46] Mikiya Okuno
Description:
Stored functions are not allowed in the generated columns as described in the manual:

https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html

"Subqueries, parameters, variables, stored functions, and user-defined functions are not permitted."

If we attempt to create such a generated column, it results in an error.

However, UDFs can be used despite the manual says it cannot be used, and stored functions passed as arguments to the UDF are accidentally accepted.

How to repeat:
See that stored functions are rejected:

mysql> create function f1(a int) returns int deterministic return a * 2;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (id serial, val int);
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t1 add val_2 int generated always as (f1(val)) virtual;
ERROR 3102 (HY000): Expression of generated column 'val_2' contains a disallowed function.

Now, install example UDFs described at:
https://dev.mysql.com/doc/refman/5.7/en//udf-compiling.html

Then, define a generated column.

mysql> alter table t1 add val_2 int generated always as (myfunc_int(f1(val))) virtual;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

It is accidentally accepted!

mysql> insert into t1 (val) values(1);
ERROR 2013 (HY000): Lost connection to MySQL server during query

Inserting a row crashes the server!

Suggested fix:
Three possible suggestions:

* Add necessary check for UDF arguments when creating a generated column.
* Disallow UDFs in generated columns as described in the manual.
* Allow stored functions used in generated columns.

IMHO, allowing UDFs in generated column is beneficial for various users.