MySQL Forums
Forum List  »  Partitioning

Re: select from partitioned table
Posted by: Mikael Ronström
Date: April 05, 2006 12:59PM

Hi,
I'll experiment a bit and see if I can get any similar behaviour.
You can use any SELECT expression on a partitioned table, no
limitations that I'm aware of, it seems like you've hit a bug.

Rgrds Mikael

janota.m wrote:
> Hi,
> I have the following problem:
> I have partitioned table
> CREATE TABLE tdataa_idx1_part (
> tm datetime NOT NULL,
> address char(6) NOT NULL,
> flag char(1),
> val float,
> PRIMARY KEY (address,tm),
> INDEX tm_addr_idx (tm,address)
> ) engine=myisam
> PARTITION BY RANGE ( unix_timestamp(tm) ) (
> PARTITION p_2006031900 VALUES LESS THAN (
> unix_timestamp('2006-03-19 00:00') ),
> PARTITION p_2006031901 VALUES LESS THAN (
> unix_timestamp('2006-03-19 01:00') ),
> PARTITION p_2006031901 VALUES LESS THAN (
> unix_timestamp('2006-03-19 02:00') ),
> .
> .
> .
> PARTITION p_2006032122 VALUES LESS THAN (
> unix_timestamp('2006-03-21 22:00') ),
> PARTITION p_2006032123 VALUES LESS THAN (
> unix_timestamp('2006-03-21 23:00') ),
> PARTITION p_2006032200 VALUES LESS THAN (
> unix_timestamp('2006-03-22 00:00') )
> );
>
> I inserted 48 milion rows with times between
> 2006-03-18 12:00 and 2006-03-21 13:00
>
> Then I created some selects and I got unexpected
> results
>
> SELECT max(tm) from tdataa_idx1_part;
> RESULT: 2006-03-21 12:49:25
>
> SELECT max(tm) from tdataa_idx1_part where tm <
> '2006-03-22';
> RESULT: 2006-03-18 22:59:58
>
> SELECT tm from tdataa_idx1_part order by tm desc
> limit 1;
> RESULT: 2006-03-21 12:49:25
>
> SELECT tm from tdataa_idx1_part where tm <
> '2006-03-22' order by tm desc limit 1;
> RESULT: 2006-03-18 22:59:58
>
>
> SELECT address,tm from tdataa_idx1_part where
> address='A00004' order by tm;
> RESULT 29378 rows
> FIRST tm= 2006-03-18 15:56:34
> LAST tm = 2006-03-21 12:49:25
>
> SELECT address,tm from tdataa_idx1_part whree
> address='A00004' AND tm > '2006-03-20' order by
> tm;
> RESULT 18722 rows
>
> SELECT address,tm from tdataa_idx1_part whree
> address='A00004' AND tm > '2006-03-20' order by
> tm desc;
> RESULT empty
>
> SELECT address,tm from tdataa_idx1_part whree
> address='A00004' AND tm < '2006-03-20' order by
> tm;
> RESULT 18722 rows
>
> SELECT address,tm from tdataa_idx1_part whree
> address='A00004' AND tm < '2006-03-20' order by
> tm desc;
> RESULT empty
>
>
> May I use desc sorting on partitioned table? May I
> use group by on partitioned table?
> Thanks for any sugestions.
> Martin

Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog: http://mikaelronstrom.blogspot.com

Options: ReplyQuote


Subject
Views
Written By
Posted
4165
April 05, 2006 04:02AM
Re: select from partitioned table
2053
April 05, 2006 12:59PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.