update query returns wrong number of rows affected

For some UPDATE queries (possibly those involving joins?), the number of
rows reported for the query is the number of rows processed, rather than the
number of rows actually changed.

This shows the results from a command prompt:

mysql> UPDATE jobpage LEFT JOIN dbin.pagedata as plant
-> ON pagenum=page_numberid AND job_numberid='7' and job_yearid='2004'
-> SET jobpage.date_received=plant.date_received
-> WHERE number='7-2004'
-> ;
Query OK, 0 rows affected (5.14 sec)
Rows matched: 224 Changed: 0 Warnings: 0

Using dbd-mysql, the result returned is 224 rather than 0

----

win2k

Binary build 809 provided by ActiveState Corp. http://www.ActiveState.com
ActiveState is a division of Sophos.
Built Feb 3 2004 00:28:51

Crypt-TEA 1.44: up to date.
Crypt-Tea 1.44: up to date.
DBD-mysql 2.9003: up to date.
DBI 1.43: up to date.
Data-Dump 1.01: up to date.
IO-Zlib 1.01: up to date.
Mail-Sender 0.8.10: up to date.
Mail-Sendmail 0.79: up to date.
TimeDate 1.16: up to date.
libwin32 0.21: up to date.

mysql Ver 12.22 Distrib 4.0.20a, for Win95/Win98 (i32)


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: update query returns wrong number of rows affected

Dave Dyer wrote:

> For some UPDATE queries (possibly those involving joins?), the number of
> rows reported for the query is the number of rows processed, rather than the
> number of rows actually changed.

What does the distinction between "affected" and "matched" mean? Is it,
that some rows already had the new value? If so, I would think that I
would indeed want to see the value "matched", because the typical use
case is I can see is comparing the number of rows against 0.


Jochen


--
http://lilypie.com/baby1/050423/1/5/1/+1

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: update query returns wrong number of rows affected

Dave Dyer wrote:

> For some UPDATE queries (possibly those involving joins?), the number of
> rows reported for the query is the number of rows processed, rather than the
> number of rows actually changed.

What does the distinction between "affected" and "matched" mean? Is it,
that some rows already had the new value? If so, I would think that I
would indeed want to see the value "matched", because the typical use
case is I can see is comparing the number of rows against 0.


Jochen


--
http://lilypie.com/baby1/050423/1/5/1/+1

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: update query returns wrong number of rows affected

On Wed, 15 Sep 2004, Dave Dyer wrote:

> For some UPDATE queries (possibly those involving joins?), the number of
> rows reported for the query is the number of rows processed, rather than the
> number of rows actually changed.
>
> This shows the results from a command prompt:
>
> mysql> UPDATE jobpage LEFT JOIN dbin.pagedata as plant
> -> ON pagenum=page_numberid AND job_numberid='7' and job_yearid='2004'
> -> SET jobpage.date_received=plant.date_received
> -> WHERE number='7-2004'
> -> ;
> Query OK, 0 rows affected (5.14 sec)
> Rows matched: 224 Changed: 0 Warnings: 0
>
> Using dbd-mysql, the result returned is 224 rather than 0

That is correct.

Rudy



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: update query returns wrong number of rows affected

On Wed, 15 Sep 2004, Dave Dyer wrote:

> For some UPDATE queries (possibly those involving joins?), the number of
> rows reported for the query is the number of rows processed, rather than the
> number of rows actually changed.
>
> This shows the results from a command prompt:
>
> mysql> UPDATE jobpage LEFT JOIN dbin.pagedata as plant
> -> ON pagenum=page_numberid AND job_numberid='7' and job_yearid='2004'
> -> SET jobpage.date_received=plant.date_received
> -> WHERE number='7-2004'
> -> ;
> Query OK, 0 rows affected (5.14 sec)
> Rows matched: 224 Changed: 0 Warnings: 0
>
> Using dbd-mysql, the result returned is 224 rather than 0

That is correct.

Rudy



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: update query returns wrong number of rows affected

At 05:00 PM 9/15/2004, Jochen Wiedmann wrote:
>Dave Dyer wrote:
>
>>For some UPDATE queries (possibly those involving joins?), the number of
>>rows reported for the query is the number of rows processed, rather than the
>>number of rows actually changed.
>
>What does the distinction between "affected" and "matched" mean? Is it, that some rows already had the new value? If so, I would think that I would indeed want to see the value "matched", because the typical use case is I can see is comparing the number of rows against 0.

