1 files changed, 542 insertions, 0 deletions
diff --git a/mysql-test/main/partition_default.test b/mysql-test/main/partition_default.test
new file mode 100644
index 00000000000..b0424f567e4
--- /dev/null
+++ b/mysql-test/main/partition_default.test
@@ -0,0 +1,542 @@
+
+--source include/have_partition.inc
+
+#
+# expression lists
+#
+create table t1 (a int, b int)
+  PARTITION BY LIST (a)
+  (
+    PARTITION p2 VALUES IN (4,5,6),
+    PARTITION p1 VALUES IN (1)
+  )
+;
+--error ER_NO_PARTITION_FOR_GIVEN_VALUE
+insert into t1 values (10,10);
+drop table t1;
+create table t1 (a int, b int)
+  PARTITION BY LIST (a)
+  (
+    PARTITION p2 VALUES IN (4,5,6),
+    PARTITION p1 VALUES IN (1),
+    PARTITION p0 DEFAULT
+  )
+;
+show create table t1;
+insert into t1 values (10,10);
+insert into t1 values (4,4);
+select * from t1;
+--sorted_result
+select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1';
+drop table t1;
+
+--error ER_PARTITION_DEFAULT_ERROR
+create table t1 (a int, b int)
+  PARTITION BY LIST (a)
+  (
+    PARTITION p2 VALUES IN (4,5,6),
+    PARTITION p1 VALUES IN (1),
+    PARTITION p0 DEFAULT,
+    PARTITION p3 DEFAULT
+  )
+;
+--error ER_PARTITION_DEFAULT_ERROR
+create table t1 (a int, b int)
+  PARTITION BY LIST (a)
+  (
+    PARTITION p0 DEFAULT,
+    PARTITION p2 VALUES IN (4,5,6),
+    PARTITION p1 VALUES IN (1),
+    PARTITION p3 DEFAULT
+  )
+;
+
+create table t1 (a int, b int)
+  PARTITION BY LIST (a)
+  (
+    PARTITION p0 DEFAULT,
+    PARTITION p2 VALUES IN (4,5,6),
+    PARTITION p1 VALUES IN (1)
+  )
+;
+show create table t1;
+insert into t1 values (10,10);
+select * from t1;
+--sorted_result
+select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1';
+drop table t1;
+
+#
+# Default has its value as 0 check that they are not clash.
+#
+create table t1 (a int, b int)
+  PARTITION BY LIST (a)
+  (
+    PARTITION p0 DEFAULT,
+    PARTITION p2 VALUES IN (4,5,6),
+    PARTITION p1 VALUES IN (1, 0)
+  )
+;
+show create table t1;
+insert into t1 values (10,10);
+select * from t1;
+--sorted_result
+select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1';
+drop table t1;
+
+#
+# columns lists
+#
+create table t1 (a int, b int)
+  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;
+insert into t1 values (10,10);
+select * from t1;
+--sorted_result
+select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1';
+drop table t1;
+
+--error ER_PARTITION_DEFAULT_ERROR
+create table t1 (a int, b int)
+  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,
+    PARTITION p3 DEFAULT
+  )
+;
+
+--error ER_PARTITION_DEFAULT_ERROR
+create table t1 (a int, b int)
+  PARTITION BY LIST COLUMNS(a,b)
+  (
+    PARTITION p0 DEFAULT,
+    PARTITION p2 VALUES IN ((1,4),(2,5),(3,6)),
+    PARTITION p1 VALUES IN ((1,1),(0,0)),
+    PARTITION p3 DEFAULT
+  )
+;
+
+#
+# partititon prunning test
+#
+
+create table t1 (a int, b int)
+  PARTITION BY LIST (a)
+  (
+    PARTITION p2 VALUES IN (4,5,6),
+    PARTITION p1 VALUES IN (1,20),
+    PARTITION p0 default
+  )
+;
+show create table t1;
+insert into t1 values (10,10);
+--sorted_result
+select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1';
+select * from t1 where a=10;
+select * from t1 where a<=10;
+select * from t1 where a<=20;
+select * from t1 where a>=10;
+select * from t1 where a>=5;
+insert into t1 values (20,20),(5,5);
+--sorted_result
+select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1';
+select * from t1 where a=10;
+select * from t1 where a<=10;
+select * from t1 where a<=20;
+select * from t1 where a>=10;
+select * from t1 where a>=5;
+explain partitions select * from t1 where a=10;
+explain partitions select * from t1 where a=5;
+select * from t1 where a=10 or a=5;
+explain partitions select * from t1 where a=10 or a=5;
+
+drop table t1;
+
+create table t1 (a int, b int)
+  PARTITION BY LIST COLUMNS(a,b)
+  (
+    PARTITION p2 VALUES IN ((1,4),(2,5),(3,6),(5,5)),
+    PARTITION p1 VALUES IN ((1,1),(20,20)),
+    PARTITION p0 DEFAULT
+  )
+;
+show create table t1;
+insert into t1 values (10,10);
+--sorted_result
+select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1';
+select * from t1 where a=10 and b=10;
+explain partitions select * from t1 where a=10 and b=10;
+select * from t1 where a=10;
+explain partitions select * from t1 where a=10;
+select * from t1 where a<=10;
+select * from t1 where a>=10;
+insert into t1 values (20,20),(5,5);
+--sorted_result
+select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1';
+select * from t1 where a=10 and b=10;
+explain partitions select * from t1 where a=10 and b=10;
+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;
+--sorted_result
+select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='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))
+(PARTITION `pDEF` DEFAULT,
+ PARTITION `p2001-01-01` VALUES IN (TO_DAYS('2001-01-01')),
+ PARTITION `pNULL` VALUES IN (NULL),
+ PARTITION `p0000-01-02` VALUES IN (TO_DAYS('0000-01-02')),
+ PARTITION `p1001-01-01` VALUES IN (TO_DAYS('1001-01-01')));
+if ($verify_without_partitions)
+{
+ALTER TABLE t1 REMOVE PARTITIONING;
+}
+INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'),
+      ('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01');
+--source include/partition_date_range.inc
+--echo # test without index
+ALTER TABLE t1 DROP KEY a;
+--source include/partition_date_range.inc
+DROP TABLE t1;
+--echo # TO_SECONDS, test of LIST and index
+CREATE TABLE t1 (a DATE, KEY(a))
+PARTITION BY LIST (TO_SECONDS(a))
+(PARTITION `pDEF` DEFAULT,
+ PARTITION `p2001-01-01` VALUES IN (TO_SECONDS('2001-01-01')),
+ PARTITION `pNULL` VALUES IN (NULL),
+ PARTITION `p0000-01-02` VALUES IN (TO_SECONDS('0000-01-02')),
+ PARTITION `p1001-01-01` VALUES IN (TO_SECONDS('1001-01-01')));
+if ($verify_without_partitions)
+{
+ALTER TABLE t1 REMOVE PARTITIONING;
+}
+INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'),
+      ('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01');
+--source include/partition_date_range.inc
+--echo # test without index
+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;
+--sorted_result
+select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='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;
+--sorted_result
+select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='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;
+--sorted_result
+select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='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 drop partition p2;
+show create table t1;
+select * from t1;
+--sorted_result
+select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1';
+
+alter table t1 add partition (PARTITION pd DEFAULT);
+show create table t1;
+--error ER_PARTITION_DEFAULT_ERROR
+alter table t1 add partition (PARTITION pdd DEFAULT);
+alter table t1 drop partition pd;
+--error ER_PARTITION_DEFAULT_ERROR
+alter table t1 add partition (PARTITION pdd DEFAULT,
+                              PARTITION pd DEFAULT);
+
+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;
+--sorted_result
+select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='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;
+--sorted_result
+select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='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;
+--sorted_result
+select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='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 drop partition p2;
+show create table t1;
+select * from t1;
+--sorted_result
+select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1';
+
+alter table t1 add partition (PARTITION pd DEFAULT);
+show create table t1;
+--error ER_PARTITION_DEFAULT_ERROR
+alter table t1 add partition (PARTITION pdd DEFAULT);
+alter table t1 drop partition pd;
+--error ER_PARTITION_DEFAULT_ERROR
+alter table t1 add partition (PARTITION pdd DEFAULT,
+                              PARTITION pd DEFAULT);
+drop table t1;
+
+
+#
+# Problem of reorganizing DEFAULT partition
+#
+create table t1 (a int)
+  PARTITION BY LIST (a)
+  (
+    PARTITION p2 VALUES IN (4,5,6),
+    PARTITION p1 VALUES IN (1),
+    PARTITION pd DEFAULT
+  )
+;
+insert into t1 values (1),(2),(3),(4);
+--sorted_result
+select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1';
+
+alter table t1 add partition
+ (partition p0 VALUES IN (2,3));
+
+--sorted_result
+select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1';
+
+drop table t1;
+
+#
+# Check that first DEFAULT works in LIST COLUMN
+#
+create table t1 (a int, b int)
+  PARTITION BY LIST COLUMNS(a,b)
+  (
+    PARTITION p0 DEFAULT,
+    PARTITION p2 VALUES IN ((1,4),(2,5),(3,6)),
+    PARTITION p1 VALUES IN ((1,1),(0,0))
+  )
+;
+
+show create table t1;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-10765: Wrong result - query does not retrieve values from
+--echo # default partition on a table partitioned by list columns
+--echo #
+
+create table t1 (i int, j int) partition by list columns(i,j) (partition p1 values in ((10,10)), partition p2 default);
+insert into t1 values (10,1);
+select * from t1 where i = 10;
+explain partitions
+select * from t1 where i = 10;
+select * from t1 where i = 10 and j=1;
+explain partitions
+select * from t1 where i = 10 and j=1;
+insert into t1 values (10,10);
+select * from t1 where i = 10 and j=10;
+explain partitions
+select * from t1 where i = 10 and j=10;
+drop table t1;
+
+create table t1
+( 
+  a int not null,
+  b int not null,
+  c int
+)
+partition by list columns(a,b) 
+(
+  partition p1 values in ((10,10)), 
+  partition p2 values in ((10,20)), 
+  partition p3 values in ((10,30)),
+  partition p4 values in ((10,40)), 
+  partition p5 values in ((10,50)) 
+);
+
+insert into t1 values 
+  (10,10,1234), 
+  (10,20,1234), 
+  (10,30,1234),
+  (10,40,1234), 
+  (10,50,1234);
+
+explain partitions 
+select * from t1 
+where a>=10  and (a <=10 and b <=30);
+drop table t1;
+
+--echo #
+--echo # MDEV-10763: Wrong result - server does not return NULL values
+--echo # from default list partition after ALTER table
+--echo #
+create table t1 (i int) partition by list (i) ( partition p1 default);
+insert into t1 values (null);
+select * from t1 where i is null;
+alter table t1 partition by list (i) ( partition p1 values in (1), partition p2 default);
+select * from t1 where i is null;
+explain partitions 
+select * from t1 where i is null;
+alter table t1 partition by list (i) (
+  partition p0 values in (NULL),
+  partition p1 values in (1),
+  partition p2 default);
+select * from t1 where i is null;
+explain partitions 
+select * from t1 where i is null;
+
+drop table t1;
+
+
+--echo #
+--echo # MDEV-12395: DROP PARTITION does not work as expected when
+--echo # table has DEFAULT LIST partition
+--echo #
+
+CREATE TABLE t1 (i INT)
+  PARTITION BY LIST (i)
+  (PARTITION p VALUES IN (1,2,3,4),
+   PARTITION pdef DEFAULT);
+INSERT INTO t1 VALUES (1),(10);
+ALTER TABLE t1 DROP PARTITION p;
+SELECT * FROM t1;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (i INT)
+  PARTITION BY LIST (i)
+  (PARTITION p VALUES IN (1,2,3,4),
+   PARTITION pdef DEFAULT);
+INSERT INTO t1 VALUES (1),(10);
+ALTER TABLE t1 DROP PARTITION pdef;
+SELECT * FROM t1;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;