Re: UPDATE crash in HEAD and 8.1

Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>   
>> update pk set id = max(id) + 2;
>>     
>
> I'm fairly sure this query is illegal per spec.  There are ancient
> discussions in the archives about whether aggregates in an UPDATE target
> list can have a consistent interpretation or not.  We never found one,
> but never got around to disallowing it either.  Maybe it's time.  If you
> try it with something like sum() you don't get a crash, but you do get
> rather bizarre behavior.
>
> Having said that, this may well expose a bug in the MAX-optimization
> code that has consequences for more useful queries.  I'll take a look
> later today if no one beats me to it.
>
>     


If you try the query on 8.0 and before you don't get a crash either, but 
the result is unexpected. Try this version:

create table pk (id bigserial primary key, orig bigint);
insert into pk (id) values (DEFAULT);
insert into pk (id) values (DEFAULT);
insert into pk (id) values (DEFAULT);
update pk set orig = id;
select * from pk;
update pk set id = max(id) + 2;
select * from pk;



One could almost argue that crashing would be better ;-)

cheers

andrew