| Bug #23457 | a problem about a partition table | ||
|---|---|---|---|
| Submitted: | 19 Oct 2006 8:41 | Modified: | 21 Oct 2006 2:42 |
| Reporter: | fu yang | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.1.11 | OS: | Windows (windows) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | partition table, primary key, query | ||
[19 Oct 2006 10:33]
Hartmut Holzgraefe
looks as if your report was truncated somehow, the final SELECT statement ends after the ORDER keyword could you check the report and add the missing pieces?
[20 Oct 2006 2:41]
fu yang
select * from table1 where cTime between '2006-04-08 00:00:00' and '2006-04-10 23:59:59' order by cTime desc,dataId desc;
[21 Oct 2006 2:42]
MySQL Verification Team
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.
If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at
http://dev.mysql.com/doc/en/installing-source.html
mysql> select * from table1 where cTime between '2006-04-08 00:00:00' and '2006-04-10
'> 23:59:59' order by cTime desc,dataId desc;
+--------+-----------+---------------------+------------+-------------+-------------+----------------+
| dataId | deviceNum | cTime | account | userName | password | sendAddress |
+--------+-----------+---------------------+------------+-------------+-------------+----------------+
| 4 | 1201 | 2006-04-08 00:13:10 | account1 | userName1 | password1 | sendAddress1 |
| 3 | 1201 | 2006-04-08 00:12:10 | account1 | userName1 | password1 | sendAddress1 |
| 2 | 1201 | 2006-04-08 00:11:10 | account1 | userName1 | password1 | sendAddress1 |
| 1 | 1201 | 2006-04-08 00:10:10 | account1 | userName1 | password1 | sendAddress1 |
| 8 | 1201 | 2006-04-09 00:13:10 | account91 | userName91 | password91 | sendAddress91 |
| 7 | 1201 | 2006-04-09 00:12:10 | account91 | userName91 | password91 | sendAddress91 |
| 6 | 1201 | 2006-04-09 00:11:10 | account91 | userName91 | password91 | sendAddress91 |
| 5 | 1201 | 2006-04-09 00:10:10 | account91 | userName91 | password91 | sendAddress91 |
| 12 | 1201 | 2006-04-10 00:13:10 | account101 | userName101 | password101 | sendAddress101 |
| 11 | 1201 | 2006-04-10 00:12:10 | account101 | userName101 | password101 | sendAddress101 |
| 10 | 1201 | 2006-04-10 00:11:10 | account101 | userName101 | password101 | sendAddress101 |
| 9 | 1201 | 2006-04-10 00:10:10 | account101 | userName101 | password101 | sendAddress101 |
+--------+-----------+---------------------+------------+-------------+-------------+----------------+
12 rows in set (0.09 sec)
mysql> select version();
+-------------+
| version() |
+-------------+
| 5.1.11-beta |
+-------------+
1 row in set (0.00 sec)
-------------------------------------------------------------------------------------------
mysql> select * from table1 where cTime between '2006-04-08 00:00:00' and '2006-04-10
'> 23:59:59' order by cTime desc,dataId desc;
+--------+-----------+---------------------+------------+-------------+-------------+----------------+
| dataId | deviceNum | cTime | account | userName | password | sendAddress |
+--------+-----------+---------------------+------------+-------------+-------------+----------------+
| 12 | 1201 | 2006-04-10 00:13:10 | account101 | userName101 | password101 | sendAddress101 |
| 11 | 1201 | 2006-04-10 00:12:10 | account101 | userName101 | password101 | sendAddress101 |
| 10 | 1201 | 2006-04-10 00:11:10 | account101 | userName101 | password101 | sendAddress101 |
| 9 | 1201 | 2006-04-10 00:10:10 | account101 | userName101 | password101 | sendAddress101 |
| 8 | 1201 | 2006-04-09 00:13:10 | account91 | userName91 | password91 | sendAddress91 |
| 7 | 1201 | 2006-04-09 00:12:10 | account91 | userName91 | password91 | sendAddress91 |
| 6 | 1201 | 2006-04-09 00:11:10 | account91 | userName91 | password91 | sendAddress91 |
| 5 | 1201 | 2006-04-09 00:10:10 | account91 | userName91 | password91 | sendAddress91 |
| 4 | 1201 | 2006-04-08 00:13:10 | account1 | userName1 | password1 | sendAddress1 |
| 3 | 1201 | 2006-04-08 00:12:10 | account1 | userName1 | password1 | sendAddress1 |
| 2 | 1201 | 2006-04-08 00:11:10 | account1 | userName1 | password1 | sendAddress1 |
| 1 | 1201 | 2006-04-08 00:10:10 | account1 | userName1 | password1 | sendAddress1 |
+--------+-----------+---------------------+------------+-------------+-------------+----------------+
12 rows in set (0.03 sec)
mysql> select version();
+----------------+
| version() |
+----------------+
| 5.1.13-beta-nt |
+----------------+
1 row in set (0.00 sec)
mysql>
Description: Create a partition table included primary key, when you run a query and order the result with 'desc', you will get the wrong result. Sometimes, the mysql server maybe down. How to repeat: CREATE TABLE `table1` ( `dataId` bigint NOT NULL, `deviceNum` bigint NOT NULL, `cTime` datetime NOT NULL, `account` varchar(60) default ' ', `userName` varchar(60) default ' ', `password` varchar(40) default ' ', `sendAddress` varchar(250) default ' ', PRIMARY KEY (`cTime`,`dataId`), KEY `dataId` (`dataId`), KEY `userName` (`userName`), KEY `password` (`password`), KEY `sendAddress` (`sendAddress`) ) ENGINE=InnoDB DEFAULT CHARSET=UTF8 PARTITION BY RANGE (TO_DAYS(cTime)) ( PARTITION P000100 VALUES LESS THAN (TO_DAYS('2000-01-01')) ); alter table table1 add partition ( partition P060408 values less than (TO_DAYS('2006-04-09')) ); alter table table1 add partition ( partition P060409 values less than (TO_DAYS('2006-04-10')) ); alter table table1 add partition ( partition P060410 values less than (TO_DAYS('2006-04-11')) ); insert into table1 values(1,1201,'2006-04-08 00:10:10','account1','userName1','password1','sendAddress1'); insert into table1 values(2,1201,'2006-04-08 00:11:10','account1','userName1','password1','sendAddress1'); insert into table1 values(3,1201,'2006-04-08 00:12:10','account1','userName1','password1','sendAddress1'); insert into table1 values(4,1201,'2006-04-08 00:13:10','account1','userName1','password1','sendAddress1'); insert into table1 values(5,1201,'2006-04-09 00:10:10','account91','userName91','password91','sendAddress91'); insert into table1 values(6,1201,'2006-04-09 00:11:10','account91','userName91','password91','sendAddress91'); insert into table1 values(7,1201,'2006-04-09 00:12:10','account91','userName91','password91','sendAddress91'); insert into table1 values(8,1201,'2006-04-09 00:13:10','account91','userName91','password91','sendAddress91'); insert into table1 values(9,1201,'2006-04-10 00:10:10','account101','userName101','password101','sendAddress101'); insert into table1 values(10,1201,'2006-04-10 00:11:10','account101','userName101','password101','sendAddress101'); insert into table1 values(11,1201,'2006-04-10 00:12:10','account101','userName101','password101','sendAddress101'); insert into table1 values(12,1201,'2006-04-10 00:13:10','account101','userName101','password101','sendAddress101'); then select * from table1 where cTime between '2006-04-08 00:00:00' and '2006-04-10 23:59:59' order