The case in point, I'm merging two databases and all the
rows are usually the same. I'm interested in the exceptions;
and to me "affected rows" means changed rows.

Maybe it's always been this way, but the C api makes the number
of changed rows easily available, I find it very useful.



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: update query returns wrong number of rows affected

At 05:00 PM 9/15/2004, Jochen Wiedmann wrote:
>Dave Dyer wrote:
>
>>For some UPDATE queries (possibly those involving joins?), the number of
>>rows reported for the query is the number of rows processed, rather than the
>>number of rows actually changed.
>
>What does the distinction between "affected" and "matched" mean? Is it, that some rows already had the new value? If so, I would think that I would indeed want to see the value "matched", because the typical use case is I can see is comparing the number of rows against 0.

The case in point, I'm merging two databases and all the
rows are usually the same. I'm interested in the exceptions;
and to me "affected rows" means changed rows.

Maybe it's always been this way, but the C api makes the number
of changed rows easily available, I find it very useful.



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: update query returns wrong number of rows affected

Dave Dyer wrote:

> The case in point, I'm merging two databases and all the
> rows are usually the same. I'm interested in the exceptions;
> and to me "affected rows" means changed rows.
>
> Maybe it's always been this way, but the C api makes the number
> of changed rows easily available, I find it very useful.

But you can answer that question by performing a simple count query?


--
http://lilypie.com/baby1/050423/1/5/1/+1

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: update query returns wrong number of rows affected

Dave Dyer wrote:

> The case in point, I'm merging two databases and all the
> rows are usually the same. I'm interested in the exceptions;
> and to me "affected rows" means changed rows.
>
> Maybe it's always been this way, but the C api makes the number
> of changed rows easily available, I find it very useful.

But you can answer that question by performing a simple count query?


--
http://lilypie.com/baby1/050423/1/5/1/+1

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: update query returns wrong number of rows affected

At 06:17 PM 9/15/2004, Jochen Wiedmann wrote:
>Dave Dyer wrote:
>
>>The case in point, I'm merging two databases and all the
>>rows are usually the same. I'm interested in the exceptions;
>>and to me "affected rows" means changed rows.
>>Maybe it's always been this way, but the C api makes the number
>>of changed rows easily available, I find it very useful.
>
>But you can answer that question by performing a simple count query?

There are good reasons to just want the count as a side
effect.

For one, it's reliable to say "change this and tell me
how many changed" it's not reliable to say "if I changed this, how many
would change" and then say "ok do it" and expect the number of changed
records to be the same.

For another, in the case in point, the query takes several seconds
and I do thousands of them. Doubling up would not be especially
smart.

It's obvious you shouldn't change the definition of numrows
at this late date, but I'd like the other number to be available.



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: update query returns wrong number of rows affected

At 06:17 PM 9/15/2004, Jochen Wiedmann wrote:
>Dave Dyer wrote:
>
>>The case in point, I'm merging two databases and all the
>>rows are usually the same. I'm interested in the exceptions;
>>and to me "affected rows" means changed rows.
>>Maybe it's always been this way, but the C api makes the number
>>of changed rows easily available, I find it very useful.
>
>But you can answer that question by performing a simple count query?

There are good reasons to just want the count as a side
effect.

For one, it's reliable to say "change this and tell me
how many changed" it's not reliable to say "if I changed this, how many
would change" and then say "ok do it" and expect the number of changed
records to be the same.

For another, in the case in point, the query takes several seconds
and I do thousands of them. Doubling up would not be especially
smart.

It's obvious you shouldn't change the definition of numrows
at this late date, but I'd like the other number to be available.



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: update query returns wrong number of rows affected

On Wed, 15 Sep 2004, Dave Dyer wrote:

> At 05:00 PM 9/15/2004, Jochen Wiedmann wrote:
> >Dave Dyer wrote:
> >
> >>For some UPDATE queries (possibly those involving joins?), the number of
> >>rows reported for the query is the number of rows processed, rather than the
> >>number of rows actually changed.
> >
> >What does the distinction between "affected" and "matched" mean? Is it, that some rows already had the new value? If so, I would think that I would indeed want to see the value "matched", because the typical use case is I can see is comparing the number of rows against 0.
>
> The case in point, I'm merging two databases and all the
> rows are usually the same. I'm interested in the exceptions;
> and to me "affected rows" means changed rows.
>

