[MDEV-27350] Complex CHECK constraint loses JSON property Created: 2021-12-23  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8
Fix Version/s: 10.5, 10.6

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-27018 IF and COALESCE lose "json" property Closed
relates to MDEV-27358 Table level CHECK(JSON_VALID()) const... Open
relates to MDEV-27359 Crossed JSON_VALID constraint imposes... Open
relates to MDEV-27360 Column level CHECK constraint is not ... Open
relates to MDEV-27370 UNION looses JSON property Open

 Description   

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"}}


Generated at Thu Feb 08 09:52:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.