Debian Codegraph @

mysql-test/suite/gcol/inc/gcol_partition.inc

raw
1 ################################################################################
2 # inc/gcol_partition.inc #
3 # #
4 # Purpose: #
5 # Testing partitioning tables with generated columns. #
6 # #
7 # #
8 # #
9 #------------------------------------------------------------------------------#
10 # Original Author: Andrey Zhakov #
11 # Original Date: 2008-09-04 #
12 # Change Author: #
13 # Change Date: #
14 # Change: #
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 generated column.
24
25 CREATE TABLE t1 (
26 a DATE NOT NULL,
27 b int generated always as (year(a)) virtual
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 select * from t1;
38
39 # Specifically for MyISAM, check that data is written into correct
40 # $MYSQLTEST_VARDIR/master-data/test/t1*p?.MYD files
41
42 --echo # Modify the expression of generated column b
43 ALTER TABLE t1 modify b int generated always as (year(a)-1) virtual;
44
45 select * from t1;
46
47 drop table t1;
48
49 --echo # Case 2. Partitioning by LIST based on a stored generated column.
50
51 CREATE TABLE t1 (a int, b int generated always as (a % 3 ) stored)
52 PARTITION BY LIST (a+1)
53 (PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2));
54
55 insert into t1 values (1,default);
56 select * from t1;
57
58 #
59 # NOTE: The following tests are currently failing due to a
60 # [suspected] bug in the existing partition functionality.
61 # Here is what was observed when using mysqld compiled prior
62 # to adding the generated column functionality.
63 # mysql> create table t1 (a int) partition by list (a)
64 # (partition p1 values in (1), partition p2 values in (2));
65 # Query OK, 0 rows affected (0.00 sec)
66 #
67 # mysql> insert into t1 values (1), (1), (2);
68 # Query OK, 3 rows affected (0.00 sec)
69 # Records: 3 Duplicates: 0 Warnings: 0
70 #
71 # mysql> select * from t1;
72 # +------+
73 # | a |
74 # +------+
75 # | 1 |
76 # | 1 |
77 # | 2 |
78 # +------+
79 # 3 rows in set (0.00 sec)
80 #
81 # mysql> alter table t1 reorganize partition p1 into
82 # (partition p1 values in (3));
83 # Query OK, 2 rows affected (3.90 sec)
84 # Records: 2 Duplicates: 2 Warnings: 0
85 #
86 # mysql> select * from t1;
87 # +------+
88 # | a |
89 # +------+
90 # | 2 | <- Two row have been lost!!!
91 # +------+
92 # 1 row in set (0.00 sec)
93
94 #
95 #alter table t1 change b b virtual int as ((a % 3)+1) stored;
96 #--error ER_NO_PARTITION_FOR_GIVEN_VALUE
97 #alter table t1 change b b virtual int as (a % 2) stored;
98 #if ($myisam_engine)
99 #{
100 # --echo # Check how data is physically partitioned.
101 # --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
102 # --exec du -b $MYSQLTEST_VARDIR/master-data/test/t1*p?.MYD
103 #}
104
105 select * from t1;
106
107 drop table t1;
108
109 --echo # Case 3. Partitioning by HASH based on a non-stored generated column.
110
111 CREATE TABLE t1 (
112 a DATE NOT NULL,
113 b int generated always as (year(a)) virtual
114 )
115 PARTITION BY HASH( b % 3 ) PARTITIONS 3;
116
117 insert into t1 values ('2005-01-01',default);
118 insert into t1 values ('2006-01-01',default);
119 select * from t1;
120
121 --echo # Modify the expression of generated column b
122 ALTER TABLE t1 modify b int generated always as (year(a)-1) virtual;
123
124 select * from t1;
125
126 drop table t1;
127
128 --echo #
129 --echo # Bug#21779011 INVALID READS AND SENDING RANDOM SERVER MEMORY BACK
130 --echo # TO CLIENT
131 --echo #
132
133 CREATE TABLE t (
134 c INTEGER GENERATED ALWAYS AS (2) VIRTUAL,
135 d INTEGER,
136 KEY (d)
137 ) PARTITION BY KEY (d) PARTITIONS 2;
138
139 INSERT INTO t (d) VALUES (1),(1),(2),(2);
140
141 SELECT c FROM t WHERE d >= 1 GROUP BY d LIMIT 2;
142
143 DROP TABLE t;
144
145 --echo #
146 --echo # Bug#21779554: CHECK_MISPLACED_ROWS BOGUS "FOUND A MISPLACED ROW"
147 --echo # AND CRASHES
148 --echo #
149 CREATE TABLE t(a INT,b INT GENERATED ALWAYS AS (1) VIRTUAL,c INT)
150 PARTITION BY KEY (b)PARTITIONS 6;
151 INSERT INTO t VALUES();
152 CHECK TABLE t EXTENDED;
153 FLUSH TABLES;
154 CHECK TABLE t EXTENDED;
155 DROP TABLE t;
156
157 --echo #
158 --echo # MDEV-18166 ASSERT_COLUMN_MARKED_FOR_READ failed on tables with vcols
159 --echo #
160 CREATE TABLE t1 (
161 a INT,
162 b INT,
163 c BIT(4) NOT NULL DEFAULT b'0',
164 pk INTEGER AUTO_INCREMENT,
165 d BIT(4) AS (c) VIRTUAL,
166 PRIMARY KEY(pk),
167 KEY (b,d)
168 ) PARTITION BY HASH(pk);
169 INSERT INTO t1 () VALUES (),();
170 UPDATE t1 SET a = 0 WHERE b IS NULL ORDER BY pk;
171 DROP TABLE t1;
172
173 --echo #
174 --echo # MDEV-26220 Server crashes with indexed by prefix virtual column
175 --echo #
176
177 CREATE TABLE t1 (pk INT PRIMARY KEY, a INT, b CHAR(20), c CHAR(20) AS (b),
178 KEY (c(10),a)) PARTITION BY HASH(pk);
179 INSERT INTO t1 (pk,a,b) VALUES (1,10,'foo'),(2,11,'baz');
180 SELECT a FROM t1;
181
182 # Cleanup
183 DROP TABLE t1;