Bug #25373 rollup grand total not have NULL for group by field basd on stored function
Submitted: 2 Jan 2007 19:48 Modified: 15 Mar 2007 15:32
Reporter: Hongliang Qiang Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.30, 5.1bk OS:Linux (linux)
Assigned to: Evgeny Potemkin CPU Architecture:Any
Tags: GROUP BY, null, stored function, with rollup

[2 Jan 2007 19:48] Hongliang Qiang
Description:
When the "group by" column of a select statement is based on a stored function return value, and "with rollup" is used, the "group by" column in the grand total row will take the value of the counterpart in last of the individual rows, rather than the "NULL" value as it should be.

How to repeat:
delimiter //
create function test_func(d1 integer) returns integer
language SQL deterministic
return  d1;
//

delimiter ;

create table test_table (fld1 int, fld2 float);

insert into test_table values (1, 3.4), (1, 2), (1, 0.9), (2, 8), (2, 7);

select sum(fld2), test_func(fld1) from test_table group by test_func(fld1) with rollup;
+-----------------+-----------------+
| sum(fld2)       | test_func(fld1) |
+-----------------+-----------------+
| 6.3000000715256 | 1               |
| 15              | 2               |
| 21.300000071526 | 2               |
+-----------------+-----------------+
3 rows in set (0.02 sec)

Suggested fix:
Using built-in function instead of user-defined stored function will render expected result, e.g.

select sum(fld2), sqrt(fld1) from test_table group by sqrt(fld1) with rollup;
+-----------------+-----------------+
| sum(fld2)       | sqrt(fld1)      |
+-----------------+-----------------+
| 6.3000000715256 | 1               |
| 15              | 1.4142135623731 |
| 21.300000071526 | NULL            |
+-----------------+-----------------+

Fix needs to be made so that using user-defined stored function render similar result, i.e. the test_func(fld1) in last row should be NULL in "how to repeat" section.
[2 Jan 2007 23:36] Hartmut Holzgraefe
mysqltest test case

Attachment: bug25373.tgz (application/x-gtar, text), 920 bytes.

[7 Mar 2007 16:13] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21380

ChangeSet@1.2432, 2007-03-07 19:10:16+03:00, evgen@moonbone.local +3 -0
  Bug#25373: Stored functions wasn't compared correctly which leads to a wrong
  result.
  
  For built-in functions like sqrt() function names are hard-coded and can be
  compared by pointer. But this isn't the case for a used-defined stored
  functions - names there are dynamical and should be compared using the
  strcmp() function.
  
  Now the Item_func::eq() function employs strcmp() function to compare
  used-defined stored functions names.
[7 Mar 2007 19:13] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21417

ChangeSet@1.2432, 2007-03-07 22:11:57+03:00, evgen@moonbone.local +3 -0
  Bug#25373: Stored functions wasn't compared correctly which leads to a wrong
  result.
  
  For built-in functions like sqrt() function names are hard-coded and can be
  compared by pointer. But this isn't the case for a used-defined stored
  functions - names there are dynamical and should be compared as strings.
  
  Now the Item_func::eq() function employs my_strcasecmp() function to compare
  used-defined stored functions names.
[12 Mar 2007 5:09] Igor Babaev
Pushed to 5.0.38, 5.1.17
[15 Mar 2007 15:32] Paul DuBois
Noted in 5.0.38, 5.1.17 changelogs.

Use of a GROUP BY clause that referred to a stored function result
together with WITH ROLLUP caused incorrect results.