Bug #6796 Triggers and stored procedures allow meaningless AVG()

• Submitted: 24 Nov 2004 14:27
• Modified: 29 Sep 2008 20:55
• Reporter: Peter Gulutzan
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.2-alpha-debug
• OS: Linux (SuSE 8.2)
• CPU Architecture: Any

[24 Nov 2004 14:27] Peter Gulutzan

Description:
I can use set functions like AVG() in a stored procedure or function or trigger, even where 
there is no SELECT and no context which implies grouping. For example I can say 
set new.s1 = avg(new.s1*5)*5; 
in a trigger, and I can say 
return avg(param1*5)*5; 
in a function. Perhaps this would be harmless if "AVG(x) = x" but it's not. 
Meaningless or inappropriate function usage should be illegal. 
 

How to repeat:
mysql> create table t44 (s1 int); 
Query OK, 0 rows affected (0.74 sec) 
 
mysql> create trigger t44_bi before insert on t44 for each row set new.s1 = avg(new.s1*5)*5; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> insert into t44 values (10); 
Query OK, 1 row affected (0.00 sec) 
 
mysql> select * from t44; 
+------+ 
| s1   | 
+------+ 
|   10 | 
+------+ 
1 row in set (0.00 sec) 
 
mysql> create function f44 (param1 int) returns int return avg(param1*5)*5; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> select f44(10); 
+---------+ 
| f44(10) | 
+---------+ 
|    NULL | 
+---------+ 
1 row in set (0.00 sec)

[25 Nov 2004 10:14] Per-Erik Martin

This is not a stored procedure or trigger specific issue:

mysql> set @x = AVG(@y);
Query OK, 0 rows affected (0.00 sec)

mysql> select @x;
+------+
| @x   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> set @y = 3;
Query OK, 0 rows affected (0.00 sec)

mysql> set @x = AVG(@y);
Query OK, 0 rows affected (0.00 sec)

mysql> select @x;
+------+
| @x   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql>

[29 Sep 2008 20:23] Konstantin Osipov

I can't repeat it with 6.0.8, needs to be re-verified.

[29 Sep 2008 20:55] MySQL Verification Team

I couldn't repeat too.