Bug #18175 union with aggregate function over 128 tables returns fail.
Submitted: 13 Mar 2006 1:01 Modified: 22 Jun 2006 15:52
Reporter: dharana ashtanga Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0.18 OS:Linux (Debian 3.1r0 Kernel 2.6.8 SMP)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[13 Mar 2006 1:01] dharana ashtanga
Description:
When using union over 128 tables with aggregate function, 5.0.18+ server returns fail, 5.0.17- returns success.
ex) below query returns error on 5.0.18+
(select avg(a) as score from table_1) union
(select avg(a) as score from table_2) union
...
...
(select avg(a) as score from table_129)
order by score desc limit 10;

Actually, I am using this query over 1000 tables and it works well until 5.0.16. After upgrading 5.0.18 this query doesn't work. I checked some versions and found that only 128 tables union is available on 5.0.18+. Number of tables exceeds 128, mysql server says "Invalid use of group function" and failed. 5.0.17- versions work well over 1000 tables.

If there is no aggregate function in query, no problem on 5.0.18+.

How to repeat:
create table table_1 ( int a );
create table table_2 ( int a );
...
...
create table table_129 ( int a );

<?php
$que = '';
for($i=1; $i<=129; $i++)
    $que = "(select avg(a) as score from table_$i) union ";
$que = substr($que, 0, -6);
$que .= " order by score desc limit 10";
$result = mysql_query($que) or die(mysql_error());
while ($row = mysql_fetch_array($result))
    echo 'avg = '.$row[score];
?>

Suggested fix:
Back to 5.0.16 or 5.0.17 is the only way I found.
Please let me know there is some configuration or other answer.
[13 Mar 2006 7:10] Hartmut Holzgraefe
test case with 128 tables, works on both 5.0.17 and 5.0.18

Attachment: t128.sql (text/x-sql), 13.60 KiB.

[13 Mar 2006 7:11] Hartmut Holzgraefe
testcase with 129 tables, works on 5.0.17 and fails on .18

Attachment: t129.sql (text/x-sql), 13.71 KiB.

[13 Mar 2006 7:13] Hartmut Holzgraefe
Verified, worked on 5.0.17, broken in 5.0.18 and 5.0.19,
see attached test files
[7 Jun 2006 20:42] 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/7373
[14 Jun 2006 11:27] 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/7625
[15 Jun 2006 17:11] 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/7713
[15 Jun 2006 17:26] 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/7715
[15 Jun 2006 18:10] 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/7716
[19 Jun 2006 0:16] Evgeny Potemkin
Fixed in 4.1.21, 5.0.23, 5.1.12
[19 Jun 2006 0:22] Evgeny Potemkin
Sorry, fixed in 5.0 and 5.1 only
[20 Jun 2006 10:56] Evgeny Potemkin
The nest_level counter indicates the depth of nesting for a subselect. It is
needed to properly resolve aggregate functions in nested subselects. Obviously 
it shouldn't be incremented for UNION parts because they have the same level of
nesting. This counter was incremented by 1 in the mysql_new_select() function
for any new select and wasn't decremented for UNION parts. This resulted in
wrongly reported error messages.
[22 Jun 2006 15:52] Paul Dubois
Noted in 5.0.23, 5.1.12 changelogs.

A UNION over more than 128 SELECT statements that use an aggregate
function failed.