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
18 --disable_warnings
19 drop table if exists t1;
20 --enable_warnings
21
22 --echo # Case 1. Partitioning by RANGE based on a non-stored generated column.
23
24 CREATE TABLE t1 (
25 a DATE NOT NULL,
26 b int generated always as (year(a)) virtual
27 )
28 PARTITION BY RANGE( b ) (
29 PARTITION p0 VALUES LESS THAN (2006),
30 PARTITION p2 VALUES LESS THAN (2008)
31 );
32
33 insert into t1 values ('2006-01-01',default);
34 insert into t1 values ('2007-01-01',default);
35 insert into t1 values ('2005-01-01',default);
36 select * from t1;
37
38 # Specifically for MyISAM, check that data is written into correct
39 # $MYSQLTEST_VARDIR/master-data/test/t1*p?.MYD files
40
41 --echo # Modify the expression of generated column b
42 ALTER TABLE t1 modify b int generated always as (year(a)-1) virtual;
43
44 select * from t1;
45
46 drop table t1;
47
48 --echo # Case 2. Partitioning by LIST based on a stored generated column.
49
50 CREATE TABLE t1 (a int, b int generated always as (a % 3 ) stored)
51 PARTITION BY LIST (a+1)
52 (PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2));
53
54 insert into t1 values (1,default);
55 select * from t1;
56
57 #
58 # NOTE: The following tests are currently failing due to a
59 # [suspected] bug in the existing partition functionality.
60 # Here is what was observed when using mysqld compiled prior
61 # to adding the generated column functionality.
62 # mysql> create table t1 (a int) partition by list (a)
63 # (partition p1 values in (1), partition p2 values in (2));
64 # Query OK, 0 rows affected (0.00 sec)
65 #
66 # mysql> insert into t1 values (1), (1), (2);
67 # Query OK, 3 rows affected (0.00 sec)
68 # Records: 3 Duplicates: 0 Warnings: 0
69 #
70 # mysql> select * from t1;
71 # +------+
72 # | a |
73 # +------+
74 # | 1 |
75 # | 1 |
76 # | 2 |
77 # +------+
78 # 3 rows in set (0.00 sec)
79 #
80 # mysql> alter table t1 reorganize partition p1 into
81 # (partition p1 values in (3));
82 # Query OK, 2 rows affected (3.90 sec)
83 # Records: 2 Duplicates: 2 Warnings: 0
84 #
85 # mysql> select * from t1;
86 # +------+
87 # | a |
88 # +------+
89 # | 2 | <- Two row have been lost!!!
90 # +------+
91 # 1 row in set (0.00 sec)
92
93 #
94 #alter table t1 change b b virtual int as ((a % 3)+1) stored;
95 #--error ER_NO_PARTITION_FOR_GIVEN_VALUE
96 #alter table t1 change b b virtual int as (a % 2) stored;
97 #if ($myisam_engine)
98 #{
99 # --echo # Check how data is physically partitioned.
100 # --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
101 # --exec du -b $MYSQLTEST_VARDIR/master-data/test/t1*p?.MYD
102 #}
103
104 select * from t1;
105
106 drop table t1;
107
108 --echo # Case 3. Partitioning by HASH based on a non-stored generated column.
109
110 CREATE TABLE t1 (
111 a DATE NOT NULL,
112 b int generated always as (year(a)) virtual
113 )
114 PARTITION BY HASH( b % 3 ) PARTITIONS 3;
115
116 insert into t1 values ('2005-01-01',default);
117 insert into t1 values ('2006-01-01',default);
118 select * from t1;
119
120 --echo # Modify the expression of generated column b
121 ALTER TABLE t1 modify b int generated always as (year(a)-1) virtual;
122
123 select * from t1;
124
125 drop table t1;
126
127 --echo #
128 --echo # Bug#21779011 INVALID READS AND SENDING RANDOM SERVER MEMORY BACK
129 --echo # TO CLIENT
130 --echo #
131
132 CREATE TABLE t (
133 c INTEGER GENERATED ALWAYS AS (2) VIRTUAL,
134 d INTEGER,
135 KEY (d)
136 ) PARTITION BY KEY (d) PARTITIONS 2;
137
138 INSERT INTO t (d) VALUES (1),(1),(2),(2);
139
140 # Need to disable ONLY_FULL_GROUP_BY mode
141 --source include/turn_off_only_full_group_by.inc
142
143 SELECT c FROM t WHERE d >= 1 GROUP BY d LIMIT 2;
144
145 --source include/restore_sql_mode_after_turn_off_only_full_group_by.inc
146
147 DROP TABLE t;
148
149 --echo #
150 --echo # Bug#21779554: CHECK_MISPLACED_ROWS BOGUS "FOUND A MISPLACED ROW"
151 --echo # AND CRASHES
152 --echo #
153 CREATE TABLE t(a INT,b INT GENERATED ALWAYS AS (1) VIRTUAL,c INT)
154 PARTITION BY KEY (b)PARTITIONS 6;
155 INSERT INTO t VALUES();
156 CHECK TABLE t EXTENDED;
157 FLUSH TABLES;
158 CHECK TABLE t EXTENDED;
159 DROP TABLE t;
160
161 --echo #
162 --echo # Bug#22574695 ASSERTION `!TABLE || (!TABLE->READ_SET || BITMAP_IS_SET(TABLE->READ_SET, FIELD_I
163 --echo #
164
165 CREATE TABLE t1(
166 col1 VARCHAR(500),
167 col2 INT,
168 col5 VARCHAR(500) GENERATED ALWAYS AS
169 (SUBSTR(col1,1,500)) VIRTUAL
170 ) ENGINE = InnoDB PARTITION BY KEY(col5) PARTITIONS 2;
171
172 INSERT INTO test . t1 SET col1 = REPEAT('_', 500);
173 COMMIT;
174 UPDATE t1 SET col2 = 1;
175 SELECT 'Obviously no crash';
176
177 DROP TABLE t1;