Merge remote-tracking branch 'origin/10.2' into 10.3

19 files changed, 374 insertions, 23 deletions

diff --git a/mysql-test/main/ctype_collate.result b/mysql-test/main/ctype_collate.result
index 93bf07908b5..62a92ac9d92 100644
--- a/mysql-test/main/ctype_collate.result
+++ b/mysql-test/main/ctype_collate.result
@@ -756,3 +756,20 @@ hex(b)
 
 61
 drop table t1;
+#
+# Start of 10.2 tests
+#
+#
+# MDEV-27690 Crash on `CHARACTER SET csname COLLATE DEFAULT` in column definition
+#
+CREATE TABLE t1 (a CHAR(10) CHARACTER SET latin1 COLLATE DEFAULT);
+DROP TABLE t1;
+SELECT CAST('a' AS CHAR(10) CHARACTER SET latin1 COLLATE DEFAULT);
+CAST('a' AS CHAR(10) CHARACTER SET latin1 COLLATE DEFAULT)
+a
+SELECT COLUMN_GET(COLUMN_CREATE(0, 'string'),0 AS CHAR CHARACTER SET latin1 COLLATE DEFAULT) AS c1;
+c1
+string
+#
+# End of 10.2 tests
+#
diff --git a/mysql-test/main/ctype_collate.test b/mysql-test/main/ctype_collate.test
index 6704395a0a1..ce8559732ee 100644
--- a/mysql-test/main/ctype_collate.test
+++ b/mysql-test/main/ctype_collate.test
@@ -339,3 +339,20 @@ select hex(b) from t1 where b<'zzz' order by b;
 
 drop table t1;
 
+--echo #
+--echo # Start of 10.2 tests
+--echo #
+
+--echo #
+--echo # MDEV-27690 Crash on `CHARACTER SET csname COLLATE DEFAULT` in column definition
+--echo #
+
+CREATE TABLE t1 (a CHAR(10) CHARACTER SET latin1 COLLATE DEFAULT);
+DROP TABLE t1;
+SELECT CAST('a' AS CHAR(10) CHARACTER SET latin1 COLLATE DEFAULT);
+SELECT COLUMN_GET(COLUMN_CREATE(0, 'string'),0 AS CHAR CHARACTER SET latin1 COLLATE DEFAULT) AS c1;
+
+
+--echo #
+--echo # End of 10.2 tests
+--echo #
diff --git a/mysql-test/main/select.result b/mysql-test/main/select.result
index 1970a984b44..2b49c927843 100644
--- a/mysql-test/main/select.result
+++ b/mysql-test/main/select.result
@@ -5546,4 +5546,50 @@ select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined));
 (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined))
 NULL
 drop table t1;
+#
+# Bug MDEV-24262 Server crashes in st_join_table::cleanup upon
+# erroneous GROUP_CONCAT
+#
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,10),(2,20);
+SELECT b, GROUP_CONCAT(b ORDER BY 2), MIN(a) AS f FROM t1 GROUP BY b ORDER BY f;
+ERROR 42S22: Unknown column '2' in 'order clause'
+DROP TABLE t1;
+#
+# Bug MDEV-24560 SIGSEGV in st_join_table::cleanup + server and client
+# hang + cross-mysqld-interaction + double free or corruption (!prev)
+#
+CREATE TABLE t1 (c INT);
+SET SESSION sql_buffer_result=1;
+SELECT GROUP_CONCAT(c ORDER BY 2) FROM t1;
+GROUP_CONCAT(c ORDER BY 2)
+NULL
+SELECT GROUP_CONCAT(c ORDER BY 0) FROM t1;
+GROUP_CONCAT(c ORDER BY 0)
+NULL
+DROP TABLE t1;
+CREATE TABLE t1 (grp INT,c CHAR);
+SET sql_buffer_result=1;
+SELECT grp,GROUP_CONCAT(c ORDER BY 2) FROM t1 GROUP BY grp;
+grp	GROUP_CONCAT(c ORDER BY 2)
+DROP TABLE t1;
+#
+# Bug MDEV-28077 "Wrong create options" with "big_tables" option enabled
+#
+CREATE TABLE t1(a VARCHAR(16383) CHARACTER SET UTF32, KEY k(a));
+Warnings:
+Note	1071	Specified key was too long; max key length is 1000 bytes
+INSERT INTO t1 VALUES ('abc'), ('def'), ('FFF'), ('abc'), ('FFF');
+SET SESSION big_tables=ON;
+SELECT DISTINCT COUNT(DISTINCT a) FROM t1;
+COUNT(DISTINCT a)
+3
+PREPARE stmt FROM 'SELECT DISTINCT COUNT(DISTINCT a) FROM t1';
+EXECUTE stmt;
+COUNT(DISTINCT a)
+3
+EXECUTE stmt;
+COUNT(DISTINCT a)
+3
+DROP TABLE t1;
 End of 10.0 tests
