MDEV-13916 Enforce check constraint on JSON type

When creating a field of type JSON, it will be automatically
converted to TEXT with CHECK (json_valid(`a`)), if there wasn't any
previous check for the column.

Additional things:
- Added two bug fixes that was found while testing JSON. These bug
  fixes has also been pushed to 10.3 (with a test case), but as they
  where minimal and needed to get this task done and tested, the fixes
  are repeated here.
  - CREATE TABLE ... SELECT drops constraints for columns that
    are both in the create and select part.
  - 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`.
- Removed some duplicate MYSQL_PLUGIN_IMPORT symbols

8 files changed, 64 insertions, 15 deletions

diff --git a/sql/field.cc b/sql/field.cc
index eef1061be4a..6e45d10d855 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -10762,6 +10762,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/sql_lex.cc b/sql/sql_lex.cc
index 8e31f479d68..df45a2f201e 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -9579,3 +9579,21 @@ bool LEX::sp_proc_stmt_statement_finalize(THD *thd, bool no_lookahead)
                                                        lip->get_tok_start());
   return LEX::sp_proc_stmt_statement_finalize_buf(thd, qbuf);
 }
+
+
+/**
+   Create JSON_VALID(field_name) expression
+*/
+
+Virtual_column_info *make_json_valid_expr(THD *thd, LEX_CSTRING *field_name)
+{
+  Lex_ident_sys_st str;
+  Item *field, *expr;
+  str.set_valid_utf8(field_name);
+  if (unlikely(!(field= thd->lex->create_item_ident_field(thd, NullS, NullS,
+                                                          &str))))
+    return 0;
+  if (unlikely(!(expr= new (thd->mem_root) Item_func_json_valid(thd, field))))
+    return 0;
+  return add_virtual_expression(thd, expr);
+}
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index c22f95978a0..ed42c2efc72 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -148,6 +148,12 @@ public:
   bool copy_or_convert(THD *thd, const Lex_ident_cli_st *str, CHARSET_INFO *cs);
   bool is_null() const { return str == NULL; }
   bool to_size_number(ulonglong *to) const;
+  void set_valid_utf8(LEX_CSTRING *name)
+  {
+    DBUG_ASSERT(Well_formed_prefix(system_charset_info, name->str,
+                                   name->length).length() == name->length);
+    str= name->str ; length= name->length;
+  }
 };
 
 
@@ -4600,5 +4606,6 @@ Item* handle_sql2003_note184_exception(THD *thd, Item* left, bool equal,
 void sp_create_assignment_lex(THD *thd, bool no_lookahead);
 bool sp_create_assignment_instr(THD *thd, bool no_lookahead);
 
+Virtual_column_info *make_json_valid_expr(THD *thd, LEX_CSTRING *field_name);
 #endif /* MYSQL_SERVER */
 #endif /* SQL_LEX_INCLUDED */
diff --git a/sql/sql_type.cc b/sql/sql_type.cc
index 96a73a85267..6cccc785fee 100644
--- a/sql/sql_type.cc
+++ b/sql/sql_type.cc
@@ -66,6 +66,7 @@ Type_handler_tiny_blob   type_handler_tiny_blob;
 Type_handler_medium_blob type_handler_medium_blob;
 Type_handler_long_blob   type_handler_long_blob;
 Type_handler_blob        type_handler_blob;
+Type_handler_json        type_handler_json;
 static Type_handler_blob_compressed type_handler_blob_compressed;
 
 Type_handler_interval_DDhhmmssff type_handler_interval_DDhhmmssff;
diff --git a/sql/sql_type.h b/sql/sql_type.h
index 3eb7d39742c..d507241075e 100644
--- a/sql/sql_type.h
+++ b/sql/sql_type.h
@@ -3280,6 +3280,7 @@ public:
     return true;
   }
   virtual bool is_scalar_type() const { return true; }
+  virtual bool is_json_type() const { return false; }
   virtual bool can_return_int() const { return true; }
   virtual bool can_return_decimal() const { return true; }
   virtual bool can_return_real() const { return true; }
@@ -5890,6 +5891,14 @@ public:
 };
 
 
+class Type_handler_json: public Type_handler_long_blob
+{
+public:
+  virtual ~Type_handler_json() {}
+  virtual bool is_json_type() const { return true; }
+};
+
+
 class Type_handler_blob: public Type_handler_blob_common
 {
   static const Name m_name_blob;
@@ -6218,6 +6227,7 @@ extern MYSQL_PLUGIN_IMPORT Type_handler_hex_hybrid  type_handler_hex_hybrid;
 extern MYSQL_PLUGIN_IMPORT Type_handler_tiny_blob   type_handler_tiny_blob;
 extern MYSQL_PLUGIN_IMPORT Type_handler_medium_blob type_handler_medium_blob;
 extern MYSQL_PLUGIN_IMPORT Type_handler_long_blob   type_handler_long_blob;
+extern MYSQL_PLUGIN_IMPORT Type_handler_json        type_handler_json;
 extern MYSQL_PLUGIN_IMPORT Type_handler_blob        type_handler_blob;
 
 extern MYSQL_PLUGIN_IMPORT Type_handler_bool        type_handler_bool;
@@ -6243,11 +6253,6 @@ extern MYSQL_PLUGIN_IMPORT Type_handler_datetime2   type_handler_datetime2;
 extern MYSQL_PLUGIN_IMPORT Type_handler_timestamp   type_handler_timestamp;
 extern MYSQL_PLUGIN_IMPORT Type_handler_timestamp2  type_handler_timestamp2;
 
-extern MYSQL_PLUGIN_IMPORT Type_handler_tiny_blob   type_handler_tiny_blob;
-extern MYSQL_PLUGIN_IMPORT Type_handler_blob        type_handler_blob;
-extern MYSQL_PLUGIN_IMPORT Type_handler_medium_blob type_handler_medium_blob;
-extern MYSQL_PLUGIN_IMPORT Type_handler_long_blob   type_handler_long_blob;
-
 extern MYSQL_PLUGIN_IMPORT Type_handler_interval_DDhhmmssff
   type_handler_interval_DDhhmmssff;
 
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 81ece13e1c5..2077ea52557 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -6690,6 +6690,10 @@ field_spec:
             $$= $<create_field>2;
 
             $$->check_constraint= $4;
+            if (!$4 && lex->last_field->type_handler()->is_json_type() &&
+                !($$->check_constraint= make_json_valid_expr(thd,
+                                                             &$$->field_name)))
+              MYSQL_YYABORT;
 
             if (unlikely($$->check(thd)))
               MYSQL_YYABORT;
@@ -7083,7 +7087,7 @@ field_type_lob:
         | JSON_SYM
           {
             Lex->charset= &my_charset_utf8mb4_bin;
-            $$.set(&type_handler_long_blob);
+            $$.set(&type_handler_json);
           }
         ;
 
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index aa622b61771..4e14be598ce 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -6628,6 +6628,10 @@ field_spec:
             $$= $<create_field>2;
 
             $$->check_constraint= $4;
+            if (!$4 && lex->last_field->type_handler()->is_json_type() &&
+                !($$->check_constraint= make_json_valid_expr(thd,
+                                                             &$$->field_name)))
+              MYSQL_YYABORT;
 
             if (unlikely($$->check(thd)))
               MYSQL_YYABORT;
@@ -7073,7 +7077,7 @@ field_type_lob:
         | JSON_SYM
           {
             Lex->charset= &my_charset_utf8mb4_bin;
-            $$.set(&type_handler_long_blob);
+            $$.set(&type_handler_json);
           }
         ;
 
diff --git a/sql/table.cc b/sql/table.cc
index d5c88b226c7..1ef216a2991 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -52,7 +52,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")};
@@ -1189,9 +1190,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;
   }
 
@@ -3133,11 +3134,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;
 }