[DB-SIG] definition of .rowcount

[Michael Bayer]

On Jun 13, 2012, at 6:14 PM, Peter Eisentraut wrote:

> On tis, 2012-06-12 at 10:07 -0400, Michael Bayer wrote:
>> So as many of you know, MySQL has actually two options for how
>> rowcount can be reported.  It can report the number of rows "found",
>> that is, matched by the WHERE criterion of an UPDATE or DELETE
>> statement, or it can report the number of rows that were actually
>> updated or deleted, and here we're talking about an UPDATE that may or
>> may not have had a net change in row value.   The Python MySQL drivers
>> tend to default to the latter.    Every other database/DBAPI I've
>> worked with only offers the former.
> 
> Even if the value that is stored before and after an update is logically
> the same, this could fire triggers, so the row is still "affected".
> Also, some data types aren't comparable, so you can't always know this.
> So I think the problem with the alternative behavior of MySQL is that it
> isn't even generally definable, so it can't possibly be the correct
> answer for a general interface.
> 
> There is also the question of how this definition for the UPDATE command
> extends to other commands.  It probably doesn't, in a sensible way
> (well, maybe for REPLACE/INSERT ON DUPLICATE KEY UPDATE, but then you
> might get a second-level inconsistency between the regular UPDATE and
> the UPDATE run as part of the INSERT).

It goes without saying that this is one of a myriad number of areas where MySQL gets it wrong. 

> I agree that the terms could be clarified, nevertheless.

Yup, I'd leave the word "affected" in, then just add a note that this can mean different things depending on backend.