The JSON aggregates do not truncate to a valid document: JSON_OBJECTAGG ignores group_concat_max_len entirely, JSON_ARRAYAGG cuts mid-element

Neither JSON aggregate truncates to a document. They fail in opposite
directions and the two halves are reported together because one fix
answers both: a group that must be cut should be cut at a boundary
between elements, leaving a value that still reads as JSON.

• JSON_OBJECTAGG ignores group_concat_max_len completely. It accumulates
  one pair per row for as many rows as the group has, with no bound and no
  diagnostic, and stops only when the allocator refuses to grow the buffer.
• JSON_ARRAYAGG does honour the setting, but cuts at a byte offset rather
  than at an element boundary. Depending on where the cut lands the result
  is either not JSON at all, or - worse - valid JSON containing an element
  that was never in the data.

JSON_OBJECTAGG ignores the cap entirely

Two thousand rows of 200 bytes each, with the cap set to 1024:

CREATE TABLE t (k VARCHAR(32), v VARCHAR(200));

INSERT INTO t SELECT CONCAT('k', seq), REPEAT('x', 200) FROM seq_1_to_2000;

SET SESSION group_concat_max_len = 1024;

SELECT LENGTH(JSON_ARRAYAGG(v))    FROM t;

SELECT LENGTH(JSON_OBJECTAGG(k,v)) FROM t;

LENGTH(JSON_ARRAYAGG(v))

1026

Warnings:

Warning  1260  Row 6 was cut by JSON_ARRAYAGG()

LENGTH(JSON_OBJECTAGG(k,v))

422893

<no warnings>

Lowering the cap to its smallest legal value changes nothing:

SET SESSION group_concat_max_len = 4;

SELECT LENGTH(JSON_ARRAYAGG(v))    FROM t;   -- 6, cut at row 1

SELECT LENGTH(JSON_OBJECTAGG(k,v)) FROM t;   -- 422893, unchanged

The result grows with the data rather than with the setting. Same query,
same cap of 1024 throughout, only the row count varying:

LENGTH() sends a single integer to the client, so the whole buffer is built
server side and max_allowed_packet is never consulted. The same holds for
any use that does not return the value to a client - assignment to a
routine variable, an INSERT ... SELECT, a materialised derived table. In
those the value is never sent anywhere, so nothing bounds it at all.

None of the session variables that might be expected to apply do:

group_concat_max_len   1024                   ignored by JSON_OBJECTAGG

max_allowed_packet     16777216               only consulted when sending

max_session_mem_used   9223372036854775807    default is effectively none

max_session_mem_used would not help even if lowered: the buffer is not
allocated MY_THREAD_SPECIFIC, so it is not counted against the session's
MEM_USED in the first place.

JSON_ARRAYAGG cuts at a byte offset, not at an element

Five four-character values, with the cap swept across every offset so the
cut lands at each position within an element in turn:

CREATE TABLE t (v VARCHAR(20));

INSERT INTO t VALUES ('aaaa'),('bbbb'),('cccc'),('dddd'),('eeee');

SET SESSION group_concat_max_len = 7;    -- and 8, 9, ... in turn

SELECT JSON_VALID(JSON_ARRAYAGG(v)), JSON_ARRAYAGG(v) FROM t;

The pattern repeats with the width of an element: of the 26 offsets swept,
8 produced text that is not JSON and 3 produced the fabricated element
described below.

Numeric elements fail the same way, one offset in five:

Three distinct malformations appear: a doubled quote, a trailing separator,
and a trailing separator followed by an opening quote.

JSON_ARRAYAGG invents elements that were never in the data

The rows at caps 9, 16 and 23 above are the serious ones. They are valid
JSON, so nothing downstream rejects them, and they contain an empty string
element that no row of the table ever held:

cap 9    ["aaaa",""]                    <- the second element is invented

cap 16   ["aaaa","bbbb",""]             <- likewise

cap 23   ["aaaa","bbbb","cccc",""]      <- likewise

The table contains 'aaaa', 'bbbb', 'cccc', 'dddd', 'eeee' and no empty
string. A consumer counting elements, or iterating them, sees a row that
does not exist. Warning 1260 is raised, but it says a row was cut, not that
one was manufactured.

Root cause

JSON_ARRAYAGG derives from Item_func_group_concat and so inherits the check
GROUP_CONCAT performs once per row, which cuts at a byte offset because for
GROUP_CONCAT the result has no structure to respect:

/* sql/item_sum.cc */

  /* stop if length of result more than max_length */

  if (result->length() > max_length)

  {

    THD *thd= current_thd;

    item->cut_max_length(result, old_length, max_length);

    item->warning_for_row= TRUE;

    report_cut_value_error(thd, item->row_count, item->func_name());

    ...

    return 1;

  }

JSON_ARRAYAGG overrides cut_max_length, but the override only repairs the
single case of a cut that lands immediately after a quote, by backing off
one byte and writing a quote back:

/* sql/item_jsonfunc.cc */

void Item_func_json_arrayagg::cut_max_length(String *result,

       uint old_length, uint max_length) const

{

  if (result->length() == 0)

    return;

  if (result->end()[-1] != '"' || old_length == max_length)

  {

    Item_func_group_concat::cut_max_length(result, old_length, max_length);

    return;

  }

  Item_func_group_concat::cut_max_length(result, old_length, max_length-1);

  result->append('"');

}

That is what produces the empty string element: where the cut falls just
after a separator and an opening quote, backing off one byte and appending
a quote closes a string that has no content, and the brackets go on around
it. Every other landing position is left exactly as GROUP_CONCAT cut it.

JSON_OBJECTAGG derives from Item_sum instead, keeps its own String, and its
add() has no length test on any path:

/* sql/item_jsonfunc.h */

class Item_func_json_arrayagg : public Item_func_group_concat

class Item_func_json_objectagg : public Item_sum

/* sql/item_jsonfunc.cc */

bool Item_func_json_objectagg::add()

{

  StringBuffer<MAX_FIELD_WIDTH> buf;

  String *key;

  key= args[0]->val_str(&buf);

  if (args[0]->is_null())

    return 0;

  null_value= 0;

  if (result.length() > 1)

    result.append(STRING_WITH_LEN(", "));

  result.append('"');

  st_append_escaped(&result, key);

  result.append(STRING_WITH_LEN("\":"));

  buf.length(0);

  append_json_value(&result, args[1], &buf);

  return 0;

}

The word group_concat_max_len does not appear in sql/item_jsonfunc.cc at
all, so nothing ends the loop but a failure to allocate.

Affected versions

Both aggregates were added by MDEV-16620 (commit ba8e5e689c8, 2019-10-14),
first released in 10.5.0, and both halves are present in that commit. The
class declarations already differ as above, and add() already reads:

/* sql/item_jsonfunc.cc @ ba8e5e689c8 */

bool Item_func_json_objectagg::add()

{

  ...

  null_value= 0;

  if (result.length() > 1)

    result.append(", ");

  result.append("\"");

  result.append(*key);

  result.append("\":");

  buf.length(0);

  append_json_value(&result, args[1], &buf);

  return 0;

}

Every release from 10.5.0 onward is therefore expected to be affected.

Verified on 10.11 only (10.11.19-MariaDB-debug). Other branches were not
tested.

Note

Allocation failure itself is handled: Binary_string::realloc_raw asks for
memory with MYF(MY_WME) and not MY_FAE, so a refusal returns an error
rather than aborting the server. The first half of this report is about
there being no bound before that point, not about what happens when memory
runs out.

Found while reviewing the JSON aggregates for unrelated work. No existing
test sets group_concat_max_len around either aggregate.