Hi all,

I have a query with a WHERE EXISTS clause that returns a nonempty result
set (33 records).
When I add a GROUP BY clause, I get an empty result.

Here's the query:

SELECT teams.id AS team_id, users.id AS user_id
FROM teams
JOIN users ON teams.user_id = users.id
WHERE EXISTS (
SELECT * FROM team_members
WHERE team_members.team_id = teams.id
)
GROUP BY users.id

The intention is:
* Select all teams that are not empty (have at least one corresponding
entry in team_members)
* Aggregate over all nonempty teams of every user
(above, I left out a SUM expression from the real-life query)

When I leave out the WHERE EXISTS clause, I get the expected result: one
record per user, with the data for one of his teams (even for those
users who created just empty teams - that's what the WHERE EXISTS is
supposed to filter out).
When I leave out the GROUP BY clause, I get the expected result: all
nonempty teams.
When I have both clauses, I get an empty result.

What's going on?

Even if the WHERE EXISTS is processed after aggregation, I should get at
least some results: most users have members in their teams. However,
that's probably a red herring, since I believe the WHERE clause is
processed before aggregation anyway (is that correct?).

Insights? Comments?

Regards,
Jo

On Nov 15, 11:16 am, Joachim Durchholz <j...@durchholz.org> wrote:
> Hi all,
>
> I have a query with a WHERE EXISTS clause that returns a nonempty result
> set (33 records).
> When I add a GROUP BY clause, I get an empty result.
>
> Here's the query:
>
> SELECT teams.id AS team_id, users.id AS user_id
> FROM teams
> JOIN users ON teams.user_id = users.id
> WHERE EXISTS (
> SELECT * FROM team_members
> WHERE team_members.team_id = teams.id
> )
> GROUP BY users.id
>
> The intention is:
> * Select all teams that are not empty (have at least one corresponding
> entry in team_members)
> * Aggregate over all nonempty teams of every user
> (above, I left out a SUM expression from the real-life query)
>
> When I leave out the WHERE EXISTS clause, I get the expected result: one
> record per user, with the data for one of his teams (even for those
> users who created just empty teams - that's what the WHERE EXISTS is
> supposed to filter out).
> When I leave out the GROUP BY clause, I get the expected result: all
> nonempty teams.
> When I have both clauses, I get an empty result.
>
> What's going on?
>
> Even if the WHERE EXISTS is processed after aggregation, I should get at
> least some results: most users have members in their teams. However,
> that's probably a red herring, since I believe the WHERE clause is
> processed before aggregation anyway (is that correct?).
>
> Insights? Comments?
>
> Regards,
> Jo

Why not use another JOIN?

SELECT
teams.id AS team_id,
users.id AS user_id
FROM teams
JOIN users ON teams.user_id = users.id
JOIN team_members ON team_members.team_id = teams.id
GROUP BY users.id