Re: not able to create multi column partition keys

[jose PK]

Thanks a lot for your reply..

I can explain my requirement ,by giving one more example

CREATE TABLE t102 ( status INT, last_updated DATETIME )
PARTITION BY LIST COLUMNS ( status ,last_updated )
(
PARTITION p101 VALUES IN ((1, '2001-01-01')) ,
PARTITION p201 VALUES IN ((2, '2001-01-01')) ,
PARTITION p301 VALUES IN ((3, '2001-01-01')) ,

PARTITION p102 VALUES IN ((1, '2001-01-02')) ,
PARTITION p202 VALUES IN ((2, '2001-01-02')) ,
PARTITION p302 VALUES IN ((3, '2001-01-02'))
);


here I have named the partitions with status + day of last_updated column
Following are the records I need to INSERT into this table
status last_updated
** **************
1 '2001-01-01 17:29:21'
2 '2001-01-01 14:36:23'
3 '2001-01-01 01:00:01'

1 '2001-01-02 01:19:22'
2 '2001-01-02 11:16:44'
3 '2001-01-02 11:00:11'




here I want
1st records to go to p101
2nd records to go to p201
3st records to go to p301

4th records to go to p102
5th records to go to p202
6th records to go to p302


Tests conducted :
----------------

test 1:

insert into t102 values (1,'2001-01-01 17:29:21'),(2,'2001-01-01 14:36:23'),(3,'2001-01-01 01:00:01'),(1,'2001-01-02 01:19:22'),(2,'2001-01-02 11:16:44'),(3,'2001-01-02 11:00:11')

but this throws error

Error Code : 1526
Table has no partition for value from column_list


test 2:

The following insert goes fine
INSERT INTO t102 VALUES (1,'2001-01-01'),(2,'2001-01-01'),(3,'2001-01-01'),(1,'2001-01-02'),(2,'2001-01-02'),(3,'2001-01-02')


SELECT partition_name, table_rows
FROM INFORMATION_SCHEMA.PARTITIONS WHERE table_name ='t102'

partition_name table_rows
p101 1
p201 1
p301 1
p102 1
p202 1
p302 1


But Problem here is ,I will be loosing the time portion of last_updated column (but I need to preserve the time portion too ).
I feel this requirement can not be implemented using RANGE partition , because all the records with "last_updated" on 2001-01-01 will go to
another partition (here p102,p202,p302 insted of p102,p201,p301)

Can you please help me on this
Regards
-JP