MDEV-24511 null field is created with CREATE..SELECTbb-10.3-nikita-mdev-24511

The regression has been initially appeared by MDEV-12588 patch (441349aa).

The reason CREATE... SELECT NULL does not create null field is that
it calls handle_select(), where the field is created directly from Item,
by calling type_handler->type_handler_for_tmp_table(), instead of plain
type_handler.

In UNION case, we'll already have Item_field's containing UNION'ed tmp
table fields to the moment of select_create::prepare() call.

There, item's type_handler is used directly in
st_select_lex_unit::join_union_item_types(), instead of
type_handler_for_tmp_table() or type_handler_for_union().

Using those two involve too many changes in CREATE...SELECT...UNION
behavior, which may be reasonably undesired to be pushed in 10.3

However, patching Field_null::type_handler() directly seems to be accurate
enough.

3 files changed, 56 insertions, 2 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/field.h b/sql/field.h
index a3385736c0a..2af5346bea9 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -2526,7 +2526,7 @@ public:
     :Field_str(ptr_arg, len_arg, null, 1,
 	       unireg_check_arg, field_name_arg, collation)
     {}
-  const Type_handler *type_handler() const { return &type_handler_null; }
+  const Type_handler *type_handler() const { return &type_handler_string; }
   Information_schema_character_attributes
     information_schema_character_attributes() const
   {