MySQL Bugs: #102832: Column type returned by union is incorrect

Bug #102832	Column type returned by union is incorrect
Submitted:	5 Mar 2021 16:59	Modified:	9 Mar 2021 8:18
Reporter:	Владислав Сокол	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.7/8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The documentation claims that "The column names for a UNION result set are taken from the column names of the first SELECT statement." (https://dev.mysql.com/doc/refman/8.0/en/union.html#union-result-set).

But this is not true when first SELECT is empty (does not contain rows). 

How to repeat:
Variant 1 (scalar datasource)
--
SELECT CAST('2021-01-01' AS DATETIME) `must be datetime` WHERE 1=0
UNION ALL
SELECT 20210102;

Variant 2 (CTE datasource)
--
WITH 
cte AS (SELECT CAST('2021-01-01' AS DATETIME) `must be datetime` WHERE 1=0)
SELECT * FROM cte
UNION ALL
SELECT 20210102;

Variant 3 (static table datasource)
--
CREATE TABLE test (ts DATETIME) SELECT '2021-01-01' ts;
SELECT ts `must be datetime` FROM test WHERE 1=0
UNION ALL
SELECT 20210102;

------

All 3 variants returns 1-column 1-row output where the value is 20210102, i.e. it has some numeric type whereas the first SELECT statement must set the datatype to DATETIME.

Thank you for the bug report.

The claim on column names is correct. However the column types of a UNION is derived from the aggregation of the specific column from all branches of the UNION.

Quoting from the manual:

"Selected columns listed in corresponding positions of each SELECT statement should have the same data type. For example, the first column selected by the first statement should have the same type as the first column selected by the other statements. If the data types of corresponding SELECT columns do not match, the types and lengths of the columns in the UNION result take into account the values retrieved by all the SELECT statements. For example, consider the following, where the column length is not constrained to the length of the value from the first SELECT".