mariadb issue with create table select where the select is a union

I have a six-way union select that I am working with that is part of a PHP script. The command works fine in the script. In executing from the SQL client, the command also works fine. However, when I try to put the SELECT inside a CREATE TABLE mytable SELECT, I get an error message as follows:

Incorrect column name ''

In five of the union segments, there is a column name '', which is a placeholder for data filled in later in the script. But in one of the unions there is varchar(32) column in that same place.

It seems that the command as executed normally does not care that there is an "anonymous" column. But in the CREATE TABLE version is does not like that.

Why are there different rules for the SELECT and CREATE TABLE SELECT for union column names?

Solution

Tables can't contain columns with blank or missing names. Result sets can. And CREATE TABLE SELECT gets the table's column names from the column names in the SELECT statement's result set. Therefore you need to make sure each column of your result set has a name, derived either from a column in some table, or from an AS alias. For example,

SELECT 'one' AS tablename, cola, colb, colc FROM table_one
 UNION ALL
SELECT 'two',              cola, colb, colc FROM table_two
 UNION ALL
SELECT 'three',            cola, colb, colc FROM table_three

is suitable for use in a CREATE TABLE statement, whereas

SELECT 'one',   cola, colb, colc FROM table_one
 UNION ALL
SELECT 'two',   cola, colb, colc FROM table_two
 UNION ALL
SELECT 'three', cola, colb, colc FROM table_three

is not, because you it does not assign an alias column name to the first column.