EVOLUTION-MANAGER

Edit File: vcol_partition_myisam.result

SET @@session.storage_engine = 'MyISAM'; drop table if exists t1; # Case 1. Partitioning by RANGE based on a non-stored virtual column. CREATE TABLE t1 ( a DATE NOT NULL, b int as (year(a)) ) PARTITION BY RANGE( b ) ( PARTITION p0 VALUES LESS THAN (2006), PARTITION p2 VALUES LESS THAN (2008) ); insert into t1 values ('2006-01-01',default); insert into t1 values ('2007-01-01',default); insert into t1 values ('2005-01-01',default); insert into t1 (a) values ('2007-01-02'); select * from t1; a b 2005-01-01 2005 2006-01-01 2006 2007-01-01 2007 2007-01-02 2007 select partition_name,table_rows,data_length from information_schema.partitions where table_name = 't1'; partition_name table_rows data_length p0 1 7 p2 3 21 # Modify the expression of virtual column b ALTER TABLE t1 modify b int as (year(a)-1); select * from t1; a b 2005-01-01 2004 2006-01-01 2005 2007-01-01 2006 2007-01-02 2006 select partition_name,table_rows,data_length from information_schema.partitions where table_name = 't1'; partition_name table_rows data_length p0 2 14 p2 2 14 drop table t1; # Case 2. Partitioning by LIST based on a stored virtual column. CREATE TABLE t1 (a int, b int as (a % 3 ) persistent) PARTITION BY LIST (a+1) (PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2)); insert into t1 values (1,default); select partition_name,table_rows,data_length from information_schema.partitions where table_name = 't1'; partition_name table_rows data_length p1 0 0 p2 1 9 select * from t1; a b 1 1 select * from t1; a b 1 1 drop table t1; # Case 3. Partitioning by HASH based on a non-stored virtual column. CREATE TABLE t1 ( a DATE NOT NULL, b int as (year(a)) ) PARTITION BY HASH( b % 3 ) PARTITIONS 3; insert into t1 values ('2005-01-01',default); insert into t1 values ('2006-01-01',default); select * from t1; a b 2005-01-01 2005 2006-01-01 2006 select partition_name,table_rows,data_length from information_schema.partitions where table_name = 't1'; partition_name table_rows data_length p0 0 0 p1 1 7 p2 1 7 # Modify the expression of virtual column b ALTER TABLE t1 modify b int as (year(a)-1); select * from t1; a b 2005-01-01 2004 2006-01-01 2005 select partition_name,table_rows,data_length from information_schema.partitions where table_name = 't1'; partition_name table_rows data_length p0 1 7 p1 1 7 p2 0 0 drop table t1; create table t1 (a int, b datetime as (now())) partition by hash(b+1) partitions 3; ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed create table t1 (a int, b varchar(100) as (user())) partition by hash(b+1) partitions 3; ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed create table t1 (a int, b double as (rand())) partition by hash(b+1) partitions 3; ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed