[MDEV-27359] Crossed JSON_VALID constraint imposes JSON property to a wrong column Created: 2021-12-24  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 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-27360 Column level CHECK constraint is not ... Open
relates to MDEV-27018 IF and COALESCE lose "json" property Closed
relates to MDEV-27370 UNION looses JSON property Open

 Description   

Notice, in the CREATE statement below the constraint belonging to the column b:

  • Says that the column a is JSON
  • Says nothing that the column b itself is JSON

However, the constraint actually imposes JSON format on the column b rather than column a.

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (
 
  a VARCHAR(1000),
 
  b VARCHAR(1000) CHECK (json_valid(a))
 
);
 
INSERT INTO t1 VALUES ('{"x": "y"}', '{"x": "y"}');
 
SELECT JSON_OBJECT('a', a), JSON_OBJECT('b', b) FROM t1;


+-------------------------+---------------------+
 
| JSON_OBJECT('a', a)     | JSON_OBJECT('b', b) |
 
+-------------------------+---------------------+
 
| {"a": "{\"x\": \"y\"}"} | {"b": {"x": "y"}}   |
 
+-------------------------+---------------------+

Notice:

  • a was treated as text
  • b was treated as JSON

It's opposite to what the CHECK constraint says!
Generated at Thu Feb 08 09:52:19 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.