[MDEV-27370] UNION looses JSON property - Jira

XML

Word

Printable

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL)
Fix Version/s: 10.5, 10.6
Component/s: Data types, JSON
Labels:
None

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

relates to

MDEV-27350 Complex CHECK constraint loses 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-27361 Hybrid functions with JSON arguments do not send format metadata

MDEV-27018 IF and COALESCE lose "json" property

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