MySQL Bugs: #25373: rollup grand total not have NULL for group by field basd on stored function

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

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.

mysqltest test case

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

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.

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.

Pushed to 5.0.38, 5.1.17

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.