Bug #15953 why did have different result after using partition?
Submitted: 23 Dec 2005 6:13 Modified: 26 Jan 2006 14:48
Reporter: zhenxing zhai Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.1.3 OS:Linux (linux)
Assigned to: CPU Architecture:Any

[23 Dec 2005 6:13] zhenxing zhai
Description:
I hava two tables,record number as follows: 

mysql> select count(1) from weblogentry; 
+----------+ 
| count(1) | 
+----------+ 
| 1295377 | 
+----------+ 
1 row in set (0.00 sec) 

mysql> select count(1) from rolleruser; 
+----------+ 
| count(1) | 
+----------+ 
| 626007 | 
+----------+ 
1 row in set (0.00 sec) 

I make hash partitions to table weblogentry ,new table named weblogentry_new,record number as follow: 

mysql> select count(1) from weblogentry_new; 
+----------+ 
| count(1) | 
+----------+ 
| 1295377 | 
+----------+ 
1 row in set (0.00 sec) 

now,I have a join query to weblogentry and rolleruser,statment as follows: 

select count(1) from weblogentry e,rolleruser u where e.id>((select max(id) from weblogentry)-100000) and e.publishentry='2' and e.ispass=1 and e.userid=u.id order by e.id desc limit 480; 

the result is: 
mysql> select count(1) from weblogentry e,rolleruser u where e.id>((select max(id) from weblogentry)-100000) and e.publishentry='2' and e.ispass=1 and e.userid=u.id order by e.id desc limit 480; 
+----------+ 
| count(1) | 
+----------+ 
| 269 | 
+----------+ 
1 row in set (0.00 sec) 

replace table weblogentry_new ,the result is: 
mysql> select count(1) from weblogentry_new e,rolleruser u where e.id>((select max(id) from weblogentry_new)-100000) and e.publishentry='2' and e.ispass=1 and e.userid=u.id order by e.id desc limit 480; 
+----------+ 
| count(1) | 
+----------+ 
| 269 | 
+----------+ 
1 row in set (0.00 sec) 

the result is equal,but when I replace count(1) to e.id,e.title,e.publishentry,u.username,u.fullname 

the statment become : 

select e.id,e.title,e.publishentry,u.username,u.fullname from weblogentry e,rolleruser u where e.id>((select max(id) from weblogentry)-100000) and e.publishentry='2' and e.ispass=1 and e.userid=u.id order by e.id desc limit 480; 

mysql> select e.id,e.title,e.publishentry,u.username,u.fullname from weblogentry e,rolleruser u where e.id>((select max(id) from weblogentry)-100000) and e.publishentry='2' and e.ispass=1 and e.userid=u.id order by e.id desc limit 480; 
.... 
269 rows in set (0.01 sec) 

the result num is stilll 269,but replace weblogentry to weblogentry_new: 

mysql> select e.id,e.title,e.publishentry,u.username,u.fullname from weblogentry_new e,rolleruser u where e.id>((select max(id) from weblogentry_new)-100000) and e.publishentry='2' and e.ispass=1 and e.userid=u.id order by e.id desc limit 480; 
..... 
66 rows in set (0.00 sec) 

the result num becomes 66,why????

How to repeat:
create two big tables,one is common table,another is partition table(my example is hash partition),do join query using each them and the third table ,if select columns contain any field of this two tables,the  record rows  number of result will be different

