After investigation, I have an incomplete answer: this is not a bug, but I haven't found the true reason for the latter.
I shall update this thread if I have an update to add.
In the meantime, feel free to create a suggestion, as we don't have any regarding this inquiry, yet: Ideas
Thanks for your understanding.
Edit: Some additional details here: In 8.2 - Tableau renaming all my fields?
Thanks for the response, hopefully you guys can find root cause on this and improve in the future.
Dave - I am fairly certain that the use of custom sql signals to Tableau to retain field names as specified in the sql. Things like Unions or custom sql defined joins might return unwanted results. I would rename the fields as desired in the custom sql in this instance.
I hope this helps.