Hi!
I have problems with UDF aggregate functions under 5.0.20-max.
It can be reproduced using example avgcost function.
For the following database:
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);
When I use avgcost alone it works ok:
mysql> select avgcost(q,p) from avgcost_test group by k1;
+--------------+
| avgcost(q,p) |
+--------------+
| 5.1286 |
+--------------+
but combining it with other function produces error:
mysql> select sqrt(avgcost(q,p)) from avgcost_test group by k1;
+--------------------+
| sqrt(avgcost(q,p)) |
+--------------------+
| NULL |
+--------------------+
I can see difference in execution plan - second one is using temporary
table:
mysql> explain select avgcost(q,p) from avgcost_test group by k1;
+----+-------------+--------------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | avgcost_test | ALL | | |
| | 2 | Using filesort |
+----+-------------+--------------+------+---------------+------+---------+------+------+----------------+
mysql> explain select sqrt(avgcost(q,p)) from avgcost_test group by k1;
+----+-------------+--------------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------+------+---------+------+------+---------------------------------+
| 1 | SIMPLE | avgcost_test | ALL | | |
| | 2 | Using temporary; Using filesort |
+----+-------------+--------------+------+---------------+------+---------+------+------+---------------------------------+
When I run mysql under valgrind I get following errors:
==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)
I also run mysql under gdb and have noticed that when I run 'select
sqrt(avgcost(q,p)) from avgcost_test group by k1' avgcost_add and
avgcost_reset functions are not called - that's why it returns bad values.
Shall I report it as bug or is it normal behavior ?
regards,
Wojtek
