MySQL Bugs: #19290: NULL results from UDF agregate function combined with other functions

Bug #19290	NULL results from UDF agregate function combined with other functions
Submitted:	24 Apr 2006 7:33	Modified:	23 Oct 2007 15:49
Reporter:	Wojciech Meler	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.20-max/5.0BK/5.1BK	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
For query that use result from UDF agregate function as an argument to other function mysql does not follow calling sequence described in manual section 27.3.4.2. - UDF Calling Sequences for Aggregate Functions (_reset and _add functions are not called) which causes NULLs in results set.

When run under valgrind I get following messages:
==1877== Conditional jump or move depends on uninitialised value(s)
==1877==    at 0x401CB7B: ???
==1877==    by 0x812C40E: Item_sum_udf_float::val_real() (in /usr/sbin/mysqld-max)
==1877==    by 0x8156F66: Item_func_hex::val_str(String*) (in /usr/sbin/mysqld-max)
==1877==    by 0x8124ADC: Item::send(Protocol*, String*) (in /usr/sbin/mysqld-max)
==1877==    by 0x8183D55: select_send::send_data(List<Item>&) (in /usr/sbin/mysqld-max)
==1877==    by 0x81E29AF: (within /usr/sbin/mysqld-max)
==1877==    by 0x81DEEA7: (within /usr/sbin/mysqld-max)
==1877==    by 0x81DEFC9: sub_select(JOIN*, st_join_table*, bool) (in /usr/sbin/mysqld-max)
==1877==    by 0x81DF312: (within /usr/sbin/mysqld-max)
==1877==    by 0x81ED533: JOIN::exec() (in /usr/sbin/mysqld-max)
==1877==    by 0x81EE47B: mysql_select(THD*, Item***, st_table_list*, unsigned, List<Item>&, Item*, unsigned, st_order*, st_order*, Item*, st_order*, unsigned long, select_result*, st_select_lex_unit*, st_select_lex*) (in /usr/sbin/mysqld-max)
==1877==    by 0x81EEAF4: handle_select(THD*, st_lex*, select_result*, unsigned long) (in /usr/sbin/mysqld-max) 

How to repeat:
Compile and install example functions (sql/udf_example.cc)

CREATE TABLE `avgcost_test` (
  `k1` smallint(6) NOT NULL default '0',
  `k2` tinyint(4) NOT NULL default '0',
  `q` int(11) default NULL,
  `p` double default NULL,
  PRIMARY KEY  (`k1`,`k2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `avgcost_test` VALUES (1,2,3,4.5),(1,3,4,5.6); 
mysql> select avgcost(q,p) from avgcost_test group by k1;
+--------------+
| avgcost(q,p) |
+--------------+
| 5.1286       |
+--------------+ 

mysql> select sqrt(avgcost(q,p)) from avgcost_test group by k1;
+--------------------+
| sqrt(avgcost(q,p)) |
+--------------------+
| NULL               |
+--------------------+

Thank you for the bug report. I was able to repeat on 5.0/5.1. The
version 4.1 not presents that bug.

mysql> CREATE AGGREGATE FUNCTION avgcost RETURNS REAL SONAME 'udf_example.so';
Query OK, 0 rows affected (0.00 sec)

mysql> select avgcost(q,p) from avgcost_test group by k1;
+--------------+
| avgcost(q,p) |
+--------------+
|       5.1286 | 
+--------------+
1 row in set (0.01 sec)

mysql> select sqrt(avgcost(q,p)) from avgcost_test group by k1;
+--------------------+
| sqrt(avgcost(q,p)) |
+--------------------+
|               NULL | 
+--------------------+
1 row in set (0.01 sec)
--------------------------------------------------------------------
mysql> select avgcost(q,p) from avgcost_test group by k1;
+--------------+
| avgcost(q,p) |
+--------------+
|       5.1286 |
+--------------+
1 row in set (0.02 sec)

mysql> select sqrt(avgcost(q,p)) from avgcost_test group by k1;
+--------------------+
| sqrt(avgcost(q,p)) |
+--------------------+
|     2.264634943776 |
+--------------------+
1 row in set (0.00 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.1.19-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql>

We can reproduce this problem as well. Our MEDIAN() implementation is nearly useless because of it. I'd think that making aggregate UDF's non-functional in non-trivial queries would merit something more severe than a "Non-critical" severity.

checked on latest 5.0 tree, can't repeat

Bug is no longer repeatable with versions 5.0 and 5.1