diff --git a/mysql-test/main/select.test b/mysql-test/main/select.test
index 7ac76286a3e..7e9d6bcf0ad 100644
--- a/mysql-test/main/select.test
+++ b/mysql-test/main/select.test
@@ -4671,4 +4671,50 @@ select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined));
 
 drop table t1;
 
+--echo #
+--echo # Bug MDEV-24262 Server crashes in st_join_table::cleanup upon
+--echo # erroneous GROUP_CONCAT
+--echo #
+
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,10),(2,20);
+--error ER_BAD_FIELD_ERROR
+SELECT b, GROUP_CONCAT(b ORDER BY 2), MIN(a) AS f FROM t1 GROUP BY b ORDER BY f;
+
+DROP TABLE t1;
+
+--echo #
+--echo # Bug MDEV-24560 SIGSEGV in st_join_table::cleanup + server and client
+--echo # hang + cross-mysqld-interaction + double free or corruption (!prev)
+--echo #
+
+CREATE TABLE t1 (c INT);
+SET SESSION sql_buffer_result=1;
+SELECT GROUP_CONCAT(c ORDER BY 2) FROM t1;
+SELECT GROUP_CONCAT(c ORDER BY 0) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (grp INT,c CHAR);
+SET sql_buffer_result=1;
+SELECT grp,GROUP_CONCAT(c ORDER BY 2) FROM t1 GROUP BY grp;
+DROP TABLE t1;
+
+--echo #
+--echo # Bug MDEV-28077 "Wrong create options" with "big_tables" option enabled
+--echo #
+CREATE TABLE t1(a VARCHAR(16383) CHARACTER SET UTF32, KEY k(a));
+INSERT INTO t1 VALUES ('abc'), ('def'), ('FFF'), ('abc'), ('FFF');
+
+# big_tables are deprecated after some version
+--disable_warnings
+SET SESSION big_tables=ON;
+SELECT DISTINCT COUNT(DISTINCT a) FROM t1;
+
+PREPARE stmt FROM 'SELECT DISTINCT COUNT(DISTINCT a) FROM t1';
+EXECUTE stmt;
+EXECUTE stmt;
+
+--enable_warnings
+DROP TABLE t1;
+
 --echo End of 10.0 tests
diff --git a/mysql-test/main/select_jcl6.result b/mysql-test/main/select_jcl6.result
index 12c01fb43a5..e411a916c94 100644
--- a/mysql-test/main/select_jcl6.result
+++ b/mysql-test/main/select_jcl6.result
@@ -5557,6 +5557,52 @@ select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined));
 (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined))
 NULL
 drop table t1;
+#
+# Bug MDEV-24262 Server crashes in st_join_table::cleanup upon
+# erroneous GROUP_CONCAT
+#
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,10),(2,20);
+SELECT b, GROUP_CONCAT(b ORDER BY 2), MIN(a) AS f FROM t1 GROUP BY b ORDER BY f;
+ERROR 42S22: Unknown column '2' in 'order clause'
+DROP TABLE t1;
+#
+# Bug MDEV-24560 SIGSEGV in st_join_table::cleanup + server and client
+# hang + cross-mysqld-interaction + double free or corruption (!prev)
+#
+CREATE TABLE t1 (c INT);
+SET SESSION sql_buffer_result=1;
+SELECT GROUP_CONCAT(c ORDER BY 2) FROM t1;
+GROUP_CONCAT(c ORDER BY 2)
+NULL
+SELECT GROUP_CONCAT(c ORDER BY 0) FROM t1;
+GROUP_CONCAT(c ORDER BY 0)
+NULL
+DROP TABLE t1;
+CREATE TABLE t1 (grp INT,c CHAR);
+SET sql_buffer_result=1;
+SELECT grp,GROUP_CONCAT(c ORDER BY 2) FROM t1 GROUP BY grp;
+grp	GROUP_CONCAT(c ORDER BY 2)
+DROP TABLE t1;
+#
+# Bug MDEV-28077 "Wrong create options" with "big_tables" option enabled
+#
+CREATE TABLE t1(a VARCHAR(16383) CHARACTER SET UTF32, KEY k(a));
+Warnings:
+Note	1071	Specified key was too long; max key length is 1000 bytes
+INSERT INTO t1 VALUES ('abc'), ('def'), ('FFF'), ('abc'), ('FFF');
+SET SESSION big_tables=ON;
+SELECT DISTINCT COUNT(DISTINCT a) FROM t1;
+COUNT(DISTINCT a)
+3
+PREPARE stmt FROM 'SELECT DISTINCT COUNT(DISTINCT a) FROM t1';
+EXECUTE stmt;
+COUNT(DISTINCT a)
+3
+EXECUTE stmt;
+COUNT(DISTINCT a)
+3
+DROP TABLE t1;
 End of 10.0 tests
 set join_cache_level=default;
 set @@optimizer_switch=@save_optimizer_switch_jcl6;
