[MDEV-27350] Complex CHECK constraint loses JSON property - Jira

XML

Word

Printable

I run this script:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (

  j longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(j))

);

INSERT INTO t1 VALUES ('{"b":"c"}');

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

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

| JSON_OBJECT('a', j) |

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

| {"a": {"b":"c"}}    |

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

Looks good so far.

Now I change the contraint a little bit by adding an AND-ed condition:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (

  j longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(j) AND length(j)<100)

);

INSERT INTO t1 VALUES ('{"b":"c"}');

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

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

| JSON_OBJECT('a', j)    |

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

| {"a": "{\"b\":\"c\"}"} |

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

Looks wrong. The value t1.j was interpreted as a scalar string rather than a JSON, and therefore was escaped.

Note, Oracle 21c (tested on dbfiddle.uk) does not escape in similar scripts (neither with a simple, nor with an AND-ed constraint):

DROP TABLE t1;

CREATE TABLE t1 (

  j CLOB CONSTRAINT cnt1 CHECK (j IS JSON)

);

INSERT INTO t1 VALUES ('{"b":"c"}');

SELECT JSON_OBJECT('a' VALUE j) FROM t

JSON_OBJECT('A'VALUEJ)

{"a" : {"b" : "c"}}

DROP TABLE t1;

CREATE TABLE t1 (

  j CLOB CONSTRAINT cnt1 CHECK (j IS JSON AND LENGTH(j)<100)

);

INSERT INTO t1 VALUES ('{"b":"c"}');

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

JSON_OBJECT('A'VALUEJ)

{"a" : {"b" : "c"}}

relates to

MDEV-27018 IF and COALESCE lose "json" property

MDEV-27358 Table level CHECK(JSON_VALID()) constraint loses 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.