WL#2506: Information Schema tables for PARTITIONing

  added I_S 'PARTITIONS' table

6 files changed, 233 insertions, 2 deletions

diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
index d15705a25f8..5a7ff176d9e 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -43,6 +43,7 @@ COLUMNS
 COLUMN_PRIVILEGES
 ENGINES
 KEY_COLUMN_USAGE
+PARTITIONS
 PLUGINS
 ROUTINES
 SCHEMATA
@@ -725,7 +726,7 @@ CREATE TABLE t_crashme ( f1 BIGINT);
 CREATE VIEW a1 (t_CRASHME) AS SELECT f1 FROM t_crashme GROUP BY f1;
 CREATE VIEW a2 AS SELECT t_CRASHME FROM a1;
 count(*)
-104
+105
 drop view a2, a1;
 drop table t_crashme;
 select table_schema,table_name, column_name from
@@ -733,6 +734,9 @@ information_schema.columns
 where data_type = 'longtext';
 table_schema	table_name	column_name
 information_schema	COLUMNS	COLUMN_TYPE
+information_schema	PARTITIONS	PARTITION_EXPRESSION
+information_schema	PARTITIONS	SUBPARTITION_EXPRESSION
+information_schema	PARTITIONS	PARTITION_DESCRIPTION
 information_schema	PLUGINS	PLUGIN_DESCRIPTION
 information_schema	ROUTINES	ROUTINE_DEFINITION
 information_schema	ROUTINES	SQL_MODE
@@ -744,6 +748,9 @@ information_schema	VIEWS	VIEW_DEFINITION
 select table_name, column_name, data_type from information_schema.columns
 where data_type = 'datetime';
 table_name	column_name	data_type
+PARTITIONS	CREATE_TIME	datetime
+PARTITIONS	UPDATE_TIME	datetime
+PARTITIONS	CHECK_TIME	datetime
 ROUTINES	CREATED	datetime
 ROUTINES	LAST_ALTERED	datetime
 TABLES	CREATE_TIME	datetime
@@ -786,6 +793,7 @@ TABLE_NAME	COLUMN_NAME	PRIVILEGES
 COLUMNS	TABLE_NAME	select
 COLUMN_PRIVILEGES	TABLE_NAME	select
 KEY_COLUMN_USAGE	TABLE_NAME	select
+PARTITIONS	TABLE_NAME	select
 STATISTICS	TABLE_NAME	select
 TABLES	TABLE_NAME	select
 TABLE_CONSTRAINTS	TABLE_NAME	select
@@ -796,7 +804,7 @@ delete from mysql.db where user='mysqltest_4';
 flush privileges;
 SELECT table_schema, count(*) FROM information_schema.TABLES GROUP BY TABLE_SCHEMA;
 table_schema	count(*)
-information_schema	18
+information_schema	19
 mysql	18
 create table t1 (i int, j int);
 create trigger trg1 before insert on t1 for each row
diff --git a/mysql-test/r/information_schema_db.result b/mysql-test/r/information_schema_db.result
index 75dc747cfab..7832e34dfd1 100644
--- a/mysql-test/r/information_schema_db.result
+++ b/mysql-test/r/information_schema_db.result
@@ -8,6 +8,7 @@ COLUMNS
 COLUMN_PRIVILEGES
 ENGINES
 KEY_COLUMN_USAGE
+PARTITIONS
 PLUGINS
 ROUTINES
 SCHEMATA
