Edit File: partition_hash.result
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;