Thread: BUG #2303: UPDATE from manual is incorrect

The following bug has been logged online:

Bug reference:      2303
Logged by:          Massimo Fidanza
Email address:      malix0@gmail.com
PostgreSQL version: 8.1
Operating system:   Linux
Description:        UPDATE from manual is incorrect
Details:

Hi all, I have an update similar to the one included in Postgresql
documentation in Postgresql 8.1 -> VI. Reference -> I. SQL Commands ->
UPDATE

The query is the last but one

UPDATE employees SET last_closed_deal = deal.id
    FROM accounts JOIN deals ON (account.id = deal.account_id)
    WHERE deal.employee_id = employees.id
    AND deal.name = 'Rocket Powered Skates'
    AND accounts.name = 'Acme Corporation'
    ORDER BY deal.signed_date DESC LIMIT 1;

this query is not correct and doesn't work with postgresql 8.1.


My query that doesn't work is the first and I modify it in second form that
is the correct one.

------------- FIRST QUERY (NOT WORKING) ---------------
update
    tblstock
set
    npezzi = sum(sr.npezzi)
    ,npezzirimanenti = sum(sr.npezzi)
    ,modelloid = sr.modelloid
    ,objid = sr2.objid
    ,tipostockid = 3
from
    tblstockrighe sr join tblstockrighe sr2
            on sr.modelloid = sr2.modelloid and sr2.id = 110
where
    sr.stockid = tblstock.id
    and sr.stockid = 270
group by
    sr.stockid
    ,sr.modelloid
    ,sr2.objid



------------- SECOND QUERY (WORK) ----------------
update
    tblstock
set
    npezzi = sr.npezzi
    ,npezzirimanenti = sr.npezzi
    ,modelloid = sr.modelloid
    ,objid = sr.objid
    ,tipostockid = 3
from
    (select
        sr.stockid
        ,sum(sr.npezzi) as npezzi
        ,sr.modelloid
        ,sr2.objid
    from
        tblstockrighe sr join tblstockrighe sr2
            on sr.modelloid = sr2.modelloid and sr2.id = 110
    where
        sr.stockid = 270
    group by
        sr.stockid
        ,sr.modelloid
        ,sr2.objid
    ) sr
where
    sr.stockid = tblstock.id

"Massimo Fidanza" <malix0@gmail.com> writes:
> The query is the last but one

> UPDATE employees SET last_closed_deal = deal.id
>     FROM accounts JOIN deals ON (account.id = deal.account_id)
>     WHERE deal.employee_id = employees.id
>     AND deal.name = 'Rocket Powered Skates'
>     AND accounts.name = 'Acme Corporation'
>     ORDER BY deal.signed_date DESC LIMIT 1;

> this query is not correct and doesn't work with postgresql 8.1.

It still works, if you enable add_missing_from.  But I agree that the
example shouldn't assume that.

> My query that doesn't work is the first and I modify it in second form that
> is the correct one.

That appears to be an entirely unrelated issue, which is whether
aggregates in the SET list of an UPDATE make any sense.  I'm inclined
to think not --- what are you aggregating over?

            regards, tom lane

I wrote:
> "Massimo Fidanza" <malix0@gmail.com> writes:
>> this query is not correct and doesn't work with postgresql 8.1.

> It still works, if you enable add_missing_from.

I take that back --- FROM is not the issue (indeed, it has one).
The problem is the ORDER BY and LIMIT clauses.  I dunno who put
in this example, but I'd like some of what they were smoking.
I've taken it out again, since without that it's not really showing
anything that the prior examples don't cover.

            regards, tom lane

Excuse me if I was no very clear and I don't put you in right direction,
But yes the problem is not with FROM but with ORDER BY or GROUP BY.
As you can see in my query I need to GROUP BY modelloid ad aggregate
on npezzi. To solve the problem I rewrite the query in the second for and
encapsulated the subquery with GROUP BY in (). This for work without
problem.


Bye Massimo Fidanza

2006/3/9, Tom Lane <tgl@sss.pgh.pa.us>:
>
> I wrote:
> > "Massimo Fidanza" <malix0@gmail.com> writes:
> >> this query is not correct and doesn't work with postgresql 8.1.
>
> > It still works, if you enable add_missing_from.
>
> I take that back --- FROM is not the issue (indeed, it has one).
> The problem is the ORDER BY and LIMIT clauses.  I dunno who put
> in this example, but I'd like some of what they were smoking.
> I've taken it out again, since without that it's not really showing
> anything that the prior examples don't cover.
>
>                         regards, tom lane
>