A column is typed JSON by any JSON_VALID check constraint, including one that reads a different column or none at all, and its contents are then spliced into documents unquoted

Whether a string column is a JSON column is decided by whether it carries a
JSON_VALID check constraint. The test does not ask what that constraint
reads:

bool Type_handler_json_common::has_json_valid_constraint(const Field *field)

{

  return field->check_constraint &&

         field->check_constraint->expr &&

         field->check_constraint->expr->type() == Item::FUNC_ITEM &&

         static_cast<const Item_func *>(field->check_constraint->expr)->

           functype() == Item_func::JSON_VALID_FUNC;

}

So the question answered is "does this column carry a check constraint that
happens to be a JSON_VALID call", where the question the callers need is
"does this column carry a check constraint that says THIS column holds a
document". Any JSON_VALID call satisfies the first, whatever it reads.

Field_string::type_handler(), Field_varstring::type_handler() and
Field_blob::type_handler() each call it, so this is what makes a column
JSON-typed at all. Each of the three carries the same comment:

  /*

    This is a temporary solution and will be fixed soon (in 10.9?).

    Type_handler_varchar_json will provide its own Field_varstring_json

    and Field_varstring_compressed_json

  */

Measured

CREATE TABLE t1 (

  a VARCHAR(100) CHECK (JSON_VALID(b)),

  b VARCHAR(100),

  d VARCHAR(100) CHECK (JSON_VALID(d)),

  e JSON

);

a's constraint reads b. d is the ordinary usage and e an
ordinary JSON column; both are here as controls. What the server sends for
each, with --enable_metadata:

Column  Type

a       253 (format=json)

b       253

d       253 (format=json)

e       252 (format=json)

a and d are indistinguishable to a client, and only d's contents
are constrained. The column the constraint does protect, b, is not typed
JSON, so the typing lands on the other column of the pair.

The consequence is a changed result, not a changed label

Being JSON-typed is what makes a value spliced into a document verbatim
rather than quoted and escaped. Fill the two columns, a with text that
is not a document and b with one that is:

INSERT INTO t1 VALUES ('not json at all', '{"x":1}', '{"y":2}', '{"z":3}');

SELECT JSON_SET('{}', '$.k', a);

NULL

Warning 4038 Syntax error in JSON text in argument 1 to function 'json_set' at position 6

SELECT JSON_SET('{}', '$.k', b);

{"k": "{\"x\":1}"}

The same statement over two columns of the same type holding text of the same
kind gives an answer in one case and NULL in the other, the difference being a
check constraint that names neither of the values involved.

A constraint that constrains nothing does it too

The argument does not have to be a column:

CREATE TABLE t1 (a VARCHAR(100) CHECK (JSON_VALID('{}')));

INSERT INTO t1 VALUES ('not json at all');

That JSON_VALID over an empty object literal is a constant true, so the
constraint refuses nothing and the row goes in. The column is still sent as
253 (format=json), and

SELECT JSON_SET('{}', '$.k', a) FROM t1;

is NULL for it. This is the minimal form: a column with no constraint on its
contents whatsoever, typed as holding documents.

The constraint itself behaves correctly

Nothing is wrong with the checking. On the cross-column table the constraint
is evaluated and enforced against the column it names:

CREATE TABLE t3 (a VARCHAR(100) CHECK (JSON_VALID(b)), b VARCHAR(100));

INSERT INTO t3 VALUES ('anything', 'not json');

ERROR 23000: CONSTRAINT `t3.a` failed for `test`.`t3`

while the same insert with a well formed document in b succeeds:

INSERT INTO t3 VALUES ('anything', '{"x":1}');

So the constraint guards b exactly as written, while the JSON type is
given to a.

Scope

A TABLE-level constraint does not do this. Measured:

CREATE TABLE t2 (a VARCHAR(100), b VARCHAR(100),

                 CONSTRAINT c CHECK (JSON_VALID(b)));

leaves both columns plain varchar, because the mechanism reads
Field::check_constraint, which is set only for a constraint written on the
column. So the behaviour depends on where the same constraint is written, and
the form that types a column is the one that can name a different one.

INFORMATION_SCHEMA.COLUMNS is not affected – it reports varchar for
a and d alike – so the two places the server describes a column
disagree with each other.

Provenance

has_json_valid_constraint() arrives in its present form with
e4b302e436c ("MDEV-27018 IF and COALESCE lose 'json' property",
2022-01-10, Alexander Barkov), which reworked how the JSON property is carried
and replaced Item::is_json_type() with the JSON type handlers. The
predicate is byte-identical at the tips of 10.5, 10.6, 10.11, 11.4, 11.8,
12.0, 12.1, 12.2, 12.3 and main.

The three type_handler() call sites are equally unchanged across those
tips, comment included, so the "temporary solution" they describe has been in
place since 10.5.

The fix

The question asked is now the one the callers need: does this column carry a
check constraint saying THIS column holds a document. A JSON_VALID call
answers for a column only when its single argument is that very column.

Two places decide, and both were blind to it

Field::type_handler(), through has_json_valid_constraint(), decides
whether a value is put into a document verbatim or quoted and escaped.

Field_longstr::make_send_field() separately asks the constraint
expression to name a format, which is what sends format=json to a
client. Fixing only the first would leave a column advertised as holding
documents while behaving as an ordinary one, so both were changed.

The second of the two understands a conjunction:
Item_cond_and::set_format_by_check_constraint() walks the conjuncts, so

js TEXT CHECK (LENGTH(js) > 0 AND JSON_VALID(js))

names a format where the first test does not. That is a real promise – a
conjunction has to hold in full – and it is kept. Rather than gating the
walk from outside, the column is passed down into it, so the recursion still
reaches every part and each JSON_VALID answers for the column it reads.

One test of the expression is now shared by both, and by the reader that
decides which checks a written document lets stand.

What does not change

• A table level constraint never typed a column and still does not.
• A constraint the server writes itself, for a JSON column or for the
  columns of a temporary table, reads the column it belongs to, so those
  columns stay JSON.
• The checking itself is untouched: a constraint naming another column is
  still evaluated and enforced against that column exactly as written.

Side effects

A column that was typed by a check over another column had its contents put
into documents verbatim; they are quoted now. Two consequences follow, both
from the column never having been a JSON column:

• where such a column held text that is not a document, a NULL result
  becomes the quoted text;
• where it held a well formed document, a nested document becomes a string.

The declared type sent to a client changes with it, so the type a column is
reported as and the way its contents are treated now agree.

Five recorded test results gain a warning line each. No value, type or
metadata changes anywhere else in the suite.

New diagnostics

Writing JSON_VALID in a column's check constraint is how a JSON column is
asked for, so a constraint that mentions it and still leaves the column
ordinary now says so, once, when the column is defined:

4193  CHECK constraint of column 'a' calls JSON_VALID() on something other

      than 'a'; the column is not a JSON column

4194  CHECK constraint of column 'b' can pass without JSON_VALID() holding;

      the column is not a JSON column

4194 is the case where JSON_VALID does read this column but the
constraint can be satisfied without it, as under an OR, so the column
keeps no promise about its contents either.

Both are raised while the column is being defined, where the expression has
not been fixed yet and a column can only be recognised by its name. The
predicate that runs on an open table matches the Field itself.