From fb979efae37dac58d20076ec3943d156e360ebf4 Mon Sep 17 00:00:00 2001 From: Daniel Black Date: Fri, 3 May 2019 18:51:19 +1000 Subject: [PATCH 1/2] bug #95233: check constraints now support case/if functions --- mysql-test/r/check_constraints.result | 27 +++++++++++++++++++++++++++ mysql-test/t/check_constraints.test | 21 +++++++++++++++++++++ sql/item_cmpfunc.cc | 15 +++++++++++++++ sql/item_cmpfunc.h | 2 ++ 4 files changed, 65 insertions(+) diff --git a/mysql-test/r/check_constraints.result b/mysql-test/r/check_constraints.result index a4ceef85b4b..b0d4ddeb68a 100644 --- a/mysql-test/r/check_constraints.result +++ b/mysql-test/r/check_constraints.result @@ -94,6 +94,12 @@ CREATE TABLE t1(f1 int, CONSTRAINT t1_ck CHECK(f1<10), CONSTRAINT t2_ck CHECK(f1 ERROR HY000: An expression of non-boolean type specified to a check constraint 't2_ck'. CREATE TABLE t1(f1 int, CONSTRAINT t1_ck CHECK(f1<10), CONSTRAINT t2_ck CHECK(f1 + 10)); ERROR HY000: An expression of non-boolean type specified to a check constraint 't2_ck'. +CREATE TABLE t1(f1 int CHECK(IF(f1 < 10, "f1 is a monkey", f1 IS NULL))); +ERROR HY000: An expression of non-boolean type specified to a check constraint 't1_chk_1'. +CREATE TABLE t1(f2 int CHECK(CASE WHEN f2 THEN "f2 is a chicken" ELSE f2 IS NULL END)); +ERROR HY000: An expression of non-boolean type specified to a check constraint 't1_chk_1'. +CREATE TABLE t1(f2 int CHECK(CASE WHEN f2 THEN f2 < 5 ELSE "f2 is a dog" END)); +ERROR HY000: An expression of non-boolean type specified to a check constraint 't1_chk_1'. CREATE TABLE t1(f1 int, CONSTRAINT t1_ck CHECK(f1<10), CONSTRAINT t2_ck CHECK(f2 > 1)); ERROR HY000: Check constraint 't2_ck' refers to non-existing column 'f2'. CREATE TABLE t1(f1 int, CHECK(f1<10), CONSTRAINT t2_ck CHECK(f1 > 1)); @@ -303,6 +309,27 @@ INSERT INTO t1(c1,c2) VALUES(1,1); ERROR HY000: Check constraint 'ck' is violated. INSERT INTO t1(c1,c2) VALUES(10,10); DROP TABLE t1; +#----------------------------------------------------------------------- +# Test case to verify check constraints with if/case functions +#----------------------------------------------------------------------- +CREATE TABLE t1(c1 int CHECK(IF(c1 < 10, c1 < 3, c1 IS NULL)), +c2 int CHECK(CASE WHEN c2 THEN c2 < 0 ELSE c2 IS NULL END), +s varchar(12) CHECK(CASE WHEN s = "monkey" THEN 12 > 11 ELSE s IS NULL END) +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `s` varchar(12) DEFAULT NULL, + CONSTRAINT `t1_chk_1` CHECK (if((`c1` < 10),(`c1` < 3),isnull(`c1`))), + CONSTRAINT `t1_chk_2` CHECK ((case when `c2` then (`c2` < 0) else isnull(`c2`) end)), + CONSTRAINT `t1_chk_3` CHECK ((case when (`s` = _utf8mb4'monkey') then (12 > 11) else isnull(`s`) end)) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci +INSERT INTO t1(c1,c2,s) VALUES(5, -1, "monkey"); +ERROR HY000: Check constraint 't1_chk_1' is violated. +INSERT INTO t1(c1,c2,s) VALUES(2, NULL, NULL); +DROP TABLE t1; #------------------------------------------------------------------------ # Test case to verify check constraints with DEFAULT column value. #------------------------------------------------------------------------ diff --git a/mysql-test/t/check_constraints.test b/mysql-test/t/check_constraints.test index af07a7e0d9e..522d17d8e96 100644 --- a/mysql-test/t/check_constraints.test +++ b/mysql-test/t/check_constraints.test @@ -113,6 +113,15 @@ CREATE TABLE t1(f1 int, CONSTRAINT t1_ck CHECK(f1<10), CONSTRAINT t2_ck CHECK(f1 --error ER_NON_BOOLEAN_EXPR_FOR_CHECK_CONSTRAINT CREATE TABLE t1(f1 int, CONSTRAINT t1_ck CHECK(f1<10), CONSTRAINT t2_ck CHECK(f1 + 10)); +--error ER_NON_BOOLEAN_EXPR_FOR_CHECK_CONSTRAINT +CREATE TABLE t1(f1 int CHECK(IF(f1 < 10, "f1 is a monkey", f1 IS NULL))); + +--error ER_NON_BOOLEAN_EXPR_FOR_CHECK_CONSTRAINT +CREATE TABLE t1(f2 int CHECK(CASE WHEN f2 THEN "f2 is a chicken" ELSE f2 IS NULL END)); + +--error ER_NON_BOOLEAN_EXPR_FOR_CHECK_CONSTRAINT +CREATE TABLE t1(f2 int CHECK(CASE WHEN f2 THEN f2 < 5 ELSE "f2 is a dog" END)); + --error ER_CHECK_CONSTRAINT_REFERS_UNKNOWN_COLUMN CREATE TABLE t1(f1 int, CONSTRAINT t1_ck CHECK(f1<10), CONSTRAINT t2_ck CHECK(f2 > 1)); @@ -269,6 +278,18 @@ INSERT INTO t1(c1,c2) VALUES(1,1); INSERT INTO t1(c1,c2) VALUES(10,10); DROP TABLE t1; +--echo #----------------------------------------------------------------------- +--echo # Test case to verify check constraints with if/case functions +--echo #----------------------------------------------------------------------- +CREATE TABLE t1(c1 int CHECK(IF(c1 < 10, c1 < 3, c1 IS NULL)), + c2 int CHECK(CASE WHEN c2 THEN c2 < 0 ELSE c2 IS NULL END), + s varchar(12) CHECK(CASE WHEN s = "monkey" THEN 12 > 11 ELSE s IS NULL END) +); +SHOW CREATE TABLE t1; +--error ER_CHECK_CONSTRAINT_VIOLATED +INSERT INTO t1(c1,c2,s) VALUES(5, -1, "monkey"); +INSERT INTO t1(c1,c2,s) VALUES(2, NULL, NULL); +DROP TABLE t1; --echo #------------------------------------------------------------------------ --echo # Test case to verify check constraints with DEFAULT column value. diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 8c32859a389..2931d70ce72 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -3078,6 +3078,10 @@ longlong Item_func_if::val_int() { return value; } +bool Item_func_if::is_bool_func() const { + return args[1]->is_bool_func() && args[2]->is_bool_func(); +} + String *Item_func_if::val_str(String *str) { DBUG_ASSERT(fixed == 1); @@ -3329,6 +3333,17 @@ double Item_func_case::val_real() { return res; } +bool Item_func_case::is_bool_func() const { + uint i; + for (i = 1; i < ncases && args[i]->is_bool_func(); i += 2); + if (i >= ncases) { + if (else_expr_num != -1) + return args[else_expr_num]->is_bool_func(); + return true; + } + return false; +} + my_decimal *Item_func_case::val_decimal(my_decimal *decimal_value) { DBUG_ASSERT(fixed == 1); char buff[MAX_FIELD_WIDTH]; diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 12a98912e04..9de4307c2b0 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -1116,6 +1116,7 @@ class Item_func_if final : public Item_func { uint decimal_precision() const override; const char *func_name() const override { return "if"; } enum Functype functype() const override { return IF_FUNC; } + bool is_bool_func() const override; }; class Item_func_nullif final : public Item_bool_func2 { @@ -1599,6 +1600,7 @@ class Item_func_case final : public Item_func { const char *func_name() const override { return "case"; } void print(const THD *thd, String *str, enum_query_type query_type) const override; + bool is_bool_func() const override; Item *find_item(String *str); const CHARSET_INFO *compare_collation() const override { return cmp_collation.collation; From 4014ef13e47abe74e521d51d6cd8048d168f358a Mon Sep 17 00:00:00 2001 From: Daniel Black Date: Sat, 4 May 2019 13:23:48 +1000 Subject: [PATCH 2/2] bug #95233: add true/false and 0/1 to the is_bool_func --- mysql-test/r/check_constraints.result | 6 ++++-- mysql-test/t/check_constraints.test | 5 ++++- sql/item.h | 1 + 3 files changed, 9 insertions(+), 3 deletions(-) diff --git a/mysql-test/r/check_constraints.result b/mysql-test/r/check_constraints.result index b0d4ddeb68a..1323fb99466 100644 --- a/mysql-test/r/check_constraints.result +++ b/mysql-test/r/check_constraints.result @@ -100,6 +100,8 @@ CREATE TABLE t1(f2 int CHECK(CASE WHEN f2 THEN "f2 is a chicken" ELSE f2 IS NULL ERROR HY000: An expression of non-boolean type specified to a check constraint 't1_chk_1'. CREATE TABLE t1(f2 int CHECK(CASE WHEN f2 THEN f2 < 5 ELSE "f2 is a dog" END)); ERROR HY000: An expression of non-boolean type specified to a check constraint 't1_chk_1'. +CREATE TABLE t1(f2 int CHECK(CASE WHEN f2 THEN 5 ELSE -1 END)); +ERROR HY000: An expression of non-boolean type specified to a check constraint 't1_chk_1'. CREATE TABLE t1(f1 int, CONSTRAINT t1_ck CHECK(f1<10), CONSTRAINT t2_ck CHECK(f2 > 1)); ERROR HY000: Check constraint 't2_ck' refers to non-existing column 'f2'. CREATE TABLE t1(f1 int, CHECK(f1<10), CONSTRAINT t2_ck CHECK(f1 > 1)); @@ -314,7 +316,7 @@ DROP TABLE t1; #----------------------------------------------------------------------- CREATE TABLE t1(c1 int CHECK(IF(c1 < 10, c1 < 3, c1 IS NULL)), c2 int CHECK(CASE WHEN c2 THEN c2 < 0 ELSE c2 IS NULL END), -s varchar(12) CHECK(CASE WHEN s = "monkey" THEN 12 > 11 ELSE s IS NULL END) +s varchar(12) CHECK(CASE WHEN s = "monkey" THEN TRUE ELSE s IS NULL END) ); SHOW CREATE TABLE t1; Table Create Table @@ -324,7 +326,7 @@ t1 CREATE TABLE `t1` ( `s` varchar(12) DEFAULT NULL, CONSTRAINT `t1_chk_1` CHECK (if((`c1` < 10),(`c1` < 3),isnull(`c1`))), CONSTRAINT `t1_chk_2` CHECK ((case when `c2` then (`c2` < 0) else isnull(`c2`) end)), - CONSTRAINT `t1_chk_3` CHECK ((case when (`s` = _utf8mb4'monkey') then (12 > 11) else isnull(`s`) end)) + CONSTRAINT `t1_chk_3` CHECK ((case when (`s` = _utf8mb4'monkey') then TRUE else isnull(`s`) end)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci INSERT INTO t1(c1,c2,s) VALUES(5, -1, "monkey"); ERROR HY000: Check constraint 't1_chk_1' is violated. diff --git a/mysql-test/t/check_constraints.test b/mysql-test/t/check_constraints.test index 522d17d8e96..682696f3d67 100644 --- a/mysql-test/t/check_constraints.test +++ b/mysql-test/t/check_constraints.test @@ -122,6 +122,9 @@ CREATE TABLE t1(f2 int CHECK(CASE WHEN f2 THEN "f2 is a chicken" ELSE f2 IS NULL --error ER_NON_BOOLEAN_EXPR_FOR_CHECK_CONSTRAINT CREATE TABLE t1(f2 int CHECK(CASE WHEN f2 THEN f2 < 5 ELSE "f2 is a dog" END)); +--error ER_NON_BOOLEAN_EXPR_FOR_CHECK_CONSTRAINT +CREATE TABLE t1(f2 int CHECK(CASE WHEN f2 THEN 5 ELSE -1 END)); + --error ER_CHECK_CONSTRAINT_REFERS_UNKNOWN_COLUMN CREATE TABLE t1(f1 int, CONSTRAINT t1_ck CHECK(f1<10), CONSTRAINT t2_ck CHECK(f2 > 1)); @@ -283,7 +286,7 @@ DROP TABLE t1; --echo #----------------------------------------------------------------------- CREATE TABLE t1(c1 int CHECK(IF(c1 < 10, c1 < 3, c1 IS NULL)), c2 int CHECK(CASE WHEN c2 THEN c2 < 0 ELSE c2 IS NULL END), - s varchar(12) CHECK(CASE WHEN s = "monkey" THEN 12 > 11 ELSE s IS NULL END) + s varchar(12) CHECK(CASE WHEN s = "monkey" THEN TRUE ELSE s IS NULL END) ); SHOW CREATE TABLE t1; --error ER_CHECK_CONSTRAINT_VIOLATED diff --git a/sql/item.h b/sql/item.h index 17c6cd9cada..c536daa3c02 100644 --- a/sql/item.h +++ b/sql/item.h @@ -3887,6 +3887,7 @@ class Item_int : public Item_num { return (uint)(max_length - (value < 0)); } bool eq(const Item *, bool) const override; + virtual bool is_bool_func() const override { return value == 0LL || value == 1LL; } bool check_partition_func_processor(uchar *) override { return false; } bool check_function_as_value_generator(uchar *) override { return false; } };