Thread: SQL spec/implementation question: UPDATE
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
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
andy <andy@squeakycode.net> writes:
> 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';
Yeah, that seems to be what they're doing. PG does not bother to make
such a test, on the grounds that it would waste more net cycles than it
would save. Most people are not in the habit of issuing lots of no-op
updates.
Also, if you have a case where you think that is what will happen, you
can add the WHERE-condition for yourself; whereas there is no way in
mysql to get rid of the useless test even if you know it's useless.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 10/21/07 17:52, Tom Lane wrote: > andy <andy@squeakycode.net> writes: >> 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'; > > Yeah, that seems to be what they're doing. PG does not bother to make > such a test, on the grounds that it would waste more net cycles than it > would save. Most people are not in the habit of issuing lots of no-op > updates. > > Also, if you have a case where you think that is what will happen, you > can add the WHERE-condition for yourself; whereas there is no way in > mysql to get rid of the useless test even if you know it's useless. Not to bash MySQL (much...) but ISTM that this is another example of MySQL playing fast and loose with SQL. IOW, the RDBMS shouldn't try to out-think me even if I seem seem to be doing something odd. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHHBoGS9HxQb37XmcRAnGwAKCmiUnUvXHDyGs5Z0q0dZYlVOFaUgCcClhu hwwRK9w9RhFM9lmAPZl2oP8= =6Tso -----END PGP SIGNATURE-----
At 6:00p -0400 on 21 Oct 2007, andy wrote: > I think your comparing apples and oranges. That's why I ask the list! To learn when I'm doing that. ;-) > I'll bet that mysql is > taking a shortcut and testing the value before updating it. Heh. And as Tom points out downthread, that "shortcut" probably doesn't gain anything in the long run. Kevin
At 6:52p -0400 on 21 Oct 2007, Tom Lane wrote: > andy <andy@squeakycode.net> writes: >> 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'; > > Yeah, that seems to be what they're doing. PG does not bother to make > such a test, on the grounds that it would waste more net cycles than it > would save. Most people are not in the habit of issuing lots of no-op > updates. Makes sense. In this particular case, it's a moot point as it's guaranteed to update a single row only (or less), but I was idly curious. In fact, for the application in question, having the behavior of Postgres would make it possible to clean up the application logic a bit, but eh. I'm stuck with MySQL for this project. :-( Kevin
At 11:33p -0400 on 21 Oct 2007, Ron Johnson wrote: > Not to bash MySQL (much...) but ISTM that this is another example of > MySQL playing fast and loose with SQL. I don't have handy a spec guide. Does this mean that MySQL is indeed showing incorrect behavior? I like what's been said upthread: The query MySQL apparently sees: UPDATE test SET name = 'kevin' WHERE passion = 'soccer' AND name <> 'kevin'; The query as I wrote: UPDATE test SET name = 'kevin' WHERE passion = 'soccer'; Even though it is, in fact, the same, it should still be updated because that's what I said. Is that what you're saying? Is that the spec? Thanks, Kevin
Kevin Hunter <hunteke@earlham.edu> writes:
> I don't have handy a spec guide. Does this mean that MySQL is indeed
> showing incorrect behavior?
I think this is really outside the spec. The relevant sections of SQL92
seem to be in 13.10 <update statement: searched>:
<update statement: searched> ::=
UPDATE <table name>
SET <set clause list>
[ WHERE <search condition> ]
...
b) If a <search condition> is specified, then it is applied
to each row of T with the <table name> bound to that row,
and the object rows are those rows for which the result of
the <search condition> is true. The <search condition> is
effectively evaluated for each row of T before updating any
row of T.
...
8) Each object row is updated as specified by each <set clause>.
There is not anything I can see addressing whether an "update" should or
should not be considered to occur if a target column happens to not
change as a result of a commanded update. There is certainly not
anything specifically requiring mysql's behavior, but I don't see
anything specifically rejecting it either.
There is
4) If the set of object rows is empty, then a completion condition
is raised: no data.
but this refers to the case where the given WHERE condition selects
no rows; I see no argument for claiming that it involves whether the
new field values match the old ones.
regards, tom lane
Tom Lane wrote: >> I don't have handy a spec guide. Does this mean that MySQL >> is indeed showing incorrect behavior? > > I think this is really outside the spec. [...] > There is not anything I can see addressing whether an > "update" should or should not be considered to occur if a > target column happens to not change as a result of a > commanded update. Moreover, I can think of one argument why an UPDATE that does not change the value of the row should still occur: There may be an ON UPDATE trigger on the table that you expect to fire whether or not the UPDATE was "optimized away". Yours, Laurenz Albe
On Sunday 21 October 2007, Kevin Hunter <hunteke@earlham.edu> wrote: > Heh. And as Tom points out downthread, that "shortcut" probably doesn't > gain anything in the long run. Considering how expensive updates are in PostgreSQL, I suspect that isn't true. However, the current behaviour does seem to be logical; we did in fact ask for the row to be updated ... -- Ghawar is dying
On Oct 22, 2007, at 11:01 AM, Alan Hodgson wrote: > On Sunday 21 October 2007, Kevin Hunter <hunteke@earlham.edu> wrote: >> Heh. And as Tom points out downthread, that "shortcut" probably >> doesn't >> gain anything in the long run. > > Considering how expensive updates are in PostgreSQL, I suspect that > isn't > true. No, the idea is that the vast majority of updates aren't going to have this happen so you're waisting cpu cycles trying to determine beforehand if the update will have any effect. The gain from not running updates that actually wouldn't change any table data would be dwarfed by the overhead of making that check on every update. > > However, the current behaviour does seem to be logical; we did in > fact ask > for the row to be updated ... Right. And, as someone else pointed out, it also allows any triggers on the table to still run. Erik Jones Software Developer | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
Albe Laurenz skrev: > Tom Lane wrote: >>> I don't have handy a spec guide. Does this mean that MySQL >>> is indeed showing incorrect behavior? >> I think this is really outside the spec. > [...] >> There is not anything I can see addressing whether an >> "update" should or should not be considered to occur if a >> target column happens to not change as a result of a >> commanded update. > > Moreover, I can think of one argument why an UPDATE that > does not change the value of the row should still occur: > There may be an ON UPDATE trigger on the table that you > expect to fire whether or not the UPDATE was "optimized > away". This case seems indeed to be "broken" in MySQL. Nis