Another problem with aliases/table joins

[kristinriebe]

SELECT p.fofTreeId, p.treeSnapnum, p.x,p.y,p.z, p.mass, p.np FROM MDR1.FOFMtree AS p, (SELECT fofTreeId, mainLeafId FROM MDR1.FOFMtree WHERE fofId=85000000000) AS m, MDR1.FOF AS f WHERE p.fofTreeId BETWEEN m.fofTreeId AND m.mainLeafId AND f.fofId = p.fofId ORDER BY p.treeSnapnum

Error message is: Incorrect table name 'm'

And here's the query plan: CALL paquExec('SELECT fofTreeId AS fofTreeId,mainLeafId AS mainLeafId FROM MDR1.FOFMtree AS MDR1.FOFMtree WHERE fofId = 85000000000 ', 'aggregation_tmp_92663371')-- CREATE DATABASE IF NOT EXISTS spider_tmp_shard-- USE spider_tmp_shard-- CREATE TABLE spider_tmp_shard.aggregation_tmp_93742005 ENGINE=MyISAM SELECT m.fofTreeId AS m.fofTreeId,m.mainLeafId AS m.mainLeafId FROM ( SELECT fofTreeId,mainLeafId FROM aggregation_tmp_92663371 ) AS m ORDER BY NULL LIMIT 0-- USE spider_tmp_shard-- INSERT INTO spider_tmp_shard.aggregation_tmp_93742005 SELECT m.fofTreeId AS m.fofTreeId,m.mainLeafId AS m.mainLeafId FROM ( SELECT fofTreeId,mainLeafId FROM aggregation_tmp_92663371 ) AS m ORDER BY NULL -- CALL paquLinkTmp('aggregation_tmp_93742005')-- CALL paquExec('SELECT p.fofTreeId AS p.fofTreeId,p.treeSnapnum AS p.treeSnapnum,p.x AS p.x,p.y AS p.y,p.z AS p.z,p.mass AS p.mass,p.np AS p.np,p.fofId AS p.fofId FROM MDR1.FOFMtree AS p JOIN ( SELECT m.fofTreeId,m.mainLeafId FROM aggregation_tmp_93742005 ) AS m ORDER BY NULL ', 'aggregation_tmp_58157251')-- CALL paquExec('SELECT p.p.fofTreeId AS p.fofTreeId,p.p.treeSnapnum AS p.treeSnapnum,p.p.x AS p.x,p.p.y AS p.y,p.p.z AS p.z,p.p.mass AS p.mass,p.p.np AS p.np FROM MDR1.FOF AS f JOIN ( SELECT p.fofTreeId,p.treeSnapnum,p.x,p.y,p.z,p.mass,p.np,p.fofId FROM aggregation_tmp_58157251 ORDER BY p.treeSnapnum ASC ) AS p WHERE ( p.p.fofTreeId BETWEEN m.m.fofTreeId AND m.m.mainLeafId AND f.fofId = p.p.fofId ) ', 'aggregation_tmp_72144206')-- CALL paquDropTmp('aggregation_tmp_92663371')-- CALL paquDropTmp('aggregation_tmp_93742005')-- CALL paquDropTmp('aggregation_tmp_58157251')-- USE spider_tmp_shard-- SET @i=0-- CREATE TABLE multidark_user_kristin.test-join-fp-3-1 ENGINE=MyISAM SELECT @i:=@i+1 AS row_id, distinct_res_table.* FROM ( SELECT DISTINCT p.fofTreeId,p.treeSnapnum,p.x,p.y,p.z,p.mass,p.np FROM aggregation_tmp_72144206 ORDER BY p.treeSnapnum ASC ) as distinct_res_table-- CALL paquDropTmp('aggregation_tmp_72144206')

[adrpar]

related to issue 17 - however BETWEEN issue remains

[adrpar]

fixed in c7bfe73f8d0f6867c7cf9568f4b98adbf5881987

[adrpar]

reopening - patch not working

[adrpar]

fixed in 20d73bd66d7171513f6eab44108cd94c1dbf5ac4