Bug #29582 huge memory consumption with union, subselect, joins

Description:
A query in this form uses a few hundred MB of ram during execution.
So, for a 32-bit server, you'll be out of memory if two clients execute this at the same time:

I narrowed down the culprit to be the UNION in the derived table.  Without the UNION, the memory doesn't get wasted as much.

The general form of the query is:

SELECT  SQL_CALC_FOUND_ROWS 
        
        COUNT(DISTINCT ...),
        IF(SUM(DISTINCT ...) IS NOT NULL, SUM(DISTINCT ...), 0), 
        IF(... IS NOT NULL, true, false)
FROM
        t1
        INNER JOIN ...
        INNER JOIN ...
        LEFT OUTER JOIN
        (
                SELECT DISTINCT ...
                FROM 
                       ...
                WHERE 
                        ...=5 AND ...=1
        ) ... ON ...
        
WHERE 
(
        ...=true OR 
        ... IS NULL
)  AND 

... IN 
(
        SELECT ....
        FROM
                .....
                INNER JOIN ... ON .....
                INNER JOIN ... ON .....
        WHERE 
                ....=1 AND 
                (..., ...)=(5, 1) AND 
                ...=0 
                
        UNION
        
        SELECT .....
        FROM 
                .....
                INNER JOIN ... ON ......
                INNER JOIN ... ON ......
                INNER JOIN ... ON ...... 
        WHERE 
                ...=2 AND 
                ...=1 AND 
                ...=5
        
)  AND 
...>'2007-02-02 11:09:13.75' 
GROUP BY 
        ....
ORDER BY 
        ... DESC 
LIMIT 20

How to repeat:
simpler testcase will be uploaded later.

Suggested fix:
.

the query above takes 20 seconds on a release build of mysqld, but >10 minutes on a full debug build.  I don't know why that would be..

Ok, the memory consumption is caused by this call:

   mysql_select( various fake_select_lex's members )

this invokes JOIN::optimize which allocates memory on the execution MEM_ROOT. When the union is within the subquery, this causes a total of O(#subselect-executions) memory to be allocated.

The fix seems to be to replace the generic mysql_select() call with 

  if (optimization already done) 
  {
     join->exec();
     // some join->cleanup()-like call here
  }

which is known not to consume memory.

The ETA is 2007-07-27.

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/31817

ChangeSet@1.2532, 2007-07-30 17:12:34+04:00, sergefp@mysql.com +3 -0
  BUG#29582: huge memory consumption with union, subselect, joins:
  - Don't call mysql_select() several times for the select that enumerates
    the temporary table with the results of the UNION. Making this call for
    every subquery execution caused O(#enumerated-rows-in-the-outer-query) 
    memory allocations.
  - Instead, call join->reinit() and join->exec(), and
    = disable constant table detection for such joins,  
    = provide special handling for table-less constant subqueries.

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/31818

ChangeSet@1.2532, 2007-07-30 17:19:49+04:00, sergefp@mysql.com +3 -0
  BUG#29582: huge memory consumption with union, subselect, joins:
  - Don't call mysql_select() several times for the select that enumerates
    the temporary table with the results of the UNION. Making this call for
    every subquery execution caused O(#enumerated-rows-in-the-outer-query) 
    memory allocations.
  - Instead, call join->reinit() and join->exec(), and
    = disable constant table detection for such joins,  
    = provide special handling for table-less constant subqueries.

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/31884

ChangeSet@1.2532, 2007-07-31 15:19:06+04:00, sergefp@mysql.com +3 -0
  BUG#29582: huge memory consumption with union, subselect, joins:
  - Don't call mysql_select() several times for the select that enumerates
    a temporary table with the results of the UNION. Making this call for
    every subquery execution caused O(#enumerated-rows-in-the-outer-query) 
    memory allocations.
  - Instead, call join->reinit() and join->exec(), and
    = disable constant table detection for such joins,  
    = provide special handling for table-less constant subqueries.

Pushed into 5.0-opt at 2007-07-31 13:46:41.

Pushed into 5.1.21-beta

Pushed into 5.0.48

Noted in 5.0.48, 5.1.21 changelogs.

Certain statements with unions, subqueries, and joins could
result in huge memory consumption.