Re: [PgSQL 7.4.1] Problem with subquery

Edwin Pauli <edwin@epauli.dyndns.org> writes:
> There are troubles with a subquery after the upgrade.

> SELECT team_naam, team_id, wpim, (
>     SELECT count(thuis_uitslag)
>         FROM wedstrijden
>         WHERE (thuis_wed = t.team_id
>             OR uit_wed = t.team_id)
>             AND seizoen_id = 3 AND klasse_id = 1)
>         AS wd, (
>     SELECT count(t.team_id)
>         FROM wedstrijden w
>         WHERE (thuis_wed = t.team_id AND thuis_uitslag >
>             uit_uitslag OR uit_wed = t.team_id
>             AND thuis_uitslag < uit_uitslag)
>             AND seizoen_id = 3
>             AND klasse_id = 1 )
>         AS gw
> FROM teams t
> WHERE seizoen_id = 3 AND klasse_id = 1
> GROUP BY t.team_naam, t.team_id, t.team_id, t.wpim

> ERROR:  more than one row returned by a subquery used as an expression

Since t.team_id is a variable of the outer query, count(t.team_id) is an
aggregate of the outer query according to the SQL spec, and accordingly
it is just a constant from the perspective of your second sub-SELECT.
That sub-SELECT is therefore not doing any aggregation of its own and
is trying to return multiple rows.

We didn't implement this fine point per-spec before 7.4, but we do now.
See http://archives.postgresql.org/pgsql-hackers/2003-06/msg00070.php

Solution: use count(*) instead.  AFAICS you have no need to check
whether t.team_id is nonnull, since the WHERE clause could not succeed
anyway if it's null.

            regards, tom lane