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.
They 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_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 the value supplied. 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. That the room asked for
a value does not cover escaping it is reported separately, so that the
counting and the spacing below can be settled on their own.

The one function that allows for the escaping asks for more than the writer
can ever use. Item_func_json_quote::fix_length_and_dec() asks for twelve:

  /*

    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);

Twelve is the cost of a pair of escapes, which is what a character outside the
first plane comes to. This function writes into utf8mb4, which carries every
character there is, so it never escapes a character for want of somewhere to
put it; what is left is the handful JSON refuses literally, all of them inside
the first plane and none costing more than six. The other six per character
is room asked for that nothing can take, and a wide enough argument passes the
width at which a result is given a blob to live in on the strength of it.

No allowance at all where a document is built rather than edited

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 return 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 returns 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.