1################################################################################ 2# inc/partition_syntax.inc # 3# # 4# Purpose: # 5# Tests around Create partitioned tables syntax # 6# # 7#------------------------------------------------------------------------------# 8# Original Author: mleich # 9# Original Date: 2006-03-05 # 10# Change Author: # 11# Change Date: # 12# Change: # 13################################################################################ 14 15# FIXME Implement testcases, where it is checked that all create and 16# alter table statements 17# - with missing mandatory parameters are rejected 18# - with optional parameters are accepted 19# - with wrong combinations of optional parameters are rejected 20# - ............ 21 22--echo 23--echo #======================================================================== 24--echo # 1. Any PRIMARY KEYs or UNIQUE INDEXes must contain the columns used 25--echo # within the partitioning functions 26--echo #======================================================================== 27--disable_warnings 28DROP TABLE IF EXISTS t1; 29--enable_warnings 30# 31--echo #------------------------------------------------------------------------ 32--echo # 1.1 column of partitioning function not included in PRIMARY KEY 33--echo # PARTITION BY HASH/KEY/LIST/RANGE 34--echo #------------------------------------------------------------------------ 35#----------- PARTITION BY HASH 36--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF 37eval CREATE TABLE t1 ( 38$column_list, 39PRIMARY KEY (f_int2) 40) 41PARTITION BY HASH(f_int1) PARTITIONS 2; 42--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF 43eval CREATE TABLE t1 ( 44$column_list, 45PRIMARY KEY (f_int2) 46) 47PARTITION BY HASH(f_int1 + f_int2) PARTITIONS 2; 48#----------- PARTITION BY KEY 49--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF 50eval CREATE TABLE t1 ( 51$column_list, 52PRIMARY KEY (f_int2) 53) 54PARTITION BY KEY(f_int1) PARTITIONS 2; 55--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF 56eval CREATE TABLE t1 ( 57$column_list, 58PRIMARY KEY (f_int2) 59) 60PARTITION BY KEY(f_int1,f_int2) PARTITIONS 2; 61#----------- PARTITION BY LIST 62--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF 63eval CREATE TABLE t1 ( 64$column_list, 65PRIMARY KEY (f_int2) 66) 67PARTITION BY LIST(f_int1) 68(PARTITION part1 VALUES IN (1)); 69--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF 70eval CREATE TABLE t1 ( 71$column_list, 72PRIMARY KEY (f_int2) 73) 74PARTITION BY LIST(f_int1 + f_int2) 75(PARTITION part1 VALUES IN (1)); 76#----------- PARTITION BY RANGE 77--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF 78eval CREATE TABLE t1 ( 79$column_list, 80PRIMARY KEY (f_int2) 81) 82PARTITION BY RANGE(f_int1) 83(PARTITION part1 VALUES LESS THAN (1)); 84--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF 85eval CREATE TABLE t1 ( 86$column_list, 87PRIMARY KEY (f_int2) 88) 89PARTITION BY RANGE(f_int1 + f_int2) 90(PARTITION part1 VALUES LESS THAN (1)); 91 92# 93--echo #------------------------------------------------------------------------ 94--echo # 1.2 column of partitioning function not included in UNIQUE INDEX 95--echo # PARTITION BY HASH/KEY/LIST/RANGE 96--echo # Variant a) Without additional PRIMARY KEY 97--echo # Variant b) With correct additional PRIMARY KEY 98--echo # Variant 1) one column in partitioning function 99--echo # Variant 2) two columns in partitioning function 100--echo #------------------------------------------------------------------------ 101# Note: If the CREATE TABLE statement contains no PRIMARY KEY but 102# UNIQUE INDEXes the MySQL layer tells the storage to use 103# the first UNIQUE INDEX as PRIMARY KEY. 104 105let $unique_index= UNIQUE INDEX (f_int2); 106 107#----------- PARTITION BY HASH 108let $partition_scheme= PARTITION BY HASH(f_int1) PARTITIONS 2; 109--source suite/parts/inc/partition_syntax_2.inc 110let $partition_scheme= PARTITION BY HASH(f_int1 + f_int2) PARTITIONS 2; 111--source suite/parts/inc/partition_syntax_2.inc 112#----------- PARTITION BY KEY 113let $partition_scheme= PARTITION BY KEY(f_int1) PARTITIONS 2; 114--source suite/parts/inc/partition_syntax_2.inc 115let $partition_scheme= PARTITION BY KEY(f_int1,f_int2) PARTITIONS 2; 116--source suite/parts/inc/partition_syntax_2.inc 117#----------- PARTITION BY LIST 118let $partition_scheme= PARTITION BY LIST(MOD(f_int1,3)) 119 (PARTITION partN VALUES IN (NULL), 120 PARTITION part0 VALUES IN (0), 121 PARTITION part1 VALUES IN (1), 122 PARTITION part2 VALUES IN (2)); 123--source suite/parts/inc/partition_syntax_2.inc 124let $partition_scheme= PARTITION BY LIST(MOD(f_int1 + f_int2,3)) 125 (PARTITION partN VALUES IN (NULL), 126 PARTITION part0 VALUES IN (0), 127 PARTITION part1 VALUES IN (1), 128 PARTITION part2 VALUES IN (2)); 129--source suite/parts/inc/partition_syntax_2.inc 130#----------- PARTITION BY RANGE 131let $partition_scheme= PARTITION BY RANGE(f_int1) 132 (PARTITION part1 VALUES LESS THAN (1), 133 PARTITION part2 VALUES LESS THAN (2147483646)); 134--source suite/parts/inc/partition_syntax_2.inc 135let $partition_scheme= PARTITION BY RANGE(f_int1 + f_int2) 136 (PARTITION part1 VALUES LESS THAN (1), 137 PARTITION part2 VALUES LESS THAN (2147483646)); 138--source suite/parts/inc/partition_syntax_2.inc 139 140# 141--echo #------------------------------------------------------------------------ 142--echo # 1.3 column of subpartitioning function not included in PRIMARY KEY 143--echo # PARTITION BY RANGE/LIST -- SUBPARTITION BY HASH/KEY 144--echo #------------------------------------------------------------------------ 145 146#----------- PARTITION BY RANGE -- SUBPARTITION BY HASH 147--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF 148eval CREATE TABLE t1 ( 149$column_list, 150PRIMARY KEY (f_int2) 151) 152PARTITION BY RANGE(f_int2) SUBPARTITION BY HASH(f_int1) 153(PARTITION part1 VALUES LESS THAN (1) 154 (SUBPARTITION subpart1)); 155#----------- PARTITION BY RANGE -- SUBPARTITION BY KEY 156--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF 157eval CREATE TABLE t1 ( 158$column_list, 159PRIMARY KEY (f_int2) 160) 161PARTITION BY RANGE(f_int2) SUBPARTITION BY KEY(f_int1) 162(PARTITION part1 VALUES LESS THAN (1) 163 (SUBPARTITION subpart1)); 164#----------- PARTITION BY LIST -- SUBPARTITION BY HASH 165--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF 166eval CREATE TABLE t1 ( 167$column_list, 168PRIMARY KEY (f_int2) 169) 170PARTITION BY LIST(f_int2) SUBPARTITION BY HASH(f_int1) 171(PARTITION part1 VALUES IN (1) 172 (SUBPARTITION subpart1)); 173#----------- PARTITION BY LIST -- SUBPARTITION BY KEY 174--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF 175eval CREATE TABLE t1 ( 176$column_list, 177PRIMARY KEY (f_int2) 178) 179PARTITION BY LIST(f_int2) SUBPARTITION BY KEY(f_int1) 180(PARTITION part1 VALUES IN (1) 181 (SUBPARTITION subpart1)); 182 183# 184--echo #------------------------------------------------------------------------ 185--echo # 1.4 column of subpartitioning function not included in UNIQUE INDEX 186--echo # PARTITION BY RANGE/LIST -- SUBPARTITION BY HASH/KEY 187--echo # Variant a) Without additional PRIMARY KEY 188--echo # Variant b) With correct additional PRIMARY KEY 189--echo #------------------------------------------------------------------------ 190let $partition_scheme= PARTITION BY RANGE(f_int2) 191SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 3 192 (PARTITION part1 VALUES LESS THAN (1), 193 PARTITION part2 VALUES LESS THAN (2147483646)); 194--source suite/parts/inc/partition_syntax_2.inc 195#----------- PARTITION BY RANGE -- SUBPARTITION BY KEY 196let $partition_scheme= PARTITION BY RANGE(f_int2) 197SUBPARTITION BY KEY(f_int1) SUBPARTITIONS 3 198 (PARTITION part1 VALUES LESS THAN (1), 199 PARTITION part2 VALUES LESS THAN (2147483646)); 200--source suite/parts/inc/partition_syntax_2.inc 201#----------- PARTITION BY LIST -- SUBPARTITION BY HASH 202let $partition_scheme= PARTITION BY LIST(MOD(f_int2,3)) 203SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2 204 (PARTITION partN VALUES IN (NULL), 205 PARTITION part0 VALUES IN (0), 206 PARTITION part1 VALUES IN (1), 207 PARTITION part2 VALUES IN (2)); 208--source suite/parts/inc/partition_syntax_2.inc 209#----------- PARTITION BY LIST -- SUBPARTITION BY KEY 210let $partition_scheme= PARTITION BY LIST(MOD(f_int2,3)) 211SUBPARTITION BY KEY(f_int1) SUBPARTITIONS 2 212 (PARTITION partN VALUES IN (NULL), 213 PARTITION part0 VALUES IN (0), 214 PARTITION part1 VALUES IN (1), 215 PARTITION part2 VALUES IN (2)); 216--source suite/parts/inc/partition_syntax_2.inc 217 218--echo 219--echo #======================================================================== 220--echo # 2 Some properties around subpartitioning 221--echo #======================================================================== 222--echo #------------------------------------------------------------------------ 223--echo # 2.1 Subpartioned table without subpartitioning rule must be rejected 224--echo #------------------------------------------------------------------------ 225--disable_warnings 226DROP TABLE IF EXISTS t1; 227--enable_warnings 228# Bug#15961 Partitions: Creation of subpart. table without subpart. rule not rejected 229--error ER_SUBPARTITION_ERROR 230eval CREATE TABLE t1 ( 231$column_list 232) 233PARTITION BY RANGE(f_int1) 234( PARTITION part1 VALUES LESS THAN (1000) (SUBPARTITION subpart11)); 235--echo #------------------------------------------------------------------------ 236--echo # 2.2 Every partition must have the same number of subpartitions. 237--echo # This is a limitation of MySQL 5.1, which could be removed in 238--echo # later releases. 239--echo #------------------------------------------------------------------------ 240--error ER_PARSE_ERROR 241eval CREATE TABLE t1 ( 242$column_list, 243PRIMARY KEY (f_int1) 244) 245PARTITION BY RANGE(f_int1) SUBPARTITION BY KEY(f_int1) 246( 247 PARTITION part1 VALUES LESS THAN (0) 248 (SUBPARTITION subpart1), 249 PARTITION part2 VALUES LESS THAN ($max_row_div4) 250 (SUBPARTITION subpart1, SUBPARTITION subpart2)); 251 252--echo 253--echo #======================================================================== 254--echo # 3 VALUES clauses 255--echo #======================================================================== 256--echo #------------------------------------------------------------------------ 257--echo # 3.1 The constants in VALUES IN clauses must differ 258--echo #------------------------------------------------------------------------ 259--error ER_MULTIPLE_DEF_CONST_IN_LIST_PART_ERROR 260eval CREATE TABLE t1 ( 261$column_list 262) 263PARTITION BY LIST(MOD(f_int1,2)) 264( PARTITION part1 VALUES IN (-1), 265 PARTITION part2 VALUES IN (0), 266 PARTITION part3 VALUES IN (-1)); 267# constant followed by the same constant 268--error ER_RANGE_NOT_INCREASING_ERROR 269CREATE TABLE t1 (f1 BIGINT, f2 BIGINT) 270PARTITION BY RANGE(f1) 271(PARTITION part1 VALUES LESS THAN (0), 272PARTITION part2 VALUES LESS THAN (0), 273PARTITION part3 VALUES LESS THAN (10000)); 274 275--echo #------------------------------------------------------------------------ 276--echo # 3.2 The constants in VALUES LESS must be in increasing order 277--echo #------------------------------------------------------------------------ 278# constant followed somewhere by the smaller constant 279--error ER_RANGE_NOT_INCREASING_ERROR 280CREATE TABLE t1 (f1 BIGINT, f2 BIGINT) 281PARTITION BY RANGE(f1) 282(PARTITION part1 VALUES LESS THAN (0), 283PARTITION part2 VALUES LESS THAN (-1), 284PARTITION part3 VALUES LESS THAN (10000)); 285 286--echo #------------------------------------------------------------------------ 287--echo # 3.3 LIST partitions must be defined with VALUES IN 288--echo #------------------------------------------------------------------------ 289--error ER_PARTITION_WRONG_VALUES_ERROR 290eval CREATE TABLE t1 ( 291$column_list 292) 293PARTITION BY LIST(MOD(f_int1,2)) 294( PARTITION part1 VALUES LESS THAN (-1), 295 PARTITION part2 VALUES LESS THAN (0), 296 PARTITION part3 VALUES LESS THAN (1000)); 297 298--echo #------------------------------------------------------------------------ 299--echo # 3.4 RANGE partitions must be defined with VALUES LESS THAN 300--echo #------------------------------------------------------------------------ 301--error ER_PARTITION_WRONG_VALUES_ERROR 302eval CREATE TABLE t1 ( 303$column_list 304) 305PARTITION BY RANGE(f_int1) 306( PARTITION part1 VALUES IN (-1), 307 PARTITION part2 VALUES IN (0), 308 PARTITION part3 VALUES IN (1000)); 309 310--echo #------------------------------------------------------------------------ 311--echo # 3.5 Use of NULL in VALUES clauses 312--echo #------------------------------------------------------------------------ 313--echo # 3.5.1 NULL in RANGE partitioning clause 314--echo # 3.5.1.1 VALUE LESS THAN (NULL) is not allowed 315--error ER_NULL_IN_VALUES_LESS_THAN 316eval CREATE TABLE t1 ( 317$column_list 318) 319PARTITION BY RANGE(f_int1) 320( PARTITION part1 VALUES LESS THAN (NULL), 321 PARTITION part2 VALUES LESS THAN (1000)); 322--echo # 3.5.1.2 VALUE LESS THAN (NULL) is not allowed 323--error ER_NULL_IN_VALUES_LESS_THAN 324eval CREATE TABLE t1 ( 325$column_list 326) 327PARTITION BY RANGE(f_int1) 328( PARTITION part1 VALUES LESS THAN (NULL), 329 PARTITION part2 VALUES LESS THAN (1000)); 330--echo # 3.5.2 NULL in LIST partitioning clause 331--echo # 3.5.2.1 VALUE IN (NULL) 332eval CREATE TABLE t1 ( 333$column_list 334) 335PARTITION BY LIST(MOD(f_int1,2)) 336( PARTITION part1 VALUES IN (NULL), 337 PARTITION part2 VALUES IN (0), 338 PARTITION part3 VALUES IN (1)); 339DROP TABLE t1; 340--echo # 3.5.2.2 VALUE IN (NULL) 341# Attention: It is intended that there is no partition with 342# VALUES IN (0), because there was a time where NULL was treated as zero 343eval CREATE TABLE t1 ( 344$column_list 345) 346PARTITION BY LIST(MOD(f_int1,2)) 347( PARTITION part1 VALUES IN (NULL), 348 PARTITION part3 VALUES IN (1)); 349--source suite/parts/inc/partition_layout_check1.inc 350DROP TABLE t1; 351--echo # 3.5.3 Reveal that IN (...NULL) is not mapped to IN(0) 352# Bug#15447: Partitions: NULL is treated as zero 353# We would get a clash here if such a mapping would be done. 354eval CREATE TABLE t1 ( 355$column_list 356) 357PARTITION BY LIST(MOD(f_int1,2)) 358( PARTITION part1 VALUES IN (NULL), 359 PARTITION part2 VALUES IN (0), 360 PARTITION part3 VALUES IN (1)); 361--source suite/parts/inc/partition_layout_check1.inc 362DROP TABLE t1; 363 364# FIXME Implement some non integer constant tests 365 366 367--echo 368--echo #======================================================================== 369--echo # 4. Check assigning the number of partitions and subpartitions 370--echo # with and without named partitions/subpartitions 371--echo #======================================================================== 372--disable_warnings 373DROP TABLE IF EXISTS t1; 374--enable_warnings 375--echo #------------------------------------------------------------------------ 376--echo # 4.1 (positive) without partition/subpartition number assignment 377--echo #------------------------------------------------------------------------ 378--echo # 4.1.1 no partition number, no named partitions 379eval CREATE TABLE t1 ( 380$column_list 381) 382PARTITION BY HASH(f_int1); 383--source suite/parts/inc/partition_layout_check1.inc 384DROP TABLE t1; 385--echo # 4.1.2 no partition number, named partitions 386eval CREATE TABLE t1 ( 387$column_list 388) 389PARTITION BY HASH(f_int1) (PARTITION part1, PARTITION part2); 390--source suite/parts/inc/partition_layout_check1.inc 391DROP TABLE t1; 392# Attention: Several combinations are impossible 393# If subpartitioning exists 394# - partitioning algorithm must be RANGE or LIST 395# This implies the assignment of named partitions. 396# - subpartitioning algorithm must be HASH or KEY 397--echo # 4.1.3 variations on no partition/subpartition number, named partitions, 398--echo # different subpartitions are/are not named 399# 400# Partition name -- "properties" 401# part1 -- first/non last 402# part2 -- non first/non last 403# part3 -- non first/ last 404# 405# Testpattern: 406# named subpartitions in 407# Partition part1 part2 part3 408# N N N 409# N N Y 410# N Y N 411# N Y Y 412# Y N N 413# Y N Y 414# Y Y N 415# Y Y Y 416--disable_query_log 417let $part01= CREATE TABLE t1 ( ; 418let $part02= ) 419PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1); 420# 421eval SET @aux = '(PARTITION part1 VALUES LESS THAN ($max_row_div2),'; 422let $part1_N= `SELECT @AUX`; 423eval SET @aux = '(PARTITION part1 VALUES LESS THAN ($max_row_div2) 424(SUBPARTITION subpart11 , SUBPARTITION subpart12 ),'; 425let $part1_Y= `SELECT @AUX`; 426# 427eval SET @aux = 'PARTITION part2 VALUES LESS THAN ($max_row),'; 428let $part2_N= `SELECT @AUX`; 429eval SET @aux = 'PARTITION part2 VALUES LESS THAN ($max_row) 430(SUBPARTITION subpart21 , SUBPARTITION subpart22 ),'; 431let $part2_Y= `SELECT @AUX`; 432# 433eval SET @aux = 'PARTITION part3 VALUES LESS THAN $MAX_VALUE)'; 434let $part3_N= `SELECT @AUX`; 435eval SET @aux = 'PARTITION part3 VALUES LESS THAN $MAX_VALUE 436(SUBPARTITION subpart31 , SUBPARTITION subpart32 ))'; 437let $part3_Y= `SELECT @AUX`; 438--enable_query_log 439 440eval $part01 $column_list $part02 $part1_N $part2_N $part3_N ; 441DROP TABLE t1; 442# Bug#15407 Partitions: crash if subpartition 443--error ER_PARSE_ERROR 444eval $part01 $column_list $part02 $part1_N $part2_N $part3_Y ; 445--error ER_PARSE_ERROR 446eval $part01 $column_list $part02 $part1_N $part2_Y $part3_N ; 447--error ER_PARSE_ERROR 448eval $part01 $column_list $part02 $part1_N $part2_Y $part3_Y ; 449--error ER_PARSE_ERROR 450eval $part01 $column_list $part02 $part1_Y $part2_N $part3_N ; 451--error ER_PARSE_ERROR 452eval $part01 $column_list $part02 $part1_Y $part2_N $part3_Y ; 453--error ER_PARSE_ERROR 454eval $part01 $column_list $part02 $part1_Y $part2_Y $part3_N ; 455eval $part01 $column_list $part02 $part1_Y $part2_Y $part3_Y ; 456--source suite/parts/inc/partition_layout_check1.inc 457DROP TABLE t1; 458 459--echo #------------------------------------------------------------------------ 460--echo # 4.2 partition/subpartition numbers good and bad values and notations 461--echo #------------------------------------------------------------------------ 462--disable_warnings 463DROP TABLE IF EXISTS t1; 464--enable_warnings 465--echo # 4.2.1 partition/subpartition numbers INTEGER notation 466# mleich: "positive/negative" is my private judgement. It need not to 467# correspond with the server response. 468# (positive) number = 2 469let $part_number= 2; 470--source suite/parts/inc/partition_syntax_1.inc 471# (positive) special case number = 1 472let $part_number= 1; 473--source suite/parts/inc/partition_syntax_1.inc 474# (negative) 0 is non sense 475let $part_number= 0; 476--source suite/parts/inc/partition_syntax_1.inc 477# (negative) -1 is non sense 478let $part_number= -1; 479--source suite/parts/inc/partition_syntax_1.inc 480# (negative) 1000000 is too huge 481let $part_number= 1000000; 482--source suite/parts/inc/partition_syntax_1.inc 483 484--echo # 4.2.2 partition/subpartition numbers DECIMAL notation 485# (positive) number = 2.0 486let $part_number= 2.0; 487--source suite/parts/inc/partition_syntax_1.inc 488# (negative) -2.0 is non sense 489let $part_number= -2.0; 490--source suite/parts/inc/partition_syntax_1.inc 491# (negative) case number = 0.0 is non sense 492let $part_number= 0.0; 493--source suite/parts/inc/partition_syntax_1.inc 494# Bug#15890 Partitions: Strange interpretation of partition number 495# (negative) number = 1.6 is non sense 496let $part_number= 1.6; 497--source suite/parts/inc/partition_syntax_1.inc 498# (negative) number is too huge 499let $part_number= 999999999999999999999999999999.999999999999999999999999999999; 500--source suite/parts/inc/partition_syntax_1.inc 501# (negative) number is nearly zero 502let $part_number= 0.000000000000000000000000000001; 503--source suite/parts/inc/partition_syntax_1.inc 504 505--echo # 4.2.3 partition/subpartition numbers FLOAT notation 506##### FLOAT notation 507# (positive) number = 2.0E+0 508let $part_number= 2.0E+0; 509--source suite/parts/inc/partition_syntax_1.inc 510# Bug#15890 Partitions: Strange interpretation of partition number 511# (positive) number = 0.2E+1 512let $part_number= 0.2E+1; 513--source suite/parts/inc/partition_syntax_1.inc 514# (negative) -2.0E+0 is non sense 515let $part_number= -2.0E+0; 516--source suite/parts/inc/partition_syntax_1.inc 517# Bug#15890 Partitions: Strange interpretation of partition number 518# (negative) 0.16E+1 is non sense 519let $part_number= 0.16E+1; 520--source suite/parts/inc/partition_syntax_1.inc 521# (negative) 0.0E+300 is zero 522let $part_number= 0.0E+300; 523--source suite/parts/inc/partition_syntax_1.inc 524# Bug#15890 Partitions: Strange interpretation of partition number 525# (negative) 1E+300 is too huge 526let $part_number= 1E+300; 527--source suite/parts/inc/partition_syntax_1.inc 528# (negative) 1E-300 is nearly zero 529let $part_number= 1E-300; 530--source suite/parts/inc/partition_syntax_1.inc 531 532--echo # 4.2.4 partition/subpartition numbers STRING notation 533##### STRING notation 534# (negative?) case number = '2' 535let $part_number= '2'; 536--source suite/parts/inc/partition_syntax_1.inc 537# (negative?) case number = '2.0' 538let $part_number= '2.0'; 539--source suite/parts/inc/partition_syntax_1.inc 540# (negative?) case number = '0.2E+1' 541let $part_number= '0.2E+1'; 542--source suite/parts/inc/partition_syntax_1.inc 543# (negative) Strings starts with digit, but 'A' follows 544let $part_number= '2A'; 545--source suite/parts/inc/partition_syntax_1.inc 546# (negative) Strings starts with 'A', but digit follows 547let $part_number= 'A2'; 548--source suite/parts/inc/partition_syntax_1.inc 549# (negative) empty string 550let $part_number= ''; 551--source suite/parts/inc/partition_syntax_1.inc 552# (negative) string without any digits 553let $part_number= 'GARBAGE'; 554--source suite/parts/inc/partition_syntax_1.inc 555 556--echo # 4.2.5 partition/subpartition numbers other notations 557# (negative) Strings starts with digit, but 'A' follows 558let $part_number= 2A; 559--source suite/parts/inc/partition_syntax_1.inc 560# (negative) Strings starts with 'A', but digit follows 561let $part_number= A2; 562--source suite/parts/inc/partition_syntax_1.inc 563# (negative) string without any digits 564let $part_number= GARBAGE; 565--source suite/parts/inc/partition_syntax_1.inc 566 567# (negative?) double quotes 568let $part_number= "2"; 569--source suite/parts/inc/partition_syntax_1.inc 570# (negative) Strings starts with digit, but 'A' follows 571let $part_number= "2A"; 572--source suite/parts/inc/partition_syntax_1.inc 573# (negative) Strings starts with 'A', but digit follows 574let $part_number= "A2"; 575--source suite/parts/inc/partition_syntax_1.inc 576# (negative) string without any digits 577let $part_number= "GARBAGE"; 578--source suite/parts/inc/partition_syntax_1.inc 579 580--echo # 4.2.6 (negative) partition/subpartition numbers per @variables 581SET @aux = 5; 582--error ER_PARSE_ERROR 583eval CREATE TABLE t1 ( 584$column_list 585) 586PARTITION BY HASH(f_int1) PARTITIONS @aux; 587--error ER_PARSE_ERROR 588eval CREATE TABLE t1 ( 589$column_list 590) 591PARTITION BY RANGE(f_int1) SUBPARTITION BY HASH(f_int1) 592SUBPARTITIONS @aux = 5 593(PARTITION part1 VALUES LESS THAN ($max_row_div2), 594 PARTITION part2 VALUES LESS THAN $MAX_VALUE); 595 596 597--echo #------------------------------------------------------------------------ 598--echo # 4.3 Mixups of assigned partition/subpartition numbers and names 599--echo #------------------------------------------------------------------------ 600--echo # 4.3.1 (positive) number of partition/subpartition 601--echo # = number of named partition/subpartition 602eval CREATE TABLE t1 ( 603$column_list 604) 605PARTITION BY HASH(f_int1) PARTITIONS 2 ( PARTITION part1, PARTITION part2 ) ; 606--source suite/parts/inc/partition_layout_check1.inc 607DROP TABLE t1; 608eval CREATE TABLE t1 ( 609$column_list 610) 611PARTITION BY RANGE(f_int1) PARTITIONS 2 612SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2 613( PARTITION part1 VALUES LESS THAN (1000) 614 (SUBPARTITION subpart11, SUBPARTITION subpart12), 615 PARTITION part2 VALUES LESS THAN $MAX_VALUE 616 (SUBPARTITION subpart21, SUBPARTITION subpart22) 617); 618--source suite/parts/inc/partition_layout_check1.inc 619DROP TABLE t1; 620--echo # 4.3.2 (positive) number of partition/subpartition , 621--echo # 0 (= no) named partition/subpartition 622--echo # already checked above 623--echo # 4.3.3 (negative) number of partitions/subpartitions 624--echo # > number of named partitions/subpartitions 625--error ER_PARSE_ERROR 626eval CREATE TABLE t1 ( 627$column_list 628) 629PARTITION BY HASH(f_int1) PARTITIONS 2 ( PARTITION part1 ) ; 630# Wrong number of named subpartitions in first partition 631--error ER_PARSE_ERROR 632eval CREATE TABLE t1 ( 633$column_list 634) 635PARTITION BY RANGE(f_int1) 636SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2 637( PARTITION part1 VALUES LESS THAN (1000) 638 (SUBPARTITION subpart11 ), 639 PARTITION part2 VALUES LESS THAN $MAX_VALUE 640 (SUBPARTITION subpart21, SUBPARTITION subpart22) 641); 642# Wrong number of named subpartitions in non first/non last partition 643--error ER_PARSE_ERROR 644eval CREATE TABLE t1 ( 645$column_list 646) 647PARTITION BY RANGE(f_int1) 648SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2 649( PARTITION part1 VALUES LESS THAN (1000) 650 (SUBPARTITION subpart11, SUBPARTITION subpart12), 651 PARTITION part2 VALUES LESS THAN (2000) 652 (SUBPARTITION subpart21 ), 653 PARTITION part3 VALUES LESS THAN $MAX_VALUE 654 (SUBPARTITION subpart31, SUBPARTITION subpart32) 655); 656# Wrong number of named subpartitions in last partition 657--error ER_PARSE_ERROR 658eval CREATE TABLE t1 ( 659$column_list 660) 661PARTITION BY RANGE(f_int1) PARTITIONS 2 662SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2 663( PARTITION part1 VALUES LESS THAN (1000) 664 (SUBPARTITION subpart11, SUBPARTITION subpart12), 665 PARTITION part2 VALUES LESS THAN $MAX_VALUE 666 (SUBPARTITION subpart21 ) 667); 668--echo # 4.3.4 (negative) number of partitions < number of named partitions 669--error ER_PARSE_ERROR 670eval CREATE TABLE t1 ( 671$column_list 672) 673PARTITION BY HASH(f_int1) PARTITIONS 1 ( PARTITION part1, PARTITION part2 ) ; 674# Wrong number of named subpartitions in first partition 675--error ER_PARSE_ERROR 676eval CREATE TABLE t1 ( 677$column_list 678) 679PARTITION BY RANGE(f_int1) 680SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 1 681( PARTITION part1 VALUES LESS THAN (1000) 682 (SUBPARTITION subpart11, SUBPARTITION subpart12), 683 PARTITION part2 VALUES LESS THAN $MAX_VALUE 684 (SUBPARTITION subpart21, SUBPARTITION subpart22) 685); 686# Wrong number of named subpartitions in non first/non last partition 687--error ER_PARSE_ERROR 688eval CREATE TABLE t1 ( 689$column_list 690) 691PARTITION BY RANGE(f_int1) 692SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 1 693( PARTITION part1 VALUES LESS THAN (1000) 694 (SUBPARTITION subpart11, SUBPARTITION subpart12), 695 PARTITION part2 VALUES LESS THAN (2000) 696 (SUBPARTITION subpart21 ), 697 PARTITION part3 VALUES LESS THAN $MAX_VALUE 698 (SUBPARTITION subpart31, SUBPARTITION subpart32) 699); 700# Wrong number of named subpartitions in last partition 701--error ER_PARSE_ERROR 702eval CREATE TABLE t1 ( 703$column_list 704) 705PARTITION BY RANGE(f_int1) 706SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 1 707( PARTITION part1 VALUES LESS THAN (1000) 708 (SUBPARTITION subpart11, SUBPARTITION subpart12), 709 PARTITION part2 VALUES LESS THAN $MAX_VALUE 710 (SUBPARTITION subpart21, SUBPARTITION subpart22) 711); 712 713 714--echo 715--echo #======================================================================== 716--echo # 5. Checks of logical partition/subpartition name 717--echo # file name clashes during CREATE TABLE 718--echo #======================================================================== 719--disable_warnings 720DROP TABLE IF EXISTS t1; 721--enable_warnings 722 723--echo #------------------------------------------------------------------------ 724--echo # 5.1 (negative) A partition/subpartition name used more than once 725--echo #------------------------------------------------------------------------ 726--echo # 5.1.1 duplicate partition name 727--error ER_SAME_NAME_PARTITION 728eval CREATE TABLE t1 ( 729$column_list 730) 731PARTITION BY HASH(f_int1) (PARTITION part1, PARTITION part1); 732# 733--echo # 5.1.2 duplicate subpartition name 734# Bug#15408 Partitions: subpartition names are not unique 735--error ER_SAME_NAME_PARTITION 736eval CREATE TABLE t1 ( 737$column_list 738) 739PARTITION BY RANGE(f_int1) 740SUBPARTITION BY HASH(f_int1) 741( PARTITION part1 VALUES LESS THAN (1000) 742 (SUBPARTITION subpart11, SUBPARTITION subpart11) 743); 744 745# FIXME Implement testcases with filename problems 746# existing file of other table --- partition/subpartition file name 747# partition/subpartition file name --- file of the same table 748 749