MySQL Bugs: #36845: Partition with composite keys and joins

Bug #36845	Partition with composite keys and joins
Submitted:	21 May 2008 8:05	Modified:	27 Jun 2008 16:37
Reporter:	Tofeeq Ali	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.1.23-rc-community	OS:	Linux (2.6.9-67.0.4.ELsmp #1 SMP Sun Feb 3 07:06:14 EST 2008 x86_64 x86_64 x86_64 GNU/Linux)
Assigned to:		CPU Architecture:	Any
Tags:	partition by range

Description:
#This table stores million of subscriber records

CREATE TABLE `subscribers` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `responderId` int(10) unsigned NOT NULL DEFAULT '0',
 `email` varchar(75) NOT NULL DEFAULT '',
 `firstName` varchar(75) NOT NULL DEFAULT '',
 `lastName` varchar(75) NOT NULL DEFAULT '',
 `status` tinyint(2) NOT NULL DEFAULT '0',
 `address` varchar(100) NOT NULL DEFAULT '',
 `address2` varchar(100) NOT NULL DEFAULT '',
 `city` varchar(75) NOT NULL DEFAULT '',
 `state` varchar(20) NOT NULL DEFAULT '',
 `zip` varchar(10) NOT NULL DEFAULT '',
 `phone` varchar(15) NOT NULL DEFAULT '',
 `fax` varchar(15) NOT NULL DEFAULT '',
 `country` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`,`responderId`),
 UNIQUE KEY `responderId` (`responderId`,`email`),
 KEY `email` (`email`),
 KEY `status` (`status`)
) ENGINE=MyISAM AUTO_INCREMENT=2097145 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (responderId) (PARTITION p0 VALUES LESS THAN (4) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (7) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (10) ENGINE = MyISAM, PARTITION p3 VALUES LESS THAN (13) ENGINE = MyISAM, PARTITION p4 VALUES LESS THAN (16) ENGINE = MyISAM, PARTITION p5 VALUES LESS THAN (19) ENGINE = MyISAM, PARTITION p6 VALUES LESS THAN (22) ENGINE = MyISAM, PARTITION p7 VALUES LESS THAN (25) ENGINE = MyISAM, PARTITION p8 VALUES LESS THAN (28) ENGINE = MyISAM, PARTITION p9 VALUES LESS THAN (31) ENGINE = MyISAM, PARTITION p10 VALUES LESS THAN (34) ENGINE = MyISAM, PARTITION p11 VALUES LESS THAN (37) ENGINE = MyISAM, PARTITION p12 VALUES LESS THAN (40) ENGINE = MyISAM, PARTITION p13 VALUES LESS THAN (43) ENGINE = MyISAM, PARTITION p14 VALUES LESS THAN (46) ENGINE = MyISAM, PARTITION p15 VALUES LESS THAN (49) ENGINE = MyISAM, PARTITION p16 VALUES LESS THAN (52) ENGINE = MyISAM, PARTITION p17 VALUES LESS THAN (55) ENGINE = MyISAM, PARTITION p18 VALUES LESS THAN (58) ENGINE = MyISAM, PARTITION p19 VALUES LESS THAN (61) ENGINE = MyISAM, PARTITION p20 VALUES LESS THAN (64) ENGINE = MyISAM, PARTITION p21 VALUES LESS THAN (67) ENGINE = MyISAM, PARTITION p22 VALUES LESS THAN (70) ENGINE = MyISAM, PARTITION p23 VALUES LESS THAN (73) ENGINE = MyISAM, PARTITION p24 VALUES LESS THAN (76) ENGINE = MyISAM, PARTITION p25 VALUES LESS THAN (79) ENGINE = MyISAM, PARTITION p26 VALUES LESS THAN (82) ENGINE = MyISAM, PARTITION p27 VALUES LESS THAN (85) ENGINE = MyISAM, PARTITION p28 VALUES LESS THAN (88) ENGINE = MyISAM, PARTITION p29 VALUES LESS THAN (91) ENGINE = MyISAM, PARTITION p30 VALUES LESS THAN (94) ENGINE = MyISAM, PARTITION p31 VALUES LESS THAN (97) ENGINE = MyISAM, PARTITION p32 VALUES LESS THAN (100) ENGINE = MyISAM, PARTITION p33 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */

#This table stores lists that is related to messages and subscribers
#id field in this table is foriegn key in subscribers table i.e. responderId

CREATE TABLE `lists` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL DEFAULT '',
  `fromName` varchar(50) NOT NULL DEFAULT '',
  `fromEmail` varchar(50) NOT NULL DEFAULT '',
  `status` tinyint(4) NOT NULL DEFAULT '0',
  `priority` tinyint(3) NOT NULL DEFAULT '10',
  PRIMARY KEY (`id`),
  UNIQUE KEY `title` (`title`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=93 ;

# This table stores messages
# id of lists table is stored as a forieng key i.e responderId

CREATE TABLE `Messages` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `responderId` int(10) unsigned NOT NULL DEFAULT '0',
  `subject` varchar(100) NOT NULL DEFAULT '',
  `messageText` text NOT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx1` (`responderId`),
  KEY `status` (`status`),
  KEY `dateAdded` (`dateAdded`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12173 ;

# This table is populated when a new message is sent to a list and records are removed when message are sent
# pId is primary key of subscribers table
# mId is primary key of Messages table

CREATE TABLE `TempMessages` (
  `pId` bigint(20) unsigned NOT NULL DEFAULT '0',
  `mId` int(10) unsigned NOT NULL DEFAULT '0',
  `status` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`pId`,`mId`),
  KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Now when I execute a query that joins subscribers and TempMessages table and use responderId in where clause, for some lists it gives empty results while works for some others. Here is the query that is issue

select p.id,p.email,p.firstName,p.lastName,p.address,p.address2,
p.city,p.state,p.zip,p.phone,p.fax, p.responderId,
p.status from subscribers p inner join
TempMessages temp on p.id=temp.pId and
temp.status=0 and p.id>0 and p.status < 2 and temp.mId=12165 order by p.id

This works fine but when I add "and p.responderId = 7" before order by to optimize the query, i get no results although in my previous query value for responderId in resultset is 7.

Now adding "and p.responderId = [some responderid]" works for few numbers e.g. 9 16, 84, 85, 90, 92 but doesn't work with some e.g. 7, 10, 11, 12, 20, 25, 26, 27, 65.

Now I don't know if it is a bug or some problem with my scheme but if it is a problem with schema then it should not work on some instances that it is doing.

How to repeat:
The bug can be repeated by created above structure and putting records in the above schema.

Thank you for the report.

I can not repeat described behavior with test data. Please try with current version 5.1.24 and if problem still exists provide output of 

explain partitions select p.id,p.email,p.firstName,p.lastName,p.address,p.address2,
p.city,p.state,p.zip,p.phone,p.fax, p.responderId as 'p_responderId',
p.status from subscribers p inner join
TempMessages temp on p.id=temp.pId and
temp.status=0 and p.id>0 and p.status < 2 and temp.mId=12165 order by p.id;

explain partitions select p.id,p.email,p.firstName,p.lastName,p.address,p.address2,
p.city,p.state,p.zip,p.phone,p.fax, p.responderId as 'p_responderId',
p.status from subscribers p inner join
TempMessages temp on p.id=temp.pId and
temp.status=0 and p.id>0 and p.status < 2 and temp.mId=12165 and p.responderId=7 order by p.id;

Thank you Sveeta,

Here are results for Explain Partitions

explain partitions select
p.id,p.email,p.firstName,p.lastName,p.address,p.address2,
p.city,p.state,p.zip,p.phone,p.fax, p.responderId as 'p_responderId',
p.status from subscribers p inner join
TempMessages temp on p.id=temp.pId and
temp.status=0 and p.id>0 and p.status < 2 and temp.mId=12165 order by
p.id;

id 	select_type 	table 	partitions 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	temp 	NULL 	range 	PRIMARY,status 	status 	1 	NULL 	566184 	Using where; Using temporary; Using filesort
1 	SIMPLE 	p 	p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31,p32,p33 	ref 	PRIMARY,status 	PRIMARY 	4 	newufa.temp.pId 	1 	Using where

and with responderid

explain partitions select
p.id,p.email,p.firstName,p.lastName,p.address,p.address2,
p.city,p.state,p.zip,p.phone,p.fax, p.responderId as 'p_responderId',
p.status from subscribers p inner join
TempMessages temp on p.id=temp.pId and
temp.status=0 and p.id>0 and p.status < 2 and temp.mId=12165 and
p.responderId=7 order by p.id;

id 	select_type 	table 	partitions 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	p 	p1,p2 	ref 	PRIMARY,responderId,status 	responderId 	4 	const 	114424 	Using where
1 	SIMPLE 	temp 	NULL 	eq_ref 	PRIMARY,status 	PRIMARY 	12 	newufa.p.id,const 	1 	Using where

Another thing to note is that responderId 7 Falls between two partitions but I also get an empty result for responderId 12 that falls in partition 3. Here are its explain partitions results

explain partitions select
p.id,p.email,p.firstName,p.lastName,p.address,p.address2,
p.city,p.state,p.zip,p.phone,p.fax, p.responderId as 'p_responderId',
p.status from subscribers p inner join
TempMessages temp on p.id=temp.pId and
temp.status=0 and p.id>0 and p.status < 2 and temp.mId=12170 and
p.responderId=12 order by p.id;

id 	select_type 	table 	partitions 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	p 	p3 	ref 	PRIMARY,responderId,status 	responderId 	4 	const 	361650 	Using where
1 	SIMPLE 	temp 	NULL 	eq_ref 	PRIMARY,status 	PRIMARY 	12 	newufa.p.id,const 	1 	Using where

Thank you for the feedback. It is possible for you to provide the data dump file?
Thanks in advance.

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".