JSON_KEYS is documented to return a JSON array but the server does not treat its result as one

The documentation for JSON_KEYS() says:

Returns the keys as a JSON array from the top-level value of a JSON object
or, if the optional path argument is provided, the top-level keys from the
path.

https://mariadb.com/kb/en/json_keys/

The server does not treat that array as one. Every JSON function decides
whether an argument in VALUE position is spliced into the document it is
building or quoted and escaped into it as a string, and it decides by the
argument's type. JSON_KEYS has a string type, so its array is escaped
into a string, and a document built out of it has a string where the
documentation says there is an array.

How to repeat

SELECT JSON_ARRAY(JSON_KEYS('{"a":1,"b":2}')) AS keys_nested;

keys_nested

["[\"a\", \"b\"]"]

An array of one string, where an array of one array was asked for. The
functions that ARE typed as returning a document put the same shape of value
in as a document:

SELECT JSON_ARRAY(JSON_EXTRACT('{"a":[1,2]}','$.a')) AS extract_nested;

SELECT JSON_ARRAY(JSON_QUERY('{"a":[1,2]}','$.a')) AS query_nested;

extract_nested

[[1, 2]]

query_nested

[[1,2]]

It is the same in every value position, in the functions that build
documents and in the ones that edit them:

SELECT JSON_OBJECT('k', JSON_KEYS('{"a":1,"b":2}')) AS keys_in_object;

SELECT JSON_SET('{"x":1}', '$.y', JSON_KEYS('{"a":1,"b":2}')) AS keys_set;

SELECT JSON_ARRAYAGG(JSON_KEYS(j)) AS keys_agg

  FROM (SELECT '{"a":1}' AS j UNION ALL SELECT '{"b":2,"c":3}') d;

keys_in_object

{"k": "[\"a\", \"b\"]"}

keys_set

{"x": 1, "y": "[\"a\", \"b\"]"}

keys_agg

["[\"a\"]","[\"b\", \"c\"]"]

Compare the same edit with a typed argument:

SELECT JSON_SET('{"x":1}', '$.y', JSON_EXTRACT('{"a":[1,2]}','$.a')) AS extract_set;

extract_set

{"x": 1, "y": [1, 2]}

The value itself is a well formed JSON array

Nothing is wrong with what JSON_KEYS produces, only with what the server
believes it to be. Read back as a document it is an array, and it can be used
as the document argument of any JSON function:

SELECT JSON_VALID(JSON_KEYS('{"a":1,"b":2}'))  AS valid,

       JSON_LENGTH(JSON_KEYS('{"a":1,"b":2}')) AS len,

       JSON_TYPE(JSON_KEYS('{"a":1,"b":2}'))   AS type;

SELECT JSON_EXTRACT(JSON_KEYS('{"a":1,"b":2}'), '$[0]') AS first_key;

valid  len  type

1      2    ARRAY

first_key

"a"

So the defect is confined to value position, and it has a workaround that
says exactly what has gone wrong - the array has to be parsed back out of
itself before it can be put in:

SELECT JSON_ARRAY(JSON_EXTRACT(JSON_KEYS('{"a":1,"b":2}'), '$')) AS workaround;

workaround

[["a", "b"]]

Root cause

Whether an argument is a document is asked of its type handler:

/* sql/item_jsonfunc.cc */

bool is_json_type(const Item *item)

{

  for ( ; ; )

  {

    if (Type_handler_json_common::is_json_type_handler(item->type_handler()))

      return true;

    ...

  }

}

The JSON functions that return a document get that handler from a shared
base class:

/* sql/item_jsonfunc.h */

class Item_json_func: public Item_str_func

{

  ...

  const Type_handler *type_handler() const override

  {

    return Type_handler_json_common::json_type_handler(max_length);

  }

};

JSON_KEYS does not derive from it:

/* sql/item_jsonfunc.h */

class Item_func_json_keys: public Item_str_func

so it inherits the string handler and is_json_type() answers false for
it. The two aggregates reach the same handler by their own override
(json_type_handler_sum), and every other document producing function -
JSON_EXTRACT, JSON_QUERY, JSON_ARRAY, JSON_OBJECT,
JSON_INSERT and its two aliases, JSON_REMOVE, JSON_MERGE,
JSON_MERGE_PATCH, JSON_ARRAY_APPEND, JSON_ARRAY_INSERT,
JSON_SEARCH, JSON_NORMALIZE, JSON_COMPACT and the rest of the
format family - derives from Item_json_func. JSON_KEYS is the only
function documented to return a JSON document that is left out.

Affected versions

JSON_KEYS arrived with the rest of the family in MDEV-9143 (commit
ebe5ebba165, 2016-11-15), first released in 10.2.0, and was declared
Item_str_func in that commit. Every release from 10.2.0 onward is
therefore expected to be affected.

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

Compatibility

Correcting the type changes output, in value position only:

SELECT JSON_ARRAY(JSON_KEYS('{"a":1,"b":2}'))

  now       ["[\"a\", \"b\"]"]

  corrected [["a", "b"]]

Every other use of JSON_KEYS is unaffected, including the
JSON_EXTRACT(..., '$') workaround above, which keeps producing the same
document by a longer route. A statement that wants the old escaped form can
ask for it with JSON_QUOTE.

Not this bug: JSON_QUOTE

JSON_QUOTE() is the other function in this file declared
Item_str_func while returning something a document could hold, and it
behaves the same way in value position:

SELECT JSON_ARRAY(JSON_QUOTE('a')) AS quote_nested;

quote_nested

["\"a\""]

It is deliberately NOT included here. Its documentation calls the result a
string ("returns a utf8mb4 string"), so the implementation and the
documentation already agree, and typing it as a document would make the
quoting it exists to perform a no-op in exactly the position it was meant
for. That is a design question with its own answer, not this mismatch.

Note

Found while auditing the JSON functions for which of them may be trusted to
have produced a document. No existing test covers JSON_KEYS in value
position.