Re: SQL spec/implementation question: UPDATE

Kevin Hunter wrote:
> Hullo list,
>
> A perhaps esoteric question:
>
> Short version:
>
> What do the specs say (if anything) about returning information from
> UPDATE commands? Or about handling update request that don't
> effectively do anything?
>
> Longer version:
>
> CREATE TABLE test (
> id SERIAL NOT NULL,
> name TEXT NOT NULL,
> passion TEXT NOT NULL,
>
> PRIMARY KEY( id )
> );
>
> INSERT INTO test (name, passion) VALUES ('colin', 'contra-dancing');
> INSERT INTO test (name, passion) VALUES ('alex', 'contemplating');
> INSERT INTO test (name, passion) VALUES ('kevin', 'soccer');
> INSERT INTO test (name, passion) VALUES ('toby', 'biking');
>
> BEGIN;
> UPDATE test SET name = 'kevin' WHERE passion = 'soccer';
> Previous statement 5 times (or whatever)
> COMMIT;
>
> Even though the last 5 statements effectively do nothing, every UPDATE
> returns "UPDATE 1". If I do the same thing in MySQL, I get "Rows
> matched: 1 Changed: 0 Warnings: 0". (I used the INNODB engine in MySQL.)
>
> In PHP, the {pg,mysql}_affected_rows functions return the same results:
> 1 from Postgres and 0 from MySQL.
>
> So, two questions: which behavior is correct, or is it even defined? If
> Postgres behavior is correct, why does it need to write to disk, (since
> the tuple isn't actually changing in value)?
>
> Experience tells me that Postgres is probably doing the correct thing,
> but it almost seems that it could be corner case, doesn't matter either
> way, and is could be just a consequence of the MVCC guarantees, etc.
>
> TIA,
>
> Kevin

I think your comparing apples and oranges. I'll bet that mysql is
taking a shortcut and testing the value before updating it.

The update is probably more close to:
update test set name = 'kevin' where passion = 'soccer' and name <> 'kevin';

In this case, pg too, would only update once.

-Andy