The reason for this error is that starting with some commit in 10.3 or bb-10.2-ext, the field->type() == MYSQL_TYPE_NULL is being passed to InnoDB, instead of the former MYSQL_TYPE_STRING. get_innobase_type_from_mysql_type() is not ready to handle that.

If this bug is addressed by adjusting get_innobase_type_from_mysql_type(), then extreme care must be taken to ensure that INFORMATION_SCHEMA.INNODB_SYS_COLUMNS will be reporting the same prtype and mtype as before. Otherwise, InnoDB data compatibility with older versions may be broken.

I am receiving this same error in MariaDB-10.4.7. It works in MySQL 5.6 and is preventing me from migrating from MySQL to MariaDB.

DROP TABLE IF EXISTS temp_t1;

CREATE TEMPORARY TABLE `temp_t1` (  

  `id` INT(11) NOT NULL,

  PRIMARY KEY (`id`)

);

CREATE TEMPORARY TABLE temp_main 

  SELECT NULL AS field1 FROM temp_t1

UNION ALL

  SELECT NULL AS field1 FROM temp_t1

I'm getting the same error as Deryl Spielman. Any ideas?

The testcase by dukethrash can indeed be reduced as follows (i.e. similar or indentical to the original description):

CREATE TABLE t ENGINE=InnoDB AS SELECT NULL AS a FROM (SELECT 1) AS b UNION ALL SELECT NULL AS c FROM (SELECT 1) AS d;

Which leads to:

ERROR 1005 (HY000): Can't create table `test`.`t` (errno: 168 "Unknown (generic) error from engine")

On InnoDB only. MyISAM, Aria and Memory do not result in the same issue. Spider gives an interesting issue for which I will create a seperate bug.
No issue is recorded in the error log, which is odd when error 168 is observed.

Bug confirmed not present in:
MariaDB: 10.3.37 (dbg), 10.3.37 (opt), 10.4.27 (dbg), 10.4.27 (opt), 10.5.16 (opt), 10.5.18 (dbg), 10.5.18 (opt), 10.6.10 (dbg), 10.6.10 (opt), 10.7.6 (dbg), 10.7.6 (opt), 10.8.5 (dbg), 10.8.5 (opt), 10.9.3 (dbg), 10.9.3 (opt), 10.10.2 (dbg), 10.10.2 (opt), 10.11.0 (dbg), 10.11.0 (opt)

Bug (or feature/syntax) confirmed not present in:
MySQL: 5.5.62 (dbg), 5.5.62 (opt), 5.6.51 (dbg), 5.6.51 (opt), 5.7.38 (dbg), 5.7.38 (opt), 8.0.29 (dbg), 8.0.29 (opt)