You can get that by toggling mysql_clinet_found_rows, but it would be
non-portable.

> Maybe it's always been this way, but the C api makes the number
> of changed rows easily available, I find it very useful.
>

IMO, that is just the C api exposing something that it maybe should not be
exposing to the user viz., an internal optimization that the database does to
cut down on disk operations. The fact is, the update operation *would/should*
update the whole set (and in an abstract sort of way it can be viewed as having
updated the whole set), but mysql notices that it does not need to physically
change the rows on disk because they are already the same and therefore returns
early and tells you (for some reason).


What you are asking the db to do is:

UPDATE thetable
SET thecol = $new_value
WHERE thecol <> new_value AND thecol = $some_old_value;

Of course if you have this you already know whether or not you need to do the
update, so maybe the folowing example is more applicable, though not as clear:

UPDATE thetable
SET thecol = $new_value
WHERE thecol <> new_value

both of which will only update thecol when the_col is != to new_value.

And Tim seems to want it to work this way too:

http://www.mail-archive.com/dbi-dev@perl.org/msg02136.html

Injoy,

Rudy



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: update query returns wrong number of rows affected

On Wed, 15 Sep 2004, Dave Dyer wrote:

> At 05:00 PM 9/15/2004, Jochen Wiedmann wrote:
> >Dave Dyer wrote:
> >
> >>For some UPDATE queries (possibly those involving joins?), the number of
> >>rows reported for the query is the number of rows processed, rather than the
> >>number of rows actually changed.
> >
> >What does the distinction between "affected" and "matched" mean? Is it, that some rows already had the new value? If so, I would think that I would indeed want to see the value "matched", because the typical use case is I can see is comparing the number of rows against 0.
>
> The case in point, I'm merging two databases and all the
> rows are usually the same. I'm interested in the exceptions;
> and to me "affected rows" means changed rows.
>

You can get that by toggling mysql_clinet_found_rows, but it would be
non-portable.

> Maybe it's always been this way, but the C api makes the number
> of changed rows easily available, I find it very useful.
>

IMO, that is just the C api exposing something that it maybe should not be
exposing to the user viz., an internal optimization that the database does to
cut down on disk operations. The fact is, the update operation *would/should*
update the whole set (and in an abstract sort of way it can be viewed as having
updated the whole set), but mysql notices that it does not need to physically
change the rows on disk because they are already the same and therefore returns
early and tells you (for some reason).


What you are asking the db to do is:

UPDATE thetable
SET thecol = $new_value
WHERE thecol <> new_value AND thecol = $some_old_value;

Of course if you have this you already know whether or not you need to do the
update, so maybe the folowing example is more applicable, though not as clear:

UPDATE thetable
SET thecol = $new_value
WHERE thecol <> new_value

both of which will only update thecol when the_col is != to new_value.

And Tim seems to want it to work this way too:

http://www.mail-archive.com/dbi-dev@perl.org/msg02136.html

Injoy,

Rudy



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: update query returns wrong number of rows affected

On Wed, 15 Sep 2004, Dave Dyer wrote:

> At 06:17 PM 9/15/2004, Jochen Wiedmann wrote:
> >Dave Dyer wrote:
> >
> >>The case in point, I'm merging two databases and all the
> >>rows are usually the same. I'm interested in the exceptions;
> >>and to me "affected rows" means changed rows.
> >>Maybe it's always been this way, but the C api makes the number
> >>of changed rows easily available, I find it very useful.
> >
> >But you can answer that question by performing a simple count query?
>
> There are good reasons to just want the count as a side
> effect.
>
> For one, it's reliable to say "change this and tell me
> how many changed" it's not reliable to say "if I changed this, how many
> would change" and then say "ok do it" and expect the number of changed
> records to be the same.

They had better be the same ;) :

eval {
$dbh->begin_work();
my $count = $do_count->execute();
$do_update->execute(); # this better not be updateing a different set
# Assuming ANSI default transaction isolation level.
$dbh->commit();
}; if (my $e = $@) {
eval {$dbh->rollback()};
die die die "I am aweary, aweary, / Oh God, that I were dead! $e";
}

> For another, in the case in point, the query takes several seconds
> and I do thousands of them. Doubling up would not be especially
> smart.
>

But you could munge your where clause to only update the rows that need to be
updated