diff --git a/mysql-test/r/information_schema_part.result b/mysql-test/r/information_schema_part.result
new file mode 100644
index 00000000000..cf49abf888a
--- /dev/null
+++ b/mysql-test/r/information_schema_part.result
@@ -0,0 +1,113 @@
+drop table if exists t1,t2,t3,t4;
+create table t1 (a int not null,b int not null,c int not null, primary key(a,b))
+partition by list (b*a)
+(partition x1 values in (1) tablespace ts1,
+partition x2 values in (3, 11, 5, 7) tablespace ts2,
+partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3);
+select * from information_schema.partitions where table_schema="test"
+and table_name="t1";
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PARTITION_NAME	SUBPARTITION_NAME	PARTITION_ORDINAL_POSITION	SUBPARTITION_ORDINAL_POSITION	PARTITION_METHOD	SUBPARTITION_METHOD	PARTITION_EXPRESSION	SUBPARTITION_EXPRESSION	PARTITION_DESCRIPTION	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	CREATE_TIME	UPDATE_TIME	CHECK_TIME	CHECKSUM	PARTITION_COMMENT	NODEGROUP	TABLESPACE_NAME
+NULL	test	t1	x1	NULL	1	NULL	LIST	NULL	b*a	NULL	1	0	0	0	#	1024	0	#	#	NULL	NULL	default	0	ts1
+NULL	test	t1	x2	NULL	2	NULL	LIST	NULL	b*a	NULL	3,11,5,7	0	0	0	#	1024	0	#	#	NULL	NULL	default	0	ts2
+NULL	test	t1	x3	NULL	3	NULL	LIST	NULL	b*a	NULL	16,8,24,27	0	0	0	#	1024	0	#	#	NULL	NULL	default	0	ts3
+create table t2 (a int not null,b int not null,c int not null, primary key(a,b))
+partition by range (a)
+partitions 3
+(partition x1 values less than (5) tablespace ts1,
+partition x2 values less than (10) tablespace ts2,
+partition x3 values less than maxvalue tablespace ts3);
+select * from information_schema.partitions where table_schema="test"
+and table_name="t2";
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PARTITION_NAME	SUBPARTITION_NAME	PARTITION_ORDINAL_POSITION	SUBPARTITION_ORDINAL_POSITION	PARTITION_METHOD	SUBPARTITION_METHOD	PARTITION_EXPRESSION	SUBPARTITION_EXPRESSION	PARTITION_DESCRIPTION	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	CREATE_TIME	UPDATE_TIME	CHECK_TIME	CHECKSUM	PARTITION_COMMENT	NODEGROUP	TABLESPACE_NAME
+NULL	test	t2	x1	NULL	1	NULL	RANGE	NULL	a	NULL	5	0	0	0	#	1024	0	#	#	NULL	NULL	default	0	ts1
+NULL	test	t2	x2	NULL	2	NULL	RANGE	NULL	a	NULL	10	0	0	0	#	1024	0	#	#	NULL	NULL	default	0	ts2
+NULL	test	t2	x3	NULL	3	NULL	RANGE	NULL	a	NULL	MAXVALUE	0	0	0	#	1024	0	#	#	NULL	NULL	default	0	ts3
+create table t3 (f1 date)
+partition by hash(month(f1))
+partitions 3;
+select * from information_schema.partitions where table_schema="test"
+and table_name="t3";
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PARTITION_NAME	SUBPARTITION_NAME	PARTITION_ORDINAL_POSITION	SUBPARTITION_ORDINAL_POSITION	PARTITION_METHOD	SUBPARTITION_METHOD	PARTITION_EXPRESSION	SUBPARTITION_EXPRESSION	PARTITION_DESCRIPTION	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	CREATE_TIME	UPDATE_TIME	CHECK_TIME	CHECKSUM	PARTITION_COMMENT	NODEGROUP	TABLESPACE_NAME
+NULL	test	t3	p0	NULL	1	NULL	HASH	NULL	month(f1)	NULL	NULL	0	0	0	#	1024	0	#	#	NULL	NULL	default	0	default
+NULL	test	t3	p1	NULL	2	NULL	HASH	NULL	month(f1)	NULL	NULL	0	0	0	#	1024	0	#	#	NULL	NULL	default	0	default
+NULL	test	t3	p2	NULL	3	NULL	HASH	NULL	month(f1)	NULL	NULL	0	0	0	#	1024	0	#	#	NULL	NULL	default	0	default
+create table t4 (f1 date, f2 int)
+partition by key(f1,f2)
+partitions 3;
+select * from information_schema.partitions where table_schema="test"
+and table_name="t4";
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PARTITION_NAME	SUBPARTITION_NAME	PARTITION_ORDINAL_POSITION	SUBPARTITION_ORDINAL_POSITION	PARTITION_METHOD	SUBPARTITION_METHOD	PARTITION_EXPRESSION	SUBPARTITION_EXPRESSION	PARTITION_DESCRIPTION	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	CREATE_TIME	UPDATE_TIME	CHECK_TIME	CHECKSUM	PARTITION_COMMENT	NODEGROUP	TABLESPACE_NAME
+NULL	test	t4	p0	NULL	1	NULL	KEY	NULL	f1,f2	NULL	NULL	0	0	0	#	1024	0	#	#	NULL	NULL	default	0	default
+NULL	test	t4	p1	NULL	2	NULL	KEY	NULL	f1,f2	NULL	NULL	0	0	0	#	1024	0	#	#	NULL	NULL	default	0	default
+NULL	test	t4	p2	NULL	3	NULL	KEY	NULL	f1,f2	NULL	NULL	0	0	0	#	1024	0	#	#	NULL	NULL	default	0	default
+drop table t1,t2,t3,t4;
+create table t1 (a int not null,b int not null,c int not null,primary key (a,b))
+partition by range (a)
+subpartition by hash (a+b)
+( partition x1 values less than (1)
+( subpartition x11 tablespace t1,
+subpartition x12 tablespace t2),
+partition x2 values less than (5)
+( subpartition x21 tablespace t1,
+subpartition x22 tablespace t2)
+);
+create table t2 (a int not null,b int not null,c int not null,primary key (a,b))
+partition by range (a)
+subpartition by key (a)
+( partition x1 values less than (1)
+( subpartition x11 tablespace t1,
+subpartition x12 tablespace t2),
+partition x2 values less than (5)
+( subpartition x21 tablespace t1,
+subpartition x22 tablespace t2)
+);
+select * from information_schema.partitions where table_schema="test";
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PARTITION_NAME	SUBPARTITION_NAME	PARTITION_ORDINAL_POSITION	SUBPARTITION_ORDINAL_POSITION	PARTITION_METHOD	SUBPARTITION_METHOD	PARTITION_EXPRESSION	SUBPARTITION_EXPRESSION	PARTITION_DESCRIPTION	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	CREATE_TIME	UPDATE_TIME	CHECK_TIME	CHECKSUM	PARTITION_COMMENT	NODEGROUP	TABLESPACE_NAME
+NULL	test	t1	x1	x11	1	1	RANGE	HASH	a	a+b	1	0	0	0	#	1024	0	#	#	NULL	NULL	default	0	t1
+NULL	test	t1	x1	x12	1	2	RANGE	HASH	a	a+b	1	0	0	0	#	1024	0	#	#	NULL	NULL	default	0	t2
+NULL	test	t1	x2	x21	2	1	RANGE	HASH	a	a+b	5	0	0	0	#	1024	0	#	#	NULL	NULL	default	0	t1
+NULL	test	t1	x2	x22	2	2	RANGE	HASH	a	a+b	5	0	0	0	#	1024	0	#	#	NULL	NULL	default	0	t2
+NULL	test	t2	x1	x11	1	1	RANGE	KEY	a	a	1	0	0	0	#	1024	0	#	#	NULL	NULL	default	0	t1
+NULL	test	t2	x1	x12	1	2	RANGE	KEY	a	a	1	0	0	0	#	1024	0	#	#	NULL	NULL	default	0	t2
+NULL	test	t2	x2	x21	2	1	RANGE	KEY	a	a	5	0	0	0	#	1024	0	#	#	NULL	NULL	default	0	t1
+NULL	test	t2	x2	x22	2	2	RANGE	KEY	a	a	5	0	0	0	#	1024	0	#	#	NULL	NULL	default	0	t2
+drop table t1,t2;
+create table t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by range (a)
+subpartition by hash (a+b)
+( partition x1 values less than (1)
+( subpartition x11 tablespace t1 nodegroup 0,
+subpartition x12 tablespace t2 nodegroup 1),
+partition x2 values less than (5)
+( subpartition x21 tablespace t1 nodegroup 0,
+subpartition x22 tablespace t2 nodegroup 1)
+);
+select * from information_schema.partitions where table_schema="test";
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PARTITION_NAME	SUBPARTITION_NAME	PARTITION_ORDINAL_POSITION	SUBPARTITION_ORDINAL_POSITION	PARTITION_METHOD	SUBPARTITION_METHOD	PARTITION_EXPRESSION	SUBPARTITION_EXPRESSION	PARTITION_DESCRIPTION	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	CREATE_TIME	UPDATE_TIME	CHECK_TIME	CHECKSUM	PARTITION_COMMENT	NODEGROUP	TABLESPACE_NAME
+NULL	test	t1	x1	x11	1	1	RANGE	HASH	a	a+b	1	0	0	0	#	1024	0	#	#	NULL	NULL	default	0	t1
+NULL	test	t1	x1	x12	1	2	RANGE	HASH	a	a+b	1	0	0	0	#	1024	0	#	#	NULL	NULL	default	1	t2
+NULL	test	t1	x2	x21	2	1	RANGE	HASH	a	a+b	5	0	0	0	#	1024	0	#	#	NULL	NULL	default	0	t1
+NULL	test	t1	x2	x22	2	2	RANGE	HASH	a	a+b	5	0	0	0	#	1024	0	#	#	NULL	NULL	default	1	t2
+show tables;
+Tables_in_test
+t1
+drop table t1;
+create table t1(f1 int, f2 int);
+select * from information_schema.partitions where table_schema="test";
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PARTITION_NAME	SUBPARTITION_NAME	PARTITION_ORDINAL_POSITION	SUBPARTITION_ORDINAL_POSITION	PARTITION_METHOD	SUBPARTITION_METHOD	PARTITION_EXPRESSION	SUBPARTITION_EXPRESSION	PARTITION_DESCRIPTION	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	CREATE_TIME	UPDATE_TIME	CHECK_TIME	CHECKSUM	PARTITION_COMMENT	NODEGROUP	TABLESPACE_NAME
+NULL	test	t1	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	0	0	0	#	1024	0	#	#	NULL	NULL		0	
+drop table t1;
+create table t1 (f1 date)
+partition by linear hash(month(f1))
+partitions 3;
+select * from information_schema.partitions where table_schema="test"
+and table_name="t1";
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PARTITION_NAME	SUBPARTITION_NAME	PARTITION_ORDINAL_POSITION	SUBPARTITION_ORDINAL_POSITION	PARTITION_METHOD	SUBPARTITION_METHOD	PARTITION_EXPRESSION	SUBPARTITION_EXPRESSION	PARTITION_DESCRIPTION	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	CREATE_TIME	UPDATE_TIME	CHECK_TIME	CHECKSUM	PARTITION_COMMENT	NODEGROUP	TABLESPACE_NAME
+NULL	test	t1	p0	NULL	1	NULL	LINEAR HASH	NULL	month(f1)	NULL	NULL	0	0	0	#	1024	0	#	#	NULL	NULL	default	0	default
+NULL	test	t1	p1	NULL	2	NULL	LINEAR HASH	NULL	month(f1)	NULL	NULL	0	0	0	#	1024	0	#	#	NULL	NULL	default	0	default
+NULL	test	t1	p2	NULL	3	NULL	LINEAR HASH	NULL	month(f1)	NULL	NULL	0	0	0	#	1024	0	#	#	NULL	NULL	default	0	default
+drop table t1;
diff --git a/mysql-test/r/ndb_partition_range.result b/mysql-test/r/ndb_partition_range.result
index d484f37ad3d..5c2b4224000 100644
--- a/mysql-test/r/ndb_partition_range.result
+++ b/mysql-test/r/ndb_partition_range.result
@@ -15,6 +15,11 @@ 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);
+select * from information_schema.partitions where table_name= 't1';
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PARTITION_NAME	SUBPARTITION_NAME	PARTITION_ORDINAL_POSITION	SUBPARTITION_ORDINAL_POSITION	PARTITION_METHOD	SUBPARTITION_METHOD	PARTITION_EXPRESSION	SUBPARTITION_EXPRESSION	PARTITION_DESCRIPTION	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	CREATE_TIME	UPDATE_TIME	CHECK_TIME	CHECKSUM	PARTITION_COMMENT	NODEGROUP	TABLESPACE_NAME
+NULL	test	t1	x1	NULL	1	NULL	RANGE	NULL	a	NULL	5	0	0	0	#	0	0	#	#	NULL	NULL	default	0	default
+NULL	test	t1	x2	NULL	2	NULL	RANGE	NULL	a	NULL	10	0	0	0	#	0	0	#	#	NULL	NULL	default	0	default
+NULL	test	t1	x3	NULL	3	NULL	RANGE	NULL	a	NULL	20	0	0	0	#	0	0	#	#	NULL	NULL	default	0	default
 select * from t1 order by a;
 a	b	c
 1	1	1
