partition_innodb.test 1.92 KB
Newer Older
unknown's avatar
unknown committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49
-- source include/have_innodb.inc

SET @max_row = 20;
let $engine= 'InnoDB';
let $MAX_VALUE= (2147483646);

let $max_row= `SELECT @max_row`;

# Column list with definition for all tables to be checked
let $column_list= f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000);

let $sub_part_no= 3;
--disable_warnings
DROP TABLE IF EXISTS t0_template;
--enable_warnings
eval CREATE TABLE t0_template (
$column_list ,
PRIMARY KEY(f_int1))
ENGINE = MEMORY;
let $num= `SELECT @max_row`;
while ($num)
{
  eval INSERT INTO t0_template
SET f_int1 = $num, f_int2 = $num, f_char1 = '$num', f_char2 = '$num',
f_charbig = '===$num===';
  dec $num;
}
# 1.   Create the table
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
eval CREATE TABLE t1 (f_date DATE, f_varchar VARCHAR(30)) engine=$engine;
# 2.   Fill the table t1 with records
INSERT INTO t1 (f_date, f_varchar)
SELECT CONCAT(CAST((f_int1 + 999) AS CHAR),'-02-10'), CAST(f_char1 AS CHAR)
FROM t0_template
WHERE f_int1 + 999 BETWEEN 1000 AND 9999;
# 3.   Calculate the number of inserted records.
SELECT IF(9999 - 1000 + 1 > @max_row, @max_row , 9999 - 1000 + 1)
       INTO @exp_row_count;
# DEBUG SELECT @exp_row_count;
# 4.   Print the layout, check Readability
ALTER TABLE t1 PARTITION BY HASH(CAST(YEAR(f_date) AS SIGNED INTEGER));
--echo #  1.1.5 Add two named partitions + test
ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
50 51 52 53 54 55 56 57 58 59
drop table t1;

CREATE TABLE t1 (f_date DATE, f_varchar VARCHAR(30))
ENGINE=InnoDB
PARTITION BY HASH(CAST(YEAR(f_date) AS SIGNED INTEGER));

--echo # This statement crashes the server.
--echo #   CREATE partitioned table with three partitions in one step
--echo #   would be harmless.
ALTER TABLE t1 ADD PARTITION PARTITIONS 1;
unknown's avatar
unknown committed
60 61 62 63 64 65 66 67 68

--disable_warnings
DROP VIEW  IF EXISTS v1;
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t0_aux;
DROP TABLE IF EXISTS t0_definition;
DROP TABLE IF EXISTS t0_template;
--enable_warnings