Bug #46018 group_concat(distinct ...) uses max_heap_table_size for memory allocations

Description:
When you have a query which uses group_concat(distinct ...), then max_heap_table_size gets used for memory allocation.  It is passed into Uniques as the limit to the memory and then max_heap_table_size / 16 gets allocated immediately.

So if you have a large max_heap_table_size then a lot of memory can be used by this function.  For example, a max_heap_table_size of 8G results in 512MB of memory being used and 16G for the setting will result in 1G of memory being used.

The call path is Item_func_group_concat::setup creates a new Unique item using thd->variables.max_heap_table_size as the last parameter which is max_in_memory_size_arg for the constructor.  

This then calls init_tree with max_in_memory_size_arg / 16 for the initial allocation size.

How to repeat:
SET SESSION max_heap_table_size = 16 * 1024 * 1024 * 1024;
CREATE TABLE gc (a int, b char(100));
INSERT INTO gc VALUES (1, 'a'), (2, 'b');
SELECT a, group_concat(distinct b) FROM gc GROUP BY a;

Suggested fix:
Do not use max_heap_table_size for this.  Instead use tmp_table_size or create a new variable to manage it.

I tend to agree with Harrison, max_heap_table_size according to the manual has completely unrelated purpose. tmp_table_size looks more appropriate

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

3146 Alexey Botchkov	2009-10-01
      Bug#46018      group_concat(distinct ...) uses max_heap_table_size for memory allocations
          Use min(max_heap_table_size, tmp_table_size) instead
      
      per-file comments:
        sql/item_sum.cc
      Bug#46018      group_concat(distinct ...) uses max_heap_table_size for memory allocations
          Item_sum_**::setup fixed, so they use ram_limitation() for Unique-s they embed
      
        sql/item_sum.h
      Bug#46018      group_concat(distinct ...) uses max_heap_table_size for memory allocations
         Item_sum::ram_limitation() declared

The patch http://lists.mysql.com/commits/85369 looks Ok to push.

pushed into mysql-next-mr-bugfixing

Pushed into 6.0.14-alpha (revid:kostja@sun.com-20091120124947-yi6h2jbgw0kbciwm) (version source revid:holyfoot@mysql.com-20091117114617-75quk99yr1coxv8c) (merge vers: 6.0.14-alpha) (pib:13)

Noted in 6.0.14 changelog.

For queries that used GROUP_CONCAT(DISTINCT ...), the value of
max_heap_table_size was used for memory allocation, which could be
excessive. Now the minimum of max_heap_table_size and tmp_table_size
is used. 

Setting report to NDI pending push to 5.6.x.

Pushed into 5.6.0-beta (revid:alik@sun.com-20091211055628-ltr7fero363uev7r) (version source revid:alik@sun.com-20091211055453-717czhtezc74u8db) (merge vers: 5.6.0-beta) (pib:13)

Noted in 5.6.0 changelog.

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20091211201717-03qf8ckwiw0np80p) (merge vers: 5.6.0-beta) (pib:16)

Moved 5.6.0 changelog entry to 5.5.3.