Error: "Unknown column 'mt.mass' in 'order clause'" after table join, when using LIMIT

[kristinriebe]

Original query

SELECT f.fofId, f.mass, mt.fofTreeId, mt.mass FROM MDR1.FOFMtree mt, MDR1.FOF f 
WHERE mt.fofTreeId BETWEEN 100000000 and 100000010 
AND mt.fofId = f.fofId 
ORDER BY mt.mass DESC 
LIMIT 100

Query plan

-- CALL paquExec('SELECT `mt`.`fofTreeId` AS `mt.fofTreeId`,`mt`.`mass` AS `mt.mass`,`mt`.`fofId` AS `mt.fofId` FROM MDR1.FOFMtree AS `mt` WHERE ( `mt`.`fofTreeId` BETWEEN 100000000 and 100000010 ) ', 'aggregation_tmp_21788886')
-- CALL paquExec('SELECT `f`.`fofId` AS `f.fofId`,`f`.`mass` AS `f.mass`,`mt`.`mt.fofTreeId` AS `mt.fofTreeId`,`mt`.`mt.mass` AS `mt.mass` FROM MDR1.FOF AS `f` JOIN ( SELECT `mt.fofTreeId`,`mt.mass`,`mt.fofId` FROM `aggregation_tmp_21788886` ORDER BY `mt.mass` DESC ) AS `mt` WHERE ( `mt`.`mt.fofId` = `f`.`fofId` ) ORDER BY `mt`.`mass` DESC LIMIT 0,100', 'aggregation_tmp_15908619')
-- CALL paquDropTmp('aggregation_tmp_21788886')
-- USE spider_tmp_shard
-- SET @i=0
-- CREATE TABLE cosmosim_user_kristin.`2014-11-06-08-43-07-1685` ENGINE=MyISAM SELECT @i:=@i+1 AS `row_id`, `f.fofId`,`f.mass`,`mt.fofTreeId`,`mt.mass` FROM `aggregation_tmp_15908619` ORDER BY `mt.mass` DESC LIMIT 0,100
-- CALL paquDropTmp('aggregation_tmp_15908619')

Problem Error Unknown column 'mt.mass' in 'order clause'. This refers to the ORDER BY at the end of the second CALL paquExec, directly before the LIMIT.

Current work-around The problem does not occur, if the LIMIT-phrase is missing or if I alias the column name, i.e. the following two queries execute correctly:

SELECT f.fofId, f.mass, mt.fofTreeId, mt.mass FROM MDR1.FOFMtree mt, MDR1.FOF f 
WHERE mt.fofTreeId BETWEEN 100000000 and 100000010 
AND mt.fofId = f.fofId 
ORDER BY mt.mass DESC

SELECT f.fofId, f.mass, mt.fofTreeId, mt.mass as m_mass FROM MDR1.FOFMtree mt, MDR1.FOF f 
WHERE mt.fofTreeId BETWEEN 100000000 and 100000010 
AND mt.fofId = f.fofId 
ORDER BY m_mass DESC
 LIMIT 100

For comparison, this is the (working) query plan for the version without the LIMIT:

-- CALL paquExec('SELECT `mt`.`fofTreeId` AS `mt.fofTreeId`,`mt`.`mass` AS `mt.mass`,`mt`.`fofId` AS `mt.fofId` FROM MDR1.FOFMtree AS `mt` WHERE ( `mt`.`fofTreeId` BETWEEN 100000000 and 100000010 ) ', 'aggregation_tmp_26721340')
-- CALL paquExec('SELECT `f`.`fofId` AS `f.fofId`,`f`.`mass` AS `f.mass`,`mt`.`mt.fofTreeId` AS `mt.fofTreeId`,`mt`.`mt.mass` AS `mt.mass` FROM MDR1.FOF AS `f` JOIN ( SELECT `mt.fofTreeId`,`mt.mass`,`mt.fofId` FROM `aggregation_tmp_26721340` ORDER BY `mt.mass` DESC ) AS `mt` WHERE ( `mt`.`mt.fofId` = `f`.`fofId` ) ', 'aggregation_tmp_86502970')
-- CALL paquDropTmp('aggregation_tmp_26721340')
-- USE spider_tmp_shard
-- SET @i=0-- CREATE TABLE cosmosim_user_kristin.`2014-11-06-08-42-55-4524` ENGINE=MyISAM SELECT @i:=@i+1 AS `row_id`, `f.fofId`,`f.mass`,`mt.fofTreeId`,`mt.mass` FROM `aggregation_tmp_86502970` ORDER BY `mt.mass` DESC 
-- CALL paquDropTmp('aggregation_tmp_86502970')

This works, because the ORDER BY in the second paquExec-call is missing.