| Bug #30503 | Aggregate UDF broken; grouping undone if used as function argument | ||
|---|---|---|---|
| Submitted: | 20 Aug 2007 8:13 | Modified: | 29 Feb 2008 14:55 |
| Reporter: | Roland Bouman | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: User-defined functions ( UDF ) | Severity: | S2 (Serious) |
| Version: | 5.0.45,5.1.20, 5.1.19 | OS: | Linux (Kubuntu Feisty) |
| Assigned to: | Jess Balint | CPU Architecture: | Any |
[20 Aug 2007 8:13]
Roland Bouman
Source file containing str_agg function
Attachment: lib_mysqludf_str.c (text/x-csrc), 5.22 KiB.
[20 Aug 2007 8:16]
Roland Bouman
tar.gz contains C source, SQL to install the UDF, linux x86 binary, and Makefile (please edit for your system)
Attachment: lib_mysqludf_str.tar.gz (application/x-gzip, text), 4.92 KiB.
[20 Aug 2007 20:03]
Jess Balint
Seeing same behaviour with avgcost, the example aggregate UDF from udf_example.c. Shown below in MySQL 5.1.19 on Linux: (root@newton77) [test]> create table cost(qty int, price real); Query OK, 0 rows affected (0.02 sec) (root@newton77) [test]> insert into cost values (1,1.0); Query OK, 1 row affected (0.00 sec) (root@newton77) [test]> select avgcost(cost.qty, cost.price) from cost; +-------------------------------+ | avgcost(cost.qty, cost.price) | +-------------------------------+ | 1.0000 | +-------------------------------+ 1 row in set (0.00 sec) (root@newton77) [test]> insert into cost values (1,2.0); Query OK, 1 row affected (0.00 sec) (root@newton77) [test]> insert into cost values (1,3.0); Query OK, 1 row affected (0.00 sec) (root@newton77) [test]> select avgcost(cost.qty, cost.price) from cost; +-------------------------------+ | avgcost(cost.qty, cost.price) | +-------------------------------+ | 2.0000 | +-------------------------------+ 1 row in set (0.00 sec) (root@newton77) [test]> select char_length(avgcost(cost.qty, cost.price)) from cost; +--------------------------------------------+ | char_length(avgcost(cost.qty, cost.price)) | +--------------------------------------------+ | NULL | | NULL | | NULL | +--------------------------------------------+ 3 rows in set (0.00 sec) (root@newton77) [test]> select length(avgcost(cost.qty, cost.price)) from cost; +---------------------------------------+ | length(avgcost(cost.qty, cost.price)) | +---------------------------------------+ | NULL | | NULL | | NULL | +---------------------------------------+ 3 rows in set (0.00 sec)
[23 Oct 2007 10:26]
Georgi Kodinov
Thank you for taking the time to report a problem. Unfortunately you are not using a current version of the product you reported a problem with -- the problem might already be fixed. Please download a new version from http://www.mysql.com/downloads/ If you are able to reproduce the bug with one of the latest versions, please change the version on this bug report to the version you tested and change the status back to "Open". Again, thank you for your continued support of MySQL. Additional info: I wasn't able to repeat the bug using the latest version. Probably fixed by the fix for bug #27333. Here's what I get on 5.0.52 (BK): +CREATE AGGREGATE FUNCTION avgcost +RETURNS REAL SONAME "UDF_EXAMPLE_LIB"; +create table cost(qty int, price real); +insert into cost values (1,1.0); +select avgcost(cost.qty, cost.price) from cost; +avgcost(cost.qty, cost.price) +1.0000 +insert into cost values (1,2.0); +insert into cost values (1,3.0); +select avgcost(cost.qty, cost.price) from cost; +avgcost(cost.qty, cost.price) +2.0000 +select char_length(avgcost(cost.qty, cost.price)) from cost; +char_length(avgcost(cost.qty, cost.price)) +6 +select length(avgcost(cost.qty, cost.price)) from cost; +length(avgcost(cost.qty, cost.price)) +6
[21 Dec 2007 23:50]
Brian Aker
Please reverify this. I am hearing complaints that this is a problem.
[29 Feb 2008 14:55]
MySQL Verification Team
I am not able to repeat anymore: mysql> create aggregate function str_agg returns string soname 'lib_mysqludf_str.so'; Query OK, 0 rows affected (0.00 sec) mysql> select str_agg(name) from category; +---------------------------------------------------------------------------------------------------------+ | str_agg(name) | +---------------------------------------------------------------------------------------------------------+ | ActionAnimationChildrenClassicsComedyDocumentaryDramaFamilyForeignGamesHorrorMusicNewSci-FiSportsTravel | +---------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select concat(str_agg(name),'') from category; +---------------------------------------------------------------------------------------------------------+ | concat(str_agg(name),'') | +---------------------------------------------------------------------------------------------------------+ | ActionAnimationChildrenClassicsComedyDocumentaryDramaFamilyForeignGamesHorrorMusicNewSci-FiSportsTravel | +---------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> show variables like "%version%"; +-------------------------+---------------------+ | Variable_name | Value | +-------------------------+---------------------+ | protocol_version | 10 | | version | 5.1.24-rc-debug | | version_comment | Source distribution | | version_compile_machine | x86_64 | | version_compile_os | unknown-linux-gnu | +-------------------------+---------------------+ 5 rows in set (0.00 sec) mysql>
Description: When an aggregate UDF appears as a function argument for another, built-in function, the effect of the grouping implied by the usage of an aggregate may be completely undone. The behaviour is dependent upon which built-in function is used, but it is hard to see a pattern. The beviour can be demonstrated in 5.0.45, 5.1.19 and 5.1.20, but not every case that is broken in 5.1 is broken in 5.0 - it depends on the built-in function and how that is used. Hard to see a pattern How to repeat: str_agg is a very simple aggregate function that concatenates the arguments similar to group_concat. Code, binary and compilation instructions will be attached subseqently to this bug report. select version(); +------------+ | version() | +------------+ | 5.0.45-log | +------------+ 1 row in set (0.00 sec) use sakila; create aggregate function str_agg returns String soname 'lib_mysqludf_str.so'; select str_agg(name) from category; +---------------------------------------------------------------------------------------------------------+ | str_agg(name) | +---------------------------------------------------------------------------------------------------------+ | ActionAnimationChildrenClassicsComedyDocumentaryDramaFamilyForeignGamesHorrorMusicNewSci-FiSportsTravel | +---------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) (this is the expected result - 1 row returned, and all category names are concatenated) mysql> select concat(str_agg(name),'') from category; +---------------------------------------------------------------------------------------------------------+ | concat(str_agg(name),'') | +---------------------------------------------------------------------------------------------------------+ | ActionAnimationChildrenClassicsComedyDocumentaryDramaFamilyForeignGamesHorrorMusicNewSci-FiSportsTravel | | ActionAnimationChildrenClassicsComedyDocumentaryDramaFamilyForeignGamesHorrorMusicNewSci-FiSportsTravel | | ActionAnimationChildrenClassicsComedyDocumentaryDramaFamilyForeignGamesHorrorMusicNewSci-FiSportsTravel | | ActionAnimationChildrenClassicsComedyDocumentaryDramaFamilyForeignGamesHorrorMusicNewSci-FiSportsTravel | | ActionAnimationChildrenClassicsComedyDocumentaryDramaFamilyForeignGamesHorrorMusicNewSci-FiSportsTravel | | ActionAnimationChildrenClassicsComedyDocumentaryDramaFamilyForeignGamesHorrorMusicNewSci-FiSportsTravel | | ActionAnimationChildrenClassicsComedyDocumentaryDramaFamilyForeignGamesHorrorMusicNewSci-FiSportsTravel | | ActionAnimationChildrenClassicsComedyDocumentaryDramaFamilyForeignGamesHorrorMusicNewSci-FiSportsTravel | | ActionAnimationChildrenClassicsComedyDocumentaryDramaFamilyForeignGamesHorrorMusicNewSci-FiSportsTravel | | ActionAnimationChildrenClassicsComedyDocumentaryDramaFamilyForeignGamesHorrorMusicNewSci-FiSportsTravel | | ActionAnimationChildrenClassicsComedyDocumentaryDramaFamilyForeignGamesHorrorMusicNewSci-FiSportsTravel | | ActionAnimationChildrenClassicsComedyDocumentaryDramaFamilyForeignGamesHorrorMusicNewSci-FiSportsTravel | | ActionAnimationChildrenClassicsComedyDocumentaryDramaFamilyForeignGamesHorrorMusicNewSci-FiSportsTravel | | ActionAnimationChildrenClassicsComedyDocumentaryDramaFamilyForeignGamesHorrorMusicNewSci-FiSportsTravel | | ActionAnimationChildrenClassicsComedyDocumentaryDramaFamilyForeignGamesHorrorMusicNewSci-FiSportsTravel | | ActionAnimationChildrenClassicsComedyDocumentaryDramaFamilyForeignGamesHorrorMusicNewSci-FiSportsTravel | +---------------------------------------------------------------------------------------------------------+ 16 rows in set (0.00 sec) (this is wrong! only 1 row should have been returned) Howver, this behaviour is not generically seen: mysql> select left(str_agg(name),6) from category; +-----------------------+ | left(str_agg(name),6) | +-----------------------+ | Action | +-----------------------+ 1 row in set (0.00 sec) (expected) Strangely enough, it is not so that "left does it right and concat does it wrong": select left(str_agg(name),length('Action')) from category; +--------------------------------------+ | left(str_agg(name),length('Action')) | +--------------------------------------+ | Action | | Action | | Action | | Action | | Action | | Action | | Action | | Action | | Action | | Action | | Action | | Action | | Action | | Action | | Action | | Action | +--------------------------------------+ 16 rows in set (0.00 sec) (wrong!) Similar behaviour is seen in 5.1.20 and 5.1.19 but the exact cases where the behaviour is broken varies. Suggested fix: Please treat aggregate UDF's as normal aggregate functions