Suggested fix:
however the table is partitioned or isn't partitioned ,the join query result should be equal
[23 Dec 2005 9:33] Valeriy Kravchuk
Thank you for a problem report. Please, send the results of SHOW CREATE TABLE for your weblogentry and weblogentry_new tables. Send also the EXPLAIN results for the (same) queries to the tables giving you different row counts.
[26 Dec 2005 2:48] zhenxing zhai
mysql> show create table weblogentry;
| weblogentry |CREATE TABLE `weblogentry` (
  `ANCHOR` varchar(255) NOT NULL default '',
  `TITLE` varchar(255) NOT NULL default '',
  `pubtime` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `updatetime` timestamp NOT NULL default '0000-00-00 00:00:00',
  `PUBLISHENTRY` tinyint(4) NOT NULL default '0',
  `ALLOWCOMMENTS` tinyint(4) NOT NULL default '0',
  `EXTRATEXT2` varchar(255) default NULL,
  `KEYWORDS` varchar(255) default NULL,
  `ISPASS` tinyint(4) default NULL,
  `id` int(11) NOT NULL auto_increment,
  `WEBSITEID` int(11) default NULL,
  `CATEGORYID` int(11) default NULL,
  `TEXT` text,
  `EXTRATEXT` text,
  `userid` int(11) default '0',
  PRIMARY KEY  (`id`),
  KEY `idx_weblogentry_title` (`TITLE`),
  KEY `idx_weblogentry_publishentry` (`PUBLISHENTRY`),
  KEY `idx_weblogentry_allowcomments` (`ALLOWCOMMENTS`),
  KEY `idx_weblogentry_ispass` (`ISPASS`),
  KEY `idx_weblogentry_websiteid` (`WEBSITEID`),
  KEY `idx_weblogentry_categoryid` (`CATEGORYID`),
  KEY `idx_weblogentry_userid` (`userid`),
  KEY `idx_weblogentry_pub_ispass_id` (`PUBLISHENTRY`,`ISPASS`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk |

1 row in set (0.00 sec)

mysql> show create table weblogentry_new;
| Table           | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |

1 row in set (0.00 sec)

explain plans as follows :

mysql> explain select e.id,e.title,e.publishentry,u.username,u.fullname from weblogentry e,rolleruser u where e.id>((select max(id) from weblogentry)-100000) and e.publishentry='2' and e.ispass=1 and e.userid=u.id order by e.id desc limit 480;
+----+-------------+-------+--------+------------------------------------------------------------------------------------------------------------------+-------------------------------+---------+---------------+------+------------------------------+
| id | select_type | table | type   | possible_keys                                                                                                    | key                           | key_len | ref           | rows | Extra                        |
+----+-------------+-------+--------+------------------------------------------------------------------------------------------------------------------+-------------------------------+---------+---------------+------+------------------------------+
|  1 | PRIMARY     | e     | range  | PRIMARY,idx_weblogentry_publishentry,idx_weblogentry_ispass,idx_weblogentry_userid,idx_weblogentry_pub_ispass_id | idx_weblogentry_pub_ispass_id | 7       | NULL          |  159 | Using where                  |
|  1 | PRIMARY     | u     | eq_ref | PRIMARY                                                                                                          | PRIMARY                       | 4       | riji.e.userid |    1 |                              |
|  2 | SUBQUERY    | NULL  | NULL   | NULL                                                                                                             | NULL                          | NULL    | NULL          | NULL | Select tables optimized away |
+----+-------------+-------+--------+------------------------------------------------------------------------------------------------------------------+-------------------------------+---------+---------------+------+------------------------------+
3 rows in set (0.01 sec)

mysql> explain select e.id,e.title,e.publishentry,u.username,u.fullname from weblogentry_new e,rolleruser u where e.id>((select max(id) from weblogentry_new)-100000) and e.publishentry='2' and e.ispass=1 and e.userid=u.id order by e.id desc limit 480;
+----+-------------+-------+--------+------------------------------------------------------------------------------------------------------------------+-------------------------------+---------+---------------+------+------------------------------+
| id | select_type | table | type   | possible_keys                                                                                                    | key                           | key_len | ref           | rows | Extra                        |
+----+-------------+-------+--------+------------------------------------------------------------------------------------------------------------------+-------------------------------+---------+---------------+------+------------------------------+
|  1 | PRIMARY     | e     | range  | PRIMARY,idx_weblogentry_publishentry,idx_weblogentry_ispass,idx_weblogentry_userid,idx_weblogentry_pub_ispass_id | idx_weblogentry_pub_ispass_id | 7       | NULL          |   81 | Using where                  |
|  1 | PRIMARY     | u     | eq_ref | PRIMARY                                                                                                          | PRIMARY                       | 4       | riji.e.userid |    1 |                              |
|  2 | SUBQUERY    | NULL  | NULL   | NULL                                                                                                             | NULL                          | NULL    | NULL          | NULL | Select tables optimized away |
+----+-------------+-------+--------+------------------------------------------------------------------------------------------------------------------+-------------------------------+---------+---------------+------+------------------------------+
3 rows in set (0.42 sec)

mysql> select count(1) from weblogentry;
+----------+
| count(1) |
+----------+
|  1295377 |
+----------+
1 row in set (0.00 sec)

mysql> select count(1) from weblogentry_new;
+----------+
| count(1) |
+----------+
|  1295377 |
+----------+
1 row in set (0.00 sec)
[26 Dec 2005 14:48] Valeriy Kravchuk
Sorry, but there is yet another table involved in you query, rolleruser. So, I need CREATE TABLE statement for it, as well as for weblogentry_new (because your SHOW CREATE TABLE result for it is empty in previous comment). How exactly is it partitioned?

To summartize: I need all the SQL statements (and data) to repeat the problem. The smallest possible set of data to reproduce it is desirable.
[27 Jan 2006 0:00] Bugs System
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".