| 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: | |
| 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 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.
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.