Thread: Bug regarding update.. from and aggregates ?

Hi

When executing the following statements:
create schema bugtest ;
drop table bugtest.t1 ;
drop table bugtest.t2 ;
create table bugtest.t1(id int4, maximum int4, dummy int4) ;
create table bugtest.t2(id int4, entry int4) ;
insert into bugtest.t1 (id) values (1) ;
insert into bugtest.t1 (id) values (2) ;
insert into bugtest.t2 (id, entry) values (1, 1) ;
insert into bugtest.t2 (id, entry) values (1, 2) ;
insert into bugtest.t2 (id, entry) values (2, 3) ;
insert into bugtest.t2 (id, entry) values (2, 4) ;
update bugtest.t1 set maximum = max(t2.entry) from bugtest.t2
    where t1.id = t2.id ;

The last update given "One row affected" - and indeed, t1 looks
like this after the update:
id | maximum
1  | 4
2  | <NULL>

While I would expect:
id | maximum
1  | 2
2  | 4

Is this I bug, or is the observed behaviour actually intended?

greetings, Florian Pflug

This should go to pgsql-sql or so.

Florian G. Pflug wrote:
> Hi
>
> When executing the following statements:
> create schema bugtest ;
> drop table bugtest.t1 ;
> drop table bugtest.t2 ;
> create table bugtest.t1(id int4, maximum int4, dummy int4) ;
> create table bugtest.t2(id int4, entry int4) ;
> insert into bugtest.t1 (id) values (1) ;
> insert into bugtest.t1 (id) values (2) ;
> insert into bugtest.t2 (id, entry) values (1, 1) ;
> insert into bugtest.t2 (id, entry) values (1, 2) ;
> insert into bugtest.t2 (id, entry) values (2, 3) ;
> insert into bugtest.t2 (id, entry) values (2, 4) ;
> update bugtest.t1 set maximum = max(t2.entry) from bugtest.t2
>     where t1.id = t2.id ;
>
> The last update given "One row affected" - and indeed, t1 looks
> like this after the update:
> id | maximum
> 1  | 4
> 2  | <NULL>
>
> While I would expect:
> id | maximum
> 1  | 2
> 2  | 4
>
> Is this I bug, or is the observed behaviour actually intended?

The query is probably not what you mean. You need to make the from
clause specific, i.e. join t1 explicitely (mssql does so implicitely,
pgsql not).

Regards,
Andreas