Bug #95233 check constraint doesn't consider IF function that returns boolean a boolean fun
Submitted: 3 May 2019 1:46 Modified: 23 Mar 2020 4:54
Reporter: Daniel Black Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:8.0.16 OS:Any
Assigned to: CPU Architecture:Any

[3 May 2019 1:46] Daniel Black
Description:
An IF() function in a check constraint isn't considered a boolean type.

How to repeat:
create table t1 (source enum('comment','post') NOT NULL, comment_id int unsigned, post_id int unsigned);

alter table t1 add check(IF(source = 'comment', comment_id IS NOT NULL AND post_id IS NULL, post_id IS NOT NULL AND comment_id IS NULL))

error:
An expression of non-boolean type specified to a check constraint 't1_chk_1'.

note:

Can be worked around, just becomes verbose

alter table t1 add check((source = 'comment' AND comment_id IS NOT NULL AND post_id IS NULL) OR (source = 'post' AND post_id IS NOT NULL AND comment_id IS NULL))

Suggested fix:
For IF/(CASE?) functions, evaluate the type of the expression.
[3 May 2019 5:54] MySQL Verification Team
Hello Daniel,

Thank you for the report and test case.

Thanks,
Umesh
[3 May 2019 6:14] Roy Lyseng
The main problem here is that MySQL does not implement a boolean type internally.

A simpler workaround may be this:

  alter table t1 add check(IF(source = 'comment', comment_id IS NOT NULL AND
  post_id IS NULL, post_id IS NOT NULL AND comment_id IS NULL) = 1);

which utilizes that a TRUE value is represented as 1 internally.
[3 May 2019 6:59] Daniel Black
The lack of boolean wasn't the problem, just the lack of a is_bool_func implementation. Quickly rectified and tested as below:

$ git diff
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 8c32859a389..c0da3b55069 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);
 
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 12a98912e04..3afa17b723c 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 {

----------- MYSQLTEST OUTPUT START -----------

create table t1 (source enum('comment','post') NOT NULL, comment_id int unsigned, post_id int unsigned);
alter table t1 add check(IF(source = 'comment', comment_id IS NOT NULL AND post_id IS NULL, post_id IS NOT NULL AND comment_id IS NULL));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `source` enum('comment','post') NOT NULL,
  `comment_id` int(10) unsigned DEFAULT NULL,
  `post_id` int(10) unsigned DEFAULT NULL,
  CONSTRAINT `t1_chk_1` CHECK (if((`source` = _utf8mb4'comment'),((`comment_id` is not null) and isnull(`post_id`)),((`post_id` is not null) and isnull(`comment_id`))))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

------------ MYSQLTEST OUTPUT END -----------
[3 May 2019 7:50] Roy Lyseng
Hi Daniel,

while this will fix your problem, I'm not so sure overall. is_bool_func() is supposed to be a property of the SQL function, regardless of the data types of its arguments. But we will consider the suggestion.
[3 May 2019 7:51] MySQL Verification Team
Thank you, please ensure to re-send the patch via "contribution" tab. Otherwise we would not be able to accept it.

regards,
Umesh
[15 May 2019 14:51] OCA Admin
Contribution submitted via Github - bug #95233: check constraints now support case/if functions 
(*) Contribution by Daniel Black (Github grooverdan, mysql-server/pull/263#issuecomment-492470224): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_275645126.txt (text/plain), 10.84 KiB.

[23 Mar 2020 4:54] Daniel Black
Hey Roy,

Now that it's been a while since the original patch, have you reconsidered? As the function is dynamic, its boolean status may as well be dynamic too. It's a pretty low overhead for the check to add this functionality.