Consider the following SQL query, executed against SQL Server (version xxx):
That query gives the following error when executed:
Eventually, I noticed found that replacing the outer select column list with * resolved the issue which led me to see that the outer query had two columns swapped compared to the inner union. Maintaining the select order in the outer query resolved the issue.