List:MySQL and Perl« Previous MessageNext Message »
From:Michael Widenius Date:June 2 1999 8:52pm
Subject:Re: Drivers giving different results
View as plain text  
>>>>> "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.

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).

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?

My point is that I don't think that database independent clients
should rely on anything that may be database or version dependent or,
even worse, only work for some cases (even if the normal case works).

Regards,
Monty

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 :)
    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 :)
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