MDEV-23968 CREATE TEMPORARY TABLE .. LIKE (system versioned table) returns error if unique index is defined in the table

- Remove row_start/row_end from keys in fix_create_like();

- Disable manual adding of implicit row_start/row_end to indexes on
  CREATE TABLE. INVISIBLE_SYSTEM fields are unoperable by user;

- Fix memory leak on allocation of Key_part_spec.

5 files changed, 120 insertions, 9 deletions

diff --git a/mysql-test/suite/versioning/r/create.result b/mysql-test/suite/versioning/r/create.result
index 5189e55bc30..65fbfaefcbd 100644
--- a/mysql-test/suite/versioning/r/create.result
+++ b/mysql-test/suite/versioning/r/create.result
@@ -524,3 +524,59 @@ with system versioning
 select 1 as x;
 ERROR HY000: Table `t1` must have at least one versioned column
 drop tables t0, t1, t2, t3;
+#
+# MDEV-23968 CREATE TEMPORARY TABLE .. LIKE (system versioned table) returns error if unique index is defined in the table
+#
+create table t1 (id int primary key, index(row_start)) with system versioning;
+ERROR 42000: Key column 'row_start' doesn't exist in table
+create table t1 (id int primary key, index(row_end)) with system versioning;
+ERROR 42000: Key column 'row_end' doesn't exist in table
+create table t1 (id int, primary key(id, row_end, row_end)) with system versioning;
+ERROR 42000: Key column 'row_end' doesn't exist in table
+create table t1 (id int primary key) with system versioning;
+create temporary table t2 like t1;
+Warnings:
+Warning	1105	System versioning is stripped from temporary `test.t2`
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` int(11) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+show create table t2;
+Table	Create Table
+t2	CREATE TEMPORARY TABLE `t2` (
+  `id` int(11) NOT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+drop temporary table t2;
+create or replace table t1 (
+a int,
+row_start timestamp(6) as row start,
+row_end timestamp(6) as row end,
+period for system_time (row_start, row_end),
+index(row_start),
+index(row_end),
+primary key(row_end, a, row_start),
+index(row_end, row_start, a)) with system versioning;
+create temporary table t2 like t1;
+Warnings:
+Warning	1105	System versioning is stripped from temporary `test.t2`
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `row_start` timestamp(6) GENERATED ALWAYS AS ROW START,
+  `row_end` timestamp(6) GENERATED ALWAYS AS ROW END,
+  PRIMARY KEY (`row_end`,`a`,`row_start`),
+  KEY `row_start` (`row_start`),
+  KEY `row_end` (`row_end`),
+  KEY `row_end_2` (`row_end`,`row_start`,`a`),
+  PERIOD FOR SYSTEM_TIME (`row_start`, `row_end`)
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+show create table t2;
+Table	Create Table
+t2	CREATE TEMPORARY TABLE `t2` (
+  `a` int(11) NOT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+drop temporary table t2;
+drop table t1;
diff --git a/mysql-test/suite/versioning/t/create.test b/mysql-test/suite/versioning/t/create.test
index fc4120ef2aa..8d3131cff74 100644
--- a/mysql-test/suite/versioning/t/create.test
+++ b/mysql-test/suite/versioning/t/create.test
@@ -406,3 +406,36 @@ with system versioning
 select 1 as x;
 
 drop tables t0, t1, t2, t3;
+
+--echo #
+--echo # MDEV-23968 CREATE TEMPORARY TABLE .. LIKE (system versioned table) returns error if unique index is defined in the table
+--echo #
+--error ER_KEY_COLUMN_DOES_NOT_EXITS
+create table t1 (id int primary key, index(row_start)) with system versioning;
+--error ER_KEY_COLUMN_DOES_NOT_EXITS
+create table t1 (id int primary key, index(row_end)) with system versioning;
+--error ER_KEY_COLUMN_DOES_NOT_EXITS
+create table t1 (id int, primary key(id, row_end, row_end)) with system versioning;
+create table t1 (id int primary key) with system versioning;
+create temporary table t2 like t1;
+--replace_result $default_engine DEFAULT_ENGINE
+show create table t1;
+--replace_result $default_engine DEFAULT_ENGINE
+show create table t2;
+drop temporary table t2;
+create or replace table t1 (
+  a int,
+  row_start timestamp(6) as row start,
+  row_end timestamp(6) as row end,
+  period for system_time (row_start, row_end),
+  index(row_start),
+  index(row_end),
+  primary key(row_end, a, row_start),
+  index(row_end, row_start, a)) with system versioning;
+create temporary table t2 like t1;
+--replace_result $default_engine DEFAULT_ENGINE
+show create table t1;
+--replace_result $default_engine DEFAULT_ENGINE
+show create table t2;
+drop temporary table t2;
+drop table t1;
diff --git a/sql/handler.cc b/sql/handler.cc
index dcc130c340e..664289eaa27 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -7390,6 +7390,8 @@ Vers_parse_info::fix_create_like(Alter_info &alter_info, HA_CREATE_INFO &create_
                                  TABLE_LIST &src_table, TABLE_LIST &table)
 {
   List_iterator<Create_field> it(alter_info.create_list);
+  List_iterator<Key> key_it(alter_info.key_list);
+  List_iterator<Key_part_spec> kp_it;
   Create_field *f, *f_start=NULL, *f_end= NULL;
 
   DBUG_ASSERT(alter_info.create_list.elements > 2);
@@ -7404,6 +7406,23 @@ Vers_parse_info::fix_create_like(Alter_info &alter_info, HA_CREATE_INFO &create_
         it.remove();
         remove--;
       }
+      key_it.rewind();
+      while (Key *key= key_it++)
+      {
+        kp_it.init(key->columns);
+        while (Key_part_spec *kp= kp_it++)
+        {
+          if (0 == lex_string_cmp(system_charset_info, &kp->field_name,
+                                  &f->field_name))
+          {
+            kp_it.remove();
+          }
+        }
+        if (0 == key->columns.elements)
+        {
+          key_it.remove();
+        }
+      }
     }
     DBUG_ASSERT(remove == 0);
     push_warning_printf(current_thd, Sql_condition::WARN_LEVEL_WARN,
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 5ced820a34d..0645452c594 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -254,8 +254,9 @@ class Key_part_spec :public Sql_alloc {
 public:
   LEX_CSTRING field_name;
   uint length;
-  Key_part_spec(const LEX_CSTRING *name, uint len)
-    : field_name(*name), length(len)
+  bool generated;
+  Key_part_spec(const LEX_CSTRING *name, uint len, bool gen= false)
+    : field_name(*name), length(len), generated(gen)
   {}
   bool operator==(const Key_part_spec& other) const;
   /**
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 7c448dea4b1..4578cb89d28 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -3328,7 +3328,7 @@ mysql_add_invisible_index(THD *thd, List<Key> *key_list,
   Key *key= NULL;
   key= new (thd->mem_root) Key(type, &null_clex_str, HA_KEY_ALG_UNDEF,
          false, DDL_options(DDL_options::OPT_NONE));
-  key->columns.push_back(new(thd->mem_root) Key_part_spec(field_name, 0),
+  key->columns.push_back(new(thd->mem_root) Key_part_spec(field_name, 0, true),
           thd->mem_root);
   key_list->push_back(key, thd->mem_root);
   return key;
@@ -3843,7 +3843,8 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
       /*
          Either field is not present or field visibility is > INVISIBLE_USER
       */
-      if (!sql_field)
+      if (!sql_field || (sql_field->invisible > INVISIBLE_USER &&
+                         !column->generated))
       {
 	my_error(ER_KEY_COLUMN_DOES_NOT_EXITS, MYF(0), column->field_name.str);
 	DBUG_RETURN(TRUE);
@@ -4509,9 +4510,10 @@ static bool vers_prepare_keys(THD *thd, HA_CREATE_INFO *create_info,
     if (key_part)
       continue; // Key already contains Sys_start or Sys_end
 
-    Key_part_spec *key_part_sys_end_col=
-        new (thd->mem_root) Key_part_spec(&create_info->vers_info.as_row.end, 0);
-    key->columns.push_back(key_part_sys_end_col);
+    Key_part_spec *row_end=
+        new (thd->mem_root) Key_part_spec(&create_info->vers_info.as_row.end, 0,
+                                          true);
+    key->columns.push_back(row_end);
   }
 
   return false;
@@ -8408,8 +8410,8 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
 	  key_part_length= 0;			// Use whole field
       }
       key_part_length /= kfield->charset()->mbmaxlen;
-      key_parts.push_back(new Key_part_spec(&cfield->field_name,
-					    key_part_length),
+      key_parts.push_back(new (thd->mem_root) Key_part_spec(&cfield->field_name,
+                                                            key_part_length, true),
                           thd->mem_root);
     }
     if (table->s->tmp_table == NO_TMP_TABLE)