Adding upstream version 1:10.5.12.upstream/1%10.5.12upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 252 insertions, 0 deletions

diff --git a/mysql-test/main/partition_list.test b/mysql-test/main/partition_list.test
new file mode 100644
index 00000000..5eadb72a
--- /dev/null
+++ b/mysql-test/main/partition_list.test
@@ -0,0 +1,252 @@
+#--disable_abort_on_error
+#
+# Simple test for the partition storage engine
+# testing list partitioning
+#
+-- source include/have_partition.inc
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+#
+# Bug 20733: Zerofill columns gives wrong result with partitioned tables
+#
+create table t1 (a int unsigned)
+partition by list (a)
+(partition p0 values in (0),
+ partition p1 values in (1),
+ partition pnull values in (null),
+ partition p2 values in (2));
+
+insert into t1 values (null),(0),(1),(2);
+select * from t1 where a < 2;
+select * from t1 where a <= 0;
+select * from t1 where a < 1;
+select * from t1 where a > 0;
+select * from t1 where a > 1;
+select * from t1 where a >= 0;
+select * from t1 where a >= 1;
+select * from t1 where a is null;
+select * from t1 where a is not null;
+select * from t1 where a is null or a > 0;
+drop table t1;
+
+create table t1 (a int unsigned, b int)
+partition by list (a)
+subpartition by hash (b)
+subpartitions 2
+(partition p0 values in (0),
+ partition p1 values in (1),
+ partition pnull values in (null, 2),
+ partition p3 values in (3));
+select partition_method, partition_expression, partition_description
+  from information_schema.partitions where table_name = "t1";
+insert into t1 values (0,0),(0,1),(1,0),(1,1),(null,0),(null,1);
+insert into t1 values (2,0),(2,1),(3,0),(3,1);
+
+explain partitions select * from t1 where a is null;
+select * from t1 where a is null;
+explain partitions select * from t1 where a = 2;
+select * from t1 where a = 2;
+select * from t1 where a <= 0;
+select * from t1 where a < 3;
+select * from t1 where a >= 1 or a is null;
+drop table t1;
+
+#
+# Test ordinary list partitioning that it works ok
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null)
+partition by list(a)
+partitions 2
+(partition x123 values in (1,5,6),
+ partition x234 values in (4,7,8));
+
+INSERT into t1 VALUES (1,1,1);
+--error ER_NO_PARTITION_FOR_GIVEN_VALUE
+INSERT into t1 VALUES (2,1,1);
+--error ER_NO_PARTITION_FOR_GIVEN_VALUE
+INSERT into t1 VALUES (3,1,1);
+INSERT into t1 VALUES (4,1,1);
+INSERT into t1 VALUES (5,1,1);
+INSERT into t1 VALUES (6,1,1);
+INSERT into t1 VALUES (7,1,1);
+INSERT into t1 VALUES (8,1,1);
+--error ER_NO_PARTITION_FOR_GIVEN_VALUE
+INSERT into t1 VALUES (9,1,1);
+INSERT into t1 VALUES (1,2,1);
+INSERT into t1 VALUES (1,3,1);
+INSERT into t1 VALUES (1,4,1);
+INSERT into t1 VALUES (7,2,1);
+INSERT into t1 VALUES (7,3,1);
+INSERT into t1 VALUES (7,4,1);
+
+SELECT * from t1;
+SELECT * from t1 WHERE a=1;
+SELECT * from t1 WHERE a=7;
+SELECT * from t1 WHERE b=2;
+
+UPDATE t1 SET a=8 WHERE a=7 AND b=3;
+SELECT * from t1;
+UPDATE t1 SET a=8 WHERE a=5 AND b=1;
+SELECT * from t1;
+
+DELETE from t1 WHERE a=8;
+SELECT * from t1;
+DELETE from t1 WHERE a=2;
+SELECT * from t1;
+DELETE from t1 WHERE a=5 OR a=6;
+SELECT * from t1;
+
+ALTER TABLE t1
+partition by list(a)
+partitions 2
+(partition x123 values in (1,5,6),
+ partition x234 values in (4,7,8));
+SELECT * from t1;
+INSERT into t1 VALUES (6,2,1);
+--error ER_NO_PARTITION_FOR_GIVEN_VALUE
+INSERT into t1 VALUES (2,2,1);
+
+drop table t1;
+#
+# Subpartition by hash, two partitions and two subpartitions
+# Defined node group
+#
+CREATE TABLE t1 (
+a int not null,
+b int not null,
+c int not null,
+primary key (a,b))
+partition by list (a)
+subpartition by hash (a+b)
+( partition x1 values in (1,2,3)
+  ( subpartition x11 nodegroup 0,
+    subpartition x12 nodegroup 1),
+  partition x2 values in (4,5,6)
+  ( subpartition x21 nodegroup 0,
+    subpartition x22 nodegroup 1)
+);
+
+INSERT into t1 VALUES (1,1,1);
+INSERT into t1 VALUES (4,1,1);
+--error ER_NO_PARTITION_FOR_GIVEN_VALUE
+INSERT into t1 VALUES (7,1,1);
+UPDATE t1 SET a=5 WHERE a=1;
+SELECT * from t1;
+UPDATE t1 SET a=6 WHERE a=4;
+SELECT * from t1;
+DELETE from t1 WHERE a=6;
+SELECT * from t1;
+
+drop table t1;
+
+# 
+CREATE TABLE t1 ( 
+a int not null,
+b int not null,
+c int not null,
+primary key(a,b))
+partition by list (a)
+(partition x1 values in (1,2,9,4) tablespace ts1);
+
+drop table t1;
+
+#
+#Bug #17173 Partitions: less-than search fails 
+#
+CREATE TABLE t1 (s1 int) PARTITION BY LIST (s1) 
+(PARTITION p1 VALUES IN (1),
+PARTITION p2 VALUES IN (2),
+PARTITION p3 VALUES IN (3),
+PARTITION p4 VALUES IN (4),
+PARTITION p5 VALUES IN (5));
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+SELECT COUNT(*) FROM t1 WHERE s1 < 3;
+DROP TABLE t1;
+
+#
+# Bug 19281 Partitions: Auto-increment value lost
+#
+create table t1 (a int auto_increment primary key)
+auto_increment=100
+partition by list (a)
+(partition p0 values in (1, 100));
+create index inx on t1 (a);
+insert into t1 values (null);
+select * from t1;
+drop table t1;
+
+--error ER_PARTITION_FUNCTION_IS_NOT_ALLOWED
+create table t1 (a char(1))
+partition by list (ascii(ucase(a)))
+(partition p1 values in (2));
+
+--echo #
+--echo # MDEV-11681: PARTITION BY LIST COLUMNS with default partition:
+--echo # Assertion `part_info->num_list_values' failed in
+--echo # get_part_iter_for_interval_cols_via_map
+--echo #
+CREATE TABLE t1 (f int) PARTITION BY LIST COLUMNS (f) (PARTITION pdef DEFAULT);
+insert into t1 values (1),(2);
+select * from t1 where f = 1;
+
+drop table t1;
+
+CREATE TABLE t1 (f int, d int) PARTITION BY LIST COLUMNS (f,d) (PARTITION pdef DEFAULT);
+insert into t1 values (1,1),(2,2);
+select * from t1 where f = 1 and d = 1 ;
+
+drop table t1;
+CREATE TABLE t1 (f int) PARTITION BY LIST (f) (PARTITION pdef DEFAULT);
+insert into t1 values (1),(2);
+select * from t1 where f = 1;
+
+drop table t1;
+
+--echo #end of 10.2 tests
+
+--echo #
+--echo # Bug MDEV-16101: More than MAX_REF_PARTS values in a list on ALTER TABLE.
+--echo #                 Currently MAX_REF_PARTS = 32.
+CREATE TABLE ts1 (a INT, PRIMARY KEY (`a`))
+PARTITION BY LIST (`a`)
+(PARTITION `p ts_0` VALUES IN (101,102,103,104,105,106,107,108,109,110,
+                               111,112,113,114,115,116,117,118,119,120,
+                               121,122,123,124,125,126,127,128,129,130,
+                               131,132,133));
+INSERT INTO ts1
+VALUES (101), (102), (103), (104), (105), (106), (107), (108), (109), (110),
+       (111), (112), (113), (114), (115), (116), (117), (118), (119), (120),
+       (121), (122), (123), (124), (125), (126), (127), (128), (129), (130),
+       (131), (132), (133);
+--error ER_NO_PARTITION_FOR_GIVEN_VALUE
+INSERT INTO ts1 VALUES (134);
+SELECT * FROM ts1;
+ALTER TABLE ts1 ADD PARTITION
+(PARTITION `p ts_1` VALUES IN (1,2,3,4,5,6,7,8,9,10,
+                               11,12,13,14,15,16,17,18,19,20,
+                               21,22,23,24,25,26,27,28,29,30,
+                               31,32,33));
+INSERT INTO ts1
+VALUES (1),  (2),  (3),  (4),  (5),  (6),  (7),  (8),  (9),  (10),
+       (11), (12), (13), (14), (15), (16), (17), (18), (19), (20),
+       (21), (22), (23), (24), (25), (26), (27), (28), (29), (30),
+       (31), (32), (33);
+--error ER_NO_PARTITION_FOR_GIVEN_VALUE
+INSERT INTO ts1 VALUES(34);
+SELECT * FROM ts1;
+ALTER TABLE ts1 REORGANIZE PARTITION `p ts_1` INTO
+(PARTITION `p ts_1` VALUES IN (1,2,3,4,5,6,7,8,9,10,
+                               11,12,13,14,15,16,17,18,19,20,
+                               21,22,23,24,25,26,27,28,29,30,
+                               31,32,33,34,35));
+INSERT INTO ts1 VALUES (34), (35);
+--error ER_NO_PARTITION_FOR_GIVEN_VALUE
+INSERT INTO ts1 VALUES (36);
+SELECT * FROM ts1;
+DROP TABLE ts1;