Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

CHECK constraints on virtual columns silently dropped #7868

Open
zachmu opened this issue May 15, 2024 · 0 comments
Open

CHECK constraints on virtual columns silently dropped #7868

zachmu opened this issue May 15, 2024 · 0 comments
Labels
bug Something isn't working sql Issue with SQL

Comments

@zachmu
Copy link
Member

zachmu commented May 15, 2024

Repro:

% 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:

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.
@zachmu zachmu added the bug Something isn't working label May 15, 2024
@timsehn timsehn added the sql Issue with SQL label May 17, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working sql Issue with SQL
Projects
None yet
Development

No branches or pull requests

2 participants