dolthub / dolthub/dolt

`CHECK` constraints on virtual columns silently dropped

Open
#7,868 0 comments 0 reactions 1 assignee Claimed by @zachmu View on GitHub
bug sql
Dominant language
Go
Stars
24.4k
Forks
873
Avg merge
1d 5h
Merged PRs (30d)
108

Description

Repro:

```sql
% dolt sql
# Welcome to the DoltSQL shell.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit.
new3/main*> create table json_index (a int primary key, b json);
new3/main*> alter table json_index add column vcol int as (b->>"$.field");
new3/main*> alter table json_index add constraint check (vcol > 0);
new3/main*> show create table json_index;
+------------+--------------------------------------------------------------------------------+
| Table | Create Table |
+------------+--------------------------------------------------------------------------------+
| json_index | CREATE TABLE `json_index` ( |
| | `a` int NOT NULL, |
| | `b` json, |
| | `vcol` int GENERATED ALWAYS AS (json_unquote(json_extract(`b`, '$.field'))), |
| | PRIMARY KEY (`a`) |
| | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin |
+------------+--------------------------------------------------------------------------------+
1 row in set (0.01 sec)
```

The check is silently ignored, and values in violation can be inserted:

```sql
new3/main*> insert into json_index (a, b) values (1, '{"field": -1}');
(ok)
```

Correctly errors in MySQL:

```
mysql> insert into json_index (a, b) values (1, '{"field": -1}');
ERROR 3819 (HY000): Check constraint 'json_index_chk_1' is violated.
```

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.