MDEV-8441 Bad SHOW CREATE TABLE output for a table with a virtual column

2 files changed, 31 insertions, 26 deletions

diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index eda8e2daf96..73a5b662fbd 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -1810,35 +1810,36 @@ int show_create_table(THD *thd, TABLE_LIST *table_list, String *packet,
       else
         packet->append(STRING_WITH_LEN(" VIRTUAL"));
     }
-
-    if (flags & NOT_NULL_FLAG)
-      packet->append(STRING_WITH_LEN(" NOT NULL"));
-    else if (field->type() == MYSQL_TYPE_TIMESTAMP)
-    {
-      /*
-        TIMESTAMP field require explicit NULL flag, because unlike
-        all other fields they are treated as NOT NULL by default.
-      */
-      packet->append(STRING_WITH_LEN(" NULL"));
-    }
-
-    if (!field->vcol_info &&
-        get_field_default_value(thd, field, &def_value, 1))
+    else
     {
-      packet->append(STRING_WITH_LEN(" DEFAULT "));
-      packet->append(def_value.ptr(), def_value.length(), system_charset_info);
-    }
+      if (flags & NOT_NULL_FLAG)
+        packet->append(STRING_WITH_LEN(" NOT NULL"));
+      else if (field->type() == MYSQL_TYPE_TIMESTAMP)
+      {
+        /*
+          TIMESTAMP field require explicit NULL flag, because unlike
+          all other fields they are treated as NOT NULL by default.
+        */
+        packet->append(STRING_WITH_LEN(" NULL"));
+      }
 
-    if (!limited_mysql_mode && print_on_update_clause(field, &def_value, false))
-    {
-      packet->append(STRING_WITH_LEN(" "));
-      packet->append(def_value);
-    }
+      if (get_field_default_value(thd, field, &def_value, 1))
+      {
+        packet->append(STRING_WITH_LEN(" DEFAULT "));
+        packet->append(def_value.ptr(), def_value.length(), system_charset_info);
+      }
 
+      if (!limited_mysql_mode &&
+          print_on_update_clause(field, &def_value, false))
+      {
+        packet->append(STRING_WITH_LEN(" "));
+        packet->append(def_value);
+      }
 
-    if (field->unireg_check == Field::NEXT_NUMBER &&
-        !(sql_mode & MODE_NO_FIELD_OPTIONS))
-      packet->append(STRING_WITH_LEN(" AUTO_INCREMENT"));
+      if (field->unireg_check == Field::NEXT_NUMBER &&
+          !(sql_mode & MODE_NO_FIELD_OPTIONS))
+        packet->append(STRING_WITH_LEN(" AUTO_INCREMENT"));
+    }
 
     if (field->comment.length)
     {
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 019650ae0c2..fa14204110a 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -3061,6 +3061,9 @@ CHARSET_INFO* get_sql_field_charset(Create_field *sql_field,
    Modifies the first column definition whose SQL type is TIMESTAMP
    by adding the features DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.
 
+   If the first TIMESTAMP column appears to be nullable, or to have an
+   explicit default, or to be a virtual column, then no promition is done.
+
    @param column_definitions The list of column definitions, in the physical
                              order in which they appear in the table.
  */
@@ -3076,7 +3079,8 @@ void promote_first_timestamp_column(List<Create_field> *column_definitions)
     {
       if ((column_definition->flags & NOT_NULL_FLAG) != 0 && // NOT NULL,
           column_definition->def == NULL &&            // no constant default,
-          column_definition->unireg_check == Field::NONE) // no function default
+          column_definition->unireg_check == Field::NONE && // no function default
+          column_definition->vcol_info == NULL)
       {
         DBUG_PRINT("info", ("First TIMESTAMP column '%s' was promoted to "
                             "DEFAULT CURRENT_TIMESTAMP ON UPDATE "