JSON functions reserve too little room for the documents they produce, silently truncating a materialized result into invalid JSON

A JSON function has to name a length for its result before it has seen a
value, so it asks for one out of what its arguments say about themselves.
Eight of them ask for too little, in three different ways:

• JSON_SET, JSON_INSERT and JSON_REPLACE read their arguments at
  half the rate they step over them, so they never count the values at all
• JSON_ARRAY and JSON_OBJECT allow twice the characters of a value for
  the writing, and a character that has to be spelt out takes six
• JSON_ARRAY_APPEND and JSON_ARRAY_INSERT make no allowance for the
  writing whatsoever
• JSON_REMOVE makes no allowance for the spacing it puts back into what is
  left, so taking a little out of a dense document returns more than went in
• every function that hands a document back writes it out again with a space
  after every separator it copies, and every one of them counts that document
  only once: JSON_SET, JSON_INSERT, JSON_REPLACE,
  JSON_ARRAY_APPEND and JSON_ARRAY_INSERT for the document they edit,
  and JSON_MERGE and JSON_MERGE_PATCH for a value that is already a
  document

Where the result is put into a column rather than sent straight to the client,
it is cut to the length that was asked for. The cut text is invalid JSON and
nothing is reported about it. Writing an internal temporary table leaves
count_cuted_fields at CHECK_FIELD_IGNORE, so the store that cuts it
answers 0 and warns nobody – and the width of that column was chosen by
nothing but the function's own declaration.

On a path that checks the length rather than truncating, the same reservation
rejects a statement whose result is a perfectly good document.

How to repeat

The width the reservation asks for, read off the column a result is put in:

CREATE TABLE t1 (v VARCHAR(200));

INSERT INTO t1 VALUES ('{"x":1}');

CREATE TABLE d1 AS SELECT JSON_SET(v, '$.p', 'z') AS r FROM t1;

SHOW CREATE TABLE d1;