And think of it from the point of a record that had a last-updated-time that
*should* probably be changed when you do the update and that would cause
thousands of extra timestamp updates that you don't need, or think of it with
update triggers in mind they would have to fire, right? So then
you would have a metric boatlaod of extra trigers that could or could not be
updating something.


Rudy


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: update query returns wrong number of rows affected

On Wed, 15 Sep 2004, Dave Dyer wrote:

> At 06:17 PM 9/15/2004, Jochen Wiedmann wrote:
> >Dave Dyer wrote:
> >
> >>The case in point, I'm merging two databases and all the
> >>rows are usually the same. I'm interested in the exceptions;
> >>and to me "affected rows" means changed rows.
> >>Maybe it's always been this way, but the C api makes the number
> >>of changed rows easily available, I find it very useful.
> >
> >But you can answer that question by performing a simple count query?
>
> There are good reasons to just want the count as a side
> effect.
>
> For one, it's reliable to say "change this and tell me
> how many changed" it's not reliable to say "if I changed this, how many
> would change" and then say "ok do it" and expect the number of changed
> records to be the same.

They had better be the same ;) :

eval {
$dbh->begin_work();
my $count = $do_count->execute();
$do_update->execute(); # this better not be updateing a different set
# Assuming ANSI default transaction isolation level.
$dbh->commit();
}; if (my $e = $@) {
eval {$dbh->rollback()};
die die die "I am aweary, aweary, / Oh God, that I were dead! $e";
}

> For another, in the case in point, the query takes several seconds
> and I do thousands of them. Doubling up would not be especially
> smart.
>

But you could munge your where clause to only update the rows that need to be
updated

And think of it from the point of a record that had a last-updated-time that
*should* probably be changed when you do the update and that would cause
thousands of extra timestamp updates that you don't need, or think of it with
update triggers in mind they would have to fire, right? So then
you would have a metric boatlaod of extra trigers that could or could not be
updating something.


Rudy


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: update query returns wrong number of rows affected

> UPDATE thetable
> SET thecol = $new_value
> WHERE thecol <> new_value

More like

UPDATE thetable SET a1=b1,a2-b2 ... WHERE a1!=b1 OR a2!=b2 ...

This would not be in my A-list of paradigms.

There are all sorts of potential complications because the
semantics of assignment are not exactly the same as the
semantics of comparison. Consider nulls on either side
of the assignment, mismatches in ENUM types, illegal
assignments due to unique keys, case mismatch of strings,
simple typos if you fail to maintain the two lists of = and !=
clauses exactly the same ... the list is probably much longer.

If I'm trying to implement a simple, reliable trigger:

if () { }

I really want the functionality from the C api

You may be correct that if some databases hide the information,
such a capability would be nonportable.


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: update query returns wrong number of rows affected

> UPDATE thetable
> SET thecol = $new_value
> WHERE thecol <> new_value

More like

UPDATE thetable SET a1=b1,a2-b2 ... WHERE a1!=b1 OR a2!=b2 ...

This would not be in my A-list of paradigms.

There are all sorts of potential complications because the
semantics of assignment are not exactly the same as the
semantics of comparison. Consider nulls on either side
of the assignment, mismatches in ENUM types, illegal
assignments due to unique keys, case mismatch of strings,
simple typos if you fail to maintain the two lists of = and !=
clauses exactly the same ... the list is probably much longer.

If I'm trying to implement a simple, reliable trigger:

if () { }

I really want the functionality from the C api

You may be correct that if some databases hide the information,
such a capability would be nonportable.


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: update query returns wrong number of rows affected

>
>> For one, it's reliable to say "change this and tell me
>> how many changed" it's not reliable to say "if I changed this, how many
>> would change" and then say "ok do it" and expect the number of changed
>> records to be the same.
>
>They had better be the same ;) :

Not in a universe with more than one concurrent process.


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: update query returns wrong number of rows affected

>
>> For one, it's reliable to say "change this and tell me
>> how many changed" it's not reliable to say "if I changed this, how many
>> would change" and then say "ok do it" and expect the number of changed
>> records to be the same.
>
>They had better be the same ;) :

Not in a universe with more than one concurrent process.


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: update query returns wrong number of rows affected

On Wed, 15 Sep 2004, Dave Dyer wrote:

> >> For one, it's reliable to say "change this and tell me
> >> how many changed" it's not reliable to say "if I changed this, how many
> >> would change" and then say "ok do it" and expect the number of changed
> >> records to be the same.
> >
> >They had better be the same ;) :
>
> Not in a universe with more than one concurrent process.

