partition_order.test

开启mysql的远程连接的方法 mysql-noinstall-5.1.6-alpha-win32.zip

#--disable_abort_on_error
#
# Simple test for the partition storage engine
# Focuses on tests of ordered index read
# 
-- source include/have_partition.inc

--disable_warnings
drop table if exists t1;
--enable_warnings

#
# Ordered index read, int type
#
CREATE TABLE t1 (
a int not null,
b int not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
 partition x2 values less than (100));

# Insert a couple of tuples
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);

select * from t1 order by b;

select * from t1 force index (b) where b > 0 order by b;

drop table t1;

#
# Ordered index read, unsigned int type
#
CREATE TABLE t1 (
a int not null,
b int unsigned not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
 partition x2 values less than (100));

# Insert a couple of tuples
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);

select * from t1 force index (b) where b > 0 order by b;

drop table t1;

#
# Ordered index read, tiny int type
#
CREATE TABLE t1 (
a int not null,
b tinyint not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
 partition x2 values less than (100));

# Insert a couple of tuples
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);

select * from t1 force index (b) where b > 0 order by b;

drop table t1; 

#
# Ordered index read, unsigned tinyint type
#
CREATE TABLE t1 (
a int not null,
b tinyint unsigned not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
 partition x2 values less than (100));

# Insert a couple of tuples
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);

select * from t1 force index (b) where b > 0 order by b;

drop table t1; 

#
# Ordered index read, smallint type
#
CREATE TABLE t1 (
a int not null,
b smallint not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
 partition x2 values less than (100));

# Insert a couple of tuples
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);

select * from t1 force index (b) where b > 0 order by b;

drop table t1; 

#
# Ordered index read, unsigned smallint type
#
CREATE TABLE t1 (
a int not null,
b smallint unsigned not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
 partition x2 values less than (100));

# Insert a couple of tuples
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);

select * from t1 force index (b) where b > 0 order by b;

drop table t1; 
#
# Ordered index read, mediumint type
#
CREATE TABLE t1 (
a int not null,
b mediumint not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
 partition x2 values less than (100));

# Insert a couple of tuples
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);

select * from t1 force index (b) where b > 0 order by b;

drop table t1; 

#
# Ordered index read, unsigned int type
#
CREATE TABLE t1 (
a int not null,
b mediumint unsigned not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
 partition x2 values less than (100));

# Insert a couple of tuples
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);

select * from t1 force index (b) where b > 0 order by b;

drop table t1; 

#
# Ordered index read, unsigned bigint type
#
CREATE TABLE t1 (
a int not null,
b bigint unsigned not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
 partition x2 values less than (100));

# Insert a couple of tuples
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);

select * from t1 force index (b) where b > 0 order by b;

drop table t1; 

#
# Ordered index read, bigint type
#
CREATE TABLE t1 (
a int not null,
b bigint not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
 partition x2 values less than (100));

# Insert a couple of tuples
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);

select * from t1 force index (b) where b > 0 order by b;

drop table t1; 
#
# Ordered index read, bigint type
#
CREATE TABLE t1 (
a int not null,
b bigint not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
 partition x2 values less than (100));

# Insert a couple of tuples
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);

select * from t1 force index (b) where b > 0 order by b;

drop table t1;

#
# Ordered index read, float type
#
CREATE TABLE t1 (
a int not null,
b float not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
 partition x2 values less than (100));

# Insert a couple of tuples
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);

select * from t1 force index (b) where b > 0 order by b;

drop table t1;

#
# Ordered index read, double type
#
CREATE TABLE t1 (
a int not null,
b double not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
 partition x2 values less than (100));

# Insert a couple of tuples
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);

select * from t1 force index (b) where b > 0 order by b;

drop table t1;

#
# Ordered index read, unsigned double type
#
CREATE TABLE t1 (
a int not null,
b double unsigned not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
 partition x2 values less than (100));

# Insert a couple of tuples
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);

select * from t1 force index (b) where b > 0 order by b;

drop table t1;

#
# Ordered index read, unsigned float type
#
CREATE TABLE t1 (
a int not null,
b float unsigned not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
 partition x2 values less than (100));

# Insert a couple of tuples
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);

select * from t1 force index (b) where b > 0 order by b;

drop table t1;

#
# Ordered index read, double precision type
#
CREATE TABLE t1 (
a int not null,
b double precision not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
 partition x2 values less than (100));

# Insert a couple of tuples
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);

select * from t1 force index (b) where b > 0 order by b;

drop table t1;
#
# Ordered index read, unsigned double precision type
#
CREATE TABLE t1 (
a int not null,
b double precision unsigned not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
 partition x2 values less than (100));

# Insert a couple of tuples
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);

select * from t1 force index (b) where b > 0 order by b;

drop table t1;

#
# Ordered index read, decimal type
#
CREATE TABLE t1 (
a int not null,
b decimal not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
 partition x2 values less than (100));

# Insert a couple of tuples