You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
The text was updated successfully, but these errors were encountered:
Original query
Query plan
Problem
Error
Unknown column 'mt.mass' in 'order clause'
.This refers to the
ORDER BY
at the end of the secondCALL 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:
For comparison, this is the (working) query plan for the version without the LIMIT:
This works, because the
ORDER BY
in the second paquExec-call is missing.The text was updated successfully, but these errors were encountered: