diff options
| -rw-r--r-- | mysql-test/r/partition_default.result | 1151 | ||||
| -rw-r--r-- | mysql-test/t/partition_default.test | 455 | ||||
| -rw-r--r-- | sql/partition_element.h | 2 | ||||
| -rw-r--r-- | sql/partition_info.cc | 158 | ||||
| -rw-r--r-- | sql/partition_info.h | 10 | ||||
| -rw-r--r-- | sql/share/errmsg-utf8.txt | 3 | ||||
| -rw-r--r-- | sql/sql_partition.cc | 118 | ||||
| -rw-r--r-- | sql/sql_partition.h | 4 | ||||
| -rw-r--r-- | sql/sql_yacc.yy | 21 |
9 files changed, 1843 insertions, 79 deletions
diff --git a/mysql-test/r/partition_default.result b/mysql-test/r/partition_default.result new file mode 100644 index 00000000000..ab9fa58a222 --- /dev/null +++ b/mysql-test/r/partition_default.result @@ -0,0 +1,1151 @@ +create table t1 (a int, b int) +PARTITION BY LIST (a) +( +PARTITION p2 VALUES IN (4,5,6), +PARTITION p1 VALUES IN (1) +) +; +insert into t1 values (10,10); +ERROR HY000: Table has no partition for value 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; +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 (4,5,6) ENGINE = MyISAM, + PARTITION p1 VALUES IN (1) ENGINE = MyISAM, + PARTITION p0 DEFAULT ENGINE = MyISAM) */ +insert into t1 values (10,10); +insert into t1 values (4,4); +select * from t1; +a b +4 4 +10 10 +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +partition_name table_rows +p0 1 +p1 0 +p2 1 +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, +PARTITION p3 DEFAULT +) +; +ERROR HY000: Only one DEFAULT partition allowed +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 +) +; +ERROR HY000: Only one DEFAULT partition allowed +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; +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 p0 DEFAULT ENGINE = MyISAM, + PARTITION p2 VALUES IN (4,5,6) ENGINE = MyISAM, + PARTITION p1 VALUES IN (1) ENGINE = MyISAM) */ +insert into t1 values (10,10); +select * from t1; +a b +10 10 +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +partition_name table_rows +p0 1 +p1 0 +p2 0 +drop table t1; +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; +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 p0 DEFAULT ENGINE = MyISAM, + PARTITION p2 VALUES IN (4,5,6) ENGINE = MyISAM, + PARTITION p1 VALUES IN (1,0) ENGINE = MyISAM) */ +insert into t1 values (10,10); +select * from t1; +a b +10 10 +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +partition_name table_rows +p0 1 +p1 0 +p2 0 +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)), +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) */ +insert into t1 values (10,10); +select * from t1; +a b +10 10 +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +partition_name table_rows +p0 1 +p1 0 +p2 0 +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)), +PARTITION p1 VALUES IN ((1,1),(0,0)), +PARTITION p0 DEFAULT, +PARTITION p3 DEFAULT +) +; +ERROR HY000: Only one DEFAULT partition allowed +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 +) +; +ERROR HY000: Only one DEFAULT partition allowed +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; +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 (4,5,6) ENGINE = MyISAM, + PARTITION p1 VALUES IN (1,20) ENGINE = MyISAM, + PARTITION p0 DEFAULT ENGINE = MyISAM) */ +insert into t1 values (10,10); +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +partition_name table_rows +p0 1 +p1 0 +p2 0 +select * from t1 where a=10; +a b +10 10 +select * from t1 where a<=10; +a b +10 10 +select * from t1 where a<=20; +a b +10 10 +select * from t1 where a>=10; +a b +10 10 +select * from t1 where a>=5; +a b +10 10 +insert into t1 values (20,20),(5,5); +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +partition_name table_rows +p0 1 +p1 1 +p2 1 +select * from t1 where a=10; +a b +10 10 +select * from t1 where a<=10; +a b +5 5 +10 10 +select * from t1 where a<=20; +a b +5 5 +20 20 +10 10 +select * from t1 where a>=10; +a b +20 20 +10 10 +select * from t1 where a>=5; +a b +5 5 +20 20 +10 10 +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 system NULL NULL NULL NULL 1 +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 p2 system NULL NULL NULL NULL 1 +select * from t1 where a=10 or a=5; +a b +5 5 +10 10 +explain partitions select * from t1 where a=10 or a=5; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p2,p0 ALL NULL NULL NULL NULL 2 Using where +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; +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),(5,5)) ENGINE = MyISAM, + PARTITION p1 VALUES IN ((1,1),(20,20)) ENGINE = MyISAM, + PARTITION p0 DEFAULT ENGINE = MyISAM) */ +insert into t1 values (10,10); +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +partition_name table_rows +p0 1 +p1 0 +p2 0 +select * from t1 where a=10 and b=10; +a b +10 10 +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 +select * from t1 where a=10; +a b +10 10 +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 system NULL NULL NULL NULL 1 +select * from t1 where a<=10; +a b +10 10 +select * from t1 where a>=10; +a b +10 10 +insert into t1 values (20,20),(5,5); +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +partition_name table_rows +p0 1 +p1 1 +p2 1 +select * from t1 where a=10 and b=10; +a b +10 10 +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 +select * from t1 where a=10 and b=10 or a=20 and b=20; +a b +20 20 +10 10 +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 +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +partition_name table_rows +p0 2 +p1 0 +p2 1 +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, +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'))); +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'); +SELECT * FROM t1 WHERE a < '1001-01-01'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +SELECT * FROM t1 WHERE a <= '1001-01-01'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +1001-01-01 +SELECT * FROM t1 WHERE a >= '1001-01-01'; +a +1001-01-01 +1002-00-00 +2001-01-01 +SELECT * FROM t1 WHERE a > '1001-01-01'; +a +1002-00-00 +2001-01-01 +SELECT * FROM t1 WHERE a = '1001-01-01'; +a +1001-01-01 +SELECT * FROM t1 WHERE a < '1001-00-00'; +a +0000-00-00 +0000-01-02 +0001-01-01 +SELECT * FROM t1 WHERE a <= '1001-00-00'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +SELECT * FROM t1 WHERE a >= '1001-00-00'; +a +1001-00-00 +1001-01-01 +1002-00-00 +2001-01-01 +SELECT * FROM t1 WHERE a > '1001-00-00'; +a +1001-01-01 +1002-00-00 +2001-01-01 +SELECT * FROM t1 WHERE a = '1001-00-00'; +a +1001-00-00 +# Disabling warnings for the invalid date +SELECT * FROM t1 WHERE a < '1999-02-31'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +1001-01-01 +1002-00-00 +SELECT * FROM t1 WHERE a <= '1999-02-31'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +1001-01-01 +1002-00-00 +SELECT * FROM t1 WHERE a >= '1999-02-31'; +a +2001-01-01 +SELECT * FROM t1 WHERE a > '1999-02-31'; +a +2001-01-01 +SELECT * FROM t1 WHERE a = '1999-02-31'; +a +SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +1001-01-01 +1002-00-00 +SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +1001-01-01 +SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; +a +1001-00-00 +1001-01-01 +1002-00-00 +SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; +a +0001-01-01 +1001-00-00 +1001-01-01 +# test without index +ALTER TABLE t1 DROP KEY a; +SELECT * FROM t1 WHERE a < '1001-01-01'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +SELECT * FROM t1 WHERE a <= '1001-01-01'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +1001-01-01 +SELECT * FROM t1 WHERE a >= '1001-01-01'; +a +1001-01-01 +1002-00-00 +2001-01-01 +SELECT * FROM t1 WHERE a > '1001-01-01'; +a +1002-00-00 +2001-01-01 +SELECT * FROM t1 WHERE a = '1001-01-01'; +a +1001-01-01 +SELECT * FROM t1 WHERE a < '1001-00-00'; +a +0000-00-00 +0000-01-02 +0001-01-01 +SELECT * FROM t1 WHERE a <= '1001-00-00'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +SELECT * FROM t1 WHERE a >= '1001-00-00'; +a +1001-00-00 +1001-01-01 +1002-00-00 +2001-01-01 +SELECT * FROM t1 WHERE a > '1001-00-00'; +a +1001-01-01 +1002-00-00 +2001-01-01 +SELECT * FROM t1 WHERE a = '1001-00-00'; +a +1001-00-00 +# Disabling warnings for the invalid date +SELECT * FROM t1 WHERE a < '1999-02-31'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +1001-01-01 +1002-00-00 +SELECT * FROM t1 WHERE a <= '1999-02-31'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +1001-01-01 +1002-00-00 +SELECT * FROM t1 WHERE a >= '1999-02-31'; +a +2001-01-01 +SELECT * FROM t1 WHERE a > '1999-02-31'; +a +2001-01-01 +SELECT * FROM t1 WHERE a = '1999-02-31'; +a +SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +1001-01-01 +1002-00-00 +SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +1001-01-01 +SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; +a +1001-00-00 +1001-01-01 +1002-00-00 +SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; +a +0001-01-01 +1001-00-00 +1001-01-01 +DROP TABLE t1; +# 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'))); +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'); +SELECT * FROM t1 WHERE a < '1001-01-01'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +SELECT * FROM t1 WHERE a <= '1001-01-01'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +1001-01-01 +SELECT * FROM t1 WHERE a >= '1001-01-01'; +a +1001-01-01 +1002-00-00 +2001-01-01 +SELECT * FROM t1 WHERE a > '1001-01-01'; +a +1002-00-00 +2001-01-01 +SELECT * FROM t1 WHERE a = '1001-01-01'; +a +1001-01-01 +SELECT * FROM t1 WHERE a < '1001-00-00'; +a +0000-00-00 +0000-01-02 +0001-01-01 +SELECT * FROM t1 WHERE a <= '1001-00-00'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +SELECT * FROM t1 WHERE a >= '1001-00-00'; +a +1001-00-00 +1001-01-01 +1002-00-00 +2001-01-01 +SELECT * FROM t1 WHERE a > '1001-00-00'; +a +1001-01-01 +1002-00-00 +2001-01-01 +SELECT * FROM t1 WHERE a = '1001-00-00'; +a +1001-00-00 +# Disabling warnings for the invalid date +SELECT * FROM t1 WHERE a < '1999-02-31'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +1001-01-01 +1002-00-00 +SELECT * FROM t1 WHERE a <= '1999-02-31'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +1001-01-01 +1002-00-00 +SELECT * FROM t1 WHERE a >= '1999-02-31'; +a +2001-01-01 +SELECT * FROM t1 WHERE a > '1999-02-31'; +a +2001-01-01 +SELECT * FROM t1 WHERE a = '1999-02-31'; +a +SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +1001-01-01 +1002-00-00 +SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +1001-01-01 +SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; +a +1001-00-00 +1001-01-01 +1002-00-00 +SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; +a +0001-01-01 +1001-00-00 +1001-01-01 +# test without index +ALTER TABLE t1 DROP KEY a; +SELECT * FROM t1 WHERE a < '1001-01-01'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +SELECT * FROM t1 WHERE a <= '1001-01-01'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +1001-01-01 +SELECT * FROM t1 WHERE a >= '1001-01-01'; +a +1001-01-01 +1002-00-00 +2001-01-01 +SELECT * FROM t1 WHERE a > '1001-01-01'; +a +1002-00-00 +2001-01-01 +SELECT * FROM t1 WHERE a = '1001-01-01'; +a +1001-01-01 +SELECT * FROM t1 WHERE a < '1001-00-00'; +a +0000-00-00 +0000-01-02 +0001-01-01 +SELECT * FROM t1 WHERE a <= '1001-00-00'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +SELECT * FROM t1 WHERE a >= '1001-00-00'; +a +1001-00-00 +1001-01-01 +1002-00-00 +2001-01-01 +SELECT * FROM t1 WHERE a > '1001-00-00'; +a +1001-01-01 +1002-00-00 +2001-01-01 +SELECT * FROM t1 WHERE a = '1001-00-00'; +a +1001-00-00 +# Disabling warnings for the invalid date +SELECT * FROM t1 WHERE a < '1999-02-31'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +1001-01-01 +1002-00-00 +SELECT * FROM t1 WHERE a <= '1999-02-31'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +1001-01-01 +1002-00-00 +SELECT * FROM t1 WHERE a >= '1999-02-31'; +a +2001-01-01 +SELECT * FROM t1 WHERE a > '1999-02-31'; +a +2001-01-01 +SELECT * FROM t1 WHERE a = '1999-02-31'; +a +SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +1001-01-01 +1002-00-00 +SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; +a +0000-00-00 +0000-01-02 +0001-01-01 +1001-00-00 +1001-01-01 +SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; +a +1001-00-00 +1001-01-01 +1002-00-00 +SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; +a +0001-01-01 +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 +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +partition_name table_rows +p0 1 +p1 1 +p2 1 +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 +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +partition_name table_rows +p0 1 +p1 1 +p2 1 +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 +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +partition_name table_rows +p0 1 +p1 1 +p2 1 +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 drop partition p2; +ERROR HY000: Table has no partition for value 2 +delete from t1 where a=2; +alter table t1 drop partition p2; +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 p1 VALUES IN (20,0) ENGINE = MyISAM, + PARTITION p0 VALUES IN (10) ENGINE = MyISAM) */ +select * from t1; +a b +0 0 +10 10 +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +partition_name table_rows +p0 1 +p1 1 +alter table t1 add partition (PARTITION pd 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 p1 VALUES IN (20,0) ENGINE = MyISAM, + PARTITION p0 VALUES IN (10) ENGINE = MyISAM, + PARTITION pd DEFAULT ENGINE = MyISAM) */ +alter table t1 add partition (PARTITION pdd DEFAULT); +ERROR HY000: Only one DEFAULT partition allowed +alter table t1 drop partition pd; +alter table t1 add partition (PARTITION pdd DEFAULT, +PARTITION pd DEFAULT); +ERROR HY000: Only one DEFAULT partition allowed +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 +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +partition_name table_rows +p0 1 +p1 1 +p2 1 +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 +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +partition_name table_rows +p0 1 +p1 1 +p2 1 +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 +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +partition_name table_rows +p0 1 +p1 1 +p2 1 +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 drop partition p2; +ERROR HY000: Table has no partition for value from column_list +delete from t1 where a=2; +alter table t1 drop partition p2; +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 p1 VALUES IN ((1,1),(0,0)) ENGINE = MyISAM, + PARTITION p0 VALUES IN ((10,10)) ENGINE = MyISAM) */ +select * from t1; +a b +0 0 +10 10 +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +partition_name table_rows +p0 1 +p1 1 +alter table t1 add partition (PARTITION pd 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 p1 VALUES IN ((1,1),(0,0)) ENGINE = MyISAM, + PARTITION p0 VALUES IN ((10,10)) ENGINE = MyISAM, + PARTITION pd DEFAULT ENGINE = MyISAM) */ +alter table t1 add partition (PARTITION pdd DEFAULT); +ERROR HY000: Only one DEFAULT partition allowed +alter table t1 drop partition pd; +alter table t1 add partition (PARTITION pdd DEFAULT, +PARTITION pd DEFAULT); +ERROR HY000: Only one DEFAULT partition allowed +drop table t1; +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); +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +partition_name table_rows +p1 1 +p2 1 +pd 2 +alter table t1 add partition +(partition p0 VALUES IN (2,3)); +select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; +partition_name table_rows +p0 2 +p1 1 +p2 1 +pd 0 +drop table t1; +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; +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 p0 DEFAULT ENGINE = MyISAM, + PARTITION p2 VALUES IN ((1,4),(2,5),(3,6)) ENGINE = MyISAM, + PARTITION p1 VALUES IN ((1,1),(0,0)) ENGINE = MyISAM) */ +drop table t1; diff --git a/mysql-test/t/partition_default.test b/mysql-test/t/partition_default.test new file mode 100644 index 00000000000..8f7fe588525 --- /dev/null +++ b/mysql-test/t/partition_default.test @@ -0,0 +1,455 @@ + +--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; + +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +alter table t1 drop partition p2; +delete from t1 where a=2; +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; + +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +alter table t1 drop partition p2; +delete from t1 where a=2; +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; diff --git a/sql/partition_element.h b/sql/partition_element.h index 308a4d6ddd2..b979b7a58e6 100644 --- a/sql/partition_element.h +++ b/sql/partition_element.h @@ -65,7 +65,7 @@ typedef struct p_column_list_val Item* item_expression; partition_info *part_info; uint partition_id; - bool max_value; + bool max_value; // MAXVALUE for RANGE type or DEFAULT value for LIST type bool null_value; char fixed; } part_column_list_val; diff --git a/sql/partition_info.cc b/sql/partition_info.cc index 4f297c630ad..9d31667a6a8 100644 --- a/sql/partition_info.cc +++ b/sql/partition_info.cc @@ -1472,6 +1472,8 @@ bool partition_info::check_list_constants(THD *thd) List_iterator<partition_element> list_func_it(partitions); DBUG_ENTER("partition_info::check_list_constants"); + DBUG_ASSERT(part_type == LIST_PARTITION); + num_list_values= 0; /* We begin by calculating the number of list values that have been @@ -1503,21 +1505,15 @@ bool partition_info::check_list_constants(THD *thd) has_null_part_id= i; found_null= TRUE; } - List_iterator<part_elem_value> list_val_it1(part_def->list_val_list); - while (list_val_it1++) - num_list_values++; + num_list_values+= part_def->list_val_list.elements; } while (++i < num_parts); list_func_it.rewind(); num_column_values= part_field_list.elements; size_entries= column_list ? (num_column_values * sizeof(part_column_list_val)) : sizeof(LIST_PART_ENTRY); - ptr= thd->calloc((num_list_values+1) * size_entries); - if (unlikely(ptr == NULL)) - { - mem_alloc_error(num_list_values * size_entries); + if (unlikely(!(ptr= thd->calloc((num_list_values+1) * size_entries)))) goto end; - } if (column_list) { part_column_list_val *loc_list_col_array; @@ -1528,6 +1524,13 @@ bool partition_info::check_list_constants(THD *thd) do { part_def= list_func_it++; + if (part_def->max_value) + { + // DEFAULT is not a real value so let's exclude it from sorting. + DBUG_ASSERT(num_list_values); + num_list_values--; + continue; + } List_iterator<part_elem_value> list_val_it2(part_def->list_val_list); while ((list_value= list_val_it2++)) { @@ -1557,6 +1560,13 @@ bool partition_info::check_list_constants(THD *thd) do { part_def= list_func_it++; + if (part_def->max_value && part_type == LIST_PARTITION) + { + // DEFAULT is not a real value so let's exclude it from sorting. + DBUG_ASSERT(num_list_values); + num_list_values--; + continue; + } List_iterator<part_elem_value> list_val_it2(part_def->list_val_list); while ((list_value= list_val_it2++)) { @@ -2287,11 +2297,19 @@ int partition_info::add_max_value(THD *thd) DBUG_ENTER("partition_info::add_max_value"); part_column_list_val *col_val; - if (!(col_val= add_column_value(thd))) + /* + Makes for LIST COLUMNS 'num_columns' DEFAULT tuples, 1 tuple for RANGEs + */ + uint max_val= (num_columns && part_type == LIST_PARTITION) ? + num_columns : 1; + for (uint i= 0; i < max_val; i++) { - DBUG_RETURN(TRUE); + if (!(col_val= add_column_value(thd))) + { + DBUG_RETURN(TRUE); + } + col_val->max_value= TRUE; } - col_val->max_value= TRUE; DBUG_RETURN(FALSE); } @@ -2566,8 +2584,7 @@ int partition_info::reorganize_into_single_field_col_val(THD *thd) */ int partition_info::fix_partition_values(THD *thd, part_elem_value *val, - partition_element *part_elem, - uint part_id) + partition_element *part_elem) { part_column_list_val *col_val= val->col_val_array; DBUG_ENTER("partition_info::fix_partition_values"); @@ -2576,59 +2593,31 @@ int partition_info::fix_partition_values(THD *thd, { DBUG_RETURN(FALSE); } - if (val->added_items != 1) - { - my_error(ER_PARTITION_COLUMN_LIST_ERROR, MYF(0)); - DBUG_RETURN(TRUE); - } - if (col_val->max_value) + + Item *item_expr= col_val->item_expression; + if ((val->null_value= item_expr->null_value)) { - /* The parser ensures we're not LIST partitioned here */ - DBUG_ASSERT(part_type == RANGE_PARTITION); - if (defined_max_value) - { - my_error(ER_PARTITION_MAXVALUE_ERROR, MYF(0)); - DBUG_RETURN(TRUE); - } - if (part_id == (num_parts - 1)) + if (part_elem->has_null_value) { - defined_max_value= TRUE; - part_elem->max_value= TRUE; - part_elem->range_value= LONGLONG_MAX; - } - else - { - my_error(ER_PARTITION_MAXVALUE_ERROR, MYF(0)); + my_error(ER_MULTIPLE_DEF_CONST_IN_LIST_PART_ERROR, MYF(0)); DBUG_RETURN(TRUE); } + part_elem->has_null_value= TRUE; } - else + else if (item_expr->result_type() != INT_RESULT) { - Item *item_expr= col_val->item_expression; - if ((val->null_value= item_expr->null_value)) - { - if (part_elem->has_null_value) - { - my_error(ER_MULTIPLE_DEF_CONST_IN_LIST_PART_ERROR, MYF(0)); - DBUG_RETURN(TRUE); - } - part_elem->has_null_value= TRUE; - } - else if (item_expr->result_type() != INT_RESULT) + my_error(ER_VALUES_IS_NOT_INT_TYPE_ERROR, MYF(0), + part_elem->partition_name); + DBUG_RETURN(TRUE); + } + if (part_type == RANGE_PARTITION) + { + if (part_elem->has_null_value) { - my_error(ER_VALUES_IS_NOT_INT_TYPE_ERROR, MYF(0), - part_elem->partition_name); + my_error(ER_NULL_IN_VALUES_LESS_THAN, MYF(0)); DBUG_RETURN(TRUE); } - if (part_type == RANGE_PARTITION) - { - if (part_elem->has_null_value) - { - my_error(ER_NULL_IN_VALUES_LESS_THAN, MYF(0)); - DBUG_RETURN(TRUE); - } - part_elem->range_value= val->value; - } + part_elem->range_value= val->value; } col_val->fixed= 2; DBUG_RETURN(FALSE); @@ -2828,6 +2817,7 @@ bool partition_info::fix_parser_data(THD *thd) key_algorithm == KEY_ALGORITHM_NONE) key_algorithm= KEY_ALGORITHM_55; } + defined_max_value= FALSE; // in case it already set (CREATE TABLE LIKE) do { part_elem= it++; @@ -2835,16 +2825,60 @@ bool partition_info::fix_parser_data(THD *thd) num_elements= part_elem->list_val_list.elements; DBUG_ASSERT(part_type == RANGE_PARTITION ? num_elements == 1U : TRUE); + for (j= 0; j < num_elements; j++) { part_elem_value *val= list_val_it++; - if (column_list) + + if (val->added_items != (column_list ? num_columns : 1)) + { + my_error(ER_PARTITION_COLUMN_LIST_ERROR, MYF(0)); + DBUG_RETURN(TRUE); + } + + /* + Check the last MAX_VALUE for range partitions and DEFAULT value + for LIST partitions. + Both values are marked with defined_max_value and + default_partition_id. + + This is a max_value/default is max_value is set and this is + a normal RANGE (no column list) or if it's a LIST partition: + + PARTITION p3 VALUES LESS THAN MAXVALUE + or + PARTITION p3 VALUES DEFAULT + */ + if (val->added_items && val->col_val_array[0].max_value && + (!column_list || part_type == LIST_PARTITION)) { - if (val->added_items != num_columns) + DBUG_ASSERT(part_type == RANGE_PARTITION || + part_type == LIST_PARTITION); + if (defined_max_value) + { + my_error((part_type == RANGE_PARTITION) ? + ER_PARTITION_MAXVALUE_ERROR : + ER_PARTITION_DEFAULT_ERROR, MYF(0)); + DBUG_RETURN(TRUE); + } + + /* For RANGE PARTITION MAX_VALUE must be last */ + if (i != (num_parts - 1) && + part_type != LIST_PARTITION) { - my_error(ER_PARTITION_COLUMN_LIST_ERROR, MYF(0)); + my_error(ER_PARTITION_MAXVALUE_ERROR, MYF(0)); DBUG_RETURN(TRUE); } + + defined_max_value= TRUE; + default_partition_id= i; + part_elem->max_value= TRUE; + part_elem->range_value= LONGLONG_MAX; + continue; + } + + if (column_list) + { for (k= 0; k < num_columns; k++) { part_column_list_val *col_val= &val->col_val_array[k]; @@ -2857,10 +2891,8 @@ bool partition_info::fix_parser_data(THD *thd) } else { - if (fix_partition_values(thd, val, part_elem, i)) - { + if (fix_partition_values(thd, val, part_elem)) DBUG_RETURN(TRUE); - } if (val->null_value) { /* diff --git a/sql/partition_info.h b/sql/partition_info.h index 5181e19d568..66579be6384 100644 --- a/sql/partition_info.h +++ b/sql/partition_info.h @@ -202,6 +202,7 @@ public: uint num_full_part_fields; uint has_null_part_id; + uint32 default_partition_id; /* This variable is used to calculate the partition id when using LINEAR KEY/HASH. This functionality is kept in the MySQL Server @@ -230,6 +231,10 @@ public: bool use_default_num_subpartitions; bool default_partitions_setup; bool defined_max_value; + inline bool has_default_partititon() + { + return (part_type == LIST_PARTITION && defined_max_value); + } bool list_of_part_fields; // KEY or COLUMNS PARTITIONING bool list_of_subpart_fields; // KEY SUBPARTITIONING bool linear_hash_ind; // LINEAR HASH/KEY @@ -323,8 +328,7 @@ public: Item* get_column_item(Item *item, Field *field); int fix_partition_values(THD *thd, part_elem_value *val, - partition_element *part_elem, - uint part_id); + partition_element *part_elem); bool fix_column_value_functions(THD *thd, part_elem_value *val, uint part_id); @@ -399,6 +403,7 @@ static inline void init_single_partition_iterator(uint32 part_id, part_iter->part_nums.start= part_iter->part_nums.cur= part_id; part_iter->part_nums.end= part_id+1; part_iter->ret_null_part= part_iter->ret_null_part_orig= FALSE; + part_iter->ret_default_part= part_iter->ret_default_part_orig= FALSE; part_iter->get_next= get_next_partition_id_range; } @@ -410,6 +415,7 @@ void init_all_partitions_iterator(partition_info *part_info, part_iter->part_nums.start= part_iter->part_nums.cur= 0; part_iter->part_nums.end= part_info->num_parts; part_iter->ret_null_part= part_iter->ret_null_part_orig= FALSE; + part_iter->ret_default_part= part_iter->ret_default_part_orig= FALSE; part_iter->get_next= get_next_partition_id_range; } diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt index 37012c17bfb..3082508613c 100644 --- a/sql/share/errmsg-utf8.txt +++ b/sql/share/errmsg-utf8.txt @@ -7219,3 +7219,6 @@ ER_CALCULATING_DEFAULT_VALUE eng "Got an error when calculating default value for %`s" ER_EXPRESSION_REFERS_TO_UNINIT_FIELD 01000 eng "Expression for field %`-.64s is refering to uninitialized field %`s" +ER_PARTITION_DEFAULT_ERROR + eng "Only one DEFAULT partition allowed" + ukr "Припустимо мати тільки один DEFAULT розділ" diff --git a/sql/sql_partition.cc b/sql/sql_partition.cc index b45f85528a6..54396b941a4 100644 --- a/sql/sql_partition.cc +++ b/sql/sql_partition.cc @@ -2311,6 +2311,15 @@ static int add_partition_values(File fptr, partition_info *part_info, { uint i; List_iterator<part_elem_value> list_val_it(p_elem->list_val_list); + + if (p_elem->max_value) + { + DBUG_ASSERT(part_info->defined_max_value || + current_thd->lex->sql_command == SQLCOM_ALTER_TABLE); + err+= add_string(fptr, " DEFAULT"); + return err; + } + err+= add_string(fptr, " VALUES IN "); uint num_items= p_elem->list_val_list.elements; @@ -3070,6 +3079,11 @@ int get_partition_id_list_col(partition_info *part_info, } } notfound: + if (part_info->defined_max_value) + { + *part_id= part_info->default_partition_id; + DBUG_RETURN(0); + } *part_id= 0; DBUG_RETURN(HA_ERR_NO_PARTITION_FOUND); } @@ -3123,6 +3137,11 @@ int get_partition_id_list(partition_info *part_info, } } notfound: + if (part_info->defined_max_value) + { + *part_id= part_info->default_partition_id; + DBUG_RETURN(0); + } *part_id= 0; DBUG_RETURN(HA_ERR_NO_PARTITION_FOUND); } @@ -4691,9 +4710,26 @@ uint prep_alter_part_table(THD *thd, TABLE *table, Alter_info *alter_info, DBUG_RETURN(TRUE); } - thd->work_part_info= thd->lex->part_info; - - if (thd->work_part_info && + partition_info *alt_part_info= thd->lex->part_info; + /* + This variable is TRUE in very special case when we add only DEFAULT + partition to the existing table + */ + bool only_default_value_added= + (alt_part_info && + alt_part_info->current_partition && + alt_part_info->current_partition->list_val_list.elements == 1 && + alt_part_info->current_partition->list_val_list.head()-> + added_items >= 1 && + alt_part_info->current_partition->list_val_list.head()-> + col_val_array[0].max_value) && + alt_part_info->part_type == LIST_PARTITION && + (alter_info->flags & Alter_info::ALTER_ADD_PARTITION); + if (only_default_value_added && + !thd->lex->part_info->num_columns) + thd->lex->part_info->num_columns= 1; // to make correct clone + + if ((thd->work_part_info= thd->lex->part_info) && !(thd->work_part_info= thd->lex->part_info->get_clone(thd))) DBUG_RETURN(TRUE); @@ -4709,12 +4745,12 @@ uint prep_alter_part_table(THD *thd, TABLE *table, Alter_info *alter_info, Alter_info::ALTER_REBUILD_PARTITION)) { partition_info *tab_part_info; - partition_info *alt_part_info= thd->work_part_info; uint flags= 0; bool is_last_partition_reorged= FALSE; part_elem_value *tab_max_elem_val= NULL; part_elem_value *alt_max_elem_val= NULL; longlong tab_max_range= 0, alt_max_range= 0; + alt_part_info= thd->work_part_info; if (!table->part_info) { @@ -4805,7 +4841,8 @@ uint prep_alter_part_table(THD *thd, TABLE *table, Alter_info *alter_info, my_error(ER_PARTITION_FUNCTION_FAILURE, MYF(0)); goto err; } - if ((flags & (HA_FAST_CHANGE_PARTITION | HA_PARTITION_ONE_PHASE)) != 0) + if ((flags & (HA_FAST_CHANGE_PARTITION | HA_PARTITION_ONE_PHASE)) != 0 && + !tab_part_info->has_default_partititon()) { /* "Fast" change of partitioning is supported in this case. @@ -4879,14 +4916,16 @@ uint prep_alter_part_table(THD *thd, TABLE *table, Alter_info *alter_info, } } if ((tab_part_info->column_list && - alt_part_info->num_columns != tab_part_info->num_columns) || + alt_part_info->num_columns != tab_part_info->num_columns && + !only_default_value_added) || (!tab_part_info->column_list && (tab_part_info->part_type == RANGE_PARTITION || tab_part_info->part_type == LIST_PARTITION) && - alt_part_info->num_columns != 1U) || + alt_part_info->num_columns != 1U && + !only_default_value_added) || (!tab_part_info->column_list && tab_part_info->part_type == HASH_PARTITION && - alt_part_info->num_columns != 0)) + (alt_part_info->num_columns != 0))) { my_error(ER_PARTITION_COLUMN_LIST_ERROR, MYF(0)); goto err; @@ -4919,9 +4958,13 @@ uint prep_alter_part_table(THD *thd, TABLE *table, Alter_info *alter_info, my_error(ER_NO_BINLOG_ERROR, MYF(0)); goto err; } - if (tab_part_info->defined_max_value) + if (tab_part_info->defined_max_value && + (tab_part_info->part_type == RANGE_PARTITION || + alt_part_info->defined_max_value)) { - my_error(ER_PARTITION_MAXVALUE_ERROR, MYF(0)); + my_error((tab_part_info->part_type == RANGE_PARTITION ? + ER_PARTITION_MAXVALUE_ERROR : + ER_PARTITION_DEFAULT_ERROR), MYF(0)); goto err; } if (num_new_partitions == 0) @@ -7677,6 +7720,7 @@ int get_part_iter_for_interval_cols_via_map(partition_info *part_info, uint flags, PARTITION_ITERATOR *part_iter) { + bool can_match_multiple_values; uint32 nparts; get_col_endpoint_func UNINIT_VAR(get_col_endpoint); DBUG_ENTER("get_part_iter_for_interval_cols_via_map"); @@ -7696,6 +7740,14 @@ int get_part_iter_for_interval_cols_via_map(partition_info *part_info, else assert(0); + can_match_multiple_values= ((flags & + (NO_MIN_RANGE | NO_MAX_RANGE | NEAR_MIN | + NEAR_MAX)) || + memcmp(min_value, max_value, min_len)); + DBUG_ASSERT(can_match_multiple_values || (flags & EQ_RANGE) || flags == 0); + if (can_match_multiple_values && part_info->has_default_partititon()) + part_iter->ret_default_part= part_iter->ret_default_part_orig= TRUE; + if (flags & NO_MIN_RANGE) part_iter->part_nums.start= part_iter->part_nums.cur= 0; else @@ -7731,7 +7783,15 @@ int get_part_iter_for_interval_cols_via_map(partition_info *part_info, nparts); } if (part_iter->part_nums.start == part_iter->part_nums.end) + { + // No matching partition found. + if (part_info->has_default_partititon()) + { + part_iter->ret_default_part= part_iter->ret_default_part_orig= TRUE; + DBUG_RETURN(1); + } DBUG_RETURN(0); + } DBUG_RETURN(1); } @@ -7792,6 +7852,7 @@ int get_part_iter_for_interval_via_mapping(partition_info *part_info, (void)min_len; (void)max_len; part_iter->ret_null_part= part_iter->ret_null_part_orig= FALSE; + part_iter->ret_default_part= part_iter->ret_default_part_orig= FALSE; if (part_info->part_type == RANGE_PARTITION) { @@ -7828,8 +7889,13 @@ int get_part_iter_for_interval_via_mapping(partition_info *part_info, else MY_ASSERT_UNREACHABLE(); - can_match_multiple_values= (flags || !min_value || !max_value || + can_match_multiple_values= ((flags & + (NO_MIN_RANGE | NO_MAX_RANGE | NEAR_MIN | + NEAR_MAX)) || memcmp(min_value, max_value, field_len)); + DBUG_ASSERT(can_match_multiple_values || (flags & EQ_RANGE) || flags == 0); + if (can_match_multiple_values && part_info->has_default_partititon()) + part_iter->ret_default_part= part_iter->ret_default_part_orig= TRUE; if (can_match_multiple_values && (part_info->part_type == RANGE_PARTITION || part_info->has_null_value)) @@ -7859,6 +7925,12 @@ int get_part_iter_for_interval_via_mapping(partition_info *part_info, { /* The right bound is X <= NULL, i.e. it is a "X IS NULL" interval */ part_iter->part_nums.end= 0; + /* + It is something like select * from tbl where col IS NULL + and we have partition with NULL to catch it, so we do not need + DEFAULT partition + */ + part_iter->ret_default_part= part_iter->ret_default_part_orig= FALSE; DBUG_RETURN(1); } } @@ -7900,7 +7972,7 @@ int get_part_iter_for_interval_via_mapping(partition_info *part_info, } } if (part_iter->part_nums.start == max_endpoint_val) - DBUG_RETURN(0); /* No partitions */ + goto not_found; } } @@ -7937,9 +8009,17 @@ int get_part_iter_for_interval_via_mapping(partition_info *part_info, } if (part_iter->part_nums.start >= part_iter->part_nums.end && !part_iter->ret_null_part) - DBUG_RETURN(0); /* No partitions */ + goto not_found; } DBUG_RETURN(1); /* Ok, iterator initialized */ + +not_found: + if (part_info->has_default_partititon()) + { + part_iter->ret_default_part= part_iter->ret_default_part_orig= TRUE; + DBUG_RETURN(1); + } + DBUG_RETURN(0); /* No partitions */ } @@ -8003,6 +8083,8 @@ int get_part_iter_for_interval_via_walking(partition_info *part_info, (void)max_len; part_iter->ret_null_part= part_iter->ret_null_part_orig= FALSE; + part_iter->ret_default_part= part_iter->ret_default_part_orig= FALSE; + if (is_subpart) { field= part_info->subpart_field_array[0]; @@ -8134,6 +8216,9 @@ uint32 get_next_partition_id_range(PARTITION_ITERATOR* part_iter) part_iter->ret_null_part= FALSE; return 0; /* NULL always in first range partition */ } + // we do not have default partition in RANGE partitioning + DBUG_ASSERT(!part_iter->ret_default_part); + part_iter->part_nums.cur= part_iter->part_nums.start; part_iter->ret_null_part= part_iter->ret_null_part_orig; return NOT_A_PARTITION_ID; @@ -8171,8 +8256,15 @@ uint32 get_next_partition_id_list(PARTITION_ITERATOR *part_iter) part_iter->ret_null_part= FALSE; return part_iter->part_info->has_null_part_id; } + if (part_iter->ret_default_part) + { + part_iter->ret_default_part= FALSE; + return part_iter->part_info->default_partition_id; + } + /* Reset partition for next read */ part_iter->part_nums.cur= part_iter->part_nums.start; part_iter->ret_null_part= part_iter->ret_null_part_orig; + part_iter->ret_default_part= part_iter->ret_default_part_orig; return NOT_A_PARTITION_ID; } else diff --git a/sql/sql_partition.h b/sql/sql_partition.h index dd352b60120..b225c14fc53 100644 --- a/sql/sql_partition.h +++ b/sql/sql_partition.h @@ -177,6 +177,10 @@ typedef struct st_partition_iter iterator also produce id of the partition that contains NULL value. */ bool ret_null_part, ret_null_part_orig; + /* + We should return DEFAULT partition. + */ + bool ret_default_part, ret_default_part_orig; struct st_part_num_range { uint32 start; diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index a61cabe3581..551a86e4a41 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -5217,6 +5217,27 @@ opt_part_values: part_info->part_type= LIST_PARTITION; } part_values_in {} + | DEFAULT + { + LEX *lex= Lex; + partition_info *part_info= lex->part_info; + if (! lex->is_partition_management()) + { + if (part_info->part_type != LIST_PARTITION) + my_yyabort_error((ER_PARTITION_WRONG_VALUES_ERROR, MYF(0), + "LIST", "DEFAULT")); + } + else + part_info->part_type= LIST_PARTITION; + if (part_info->init_column_part(thd)) + { + MYSQL_YYABORT; + } + if (part_info->add_max_value(thd)) + { + MYSQL_YYABORT; + } + } ; part_func_max: |