From 438811b4b28fab6c775522bc35b53923c8e1e9c4 Mon Sep 17 00:00:00 2001
From: Monty <monty@mariadb.org>
Date: Wed, 13 Feb 2019 18:21:19 +0200
Subject: Fixed two bugs related to column level constraints

- CREATE TABLE ... SELECT drops constraints for columns that
  are both in the create and select part.
  - Fixed by copying the constraint in
    Column_definiton::redefine_stage1_common()
- If one has both a default expression and check constraint for a
  column, one can get the error "Expression for field `a` is refering
  to uninitialized field `a`.
  - Fixed by ignoring default expressions for current column when checking
    for CHECK constraint
---
 mysql-test/main/constraints.result | 17 +++++++++++++++++
 mysql-test/main/constraints.test   | 20 ++++++++++++++++++++
 sql/field.cc                       |  1 +
 sql/table.cc                       | 25 +++++++++++++++++--------
 4 files changed, 55 insertions(+), 8 deletions(-)

diff --git a/mysql-test/main/constraints.result b/mysql-test/main/constraints.result
index 3c061989fd3..aba226cdd33 100644
--- a/mysql-test/main/constraints.result
+++ b/mysql-test/main/constraints.result
@@ -111,3 +111,20 @@ long_enough_name	CREATE TABLE `long_enough_name` (
   CONSTRAINT `constr` CHECK (`f6` >= 0)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE long_enough_name;
+create table t1 (a int check (a>10)) select 100 as 'a';
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL CHECK (`a` > 10)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+create table t1 (a text default(length(now())) check (length(a) > 1));
+insert into t1 values ();
+insert into t1 values ("ccc");
+insert into t1 values ("");
+ERROR 23000: CONSTRAINT `t1.a` failed for `test`.`t1`
+select * from t1;
+a
+19
+ccc
+drop table t1;
diff --git a/mysql-test/main/constraints.test b/mysql-test/main/constraints.test
index fe51e5060dc..c06f585d04f 100644
--- a/mysql-test/main/constraints.test
+++ b/mysql-test/main/constraints.test
@@ -102,3 +102,23 @@ SELECT * FROM long_enough_name AS tbl;
 SHOW CREATE TABLE long_enough_name;
  
 DROP TABLE long_enough_name;
+
+#
+# Check that we don't loose constraints as part of CREATE ... SELECT
+#
+
+create table t1 (a int check (a>10)) select 100 as 'a';
+show create table t1;
+drop table t1;
+
+#
+# Check that we constraints on field with default expressions work
+#
+
+create table t1 (a text default(length(now())) check (length(a) > 1));
+insert into t1 values ();
+insert into t1 values ("ccc");
+--error ER_CONSTRAINT_FAILED
+insert into t1 values ("");
+select * from t1;
+drop table t1;
diff --git a/sql/field.cc b/sql/field.cc
index fbaf3c6ea91..a20525b2764 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -11047,6 +11047,7 @@ Column_definition::redefine_stage1_common(const Column_definition *dup_field,
   interval=     dup_field->interval;
   vcol_info=    dup_field->vcol_info;
   invisible=    dup_field->invisible;
+  check_constraint= dup_field->check_constraint;
 }
 
 
diff --git a/sql/table.cc b/sql/table.cc
index a44e0fcc0a5..d4b1be17c11 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -51,7 +51,8 @@
 
 static Virtual_column_info * unpack_vcol_info_from_frm(THD *, MEM_ROOT *,
               TABLE *, String *, Virtual_column_info **, bool *);
-static bool check_vcol_forward_refs(Field *, Virtual_column_info *);
+static bool check_vcol_forward_refs(Field *, Virtual_column_info *,
+                                    bool check_constraint);
 
 /* INFORMATION_SCHEMA name */
 LEX_CSTRING INFORMATION_SCHEMA_NAME= {STRING_WITH_LEN("information_schema")};
@@ -1135,9 +1136,9 @@ bool parse_vcol_defs(THD *thd, MEM_ROOT *mem_root, TABLE *table,
   for (field_ptr= table->field; *field_ptr; field_ptr++)
   {
     Field *field= *field_ptr;
-    if (check_vcol_forward_refs(field, field->vcol_info) ||
-        check_vcol_forward_refs(field, field->check_constraint) ||
-        check_vcol_forward_refs(field, field->default_value))
+    if (check_vcol_forward_refs(field, field->vcol_info, 0) ||
+        check_vcol_forward_refs(field, field->check_constraint, 1) ||
+        check_vcol_forward_refs(field, field->default_value, 0))
       goto end;
   }
 
@@ -3087,11 +3088,19 @@ end:
   DBUG_RETURN(vcol_info);
 }
 
-static bool check_vcol_forward_refs(Field *field, Virtual_column_info *vcol)
+static bool check_vcol_forward_refs(Field *field, Virtual_column_info *vcol,
+                                    bool check_constraint)
 {
-  bool res= vcol &&
-            vcol->expr->walk(&Item::check_field_expression_processor, 0,
-                                  field);
+  bool res;
+  uint32 flags= field->flags;
+  if (check_constraint)
+  {
+    /* Check constraints can refer it itself */
+    field->flags|= NO_DEFAULT_VALUE_FLAG;
+  }
+  res= (vcol &&
+        vcol->expr->walk(&Item::check_field_expression_processor, 0, field));
+  field->flags= flags;
   return res;
 }
 
-- 
cgit v1.2.1