MDEV-24511 null field is created with CREATE..SELECT

When creating fields for UNION results, Field_null is not allowed.
Should create binary(0) instead.

5 files changed, 68 insertions, 5 deletions

diff --git a/mysql-test/main/union.result b/mysql-test/main/union.result
index a892f6c9e40..011d65578cb 100644
--- a/mysql-test/main/union.result
+++ b/mysql-test/main/union.result
@@ -1609,7 +1609,7 @@ NULL	binary(0)	YES		NULL
 CREATE TABLE t5 SELECT NULL UNION SELECT NULL;
 DESC t5;
 Field	Type	Null	Key	Default	Extra
-NULL	null	YES		NULL	
+NULL	binary(0)	YES		NULL	
 CREATE TABLE t6 
 SELECT * FROM (SELECT * FROM (SELECT NULL)a) b UNION SELECT a FROM t1;
 DESC t6;
@@ -2635,5 +2635,34 @@ CAST(1 AS UNSIGNED)
 1
 1
 #
+# MDEV-24511 null field is created with CREATE..SELECT
+#
+set @save_default_storage_engine=@@default_storage_engine;
+SET @@default_storage_engine=MEMORY;
+CREATE TABLE t1 SELECT NULL UNION SELECT NULL;
+ALTER TABLE t1 ADD INDEX (`PRIMARY`);
+ERROR 42000: Key column 'PRIMARY' doesn't exist in table
+CREATE TABLE t2 SELECT NULL;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `NULL` binary(0) DEFAULT NULL
+) ENGINE=MEMORY DEFAULT CHARSET=latin1
+CREATE TABLE t3 SELECT NULL UNION SELECT NULL;
+SHOW CREATE TABLE t3;
+Table	Create Table
+t3	CREATE TABLE `t3` (
+  `NULL` binary(0) DEFAULT NULL
+) ENGINE=MEMORY DEFAULT CHARSET=latin1
+CREATE OR REPLACE TABLE t4 SELECT NULL UNION SELECT NULL;
+SHOW CREATE TABLE t4;
+Table	Create Table
+t4	CREATE TABLE `t4` (
+  `NULL` binary(0) DEFAULT NULL
+) ENGINE=MEMORY DEFAULT CHARSET=latin1
+ALTER TABLE t4 ADD INDEX (`NULL`);
+DROP TABLE t1, t2, t3, t4;
+set @@default_storage_engine=@save_default_storage_engine;
+#
 # End of 10.3 tests
 #
diff --git a/mysql-test/main/union.test b/mysql-test/main/union.test
index ab629ce076d..484393611ae 100644
--- a/mysql-test/main/union.test
+++ b/mysql-test/main/union.test
@@ -1874,5 +1874,30 @@ SELECT CAST(1 AS UNSIGNED) UNION ALL SELECT CAST(1 AS SIGNED);
 --enable_ps_protocol
 
 --echo #
+--echo # MDEV-24511 null field is created with CREATE..SELECT
+--echo #
+
+set @save_default_storage_engine=@@default_storage_engine;
+SET @@default_storage_engine=MEMORY;
+
+CREATE TABLE t1 SELECT NULL UNION SELECT NULL;
+--error ER_KEY_COLUMN_DOES_NOT_EXITS
+ALTER TABLE t1 ADD INDEX (`PRIMARY`);
+
+CREATE TABLE t2 SELECT NULL;
+SHOW CREATE TABLE t2;
+
+CREATE TABLE t3 SELECT NULL UNION SELECT NULL;
+SHOW CREATE TABLE t3;
+
+CREATE OR REPLACE TABLE t4 SELECT NULL UNION SELECT NULL;
+SHOW CREATE TABLE t4;
+ALTER TABLE t4 ADD INDEX (`NULL`);
+
+DROP TABLE t1, t2, t3, t4;
+
+set @@default_storage_engine=@save_default_storage_engine;
+
+--echo #
 --echo # End of 10.3 tests
 --echo #
diff --git a/sql/sql_type.cc b/sql/sql_type.cc
index 720fea7ebb4..128a4e68533 100644
--- a/sql/sql_type.cc
+++ b/sql/sql_type.cc
@@ -5933,7 +5933,7 @@ void Type_handler_geometry::Item_param_set_param_func(Item_param *param,
 
 /***************************************************************************/
 
-bool Type_handler_string_result::union_element_finalize(const Item * item) const
+bool Type_handler_string_result::union_element_finalize(Item_type_holder *item) const
 {
   if (item->collation.derivation == DERIVATION_NONE)
   {
@@ -5943,6 +5943,12 @@ bool Type_handler_string_result::union_element_finalize(const Item * item) const
   return false;
 }
 
+bool Type_handler_null::union_element_finalize(Item_type_holder *item) const
+{
+  item->set_handler(&type_handler_string);
+  return false;
+}
+
 /***************************************************************************/
 
 bool Type_handler::Vers_history_point_resolve_unit(THD *thd,
diff --git a/sql/sql_type.h b/sql/sql_type.h
index 383ce800f7b..08b599af7ab 100644
--- a/sql/sql_type.h
+++ b/sql/sql_type.h
@@ -62,6 +62,7 @@ class Item_func_minus;
 class Item_func_mul;
 class Item_func_div;
 class Item_func_mod;
+class Item_type_holder;
 class cmp_item;
 class in_vector;
 class Type_handler_hybrid_field_type;
@@ -1191,7 +1192,7 @@ public:
     Performs the final data type validation for a UNION element,
     after the regular "aggregation for result" was done.
   */
-  virtual bool union_element_finalize(const Item * item) const
+  virtual bool union_element_finalize(Item_type_holder *item) const
   {
     return false;
   }
@@ -2244,7 +2245,7 @@ public:
   void sortlength(THD *thd,
                   const Type_std_attributes *item,
                   SORT_FIELD_ATTR *attr) const;
-  bool union_element_finalize(const Item * item) const;
+  bool union_element_finalize(Item_type_holder *item) const;
   bool Column_definition_prepare_stage1(THD *thd,
                                         MEM_ROOT *mem_root,
                                         Column_definition *c,
@@ -3118,6 +3119,7 @@ public:
   bool Item_send(Item *item, Protocol *protocol, st_value *buf) const;
   Field *make_conversion_table_field(TABLE *, uint metadata,
                                      const Field *target) const;
+  bool union_element_finalize(Item_type_holder *item) const;
   bool Column_definition_fix_attributes(Column_definition *c) const;
   bool Column_definition_prepare_stage1(THD *thd,
                                         MEM_ROOT *mem_root,
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index c89e59a06f8..48d8c16db68 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -1154,7 +1154,8 @@ cont:
         Test if the aggregated data type is OK for a UNION element.
         E.g. in case of string data, DERIVATION_NONE is not allowed.
       */
-      if (type->type_handler()->union_element_finalize(type))
+      if (type->type() == Item::TYPE_HOLDER && type->type_handler()->
+            union_element_finalize(static_cast<Item_type_holder*>(type)))
         goto err;
     }