Limit has no effect on aggregation

Question

I have 2 tables:

users:
id 
------
1

permissions:
id | action | userId
---|--------|--------
 1 |  read  |    1
 2 |  write |    1

When I run the following query (return users with its permissions):

select
    users.*,
    (string_agg(permissions.action::text, ',') as permissions
from
    "users"
join "permissions" "permissions" on
    "userId" = "users"."id"
    and "userId" = 1
group by
    "users"."id"
order by
    "users"."id" asc
limit 1

I get:

id | permissions
---|-------------
 1 |  read,write

Which is great (this is what I need) but I expected it to return:

id | permissions
---|-------------
 1 |  read

Because of the query has limit 1 constraint.
Anyone can shed some light on what happens in the background?

Answer 1

LIMIT is applied late in the query, after aggregation, so you get the complete first group.

You should apply the LIMIT in a lateral subquery:

SELECT users.*, p.action
FROM users
   CROSS JOIN LATERAL (SELECT permissions.action
                       FROM permissions
                       WHERE permissions."userId" = users.id
                       ORDER BY permissions.id
                       LIMIT 1) AS p
WHERE users.id = 1;

And alternative is the non-standard DISTINCT ON:

SELECT DISTINCT ON (users.id)
       users.*, p.action
FROM users
   JOIN permissions AS p
      ON p."userId" = users.id
WHERE users.id = 1;
ORDER BY users.id, permissions.id
LIMIT 1;