diff --git a/mysql-test/main/select_pkeycache.result b/mysql-test/main/select_pkeycache.result
index 1970a984b44..2b49c927843 100644
--- a/mysql-test/main/select_pkeycache.result
+++ b/mysql-test/main/select_pkeycache.result
@@ -5546,4 +5546,50 @@ select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined));
 (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined))
 NULL
 drop table t1;
+#
+# Bug MDEV-24262 Server crashes in st_join_table::cleanup upon
+# erroneous GROUP_CONCAT
+#
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,10),(2,20);
+SELECT b, GROUP_CONCAT(b ORDER BY 2), MIN(a) AS f FROM t1 GROUP BY b ORDER BY f;
+ERROR 42S22: Unknown column '2' in 'order clause'
+DROP TABLE t1;
+#
+# Bug MDEV-24560 SIGSEGV in st_join_table::cleanup + server and client
+# hang + cross-mysqld-interaction + double free or corruption (!prev)
+#
+CREATE TABLE t1 (c INT);
+SET SESSION sql_buffer_result=1;
+SELECT GROUP_CONCAT(c ORDER BY 2) FROM t1;
+GROUP_CONCAT(c ORDER BY 2)
+NULL
+SELECT GROUP_CONCAT(c ORDER BY 0) FROM t1;
+GROUP_CONCAT(c ORDER BY 0)
+NULL
+DROP TABLE t1;
+CREATE TABLE t1 (grp INT,c CHAR);
+SET sql_buffer_result=1;
+SELECT grp,GROUP_CONCAT(c ORDER BY 2) FROM t1 GROUP BY grp;
+grp	GROUP_CONCAT(c ORDER BY 2)
+DROP TABLE t1;
+#
+# Bug MDEV-28077 "Wrong create options" with "big_tables" option enabled
+#
+CREATE TABLE t1(a VARCHAR(16383) CHARACTER SET UTF32, KEY k(a));
+Warnings:
+Note	1071	Specified key was too long; max key length is 1000 bytes
+INSERT INTO t1 VALUES ('abc'), ('def'), ('FFF'), ('abc'), ('FFF');
+SET SESSION big_tables=ON;
+SELECT DISTINCT COUNT(DISTINCT a) FROM t1;
+COUNT(DISTINCT a)
+3
+PREPARE stmt FROM 'SELECT DISTINCT COUNT(DISTINCT a) FROM t1';
+EXECUTE stmt;
+COUNT(DISTINCT a)
+3
+EXECUTE stmt;
+COUNT(DISTINCT a)
+3
+DROP TABLE t1;
 End of 10.0 tests
diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl
index 10ed8760bdf..a01de00e0c2 100755
--- a/mysql-test/mysql-test-run.pl
+++ b/mysql-test/mysql-test-run.pl
@@ -5755,7 +5755,7 @@ sub usage ($) {
   {
     print STDERR "$message\n";
     print STDERR "For full list of options, use $0 --help\n";
-    exit;      
+    exit(1);
   }
 
   local $"= ','; # for @DEFAULT_SUITES below
diff --git a/mysql-test/suite/parts/inc/part_alter_values.inc b/mysql-test/suite/parts/inc/part_alter_values.inc
index ac69169a9ca..2f16476c78b 100644
--- a/mysql-test/suite/parts/inc/part_alter_values.inc
+++ b/mysql-test/suite/parts/inc/part_alter_values.inc
@@ -36,12 +36,43 @@ ALTER TABLE t1 REORGANIZE PARTITION p1 INTO
 );
 DROP TABLE t1;
 
