search

adrpar / paqu

A parallel query engine for MySQL + Spider Engine built on a fork of shard-query
GNU General Public License v2.0
10 stars 0 forks source link

Unknown column 'fp.fp.fofId' in 'field list' #23

Open kristinriebe opened 10 years ago

kristinriebe commented 10 years ago

Query: 

select fp.fofId, p.particleId,p.x,p.y,p.z from (select fofId, particleId from MDR1.FOFParticles where fofId between 85000000000 and 85000000010) as fp, MDR1.Particles85 as p where fp.particleId = p.particleId and sprng_dbl(1234) < 0.0001

Query plan:

-- CALL paquExec('SELECT `fofId` AS `fofId`,`particleId` AS `particleId` FROM MDR1.FOFParticles WHERE ( `fofId` between 85000000000 and 85000000010 ) ', 'aggregation_tmp_11864950')
-- CALL paquExec('SELECT `p`.`particleId` AS `p.particleId`,`p`.`x` AS `p.x`,`p`.`y` AS `p.y`,`p`.`z` AS `p.z`,`fp`.`fp.fofId` AS `fp.fofId` FROM MDR1.Particles85 AS `p` JOIN ( SELECT `fofId`,`particleId` FROM `aggregation_tmp_11864950` ) AS `fp` WHERE ( sprng_dbl( 1234 ) < 0.0001 ) and ( `fp`.`particleId` = `p`.`particleId` ) ', 'aggregation_tmp_68754678')-- CALL paquDropTmp('aggregation_tmp_11864950')
-- USE spider_tmp_shard
-- SET @i=0
-- CREATE TABLE cosmosim_user_kristin.`test-irow5` ENGINE=MyISAM SELECT @i:=@i+1 AS `row_id`, `p.particleId`,`p.x`,`p.y`,`p.z`,`fp.fofId` FROM `aggregation_tmp_68754678` 
-- CALL paquDropTmp('aggregation_tmp_68754678')

The query plan becomes correct, if I use fofId instead of fp.fofId at the first occurence:

select fofId, p.particleId,p.x,p.y,p.z from (select fofId, particleId from MDR1.FOFParticles where fofId between 85000000000 and 85000000010) as fp, MDR1.Particles85 as p where fp.particleId = p.particleId and sprng_dbl(1234) < 0.0001

correct plan:

-- CALL paquExec('SELECT `fofId` AS `fofId`,`particleId` AS `particleId` FROM MDR1.FOFParticles WHERE ( `fofId` between 85000000000 and 85000000010 ) ', 'aggregation_tmp_87009600')
-- CALL paquExec('SELECT `p`.`particleId` AS `p.particleId`,`p`.`x` AS `p.x`,`p`.`y` AS `p.y`,`p`.`z` AS `p.z`,`fp`.`fofId` AS `fofId` FROM MDR1.Particles85 AS `p` JOIN ( SELECT `fofId`,`particleId` FROM `aggregation_tmp_87009600` ) AS `fp` WHERE ( sprng_dbl( 1234 ) < 0.0001 ) and ( `fp`.`particleId` = `p`.`particleId` )', 'aggregation_tmp_76377167')
-- CALL paquDropTmp('aggregation_tmp_87009600')
-- USE spider_tmp_shard-- SET @i=0
-- CREATE TABLE cosmosim_user_kristin.`test-irow8` ENGINE=MyISAM SELECT @i:=@i+1 AS `row_id`, `p.particleId`,`p.x`,`p.y`,`p.z`,`fofId` FROM `aggregation_tmp_76377167`
-- CALL paquDropTmp('aggregation_tmp_76377167')