Debian Codegraph @

mysql-test/suite/vcol/r/vcol_partition_myisam.result

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