Bug #21019 First result of SELECT COUNT(*) different than consecutive runs
Submitted: 12 Jul 2006 21:29 Modified: 9 Aug 2006 13:35
Reporter: Geert Vanderkelen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1->5.1 OS:Any
Assigned to: Georgi Kodinov

[12 Jul 2006 21:29] Geert Vanderkelen
Description:
A query doing a join over two MyISAM tables gives the first run a diffent result as the consecutive runs of the same query. The WHERE clause looks a bit weird to me, but never the less, it should be same result at each attempt.

I tried to find what it was but in vain.

It was reported for 4.1 and 5.1 orignally, and I saw same effect using 5.0.

How to repeat:
Query and data is available (privately).

Loading the data from the SQL dump file and then using the query.
[12 Jul 2006 21:39] Geert Vanderkelen
Verified using MySQL 5.0.22 on MacOSX and 5.1bk on Red Hat (64bit).
[24 Jul 2006 15:35] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9504
[26 Jul 2006 10:32] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9577
[2 Aug 2006 18:38] Evgeny Potemkin
When optimizing conditions like 'a = <some_val> OR a IS NULL' so that they're
united into a single condition on the key and checked together the server must 
check which value is the NULL value in a correct way : not only using ->is_null 
but also check if the expression doesn't depend on any tables referenced in the 
current statement. 
This additional check must be performed because that optimization takes place 
before the actual execution of the statement, so if the field was initialized 
to NULL from a previous statement the optimization would be applied incorrectly.

Fixed in 4.1.22, 5.0.25, 5.1.12
[9 Aug 2006 13:35] Jon Stephens
Documented bugfix in 4.1.22/5.0.25/5.1.12 changelogs.
[9 Oct 2006 10:14] Jon Stephens
Changelog entry was missing per IRC discussion with Geert, now documented in 4.1.22/5.0.25/5.1.12 changelogs.

http://lists.mysql.com/commits/13326