github.com/matrixorigin/matrixone@v1.2.0/pkg/sql/plan/partition_key_test.go (about) 1 // Copyright 2022 Matrix Origin 2 // 3 // Licensed under the Apache License, Version 2.0 (the "License"); 4 // you may not use this file except in compliance with the License. 5 // You may obtain a copy of the License at 6 // 7 // http://www.apache.org/licenses/LICENSE-2.0 8 // 9 // Unless required by applicable law or agreed to in writing, software 10 // distributed under the License is distributed on an "AS IS" BASIS, 11 // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 12 // See the License for the specific language governing permissions and 13 // limitations under the License. 14 15 package plan 16 17 import "testing" 18 19 func TestCreateKeyPartitionTable(t *testing.T) { 20 //sql := "create table p_table_18(col1 bigint,col2 varchar(25),col3 decimal(6,4))partition by key(col3)partitions 2;" 21 sql := "create table p_table_18(col1 bigint,col2 varchar(25),col3 decimal(20,4))partition by key(col3)partitions 2;" 22 //sql := "create table p_table_18(col1 bigint,col2 varchar(25),col3 float)partition by key(col3)partitions 2;" 23 //sql := "create table p_table_18(col1 bigint,col2 varchar(25),col3 double)partition by key(col3)partitions 2;" 24 mock := NewMockOptimizer(false) 25 logicPlan, err := buildSingleStmt(mock, t, sql) 26 if err != nil { 27 t.Fatalf("%+v", err) 28 } 29 outPutPlan(logicPlan, true, t) 30 } 31 32 // ---------------------------------- Key Partition ---------------------------------- 33 func TestKeyPartition(t *testing.T) { 34 // KEY(column_list) Partition 35 sqls := []string{ 36 "create table p_table_1(col1 bigint,col2 varchar(25),col3 decimal(6,4))partition by key(col3)partitions 2;", 37 "create table p_table_2(col1 bigint,col2 varchar(25),col3 decimal(20,4))partition by key(col3)partitions 2;", 38 "create table p_table_3(col1 bigint,col2 varchar(25),col3 float)partition by key(col3)partitions 2;", 39 "create table p_table_4(col1 bigint,col2 varchar(25),col3 double)partition by key(col3)partitions 2;", 40 "create table p_table_5(col1 bigint,col2 varchar(25),col3 timestamp)partition by key(col3)partitions 2;", 41 "create table p_table_6(col1 bigint,col2 varchar(25),col3 time)partition by key(col3)partitions 2;", 42 "CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col3) PARTITIONS 4;", 43 "CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col3);", 44 "CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR KEY(col3) PARTITIONS 5;", 45 "CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY ALGORITHM = 1 (col3);", 46 "CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR KEY ALGORITHM = 1 (col3) PARTITIONS 5;", 47 "CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col1, col2) PARTITIONS 4;", 48 `CREATE TABLE t1 ( 49 col1 INT NOT NULL, 50 col2 DATE NOT NULL, 51 col3 INT NOT NULL, 52 col4 INT NOT NULL, 53 PRIMARY KEY (col1, col2) 54 ) 55 PARTITION BY KEY(col1) 56 PARTITIONS 4;`, 57 `CREATE TABLE k1 ( 58 id INT NOT NULL PRIMARY KEY, 59 name VARCHAR(20) 60 ) 61 PARTITION BY KEY() 62 PARTITIONS 2;`, 63 `CREATE TABLE k1 ( 64 id INT NOT NULL, 65 name VARCHAR(20), 66 sal DOUBLE, 67 PRIMARY KEY (id, name) 68 ) 69 PARTITION BY KEY() 70 PARTITIONS 2;`, 71 `CREATE TABLE k1 ( 72 id INT NOT NULL, 73 name VARCHAR(20), 74 UNIQUE KEY (id) 75 ) 76 PARTITION BY KEY() 77 PARTITIONS 2;`, 78 `CREATE TABLE t1 ( 79 col1 INT NOT NULL, 80 col2 DATE NOT NULL, 81 col3 INT NOT NULL, 82 col4 INT NOT NULL, 83 PRIMARY KEY (col1, col2) 84 ) 85 PARTITION BY KEY() 86 PARTITIONS 4;`, 87 `CREATE TABLE t2 ( 88 col1 INT NOT NULL, 89 col2 DATE NOT NULL, 90 col3 INT NOT NULL, 91 col4 INT NOT NULL, 92 PRIMARY KEY (col1), 93 unique key (col1, col4) 94 ) 95 PARTITION BY KEY() 96 PARTITIONS 4;`, 97 98 `CREATE TABLE t8 ( 99 col1 INT, 100 col2 DATE NOT NULL, 101 col3 INT NOT NULL, 102 col4 INT NOT NULL, 103 UNIQUE KEY (col1, col3) 104 ) 105 PARTITION BY KEY(col1) 106 PARTITIONS 4;`, 107 } 108 109 mock := NewMockOptimizer(false) 110 for _, sql := range sqls { 111 t.Log(sql) 112 logicPlan, err := buildSingleStmt(mock, t, sql) 113 if err != nil { 114 t.Fatalf("%+v", err) 115 } 116 outPutPlan(logicPlan, true, t) 117 } 118 } 119 120 func TestKeyPartitionError(t *testing.T) { 121 sqls := []string{ 122 "create table p_t1(col1 bigint,col2 varchar(25),col3 blob)partition by key(col3)partitions 2;", 123 "create table p_t2(col1 bigint,col2 varchar(25),col3 text)partition by key(col3)partitions 2;", 124 "create table p_t3(col1 bigint,col2 varchar(25),col3 json)partition by key(col3)partitions 2;", 125 "CREATE TABLE ts (id INT, purchased DATE) PARTITION BY KEY( id ) PARTITIONS 4 SUBPARTITION BY HASH( TO_DAYS(purchased) ) SUBPARTITIONS 2;", 126 "CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col4) PARTITIONS 4;", 127 "CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY ALGORITHM = 3 (col3);", 128 `CREATE TABLE t1 ( 129 col1 INT NOT NULL, 130 col2 DATE NOT NULL, 131 col3 INT NOT NULL, 132 col4 INT NOT NULL, 133 PRIMARY KEY (col1, col2) 134 ) 135 PARTITION BY KEY(col3) 136 PARTITIONS 4;`, 137 `CREATE TABLE k1 ( 138 id INT NOT NULL, 139 name VARCHAR(20) 140 ) 141 PARTITION BY KEY() 142 PARTITIONS 2;`, 143 `CREATE TABLE t4 ( 144 col1 INT NOT NULL, 145 col2 INT NOT NULL, 146 col3 INT NOT NULL, 147 col4 INT NOT NULL, 148 UNIQUE KEY (col1, col3), 149 UNIQUE KEY (col2, col4) 150 ) 151 PARTITION BY KEY() 152 PARTITIONS 2;`, 153 154 `CREATE TABLE t2 ( 155 col1 INT NOT NULL, 156 col2 DATE NOT NULL, 157 col3 INT NOT NULL, 158 col4 INT NOT NULL, 159 PRIMARY KEY (col1), 160 unique key (col3, col4) 161 ) 162 PARTITION BY KEY() 163 PARTITIONS 4;`, 164 165 `CREATE TABLE t3 ( 166 col1 INT NOT NULL, 167 col2 DATE NOT NULL, 168 col3 INT NOT NULL, 169 col4 INT NOT NULL, 170 PRIMARY KEY (col1, col4), 171 unique key (col1) 172 ) 173 PARTITION BY KEY() 174 PARTITIONS 4;`, 175 176 `CREATE TABLE t1 ( 177 col1 INT NOT NULL, 178 col2 DATE NOT NULL, 179 col3 INT NOT NULL, 180 col4 INT NOT NULL, 181 PRIMARY KEY (col1, col2) 182 ) 183 PARTITION BY KEY(col3) 184 PARTITIONS 4;`, 185 186 `create table p_table_07( 187 col1 int, 188 col2 char(25), 189 col3 decimal(4,2), 190 unique key k2(col1,col2) 191 )partition by key() 192 partitions 8;`, 193 194 `create table p_table_09( 195 col1 int NOT NULL, 196 col2 char(25) NOT NULL, 197 col3 decimal(4,2) NOT NULL 198 )partition by key() 199 partitions 8;`, 200 201 `create table p_table_01( 202 col1 int, 203 col2 char(25), 204 col3 int NOT NULL, 205 UNIQUE KEY k2(col1,col2), 206 UNIQUE KEY k3(col3) 207 ) 208 partition by key() 209 partitions 8;`, 210 211 `create table p_table_02( 212 col1 int NOT NULL, 213 col2 char(25) NOT NULL, 214 col3 int NOT NULL, 215 UNIQUE KEY k2(col1,col2), 216 UNIQUE KEY k3(col3) 217 ) 218 partition by key() 219 partitions 8;`, 220 221 `create table p_table_03( 222 col1 int, 223 col2 char(25), 224 col3 decimal(4,2) NOT NULL, 225 UNIQUE KEY k2(col1,col2) 226 ) 227 partition by key() 228 partitions 8;`, 229 230 `CREATE TABLE t5 ( 231 col1 INT, 232 col2 DATE NOT NULL, 233 col3 INT NOT NULL, 234 col4 INT NOT NULL, 235 UNIQUE KEY (col1, col3) 236 ) 237 PARTITION BY KEY() 238 PARTITIONS 4;`, 239 240 `CREATE TABLE t7 ( 241 col1 INT, 242 col2 DATE NOT NULL, 243 col3 INT NOT NULL, 244 col4 INT NOT NULL, 245 UNIQUE KEY (col1, col3) 246 ) 247 PARTITION BY KEY() 248 PARTITIONS 4;`, 249 250 `create table pt_table_21( 251 col1 tinyint, 252 col2 smallint, 253 col3 int, 254 col4 bigint, 255 col5 tinyint unsigned, 256 col6 smallint unsigned, 257 col7 int unsigned, 258 col8 bigint unsigned, 259 col9 float, 260 col10 double, 261 col11 varchar(255), 262 col12 Date, 263 col13 DateTime, 264 col14 timestamp, 265 col15 bool, 266 col16 decimal(5,2), 267 col17 text, 268 col18 varchar(255), 269 col19 varchar(255), 270 col20 int, 271 col21 int 272 ) 273 partition by key(col1,col2,col3, col4,col5 ,col6 ,col7 ,col8 ,col9 ,col10,col11,col12,col13,col16,col18,col19,col20,col21) 274 partitions 4;`, 275 } 276 mock := NewMockOptimizer(false) 277 for _, sql := range sqls { 278 _, err := buildSingleStmt(mock, t, sql) 279 t.Log(sql) 280 t.Log(err) 281 if err == nil { 282 t.Fatalf("%+v", err) 283 } 284 } 285 }