323 lines
8.0 KiB
Plaintext
323 lines
8.0 KiB
Plaintext
-- source include/have_ndb.inc
|
|
#
|
|
# Simple test for the partition storage engine
|
|
# Focuses on range partitioning tests
|
|
#
|
|
#-- source include/have_partition.inc
|
|
|
|
--disable_query_log
|
|
# This test uses "partition by range" which is a feature only available
|
|
# when turning on MySQL's system variable "new", this functionality
|
|
# is certainly not new but still only enabled when "new" is on.
|
|
set new=on;
|
|
--enable_query_log
|
|
|
|
#
|
|
# Partition by range, basic
|
|
#
|
|
CREATE TABLE t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key(a,b),
|
|
index (a))
|
|
engine = ndb
|
|
partition by range (a)
|
|
partitions 3
|
|
(partition x1 values less than (5),
|
|
partition x2 values less than (10),
|
|
partition x3 values less than (20));
|
|
|
|
# Simple insert and verify test
|
|
INSERT into t1 values (1, 1, 1);
|
|
INSERT into t1 values (6, 1, 1);
|
|
INSERT into t1 values (10, 1, 1);
|
|
INSERT into t1 values (15, 1, 1);
|
|
|
|
--replace_column 16 # 19 # 20 #
|
|
select * from information_schema.partitions where table_name= 't1';
|
|
|
|
select * from t1 order by a;
|
|
|
|
select * from t1 where a=1 order by a;
|
|
select * from t1 where a=15 and b=1 order by a;
|
|
select * from t1 where a=21 and b=1 order by a;
|
|
select * from t1 where a=21 order by a;
|
|
select * from t1 where a in (1,6,10,21) order by a;
|
|
select * from t1 where b=1 and a in (1,6,10,21) order by a;
|
|
|
|
# BUG#33061: ORDER BY DESC becomes ASC in NDB partition pruning to one partition
|
|
INSERT into t1 values (1, 2, 2);
|
|
select max(b) from t1 where a = 1;
|
|
select b from t1 where a = 1 order by b desc;
|
|
|
|
drop table t1;
|
|
|
|
#
|
|
# Partition by range, basic
|
|
#
|
|
CREATE TABLE t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key(b),
|
|
unique (a))
|
|
engine = ndb
|
|
partition by range (b)
|
|
partitions 3
|
|
(partition x1 values less than (5),
|
|
partition x2 values less than (10),
|
|
partition x3 values less than (20));
|
|
|
|
# Simple insert and verify test
|
|
INSERT into t1 values (1, 1, 1);
|
|
INSERT into t1 values (2, 6, 1);
|
|
INSERT into t1 values (3, 10, 1);
|
|
INSERT into t1 values (4, 15, 1);
|
|
|
|
select * from t1 order by a;
|
|
UPDATE t1 set a = 5 WHERE b = 15;
|
|
select * from t1 order by a;
|
|
UPDATE t1 set a = 6 WHERE a = 5;
|
|
select * from t1 order by a;
|
|
|
|
select * from t1 where b=1 order by b;
|
|
select * from t1 where b=15 and a=1 order by b;
|
|
select * from t1 where b=21 and a=1 order by b;
|
|
select * from t1 where b=21 order by b;
|
|
select * from t1 where b in (1,6,10,21) order by b;
|
|
select * from t1 where a in (1,2,5,6) order by b;
|
|
select * from t1 where a=1 and b in (1,6,10,21) order by b;
|
|
|
|
# test for bug#42443
|
|
select * from t1 where b=6;
|
|
DELETE from t1 WHERE b = 6;
|
|
select * from t1 where b=6;
|
|
select * from t1 where a=6;
|
|
UPDATE t1 SET c=1001 WHERE a=6;
|
|
select * from t1 where a=6;
|
|
DELETE from t1 WHERE a = 6;
|
|
select * from t1 where a=6;
|
|
|
|
#
|
|
# Test that explicit partition info _is_ shown in show create table
|
|
# result _should_ contain (PARTITION x1 ... etc)
|
|
#
|
|
--disable_warnings
|
|
show create table t1;
|
|
--enable_warnings
|
|
|
|
drop table t1;
|
|
|
|
#
|
|
# Bug #17499, #17687
|
|
# Alter partitioned NDB table causes mysqld to core
|
|
#
|
|
|
|
CREATE TABLE t1
|
|
(id MEDIUMINT NOT NULL,
|
|
b1 BIT(8),
|
|
vc VARCHAR(255),
|
|
bc CHAR(255),
|
|
d DECIMAL(10,4) DEFAULT 0,
|
|
f FLOAT DEFAULT 0,
|
|
total BIGINT UNSIGNED,
|
|
y YEAR,
|
|
t DATE PRIMARY KEY) ENGINE=NDB
|
|
PARTITION BY RANGE (YEAR(t))
|
|
(PARTITION p0 VALUES LESS THAN (1901),
|
|
PARTITION p1 VALUES LESS THAN (1946),
|
|
PARTITION p2 VALUES LESS THAN (1966),
|
|
PARTITION p3 VALUES LESS THAN (1986),
|
|
PARTITION p4 VALUES LESS THAN (2005),
|
|
PARTITION p5 VALUES LESS THAN MAXVALUE);
|
|
|
|
INSERT INTO t1 VALUES (0,NULL,NULL,NULL,NULL,NULL,NULL,NULL, '2009-05-15');
|
|
SELECT * FROM t1;
|
|
ALTER TABLE t1 ENGINE=INNODB;
|
|
SELECT * FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
CREATE LOGFILE GROUP lg1
|
|
ADD UNDOFILE 'undofile.dat'
|
|
INITIAL_SIZE 16M
|
|
UNDO_BUFFER_SIZE=1M
|
|
ENGINE=NDB;
|
|
|
|
CREATE TABLESPACE ts1
|
|
ADD DATAFILE 'datafile.dat'
|
|
USE LOGFILE GROUP lg1
|
|
INITIAL_SIZE 12M
|
|
EXTENT_SIZE 256k
|
|
ENGINE NDB;
|
|
|
|
CREATE TABLE test.t1 (
|
|
a1 INT,
|
|
a2 TEXT NOT NULL,
|
|
a3 BIT NOT NULL,
|
|
a4 DECIMAL(8,3),
|
|
a5 INT NOT NULL,
|
|
a6 INT,
|
|
PRIMARY KEY(a1))
|
|
TABLESPACE ts1 STORAGE DISK ENGINE=NDB
|
|
PARTITION BY LIST (a1)
|
|
(PARTITION p0 VALUES IN (1,2,3,4,5),
|
|
PARTITION p1 VALUES IN (6,7,8,9, 10),
|
|
PARTITION p2 VALUES IN (11, 12, 13, 14, 15));
|
|
|
|
# Alter table directly without any statements inbetween
|
|
ALTER TABLE test.t1 DROP COLUMN a6;
|
|
ALTER TABLE test.t1 ADD COLUMN a6 VARCHAR(255);
|
|
|
|
let $j= 15;
|
|
--disable_query_log
|
|
while ($j)
|
|
{
|
|
eval INSERT INTO test.t1 VALUES ($j, "Tested Remotely from Texas, USA",
|
|
b'1',$j.00,$j+1,"By NIK $j");
|
|
dec $j;
|
|
}
|
|
--enable_query_log
|
|
SELECT COUNT(*) FROM test.t1;
|
|
|
|
ALTER TABLE test.t1 DROP COLUMN a4;
|
|
SELECT COUNT(*) FROM test.t1;
|
|
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE test.t1 (
|
|
a1 INT,
|
|
a2 TEXT NOT NULL,
|
|
a3 BIT NOT NULL,
|
|
a4 DECIMAL(8,3),
|
|
a5 INT NOT NULL,
|
|
a6 VARCHAR(255),
|
|
PRIMARY KEY(a1))
|
|
TABLESPACE ts1 STORAGE DISK ENGINE=NDB
|
|
PARTITION BY HASH(a1)
|
|
PARTITIONS 4;
|
|
|
|
let $j= 15;
|
|
--disable_query_log
|
|
while ($j)
|
|
{
|
|
eval INSERT INTO test.t1 VALUES ($j, "Tested Remotely from Texas, USA",
|
|
b'1',$j.00,$j+1,"By NIK $j");
|
|
dec $j;
|
|
}
|
|
--enable_query_log
|
|
SELECT COUNT(*) FROM test.t1;
|
|
|
|
ALTER TABLE test.t1 DROP COLUMN a4;
|
|
SELECT COUNT(*) FROM test.t1;
|
|
|
|
DROP TABLE t1;
|
|
|
|
ALTER TABLESPACE ts1
|
|
DROP DATAFILE 'datafile.dat';
|
|
DROP TABLESPACE ts1;
|
|
DROP LOGFILE GROUP lg1 ENGINE=NDB;
|
|
|
|
|
|
#
|
|
# Bug #17701 ALTER TABLE t1 ADD PARTITION for PARTITION BY LIST hangs test
|
|
#
|
|
|
|
CREATE TABLE t1
|
|
(id MEDIUMINT NOT NULL PRIMARY KEY,
|
|
b1 BIT(8),
|
|
vc VARCHAR(255),
|
|
bc CHAR(255),
|
|
d DECIMAL(10,4) DEFAULT 0,
|
|
f FLOAT DEFAULT 0,
|
|
total BIGINT UNSIGNED,
|
|
y YEAR,
|
|
t DATE) ENGINE=NDB
|
|
PARTITION BY LIST(id)
|
|
(PARTITION p0 VALUES IN (2, 4),
|
|
PARTITION p1 VALUES IN (42, 142));
|
|
|
|
INSERT INTO t1 VALUES (2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
|
|
SELECT * FROM t1;
|
|
ALTER TABLE t1 ADD PARTITION
|
|
(PARTITION p2 VALUES IN (412));
|
|
SELECT * FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
#
|
|
# Bug #17806 Update on NDB table with list partition causes mysqld to core
|
|
# Bug #16385 Partitions: crash when updating a range partitioned NDB table
|
|
#
|
|
CREATE TABLE t1 (
|
|
a int not null PRIMARY KEY,
|
|
b int not null,
|
|
c int not null) engine=ndb
|
|
partition by list(a)
|
|
partitions 2
|
|
(partition x123 values in (1,5,6),
|
|
partition x234 values in (4,7,8));
|
|
INSERT into t1 VALUES (5,1,1);
|
|
select * from t1;
|
|
UPDATE t1 SET a=8 WHERE a=5 AND b=1;
|
|
select * from t1;
|
|
drop table t1;
|
|
|
|
CREATE TABLE t1 ( f1 INTEGER PRIMARY KEY, f2 char(20)) engine=ndb
|
|
PARTITION BY RANGE(f1)
|
|
( PARTITION part1 VALUES LESS THAN (2),
|
|
PARTITION part2 VALUES LESS THAN (1000));
|
|
INSERT INTO t1 VALUES(1, '---1---');
|
|
INSERT INTO t1 VALUES(2, '---2---');
|
|
select * from t1 order by f1;
|
|
UPDATE t1 SET f1 = f1 + 4 WHERE f1 = 2;
|
|
select * from t1 order by f1;
|
|
UPDATE t1 SET f1 = f1 + 4 WHERE f1 = 1;
|
|
select * from t1 order by f1;
|
|
drop table t1;
|
|
|
|
|
|
# Check partitioning at NdbApi level for range partitioned table
|
|
#
|
|
create table t1 ( a int, b int, c int, primary key (a,b)) engine=ndb
|
|
partition by range (a)
|
|
(partition part0 values less than (3),
|
|
partition part1 values less than (6));
|
|
|
|
insert into t1 values (0, 0, 0);
|
|
insert into t1 values (0, 1, 1);
|
|
insert into t1 values (0, 2, 2);
|
|
insert into t1 values (1, 0, 3);
|
|
insert into t1 values (1, 1, 4);
|
|
insert into t1 values (1, 2, 5);
|
|
insert into t1 values (4, 0, 6);
|
|
insert into t1 values (4, 1, 7);
|
|
insert into t1 values (4, 2, 8);
|
|
|
|
--echo All partitions scan
|
|
--source suite/ndb/include/ndb_init_scan_counts.inc
|
|
select * from t1 order by c;
|
|
--source suite/ndb/include/ndb_scan_counts.inc
|
|
|
|
--echo Single partition scan
|
|
--source suite/ndb/include/ndb_init_scan_counts.inc
|
|
select * from t1 where a=0 order by c;
|
|
--source suite/ndb/include/ndb_scan_counts.inc
|
|
|
|
--echo Single partition scan
|
|
--source suite/ndb/include/ndb_init_scan_counts.inc
|
|
select * from t1 where a=4 order by c;
|
|
--source suite/ndb/include/ndb_scan_counts.inc
|
|
|
|
--echo Single partition scan
|
|
--source suite/ndb/include/ndb_init_scan_counts.inc
|
|
select * from t1 where a<3 order by c;
|
|
--source suite/ndb/include/ndb_scan_counts.inc
|
|
|
|
--echo MRR single partition scan
|
|
--source suite/ndb/include/ndb_init_scan_counts.inc
|
|
select * from t1 where a in (0, 2) order by c;
|
|
--source suite/ndb/include/ndb_scan_counts.inc
|
|
|
|
drop table t1;
|
|
|