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  }