diff options
| author | Sergei Golubchik <serg@mariadb.org> | 2017-09-14 15:10:23 +0200 |
|---|---|---|
| committer | Sergei Golubchik <serg@mariadb.org> | 2017-09-18 10:12:23 +0200 |
| commit | 4c6c352138d8370784f87118dc172c54a2a7a6ca (patch) | |
| tree | 8887b596772f4986faaf0d1ce582b4a4614b666b | |
| parent | e6ce97a592858cf7274f045150e5208128e2c35a (diff) | |
| download | mariadb-git-4c6c352138d8370784f87118dc172c54a2a7a6ca.tar.gz | |
MDEV-13596 CHECK constraints disallow NULL to pass through, violating SQL
SQL Standard (4.23.3.4 Table check constraints, part 2, SQL:2016) says
that CHECK constraint rejects rows *only* if the condition is FALSE.
That is, both TRUE and NULL should be allowed.
| -rw-r--r-- | mysql-test/r/check_constraint.result | 10 | ||||
| -rw-r--r-- | mysql-test/t/check_constraint.test | 11 | ||||
| -rw-r--r-- | sql/table.cc | 3 |
3 files changed, 23 insertions, 1 deletions
diff --git a/mysql-test/r/check_constraint.result b/mysql-test/r/check_constraint.result index 308b6a2fc2b..525140d96e7 100644 --- a/mysql-test/r/check_constraint.result +++ b/mysql-test/r/check_constraint.result @@ -142,3 +142,13 @@ create table t1 (a int check (@b in (select user from mysql.user))); ERROR HY000: Function or expression 'select ...' cannot be used in the CHECK clause of `a` create table t1 (a int check (a > @b)); ERROR HY000: Function or expression '@b' cannot be used in the CHECK clause of `a` +create table t1 (a int check (a = 1)); +insert t1 values (1); +insert t1 values (2); +ERROR 23000: CONSTRAINT `a` failed for `test`.`t1` +insert t1 values (NULL); +select * from t1; +a +1 +NULL +drop table t1; diff --git a/mysql-test/t/check_constraint.test b/mysql-test/t/check_constraint.test index 43b4417cfa3..f72ce38087e 100644 --- a/mysql-test/t/check_constraint.test +++ b/mysql-test/t/check_constraint.test @@ -92,3 +92,14 @@ create or replace table t1( c1 int auto_increment primary key, check( c1 > 0 or create table t1 (a int check (@b in (select user from mysql.user))); --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED create table t1 (a int check (a > @b)); + +# +# MDEV-13596 CHECK constraints disallow NULL to pass through, violating SQL +# +create table t1 (a int check (a = 1)); +insert t1 values (1); +--error ER_CONSTRAINT_FAILED +insert t1 values (2); +insert t1 values (NULL); +select * from t1; +drop table t1; diff --git a/sql/table.cc b/sql/table.cc index 450e116080a..e80a938ee72 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -5090,7 +5090,8 @@ int TABLE::verify_constraints(bool ignore_failure) { for (Virtual_column_info **chk= check_constraints ; *chk ; chk++) { - if ((*chk)->expr->val_int() == 0) + /* yes! NULL is ok, see 4.23.3.4 Table check constraints, part 2, SQL:2016 */ + if ((*chk)->expr->val_int() == 0 && !(*chk)->expr->null_value) { my_error(ER_CONSTRAINT_FAILED, MYF(ignore_failure ? ME_JUST_WARNING : 0), (*chk)->name.str, |