Bug #4688 MySQL allows create function referencing to table

• Submitted: 22 Jul 2004 4:18
• Modified: 5 Mar 2005 14:05
• Reporter: Dmitry Lenev
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0
• Assigned to: Dmitry Lenev
• CPU Architecture: Any

[22 Jul 2004 4:18] Dmitry Lenev

Description:
MySQL allows to create function with SET statement referencing table.

Naturally (because of restrictions imposed by MySQL's implementation of functions) if one uses such function in statement like select server crashes.

How to repeat:
create table tsb (i integer);
insert into tsb values (),();
delimiter |;
create function sub2(i int) returns int begin set @a:=(select * from tsb limit 1); return i+1; end|
select * from tsb where sub2(1)|
And server crashes here...

Suggested fix:
Until it will be possible to use other tables in stored functions prohibit usage of such SET statements in them.

[25 Nov 2004 23:55] Peter Gulutzan

MySQL also allows table references in a function's RETURN statement. 
Example: 
 
mysql> create table t (s1 int); 
Query OK, 0 rows affected (0.29 sec) 
 
mysql> create function f43 () returns int return (1 = exists(select 'a' from t)); 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> select f43(); 
ERROR 2013 (HY000): Lost connection to MySQL server during query

[5 Mar 2005 14:05] Dmitry Lenev

Hi!

Restriction which does not allowed to access tables in stored functions was removed in 5.0.3. So now it is perfectly ok to reference to tables in them.

Both test cases work as expected now.