Mariadb troubleshooting using UNION's CREATE TABLE SELECT

Question

I'm working on a PHP script that contains six UNION SELECTs, and within the script this command runs just fine. This command also works fine when executed from a SQL client. However, when I try to put the SELECT statement inside the CREATE TABLE mytable SELECT, I get the following error message:

Incorrect column name ''

In five of the six UNION SELECT fragments, there is a column name named '', which is used as a placeholder when filling in data in subsequent parts of the script. But in one of the UNIONs, the same position is a varchar(32) column.

It seems that the existence of an "anonymous" column does not matter in the normal execution of the command. But in the CREATE TABLE version, it doesn't like this.

Why do SELECT and CREATE TABLE SELECT have different rules for union column names?

Answer 1

Tables cannot contain blank or missing column names, while result sets can. The CREATE TABLE SELECT statement obtains the column names of the table from the result set of the SELECT statement. Therefore, you need to ensure that each column of the result set has a name, either derived from a column in some table or specified via 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

applies to the CREATE TABLE statement, while

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

does not apply because it does not assign an alias column name to the first column.