You missed the transaction right below that statement ;)

Injoy,

Rudy


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: update query returns wrong number of rows affected

On Wed, 15 Sep 2004, Dave Dyer wrote:

> >> For one, it's reliable to say "change this and tell me
> >> how many changed" it's not reliable to say "if I changed this, how many
> >> would change" and then say "ok do it" and expect the number of changed
> >> records to be the same.
> >
> >They had better be the same ;) :
>
> Not in a universe with more than one concurrent process.

You missed the transaction right below that statement ;)

Injoy,

Rudy


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: update query returns wrong number of rows affected

Rudy, --

On Wed, Sep 15, 2004 at 06:24:07PM -0700, Dave Dyer wrote:
> > >>and to me "affected rows" means changed rows.
> > For one, it's reliable to say "change this and tell me
> > how many changed" it's not reliable to say "if I changed this, how many
> > would change" and then say "ok do it" and expect the number of changed
> > records to be the same.

I would add "reliable" here means lack of race conditions.
For those of us who care about consistency of apps based on
non-transactional engine, it makes lots of sense.
Mysql provides such info, so it should not be an issue for perl layer.

> > >Dave Dyer wrote:
> > >>Maybe it's always been this way, but the C api makes the number
> > >>of changed rows easily available, I find it very useful.

No, that's DBD has been changed at some point,
for compatibility with oracle and other DBD-s.

mysql_client_found_rows() can alter this behavior,
however to me something like $sth->affected_rows()
would be more convenient.

On Wed, Sep 15, 2004 at 09:51:24PM -0400, Rudy Lippan wrote:
> eval {
> $dbh->begin_work();
> my $count = $do_count->execute();
> $do_update->execute(); # this better not be updateing a different set
> # Assuming ANSI default transaction isolation level.
> $dbh->commit();
> }; if (my $e = $@) {
> eval {$dbh->rollback()};
> die die die "I am aweary, aweary, / Oh God, that I were dead! $e";
> }

Use transaction just for return updated rows count? Wow.
Why don't just implement something like $sth->updated_rows() instead?
Why switch over to InnoDB at all, if apps is happy with MyISAM?
Pretty strong design decision...
I guess it was a joke.

> And think of it from the point of a record that had a last-updated-time that
> *should* probably be changed when you do the update and that would cause
> thousands of extra timestamp updates that you don't need, or think of it with
> update triggers in mind they would have to fire, right? So then
> you would have a metric boatlaod of extra trigers that could or could not be
> updating something.

Yes, if apps *must do so* (update timestamp) always, it makes sense.

Assumption here is mysql does timestamp update always,
even if engine detected no actual updates made
in other [then timestamp] columns.
Is it really so?
Let's see...
--------------------------------------------
w@kane:~>mysql -hkane test
Your MySQL connection id is 11244 to server version: 4.0.20-standard-log
mysql> DROP TABLE IF EXISTS t1
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (id INT , txt TIMESTAMP )
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO t1 (id) VALUES (11)
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 (id) VALUES (12)
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1
+------+----------------+
| id | tstamp |
+------+----------------+
| 11 | 20040915213930 |
| 12 | 20040915213930 |
+------+----------------+
2 rows in set (0.00 sec)

mysql> SELECT BENCHMARK(5000000,LOG2(2))
+----------------------------+
| BENCHMARK(5000000,LOG2(2)) |
+----------------------------+
| 0 |
+----------------------------+
1 row in set (3.46 sec)

mysql> UPDATE t1 SET id=11 WHERE id=11
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

mysql> SELECT * FROM t1
+------+----------------+
| id | tstamp |
+------+----------------+
| 11 | 20040915213930 |
| 12 | 20040915213930 |
+------+----------------+
2 rows in set (0.00 sec)
--------------------------------------------

I don't see any updated timestamp in id=11 row.
That's correct behaviour,
as long as we define that "update attempt" means different then "updated".
It would be really bad, if trigger would fire on any attempt
of update instead actual change of value.

Back again,

> *should* probably be changed when you do the update

in this case there is no difference between "updated"
and "matched" rows *anyway*: since 1 column (timestamp)
forced to update always, "matched rows" and "updated rows"
counts will be always the same, and whole subject our
discussion will not have a ground.

On the other hands, in more natural case, when timestamp
need to be updated *only if* any other columns updated,
hiding "updated" count seems to be a problem.

