Re: why did have different result after using partition?

[Mikael Ronström]

Hi,
It seems likely that you have hit a bug so please file a bug report.
Try and see if the select max-part of the query delivers the same
result. This is an area that could be affected by partitioning.

Rgrds Mikael

zhenxing zhai wrote:
> 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????