Bug #28273 GROUP_CONCAT and ORDER BY: No warning when result gets truncated.
Submitted: 7 May 2007 8:24 Modified: 7 Jun 2007 16:25
Reporter: Martin Hansson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0 OS:Any
Assigned to: Martin Hansson CPU Architecture:Any

[7 May 2007 8:24] Martin Hansson
Description:
GROUP_CONCAT truncates the result to GROUP_CONCAT_MAX_LEN, but the warnings are not consistent.

There is a difference in how the execution is processed. 

Without ORDER BY: Normal nested loops join, result is built incrementally with dump_leaf_key, which raises a warning flag when (if) size grows too large. This is read later.

With ORDER BY: The whole result is 'dumped' in one iteration of nested loops join by doing a tree walk that subsequently calls dump_leaf_key. However, by this time the warning flag has already been read and nothing gets sent to the client.

How to repeat:
CREATE TABLE t1( a VARCHAR( 10 ), b INT );
INSERT INTO t1 VALUES ( repeat( 'a', 10 ), 1), 
                      ( repeat( 'b', 10 ), 2);
SET GROUP_CONCAT_MAX_LEN = 20;
SELECT GROUP_CONCAT( a ) FROM t1;           # cuts 1 char and gives a warning
SELECT GROUP_CONCAT( DISTINCT a ) FROM t1;  # cuts 1 char and gives a warning
SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1;          # no warning
SELECT GROUP_CONCAT( DISTINCT a ORDER BY b ) FROM t1; # no warning

Suggested fix:
Inside Item_func_group_concat::val_str(String*), read the warning flag after doing tree_walk as well.
[7 May 2007 11:36] Miguel Solorzano
Thank you for the bug report. Verified as described.
[8 May 2007 12:29] 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/26289

ChangeSet@1.2479, 2007-05-08 14:29:03+03:00, mhansson@linux-st28.site +4 -0
  bug#28273: GROUP_CONCAT and ORDER BY: No warning when result gets truncated.
  
  When using GROUP_CONCAT with order by, a tree is used for the sorting,
  instead of normal nested loops join. But the code that outputs the warning was
  before the call to traverse the tree. Hence, there's no warning if the output 
  gets trimmed to GROUP_CONCAT_MAX_LEN in this case.
[8 May 2007 12:34] 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/26290

ChangeSet@1.2479, 2007-05-08 14:34:40+03:00, mhansson@linux-st28.site +3 -0
  bug#28273: GROUP_CONCAT and ORDER BY: No warning when result gets truncated.
  
  When using GROUP_CONCAT with order by, a tree is used for the sorting,
  instead of normal nested loops join. But the code that outputs the warning was
  before the call to traverse the tree. Hence, there's no warning if the output 
  gets trimmed to GROUP_CONCAT_MAX_LEN in this case.
[11 May 2007 16:06] 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/26501

ChangeSet@1.2479, 2007-05-11 16:05:20+03:00, mhansson@linux-st28.site +3 -0
  bug#28273: GROUP_CONCAT and ORDER BY: No warning when result gets truncated.
  
  When using GROUP_CONCAT with ORDER BY, a tree is used for the sorting, as 
  opposed to normal nested loops join used when there is no ORDER BY. 
  
  The tree traversal that generates the result counts the lines that have been 
  cut down. (as they get cut down to the field's max_size)
  But the check of that count was before the tree traversal, so no 
  warning was generated if the output is truncated.
  
  Fixed by moving the check to after the tree traversal.
[11 May 2007 16:06] 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/26504

ChangeSet@1.2479, 2007-05-11 15:38:18+03:00, mhansson@linux-st28.site +3 -0
  bug#28273: GROUP_CONCAT and ORDER BY: No warning when result gets truncated.
  
  When using GROUP_CONCAT with ORDER BY, a tree is used for the sorting, as 
  opposed to normal nested loops join used when there is no ORDER BY. 
  
  The tree traversal that generates the result counts the lines that have been 
  cut down. (as they get cut down to the field's max_size)
  But the check of that count was before the tree traversal, so no 
  warning was generated if the output is truncated.
  
  Fixed by moving the check to after the tree traversal.
[20 May 2007 17:11] Bugs System
Pushed into 5.0.44
[20 May 2007 17:13] Bugs System
Pushed into 5.1.19-beta
[7 Jun 2007 16:25] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.44 and 5.1.20 changelogs.
[6 Jun 2008 5:57] dhiraj jakkali
how to increase group_concat max length