diff options
Diffstat (limited to 'mysql-test/suite/vcol/r/vcol_partition_myisam.result')
| -rw-r--r-- | mysql-test/suite/vcol/r/vcol_partition_myisam.result | 89 |
1 files changed, 89 insertions, 0 deletions
diff --git a/mysql-test/suite/vcol/r/vcol_partition_myisam.result b/mysql-test/suite/vcol/r/vcol_partition_myisam.result new file mode 100644 index 00000000..a7733ddf --- /dev/null +++ b/mysql-test/suite/vcol/r/vcol_partition_myisam.result @@ -0,0 +1,89 @@ +SET @@session.default_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 |