List:MySQL and Perl« Previous MessageNext Message »
From:Tim Bunce Date:May 25 1999 10:11pm
Subject:Re: Drivers giving different results
View as plain text  
On Tue, May 25, 1999 at 08:10:26PM +0200, Jochen Wiedmann wrote:
> 
> Hi, Monty,
> 
> I recently had a discussion with some DBD::mysql users, in particular
> Tim Bunce the DBI maintainer, about the following:
> 
> If I do a
> 
> 	UPDATE foo SET col=col
> 
> then the MySQL optimizer detects that no work needs to be done, which
> is of course a good thing.

That's not a good example because it clouds the issue.
Here's a better one:

	UPDATE hour_status SET status = 'Y' WHERE date = '...'

In a system where the application knows that there should be 24 records
updated.

In this scenario an application that takes it's error checking seriously
would check that the row count returned was 24.

The problem is the MySQL *may* return less than 24 if one or more
rows already had status = 'Y'. It may even return 0!

That is highly non-standard behaviour. I note that your documentation
says that you follow "Entry level SQL92. ODBC level 0-2". I'm not aware
of any other SQL RDBMS, standard or otherwise, that has this behaviour.
Not Oracle, Informix, Sybase/SQLServer, Ingres etc etc.

It even causes problems with your own documentation:

: 19.4.1 mysql_affected_rows()
:
: my_ulonglong mysql_affected_rows(MYSQL *mysql)
:
: 19.4.1.1 Description
:
: Returns the number of rows affected (changed) by the last UPDATE,
: DELETE or INSERT query. May be called immediately after mysql_query()
: for UPDATE, DELETE or INSERT statements. For SELECT statements,
: mysql_affected_rows() works like mysql_num_rows().
:
: mysql_affected_rows() is currently implemented as a macro.
:
: 19.4.1.2 Return values
:
: An integer greater than zero indicates the number of rows affected or
: retrieved. Zero indicates that no records matched the WHERE clause in
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Not true.

> The problem is that mysql_affected_rows
> seems to return the value 0, in other words the number of *physically*
> affected rows. This seems to break some peoples reliabiliy checks, which
> expects the number of rows being returned.

... the number of rows matched ...

> On the other hand the MySQL server obviously knows the number of matched
> rows:
> 
> 	mysql> update user set Host=Host;
> 	Query OK, 0 rows affected (0.00 sec)
> 	Rows matched: 11  Changed: 0  Warnings: 0
> 
> It seems this information is now returned by mysql_info(). (I didn't
> verify.)
> 
> I am now asked to change the DBD::mysql driver not to return the result
> of mysql_affected_rows(), but read mysql_info() and parse the output to
> return the matched rows. This is something I definitely don't like,
> because it is slow and unsafe.
> 
> My question is whether you can either
> 
>   - supply the same info via a new function, mysql_matched_rows(), say or

That's the preferred option.

>   - change mysql_affected_rows to return the number of matched rows

That can be made more compatible / flexible by enabling the behaviour
change via an extra option to mysql_options().

I see from the docs you already have an option to "Tell mysql_info() to
return found rows instead of updated rows when using UPDATE." So you've
already come across this issue before (the changing the textual mysql_info
isn't very helpful from an application compatibility point of view).

Tim.
Thread
Re: Drivers giving different resultsJochen Wiedmann25 May
  • Re: Drivers giving different resultsVivek Khera25 May
  • Re: Drivers giving different resultsTim Bunce26 May
  • Re: Drivers giving different resultsDan Busarow29 May
    • Re: Drivers giving different resultsJochen Wiedmann29 May
  • Re: Drivers giving different resultsMichael Widenius2 Jun
    • Re: Drivers giving different resultsTim Bunce2 Jun
      • Re: Drivers giving different resultsMichael Widenius3 Jun
        • Re: Drivers giving different resultsTim Bunce3 Jun
          • Re: Drivers giving different resultsMichael Widenius3 Jun
            • Re: Drivers giving different resultsTim Bunce3 Jun
Re: Drivers giving different resultsJochen Wiedmann26 May