Re: how to cache subtotals -- with subselect?

On Mon, 3 Mar 2003, Will Trillich wrote:

> i'm having some trouble finding the error here -- we're trying
> to cache subtotals from subset tables:
>
> db=# select
> db-#    -- person last-name:
> db-#    p.lname,
> db-#    -- CACHED subtotals where team involvement == 'client':
> db-#    p.been_client,
> db-#    -- LIVE subtotals where team involvement == 'client':
> db-#    (
> db(#      select count(*)
> db(#      from team t join involv_v v on (t.involv_v=v.id)
> db(#      where t.person=p.id and v.name='Client'
> db(#    ) as ct
> db-# from _person p;
>
>   lname   | been_client | ct
> ----------+-------------+----
>  Trillich |           4 |  0 <== whoops
>  Tharp    |           0 |  0
>  Stiles   |           0 |  0
> (3 rows)
>
> here, 'trillich' has person.been_client = 4 which should instead
> be 0 (that is, of the projects he's involved with, none are
> as a 'client'). so...
>
> db=#  update _person set
> db-#  been_client = (
> db(#     select count(*) from team t join involv_v v on (v.id=t.involv_v)
> db(#     where v.name='Client' and t.person=person.id
> db(#  );
> NOTICE:  Adding missing FROM-clause entry in subquery for table "person"
> UPDATE 3
>
> three? that's not right. and what's up with adding a from
> clause? the outer query already has a reference to _person which
> the inner needs to use.

But the inner doesn't have a reference to _person, it has a reference to
person, was it intentional to use different tables in those two places?