MySQL Bugs: #64126: SELECT results vary with range v IN()

Bug #64126	SELECT results vary with range v IN()
Submitted:	25 Jan 2012 8:37	Modified:	25 Feb 2012 9:23
Reporter:	Jim Morrison	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.5.17	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Optimizer, Partition pruning, partitioning

Description:
I am getting a very strange response from a very simple query on a big, partitioned table. I'm sure this must be a bug in the partition handling or I'm going mad. 

SELECT * FROM logtable WHERE log_day BETWEEN 10 AND 20 AND log_item = 1234; 
// Returns 5 results (yikes!?) 

SELECT * FROM logtable WHERE log_day IN (10,11,12 .. 18,19) AND log_item = 1234; 
// Returns 10 results (correct!) 

The server info is: 

| version | 5.5.17-log | 
| version_comment | MySQL Community Server (GPL) | 
| version_compile_machine | x86_64 | 
| version_compile_os | Linux | 

The table is MyISAM; with one index: PRIMARY KEY(log_item, log_day) 

It's partitioned: PARTITION BY LINEAR KEY () PARTITIONS 256 

It has 360 million rows. 

How to repeat:
CREATE TABLE logtable (
   log_day int unsigned NOT NULL,
   log_item int unsigned NOT NULL,
   PRIMARY KEY( log_day, log_item)
) ENGINE=MyISAM PARTITION BY LINEAR KEY () PARTITIONS 256;

Fill with a few million rows ( 360M definitely breaks )...

Suggested fix:
Appears to be a bug in partition pruning since EXPLAIN PARTITIONS should contain 4x partitions but in the BETWEEN type statement EXPLAIN PARTITIONS only lists 1x partition.

NB.  Works in 5.1 so broken since then.

Please, check if this still happens with a recent version, 5.5.20. If it does, them small (if possible) data set (with no sensitive data) to demonstrate it will really help.

Thanks Valeriy.  We'll upgrade the dev server later this week and organise a demo dataset.

Thanks Valeriy.  We'll upgrade the dev server later this week and organise a demo dataset.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".