[Commits] a01d107: More tests

Oleksandr Byelkin sanja at mariadb.com
Fri Sep 2 16:04:40 EEST 2016


revision-id: a01d107c03a1d3eb8b9d2ea1c235e5fdf6f783cd (mariadb-10.2.1-59-ga01d107)
parent(s): 3fc69b08a685145c19e58b9cd1cd4a093a273a88
committer: Oleksandr Byelkin
timestamp: 2016-09-02 15:04:40 +0200
message:

More tests

---
 mysql-test/r/partition_default.result | 267 ++++++++++++++++++++++++++++++++++
 mysql-test/t/partition_default.test   | 138 ++++++++++++++++++
 2 files changed, 405 insertions(+)

diff --git a/mysql-test/r/partition_default.result b/mysql-test/r/partition_default.result
index e4ce9b6..cfd214a 100644
--- a/mysql-test/r/partition_default.result
+++ b/mysql-test/r/partition_default.result
@@ -252,6 +252,83 @@ explain partitions select * from t1 where a=10 and b=10 or a=20 and b=20;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	p1,p0	ALL	NULL	NULL	NULL	NULL	2	Using where
 drop table t1;
+create table t1 (a int, b int);
+insert into t1 values (10,10),(2,5),(0,0);
+select * from t1;
+a	b
+10	10
+2	5
+0	0
+alter table t1
+PARTITION BY LIST (a+b)
+(
+PARTITION p2 VALUES IN (1,2,3,7),
+PARTITION p1 VALUES IN (21,0),
+PARTITION p0 DEFAULT
+)
+;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY LIST (a+b)
+(PARTITION p2 VALUES IN (1,2,3,7) ENGINE = MyISAM,
+ PARTITION p1 VALUES IN (21,0) ENGINE = MyISAM,
+ PARTITION p0 DEFAULT ENGINE = MyISAM) */
+select * from t1;
+a	b
+2	5
+0	0
+10	10
+explain partitions select * from t1 where a=2 and b=5;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p2	system	NULL	NULL	NULL	NULL	1	
+explain partitions select * from t1 where a=10 and b=10;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0	system	NULL	NULL	NULL	NULL	1	
+drop table t1;
+create table t1 (a int, b int);
+insert into t1 values (10,10),(2,5),(0,0);
+select * from t1;
+a	b
+10	10
+2	5
+0	0
+alter table t1
+PARTITION BY LIST (a+5)
+(
+PARTITION p2 VALUES IN (1,2,3,7),
+PARTITION p1 VALUES IN (0),
+PARTITION p0 DEFAULT
+)
+;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY LIST (a+5)
+(PARTITION p2 VALUES IN (1,2,3,7) ENGINE = MyISAM,
+ PARTITION p1 VALUES IN (0) ENGINE = MyISAM,
+ PARTITION p0 DEFAULT ENGINE = MyISAM) */
+select * from t1;
+a	b
+2	5
+10	10
+0	0
+explain partitions select * from t1 where a>=2;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p2,p1,p0	ALL	NULL	NULL	NULL	NULL	3	Using where
+explain partitions select * from t1 where a>=2 and a<=3;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p2,p0	ALL	NULL	NULL	NULL	NULL	3	Using where
+explain partitions select * from t1 where a=10;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0	ALL	NULL	NULL	NULL	NULL	2	Using where
+drop table t1;
 CREATE TABLE t1 (a DATE, KEY(a))
 PARTITION BY LIST (TO_DAYS(a))
 (PARTITION `pDEF` DEFAULT,
@@ -677,3 +754,193 @@ a
 1001-00-00
 1001-01-01
 DROP TABLE t1;
+create table t1 (a int, b int);
+insert into t1 values (10,10),(2,5),(0,0);
+select * from t1;
+a	b
+10	10
+2	5
+0	0
+alter table t1
+PARTITION BY LIST (a)
+(
+PARTITION p2 VALUES IN (1,2,3),
+PARTITION p1 VALUES IN (20,0),
+PARTITION p0 DEFAULT
+)
+;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY LIST (a)
+(PARTITION p2 VALUES IN (1,2,3) ENGINE = MyISAM,
+ PARTITION p1 VALUES IN (20,0) ENGINE = MyISAM,
+ PARTITION p0 DEFAULT ENGINE = MyISAM) */
+select * from t1;
+a	b
+2	5
+0	0
+10	10
+explain partitions select * from t1 where a=2 and b=5;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p2	system	NULL	NULL	NULL	NULL	1	
+explain partitions select * from t1 where a=10 and b=10;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0	system	NULL	NULL	NULL	NULL	1	
+alter table t1
+PARTITION BY LIST (a)
+(
+PARTITION p2 VALUES IN (1,2,3),
+PARTITION p1 VALUES IN (20,0),
+PARTITION p0 VALUES IN (10)
+)
+;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY LIST (a)
+(PARTITION p2 VALUES IN (1,2,3) ENGINE = MyISAM,
+ PARTITION p1 VALUES IN (20,0) ENGINE = MyISAM,
+ PARTITION p0 VALUES IN (10) ENGINE = MyISAM) */
+select * from t1;
+a	b
+2	5
+0	0
+10	10
+explain partitions select * from t1 where a=2 and b=5;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p2	system	NULL	NULL	NULL	NULL	1	
+explain partitions select * from t1 where a=10 and b=10;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0	system	NULL	NULL	NULL	NULL	1	
+alter table t1
+PARTITION BY LIST (a)
+(
+PARTITION p2 DEFAULT,
+PARTITION p1 VALUES IN (20,0),
+PARTITION p0 VALUES IN (10)
+)
+;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY LIST (a)
+(PARTITION p2 DEFAULT ENGINE = MyISAM,
+ PARTITION p1 VALUES IN (20,0) ENGINE = MyISAM,
+ PARTITION p0 VALUES IN (10) ENGINE = MyISAM) */
+select * from t1;
+a	b
+2	5
+0	0
+10	10
+explain partitions select * from t1 where a=2 and b=5;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p2	system	NULL	NULL	NULL	NULL	1	
+explain partitions select * from t1 where a=10 and b=10;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0	system	NULL	NULL	NULL	NULL	1	
+drop table t1;
+create table t1 (a int, b int);
+insert into t1 values (10,10),(2,5),(0,0);
+select * from t1;
+a	b
+10	10
+2	5
+0	0
+alter table t1
+PARTITION BY LIST COLUMNS(a,b)
+(
+PARTITION p2 VALUES IN ((1,4),(2,5),(3,6)),
+PARTITION p1 VALUES IN ((1,1),(0,0)),
+PARTITION p0 DEFAULT
+)
+;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!50500 PARTITION BY LIST  COLUMNS(a,b)
+(PARTITION p2 VALUES IN ((1,4),(2,5),(3,6)) ENGINE = MyISAM,
+ PARTITION p1 VALUES IN ((1,1),(0,0)) ENGINE = MyISAM,
+ PARTITION p0 DEFAULT ENGINE = MyISAM) */
+select * from t1;
+a	b
+2	5
+0	0
+10	10
+explain partitions select * from t1 where a=2 and b=5;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p2	system	NULL	NULL	NULL	NULL	1	
+explain partitions select * from t1 where a=10 and b=10;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0	system	NULL	NULL	NULL	NULL	1	
+alter table t1
+PARTITION BY LIST COLUMNS(a,b)
+(
+PARTITION p2 VALUES IN ((1,4),(2,5),(3,6)),
+PARTITION p1 VALUES IN ((1,1),(0,0)),
+PARTITION p0 VALUES IN ((10,10))
+)
+;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!50500 PARTITION BY LIST  COLUMNS(a,b)
+(PARTITION p2 VALUES IN ((1,4),(2,5),(3,6)) ENGINE = MyISAM,
+ PARTITION p1 VALUES IN ((1,1),(0,0)) ENGINE = MyISAM,
+ PARTITION p0 VALUES IN ((10,10)) ENGINE = MyISAM) */
+select * from t1;
+a	b
+2	5
+0	0
+10	10
+explain partitions select * from t1 where a=2 and b=5;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p2	system	NULL	NULL	NULL	NULL	1	
+explain partitions select * from t1 where a=10 and b=10;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0	system	NULL	NULL	NULL	NULL	1	
+alter table t1
+PARTITION BY LIST COLUMNS(a,b)
+(
+PARTITION p2 DEFAULT,
+PARTITION p1 VALUES IN ((1,1),(0,0)),
+PARTITION p0 VALUES IN ((10,10))
+)
+;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!50500 PARTITION BY LIST  COLUMNS(a,b)
+(PARTITION p2 DEFAULT ENGINE = MyISAM,
+ PARTITION p1 VALUES IN ((1,1),(0,0)) ENGINE = MyISAM,
+ PARTITION p0 VALUES IN ((10,10)) ENGINE = MyISAM) */
+select * from t1;
+a	b
+2	5
+0	0
+10	10
+explain partitions select * from t1 where a=2 and b=5;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p2	system	NULL	NULL	NULL	NULL	1	
+explain partitions select * from t1 where a=10 and b=10;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0	system	NULL	NULL	NULL	NULL	1	
+drop table t1;
diff --git a/mysql-test/t/partition_default.test b/mysql-test/t/partition_default.test
index dd25fea..9faf777 100644
--- a/mysql-test/t/partition_default.test
+++ b/mysql-test/t/partition_default.test
@@ -160,6 +160,51 @@ select * from t1 where a=10 and b=10 or a=20 and b=20;
 explain partitions select * from t1 where a=10 and b=10 or a=20 and b=20;
 drop table t1;
 
+#
+# partition pruning with expressions
+#
+create table t1 (a int, b int);
+
+insert into t1 values (10,10),(2,5),(0,0);
+
+select * from t1;
+
+alter table t1
+  PARTITION BY LIST (a+b)
+  (
+    PARTITION p2 VALUES IN (1,2,3,7),
+    PARTITION p1 VALUES IN (21,0),
+    PARTITION p0 DEFAULT
+  )
+;
+show create table t1;
+select * from t1;
+explain partitions select * from t1 where a=2 and b=5;
+explain partitions select * from t1 where a=10 and b=10;
+drop table t1;
+
+create table t1 (a int, b int);
+
+insert into t1 values (10,10),(2,5),(0,0);
+
+select * from t1;
+
+alter table t1
+  PARTITION BY LIST (a+5)
+  (
+    PARTITION p2 VALUES IN (1,2,3,7),
+    PARTITION p1 VALUES IN (0),
+    PARTITION p0 DEFAULT
+  )
+;
+show create table t1;
+select * from t1;
+explain partitions select * from t1 where a>=2;
+explain partitions select * from t1 where a>=2 and a<=3;
+explain partitions select * from t1 where a=10;
+drop table t1;
+
+
 
 CREATE TABLE t1 (a DATE, KEY(a))
 PARTITION BY LIST (TO_DAYS(a))
@@ -198,3 +243,96 @@ INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'),
 ALTER TABLE t1 DROP KEY a;
 --source include/partition_date_range.inc
 DROP TABLE t1;
+
+#
+# ALTER TABLE test
+#
+
+create table t1 (a int, b int);
+
+insert into t1 values (10,10),(2,5),(0,0);
+
+select * from t1;
+
+alter table t1
+  PARTITION BY LIST (a)
+  (
+    PARTITION p2 VALUES IN (1,2,3),
+    PARTITION p1 VALUES IN (20,0),
+    PARTITION p0 DEFAULT
+  )
+;
+show create table t1;
+select * from t1;
+explain partitions select * from t1 where a=2 and b=5;
+explain partitions select * from t1 where a=10 and b=10;
+alter table t1
+  PARTITION BY LIST (a)
+  (
+    PARTITION p2 VALUES IN (1,2,3),
+    PARTITION p1 VALUES IN (20,0),
+    PARTITION p0 VALUES IN (10)
+  )
+;
+show create table t1;
+select * from t1;
+explain partitions select * from t1 where a=2 and b=5;
+explain partitions select * from t1 where a=10 and b=10;
+alter table t1
+  PARTITION BY LIST (a)
+  (
+    PARTITION p2 DEFAULT,
+    PARTITION p1 VALUES IN (20,0),
+    PARTITION p0 VALUES IN (10)
+  )
+;
+show create table t1;
+select * from t1;
+explain partitions select * from t1 where a=2 and b=5;
+explain partitions select * from t1 where a=10 and b=10;
+drop table t1;
+
+
+create table t1 (a int, b int);
+
+insert into t1 values (10,10),(2,5),(0,0);
+
+select * from t1;
+
+alter table t1
+  PARTITION BY LIST COLUMNS(a,b)
+  (
+    PARTITION p2 VALUES IN ((1,4),(2,5),(3,6)),
+    PARTITION p1 VALUES IN ((1,1),(0,0)),
+    PARTITION p0 DEFAULT
+  )
+;
+show create table t1;
+select * from t1;
+explain partitions select * from t1 where a=2 and b=5;
+explain partitions select * from t1 where a=10 and b=10;
+alter table t1
+  PARTITION BY LIST COLUMNS(a,b)
+  (
+    PARTITION p2 VALUES IN ((1,4),(2,5),(3,6)),
+    PARTITION p1 VALUES IN ((1,1),(0,0)),
+    PARTITION p0 VALUES IN ((10,10))
+  )
+;
+show create table t1;
+select * from t1;
+explain partitions select * from t1 where a=2 and b=5;
+explain partitions select * from t1 where a=10 and b=10;
+alter table t1
+  PARTITION BY LIST COLUMNS(a,b)
+  (
+    PARTITION p2 DEFAULT,
+    PARTITION p1 VALUES IN ((1,1),(0,0)),
+    PARTITION p0 VALUES IN ((10,10))
+  )
+;
+show create table t1;
+select * from t1;
+explain partitions select * from t1 where a=2 and b=5;
+explain partitions select * from t1 where a=10 and b=10;
+drop table t1;


More information about the commits mailing list