-#
-# MDEV-15456 Server crashes upon adding or dropping a partition in ALTER under LOCK TABLE after ER_SAME_NAME_PARTITION
-#
+--echo #
+--echo # MDEV-15456 Server crashes upon adding or dropping a partition in ALTER under LOCK TABLE after ER_SAME_NAME_PARTITION
+--echo #
 --eval create table t1 (i int) engine=$engine partition by range(i) (partition p0 values less than (10))
 lock table t1 write;
 --error ER_SAME_NAME_PARTITION
 alter table t1 add partition (partition p0 values less than (20));
 alter table t1 add partition (partition p1 values less than (20)) /* comment */;
 drop table t1;
+
+--echo #
+--echo # MDEV-27065 Partitioning tables with custom data directories moves data back to default directory
+--echo #
+
+--mkdir $MYSQLTEST_VARDIR/tmp/mdev_27065
+
+--disable_query_log
+--eval CREATE TABLE t1 (id INT) ENGINE=$engine DATA DIRECTORY='$MYSQLTEST_VARDIR/tmp/mdev_27065'
+--enable_query_log
+ALTER TABLE t1 PARTITION BY RANGE(id)(
+  PARTITION p0 VALUES LESS THAN (1000),
+  PARTITION p1 VALUES LESS THAN MAXVALUE
+);
+DROP TABLE t1;
+
+# InnoDB doesn't support INDEX DIRECTORY.
+if (`SELECT IF('$engine' != 'InnoDB', 1, 0)`)
+{
+  --disable_query_log
+  --eval CREATE TABLE t2 (id INT) ENGINE=$engine INDEX DIRECTORY='$MYSQLTEST_VARDIR/tmp/mdev_27065'
+  --enable_query_log
+  ALTER TABLE t2 PARTITION BY RANGE(id)(
+    PARTITION p0 VALUES LESS THAN (1000),
+    PARTITION p1 VALUES LESS THAN MAXVALUE
+  );
+  DROP TABLE t2;
+}
+
+--remove_files_wildcard $MYSQLTEST_VARDIR/tmp/mdev_27065 *
+--rmdir $MYSQLTEST_VARDIR/tmp/mdev_27065
diff --git a/mysql-test/suite/parts/r/partition_alter_innodb.result b/mysql-test/suite/parts/r/partition_alter_innodb.result
index f3921a1db26..ae3caaa4981 100644
--- a/mysql-test/suite/parts/r/partition_alter_innodb.result
+++ b/mysql-test/suite/parts/r/partition_alter_innodb.result
@@ -42,9 +42,22 @@ PARTITION p3 VALUES IN (4,5,6)
 );
 ERROR HY000: Syntax error: LIST PARTITIONING requires definition of VALUES IN for each partition
 DROP TABLE t1;
+#
+# MDEV-15456 Server crashes upon adding or dropping a partition in ALTER under LOCK TABLE after ER_SAME_NAME_PARTITION
+#
 create table t1 (i int) engine=InnoDB partition by range(i) (partition p0 values less than (10));
 lock table t1 write;
 alter table t1 add partition (partition p0 values less than (20));
 ERROR HY000: Duplicate partition name p0
 alter table t1 add partition (partition p1 values less than (20)) /* comment */;
 drop table t1;
+#
+# MDEV-27065 Partitioning tables with custom data directories moves data back to default directory
+#
+ALTER TABLE t1 PARTITION BY RANGE(id)(
+PARTITION p0 VALUES LESS THAN (1000),
+PARTITION p1 VALUES LESS THAN MAXVALUE
+);
+Warnings:
+Warning	1618	<DATA DIRECTORY> table option of old schema is ignored
+DROP TABLE t1;
diff --git a/mysql-test/suite/parts/r/partition_alter_maria.result b/mysql-test/suite/parts/r/partition_alter_maria.result
index 77f511d9b3b..358ffbdfbe7 100644
--- a/mysql-test/suite/parts/r/partition_alter_maria.result
+++ b/mysql-test/suite/parts/r/partition_alter_maria.result
@@ -69,9 +69,29 @@ PARTITION p3 VALUES IN (4,5,6)
 );
 ERROR HY000: Syntax error: LIST PARTITIONING requires definition of VALUES IN for each partition
 DROP TABLE t1;
