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