brick_primary constraint does not work

Question

The following sql works fine:

select top 20 a.*,dered_mag_g, dered_mag_r, dered_mag_z,
case when flux_g > 0 then 1.086/(flux_g * sqrt(flux_ivar_g)) else 0 end as e_g,
case when flux_r > 0 then 1.086/(flux_r * sqrt(flux_ivar_r)) else 0 end as e_r,
case when flux_z > 0 then 1.086/(flux_z * sqrt(flux_ivar_z)) else 0 end as e_z,
brick_primary
from mydb://test as a, ls_dr7.tractor as t
where q3c_join(a.ra, a.dec,t.ra, t.dec,1./3600.)

If I add "and brick_primary = 1", the query hangs (timeout error).

Why???....

Answer 1

Thank you for contacting us with your question.

Try modifying the query using a with clause, shown below.   May I ask what you are using a reference for q3c_joins?  We will update that to add this nuance for next time. 

with r as (
select top 20 a.*,dered_mag_g, dered_mag_r, dered_mag_z,
case when flux_g > 0 then 1.086/(flux_g * sqrt(flux_ivar_g)) else 0 end as e_g,
case when flux_r > 0 then 1.086/(flux_r * sqrt(flux_ivar_r)) else 0 end as e_r,
case when flux_z > 0 then 1.086/(flux_z * sqrt(flux_ivar_z)) else 0 end as e_z,
brick_primary
from mydb://test as a, ls_dr7.tractor as t
where q3c_join(a.ra, a.dec,t.ra, t.dec,1./3600.)
)
select * from r
where r.brick_primary=1