/usr/lib/mysql-testsuite/r/partition_hash.result is in percona-server-test-5.6 5.6.22-rel71.0-0ubuntu4.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 | drop table if exists t1;
#
# Bug#12725206/Bug#61765: WRONG QUERY RESULTS ON SUBPARTITIONS BASED
# ON USIGNED BIGINT
#
CREATE TABLE t1 (
a int(11) NOT NULL,
b bigint(20) UNSIGNED NOT NULL
)
PARTITION BY RANGE (a)
SUBPARTITION BY HASH (b)
SUBPARTITIONS 3
(PARTITION p1 VALUES LESS THAN (1308614400),
PARTITION p2 VALUES LESS THAN (1308700800),
PARTITION p3 VALUES LESS THAN (1308787200),
PARTITION p4 VALUES LESS THAN (1308873600),
PARTITION p5 VALUES LESS THAN (1308960000)
);
INSERT INTO t1 VALUES
(1308614400,18446744073709551615),
(1308700800,0xFFFFFFFFFFFFFFFE),
(1308787200,18446744073709551613),
(1308873600,18446744073709551612),
(1308873600, 12531568256096620965),
(1308873600, 12531568256096),
(1308873600, 9223372036854775808);
SELECT a,b,HEX(b) FROM t1 ORDER BY a, b;
a b HEX(b)
1308614400 18446744073709551615 FFFFFFFFFFFFFFFF
1308700800 18446744073709551614 FFFFFFFFFFFFFFFE
1308787200 18446744073709551613 FFFFFFFFFFFFFFFD
1308873600 12531568256096 B65BBACA460
1308873600 9223372036854775808 8000000000000000
1308873600 12531568256096620965 ADE912EF0F3351A5
1308873600 18446744073709551612 FFFFFFFFFFFFFFFC
# The following queries returned nothing
SELECT * FROM t1 WHERE b = 9223372036854775808;
a b
1308873600 9223372036854775808
SELECT * FROM t1 WHERE b = 18446744073709551612;
a b
1308873600 18446744073709551612
SELECT * FROM t1 WHERE b = 18446744073709551615;
a b
1308614400 18446744073709551615
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = 9223372036854775808;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 p1_p1sp2,p2_p2sp2,p3_p3sp2,p4_p4sp2,p5_p5sp2 ALL NULL NULL NULL NULL 3 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = 18446744073709551612;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 p1_p1sp1,p2_p2sp1,p3_p3sp1,p4_p4sp1,p5_p5sp1 ALL NULL NULL NULL NULL 3 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = 18446744073709551615;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 p1_p1sp1,p2_p2sp1,p3_p3sp1,p4_p4sp1,p5_p5sp1 ALL NULL NULL NULL NULL 3 Using where
DROP TABLE t1;
CREATE TABLE t1 (c1 INT)
PARTITION BY HASH (c1)
PARTITIONS 15;
INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
ALTER TABLE t1 COALESCE PARTITION 13;
DROP TABLE t1;
CREATE TABLE t1 (c1 INT)
PARTITION BY LINEAR HASH (c1)
PARTITIONS 5;
INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
ALTER TABLE t1 COALESCE PARTITION 3;
DROP TABLE t1;
create table t1 (a int unsigned)
partition by hash(a div 2)
partitions 4;
insert into t1 values (null),(0),(1),(2),(3),(4),(5),(6),(7);
select * from t1 where a < 0;
a
select * from t1 where a is null or (a >= 5 and a <= 7);
a
NULL
5
6
7
select * from t1 where a is null;
a
NULL
select * from t1 where a is not null;
a
0
1
2
3
4
5
6
7
select * from t1 where a >= 1 and a < 3;
a
1
2
select * from t1 where a >= 3 and a <= 5;
a
3
4
5
select * from t1 where a > 2 and a < 4;
a
3
select * from t1 where a > 3 and a <= 6;
a
4
5
6
select * from t1 where a > 5;
a
6
7
select * from t1 where a >= 1 and a <= 5;
a
1
2
3
4
5
explain partitions select * from t1 where a < 0;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 p0,p1,p2,p3 ALL NULL NULL NULL NULL 9 Using where
explain partitions select * from t1 where a is null or (a >= 5 and a <= 7);
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 p0,p2,p3 ALL NULL NULL NULL NULL 7 Using where
explain partitions select * from t1 where a is null;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 3 Using where
explain partitions select * from t1 where a is not null;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 p0,p1,p2,p3 ALL NULL NULL NULL NULL 9 Using where
explain partitions select * from t1 where a >= 1 and a < 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 5 Using where
explain partitions select * from t1 where a >= 3 and a <= 5;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 p1,p2 ALL NULL NULL NULL NULL 4 Using where
explain partitions select * from t1 where a > 2 and a < 4;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 p1 ALL NULL NULL NULL NULL 2 Using where
explain partitions select * from t1 where a > 3 and a <= 6;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 p2,p3 ALL NULL NULL NULL NULL 4 Using where
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 p0,p1,p2,p3 ALL NULL NULL NULL NULL 9 Using where
explain partitions select * from t1 where a >= 1 and a <= 5;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 p0,p1,p2 ALL NULL NULL NULL NULL 7 Using where
drop table t1;
CREATE TABLE t1 (
a int not null,
b int not null,
c int not null,
primary key(a,b))
partition by hash (a + 2)
partitions 3
(partition x1 tablespace ts1,
partition x2 tablespace ts2,
partition x3 tablespace ts3);
insert into t1 values (1,1,1);
insert into t1 values (2,1,1);
insert into t1 values (3,1,1);
insert into t1 values (4,1,1);
insert into t1 values (5,1,1);
select * from t1;
a b c
1 1 1
4 1 1
2 1 1
5 1 1
3 1 1
update t1 set c=3 where b=1;
select * from t1;
a b c
1 1 3
4 1 3
2 1 3
5 1 3
3 1 3
select b from t1 where a=3;
b
1
select b,c from t1 where a=1 AND b=1;
b c
1 3
delete from t1 where a=1;
delete from t1 where c=3;
select * from t1;
a b c
ALTER TABLE t1
partition by hash (a + 3)
partitions 3
(partition x1 tablespace ts1,
partition x2 tablespace ts2,
partition x3 tablespace ts3);
select * from t1;
a b c
drop table t1;
CREATE TABLE t1 (
a int not null,
b int not null,
c int not null,
primary key(a,b))
partition by hash (a)
(partition x1);
drop table t1;
CREATE TABLE t1 (
a int not null,
b int not null,
c int not null,
primary key(a,b))
partition by key (a)
(partition x1);
drop table t1;
CREATE TABLE t1 (f1 INTEGER, f2 char(20)) ENGINE = 'MYISAM' PARTITION BY HASH(f1) PARTITIONS 2;
INSERT INTO t1 SET f1 = 0 - 1, f2 = '#######';
select * from t1;
f1 f2
-1 #######
drop table t1;
CREATE TABLE t1 (s1 int) ENGINE=BLACKHOLE PARTITION BY HASH (s1);
INSERT INTO t1 VALUES (0);
DROP TABLE t1;
create table t1 (c1 int DEFAULT NULL,
c2 varchar (30) DEFAULT NULL,
c3 date DEFAULT NULL)
engine = myisam
partition by hash (to_days(c3))
partitions 12;
insert into t1 values
(136,'abc','2002-01-05'),(142,'abc','2002-02-14'),(162,'abc','2002-06-28'),
(182,'abc','2002-11-09'),(158,'abc','2002-06-01'),(184,'abc','2002-11-22');
select * from t1;
c1 c2 c3
136 abc 2002-01-05
158 abc 2002-06-01
142 abc 2002-02-14
162 abc 2002-06-28
182 abc 2002-11-09
184 abc 2002-11-22
select * from t1 where c3 between '2002-01-01' and '2002-12-31';
c1 c2 c3
136 abc 2002-01-05
158 abc 2002-06-01
142 abc 2002-02-14
162 abc 2002-06-28
182 abc 2002-11-09
184 abc 2002-11-22
drop table t1;
CREATE TABLE t1 (c1 INT) ENGINE=MyISAM PARTITION BY HASH(c1) PARTITIONS 1;
INSERT DELAYED INTO t1 VALUES (1);
DROP TABLE t1;
|