MySQL Lists are EOL. Please join:

List:MySQL and Perl« Previous MessageNext Message »
From:Tim Bunce Date:June 3 1999 2:25am
Subject:Re: Drivers giving different results
View as plain text  
On Wed, Jun 02, 1999 at 11:52:40PM +0300, Michael Widenius wrote:
> >>>>> "Tim" == Tim Bunce <Tim.Bunce@stripped> writes:
> 
> Tim> On Mon, May 31, 1999 at 12:25:49PM +0200, Michael Widenius wrote:
> >> 
> >> Hi!
> >> 
> >> You can currently get the above behaviour if you add the flag
> >> CLIENT_FOUND_ROWS to the last argument of mysql_real_connect().
> >> 
> >> One problem is that ANY SQL server is allowed to optimize the query:
> >> 
> >> UPDATE foo SET col=col
> >> to
> >> UPDATE foo SET col=col WHERE col<>col
> >> 
> >> I think that one should strongly discourage the use of affected rows in the
> >> above manner as any SQL server may add the above optimisation in any
> >> updated version!
> 
> Sorry, the above should of course be:
> 
> UPDATE foo SET col=#constant#
> 
> to
> 
> UPDATE foo SET col=#constant# WHERE col=#constant#
> 
> 
> Tim> What makes you say that? Specifically. Is it specified somewhere in
> Tim> a standards document somewhere? (The word "affected" is, sadly, rather
> Tim> too ambiguous to be relied upon by itself.)
> 
> Tim> I know of no other SQL server that does that. Do you?
> 
> No, I don't know of any SQL server that does the above optimization,
> (MySQL only optimizes when to update a row), but it doesn't mean that
> they don't do it in some context!  I will probably someday refine the
> MySQL update optimizer to do the above optimization when updating key
> columns and when I do this, all clients that sets CLIENT_FOUND_ROWS
> will then be MUCH slower for some updates.

I'm actually not much bothered about that case. Such updates are rare.
I'd also guess that some database may well optimise out such pointless
no-change-key-updates.

I'm much more concerned about this kind of scenario:

        UPDATE table SET status = 'Y' WHERE unique_key = ...

An application that takes it's error checking seriously would check
that the row count returned was 1 and regard 0 as an error implying
that the row does not exist.

The problem is that MySQL may return 0 if the row just happens to
already have status = 'Y'.

> They problem is the SQL standard doesn't specify how a SQL server
> resolves a query, only the result of it.  Testing this is non trivial
> as it depends on how good the SQL optimizer is and when it does this
> optimization. (The above optimization should only be done when
> updating an index column on a large table where there are very
> different values, which makes it a bit harder to test).

The scenario I'm concerned about isn't affected by SQL optimizer issues.

> I don't have manuals over the different API:s for the different
> database vendors.  Have you checked if they claim that the will always
> return how many rows that matched the WHERE clause in all future version?

Nope. Those I've seen just use the term "affected" without clarifying
it (logically affected vs physically affected).

> PS: I think that, in many cases, the result MySQL returns as
>     'affected' rows is actually more useful than the number of rows
>     that matched the WHERE :)

For the scenario I described above, I disagree.

>     I don't know if the suggested change is going to break many
>     already working MySQL applications, but I hope you are going to
>     document the use of CLIENT_FOUND_ROWS very clearly to not affect
>     the number of mails to 'msql-mysql-modules' too much :)

That's up to Jochen.

Assuming it's made an option then the fix for the rare soul who
actually wants a 'physically modified' count will be trivial.

Meanwhile, I'll leave you with this rather sad bit of code I found in
the Catalog.pm support modules:

	my $affected = $stmt->execute() or ...
	#
	# Return 1 if update occured at least on a row, even
	# if nothing was modified in that row.
	#
	$ret = 1 unless $affected == 0 and $stmt->{info} =~ /matched: 0 /;

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