d1  CREATE TABLE `d1` (

  `r` varchar(413) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci

413 is 200 for the document, 200 + 6 for the document counted a second time,
and 3 + 4 for the path. The value is not in it.

Add a second path/value pair and the width grows by 14, which is the first
path and the first value; the second pair's value is again not counted:

CREATE TABLE d2 AS SELECT JSON_SET(v,'$.p','z','$.q','w') AS r FROM t1;

SHOW CREATE TABLE d2;

d2  CREATE TABLE `d2` (

  `r` varchar(427) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci

The truncation itself, with the derived table materialized:

SET @@optimizer_switch='derived_merge=off';

SELECT LENGTH(JSON_SET(v, '$.p', REPEAT('z',5000))) AS produced FROM t1;

SELECT LENGTH(r) AS kept, JSON_VALID(r) AS valid

  FROM (SELECT JSON_SET(v, '$.p', REPEAT('z',5000)) AS r FROM t1) AS d;

produced

5017

kept    valid

413     0

Warnings:

Note  4037  Unexpected end of JSON text in argument 1 to function 'json_valid'

5017 characters were produced and 413 were kept. The store that cut them says
nothing; the only diagnostic above is JSON_VALID complaining about the
already damaged value, and only because it was asked.

JSON_INSERT and JSON_REPLACE behave the same way, and a second pair cuts
at 427:

SELECT LENGTH(r) AS kept, JSON_VALID(r) AS valid

  FROM (SELECT JSON_INSERT(v, '$.p', REPEAT('z',5000)) AS r FROM t1) AS d;

SELECT LENGTH(r) AS kept, JSON_VALID(r) AS valid

  FROM (SELECT JSON_REPLACE(v, '$.x', REPEAT('z',5000)) AS r FROM t1) AS d;

SELECT LENGTH(r) AS kept, JSON_VALID(r) AS valid

  FROM (SELECT JSON_SET(v,'$.p','x','$.q',REPEAT('z',5000)) AS r FROM t1) AS d;

kept    valid

413     0

kept    valid

413     0

kept    valid

427     0

On a path that reports rather than truncates, the same reservation fails a
statement that should succeed:

CREATE TABLE d3 AS SELECT JSON_SET(v, '$.p', REPEAT('z',5000)) AS r FROM t1;

ERROR 22001: Data too long for column 'r' at row 1

A value no longer than the document is unaffected, which is why this goes
unnoticed:

SELECT LENGTH(r) AS kept, JSON_VALID(r) AS valid

  FROM (SELECT JSON_SET(v, '$.p', 'z') AS r FROM t1) AS d;

kept    valid

18      1

Reading the arguments two at a time and one at a time

Item_func_json_insert::fix_length_and_dec() serves all three of
JSON_SET, JSON_INSERT and JSON_REPLACE. Its loop advances
n_arg by 2, but indexes the arguments with n_arg/2 and n_arg/2+1,
which advance by 1:

/* sql/item_jsonfunc.cc */

  collation.set(args[0]->collation);

  char_length= args[0]->max_char_length();

  for (n_arg= 1; n_arg < arg_count; n_arg+= 2)

  {

    paths[n_arg/2].set_constant_flag(args[n_arg]->const_item());

    /*

      In the resulting JSON we can insert the property

      name from the path, and the value itself.

    */

    char_length+= args[n_arg/2]->max_char_length() + 6;

    char_length+= args[n_arg/2+1]->max_char_length() + 4;

  }

The path index on the first line is right; the two below it are not. A document
and k path/value pairs is 2k+1 arguments, and over k iterations the loop reads
args[0] through args[k] – the first half of the list. So

• the document is counted a second time, having already been counted before the
  loop
• the value of the last pair is never counted at all, at any k
• an earlier value, once the loop reaches it, is added in the term meant for a
  path

Item_func_json_array_append::fix_length_and_dec() immediately alongside
indexes args[n_arg+1] and is right about which argument it is looking at.

The room asked for does not allow for the writing

Correcting the indexing is not enough on its own, and taken by itself it makes
matters worse for a second class of statement. The old arithmetic counts the
document twice, and that accidental slack has been covering values whose
written form is longer than what was handed over. Once the indexing is right
the slack is gone, and a shape that used to survive stops surviving:

CREATE TABLE t1 (v VARCHAR(200));

INSERT INTO t1 VALUES (CONCAT('{"x":"', REPEAT('a',190), '"}'));

CREATE TABLE d1 AS SELECT JSON_SET(v, '$.p', REPEAT('"',20)) AS r FROM t1;

That result is 248 characters. Today it is given 413 and survives; with the
indexing corrected and nothing else it is given 233 and fails with
ER_DATA_TOO_LONG.

A value that is not already a document is written into the result as a JSON
string, and the writing can make a character longer. JSON_ARRAY is the only
one of these functions that allows for that at all, and what it allows is not
enough:

      arg_length= arg->max_char_length() * 2; /*escaping possible */

A character that cannot be written as it stands is spelt out instead.
json_escape() writes a backslash, a u, and the hex of the UTF-16 form,
which my_uni_utf16() gives as two bytes for a character of the first plane
and four for any other – so six characters, or ten at the very worst.
Item_func_json_quote::fix_length_and_dec() is the only one that allows for
any of this, and it asks for twelve, two more per character than the writer can
ever use:

  /*

    Odd but realistic worst case is when all characters

    of the argument turn into '\uXXXX\uXXXX', which is 12.

  */

  fix_char_length_ulonglong((ulonglong) args[0]->max_char_length() * 12 + 2);

The same shortfall where a document is built rather than edited

Measured on a VARCHAR(20) column holding twenty {{CHAR(1)}}s, each of which
is written out as \u0001:

CREATE TABLE t1 (v VARCHAR(10), s VARCHAR(20));

INSERT INTO t1 VALUES ('{"x":1}', REPEAT(CHAR(1),20));

SET @@optimizer_switch='derived_merge=off';

SELECT LENGTH(JSON_ARRAY(s)) AS produced FROM t1;

SELECT LENGTH(r) AS kept, JSON_VALID(r) AS valid

  FROM (SELECT JSON_ARRAY(s) AS r FROM t1) AS d;

produced

124

kept    valid

46      0

JSON_OBJECT is served by the same {{Item_func_json_array::
fix_length_and_dec()}} and understates it the same way.

JSON_ARRAY_APPEND and JSON_ARRAY_INSERT are worse again:
Item_func_json_array_append::fix_length_and_dec() asks for
max_char_length() + 4 and makes no allowance for the writing at all, nor for
a value that is spelt null.

A function that takes something out can still hand back more

JSON_REMOVE writes what is left of the document out again, and it writes a
space after every separator it copies. Taking out less than that spacing adds
therefore returns more than went in, while the reservation allows for none of
it:

bool Item_func_json_remove::fix_length_and_dec(THD *thd)

{

  collation.set(args[0]->collation);

  max_length= args[0]->max_length;

Measured on a VARCHAR(64) utf8mb4 column holding a dense array of
thirty ones, 61 characters long:

CREATE TABLE t1 (v VARCHAR(64)) CHARSET utf8mb4;

INSERT INTO t1 VALUES ('[1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1]');

SET @@optimizer_switch='derived_merge=off';

SELECT LENGTH(JSON_REMOVE(v,'$[0]')) AS produced FROM t1;

SELECT LENGTH(r) AS kept, JSON_VALID(r) AS valid

  FROM (SELECT JSON_REMOVE(v,'$[0]') AS r FROM t1) AS d;

produced

87

kept    valid

64      0

Warnings:

Note  4037  Unexpected end of JSON text in argument 1 to function 'json_valid'

One element of two characters was taken out and twenty-eight spaces arrived.
The declared width and the value are visible side by side without any table in
the way:

--enable_metadata

SELECT JSON_REMOVE(v,'$[0]') AS r FROM t1;

Type              Length  Max length

253 (format=json)  256      87

Length 256 is 64 utf8mb4 characters, the width asked for. Max length
87 is what the same statement produced.

A path that matches nothing at all is the clearest case, because then nothing
is taken out and only the spacing is added – 61 characters in, 90 out:

SELECT LENGTH(JSON_REMOVE(v,'$.nothing')) AS produced FROM t1;

SELECT LENGTH(r) AS kept, JSON_VALID(r) AS valid

  FROM (SELECT JSON_REMOVE(v,'$.nothing') AS r FROM t1) AS d;

produced

90

kept    valid

64      0

The neighbouring function that adds the same spacing does allow for it.
JSON_LOOSE asks for twice the argument, and JSON_COMPACT, which only
ever takes spacing away, asks for exactly the argument:

  case COMPACT:

    max_length= args[0]->max_length;

    break;

  case LOOSE:

    max_length= args[0]->max_length * 2;

    break;

JSON_QUERY and JSON_KEYS also ask for exactly the argument, and both are
correct to: JSON_QUERY hands back a piece of the document as it stands
without respacing it (49 characters in, 49 out), and every key JSON_KEYS
lists costs less to list than it cost to declare (49 in, 40 out).

The spacing is not particular to JSON_REMOVE

Every one of these functions writes the document out again the same way, so
the same spacing arrives whether something was taken out of the document, put
into it, or neither. None of them allows for it. On the same dense array of
thirty ones in a VARCHAR(64):

SELECT LENGTH(JSON_ARRAY_APPEND(v,'$',1)) AS produced FROM t1;

SELECT LENGTH(r) AS kept, JSON_VALID(r) AS valid

  FROM (SELECT JSON_ARRAY_APPEND(v,'$',1) AS r FROM t1) AS d;

produced

93

kept    valid

69      0

A value that is already a document is counted once for the same reason, which
reaches JSON_MERGE and JSON_MERGE_PATCH:

SELECT LENGTH(JSON_MERGE(JSON_QUERY(v,'$'),JSON_QUERY(v,'$'))) AS produced

  FROM t1;

SELECT LENGTH(r) AS kept, JSON_VALID(r) AS valid

  FROM (SELECT JSON_MERGE(JSON_QUERY(v,'$'),JSON_QUERY(v,'$')) AS r

          FROM t1) AS d;

produced

180

kept    valid

132     0

JSON_SET, JSON_INSERT and JSON_REPLACE have the same shortfall, but
it cannot be reached as they stand: counting the document a second time, which
is the indexing defect above, happens to cover the spacing as well. It becomes
reachable the moment the indexing is corrected, so the two cannot be repaired
independently of one another.

Where it is reachable

Anywhere the result goes into a column: a materialized derived table or CTE,
GROUP BY, DISTINCT, UNION, an ORDER BY that needs a temporary table, a cursor,
INSERT ... SELECT, and the declared column type of {{CREATE TABLE ... AS
SELECT}} and of a view.

An under-reservation can only cut where the width it names is honoured exactly.
Above CONVERT_IF_BIGGER_TO_BLOB the result is given a blob whose capacity is
far more than was asked for, and the wrong number stops mattering. So the
reachable cases are small documents and small values – and a test written above
that width passes whether the arithmetic is right or wrong.

A JSON column does not expose it for the same reason. JSON is
LONGTEXT, whose max_char_length() is large enough to absorb any of these
shortfalls, so the reservation is wrong there too but never binds. A document
held in VARCHAR, CHAR or TEXT, or written as a literal, does expose
it.

Affected versions

MDEV-29264 (820175115ef, 2022-08-13) corrected the indexing, in both
Item_func_json_array_append::fix_length_and_dec() and
Item_func_json_insert::fix_length_and_dec():

-    char_length+= args[n_arg/2+1]->max_char_length() + 4;

+    char_length+=

+        static_cast<ulonglong>(args[n_arg+1]->max_char_length()) + 4;

It went into 10.6 and is in 10.6, 10.11 and above.

MDEV-32454 (b93252a3036, 2023-12-15) was made on 10.4, where the
uncorrected form still stood, and added the args[n_arg/2] term to
Item_func_json_insert::fix_length_and_dec() on top of it. Merging that
commit upwards restored the pre-MDEV-29264 indexing for
Item_func_json_insert in every branch it reached, while
Item_func_json_array_append, which it did not touch, kept the fix. That is
why the two neighbouring functions disagree today.

The wrong indexing is at the tips of 10.5, 10.6, 10.11, 11.4, 11.8, 12.0, 12.1
and main. The missing room for the writing, and the missing room for the
spacing a document is written out with, are older than either commit and are
present in all of them as well.

Verified on 10.11 only (10.11.19-MariaDB-debug). Other branches were read but
not run.

Found while auditing the JSON functions for an unrelated change.