diff options
Diffstat (limited to 'mysql-test/suite/pbxt/t/partition_pruning.test')
| -rw-r--r-- | mysql-test/suite/pbxt/t/partition_pruning.test | 760 |
1 files changed, 760 insertions, 0 deletions
diff --git a/mysql-test/suite/pbxt/t/partition_pruning.test b/mysql-test/suite/pbxt/t/partition_pruning.test new file mode 100644 index 00000000000..234eb67535e --- /dev/null +++ b/mysql-test/suite/pbxt/t/partition_pruning.test @@ -0,0 +1,760 @@ +# +# Partition pruning tests. Currently we only detect which partitions to +# prune, so the test is EXPLAINs. +# +-- source include/have_partition.inc + +--disable_warnings +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +--enable_warnings + + +# Check if we can infer from condition on partition fields that +# no records will match. +create table t1 ( a int not null) partition by hash(a) partitions 2; +insert into t1 values (1),(2),(3); +explain select * from t1 where a=5 and a=6; +drop table t1; + +# Simple HASH partitioning +create table t1 ( + a int(11) not null +) partition by hash (a) partitions 2; +insert into t1 values (1),(2),(3); + +explain partitions select * from t1 where a=1; +explain partitions select * from t1 where a=2; +explain partitions select * from t1 where a=1 or a=2; + +# Partitioning over several fields +create table t2 ( + a int not null, + b int not null +) partition by key(a,b) partitions 2; +insert into t2 values (1,1),(2,2),(3,3); + +explain partitions select * from t2 where a=1; +explain partitions select * from t2 where b=1; + +explain partitions select * from t2 where a=1 and b=1; + +# RANGE(expr) partitioning +create table t3 ( + a int +) +partition by range (a*1) ( + partition p0 values less than (10), + partition p1 values less than (20) +); +insert into t3 values (5),(15); + +explain partitions select * from t3 where a=11; +explain partitions select * from t3 where a=10; +explain partitions select * from t3 where a=20; + +explain partitions select * from t3 where a=30; + +# LIST(expr) partitioning +create table t4 (a int not null, b int not null) partition by LIST (a+b) ( + partition p0 values in (12), + partition p1 values in (14) +); +insert into t4 values (10,2), (10,4); + +# empty OR one +explain partitions select * from t4 where (a=10 and b=1) or (a=10 and b=2); + +# empty OR one OR empty +explain partitions select * from t4 + where (a=10 and b=1) or (a=10 and b=2) or (a=10 and b = 3); + +# one OR empty OR one +explain partitions select * from t4 where (a=10 and b=2) or (a=10 and b=3) + or (a=10 and b = 4); + +# empty OR full +explain partitions select * from t4 where (a=10 and b=1) or a=11; + +# one OR full +explain partitions select * from t4 where (a=10 and b=2) or a=11; + +drop table t1, t2, t3, t4; + +# LIST(expr)/HASH subpartitioning. +create table t5 (a int not null, b int not null, + c int not null, d int not null) +partition by LIST(a+b) subpartition by HASH (c+d) subpartitions 2 +( + partition p0 values in (12), + partition p1 values in (14) +); + +insert into t5 values (10,2,0,0), (10,4,0,0), (10,2,0,1), (10,4,0,1); +explain partitions select * from t5; + +# empty OR one OR empty +explain partitions select * from t5 + where (a=10 and b=1) or (a=10 and b=2) or (a=10 and b = 3); + +# one OR empty OR one +explain partitions select * from t5 where (a=10 and b=2) or (a=10 and b=3) + or (a=10 and b = 4); + +# conditions on subpartitions only +explain partitions select * from t5 where (c=1 and d=1); +explain partitions select * from t5 where (c=2 and d=1); + +# mixed partition/subpartitions. +explain partitions select * from t5 where (a=10 and b=2 and c=1 and d=1) or +(c=2 and d=1); + +# same as above +explain partitions select * from t5 where (a=10 and b=2 and c=1 and d=1) or +(b=2 and c=2 and d=1); + +# LIST(field) partitioning, interval analysis. +create table t6 (a int not null) partition by LIST(a) ( + partition p1 values in (1), + partition p3 values in (3), + partition p5 values in (5), + partition p7 values in (7), + partition p9 values in (9) +); +insert into t6 values (1),(3),(5); + +explain partitions select * from t6 where a < 1; +explain partitions select * from t6 where a <= 1; +explain partitions select * from t6 where a > 9; +explain partitions select * from t6 where a >= 9; + +explain partitions select * from t6 where a > 0 and a < 5; +explain partitions select * from t6 where a > 5 and a < 12; +explain partitions select * from t6 where a > 3 and a < 8 ; + +explain partitions select * from t6 where a >= 0 and a <= 5; +explain partitions select * from t6 where a >= 5 and a <= 12; +explain partitions select * from t6 where a >= 3 and a <= 8; + +explain partitions select * from t6 where a > 3 and a < 5; + +drop table t6; + +create table t6 (a int unsigned not null) partition by LIST(a) ( + partition p1 values in (1), + partition p3 values in (3), + partition p5 values in (5), + partition p7 values in (7), + partition p9 values in (9) +); +insert into t6 values (1),(3),(5); + +explain partitions select * from t6 where a < 1; +explain partitions select * from t6 where a <= 1; +explain partitions select * from t6 where a > 9; +explain partitions select * from t6 where a >= 9; + +explain partitions select * from t6 where a > 0 and a < 5; +explain partitions select * from t6 where a > 5 and a < 12; +explain partitions select * from t6 where a > 3 and a < 8 ; + +explain partitions select * from t6 where a >= 0 and a <= 5; +explain partitions select * from t6 where a >= 5 and a <= 12; +explain partitions select * from t6 where a >= 3 and a <= 8; + +explain partitions select * from t6 where a > 3 and a < 5; + +# RANGE(field) partitioning, interval analysis. +create table t7 (a int not null) partition by RANGE(a) ( + partition p10 values less than (10), + partition p30 values less than (30), + partition p50 values less than (50), + partition p70 values less than (70), + partition p90 values less than (90) +); +insert into t7 values (10),(30),(50); + +# leftmost intervals +explain partitions select * from t7 where a < 5; +explain partitions select * from t7 where a < 10; +explain partitions select * from t7 where a <= 10; +explain partitions select * from t7 where a = 10; + +#rightmost intervals +explain partitions select * from t7 where a < 90; +explain partitions select * from t7 where a = 90; +explain partitions select * from t7 where a > 90; +explain partitions select * from t7 where a >= 90; + +# misc intervals +explain partitions select * from t7 where a > 11 and a < 29; + +drop table t7; + +create table t7 (a int unsigned not null) partition by RANGE(a) ( + partition p10 values less than (10), + partition p30 values less than (30), + partition p50 values less than (50), + partition p70 values less than (70), + partition p90 values less than (90) +); +insert into t7 values (10),(30),(50); + +# leftmost intervals +explain partitions select * from t7 where a < 5; +explain partitions select * from t7 where a < 10; +explain partitions select * from t7 where a <= 10; +explain partitions select * from t7 where a = 10; + +#rightmost intervals +explain partitions select * from t7 where a < 90; +explain partitions select * from t7 where a = 90; +explain partitions select * from t7 where a > 90; +explain partitions select * from t7 where a >= 90; + +# misc intervals +explain partitions select * from t7 where a > 11 and a < 29; + +# LIST(monontonic_func) partitioning +create table t8 (a date not null) partition by RANGE(YEAR(a)) ( + partition p0 values less than (1980), + partition p1 values less than (1990), + partition p2 values less than (2000) +); +insert into t8 values ('1985-05-05'),('1995-05-05'); + +explain partitions select * from t8 where a < '1980-02-02'; + +# LIST(strict_monotonic_func) partitioning +create table t9 (a date not null) partition by RANGE(TO_DAYS(a)) ( + partition p0 values less than (732299), -- 2004-12-19 + partition p1 values less than (732468), -- 2005-06-06 + partition p2 values less than (732664) -- 2005-12-19 +); +insert into t9 values ('2005-05-05'), ('2005-04-04'); + +explain partitions select * from t9 where a < '2004-12-19'; +explain partitions select * from t9 where a <= '2004-12-19'; + +drop table t5,t6,t7,t8,t9; + +# Test the case where we can't create partitioning 'index' +# +# Not supported after bug#18198 is fixed +# +#create table t1 (a enum('a','b','c','d') default 'a') +# partition by hash (ascii(a)) partitions 2; +#insert into t1 values ('a'),('b'),('c'); +#explain partitions select * from t1 where a='b'; +#drop table t1; + +# +# Test cases for bugs found in code review: +# +create table t1 ( + a1 int not null +) +partition by range (a1) ( + partition p0 values less than (3), + partition p1 values less than (6), + partition p2 values less than (9) +); +insert into t1 values (1),(2),(3); +explain partitions select * from t1 where a1 > 3; +explain partitions select * from t1 where a1 >= 3; + +explain partitions select * from t1 where a1 < 3 and a1 > 3; +drop table t1; + +# +create table t3 (a int, b int) + partition by list(a) subpartition by hash(b) subpartitions 4 ( + partition p0 values in (1), + partition p1 values in (2), + partition p2 values in (3), + partition p3 values in (4) + ); +insert into t3 values (1,1),(2,2),(3,3); + +explain partitions select * from t3 where a=2 or b=1; +explain partitions select * from t3 where a=4 or b=2; +explain partitions select * from t3 where (a=2 or b=1) and (a=4 or b=2) ; +drop table t3; + +# Test for NULLs +create table t1 (a int) partition by hash(a) partitions 2; +insert into t1 values (1),(2); +explain partitions select * from t1 where a is null; + +# this uses both partitions +explain partitions select * from t1 where a is not null; +drop table t1; + +# Join tests +create table t1 (a int not null, b int not null, key(a), key(b)) + partition by hash(a) partitions 4; +insert into t1 values (1,1),(2,2),(3,3),(4,4); + +explain partitions +select * from t1 X, t1 Y +where X.b = Y.b and (X.a=1 or X.a=2) and (Y.a=2 or Y.a=3); + +explain partitions +select * from t1 X, t1 Y where X.a = Y.a and (X.a=1 or X.a=2); + +drop table t1; + +# Tests for "short ranges" +create table t1 (a int) partition by hash(a) partitions 20; +insert into t1 values (1),(2),(3); +explain partitions select * from t1 where a > 1 and a < 3; +explain partitions select * from t1 where a >= 1 and a < 3; +explain partitions select * from t1 where a > 1 and a <= 3; +explain partitions select * from t1 where a >= 1 and a <= 3; +drop table t1; + +create table t1 (a int, b int) + partition by list(a) subpartition by hash(b) subpartitions 20 +( + partition p0 values in (0), + partition p1 values in (1), + partition p2 values in (2), + partition p3 values in (3) +); +insert into t1 values (1,1),(2,2),(3,3); + +explain partitions select * from t1 where b > 1 and b < 3; +explain partitions select * from t1 where b > 1 and b < 3 and (a =1 or a =2); +drop table t1; + +# Test partition pruning for single-table UPDATE/DELETE. +# TODO: Currently we test only "all partitions pruned away" case. Add more +# tests when the patch that makes use of partition pruning results at +# execution phase is pushed. + +create table t1 (a int) partition by list(a) ( + partition p0 values in (1,2), + partition p1 values in (3,4) +); +insert into t1 values (1),(1),(2),(2),(3),(4),(3),(4); + +# This won't do any table access +flush status; +update t1 set a=100 where a=5; +show status like 'Handler_read_rnd_next'; + +# ... as compared to this, which will scan both partitions +flush status; +update t1 set a=100 where a+1=5+1; +show status like 'Handler_read_rnd_next'; + +# Same as above for DELETE: +flush status; +delete from t1 where a=5; +show status like 'Handler_read_rnd_next'; + +flush status; +delete from t1 where a+1=5+1; +show status like 'Handler_read_rnd_next'; + +# Same as above multi-table UPDATE/DELETE +create table t2 like t1; +insert into t2 select * from t2; + +flush status; +update t1,t2 set t1.a=1000, t2.a=1000 where t1.a=5 and t2.a=5; +show status like 'Handler_read_rnd_next'; +# ^ This shows 3 accesses, these are caused by const table reads. +# They should vanish when partition pruning results are used. + +flush status; +delete t1,t2 from t1, t2 where t1.a=5 and t2.a=5; +show status like 'Handler_read_rnd_next'; +drop table t1,t2; + +# +# WL#2986 Tests (Checking if partition pruning results are used at query +# execution phase) +# +CREATE TABLE `t1` ( + `a` int(11) default NULL +); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +CREATE TABLE `t2` ( + `a` int(11) default NULL, + KEY `a` (`a`) +) ; + +insert into t2 select A.a + 10*(B.a + 10* C.a) from t1 A, t1 B, t1 C ; +insert into t1 select a from t2; + +drop table t2; +CREATE TABLE `t2` ( + `a` int(11) default NULL, + `b` int(11) default NULL +) +PARTITION BY RANGE (a) ( +PARTITION p0 VALUES LESS THAN (200), +PARTITION p1 VALUES LESS THAN (400), +PARTITION p2 VALUES LESS THAN (600), +PARTITION p3 VALUES LESS THAN (800), +PARTITION p4 VALUES LESS THAN (1001)); + +insert into t2 select a,1 from t1 where a < 200; +insert into t2 select a,2 from t1 where a >= 200 and a < 400; +insert into t2 select a,3 from t1 where a >= 400 and a < 600; +insert into t2 select a,4 from t1 where a >= 600 and a < 800; +insert into t2 select a,5 from t1 where a >= 800 and a < 1001; + +explain partitions select * from t2; +explain partitions select * from t2 where a < 801 and a > 200; +explain partitions select * from t2 where a < 801 and a > 800; +explain partitions select * from t2 where a > 600; +explain partitions select * from t2 where a > 600 and b = 1; +explain partitions select * from t2 where a > 600 and b = 4; +explain partitions select * from t2 where a > 600 and b = 5; +explain partitions select * from t2 where b = 5; + +flush status; +update t2 set b = 100 where b = 6; +show status like 'Handler_read_rnd_next'; +flush status; +update t2 set a = 1002 where a = 1001; +show status like 'Handler_read_rnd_next'; +flush status; +update t2 set b = 6 where a = 600; +show status like 'Handler_read_rnd_next'; +flush status; +update t2 set b = 6 where a > 600 and a < 800; +show status like 'Handler_read_rnd_next'; +flush status; +delete from t2 where a > 600; +show status like 'Handler_read_rnd_next'; + +drop table t2; +CREATE TABLE `t2` ( + `a` int(11) default NULL, + `b` int(11) default NULL, + index (b) +) +PARTITION BY RANGE (a) ( +PARTITION p0 VALUES LESS THAN (200), +PARTITION p1 VALUES LESS THAN (400), +PARTITION p2 VALUES LESS THAN (600), +PARTITION p3 VALUES LESS THAN (800), +PARTITION p4 VALUES LESS THAN (1001)); + +insert into t2 select a,1 from t1 where a < 100; +insert into t2 select a,2 from t1 where a >= 200 and a < 300; +insert into t2 select a,3 from t1 where a >= 300 and a < 400; +insert into t2 select a,4 from t1 where a >= 400 and a < 500; +insert into t2 select a,5 from t1 where a >= 500 and a < 600; +insert into t2 select a,6 from t1 where a >= 600 and a < 700; +insert into t2 select a,7 from t1 where a >= 700 and a < 800; +insert into t2 select a,8 from t1 where a >= 800 and a < 900; +insert into t2 select a,9 from t1 where a >= 900 and a < 1001; + +explain partitions select * from t2; +# not using indexes +explain partitions select * from t2 where a = 101; +explain partitions select * from t2 where a = 550; +explain partitions select * from t2 where a = 833; +explain partitions select * from t2 where (a = 100 OR a = 900); +explain partitions select * from t2 where (a > 100 AND a < 600); +# PBXT: wait for sweeper +analyze table t2; +explain partitions select * from t2 where b = 4; +explain extended select * from t2 where b = 6; +explain partitions select * from t2 where b = 6; +explain extended select * from t2 where b in (1,3,5); +explain partitions select * from t2 where b in (1,3,5); +explain extended select * from t2 where b in (2,4,6); +explain partitions select * from t2 where b in (2,4,6); +explain extended select * from t2 where b in (7,8,9); +explain partitions select * from t2 where b in (7,8,9); +explain extended select * from t2 where b > 5; +explain partitions select * from t2 where b > 5; +explain extended select * from t2 where b > 5 and b < 8; +explain partitions select * from t2 where b > 5 and b < 8; +explain extended select * from t2 where b > 5 and b < 7; +explain partitions select * from t2 where b > 5 and b < 7; +explain extended select * from t2 where b > 0 and b < 5; +explain partitions select * from t2 where b > 0 and b < 5; + +flush status; +update t2 set a = 111 where b = 10; +show status like 'Handler_read_rnd_next'; +show status like 'Handler_read_key'; +flush status; +update t2 set a = 111 where b in (5,6); +show status like 'Handler_read_rnd_next'; +show status like 'Handler_read_key'; +flush status; +update t2 set a = 222 where b = 7; +show status like 'Handler_read_rnd_next'; +show status like 'Handler_read_key'; +flush status; +delete from t2 where b = 7; +show status like 'Handler_read_rnd_next'; +show status like 'Handler_read_key'; +flush status; +delete from t2 where b > 5; +show status like 'Handler_read_rnd_next'; +show status like 'Handler_read_key'; +show status like 'Handler_read_prev'; +show status like 'Handler_read_next'; +flush status; +delete from t2 where b < 5 or b > 3; +show status like 'Handler_read_rnd_next'; +show status like 'Handler_read_key'; +show status like 'Handler_read_prev'; +show status like 'Handler_read_next'; + +drop table t1, t2; + +# BUG#18025 +# part1: mediumint columns +create table t1 ( f_int1 mediumint, f_int2 integer) +partition by list(mod(f_int1,4)) ( + partition p_3 values in (-3), + partition p_2 values in (-2), + partition p_1 values in (-1), + partition p0 values in (0), + partition p1 values in (1), + partition p2 values in (2), + partition p3 values in (3) +); + +insert into t1 values (9, 9), (8, 8), (7, 7), (6, 6), (5, 5), + (4, 4), (3, 3), (2, 2), (1, 1); +select * from t1 where f_int1 between 5 and 15 order by f_int1; + +drop table t1; + +# part2: bug in pruning code +#create table t1 (a char(10) binary) +#partition by list(ascii(a)) +# (partition p1 values in (ascii('a')), +# partition p2 values in (ascii('b')), +# partition p3 values in (ascii('c')), +# partition p4 values in (ascii('d')), +# partition p5 values in (ascii('e'))); +#insert into t1 values ('a'),('bb'),('ccc'),('dddd'),('eeEee'); +#select * from t1 where a>='a' and a <= 'dddd'; +#explain partitions select * from t1 where a>='a' and a <= 'dddd'; +#drop table t1; + +# BUG#18659: Assertion failure when subpartitioning is used and partition is +# "IS NULL" +create table t1 (f_int1 integer) partition by list(abs(mod(f_int1,2))) + subpartition by hash(f_int1) subpartitions 2 +( + partition part1 values in (0), + partition part2 values in (1), + partition part4 values in (null) +); +insert into t1 set f_int1 = null; + +select * from t1 where f_int1 is null; +explain partitions select * from t1 where f_int1 is null; +drop table t1; + +# +# BUG#18558 +# +create table t1 (a int not null, b int not null) +partition by list(a) + subpartition by hash(b) subpartitions 4 +( + partition p0 values in (1), + partition p1 values in (2), + partition p2 values in (3) +); +insert into t1 values (1,1),(1,2),(1,3),(1,4), + (2,1),(2,2),(2,3),(2,4); +explain partitions select * from t1 where a=1 AND (b=1 OR b=2); +drop table t1; + +create table t1 (a int, b int not null) +partition by list(a) + subpartition by hash(b) subpartitions 2 +( + partition p0 values in (1), + partition p1 values in (2), + partition p2 values in (3), + partition pn values in (NULL) +); +insert into t1 values (1,1),(1,2),(1,3),(1,4), + (2,1),(2,2),(2,3),(2,4), (NULL,1); + +explain partitions select * from t1 where a IS NULL AND (b=1 OR b=2); + +explain partitions select * from t1 where (a IS NULL or a < 1) AND (b=1 OR b=2); +explain partitions select * from t1 where (a IS NULL or a < 2) AND (b=1 OR b=2); +explain partitions select * from t1 where (a IS NULL or a <= 1) AND (b=1 OR b=2); + +drop table t1; + +create table t1 ( a int) partition by list (MOD(a, 10)) +( partition p0 values in (0), partition p1 values in (1), + partition p2 values in (2), partition p3 values in (3), + partition p4 values in (4), partition p5 values in (5), + partition p6 values in (6), partition pn values in (NULL) +); +insert into t1 values (NULL), (0),(1),(2),(3),(4),(5),(6); +explain partitions select * from t1 where a is null or a < 2; +drop table t1; + +# Testcase from BUG#18751 +create table t1 (s1 int) partition by list (s1) + (partition p1 values in (0), + partition p2 values in (1), + partition p3 values in (null)); + +insert into t1 values (0),(1),(null); + +select count(*) from t1 where s1 < 0 or s1 is null; +explain partitions select count(*) from t1 where s1 < 0 or s1 is null; +drop table t1; + +# No tests for NULLs in RANGE(monotonic_expr()) - they depend on BUG#15447 +# being fixed. + +# +# BUG#17946 Like searches fail with partitioning +# +create table t1 (a char(32) primary key) +partition by key() +partitions 100; +insert into t1 values ('na'); +select * from t1; +select * from t1 where a like 'n%'; +drop table t1; + + +# BUG#19055 Crashes for varchar_col=NUMBER or varchar_col IS NULL +create table t1 (s1 varchar(15)) partition by key (s1); +select * from t1 where s1 = 0 or s1 is null; +insert into t1 values ('aa'),('bb'),('0'); +explain partitions select * from t1 where s1 = 0 or s1 is null; +drop table t1; + +# +# BUG#19684: EXPLAIN PARTITIONS produces garbage in 'partitions' column when +# the length of string to be displayed exceeds some limit. +create table t2 (a int, b int) + partition by LIST(a) + subpartition by HASH(b) subpartitions 40 +( partition p_0_long_partition_name values in(1), + partition p_1_long_partition_name values in(2)); + +insert into t2 values (1,1),(2,2); + +--vertical_results +explain partitions select * from t2; +--horizontal_results +drop table t2; + + +# BUG#20484 "Partitions: crash with explain and union" +create table t1 (s1 int); +explain partitions select 1 from t1 union all select 2; +drop table t1; + + +# BUG#20257: partition pruning test coverage for BIGINT UNSIGNED +create table t1 (a bigint unsigned not null) partition by range(a) ( + partition p0 values less than (10), + partition p1 values less than (100), + partition p2 values less than (1000), + partition p3 values less than (18446744073709551000), + partition p4 values less than (18446744073709551614) +); +insert into t1 values (5),(15),(105),(1005); +insert into t1 values (18446744073709551000+1); +insert into t1 values (18446744073709551614-1); + +explain partitions select * from t1 where a < 10; +explain partitions select * from t1 + where a >= 18446744073709551000-1 and a <= 18446744073709551000+1; + +explain partitions select * from t1 + where a between 18446744073709551001 and 18446744073709551002; + +explain partitions select * from t1 where a = 18446744073709551000; +explain partitions select * from t1 where a = 18446744073709551613; +explain partitions select * from t1 where a = 18446744073709551614; +drop table t1; +# +# Test all variants of usage for interval_via_mapping +# and interval_via_walking +# +# t1 will use interval_via_mapping since it uses a +# monotonic function, whereas t2 will use +# interval_via_walking since the intervals are short +# and the function isn't monotonic (it is, but it isn't +# discovered in this version). +# + create table t1 (a int) + partition by range(a) ( + partition p0 values less than (64), + partition p1 values less than (128), + partition p2 values less than (255) +); + +create table t2 (a int) + partition by range(a+0) ( + partition p0 values less than (64), + partition p1 values less than (128), + partition p2 values less than (255) +); + +insert into t1 values (0x20), (0x20), (0x41), (0x41), (0xFE), (0xFE); +insert into t2 values (0x20), (0x20), (0x41), (0x41), (0xFE), (0xFE); +explain partitions select * from t1 where a=0; +explain partitions select * from t2 where a=0; +explain partitions select * from t1 where a=0xFE; +explain partitions select * from t2 where a=0xFE; +explain partitions select * from t1 where a > 0xFE AND a <= 0xFF; +explain partitions select * from t2 where a > 0xFE AND a <= 0xFF; +explain partitions select * from t1 where a >= 0xFE AND a <= 0xFF; +explain partitions select * from t2 where a >= 0xFE AND a <= 0xFF; +explain partitions select * from t1 where a < 64 AND a >= 63; +explain partitions select * from t2 where a < 64 AND a >= 63; +explain partitions select * from t1 where a <= 64 AND a >= 63; +explain partitions select * from t2 where a <= 64 AND a >= 63; +drop table t1; +drop table t2; + +create table t1(a bigint unsigned not null) partition by range(a+0) ( + partition p1 values less than (10), + partition p2 values less than (20), + partition p3 values less than (2305561538531885056), + partition p4 values less than (2305561538531950591) +); + +insert into t1 values (9),(19),(0xFFFF0000FFFF000-1), (0xFFFF0000FFFFFFF-1); +insert into t1 values (9),(19),(0xFFFF0000FFFF000-1), (0xFFFF0000FFFFFFF-1); + +explain partitions select * from t1 where + a >= 2305561538531885056-10 and a <= 2305561538531885056-8; + +explain partitions select * from t1 where + a > 0xFFFFFFFFFFFFFFEC and a < 0xFFFFFFFFFFFFFFEE; + +explain partitions select * from t1 where a>=0 and a <= 0xFFFFFFFFFFFFFFFF; +drop table t1; + +create table t1 (a bigint) partition by range(a+0) ( + partition p1 values less than (-1000), + partition p2 values less than (-10), + partition p3 values less than (10), + partition p4 values less than (1000) +); +insert into t1 values (-15),(-5),(5),(15),(-15),(-5),(5),(15); +explain partitions select * from t1 where a>-2 and a <=0; +drop table t1; + +--disable_query_log +drop database pbxt; +--enable_query_log |