diff options
Diffstat (limited to 'mysql-test/main/partition_default.result')
| -rw-r--r-- | mysql-test/main/partition_default.result | 1267 |
1 files changed, 1267 insertions, 0 deletions
diff --git a/mysql-test/main/partition_default.result b/mysql-test/main/partition_default.result new file mode 100644 index 00000000000..db3b714bbcc --- /dev/null +++ b/mysql-test/main/partition_default.result @@ -0,0 +1,1267 @@ +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 + 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 + 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 + 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 + 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 + 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 + 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 + 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 + 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 + 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 + 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 + 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; +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 + 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 + 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 + 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 + 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 + 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; +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 + 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 + 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 0 +p1 1 +p2 1 +pd 2 +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 + 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; +# +# MDEV-10765: Wrong result - query does not retrieve values from +# default partition on a table partitioned by list columns +# +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; +i j +10 1 +explain partitions +select * from t1 where i = 10; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1,p2 system NULL NULL NULL NULL 1 +select * from t1 where i = 10 and j=1; +i j +10 1 +explain partitions +select * from t1 where i = 10 and j=1; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p2 system NULL NULL NULL NULL 1 +insert into t1 values (10,10); +select * from t1 where i = 10 and j=10; +i j +10 10 +explain partitions +select * from t1 where i = 10 and j=10; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1 system NULL NULL NULL NULL 1 +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); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1,p2,p3 ALL NULL NULL NULL NULL 3 Using where +drop table t1; +# +# MDEV-10763: Wrong result - server does not return NULL values +# from default list partition after ALTER table +# +create table t1 (i int) partition by list (i) ( partition p1 default); +insert into t1 values (null); +select * from t1 where i is null; +i +NULL +alter table t1 partition by list (i) ( partition p1 values in (1), partition p2 default); +select * from t1 where i is null; +i +NULL +explain partitions +select * from t1 where i is null; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p2 system NULL NULL NULL NULL 1 +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; +i +NULL +explain partitions +select * from t1 where i is null; +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; +# +# MDEV-12395: DROP PARTITION does not work as expected when +# table has DEFAULT LIST partition +# +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; +i +10 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + PARTITION BY LIST (`i`) +(PARTITION `pdef` DEFAULT ENGINE = MyISAM) +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; +i +1 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + PARTITION BY LIST (`i`) +(PARTITION `p` VALUES IN (1,2,3,4) ENGINE = MyISAM) +DROP TABLE t1; |