Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5, 10.6, 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.7(EOL), 10.8(EOL)
-
None
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"}}
|
Attachments
Issue Links
- relates to
-
MDEV-27018 IF and COALESCE lose "json" property
- Closed
-
MDEV-27358 Table level CHECK(JSON_VALID()) constraint loses JSON property
- Open
-
MDEV-27359 Crossed JSON_VALID constraint imposes JSON property to a wrong column
- Open
-
MDEV-27360 Column level CHECK constraint is not SQL Standard compliant
- Open
-
MDEV-27370 UNION looses JSON property
- Open