JSON_ARRAYAGG hands back a value labelled binary, so it is unparseable in wide character sets and is never converted for the client

JSON_ARRAYAGG() builds the brackets around its elements in a scratch
buffer and then exchanges that buffer with the result. The scratch buffer is
left at its default character set, which is the binary one, and the exchange
carries the character set across along with the bytes. The result is
therefore handed back saying it holds bytes rather than text, even though
CHARSET() and the column metadata both say otherwise.

That one mislabelling costs three times over, and the three costs are visible
independently:

1. In ucs2, utf16 and utf32 the brackets are written one byte wide
   while every element between them is written at full width. The value is not
   a document: JSON_VALID() answers 0. It is wrong at rest, so it is stored
   that way and read back that way.
2. The value is never converted to character_set_results on its way to the
   client, because a binary label is read as needing no conversion.
   JSON_ARRAYAGG is alone among the JSON functions in this.
3. In a character set that has no code point for the JSON punctuation, the
   value is read as a document when it cannot be one. The label says a byte is
   a character, so 5B reads as a bracket where the declared character set
   spells it as a letter, and JSON_VALID() answers 1 for something no
   reader of that character set could parse.

How to repeat - the value is not a document

SELECT JSON_VALID(JSON_ARRAYAGG(CONVERT(v USING ucs2))) AS valid

  FROM (SELECT 'a' AS v UNION ALL SELECT 'b') d;

valid

0

Warnings:

Note  4036  Character disallowed in JSON in argument 1 to function 'json_valid' at position 2

The bytes show it plainly - both brackets are one byte, 5B and 5D,
while each character between them is two:

SELECT HEX(JSON_ARRAYAGG(CONVERT(v USING ucs2))) AS h

  FROM (SELECT 'a' AS v UNION ALL SELECT 'b') d;

5B 002200610022002C002200620022 5D

^^                              ^^

one byte                        one byte

Everything after the opening bracket is a byte out of step, so nothing in the
value reads as what it was meant to be.

The value is wrong at rest rather than only on the way out. Stored into a
ucs2 column it keeps the same bytes and stays unparseable:

CREATE TABLE t1 (a VARCHAR(64) CHARACTER SET ucs2);

INSERT INTO t1

  SELECT JSON_ARRAYAGG(CONVERT(v USING ucs2))

    FROM (SELECT 'a' AS v UNION ALL SELECT 'b') d;

SELECT HEX(a) AS stored, JSON_VALID(a) AS valid FROM t1;

stored                            valid

5B002200610022002C0022006200225D  0

Warnings:

Note  4038  Syntax error in JSON text in argument 1 to function 'json_valid' at position 2

How to repeat - the value is not converted for the client

swe7 has no code point for a bracket, so a result carrying one has to
arrive with a replacement character in its place. Every JSON function does
that except this one:

SET character_set_results = swe7;

SELECT JSON_ARRAY('a','b')      AS json_array,

       JSON_ARRAYAGG(v)         AS json_arrayagg,

       JSON_OBJECTAGG(v, v)     AS json_objectagg

  FROM (SELECT 'a' AS v UNION ALL SELECT 'b') d;

json_array   json_arrayagg  json_objectagg

?"a", "b"?   ["a","b"]      ?"a":"a", "b":"b"?

JSON_ARRAY and JSON_OBJECTAGG were converted; JSON_ARRAYAGG was
handed over untouched. The client was told in the column metadata that it was
about to receive swe7, and received something else.

The declared character set of the expression is correct throughout - only the
value carries the wrong label:

SELECT CHARSET(JSON_ARRAYAGG(CONVERT(v USING utf8mb4))) AS cs,

       COLLATION(JSON_ARRAYAGG(CONVERT(v USING utf8mb4))) AS co

  FROM (SELECT 'a' AS v UNION ALL SELECT 'b') d;

cs       co

utf8mb4  utf8mb4_general_ci

How to repeat - the value is read as a document where it cannot be one

swe7 puts national letters at the code points the brackets and braces
occupy in ASCII, so no document can be written in it at all. The sibling
aggregate says so; this one does not:

