diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
commit | 3f619478f796eddbba6e39502fe941b285dd97b1 (patch) | |
tree | e2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/main/desc_index_range.test | |
parent | Initial commit. (diff) | |
download | mariadb-upstream.tar.xz mariadb-upstream.zip |
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to '')
-rw-r--r-- | mysql-test/main/desc_index_range.test | 143 |
1 files changed, 143 insertions, 0 deletions
diff --git a/mysql-test/main/desc_index_range.test b/mysql-test/main/desc_index_range.test new file mode 100644 index 00000000..ec52e254 --- /dev/null +++ b/mysql-test/main/desc_index_range.test @@ -0,0 +1,143 @@ +# +# Tests for range access and descending indexes +# +--source include/have_sequence.inc +--source include/have_innodb.inc + +# The test uses optimizer trace: +--source include/not_embedded.inc + +create table t1 ( + a int, + key (a desc) +); +insert into t1 select seq from seq_1_to_1000; + +set optimizer_trace=1; +explain select * from t1 force index(a) where a in (2, 4, 6); + +#enable after fix MDEV-27871 +--disable_view_protocol +select json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) +from information_schema.optimizer_trace; +--enable_view_protocol +set optimizer_trace=default; + +--echo # These should go in reverse order: +select * from t1 force index(a) where a in (2, 4, 6); +drop table t1; + +--echo # +--echo # Multi-part key tests +--echo # +create table t1 ( + a int not null, + b int not null, + key ab(a, b desc) +); + +insert into t1 select A.seq, B.seq*10 from seq_1_to_10 A, seq_1_to_10 B; + +#enable after fix MDEV-27871 +--disable_view_protocol +set optimizer_trace=1; +explain select * from t1 force index(ab) where a>=8 and b>=50; +select json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) +from information_schema.optimizer_trace; + +explain select * from t1 force index(ab) where a>=8 and b<=50; +select json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) +from information_schema.optimizer_trace; +--enable_view_protocol + +select * from t1 force index(ab) where a>=8 and b<=50; +select * from t1 ignore index(ab) where a>=8 and b<=50 order by a, b desc; + +#enable after fix MDEV-27871 +--disable_view_protocol +explain +select * from t1 where a between 2 and 4 and b between 50 and 80; +select json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) +from information_schema.optimizer_trace; +--enable_view_protocol + +select * from t1 where a between 2 and 4 and b between 50 and 80; + +drop table t1; + +create table t2 ( + a int not null, + b int not null, + key ab(a desc, b desc) +); +insert into t2 select A.seq, B.seq*10 from seq_1_to_10 A, seq_1_to_10 B; + +#enable after fix MDEV-27871 +--disable_view_protocol +explain +select * from t2 where a between 2 and 4; +select json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) +from information_schema.optimizer_trace; + +explain +select * from t2 where a between 2 and 4 and b between 50 and 80; +select json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) +from information_schema.optimizer_trace; +--enable_view_protocol + +drop table t2; + +--echo # +--echo # Check that "Using index for group-by" is disabled (it's not supported, yet) +--echo # +CREATE TABLE t1 (p int NOT NULL, a int NOT NULL, PRIMARY KEY (p,a desc)); +insert into t1 select 2,seq from seq_0_to_1000; +EXPLAIN select MIN(a) from t1 where p = 2 group by p; +#enable after fix MDEV-27871 +--disable_view_protocol +select json_detailed(json_extract(trace, '$**.potential_group_range_indexes')) +from information_schema.optimizer_trace; +--enable_view_protocol +drop table t1; + +set optimizer_trace=default; + +--echo # +--echo # MDEV-27426: Wrong result upon query using index_merge with DESC key +--echo # + +CREATE TABLE t1 (pk INT, a INT, b int, KEY(a), PRIMARY KEY(pk DESC)) +ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,4,5),(2,9,6),(3,NULL,7),(4,NULL,8); + +SELECT * FROM t1 WHERE pk > 10 OR a > 0; +DROP TABLE t1; + + +--echo # +--echo # MDEV-27529: Wrong result upon query using index_merge with DESC key (#2) +--echo # + +create table t1 ( + pk int, + a int, + b int, + primary key(pk desc), + key(a), + key(b) +) engine=innodb; + +insert into t1 values (0, 111111, 255); + +insert into t1 select seq+50000, NULL, seq+1000 from seq_1_to_260; + +insert into t1 values (10000, NULL, 255); +insert into t1 select seq+20000, seq+20000, seq+20000 from seq_1_to_1500; + +analyze table t1; + +--echo # Must use ROR-intersect: +explain select * from t1 where b = 255 AND a IS NULL; +select * from t1 where b = 255 AND a IS NULL; + +drop table t1; |