1SET @@session.storage_engine = 'MyISAM'; 2drop table if exists t1; 3# Case 1. Partitioning by RANGE based on a non-stored virtual column. 4CREATE TABLE t1 ( 5a DATE NOT NULL, 6b int as (year(a)) 7) 8PARTITION BY RANGE( b ) ( 9PARTITION p0 VALUES LESS THAN (2006), 10PARTITION p2 VALUES LESS THAN (2008) 11); 12insert into t1 values ('2006-01-01',default); 13insert into t1 values ('2007-01-01',default); 14insert into t1 values ('2005-01-01',default); 15insert into t1 (a) values ('2007-01-02'); 16select * from t1; 17a b 182005-01-01 2005 192006-01-01 2006 202007-01-01 2007 212007-01-02 2007 22select partition_name,table_rows,data_length from information_schema.partitions where table_name = 't1'; 23partition_name table_rows data_length 24p0 1 7 25p2 3 21 26# Modify the expression of virtual column b 27ALTER TABLE t1 modify b int as (year(a)-1); 28select * from t1; 29a b 302005-01-01 2004 312006-01-01 2005 322007-01-01 2006 332007-01-02 2006 34select partition_name,table_rows,data_length from information_schema.partitions where table_name = 't1'; 35partition_name table_rows data_length 36p0 2 14 37p2 2 14 38drop table t1; 39# Case 2. Partitioning by LIST based on a stored virtual column. 40CREATE TABLE t1 (a int, b int as (a % 3 ) persistent) 41PARTITION BY LIST (a+1) 42(PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2)); 43insert into t1 values (1,default); 44select partition_name,table_rows,data_length from information_schema.partitions where table_name = 't1'; 45partition_name table_rows data_length 46p1 0 0 47p2 1 9 48select * from t1; 49a b 501 1 51select * from t1; 52a b 531 1 54drop table t1; 55# Case 3. Partitioning by HASH based on a non-stored virtual column. 56CREATE TABLE t1 ( 57a DATE NOT NULL, 58b int as (year(a)) 59) 60PARTITION BY HASH( b % 3 ) PARTITIONS 3; 61insert into t1 values ('2005-01-01',default); 62insert into t1 values ('2006-01-01',default); 63select * from t1; 64a b 652005-01-01 2005 662006-01-01 2006 67select partition_name,table_rows,data_length from information_schema.partitions where table_name = 't1'; 68partition_name table_rows data_length 69p0 0 0 70p1 1 7 71p2 1 7 72# Modify the expression of virtual column b 73ALTER TABLE t1 modify b int as (year(a)-1); 74select * from t1; 75a b 762005-01-01 2004 772006-01-01 2005 78select partition_name,table_rows,data_length from information_schema.partitions where table_name = 't1'; 79partition_name table_rows data_length 80p0 1 7 81p1 1 7 82p2 0 0 83drop table t1; 84create table t1 (a int, b datetime as (now())) partition by hash(b+1) partitions 3; 85ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed 86create table t1 (a int, b varchar(100) as (user())) partition by hash(b+1) partitions 3; 87ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed 88create table t1 (a int, b double as (rand())) partition by hash(b+1) partitions 3; 89ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed 90