GROUP_CONCAT(DISTINCT) and JSON_ARRAYAGG(DISTINCT) return a truncated result with no diagnostic when the merge buffer for the walk of the duplicate filter is too small

GROUP_CONCAT(DISTINCT x) and JSON_ARRAYAGG(DISTINCT x) without ORDER BY build their result by walking the Unique object that filtered the duplicates out. That walk merges back whatever the object spilled to disk, so it can fail on its own. Item_func_group_concat::val_str() discards the return value of the walk, so the aggregate returns however much of the group had been appended before the failure, which can be nothing at all, and the statement reports success.

Most of those failures still reach the user, because the step that failed reports itself. The merge buffer is allocated with MY_WME, and the spill file is opened with MY_WME, so running out of memory or failing to read raises an error and the statement is aborted. Discarding the return value costs nothing there.

One failure is silent. The guard at the top of merge_walk() returns the error value without raising anything when the merge buffer it was given cannot hold at least one key from every chunk. The walk then delivers no keys at all, and because the return value is discarded the aggregate returns a short or empty result, with no warning and no error, and the statement succeeds.

The existing cut value warning does not cover this. It is raised by dump_leaf_key(), the callback that appends the rows, when the result reaches group_concat_max_len. A walk that never delivers a key does not get the callback far enough to reach that limit, so the result is both wrong and quiet.

How to repeat

The walk only merges when the Unique has spilled, and the buffer is sized from max_in_memory_size, so reaching the guard on real data needs a very wide key. On a debug build, with one line added to Unique::walk() in sql/uniques.cc, immediately after the early return that handles a tree still entirely in memory:

  DBUG_EXECUTE_IF("unique_walk_merge_fail", return 1;);

CREATE TABLE t1 (a VARCHAR(100));

INSERT INTO t1 SELECT LPAD(seq MOD 200, 100, '0') FROM seq_1_to_600;

SET @@tmp_memory_table_size=0;

SET SESSION debug_dbug='+d,unique_walk_merge_fail';

SELECT LENGTH(GROUP_CONCAT(DISTINCT a)) AS gc_len FROM t1;

SELECT JSON_LENGTH(JSON_ARRAYAGG(DISTINCT a)) AS ja_len FROM t1;

gc_len

0

ja_len

0

Both aggregates lose the whole group, and the client is shown no warning and no error.

The same two queries with tmp_memory_table_size and debug_dbug back at their defaults, so that the filter never spills and the walk never merges, are the control:

gc_len

20199

ja_len

200

Root cause

The return value is not examined:

/* sql/item_sum.cc */

String* Item_func_group_concat::val_str(String* str)

{

  DBUG_ASSERT(fixed());

  if (null_value)

    return 0;

  if (!result_finalized) // Result yet to be written.

  {

    if (tree != NULL) // order by

      tree_walk(tree, &dump_leaf_key, this, left_root_right);

    else if (distinct) // distinct (and no order by).

      unique_filter->walk(table, &dump_leaf_key, this);

Every failure of the walk is reported through exactly that value, and the two that raise an error of their own can be seen doing so. The spill file is opened with MY_WME:

/* sql/uniques.cc, Unique::Unique() */

  (void) open_cached_file(&file, mysql_tmpdir, TEMP_PREFIX, DISK_CHUNK_SIZE,

                          MYF(MY_WME | MY_TRACK_WITH_LIMIT));

and so is the merge buffer:

/* sql/uniques.cc, Unique::walk() */

  if (elements == 0)                       /* the whole tree is in memory */

    return tree_walk(&tree, action, walk_action_arg, left_root_right);

  sort.return_rows= elements+tree.elements_in_tree;

  /* flush current tree to the file to have some memory for merge buffer */

  if (flush())

    return 1;

  if (flush_io_cache(&file) || reinit_io_cache(&file, READ_CACHE, 0L, 0, 0))

    return 1;

  ...

  if (!(merge_buffer = (uchar *)my_malloc(key_memory_Unique_merge_buffer,

                                     buff_sz, MYF(MY_THREAD_SPECIFIC|MY_WME))))

    return 1;

The guard that is silent is the first statement of merge_walk(). It refuses a merge buffer that cannot hold one key from every chunk plus one spare, and returns without raising anything:

/* sql/uniques.cc, merge_walk() */

  if (end <= begin ||

      merge_buffer_size < (size_t) (key_length * (end - begin + 1)) ||

      init_queue(&queue, (uint) (end - begin),

                 offsetof(Merge_chunk, m_current_key), 0,

                 buffpek_compare, &compare_context, 0, 0))

    return 1;

merge_walk() starts with its result set to the error value and clears it only after the merge loop has delivered every key, so any exit before that point means keys were never delivered:

/* sql/uniques.cc, merge_walk() */

  int res= 1;

  ...

  res= 0;

end:

  delete_queue(&queue);

  return res;

One property of the return value is worth recording, because it is not obvious from the signature: a non-zero result does not always mean a failure. merge_walk() also returns it when the callback asked for the walk to stop, which dump_leaf_key() does after cutting the result at group_concat_max_len, and after an exhausted LIMIT. The first of those reports itself and the second loses nothing.

Affected versions

MDEV-11563 (a006e88cac0, Varun Gupta, 2020-03-21) changed GROUP_CONCAT(DISTINCT) without ORDER BY to build its result from the duplicate filter in val_str() rather than while rows are added. The return value has been discarded since that commit, which was first released in 10.5.4.

The statement is present unchanged, verified by reading each branch:

• 10.5, sql/item_sum.cc:4470
• 10.11, sql/item_sum.cc:4496
• 11.4, sql/item_sum.cc:4496
• main, sql/item_sum.cc:4500

The symptom above was reproduced on a build of the current development branch only. Other branches were not run.

Note

Found while working on MDEV-21879. JSON_ARRAYAGG is affected because it inherits this code path from GROUP_CONCAT.