Good designed apps requires to use "updated" count.

In oracle, for example,
it's real pain (if even possible) to define trigger firing on
"update timestamp only if any of other columns were really updated".
If it gets defined, it conflicts with explicit set value.

In mysql such behaviour is set by default.

What is the reason push mysql behave badly way?
Mysql (so far) is able to fire trigger only if real updates made,
and still being usable for event-driven applications.

Compatibility is important, but why not with mysql? :)

Wherever we do bulk insert or massive replace,
it's necessary to work around oracle weakness anyway.
AFAIK oracle can not
- REPLACE
- bulk INSERT

How mysql API can be compatible with something does not exist?
How perl application can be designed portable way here,
other then having separate logic blocks for every database type?

Oracle's "affected rows" won't care about actual updates:
--------------------------------------------
w@kane:~>yasql --host=kane --sid=test -q
@test!kane> DROP TABLE t1;
0 rows affected (0.11 seconds)

@test!kane> CREATE TABLE t1 (id NUMBER , txt VARCHAR2(2) );
0 rows affected (0.03 seconds)

@test!kane> INSERT INTO t1 (id,txt) VALUES (11,11);
1 row affected (0.01 seconds)

@test!kane> INSERT INTO t1 (id,txt) VALUES (12,12);
1 row affected (0.00 seconds)

@test!kane> SELECT * FROM t1;
ID TXT
---- ----
11 11
12 12
2 rows selected (0.01 seconds)

@test!kane> UPDATE t1 SET txt=12 WHERE id=12;
1 row affected (0.02 seconds)
--------------------------------------------
Oracle returns just matched rows as "affected",
even if no real affects made.

Bulk INSERT and REPLACE are missing in ora9i:
--------------------------------------------
@test!kane> INSERT INTO t1 (id,txt) VALUES (14,14), (15,15), (16,16);
ORA-00933: SQL command not properly ended (DBD ERROR: error possibly near <*> indicator at char 38 in 'INSERT INTO t1 (id,txt) VALUES (14,14)<*>, (15,15), (16,16)')
Query: INSERT INTO t1 (id,txt) VALUES (14,14), (15,15), (16,16)

@test!kane> REPLACE INTO t1 (id,txt) VALUES (12,12);
ORA-00900: invalid SQL statement
Query: REPLACE INTO t1 (id,txt) VALUES (12,12)
--------------------------------------------

So it's clear for oracle here is "nothing to count about".
Here we [mysql] go, our own [incompatible] way.
;)


Regards,
--w

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: update query returns wrong number of rows affected

Rudy, --

On Wed, Sep 15, 2004 at 06:24:07PM -0700, Dave Dyer wrote:
> > >>and to me "affected rows" means changed rows.
> > For one, it's reliable to say "change this and tell me
> > how many changed" it's not reliable to say "if I changed this, how many
> > would change" and then say "ok do it" and expect the number of changed
> > records to be the same.

I would add "reliable" here means lack of race conditions.
For those of us who care about consistency of apps based on
non-transactional engine, it makes lots of sense.
Mysql provides such info, so it should not be an issue for perl layer.

> > >Dave Dyer wrote:
> > >>Maybe it's always been this way, but the C api makes the number
> > >>of changed rows easily available, I find it very useful.

No, that's DBD has been changed at some point,
for compatibility with oracle and other DBD-s.

mysql_client_found_rows() can alter this behavior,
however to me something like $sth->affected_rows()
would be more convenient.

On Wed, Sep 15, 2004 at 09:51:24PM -0400, Rudy Lippan wrote:
> eval {
> $dbh->begin_work();
> my $count = $do_count->execute();
> $do_update->execute(); # this better not be updateing a different set
> # Assuming ANSI default transaction isolation level.
> $dbh->commit();
> }; if (my $e = $@) {
> eval {$dbh->rollback()};
> die die die "I am aweary, aweary, / Oh God, that I were dead! $e";
> }

Use transaction just for return updated rows count? Wow.
Why don't just implement something like $sth->updated_rows() instead?
Why switch over to InnoDB at all, if apps is happy with MyISAM?
Pretty strong design decision...
I guess it was a joke.

> And think of it from the point of a record that had a last-updated-time that
> *should* probably be changed when you do the update and that would cause
> thousands of extra timestamp updates that you don't need, or think of it with
> update triggers in mind they would have to fire, right? So then
> you would have a metric boatlaod of extra trigers that could or could not be
> updating something.

