12 messages in com.mysql.lists.perlRe: Drivers giving different results
FromSent OnAttachments
Jochen Wiedmann25 May 1999 11:10 
Vivek Khera25 May 1999 12:01 
Jochen Wiedmann25 May 1999 13:18 
Tim Bunce25 May 1999 15:10 
Dan Busarow28 May 1999 14:25 
Jochen Wiedmann29 May 1999 01:36 
Michael Widenius31 May 1999 03:25 
Tim Bunce02 Jun 1999 03:04 
Michael Widenius02 Jun 1999 13:52 
Tim Bunce02 Jun 1999 19:24 
Michael Widenius03 Jun 1999 06:27 
Tim Bunce03 Jun 1999 07:15 
Subject:Re: Drivers giving different results
From:Michael Widenius (mon@monty.pp.sci.fi)
Date:06/02/1999 01:52:18 PM
List:com.mysql.lists.perl

"Tim" == Tim Bunce <Tim.@ig.co.uk> 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 :)