Mysql sort - SORTING SOME BUT NOT OTHERS?

Question

Okay, here is my query:

SELECT NAME, 
       DATE_FORMAT(DATE_WRITTEN, "%c/%e/%y") AS written_date, 
       DATE_FORMAT(RETURN_DATE, "%c/%e/%y")  AS return_date 
FROM   `pfp`.`returns` AS `Re` 
       LEFT JOIN `pfp`.`insurance` AS `Insurance` 
              ON ( `insurance`.`id` = `Re`.`INSURANCE_ID` ) 
       LEFT JOIN `pfp`.`remain` AS `Remain` 
              ON ( `remain`.`id` = `Re`.`REMAIN_ID` ) 
       LEFT JOIN `pfp`.`formula` AS `Formula` 
              ON ( `formula`.`id` = `remain`.`FORMULA_ID` ) 
WHERE  `NOT_RETURNED` = 'F' 
       AND `RETURN_DATE` BETWEEN '2014-01-01' AND '2014-08-22' 
ORDER  BY `RETURN_DATE` DESC 
LIMIT  100 

The problem is that it sorts by the date 14-8-9 down to 14-8-7 then jumps back up to 14-8-22 and downward from there... why??

Answer 1

When you sort by return_date, you are sorting by the formatted alias. Instead, use the table alias to identify that you really want the column:

WHERE  `NOT_RETURNED` = 'F' 
       AND `RETURN_DATE` BETWEEN '2014-01-01' AND '2014-08-22' 
ORDER  BY re.RETURN_DATE DESC 
LIMIT  100 

I am guessing that it is in the re table. Use the appropriate alias.

EDIT:

The fact that the column aliases are searched first is documented:

MySQL resolves unqualified column or alias references in ORDER BY clauses by searching in the select_expr values, then in the columns of the tables in the FROM clause. For GROUP BY or HAVING clauses, it searches the FROM clause before searching in the select_expr values. (For GROUP BY and HAVING, this differs from the pre-MySQL 5.0 behavior that used the same rules as for ORDER BY.)

I can speculate on the reasons for this (which I think is consistent with the ANSI standard). SQL queries are logically processed in a particular order, something like from, then where, then select, then order by (leaving out other clauses). This logical processing determines how the query is compiled and what identifiers mean. The logical processing explains why column aliases are not allowed in the where clause -- from the perspective of the compiler, they are not yet identified.

When it comes to the order by, the identifier is determined from the inside out. The first definition is the version in the select, so it chooses that before going to the from.