MySQL Bugs: #39137: DELETE makes stored function to fail (if table type is MyISAM)

Bug #39137	DELETE makes stored function to fail (if table type is MyISAM)
Submitted:	30 Aug 2008 18:53	Modified:	31 Aug 2008 18:28
Reporter:	[ name withheld ]	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.51, 5.0.66a	OS:	Linux (Ubuntu hardy)
Assigned to:		CPU Architecture:	Any

Description:
See thread http://forums.mysql.com/read.php?98,224441,224441#msg-224441.

How to repeat:
See thread http://forums.mysql.com/read.php?98,224441,224441#msg-224441.

Verified with 5.0.66a:

C:\Program Files\MySQL\MySQL Server 6.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.0.66a-enterprise-gpl-nt MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DELIMITER @@
mysql> CREATE FUNCTION `test`() RETURNS char(64) CHARSET latin1
    -> BEGIN
    -> DECLARE val CHAR(64);
    -> SELECT attr INTO val FROM test_table;
    -> DELETE FROM test_table;
    -> RETURN val;
    -> END@@
Query OK, 0 rows affected (0.13 sec)

mysql> DELIMITER ;
mysql> create table test_table (attr varchar(64)) engine=myisam;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into test_table set attr='foo';
Query OK, 1 row affected (0.00 sec)

mysql> select test();
+--------+
| test() |
+--------+
| foo    |
+--------+
1 row in set (0.03 sec)

mysql> select test();
+--------+
| test() |
+--------+
| NULL   |
+--------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1329
Message: No data - zero rows fetched, selected, or processed
1 row in set (0.00 sec)

mysql> create table another_table (another_attr varchar(64)) engine=myisam;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into another_table set another_attr='foo';
Query OK, 1 row affected (0.00 sec)

mysql> select * from another_table;
+--------------+
| another_attr |
+--------------+
| foo          |
+--------------+
1 row in set (0.00 sec)

mysql> select * from another_table where another_attr=test();
Empty set, 1 warning (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1329
Message: No data - zero rows fetched, selected, or processed
1 row in set (0.00 sec)

The above may be even somehow expected - there are NO rows in test_table... But let's continue:

mysql> insert into test_table set attr='foo';
Query OK, 1 row affected (0.00 sec)

mysql> select * from another_table where another_attr=test();
Empty set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1329
Message: No data - zero rows fetched, selected, or processed
1 row in set (0.00 sec)

We just inserted one row, so function should work. This is a bug, IMHO. Actually, stored functions that change tables should NOT be allowed in SQL statements at all.

5.1.26 just does NOT allow to create a function like this:

C:\Program Files\MySQL\MySQL Server 6.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.26-rc-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DELIMITER @@
mysql> CREATE FUNCTION `test`() RETURNS char(64) CHARSET latin1
    -> BEGIN
    -> DECLARE val CHAR(64);
    -> SELECT attr INTO val FROM test_table;
    -> DELETE FROM test_table;
    -> RETURN val;
    -> END@@
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQ
L DATA in its declaration and binary logging is enabled (you *might* want to use
 the less safe log_bin_trust_function_creators variable)

> This is a bug, IMHO. Actually, stored
> functions that change tables should NOT be allowed in SQL statements at all.

> 5.1.26 just does NOT allow to create a function like this:

it is very unfortunate if capability to delete rows from tables that are unrelated to the use of the function is not anymore supported in 5.1.  without that capability, i have not found any simple solution to the problem that i have in hand.  the query is used in freeradius, which does not allow more than one mysql statement when returning attributes.