[MDEV-27370] UNION looses JSON property Created: 2021-12-27  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Data types, 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-27350 Complex CHECK constraint loses JSON p... Open
relates to MDEV-27358 Table level CHECK(JSON_VALID()) const... Open
relates to MDEV-27359 Crossed JSON_VALID constraint imposes... Open
relates to MDEV-27361 Hybrid functions with JSON arguments ... Open
relates to MDEV-27018 IF and COALESCE lose "json" property Closed

 Description    

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (j JSON);
 
INSERT INTO t1 VALUES (JSON_OBJECT('b','c'));
 
SELECT JSON_OBJECT('a', j) FROM (SELECT j FROM t1) d1;


+---------------------+
 
| JSON_OBJECT('a', j) |
 
+---------------------+
 
| {"a": {"b": "c"}}   |
 
+---------------------+

Looks good so far.

SELECT JSON_OBJECT('a', j) FROM (SELECT j FROM t1 UNION ALL SELECT j FROM t1) d1;


+-------------------------+
 
| JSON_OBJECT('a', j)     |
 
+-------------------------+
 
| {"a": "{\"b\": \"c\"}"} |
 
| {"a": "{\"b\": \"c\"}"} |
 
+-------------------------+

The value was erroneously escaped.

If I run similar queries in Oracle, the values does not get escaped:

CREATE TABLE t1 (j VARCHAR(10000) CONSTRAINT ensure_json CHECK (po_document IS JSON));
 
INSERT INTO t1 VALUES (JSON_OBJECT('b','c'));
 
SELECT JSON_OBJECT('a' VALUE j) FROM (SELECT j FROM t1) d1;
 
SELECT JSON_OBJECT('a' VALUE j) FROM (SELECT j FROM t1 UNION ALL SELECT j FROM t1) d1;


JSON_OBJECT('A'VALUEJ)
 
{"a" : {"b" : "c"}}


JSON_OBJECT('A'VALUEJ)
 
{"a" : "{"b":"c"}"}
 
{"a" : "{"b":"c"}"}


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