Query sql with ALIAS equal to column name with unexpected return

Question

That consultation

SELECT DATE_FORMAT(data,'%d/%m/%Y') AS ALIASdata
FROM departamentos
ORDER BY data DESC

me returns the dates in the order I want

18/05/2020
17/04/2019
17/05/2018
17/03/2018
17/03/2015
17/03/2014

If I run this query, with ALIAS equal to column name

SELECT DATE_FORMAT(data,'%d/%m/%Y') AS data
FROM departamentos
ORDER BY data DESC

return me a unexpected order

18/05/2020
17/05/2018
17/04/2019
17/03/2018
17/03/2015
17/03/2014

Why does that happen?

Answer 1

The issue is not that the alias name is equal to the column name.

The reason is that DATE_FORMAT returns a string, and string comparisons are different from date comparisons.

A simple way to visualize the difference in the comparison is to imagine that in the comparison string the date becomes a single number. Thus, "17052018" is greater than "17042019".