Aggregate functions are not allowed in JOIN conditions. ArrayAgg function in CTE join ON clause

[a4tarasiuk]

Django 4.2
django-cte 1.3.3

postgres 12

There is an SQL query that is needed to be performed via CTE in Django:

WITH RECURSIVE "cte" AS (SELECT "a"."id",
                                ARRAY_AGG(DISTINCT "b"."id") AS "b_ids"
                         FROM "a"
                                  INNER JOIN "b" ON ("b"."id" = "a"."id")
                         GROUP BY "a".id)

SELECT "a.id",
       ARRAY_AGG(DISTINCT "b"."id") AS "b_ids",
       ARRAY_AGG(DISTINCT "cte"."id") AS "cte_ids"
FROM a
         INNER JOIN "b" ON ("b"."id" = "a"."id")
         LEFT OUTER JOIN cte 
                 ON "a".b_ids && cte.b_ids
GROUP BY "a".id

django-cte results to this query:

WITH RECURSIVE "cte" AS (SELECT "a"."id",
                                ARRAY_AGG(DISTINCT "b"."id") AS "b_ids"
                         FROM "a"
                                  INNER JOIN "b" ON ("b"."id" = "a"."id")
                         GROUP BY "a".id)

SELECT "a.id",
       ARRAY_AGG(DISTINCT "b"."id") AS "b_ids",
       ARRAY_AGG(DISTINCT "cte"."id") AS "cte_ids"
FROM a
         INNER JOIN "b" ON ("b"."id" = "a"."id")
         LEFT OUTER JOIN cte 
                 ON ARRAY_AGG(DISTINCT "b"."id") && cte.b_ids
GROUP BY "a".id

It does not use an alias b_ids for the array_agg column.

Django code:

cte = (
    With(A)
    .annotate(b_ids=ArrayAgg("b__id", distinct=True))
    .values("id")
)

a_query = A.objects.annotate(b_ids=ArrayAgg("b__id", distinct=True))

q = (
    cte.join(
        a_query, 
        b_ids__overlap=cte.col.b_ids, 
        _join_type=LOUTER,
    )
    .with_cte(cte)
    .annotate(cte_ids=ArrayAgg(cte.col.id, distinct=True))
    .values("id")
)

[millerdev]

The code snippet appears to redefine q without referencing it. Was that a typo? Does it work if you reference the earlier-defined q instead of A? Aside: consider using a different name for the first query rather than re-defining q.

a_query = A.objects.annotate(b_ids=ArrayAgg("b__id", distinct=True))

q = (
    cte.join(
        a_query, 
        b_ids__overlap=...

[a4tarasiuk]

@millerdev Yeah... my bad. It was a typo.

A small update.
The issue is still reproduced, but to solve my task I had to use multiple nested CTEs so I don't have this problem anymore.
When we manipulate with array_agg field on CTE level it works ok. The issue is only with the most outer query (its mapping to raw SQL).