cubicweb #1579228 missing join on left outer join when using BEING [resolved]

The following RQL

Any G1, CP, CP1148 WHERE G1 identity G1148 WITH G1,CP BEING ( Any G, COUNT(P) GROUPBY G WHERE G in_department D, D eid 1122, P member_of_group G ), CP1148, G1148  BEING ( Any COUNT(P1148),G GROUPBY G WHERE G   in_department D, D eid 1122, K1148 applied_to P1148, K1148 eid 1148, P1148? member_of_group G  )

Gives the following SQL debug

 querier input Any G1, CP, CP1148 WHERE G1 identity G1148 WITH G1,CP BEING ( Any G, COUNT(P) GROUPBY G WHERE G in_department D, D eid 1122, P member_of_group G ), CP1148, G1148  BEING ( Any COUNT(P1148),G    GROUPBY G WHERE G in_department D, D eid 1122, K1148 applied_to P1148, K1148 eid 1148, P1148? member_of_group G  ) None
exec SELECT _T0.C0, _T0.C1, _T1.C0
FROM (SELECT COUNT(rel_applied_to1.eid_to) AS C0, _G.cw_eid AS C1
FROM applied_to_relation AS rel_applied_to1, cw_CompanyGroup AS _G LEFT OUTER JOIN member_of_group_relation AS rel_member_of_group2 ON (rel_member_of_group2.eid_to=_G.cw_eid), in_department_relation AS   rel_in_department0
WHERE rel_in_department0.eid_from=_G.cw_eid AND rel_in_department0.eid_to=%(80687624)s AND rel_applied_to1.eid_from=%(80687448)s
GROUP BY _G.cw_eid) AS _T1, (SELECT rel_member_of_group1.eid_to AS C0, COUNT(rel_member_of_group1.eid_from) AS C1
FROM in_department_relation AS rel_in_department0, member_of_group_relation AS rel_member_of_group1
WHERE rel_in_department0.eid_from=rel_member_of_group1.eid_to AND rel_in_department0.eid_to=%(80687360)s
GROUP BY rel_member_of_group1.eid_to) AS _T0
WHERE _T0.C0=_T1.C1 {'80687360': 1122, '80687624': 1122, '80687448': 1148} <connection object at 0x4012750; dsn: 'dbname=trombino  user=arthur password=xxxxxx', closed: 0>

Apparently there is missing a join on rel_member_of_group2.eid_from. The resulting table gives too many counts on CP1148 (instead of having some set to zero).

prioritynormal
typebug
done in3.12.1
load0.300
load left0.000
closed by#7eaef037ea9d [sql generation] fix missing join with outer joins w/ rhs invariant variable...