Debian Codegraph @

mysql-test/suite/vcol/inc/vcol_partition.inc

raw
1 ################################################################################
2 # inc/vcol_partition.inc #
3 # #
4 # Purpose: #
5 # Testing partitioning tables with virtual columns. #
6 # #
7 # #
8 # #
9 #------------------------------------------------------------------------------#
10 # Original Author: Andrey Zhakov #
11 # Original Date: 2008-09-04 #
12 # Change Author: Oleksandr Byelkin (Monty program Ab)
13 # Date: 2009-03-24
14 # Change: Syntax changed
15 ################################################################################
16
17 --source include/have_partition.inc
18
19 --disable_warnings
20 drop table if exists t1;
21 --enable_warnings
22
23 --echo # Case 1. Partitioning by RANGE based on a non-stored virtual column.
24
25 CREATE TABLE t1 (
26 a DATE NOT NULL,
27 b int as (year(a))
28 )
29 PARTITION BY RANGE( b ) (
30 PARTITION p0 VALUES LESS THAN (2006),
31 PARTITION p2 VALUES LESS THAN (2008)
32 );
33
34 insert into t1 values ('2006-01-01',default);
35 insert into t1 values ('2007-01-01',default);
36 insert into t1 values ('2005-01-01',default);
37 insert into t1 (a) values ('2007-01-02');
38 select * from t1;
39
40 select partition_name,table_rows,data_length from information_schema.partitions where table_name = 't1';
41
42 --echo # Modify the expression of virtual column b
43 ALTER TABLE t1 modify b int as (year(a)-1);
44
45 select * from t1;
46
47 select partition_name,table_rows,data_length from information_schema.partitions where table_name = 't1';
48
49 drop table t1;
50
51 --echo # Case 2. Partitioning by LIST based on a stored virtual column.
52
53 CREATE TABLE t1 (a int, b int as (a % 3 ) persistent)
54 PARTITION BY LIST (a+1)
55 (PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2));
56
57 insert into t1 values (1,default);
58 select partition_name,table_rows,data_length from information_schema.partitions where table_name = 't1';
59 select * from t1;
60
61 #
62 # NOTE: The following tests are currently failing due to a
63 # [suspected] bug in the existing partition functionality.
64 # Here is what was observed when using mysqld compiled prior
65 # to adding the virtual column functionality.
66 # mysql> create table t1 (a int) partition by list (a)
67 # (partition p1 values in (1), partition p2 values in (2));
68 # Query OK, 0 rows affected (0.00 sec)
69 #
70 # mysql> insert into t1 values (1), (1), (2);
71 # Query OK, 3 rows affected (0.00 sec)
72 # Records: 3 Duplicates: 0 Warnings: 0
73 #
74 # mysql> select * from t1;
75 # +------+
76 # | a |
77 # +------+
78 # | 1 |
79 # | 1 |
80 # | 2 |
81 # +------+
82 # 3 rows in set (0.00 sec)
83 #
84 # mysql> alter table t1 reorganize partition p1 into
85 # (partition p1 values in (3));
86 # Query OK, 2 rows affected (3.90 sec)
87 # Records: 2 Duplicates: 2 Warnings: 0
88 #
89 # mysql> select * from t1;
90 # +------+
91 # | a |
92 # +------+
93 # | 2 | <- Two row have been lost!!!
94 # +------+
95 # 1 row in set (0.00 sec)
96
97 #
98 #alter table t1 change b b int as ((a % 3)+1) persistent;
99 #--error ER_NO_PARTITION_FOR_GIVEN_VALUE
100 #alter table t1 change b b int as (a % 2) persistent;
101 #select partition_name,table_rows,data_length from information_schema.partitions where table_name = 't1';
102
103 select * from t1;
104
105 drop table t1;
106
107 --echo # Case 3. Partitioning by HASH based on a non-stored virtual column.
108
109 CREATE TABLE t1 (
110 a DATE NOT NULL,
111 b int as (year(a))
112 )
113 PARTITION BY HASH( b % 3 ) PARTITIONS 3;
114
115 insert into t1 values ('2005-01-01',default);
116 insert into t1 values ('2006-01-01',default);
117 select * from t1;
118
119 select partition_name,table_rows,data_length from information_schema.partitions where table_name = 't1';
120
121 --echo # Modify the expression of virtual column b
122 ALTER TABLE t1 modify b int as (year(a)-1);
123
124 select * from t1;
125
126 select partition_name,table_rows,data_length from information_schema.partitions where table_name = 't1';
127
128 drop table t1;
129
130 #
131 # Restrictions when partitioned
132 #
133
134 --error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
135 create table t1 (a int, b datetime as (now())) partition by hash(b+1) partitions 3;
136 --error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
137 create table t1 (a int, b varchar(100) as (user())) partition by hash(b+1) partitions 3;
138 --error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
139 create table t1 (a int, b double as (rand())) partition by hash(b+1) partitions 3;