diff --git a/mysql-test/t/information_schema_part.test b/mysql-test/t/information_schema_part.test
new file mode 100644
index 00000000000..163b04248b8
--- /dev/null
+++ b/mysql-test/t/information_schema_part.test
@@ -0,0 +1,101 @@
+
+-- source include/have_partition.inc
+
+--disable_warnings
+drop table if exists t1,t2,t3,t4;
+--enable_warnings
+
+create table t1 (a int not null,b int not null,c int not null, primary key(a,b))
+partition by list (b*a)
+(partition x1 values in (1) tablespace ts1,
+ partition x2 values in (3, 11, 5, 7) tablespace ts2,
+ partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3);
+
+--replace_column 16 # 19 # 20 #
+select * from information_schema.partitions where table_schema="test"
+and table_name="t1";
+
+create table t2 (a int not null,b int not null,c int not null, primary key(a,b))
+partition by range (a)
+partitions 3
+(partition x1 values less than (5) tablespace ts1,
+ partition x2 values less than (10) tablespace ts2,
+ partition x3 values less than maxvalue tablespace ts3);
+--replace_column 16 # 19 # 20 #
+select * from information_schema.partitions where table_schema="test"
+and table_name="t2";
+
+create table t3 (f1 date)
+partition by hash(month(f1))
+partitions 3;
+--replace_column 16 # 19 # 20 #
+select * from information_schema.partitions where table_schema="test"
+and table_name="t3";
+
+create table t4 (f1 date, f2 int)
+partition by key(f1,f2)
+partitions 3;
+--replace_column 16 # 19 # 20 #
+select * from information_schema.partitions where table_schema="test"
+and table_name="t4";
+
+drop table t1,t2,t3,t4;
+
+create table t1 (a int not null,b int not null,c int not null,primary key (a,b))
+partition by range (a)
+subpartition by hash (a+b)
+( partition x1 values less than (1)
+  ( subpartition x11 tablespace t1,
+    subpartition x12 tablespace t2),
+   partition x2 values less than (5)
+  ( subpartition x21 tablespace t1,
+    subpartition x22 tablespace t2)
+);
+
+create table t2 (a int not null,b int not null,c int not null,primary key (a,b))
+partition by range (a)
+subpartition by key (a)
+( partition x1 values less than (1)
+  ( subpartition x11 tablespace t1,
+    subpartition x12 tablespace t2),
+   partition x2 values less than (5)
+  ( subpartition x21 tablespace t1,
+    subpartition x22 tablespace t2)
+);
+--replace_column 16 # 19 # 20 #
+select * from information_schema.partitions where table_schema="test"; 
+drop table t1,t2;
+
+create table t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by range (a)
+subpartition by hash (a+b)
+( partition x1 values less than (1)
+ ( subpartition x11 tablespace t1 nodegroup 0,
+   subpartition x12 tablespace t2 nodegroup 1),
+  partition x2 values less than (5)
+( subpartition x21 tablespace t1 nodegroup 0,
+  subpartition x22 tablespace t2 nodegroup 1)
+);
+   
+--replace_column 16 # 19 # 20 #
+select * from information_schema.partitions where table_schema="test";
+show tables;
+drop table t1;
+
+create table t1(f1 int, f2 int);
+--replace_column 16 # 19 # 20 #
+select * from information_schema.partitions where table_schema="test";
+drop table t1;
+
+create table t1 (f1 date)
+partition by linear hash(month(f1))
+partitions 3;
+--replace_column 16 # 19 # 20 #
+select * from information_schema.partitions where table_schema="test"
+and table_name="t1";
+drop table t1;
+
diff --git a/mysql-test/t/ndb_partition_range.test b/mysql-test/t/ndb_partition_range.test
index fb62fac044f..2eb324ccdcb 100644
--- a/mysql-test/t/ndb_partition_range.test
+++ b/mysql-test/t/ndb_partition_range.test
@@ -32,6 +32,9 @@ 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;