Bug #14138 ROLLUP and PROCEDURE ANALYSE() hang server (kill -SIGINT doesn't help)
Submitted: 19 Oct 2005 12:09 Modified: 31 Oct 2005 19:22
Reporter: Konstantin Osipov (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1/5.0.XX OS:Linux (SuSE 9.1)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[19 Oct 2005 12:09] Konstantin Osipov
Description:
Seems that ROLLUP and PROCEDURE ANALYSE() do not work together.
See how-to-repeat section for details.

How to repeat:
drop table t1,t2;
create table t1 (product varchar(32), country_id int not null, year int,
                 profit int);
insert into t1  values ( 'Computer', 2,2000, 1200),
    ( 'TV', 1, 1999, 150),
    ( 'Calculator', 1, 1999,50),
    ( 'Computer', 1, 1999,1500),
    ( 'Computer', 1, 2000,1500),
    ( 'TV', 1, 2000, 150),
    ( 'TV', 2, 2000, 100),
    ( 'TV', 2, 2000, 100),
    ( 'Calculator', 1, 2000,75),
    ( 'Calculator', 2, 2000,75),
    ( 'TV', 1, 1999, 100),
    ( 'Computer', 1, 1999,1200),
    ( 'Computer', 2, 2000,1500),
    ( 'Calculator', 2, 2000,75),
    ( 'Phone', 3, 2003,10)
    ;
create table t2 (country_id int primary key, country char(20) not null); 
insert into t2 values (1, 'USA'),(2,'India'), (3,'Finland');

select product, sum(profit),avg(profit) from t1 group by product with rollup;

+------------+-------------+-------------+
| product    | sum(profit) | avg(profit) |
+------------+-------------+-------------+
| Calculator |         275 |     68.7500 |
| Computer   |        6900 |   1380.0000 |
| Phone      |          10 |     10.0000 |
| TV         |         600 |    120.0000 |
| NULL       |        7785 |    519.0000 |
+------------+-------------+-------------+

select product, sum(profit),avg(profit)   
from t1 group by product with rollup procedure analyse()
-- at this point the server goes into an infinite loop (100% cpu, killall
-- mysqld doesn't help)

Suggested fix:
Document the interface of PROCEDUREs, probably redesign to allow development of other server features easily.
[19 Oct 2005 12:30] MySQL Verification Team
Also on 5.0.16 BK source.
[19 Oct 2005 13:20] Konstantin Osipov
See also Bug#13673 "PROCEDURE ANALYSE() in stored procedure/prepared statements crashes MySQL"
[25 Oct 2005 21:41] 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/internals/31471
[26 Oct 2005 17:54] 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/internals/31526
[26 Oct 2005 20:58] 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/internals/31530
[28 Oct 2005 13:57] Evgeny Potemkin
Procedure analyse() redefines select's fields_list. setup_copy_fields() assumes
that fields_list is a part of all_fields_list. Because select have only 
3 columns and analyse() redefines it to have 10 columns, int overrun in
setup_copy_fields() occurs and server goes to almost infinite loop.

Fixed in 4.1.16, cset 1.2451
[28 Oct 2005 22:45] Evgeny Potemkin
Fixed in 5.0.16
[31 Oct 2005 19:22] Paul DuBois
Noted in 4.1.16, 5.0.16 changelogs.
[15 Nov 2005 16:15] 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/internals/32276