SELECT JSON_VALID(JSON_ARRAYAGG(CONVERT(v USING swe7)))  AS arrayagg_valid,

       JSON_VALID(JSON_OBJECTAGG(CONVERT(v USING swe7),

                                 CONVERT(v USING swe7))) AS objectagg_valid

  FROM (SELECT 'a' AS v UNION ALL SELECT 'b') d;

arrayagg_valid  objectagg_valid

1               0

Warnings:

Note  4038  Syntax error in JSON text in argument 1 to function 'json_valid' at position 1

Both expressions are declared swe7, and both hold bytes that are not a
document in it:

SELECT HEX(JSON_ARRAYAGG(CONVERT(v USING swe7))) AS h,

       CHARSET(JSON_ARRAYAGG(CONVERT(v USING swe7))) AS cs

  FROM (SELECT 'a' AS v UNION ALL SELECT 'b') d;

h                   cs

5B2261222C2262225D  swe7

The difference is not in the bytes and not in the declared character set, both
of which are as they should be. It is that JSON_VALID() reads this value
under the label the value carries, which is the binary one, and under that
label 5B is a bracket. The 1 is therefore an answer about a character set
the expression is not in.

Affected character sets

Every row below was measured on 10.11, over the same two rows.

So the first symptom appears exactly where one character is more than one
byte. Where a character is one byte the brackets happen to come out the right
width, and the value is a correct document in every such character set that
can spell one.

swe7 is the last row rather than one of the first because it fails the
other way about: a character there is one byte, so the brackets come out the
width the label says, but the label is the wrong one and the byte it wrote is
not a bracket in the character set the expression was resolved to. That is the
third symptom.

The second symptom does not depend on the character set at all. A result
computed in latin1 or utf8mb4 is a valid document and is still
delivered without conversion.

Neither symptom depends on the shape of the query. One element is affected as
much as several, each group of a GROUP BY is affected, and so are the
DISTINCT and ORDER BY forms.

Root cause

/* sql/item_jsonfunc.cc */

String* Item_func_json_arrayagg::val_str(String *str)

{

  if ((str= Item_func_group_concat::val_str(str)))

  {

    String s;             /* default constructed - my_charset_bin */

    s.append('[');        /* mbminlen 1, so one raw byte          */

    s.swap(*str);         /* Charset::swap - the label goes too   */

    str->append(s);

    str->append(']');     /* str is binary now, so one raw byte   */

  }

  return str;

}

String carries its character set in the object, and String::swap()
calls Charset::swap(), so the exchange moves the binary label onto the
result and the result's own label onto the scratch buffer. From that point on:

• String::append(char) converts what it is given only when the string it
  is appending to has mbminlen > 1. The binary character set has
  mbminlen 1, so both brackets go down as single raw bytes. The elements
  between them were already written, by Item_func_group_concat, in the
  result's real character set - hence the mismatch.
• Protocol::needs_conversion() returns false when the source character set
  is the binary one, so the wire conversion to character_set_results is
  skipped.

Stored copies are not affected by the label, only by the bytes:
Item::save_str_in_field() passes the item's declared collation to
Field::store() rather than the returned value's own label. That is why the
stored value above is byte for byte the returned one.

Affected versions

JSON_ARRAYAGG was added by MDEV-16620 (commit d0fc07c85f1,
2019-07-04), first released in 10.5.0, and the block was defective as written:

/* sql/item_jsonfunc.cc @ d0fc07c85f1 */

String* Item_func_json_arrayagg::val_str(String *str)

{

  str= Item_func_group_concat::val_str(str);

  String s;

  s.append('[');

  s.swap(*str);

  str->append(s);

  str->append(']');

  return str;

}

MDEV-22236 (commit e545a60bf41, 2020-04-28) rewrote exactly this block to
guard against a NULL result, leaving the character set handling as it was.

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.

Related, but a separate defect

MDEV-40605 reports a similar looking result from JSON_OBJECTAGG() - an
opening brace one byte wide in the same character sets - but the cause is
unrelated. There the brace is appended in the constructor, at parse time,
before fix_fields() has resolved a character set to write it in, while the
closing brace is written afterwards and is converted normally. Nothing is
mislabelled, so JSON_OBJECTAGG is delivered to the client correctly, as
the swe7 output above shows.

The two want separate fixes. Neither is a consequence of the other.

Note

Found while testing an unrelated change to the JSON aggregates; the affected
character sets are not covered by any existing test.