+#
+# MDEV-15456 Server crashes upon adding or dropping a partition in ALTER under LOCK TABLE after ER_SAME_NAME_PARTITION
+#
 create table t1 (i int) engine=Aria partition by range(i) (partition p0 values less than (10));
 lock table t1 write;
 alter table t1 add partition (partition p0 values less than (20));
 ERROR HY000: Duplicate partition name p0
 alter table t1 add partition (partition p1 values less than (20)) /* comment */;
 drop table t1;
+#
+# MDEV-27065 Partitioning tables with custom data directories moves data back to default directory
+#
+ALTER TABLE t1 PARTITION BY RANGE(id)(
+PARTITION p0 VALUES LESS THAN (1000),
+PARTITION p1 VALUES LESS THAN MAXVALUE
+);
+Warnings:
+Warning	1618	<DATA DIRECTORY> table option of old schema is ignored
+DROP TABLE t1;
+ALTER TABLE t2 PARTITION BY RANGE(id)(
+PARTITION p0 VALUES LESS THAN (1000),
+PARTITION p1 VALUES LESS THAN MAXVALUE
+);
+Warnings:
+Warning	1618	<INDEX DIRECTORY> table option of old schema is ignored
+DROP TABLE t2;
diff --git a/mysql-test/suite/parts/r/partition_alter_myisam.result b/mysql-test/suite/parts/r/partition_alter_myisam.result
index ce3e04d6c97..9d76881fdfa 100644
--- a/mysql-test/suite/parts/r/partition_alter_myisam.result
+++ b/mysql-test/suite/parts/r/partition_alter_myisam.result
@@ -42,12 +42,32 @@ PARTITION p3 VALUES IN (4,5,6)
 );
 ERROR HY000: Syntax error: LIST PARTITIONING requires definition of VALUES IN for each partition
 DROP TABLE t1;
+#
+# MDEV-15456 Server crashes upon adding or dropping a partition in ALTER under LOCK TABLE after ER_SAME_NAME_PARTITION
+#
 create table t1 (i int) engine=MyISAM partition by range(i) (partition p0 values less than (10));
 lock table t1 write;
 alter table t1 add partition (partition p0 values less than (20));
 ERROR HY000: Duplicate partition name p0
 alter table t1 add partition (partition p1 values less than (20)) /* comment */;
 drop table t1;
