Understanding behaviour of order by in a query

Question

I aim to select full name of employees that either have no boss or their boss lives on a street that contains letter 'o' or letter 'u'. Then, I want to list them in descending order by full name. The problem comes in the ordering, because by queries that I think are the same I get different answers.

When I introduce in MySQL Workbench the following command:

select concat(surnames,', ',name) as 'Full Name', street
from employee
where boss is null or (boss is not null and (street like'%u%' or steet like '%o%'))
order by concat(surnames,', ',name) desc;

By this command I get the answer I want, that is:

Full Name              Street
Suárez García, Juan Juan Valdés 25
Sarasola Goñi, Vanesa   Austria
Requena Santos, Pilar   Alicante 3
Puertas Elorza, Marta   Lope de Vega 3
Piedra Trujillo, Ramón  Madre Vedruna 21
Narváez Alonso, Alba    Vara de Rey 22
Gómez de la Sierra, Francisco   Loscertales 9
Chávarri Díez, Lorea    
Arrieta Alcorta, Kepa   Urbieta 33
Álvarez González, Ana   Graus 19

But when I change the ordering by another that looks the same to me:

select concat(surnames,', ',name) as 'Full Name', street
from employee
where boss is null or (boss is not null and (street like'%u%' or steet like '%o%'))
order by 'FullName' desc;

I get a wrong answer that looks like:

Full Name               Street
Suárez García, Juan Juan Valdés 25
Puertas Elorza, Marta   Lope de Vega 3
Chávarri Díez, Lorea    
Narváez Alonso, Alba    Vara de Rey 22
Gómez de la Sierra, Francisco   Loscertales 9
Piedra Trujillo, Ramón  Madre Vedruna 21
Sarasola Goñi, Vanesa   Austria
Requena Santos, Pilar   Alicante 3
Álvarez González, Ana   Graus 19
Arrieta Alcorta, Kepa   Urbieta 33

Can somebody tell me what's going on here?

Answer 1

When you define an alias, you can use either single-quotes, as if it's a string literal, or you can use back-ticks, as if it's an identifier. But when you refer to the alias later in the query, you must treat it as an identifier.

order by 'FullName' desc;

This orders by the constant string value 'FullName', not the alias. Ordering by a constant value or expression makes every row tied with every other row, and the resulting order is undefined.

If you want to refer to the identifier, use back-ticks:

order by `FullName` desc;

By using the identifier, the order is by the values of the named column from each respective row, and that will give you the order you want.

This page in the manual talks about this difference: https://dev.mysql.com/doc/refman/8.0/en/problems-with-alias.html