Re: Minor buglet in update...from (I think) - Mailing list pgsql-hackers

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Oh, so it is the aggregate.  What threw me off is that both parts of the
> WHERE clause are required to cause the failure,

Not necessarily; I think it's got more to do with a null aggregate
result:

regression=# create table t1 (f1 datetime);
CREATE
regression=# create table t2 (f2 datetime);
CREATE
regression=# update t2 set f2 = min(f1) from t1;
ERROR:  ExecutePlan: (junk) `ctid' is NULL!
regression=# insert into t1 values ('now');
INSERT 400577 1
regression=# update t2 set f2 = min(f1) from t1;
ERROR:  ExecutePlan: (junk) `ctid' is NULL!
regression=# insert into t2 values ('now');
INSERT 400578 1
regression=# update t2 set f2 = min(f1) from t1;
UPDATE 1
regression=#

However the ERROR is only one symptom.  The real problem is that the
calculation that's being done is useless/nonsensical.

> I don't see a problem with aggregates in UPDATE,

Think harder ... what is the aggregate being taken over, and how do you
associate the aggregate's single result row with any particular row in
the UPDATE's target table?
        regards, tom lane