A SELECT from a subquery containing a UNION with inconsistent result column names fails with an ill-fitting error message, but only when used with a WHERE clause.
This is a heavily striped down version of a query that fails:
yields: ERROR 1054 (42S22): Unknown column 'x' in 'order clause'
which is strange, because there is no order clause.
This slightly different statement, however, works fine:
(Note: second branch of UNION has no aggregate function)
and yields, as expected:
Omitting the WHERE clause from the first query allows it to succeed, too.
This seems to be a regression. My original query (much longer) used to work with an older 10.x version, but I cannot reconstruct any more which version that was.
On an old MySQL server 5.7.9, the problem does NOT come up. SQLite also has NO problems with the query, so I assume the query is legal as such, even given the inconsistent naming of columns.
Of course, a workaround is easy: use consistent aliases: