MySQL Bugs: #44249: Errors in aggregate UDF-calling protocol with multiple calls

Bug #44249	Errors in aggregate UDF-calling protocol with multiple calls
Submitted:	13 Apr 2009 22:16	Modified:	4 Jun 2009 5:45
Reporter:	Wes Munsil	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: User-defined functions ( UDF )	Severity:	S1 (Critical)
Version:	5.0.45	OS:	MacOS (10.5.6)
Assigned to:		CPU Architecture:	Any

Description:
Given the aggregate function avgcost defined in the file udf_example.c in the MySQL distribution and this table

mysql> select * from foo;
+----------+-------+
| quantity | price |
+----------+-------+
|       10 |    40 | 
|        5 |    30 | 
|        7 |    50 | 
+----------+-------+
3 rows in set (0.00 sec)

the query

select avgcost(quantity, price) - avgcost(quantity, price) from foo;

sometimes returns the correct result

+-----------------------------------------------------+
| avgcost(quantity, price) - avgcost(quantity, price) |
+-----------------------------------------------------+
|                                              0.0000 | 
+-----------------------------------------------------+

but other times returns

+-----------------------------------------------------+
| avgcost(quantity, price) - avgcost(quantity, price) |
+-----------------------------------------------------+
|                                                NULL | 
+-----------------------------------------------------+

or even

+-------------------------------------------------------+
| avgcost (quantity, price) - avgcost (quantity, price) |
+-------------------------------------------------------+
|                                                  NULL | 
|                                                  NULL | 
|                                                  NULL | 
+-------------------------------------------------------+

On instrumenting the source code for avgcost, I see that when the correct result is returned the sequence of calls is as expected:

in avgcost_init
in avgcost_init
in avgcost_clear
in avgcost_add
in avgcost_clear
in avgcost_add
in avgcost_add
in avgcost_add
in avgcost_add
in avgcost_add
in avgcost
in avgcost
in avgcost_deinit
in avgcost_deinit

but when the single row of NULL is returned the sequence of calls is

in avgcost_init
in avgcost_init
in avgcost_clear
in avgcost_add
in avgcost_add
in avgcost_add
in avgcost
in avgcost
in avgcost_deinit
in avgcost_deinit

and when the three rows of NULL are returned the sequence of calls is

in avgcost_init
in avgcost_init
in avgcost
in avgcost
in avgcost
in avgcost
in avgcost
in avgcost
in avgcost_deinit
in avgcost_deinit

Now, it is true that in one of the examples above, a space appears between the function name and the left parenthesis, which I understand is verboten. However, no error message was given and the function was definitely called, as we see from the trace.

Though I collected this information on OS X 10.5.6, I have seen similar results on Centos 5.2.

How to repeat:
Please see the Description.

Suggested fix:
A workaround is to pull out results of individual calls into a temp table, and rewrite the expression to use those results instead. But this is clearly undesirable.

Thank you for the report.

I can not repeat described behavior. Additonally version you use is old. Please try with current version 5.0.77 and if problem still exists provide output of SHOW CREATE TABLE foo

Thank you. I will try to install 5.0.77 and then try to reproduce the error. In the meantime, if you are interested, the output of that is this:

mysql> show create table foo;
+-------+------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                 |
+-------+------------------------------------------------------------------------------------------------------------------------------+
| foo   | CREATE TABLE `foo` (
  `quantity` int(11) default NULL,
  `price` double default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 
+-------+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

Thank you for the feedback.

I can not repeat described behavior with your table definition as well, although I get wrong, but different from yours, results with version 5.0.51. so I set the status of the report as "Need feedback" and will wait when you upgrade.

test case

Attachment: bug44249.test (application/octet-stream, text), 1.01 KiB.

option file

Attachment: bug44249-master.opt (application/octet-stream, text), 21 bytes.

So far, I have not been able to reproduce this bug with 5.0.77 under Mac OS X. But I'd like to leave it open for a few more days, until I have a chance to do more thorough testing with 5.0.77 under CentOS 5.2. Thank you.

Thank you for the feedback.

I will set status of the report to "Need feedback" and will wait results of your tests on CentOS.

Please forgive the stupid question. I'm looking at http://dev.mysql.com/downloads/mysql/5.0.html. Which one do I want for CentOS 5.2?

$ cat /etc/redhat-release
CentOS release 5.2 (Final)

$ uname -a
Linux xxxxx 2.6.18-028stab060.8 #1 SMP Mon Feb 9 20:25:36 MSK 2009 x86_64 x86_64 x86_64 GNU/Linux

So... does that question not have an easy answer, then?

Thank you for the feedback.

Sorry: was out of home.

You can download Red Hat or Generic Linux version.

Thanks. I plan to test this soon.

Thank you for the feedback.

We will wait results from you.

OK, I tried with community-5.0.82-0.rhel5.x86_64 on Centos 5.2. So far, it appears to work. You may close this... I will reopen if I discover any problems down the line.

Thanks very much for your support.

Thank you for the feedback.

Closed as suggested.