Yes, if apps *must do so* (update timestamp) always, it makes sense.

Assumption here is mysql does timestamp update always,
even if engine detected no actual updates made
in other [then timestamp] columns.
Is it really so?
Let's see...
--------------------------------------------
w@kane:~>mysql -hkane test
Your MySQL connection id is 11244 to server version: 4.0.20-standard-log
mysql> DROP TABLE IF EXISTS t1
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (id INT , txt TIMESTAMP )
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO t1 (id) VALUES (11)
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 (id) VALUES (12)
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1
+------+----------------+
| id | tstamp |
+------+----------------+
| 11 | 20040915213930 |
| 12 | 20040915213930 |
+------+----------------+
2 rows in set (0.00 sec)

mysql> SELECT BENCHMARK(5000000,LOG2(2))
+----------------------------+
| BENCHMARK(5000000,LOG2(2)) |
+----------------------------+
| 0 |
+----------------------------+
1 row in set (3.46 sec)

mysql> UPDATE t1 SET id=11 WHERE id=11
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

mysql> SELECT * FROM t1
+------+----------------+
| id | tstamp |
+------+----------------+
| 11 | 20040915213930 |
| 12 | 20040915213930 |
+------+----------------+
2 rows in set (0.00 sec)
--------------------------------------------

I don't see any updated timestamp in id=11 row.
That's correct behaviour,
as long as we define that "update attempt" means different then "updated".
It would be really bad, if trigger would fire on any attempt
of update instead actual change of value.

Back again,

> *should* probably be changed when you do the update

in this case there is no difference between "updated"
and "matched" rows *anyway*: since 1 column (timestamp)
forced to update always, "matched rows" and "updated rows"
counts will be always the same, and whole subject our
discussion will not have a ground.

On the other hands, in more natural case, when timestamp
need to be updated *only if* any other columns updated,
hiding "updated" count seems to be a problem.

Good designed apps requires to use "updated" count.

In oracle, for example,
it's real pain (if even possible) to define trigger firing on
"update timestamp only if any of other columns were really updated".
If it gets defined, it conflicts with explicit set value.

In mysql such behaviour is set by default.

What is the reason push mysql behave badly way?
Mysql (so far) is able to fire trigger only if real updates made,
and still being usable for event-driven applications.

Compatibility is important, but why not with mysql? :)

Wherever we do bulk insert or massive replace,
it's necessary to work around oracle weakness anyway.
AFAIK oracle can not
- REPLACE
- bulk INSERT

How mysql API can be compatible with something does not exist?
How perl application can be designed portable way here,
other then having separate logic blocks for every database type?

Oracle's "affected rows" won't care about actual updates:
--------------------------------------------
w@kane:~>yasql --host=kane --sid=test -q
@test!kane> DROP TABLE t1;
0 rows affected (0.11 seconds)

@test!kane> CREATE TABLE t1 (id NUMBER , txt VARCHAR2(2) );
0 rows affected (0.03 seconds)

@test!kane> INSERT INTO t1 (id,txt) VALUES (11,11);
1 row affected (0.01 seconds)

@test!kane> INSERT INTO t1 (id,txt) VALUES (12,12);
1 row affected (0.00 seconds)

@test!kane> SELECT * FROM t1;
ID TXT
---- ----
11 11
12 12
2 rows selected (0.01 seconds)

@test!kane> UPDATE t1 SET txt=12 WHERE id=12;
1 row affected (0.02 seconds)
--------------------------------------------
Oracle returns just matched rows as "affected",
even if no real affects made.

Bulk INSERT and REPLACE are missing in ora9i:
--------------------------------------------
@test!kane> INSERT INTO t1 (id,txt) VALUES (14,14), (15,15), (16,16);
ORA-00933: SQL command not properly ended (DBD ERROR: error possibly near <*> indicator at char 38 in 'INSERT INTO t1 (id,txt) VALUES (14,14)<*>, (15,15), (16,16)')
Query: INSERT INTO t1 (id,txt) VALUES (14,14), (15,15), (16,16)

@test!kane> REPLACE INTO t1 (id,txt) VALUES (12,12);
ORA-00900: invalid SQL statement
Query: REPLACE INTO t1 (id,txt) VALUES (12,12)
--------------------------------------------

So it's clear for oracle here is "nothing to count about".
Here we [mysql] go, our own [incompatible] way.
;)


Regards,
--w

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org