[MDEV-27358] Table level CHECK(JSON_VALID()) constraint loses JSON property - Jira

XML

Word

Printable

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (

  a VARCHAR(1000),

  CONSTRAINT ensure_a_json CHECK (json_valid(a))

);

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

SELECT JSON_OBJECT('a', a) FROM t1;

+-------------------------+

| JSON_OBJECT('a', a)     |

+-------------------------+

| {"a": "{\"x\": \"y\"}"} |

+-------------------------+

Notice, it did not recognize that the column is JSON and escapes it as plain text.

If I run a similar script with Oracle, JSON is recognized and does not get escaped:

DROP TABLE t1;

CREATE TABLE t1 (

  a VARCHAR(1000),

  CONSTRAINT ensure_a_json CHECK (a IS JSON)

);

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

SELECT JSON_OBJECT('a' VALUE a) FROM t1;

JSON_OBJECT('A'VALUEA)

{"a":{"x": "y"}}

relates to

MDEV-27350 Complex CHECK constraint loses JSON property

MDEV-27018 IF and COALESCE lose "json" property

MDEV-27359 Crossed JSON_VALID constraint imposes JSON property to a wrong column

MDEV-27360 Column level CHECK constraint is not SQL Standard compliant

MDEV-27370 UNION looses JSON property

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.