aggr: a restricted group drops the rows with no match, though aggr keeps all rows by default #1650

What happens

A.aggr(B & cond, ...) keeps all rows of A by default (exclude_nonmatching=False, a LEFT JOIN). But when the group B is restricted, the restriction goes into the outer WHERE clause, after the LEFT JOIN. Rows of A with no matching row in B & cond then have NULL in B's columns, fail the condition and drop out, so the result behaves like exclude_nonmatching=True.

Reproduction

On master (3c912c4, MySQL 8.0):

import datajoint as dj

schema = dj.Schema("aggr_repro")

@schema
class Parent(dj.Manual):
    definition = """
    parent_id : int32
    """

@schema
class Child(dj.Manual):
    definition = """
    -> Parent
    child_id : int32
    ---
    color : varchar(8)
    """

Parent.insert([(1,), (2,), (3,)])
Child.insert([(1, 1, "red"), (1, 2, "blue"), (2, 1, "blue")])

Parent.aggr(Child, n="count(child_id)").to_dicts()
# [{'parent_id': 1, 'n': 2}, {'parent_id': 2, 'n': 1}, {'parent_id': 3, 'n': 0}]   -- all parents, as documented

Parent.aggr(Child & 'color="red"', n="count(child_id)").to_dicts()
# [{'parent_id': 1, 'n': 1}]   -- parents 2 and 3 are missing

The SQL for the second query:

SELECT `parent_id`, count(child_id) as `n`
FROM `aggr_repro`.`parent` LEFT JOIN `aggr_repro`.`child` USING (`parent_id`)
WHERE ( (color="red"))
GROUP BY `parent_id`

Projecting the restricted group first ((Child & 'color="red"').proj()) produces the same SQL and the same result.

Expected

[{'parent_id': 1, 'n': 1}, {'parent_id': 2, 'n': 0}, {'parent_id': 3, 'n': 0}]

The group's restriction belongs to the group. It should go in the join condition (LEFT JOIN child ON … AND color="red") or in a subquery for the group (LEFT JOIN (SELECT … FROM child WHERE color="red") AS …), not in the outer WHERE. Today a count of zero can't be expressed for a restricted group without dropping the row, and the documented default ("keeps all subjects, even those with 0 sessions") holds only when the group is unrestricted.