Limit has no effect on aggregation 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
    &quot;users&quot;
join &quot;permissions&quot; &quot;permissions&quot; on
    &quot;userId&quot; = &quot;users&quot;.&quot;id&quot;
    and &quot;userId&quot; = 1
group by
    &quot;users&quot;.&quot;id&quot;
order by
    &quot;users&quot;.&quot;id&quot; 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?
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.&quot;userId&quot; = 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.&quot;userId&quot; = users.id
WHERE users.id = 1;
ORDER BY users.id, permissions.id
LIMIT 1;