summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/partition_hash.result
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
commit3f619478f796eddbba6e39502fe941b285dd97b1 (patch)
treee2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/main/partition_hash.result
parentInitial commit. (diff)
downloadmariadb-3f619478f796eddbba6e39502fe941b285dd97b1.tar.xz
mariadb-3f619478f796eddbba6e39502fe941b285dd97b1.zip
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/main/partition_hash.result')
-rw-r--r--mysql-test/main/partition_hash.result202
1 files changed, 202 insertions, 0 deletions
diff --git a/mysql-test/main/partition_hash.result b/mysql-test/main/partition_hash.result
new file mode 100644
index 00000000..9a63db3f
--- /dev/null
+++ b/mysql-test/main/partition_hash.result
@@ -0,0 +1,202 @@
+drop table if exists t1;
+CREATE TABLE t1 (c1 INT)
+PARTITION BY HASH (c1)
+PARTITIONS 15;
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+ALTER TABLE t1 COALESCE PARTITION 13;
+DROP TABLE t1;
+CREATE TABLE t1 (c1 INT)
+PARTITION BY LINEAR HASH (c1)
+PARTITIONS 5;
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+ALTER TABLE t1 COALESCE PARTITION 3;
+DROP TABLE t1;
+create table t1 (a int unsigned)
+partition by hash(a div 2)
+partitions 4;
+insert into t1 values (null),(0),(1),(2),(3),(4),(5),(6),(7);
+select * from t1 where a < 0;
+a
+select * from t1 where a is null or (a >= 5 and a <= 7);
+a
+NULL
+5
+6
+7
+select * from t1 where a is null;
+a
+NULL
+select * from t1 where a is not null;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+select * from t1 where a >= 1 and a < 3;
+a
+1
+2
+select * from t1 where a >= 3 and a <= 5;
+a
+3
+4
+5
+select * from t1 where a > 2 and a < 4;
+a
+3
+select * from t1 where a > 3 and a <= 6;
+a
+4
+5
+6
+select * from t1 where a > 5;
+a
+6
+7
+select * from t1 where a >= 1 and a <= 5;
+a
+1
+2
+3
+4
+5
+explain partitions select * from t1 where a < 0;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p0,p1,p2,p3 ALL NULL NULL NULL NULL 9 Using where
+explain partitions select * from t1 where a is null or (a >= 5 and a <= 7);
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p0,p2,p3 ALL NULL NULL NULL NULL 7 Using where
+explain partitions select * from t1 where a is null;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 3 Using where
+explain partitions select * from t1 where a is not null;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p0,p1,p2,p3 ALL NULL NULL NULL NULL 9 Using where
+explain partitions select * from t1 where a >= 1 and a < 3;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 5 Using where
+explain partitions select * from t1 where a >= 3 and a <= 5;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p1,p2 ALL NULL NULL NULL NULL 4 Using where
+explain partitions select * from t1 where a > 2 and a < 4;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p1 ALL NULL NULL NULL NULL 2 Using where
+explain partitions select * from t1 where a > 3 and a <= 6;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p2,p3 ALL NULL NULL NULL NULL 4 Using where
+explain partitions select * from t1 where a > 5;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p0,p1,p2,p3 ALL NULL NULL NULL NULL 9 Using where
+explain partitions select * from t1 where a >= 1 and a <= 5;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p0,p1,p2 ALL NULL NULL NULL NULL 7 Using where
+drop table t1;
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by hash (a + 2)
+partitions 3
+(partition x1 tablespace ts1,
+partition x2 tablespace ts2,
+partition x3 tablespace ts3);
+insert into t1 values (1,1,1);
+insert into t1 values (2,1,1);
+insert into t1 values (3,1,1);
+insert into t1 values (4,1,1);
+insert into t1 values (5,1,1);
+select * from t1;
+a b c
+1 1 1
+4 1 1
+2 1 1
+5 1 1
+3 1 1
+update t1 set c=3 where b=1;
+select * from t1;
+a b c
+1 1 3
+4 1 3
+2 1 3
+5 1 3
+3 1 3
+select b from t1 where a=3;
+b
+1
+select b,c from t1 where a=1 AND b=1;
+b c
+1 3
+delete from t1 where a=1;
+delete from t1 where c=3;
+select * from t1;
+a b c
+ALTER TABLE t1
+partition by hash (a + 3)
+partitions 3
+(partition x1 tablespace ts1,
+partition x2 tablespace ts2,
+partition x3 tablespace ts3);
+select * from t1;
+a b c
+drop table t1;
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by hash (a)
+(partition x1);
+drop table t1;
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by key (a)
+(partition x1);
+drop table t1;
+CREATE TABLE t1 (f1 INTEGER, f2 char(20)) ENGINE = 'MYISAM' PARTITION BY HASH(f1) PARTITIONS 2;
+INSERT INTO t1 SET f1 = 0 - 1, f2 = '#######';
+select * from t1;
+f1 f2
+-1 #######
+drop table t1;
+set sql_mode="";
+CREATE TABLE t1 (s1 int) ENGINE=BLACKHOLE PARTITION BY HASH (s1);
+INSERT INTO t1 VALUES (0);
+DROP TABLE t1;
+set sql_mode=default;
+create table t1 (c1 int DEFAULT NULL,
+c2 varchar (30) DEFAULT NULL,
+c3 date DEFAULT NULL)
+engine = myisam
+partition by hash (to_days(c3))
+partitions 12;
+insert into t1 values
+(136,'abc','2002-01-05'),(142,'abc','2002-02-14'),(162,'abc','2002-06-28'),
+(182,'abc','2002-11-09'),(158,'abc','2002-06-01'),(184,'abc','2002-11-22');
+select * from t1;
+c1 c2 c3
+136 abc 2002-01-05
+158 abc 2002-06-01
+142 abc 2002-02-14
+162 abc 2002-06-28
+182 abc 2002-11-09
+184 abc 2002-11-22
+select * from t1 where c3 between '2002-01-01' and '2002-12-31';
+c1 c2 c3
+136 abc 2002-01-05
+158 abc 2002-06-01
+142 abc 2002-02-14
+162 abc 2002-06-28
+182 abc 2002-11-09
+184 abc 2002-11-22
+drop table t1;
+CREATE TABLE t1 (c1 INT) ENGINE=MyISAM PARTITION BY HASH(c1) PARTITIONS 1;
+INSERT DELAYED INTO t1 VALUES (1);
+DROP TABLE t1;