MySQL Bugs: #21019: First result of SELECT COUNT(*) different than consecutive runs

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	CPU Architecture:	Any

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.

Verified using MySQL 5.0.22 on MacOSX and 5.1bk on Red Hat (64bit).

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

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

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

Documented bugfix in 4.1.22/5.0.25/5.1.12 changelogs.

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