+#
+# MDEV-27065 Partitioning tables with custom data directories moves data back to default directory
+#
+ALTER TABLE t1 PARTITION BY RANGE(id)(
+PARTITION p0 VALUES LESS THAN (1000),
+PARTITION p1 VALUES LESS THAN MAXVALUE
+);
+Warnings:
+Warning	1618	<DATA DIRECTORY> table option of old schema is ignored
+DROP TABLE t1;
+ALTER TABLE t2 PARTITION BY RANGE(id)(
+PARTITION p0 VALUES LESS THAN (1000),
+PARTITION p1 VALUES LESS THAN MAXVALUE
+);
+Warnings:
+Warning	1618	<INDEX DIRECTORY> table option of old schema is ignored
+DROP TABLE t2;
 create table t1 ( c1 int, c2 int, c3 varchar(100)) delay_key_write=1
 partition by key(c1) (
 partition p01 data directory = 'MYSQL_TMP_DIR'
diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc
index dc4ec0407db..b4bb3e7e61e 100644
--- a/sql/ha_partition.cc
+++ b/sql/ha_partition.cc
@@ -696,6 +696,7 @@ int ha_partition::create(const char *name, TABLE *table_arg,
 			 HA_CREATE_INFO *create_info)
 {
   int error;
+  THD *thd= ha_thd();
   char name_buff[FN_REFLEN + 1], name_lc_buff[FN_REFLEN];
   char *name_buffer_ptr;
   const char *path;
@@ -714,8 +715,27 @@ int ha_partition::create(const char *name, TABLE *table_arg,
     my_error(ER_PARTITION_NO_TEMPORARY, MYF(0));
     DBUG_RETURN(TRUE);
   }
+  /*
+    The following block should be removed once the table-level data directory
+    specification is supported by the partitioning engine (MDEV-28108).
+  */
+  if (thd_sql_command(thd) == SQLCOM_ALTER_TABLE && create_info)
+  {
+    if (create_info->data_file_name)
+    {
+      push_warning_printf(
+          thd, Sql_condition::WARN_LEVEL_WARN, WARN_OPTION_IGNORED,
+          "<DATA DIRECTORY> table option of old schema is ignored");
+    }
+    if (create_info->index_file_name)
+    {
+      push_warning_printf(
+          thd, Sql_condition::WARN_LEVEL_WARN, WARN_OPTION_IGNORED,
+          "<INDEX DIRECTORY> table option of old schema is ignored");
+    }
+  }
 
-  if (get_from_handler_file(name, ha_thd()->mem_root, false))
+  if (get_from_handler_file(name, thd->mem_root, false))
     DBUG_RETURN(TRUE);
   DBUG_ASSERT(m_file_buffer);
   name_buffer_ptr= m_name_buffer_ptr;
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index a5ad00c9bb9..88db52db13d 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -2765,6 +2765,8 @@ bool Item_in_subselect::inject_in_to_exists_cond(JOIN *join_arg)
     }
 
     where_item= and_items(thd, join_arg->conds, where_item);
+
+    /* This is the fix_fields() call mentioned in the comment above */
     if (where_item->fix_fields_if_needed(thd, 0))
       DBUG_RETURN(true);
     // TIMOUR TODO: call optimize_cond() for the new where clause
@@ -2775,7 +2777,10 @@ bool Item_in_subselect::inject_in_to_exists_cond(JOIN *join_arg)
     /* Attach back the list of multiple equalities to the new top-level AND. */
     if (and_args && join_arg->cond_equal)
     {
-      /* The argument list of the top-level AND may change after fix fields. */
+      /*
+        The fix_fields() call above may have changed the argument list, so
+        fetch it again:
+      */
       and_args= ((Item_cond*) join_arg->conds)->argument_list();
       ((Item_cond_and *) (join_arg->conds))->m_cond_equal=
                                              *join_arg->cond_equal;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 8d922ca953f..89344a4457d 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -3464,13 +3464,11 @@ JOIN::create_postjoin_aggr_table(JOIN_TAB *tab, List<Item> *table_fields,
   tmp_table_param.using_outer_summary_function=
     tab->tmp_table_param->using_outer_summary_function;
   tab->join= this;
-  DBUG_ASSERT(tab > tab->join->join_tab || !top_join_tab_count || !tables_list);
+  DBUG_ASSERT(tab > tab->join->join_tab || !top_join_tab_count ||
+              !tables_list);
+  tab->table= table;
   if (tab > join_tab)
     (tab - 1)->next_select= sub_select_postjoin_aggr;
-  if (!(tab->aggr= new (thd->mem_root) AGGR_OP(tab)))
-    goto err;
-  tab->table= table;
-  table->reginfo.join_tab= tab;
 
   /* if group or order on first table, sort first */
   if ((group_list && simple_group) ||
@@ -3521,12 +3519,15 @@ JOIN::create_postjoin_aggr_table(JOIN_TAB *tab, List<Item> *table_fields,
       order= NULL;
     }
   }
-
+  if (!(tab->aggr= new (thd->mem_root) AGGR_OP(tab)))
+    goto err;
+  table->reginfo.join_tab= tab;
   DBUG_RETURN(false);
 
 err:
   if (table != NULL)
     free_tmp_table(thd, table);
+  tab->table= NULL;
   DBUG_RETURN(true);
 }
 
@@ -13187,6 +13188,7 @@ void JOIN::cleanup(bool full)
           if (curr_tab->aggr)
           {
             free_tmp_table(thd, curr_tab->table);
+            curr_tab->table= NULL;
             delete curr_tab->tmp_table_param;
             curr_tab->tmp_table_param= NULL;
             curr_tab->aggr= NULL;
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 3770488f3f4..9a0a9f3149e 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -2035,6 +2035,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
 
 %type <charset>
         opt_collate
+        collate
         charset_name
         charset_or_alias
         charset_name_or_default
@@ -7518,10 +7519,7 @@ charset_or_alias:
           }
         ;
 
-collate: COLLATE_SYM collation_name_or_default
-         {
-           Lex->charset= $2;
-         }
+collate: COLLATE_SYM collation_name_or_default { $$= $2; }
        ;
 
 opt_binary:
@@ -7536,11 +7534,17 @@ binary:
         | BINARY charset_or_alias { bincmp_collation($2, true); }
         | charset_or_alias collate
           {
-            if (!my_charset_same(Lex->charset, $1))
-              my_yyabort_error((ER_COLLATION_CHARSET_MISMATCH, MYF(0),
-                                Lex->charset->name, $1->csname));
+            if (!$2)
+              Lex->charset= $1; // CHARACTER SET cs COLLATE DEFAULT
+            else
+            {
+              if (!my_charset_same($2, $1))
+                my_yyabort_error((ER_COLLATION_CHARSET_MISMATCH, MYF(0),
+                                  $2->name, $1->csname));
+              Lex->charset= $2;
+            }
           }
-        | collate { }
+        | collate { Lex->charset= $1; }
         ;
 
 opt_bin_mod:
diff --git a/sql/structs.h b/sql/structs.h
index 46947b50502..c6f9fef2277 100644
--- a/sql/structs.h
+++ b/sql/structs.h
@@ -72,14 +72,14 @@ typedef struct st_key_part_info {	/* Info about a key part */
   uint  offset;                         /* Offset in record (from 0) */
   uint  null_offset;                    /* Offset to null_bit in record */
   /* Length of key part in bytes, excluding NULL flag and length bytes */
-  uint16 length;
+  uint length;
   /* 
     Number of bytes required to store the keypart value. This may be
     different from the "length" field as it also counts
      - possible NULL-flag byte (see HA_KEY_NULL_LENGTH)
      - possible HA_KEY_BLOB_LENGTH bytes needed to store actual value length.
   */
-  uint16 store_length;
+  uint store_length;
   uint16 key_type;
   uint16 fieldnr;                       /* Fieldnr begins counting from 1 */
   uint16 key_part_flag;                 /* 0 or HA_REVERSE_SORT */
diff --git a/storage/spider/mysql-test/spider/r/spider3_fixes.result b/storage/spider/mysql-test/spider/r/spider3_fixes.result
index d6aec25bfc1..7a80e102462 100644
--- a/storage/spider/mysql-test/spider/r/spider3_fixes.result
+++ b/storage/spider/mysql-test/spider/r/spider3_fixes.result
@@ -200,6 +200,14 @@ id
 5000
 10000
 connection master_1;
+#
+# MDEV-28225 Disallow user to create Spider temporary table
+#
+connection master_1;
+CREATE TEMPORARY TABLE t1 (
+a INT
+) ENGINE=Spider;
+ERROR HY000: Table storage engine 'SPIDER' does not support the create option 'TEMPORARY'
 
 deinit
 connection master_1;
diff --git a/storage/spider/mysql-test/spider/t/spider3_fixes.test b/storage/spider/mysql-test/spider/t/spider3_fixes.test
index 13fa6f5fa39..73126d6d348 100644
--- a/storage/spider/mysql-test/spider/t/spider3_fixes.test
+++ b/storage/spider/mysql-test/spider/t/spider3_fixes.test
@@ -264,6 +264,16 @@ if ($USE_CHILD_GROUP2)
 }
 
 
+--echo #
+--echo # MDEV-28225 Disallow user to create Spider temporary table
+--echo #
+--connection master_1
+--error ER_ILLEGAL_HA_CREATE_OPTION
+CREATE TEMPORARY TABLE t1 (
+  a INT
+) ENGINE=Spider;
+
+
 --echo
 --echo deinit
 --disable_warnings
diff --git a/storage/spider/spd_table.cc b/storage/spider/spd_table.cc
index ca3b7fa08ed..20347c4ebad 100644
--- a/storage/spider/spd_table.cc
+++ b/storage/spider/spd_table.cc
@@ -6878,7 +6878,7 @@ int spider_db_init(
   spider_hton_ptr = spider_hton;
 
   spider_hton->state = SHOW_OPTION_YES;
-  spider_hton->flags = HTON_NO_FLAGS;
+  spider_hton->flags = HTON_TEMPORARY_NOT_SUPPORTED;
 #ifdef HTON_CAN_READ_CONNECT_STRING_IN_PARTITION
   spider_hton->flags |= HTON_CAN_READ_CONNECT_STRING_IN_PARTITION;
 #endif