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?