List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:June 3 1999 1:28pm
Subject:Re: Drivers giving different results
View as plain text  
<cut>

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

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

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

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

Tim> The problem is that MySQL may return 0 if the row just happens to
Tim> 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).

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

Yes, it is!  In the case:

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

If you have a key on 'status' and you don't have a single 'Y' in the
table, the optimizer could very easily detect this and ignore the
update completely.

I can however understand that some applications may want to check
the result from the above query (Thats why I added CLIENT_FOUND_ROWS
in the first place).

On the other hand, I can also think of applications that really want
to know if the status really did change (as this may indicate that
some other client did change the status first).  This is actually VERY 
useful and it's a pity that most other databases doesn't support this!

>> 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?

Tim> Nope. Those I've seen just use the term "affected" without clarifying
Tim> 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 :)

Tim> For the scenario I described above, I disagree.

See above; I think its depends on what the applications does.  The only
thing I can see that speaks for your case it's that is more common,
but I wouldn't say that it's better :)

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

Tim> That's up to Jochen.

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

I agree.

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

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

I agree that the above is very sad!  It will only work if you are
using MySQL with the English language file!

It should be:

$ret = 1 unless $affected == 0 and $stmt->{info} =~ /^[^:]: 0 /;

Regards,
Monty
Thread
Re: Drivers giving different resultsJochen Wiedmann26 May