Part of WHERE-clause got lost
Opened this issue · 0 comments
kristinriebe commented
Problem
For a table join with a subquery like below, PaQu ignored my second condition ABS(r.zred-0.5)<0.05
.
I can make the query work by adding this condition directly in the subquery, see last point.
Query
SELECT r.zred, f.snapnum, f.x,f.y,f.z,f.mass, f.size
FROM MDR1.FOF f,
(SELECT DISTINCT r.zred, r.snapnum FROM MDR1.Redshifts r) AS r
WHERE r.snapnum=f.snapnum AND ABS(r.zred-0.5)<0.05
ORDER BY f.mass DESC LIMIT 10
Query Plan
-- CALL paquExec('SELECT DISTINCT `r`.`zred` AS `r.zred`,`r`.`snapnum` AS `r.snapnum` FROM MDR1.Redshifts AS `r` ', 'aggregation_tmp_51495722')
-- CALL paquExec('SELECT `f`.`snapnum` AS `f.snapnum`,`f`.`x` AS `f.x`,`f`.`y` AS `f.y`,`f`.`z` AS `f.z`,`f`.`mass` AS `f.mass`,`f`.`size` AS `f.size`,`r`.`r.zred` AS `r.zred`
FROM MDR1.FOF AS `f` JOIN ( SELECT DISTINCT `r.zred`,`r.snapnum` FROM `aggregation_tmp_51495722` ) AS `r`
WHERE ( `r`.`r.snapnum` = `f`.`snapnum` ) ORDER BY `f`.`mass` DESC LIMIT 0,10', 'aggregation_tmp_83801433')
-- CALL paquDropTmp('aggregation_tmp_51495722')-- USE spider_tmp_shard
-- SET @i=0
-- CREATE TABLE cosmosim_user_kristin.`2014-12-10-10-15-31-5457` ENGINE=MyISAM SELECT @i:=@i+1 AS `row_id`, `f.snapnum`,`f.x`,`f.y`,`f.z`,`f.mass`,`f.size`,`r.zred` FROM `aggregation_tmp_83801433` ORDER BY `f.mass` DESC LIMIT 0,10
-- CALL paquDropTmp('aggregation_tmp_83801433')
Work around
Include the 2. condition in the subquery as well:
SELECT r.zred, f.snapnum, f.x,f.y,f.z,f.mass, f.size
FROM MDR1.FOF f,
(SELECT DISTINCT r.zred, r.snapnum FROM MDR1.Redshifts r WHERE ABS(r.zred-0.5)<0.05) AS r
WHERE r.snapnum=f.snapnum
ORDER BY f.mass DESC LIMIT 10