ORDER BY two columns from different tables - order is switched

[kristinriebe]

Query

SELECT mt.fofTreeId, mt.mass, ts.zred FROM MDR1.FOFMtree mt, MDR1.TreeSnapnums ts 
WHERE mt.fofTreeId between 100000000 and 100000010 
AND ts.treeSnapnum = mt.treeSnapnum 
ORDER BY ts.zred DESC, mt.mass

I want to sort the results first by zred, then by mass. But they are reordered in the query plan, so that everything is sorted first by mass and only then by zred. If I sorted by columns of the same table, the order would be preserved correctly, so in principle sorting by two columns is working.

Query plan Problem is in the CREATE TABLE line.

-- CALL paquExec('SELECT `mt`.`fofTreeId` AS `mt.fofTreeId`,`mt`.`mass` AS `mt.mass`,`mt`.`treeSnapnum` AS `mt.treeSnapnum` FROM MDR1.FOFMtree AS `mt` WHERE ( `mt`.`fofTreeId` between 100000000 and 100000010 ) ', 'aggregation_tmp_17358078')
-- CALL paquExec('SELECT `ts`.`zred` AS `ts.zred`,`mt`.`mt.fofTreeId` AS `mt.fofTreeId`,`mt`.`mt.mass` AS `mt.mass` FROM MDR1.TreeSnapnums AS `ts` JOIN ( SELECT `mt.fofTreeId`,`mt.mass`,`mt.treeSnapnum` FROM `aggregation_tmp_17358078` ORDER BY `mt.mass` ASC ) AS `mt` WHERE ( `ts`.`treeSnapnum` = `mt`.`mt.treeSnapnum` ) ', 'aggregation_tmp_98646210')
-- CALL paquDropTmp('aggregation_tmp_17358078')
-- USE spider_tmp_shard
-- SET @i=0
-- CREATE TABLE cosmosim_user_kristin.`2014-11-06-10-09-36-7604` ENGINE=MyISAM SELECT @i:=@i+1 AS `row_id`, `ts.zred`,`mt.fofTreeId`,`mt.mass` FROM `aggregation_tmp_98646210` ORDER BY `mt.mass` ASC,`ts.zred` DESC 
-- CALL paquDropTmp('aggregation_tmp_98646210')